一、mysqldump

[root@db01 ~]# mkdir /data/backup -p
[root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock  xxxxx

1. 备份基础参数
1）全备 -A

[root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock  -A >/data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, 
even those that changed suppressed parts of the database. 
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-database

2）单库或多库备份 -B

[root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock  -B world oldboy >/data/backup/db.sql  
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions,
even those that changed suppressed parts of the database. 
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

3）单表或多表备份

[root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock  world city country >/data/backup/tub.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions,
even those that changed suppressed parts of the database.
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

2. 特殊备份参数
1）–master-data=2
在备份时，自动记录二进制日志的文件和位置号
可以自动加锁和解锁
加了 –single-transaction 可以减少锁表时间

[root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock  -A --master-data=2 >/data/backup/tub.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions,
even those that changed suppressed parts of the database.
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=332;

2）–single-transaction
在对于InnoDB表备份时，开启一个单独事务，备份所有表的一致性快照数据。
3） -R -E –triggers

[root@db01 ~]#  mysqldump -uroot -p123456 -S /tmp/mysql.sock  -A --master-data=2 --single-transaction -R -E --triggers   >/data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

4）–max-allowed-packet 最大的数据包大小给128M就可以，不够再加
1153 - Got a packet bigger than ‘max_allowed_packet’ bytes

[root@db01 ~]#  mysqldump -uroot -p456 -S /tmp/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=128M >/data/backup/full.sql