This example will help you know how to backup one or multi MySQL database using mysqldump command. If your database is very big, you may need to compress the backup sql file. To do that, you can use gzip compression to pipe the output to gzip then you will get the output as gzip file.
1. Backup one database from Command Line using mysqldump
mysqldump -u root -p your_mysql_database_name > your_mysql_database_name.sql |
The command above assumes that you would like to backup the database your_mysql_database_name into a file called your_mysql_database_name.sql with MySQL root account.
2. Backup multi databases from Command Line using mysqldump
mysqldump -u root -p --databases database1 database2 database3 > database1_database2_database3.sql |
The command above assumes that you would like to backup 3 databases database1,database2 and database3 into a file called database1_database2_database3.sql with MySQL root account.
3. Back certain of tables from Command Line using mysqldump
mysqldump -u root -p your_mysql_database_name table1 table2 table3 > table1_table2_table3.sql |
The command above assumes that you would like to backup 3 tables: table1, table2 and table3 from database your_mysql_database_name into a file called table1_table2_table3.sql with MySQL root account.
4. Backup with GZIP compression
mysqldump -u root -p your_mysql_database_name | gzip -9 > your_mysql_database_name.sql.gz |
mysqldump -u root -p --databases database1 database2 database3 | gzip -9 > database1_database2_database3.sql.gz |
mysqldump -u root -p your_mysql_database_name table1 table2 table3 | gzip -9 > table1_table2_table3.sql.gz |
Commands above will compress the backup files with GZIP compression. It will reduce the volume of your backup files.
Note: you should be asked to enter your mysql account password, once you enter the password just key in your database password and you are done after minutes 🙂
If you need to know how to restore your mysql database, read this post
