catalogue
1. Export all database structures of the whole instance
2. Export data of a table under the database
3. Export a table structure in the database (excluding data)
4. Backup a single library without parameters (not recommended for production)
5. Use parameters to back up a single library (recommended for production)
Backup command parameters of different engines in enterprise production scenarios
1. Key parameters of mysqldump
2. Different engine backup command parameter usage
Backup command: mysqldump
Mysql database comes with a very useful backup command. It is a logical backup and exports SQL statements. That is, the process of directly outputting data from MySQL database in the form of logical SQL statements or generating backup files.
Parameter analysis
-A --all-databases: Export all databases -Y --all-tablespaces: Export all tablespaces -y --no-tablespaces: No tablespace information is exported --add-drop-database Add before each database is created drop Database statement. --add-drop-table Add before each data table is created drop Data table statement.(It is on by default. Use--skip-add-drop-table Cancel option) --add-locks Add before exporting each table LOCK TABLES And after UNLOCK TABLE. (It is on by default. Use--skip-add-locks Cancel option) --comments Additional comment information. It is on by default and can be used--skip-comments cancel --compact Export less output information(For debugging). Remove comments, head and tail structures. The following options are available:--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys -c --complete-insert: Use complete insert sentence(Include column name). This can improve the insertion efficiency, but it may be affected max_allowed_packet The insertion failed due to the influence of parameters. -C --compress: Enable compression to pass all information between the client and the server -B--databases: Export several databases. All parameters after the parameter are treated as database names. --debug output debug Information for debugging. The default value is: d:t:o,/tmp/ --debug-info Output debugging information and exit --default-character-set Set the default character set. The default value is utf8 --delayed-insert Delay insertion mode is adopted( INSERT DELAYED)Export data -E--events: Export events. --master-data: When the backup is written in the backup file binlog After the file is restored, the incremental data is restored from the log after the file. When the value is 1, binlog The file name and location have no comments. If it is 2, it will be deleted in the backup file binlog Comment on the file name and location of --flush-logs Refresh the log before starting the export. Please note: if you export more than one database at a time(Use options--databases perhaps--all-databases)，The logs will be refreshed one by one. Except use--lock-all-tables perhaps--master-data Outside. In this case, the log will be refreshed once, and the corresponding tables will be locked at the same time. Therefore, if you intend to export and refresh logs at the same time, you should use--lock-all-tables perhaps--master-data and--flush-logs. --flush-privileges Export in mysql After the database, issue a message FLUSH PRIVILEGES sentence. For proper recovery, this option should be used for export mysql Databases and dependencies mysql Database data at any time. --force Ignore occurrences during export SQL Wrong. -h --host: Host information to be exported --ignore-table Do not export the specified table. When you specify to ignore multiple tables, you need to repeat them multiple times, one table at a time. Each table must specify both a database and a table name. For example:--ignore-table=database.table1 --ignore-table=database.table2 ...... -x --lock-all-tables: Submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock and is automatically closed--single-transaction and--lock-tables Options. -l --lock-tables: Lock all tables before starting export. use READ LOCAL Lock table to allow MyISAM Table parallel insertion. For tables that support transactions, for example InnoDB and BDB，--single-transaction Is a better choice because it doesn't need to lock the table at all. Note that when exporting multiple databases,--lock-tables Lock the tables separately for each database. Therefore, this option cannot guarantee the logical consistency between the tables in the exported file and the database. The export status of different database tables can be completely different. --single-transaction: fit innodb Backup of transaction database. To ensure the consistency of backup, the principle is to set the isolation level of this session as Repeatable read，To ensure this session (that is dump)You will not see the data submitted by other sessions. -F: Refresh binlog，If binlog It's open,-F Parameters are refreshed automatically during backup binlog Switch. -n --no-create-db: Export data only, not add CREATE DATABASE sentence. -t --no-create-info: Export data only, not add CREATE TABLE sentence. -d --no-data: Do not export any data, only export the database table structure. -p --password: Connect database password -P --port: Connection database port number -u --user: Specify the user name of the connection. --routines, -R Export stored procedures and custom functions. --triggers Export trigger
Example use
1. Export all database structures of the whole instance
If all data is selected, the system mysql The library still carries data, and only structure is created by individuals mysqldump -ugoodhope -pgoodhope --all-databases -d > all.sql We can specify our own library, so only the structure can be backed up mysqldump -ugoodhope -pgoodhope -d dbname > all.sql
2. Export data of a table under the database
mysqldump -ugoodhope -pgoodhope dbname tablename > all.sql
export test Kuxia a Data addition structure of table
mysqldump -ugoodhope -pgoodhope test107 a > all.sql3. Export a table structure in the database (excluding data)
mysqldump -ugoodhope -pgoodhope -d dbname tablename > all.sql database tst107 lower a Table structure export mysqldump -ugoodhope -pgoodhope -d test107 a > all.sql
4. Backup a single library without parameters (not recommended for production)
Backup: mysqldump -ugoodhope -pgoodhope test107 > all.sql Create Library: create database test107 Restore Library: mysql -ugoodhope -pgoodhope dbname<all.sql No library was created or specified in the backed up file During recovery, we need to create an empty library first, and then specify the library during restoration to restore normally. It is cumbersome and we do not recommend using it
5. Use parameters to back up a single library (recommended for production)
1. Backup
mysqldump -ugoodhope -pgoodhope -B test107 > all.sql
Note: after adding the - B parameter, back up the commands of Create database and use mytest in the file
Benefits of adding - B parameter:
After adding the - B parameter, there are statements to create and use the library in the exported data file. There is no need to manually create the library in the original library. In the recovery process, there is no need to manually create the library, and you can restore it directly.
2. Recovery operation
Delete Library (if library already exists) mysql -ugoodhope -pgoodhope -e 'drop database test107;' recovery mysql -ugoodhope -pgoodhope <all.sql
6. Compressed backup
mysqldump -ugoodhope -pgoodhope -B test107 | gzip > all.gz Generate compressed files, We can decompress the backup data first gzip -d all.gz Restore can be used directly gz Files can also be decompressed and used. Decompressed files are OK recovery mysql -ugoodhope -pgoodhope <all.gz
7. Backup multiple libraries
adopt-B Worth up to multiple libraries mysqldump -ugoodhope -pgoodhope -B test107 testoracle | gzip > all.gz
8. Sub database backup
We can perform multiple backups based on the above single library backup, or we can use one instruction to backup each library circularly
for dbname in ` mysql -ugoodhope -pgoodhope -e "show databases;" | grep -Evi "database|infor|perfor"`
do
mysqldump -ugoodhope -pgoodhope --events -B $dbname | gzip >${dbname}_bak.sql.gz
done
explain: ${dbname}_bak，Because the backup file name is required to $dbname_bak.sql.gz Format command, but the system cannot distinguish whether the variable is $dbname still $dbname_bak，So you need braces at this time“{}"Enclose the variables, that is ${dbname}_bak.sql.gz Yes.
Backup command parameters of different engines in enterprise production scenarios
1. Key parameters of mysqldump
-B: Specify multiple libraries, and add database building statements and in the backup file use sentence --compact: Remove the notes in the backup file, which is suitable for debugging and not used in production scenarios -A: Back up all libraries -F: Refresh binlog journal --master-data: Add in backup file binlog Log file name and corresponding location point -x --lock-all-tables: Lock table -l: Read only lock table -d: Backup table structure only -t: Backup data only --single-transaction: fit innodb Backup of transaction database InnoDB When a table is backed up, the option is usually enabled--single-transaction To ensure the consistency of backup, the principle is to set the isolation level of this session as Repeatable read，To ensure this session (that is dump)You will not see the data submitted by other sessions.
2. Different engine backup command parameter usage
1)Myisam Engine: mysqldump -uroot -p123456 -A -B --master-data=1 -x| gzip > /data/all_$(date +%F).sql.gz (2)InnoDB Engine: mysqldump -uroot -p123456 -A -B --master-data=1 --single-transaction > /data/bak.sql (3)production environment DBA Command given a,for MyISAM mysqldump --user=root --all-databases --flush-privileges --lock-all-tables \ --master-data=1 --flush-logs --triggers --routines --events \ --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql b,for InnoDB mysqldump --ugoodhoe -pgoodhope --all-databases --flush-privileges --single-transaction \ --master-data=1 --flush-logs --triggers --routines --events \ --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql