Backup MySQL Database
MySQL provides mysqldump utility to let us dump SQL statements that can recreate the whole database. We can output the SQL statements to a plain text file, furthermore, a compressed file, or we can pipe the output to feed any other live database. Here in this post, we introduce some use cases for different scenarios to backup MySQL database.
- Backup Data Only
- Backup Data and Routines
- Backup Data and Routines with Compression
- Backup Data and Routines with Highest Degree Compression
- Backup or Migrate Database to a Local One
- Backup or Migrate Database to a Remote One
- More Options
- Exclude Some Tables
- Add a timer
- Put Jobs to Background
Backup Data Only
We'd like to backup only the data of database to a SQL file named example_db_data.sql.
[root@test ~]# mysqldump -u root -p'12345678' example_db > example_db_data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test ~]# ll
...
-rw-r--r--. 1 root root 1713574900 Aug 21 15:40 example_db_data.sql
As you can see, the size of SQL file is about 1.59 GB. Later on, you have to know how to import this SQL file into a database.
Backup Data and Routines
We call all kinds of stored procedures as routines in MySQL, which include procedures, functions, views and events. With -R, we can add routines to the backup file.
[root@test ~]# mysqldump -u root -p'12345678' example_db -R > example_db_data_routines.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test ~]# ll
...
-rw-r--r--. 1 root root 1713643597 Aug 21 15:41 example_db_data_routines.sql
The file size is a little larger than the first SQL file, but not much.
Backup Data and Routines with Compression
If the file size is too big to be stored, we can pipe the output stream to gzip in order to compress the file.
[root@test ~]# mysqldump -u root -p'12345678' example_db -R | gzip > example_db_data_routines.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test ~]# ll
...
-rw-r--r--. 1 root root 317699305 Aug 21 15:43 example_db_data_routines.sql.gz
The file size is reduced dramatically where the calculated compression ratio is about 18.5%. Please note that, you have to decompress the file before importing SQL into a database.
Backup Data and Routines with Highest Degree Compression
Compression is good, but it has a price. You have to trade off CPU resource against disk space. The highest degree of gzip is 9, which may let you pay more, but get less.
[root@test ~]# mysqldump -u root -p'12345678' example_db -R | gzip -9 > example_db_data_routines.sql.gz9
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test ~]# ll
-rw-r--r--. 1 root root 310848291 Aug 21 15:47 example_db_data_routines.sql.gz9
The compression ratio is somewhat improved, about 18.1%, better but not impressive.
Backup or Migrate Database to a Local One
Not only files, you can also backup the whole database to another one, either a local or remote database. In practice, we regularly use the technique as if we take a snapshot of the database.
[root@test ~]# mysqldump -u root -p'12345678' exmaple_db -R | mysql -u root -p'12345678' -D exmaple_db_backup
mysql: mysqldump: [Warning] Using a password on the command line interface can be insecure.
[Warning] Using a password on the command line interface can be insecure.
Since we are in the same server, there's no need to compress the output.
Backup or Migrate Database to a Remote One
To backup or migrate the whole database to a remote one, we have to think over the network condition. Chances are, we could transfer the data over the internet which is much slower than LAN. Therefore, we compress the output and then pipe the stream to a remote server via SSH tunnel in this example.
[root@test ~]# mysqldump -u root -p'12345678' exmaple_db -R | gzip | ssh 10.10.10.10 "gunzip | mysql -u root -p'12345678' -D exmaple_db_backup"
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
As you can see, we decompressed the output stream before we fed it into the database.
More Options
Any of the above use cases can be added some more options to meet your requirements. These techniques can be used in shell scripts and scheduled for your routine backup jobs.
Exclude Some Tables
Whenever you want to exclude a big or unchanged table for saving some space, you can add an option --igonore-table to this command.
[root@test ~]# mysqldump -u root -p'12345678' example_db --ignore-table=example_db.a_very_big_table > example_db_data.sql
Add a timer
For knowing how much time it will consume, you can add time ahead of command.
[root@test ~]# time mysqldump -u root -p'12345678' example_db > example_db_data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
real 2m34.493s
user 2m46.269s
sys 0m19.949s
Put Jobs to Background
To avoid accidental interruptions, you can run the job on the background by adding a leading nohup and a tailing & before issuing the command.
[root@test ~]# nohup mysqldump -u root -p'12345678' example_db > example_db_data.sql &
[1] 2602
...
[root@test ~]# jobs
[1]+ Running nohup mysqldump -u root -p'12345678' example_db -R | gzip -9 > example_db_data_routines.sql.gz9 &
That's it. I hope them helpful.