Tip : Backing up mysql database using mysqldump

Mysql server comes with a hadny command line tool called mysqldump, which you can use to backup your mysql databases. The most simple example is given below :

mysqldump -u sampleuser -p sampledb > samplefile

After entering this command, mysqldump will first prompt you to enter password for user 'sampleuser'. After mysqldump will connect to the mysql server running on your local machine and will try to log in using the username and password provided. After a successful login it start reading data stored in db 'sampledb' and printing that data as sql statements to the standard output. but since we have redirected the standard output to the file 'samplefile', those statements will be written into that file. Once the process is complete, you can use the file 'samplefile' to recreate and restore 'sampledb' should the need arise.

Another use full option is -h, which you can use backup data from remote mysql server. For exaple :

mysqldump -u sampleuser -p -h mysql.example.com sampledb > samplefile

If you want to backup only few selected tables from a database, you can specify their name after the database name. For example :

mysqldump -u sampleuser -p -h mysql.example.com sampledb table1 table2 > samplefile

If you want to backup multiple databases at once then use -B options and specify the names of all the databases you want to backup :

mysqldump -u sampleuser -p -h mysql.example.com -B sampledb1 sampledb2 sampledb3 > samplefile

Need to back up only database structure without the data, use -d option :

mysqldump -u sampleuser -p -h mysql.example.com  - d sampledb

On the other hand if you only want to save the data, without the table structure use -t option :

mysqldump -u sampleuser -p -h mysql.example.com -t sampledb > samplefile