Tuesday, May 14, 2013

MySQL Database Back up and Restore (Back up From the Command Line)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database.

Here is the proper syntax:
$ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]
Code for compressed format:
$ mysqldump -u [uname] -p[pass] [dbname] | gzip > [backupfile.sql.gz] 
If your db server located in remote system, using its IP & port we can take the back up :
$ mysqldump -P 3301 -h 231.200.12.45 -u [uname] -p[pass] [dbname] | gzip > [backupfile.sql.gz] 
Compressed format to store more space. Compare to .sql format, .sql.gz size is very very less. To see it in action, use below command to see the size of both files :
 ll -sh
Take back up of root files :
 tar -pczf filename.tar.gz /your/file/path
To extract this file, use below command :
 tar -zxvf filename.tar.gz 
Import  back sql file. Directly you can import file.sql.gz file into your database. 
use below command to do it.
 gunzip < file.sql.gz | mysql -u [uname] -p[pass] [dbname] 
For more details refer this link.