编程知识 cdmana.com

Introduction to mysqldump

mysqldump The introduction is divided into two parts: using examples and configuration items . The second and third of these are performance optimization options .
1. Examples of use

# Back up the entire library 
shell> mysqldump db_name > backup-file.sql
# from dump File restore data mode 1 
shell> mysql db_name < backup-file.sql
# from dump File restore data mode 2 
shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
# Use mysqldump Copy data from one library to another MySQL The server 
shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
# Exporting multiple databases 
shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
# Export all databases 
shell> mysqldump --all-databases > all_databases.sql
# in the light of InnoDB Online backup of tables . A global lock is required at the beginning of the export ( Use FLUSH TABLES
WITH READ LOCK).
shell> mysqldump --all-databases --master-data --single-transaction >
all_databases.sql

2. Performance optimization options
1. --extended-insert, -e: Use syntax containing multiple values to generate INSERT sentence . This will shrink dump Proper documentation
product , And it's accelerating dump Of documents insert operation .
2. --insert-ignore: Use INSERT IGNORE sentence , instead of INSERT sentence .
3. --max-allowed-packet=value: Maximum buffer size for communication between client and server . The default is 24MB, The biggest is
1GB.
4. --net-buffer-length=value: The initial size of communication between the client and the server . When creating multiple lines INSERT When the sentence is ,
mysqldump Create the most --net-buffer-length Byte long lines . If you increase this variable MySQL Server's
net_buffer_length The value of the system variable is at least larger than this .
5. --opt: This option , Default on , yes --add-drop-table --addlocks
--create-options --disable-keys --extended-insert --lock-tables --quick
--set-charset Short for combination . It can quickly dump, And generated dump Files can be loaded faster
MySQL The server .
6. --quick, -q: This option is useful for exporting large tables .
3. Transaction options
The following options balance the performance of the export operation , Depending on the reliability and consistency of the exported data .
1. --add-locks: For each table dump, Around a couple of LOCAK TABLES and UNLOCK TABLES sentence . This will make the director
When the output file is loaded , Write faster .
2. --lock-all-tables, -x: Lock all tables in all libraries . It's through the whole dump Get a global read lock during the period . this
Options are automatically turned off --single-transaction and --locktables.
# Back up the entire library
shell> mysqldump db_name > backup-file.sql
# from dump File restore data mode 1
shell> mysql db_name < backup-file.sql
# from dump File restore data mode 2
shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
# Use mysqldump Copy data from one library to another MySQL The server
shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
# Exporting multiple databases
shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
# Export all databases
shell> mysqldump --all-databases > all_databases.sql
# in the light of InnoDB Online backup of tables . A global lock is required at the beginning of the export ( Use FLUSH TABLES
WITH READ LOCK).
shell> mysqldump --all-databases --master-data --single-transaction >
all_databases.sql
3. --lock-tables, -l: For each database to be exported , Before you export them , Lock all tables to be exported . For support
My schedule ,--single-transaction Option ratio --lock-tables Much better , Because it doesn't have to lock all the tables at all . because -
-lock-tables Lock the tables of each library separately , This option ensures that the tables in the export file are logically linked between databases
Cause . Tables in different databases can have completely different export States .
4. --no-autocommit: Use SET autocommit = 0 and COMMIT Close the... Of each exported table INSERT sentence .
5. --single-transaction: Useful for tables that support transactions , Ensure consistent export State .

版权声明
本文为[lyle_ Luo]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224105400252r.html

Scroll to Top