nazly.me

Weblog of Nazly Ahmed

Nazly Ahmed

Nazly Ahmed

Web Developer. PHP Addict. Wordpress Hacker. FOSS Enthusiast. (Micro)Blogger. Photo Hobbyist. Cricket Fanatic. Husband. Dad.

Optimizing the MySQL tables of a WordPress Website

If you are running a WordPress Website or a Blog where the content is updated and/or deleted frequently, you will need to optimize the MySQL tables more often than not so that you don’t run into database issues and down times. This also helps to maintain the average response time of MySQL queries.

Read More >>

Posted 7 years ago

Basics of maintaing a WordPress site

WordPress is simple, yet powerful. Those are the key ingredients why it is popular among the Bloggers and Web Developers alike. WordPress will always be the first choice as a blogging platform. In the recent times it has moved far from being just a blogging engine. Most Web developers choose WordPress as their primary CMS of choice ahead of other popular Content Management Systems. Regardless of you being a Blogger or a Web Developer, the manner you maintain your WordPress files and database will play an important role in running a successful website.

Read More >>

Posted 7 years ago

Protect your website against SQL injection

SQL injection is one of the deadliest techniques attackers use to exploit the weakness in your database code of your website. Regardless of the technology/scripting language you must make sure your code is 100% perfect against SQL injection.

Here I will use PHP and MySQL examples for its wide usage and also I’m much more comfortable with it.

Read More >>

Posted 8 years ago

Restoring Large MySQL dumps

A lot of things influenced me to write this post. In fact I wanted to write on this sometime back but finally I was able to squeeze some time. Lately I have been moving data between servers and the main problem I had was with large MySQL dumps because I have been using PHPMyAdmin for most of the MySQL operations and its one of the best tools available and most importantly its web-based. Even on my local development environment I'm comfortable with using PHPMyAdmin and on the web servers it can be very handy if the server is a shared hosting server. I would rather recommend using the command line client utilities that MySQL offers for import/export operations because its the safest. But you will need SSH access to your server. If you do have SSH access don't hesitate to choose this method above the others.

Export/Backup

Using the mysqldump client it is possible to backup a database into a SQL file which will contain SQL statements that can recreate the database tables when restored.

The following command from shell can be executed to backup a specific database

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

Click Here for more options on using mysqldump

Import/Restore

You can use this command from shell to restore the database using the SQL dump file

mysql -u [username] -p [password] [databasename] < [backupfile.sql]

So thats quite basic and safest ways to import/export. But then you might ask whats the options you have if you are on a shared server and do not have SSH access. Well then PHPMyAdmin is the only choice available coz its web-based. Most servers have PHPMyAdmin as an option in the Server's Control Panel. Worst case if you don't have it or cannot find it you simply can download the source from www.phpmyadmin.net and upload the files via FTP and set it up. Installation is quite simple if you follow the Documentation.txt file. Once it is setup you can create databases etc. When you have selected a database from the left panel there will be tabs called SQL and Export. Using the SQL tab you can restore the database using your SQL dump file. Similarly you can use the Export option to export the data to a SQL file.

But then again you come across problems when you have a large MySQL dump. Exporting a large database won't be a problem but there are times that the SQL dump file tends to get corrupted for various reasons. Importing a large SQL dump file would create a problems coz with default installations there is a 2MB upload limit. This is not a PHPMyAdmin limit. This limit is set in the PHP configuration. To increase this upload limit you have to change the post_max_size and upload_max_filesize directives in the php.ini and then you can restore a large SQL dump. But if you are on a shared hosting server its highly unlikely that you can change the directives in the php.ini file. So thats where most people get stuck. When you are moving from your local machine to the server this is one problem you will face. Similar problem I faced when I had to move a large database from a server that I could SSH to a shared hosting server. I dumped the database to a SQL file using mysqldump command line utility and then when I tried to restore using PHPMyAdmin there was this upload limit. Arghh.. At that time I simply split the file manually into smaller files which are less than 2MB and uploaded one by one. It came to about 7 files at that time so didn't really bother about splitting them manually. This is a dirty trick but still effective but I won't suggest you to use this method. At a later time when I came across a similar instance I planned to write a tiny PHP script that would do the job. But thankfully I got a new server that I could SSH into.

So things can get bit messy at these situations so gotta figure out ways to overcome those with the limited resources we have. Lately I found BigDump: Staggered MySQL Dump Importer which seems to do the job on the web servers with hard runtime limit. So I guess I have more choices now. I haven't tried out this yet. Hopefully can play around with this next time when I have tight limits.

Posted 10 years ago

Copying records in MySQL

Copying records from one table to another can be a very basic requirement. But writing queries to perform this task can be bit of a work around. But there is a very simple query to get this done.

Its by using INSERT … SELECT

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Copy one record from a table to another

1
INSERT INTO `dest_table` SELECT * FROM source_table WHERE id = '10'

It can be very simple as this. Even multiple records can be copied from a single table or several tables. If I'm not mistaken INSERT … SELECT works on MySQL versions 4.1 and above.

Posted 11 years ago

MySQL GUI tools

