In this post, I’m going to share about MySQL utility which is used to take SQL based backups known as “mysqldump”. If you are here then you must have an overview of how to use it to take backups.
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. It command can also generate output in CSV, other delimited text, or XML format. It was originally written by Igor Romanenko.
It doesn’t usually consume much CPU resources on modern hardware as by default it uses a single thread. This method is good for a heavily loaded server.
Disk input/outputs per second (IOPS), can however increase for multiple reasons. When you back-up on the same device as the database, this produces unnecessary random IOPS. The dump is done sequentially, on a per table basis, causing a full-table scan and many buffer page misses on tables that are not fully cached in memory.
Basic usage:
mysqldump -u user -p database_name > dump.sql
So that is how you take backup of a database using mysqldump, it creates a backup file as dump.sql which can be used to restore the given database into any MySQL server.
Most important MySQLDump Options:
I think these are the mostly used and important options in a practical environment:
Do not lock the live Database(Hot Backup):
–single-transaction: When you have a live database and you do not want to lock any table while taking the backup use ‘–single-transaction’. How? It makes sure that mysqldump setup a repeatable read transaction for all the tables being dump so that the data is consistent and the tables are not locked for any further transaction.
mysqldump -u user -p password Database_name --single-transaction > dump.sql
Get exact Binary log file and Position:
–master-data=2: If you are taking backup of a server to create a slave/secondary database and want to setup replication between them use ‘–master-data’. Why? It first creates a lock to make sure the binary log coordinates are consistent and then you have the values in the dump.sql file. Why –master-data=2? It writes the ‘change master to …’ command as SQL comment instead SQL statement such that it does not apply the command at the time of restoration. So you can change the command to include ‘master_host’, ‘user’, ‘password’, etc.
mysqldump -u user -p password Database_name --master-data=2 > dump.sql
Create slave of existing slave:
–dump-slave=2: It is almost similar to the option we discussed in the statement above (–master-data), the only difference is when you are creating a slave of an existing slave server, it will add the binary log file details of the slave and not the master.
mysqldump -u user -p password Database_name --dump-slave=2 > dump.sql
Backup only the Table Data:
–no-create-info: If you just want to backup the table data only and not the table structure you can you this option. If you check the backup file created while including this option you will see that there will be no statement like ‘Create Table table name…..’ just the Insert statements will be there. Here is the full command if you want to backup table ‘T1’ of Database ‘DB’:
mysqldump -u user -p password DB T1 --no-create-info > dump.sql
Backup only the Table Structure:
–no-data: If you just want to backup the table structure only and not the table data you can you this option. If you check the backup file created while including this option you will see that there will be no statement like ‘Insert into table name…..’ just the ‘Create Table’ statements will be there. Here is the full command if you want to backup table ‘T1’ of Database ‘DB’:
mysqldump -u user -p password DB T1 --no-create-info > dump.sql
Backup only the Stored Procedures:
–routines: We can use this option to dump the stored procedures but in the experience as a DBA we get requests from developers for only backing up the stored procedures and not the database or table content. Use the command below to backup just the stored procedures:
mysqldump -u user -p password database_name --no-create-info --no-create-db --no-data --routines > storedprocdump.sql
Here ‘–n0-create-db’ is used to skip the ‘CREATE DATABASE’ statement.
Now I’ll share some of the options that I use regularly:
Backup a single Database:
mysqldump -u user -p database_name > dump.sql
Backup multiple Databases:
mysqldump -u user -p --databases database_name1 database_name2 database_name3 > dump.sql
Backup all Databases:
mysqldump -u user -p --all-databases > dump.sql
Backup single table of a database:
mysqldump -u user -p database_name table_name > dump.sql
Backup multiple tables of single data:
mysqldump -u user -p database_name table_name1 table_name2 table_name3 > dump.sql
Backup a table and restore to different database:
Backup: mysqldump -u user -p database_name table_name > dump.sql Restore: mysql -u user -p database_name2 < dump.sql
Backup triggers only:
mysqldump -u user -p database_name --triggers --no-create-info --no-data --no-create-db > dump.sql
Backup views only:
We cannot backup just the views, we can take full backup and just restore the views.
I’ll keep on adding more scenario based backup situations where we can use our backup utility, ask in comments for the same and let me know in case anything can be improved, any suggestions are highly appreciated.
Sources: dev.mysql.com