Backup and Restore of MySql database from the command line (SSH)

The easiest way to backup your database would be to telnet to the your database server machine and use the mysqldump command to dump your whole database to a backup file. If you do not have telnet or shell access to your server, don't worry about it; I shall outline a method of doing so using the PHPMyAdmin web interface, which you can setup on any web server which executes PHP scripts.

Playing with mysqldump

On ourVPS or dedicated servers, you have either a shell or SSH access to your database server, you can backup the database using mysqldump. By default, the output of the command will dump the contents of the database in SQL statements to your console. This output can then be piped or redirected to any location you want. If you plan to backup your database, you can pipe the output to a sql file, which will contain the SQL statements to recreate and populate the database tables when you wish to restore your database. There are more adventurous ways to use the output of mysqldump.

Backing Up a Database:

You can use mysqldump to create a simple backup of your database using the following syntax.

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

      o [username] - this is your database username  
      o [password] - this is the password for your database  
      o [databasename] - the name of your database  
      o [backupfile.sql] - the file to which the backup should be written.  

The resultant dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database 'Customers' with the username 'sadmin' and password 'passxx' to a file custback.sql, you would issue the command:

mysqldump -u sadmin -p passxx Customers > custback.sql   

You can also ask mysqldump to add a drop table command before every create command by using the option --add-drop-table. This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

mysqldump --add-drop-table -u sadmin -p passxx Customers > custback.sql   

Compressing your Backup file on the Fly

Backups of databases take up a lot of space. You can compress the output of mysqldump to save valuable space while you're backing up your databases. Since mysqldump sends its output to the console, we can pipe the output through gzip or bzip2 and send the compressed dump to the backup file. Here's how you would do that with bzip2 and gzip respectively.

--add-drop-table -u sadmin -p passxx Customers  | bzip2 -c >custback.sql.bz2  

OR
mysqldump -u sadmin -p passxx Customers | gzip >custback.sql.gz

Restoring a database

you can easily restore the mysqldump file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch.

Here's how you would restore your custback.sql file to the Customers database.

mysql -u sadmin -p passxx Customers < custback.sql   

Here's the general format of the command:

mysql -u [username] -p [password] [database_to_restore] < [backupfile]   

Restoring compressed files

You can restore your zipped backup files by first uncompressing its contents and then sending it to mysql.

gunzip < custback.sql.gz | mysql -u sadmin -p passxx Customers