I have been playing around with the MySQL GUI tools lately specially with the MySQL Query Browser and its really easy to play around with SQL queries. I did use PHPMyAdmin effectively though I simply like the Query Browser. MySQL Migration Toolkit and MySQL Administrator are the other two GUI tools available. If you haven't started on it, go get 'em [Wink]. You can download them from http://dev.mysql.com/downloads/

Posted 13 years ago

Export MySQL database to an MS Excel format

Here is an updated version of the script I had at PHP-Help.net which exports a specified MySQL table. After couple of requests I got, I made some changes to the script to download all the tables of a specific database. Let me know if u catch any bugs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
<?php
#Name of the Database to export can be sent via GET variable called 'db'
$dbToExport=(isset($_GET["db"]))?$_GET["db"]:"test";
 
mysql_connect("localhost","root","******") or die("Connection to MySQL failed");
mysql_select_db($dbToExport) or die("Couldn't connect to DB");
 
#Get all tables in the database
$mTblQuery="show tables";
$mTblResult=mysql_query($mTblQuery);
 
$dataStr="";
#Loop through the table names
while($tblRow=mysql_fetch_assoc($mTblResult)){
	#Store output of the table name
	$dataStr.="Table : \t".$tblRow["Tables_in_".$dbToExport]."\r\n";
 
	#Select all records from the table
	$mQuery="select * from `".$tblRow["Tables_in_".$dbToExport]."`";	
	$mResult=mysql_query($mQuery);
 
	#Get no of fields in the table
	$numFields=mysql_num_fields($mResult) or die(mysql_error());
 
	#Get all fields in the table
	$tblFields=array();
	for($i=0;$i<$numFields;$i++){
		$tblFields[]=mysql_field_name($mResult,$i);
	}
 
	#Store output of fieldnames
	$dataStr.=implode("\t",$tblFields);
	$dataStr.="\r\n";
 
	#Store output of all the records
	while($row=mysql_fetch_assoc($mResult)){
		$rec=array();
		foreach($tblFields as $tblField){
			$recData=str_replace("\r\n"," ",$row[$tblField]);
			$recData=str_replace("\n"," ",$recData);
			$recData=str_replace("\t"," ",$recData);
 
			$rec[]=$recData;
		}
		$dataStr.=implode("\t",$rec);
		$dataStr.="\r\n";
	}
	$dataStr.="\r\n";
	$dataStr.="\r\n";
}
 
#Force the browser to download the file
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=export_$dbToExport.xls");
header("Pragma: no-cache");
header("Expires: 0");
 
echo $dataStr;//Display Stored Output
?>
Posted 13 years ago

Storing Images in MySQL

I just wrote a quick example that explains how to store images in MySQL database using the BLOB fieldtype which can used to store Binary Data. But I have been used to store the images in the filesystem and have a reference to it in the database. Most of them feel storing images or other binary data in the database is a bad idea as it creates too much overhead. But there are some advantages using this method as well. Have fun..

—– Table Structure —–

1
2
3
4
5
6
 
mysql> CREATE TABLE `imgtest` (
    -> `id` int(10) unsigned NOT NULL auto_increment,
    -> `imgstr` longblob NOT NULL,
    -> PRIMARY KEY (`id`)
    -> );

Insert image to the database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
#Image Path
$imagePath="/path/to/img/img3.jpg";
 
#Connect to MySQL
mysql_connect("localhost","user","******");
mysql_select_db("test");
 
#Read Image file into a String
$imgStr=addslashes(file_get_contents($imagePath));
 
#Store Image String to the database
$query="insert into imgtest (imgstr) values ('".$imgStr."')";
mysql_query($query) or die(mysql_error());
?>

Display image from database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
#Set Content Type
header("Content-Type: image/jpg");
 
#Image Id
$imgId=(isset($_GET["id"]))?$_GET["id"]:1;
 
#Connect to MySQL
mysql_connect("localhost","root","*****");
mysql_select_db("test");
 
#Output Image String from database
$query="select imgstr from imgtest where id='$imgId'";
$result=mysql_query($query);
$imgStr=mysql_result($result,0,"imgstr");
echo $imgStr;
?>
Posted 13 years ago

MySQL 4.1 authentication issue

I have been working on MySQL 4.0.17 till now and wanted to upgrad to MySQL 4.1. So I downloaded and installed MySQL 4.1 and the installation was successful and it was working fine. But when I tried to access it through PHPMyAdmin or from any of my PHP code it threw this error


Client does not support authentication protocol requested by server;
consider upgrading MySQL client

So I checked it out at MySQL.com to see what I was missing and found out that in MySQL 4.1 the authentication process has been upgraded by making the hashes more secure. So when trying to access it with an old client it throws the above error.

The mysql extension in PHP doesn't support the new authentication protocol. So the workaround for this will be to reset the passwords in the format prior to MySQL 4.1. This can be done by using the old_password() function. Make sure to only reset the passwords of the users that need to access using a client prior to 4.1. Here is how it can be done

1
2
3
 
mysql> SET PASSWORD FOR
    -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

or

1
2
3
4
 
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
    -> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

The mysqli extension (MySQL Improved) which comes with PHP 5 is compatible with the improved password hashing in MySQL 4.1 and above.

Posted 13 years ago