The easiest way to backup your databases without using a web interface like phpmyadmin, is to use the mysql-client built-in utility mysqldump. It provide the ability to perform backup of a single database, as well as full dump of your MySQL server.
In our examples, we have previously set our MySQL credentials in the file .my.cnf, explained in our previous article Login automatically in MySQL with .my.cnf. This way we can your directly mysqldump instead of mysqldump -u root -p
Backup/Restore a single database
To backup a single database with mysqldump, you just need the name of the database you want to dump :
mysqldump database_name > database_name_dump.sql
Then to restore your database, you can use :
mysql database_name < database_name_dump.sql ``` ## Make a full dump of your MySQL server To perform a full dump of your MySQL server, including server settings and users, you can use the command : ``` mysqldump --all-databases --events > fulldump.sql
Then to restore your MySQL server, you can use :
mysql < fulldump.sql
Optimization
Here some tips to perform your backup faster and to ensure data integrity of your backup.
Disable tables locking during the dump
By default, mysqldump will lock the table of your database during the dump process to make sure there will not have new data added during this time-frame.
But it may impact your applications during the dump, because it will not be possible to read/write/update data inside the locked table.
Nowadays,MySQL servers are running with the storage engine InnoDB by default, and it provide the ability to perform backup with mysqldump without locking tables.
To dump your databases without locking tables, you can use the flag --single-transaction with mysqldump. It will automatically turn off the option --lock-tables and perform a snapshot by dumping all tables in a single transaction.
Compress your MySQL dump on the fly
If you want to reduce the size of your backup, you can easily compress them with GZIP, like in this example :
mysqldump --single-transaction database_name | gzip -9 >database_name_dump.gz