MySQL学习笔记mysqldump是一个逻辑备份的客户端工具
客户端通用参数
-u -p -S -h -P 本地备份: mysqldump -uroot -p -S /tmp/mysql.sock 远程备份: mysqldump -uroot -p -h 10.0.0.51 -P3306
备份专用基本参数
-A 全备参数
mysqldump -uroot -p -A >/data/backup/full.sql time 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-databases --triggers --routines --events. 扩展：测试机器IO dd if=/dev/zero of=/tmp/bigfile bs=512 count=1024000 50G数据备份时间大约在15-30min为合理时间
-B 备份多个单库
说明：生产中需要备份，生产相关的库和MySQL库 mysqldump -uroot -p123 -S /tmp/mysql.sock -B oldboy world >/data/backup/full.sql
备份单个或多个表
world数据库下的city,country表 mysqldump -uroot -p world city country >/backup/bak1.sql 以上备份恢复时:必须库事先存在,并且use才能source恢复
备份库表写法与区别
mysqldump -uroot -p -B world>/data/backup/bak.sql mysqldump -uroot -p world>/data/backup/bak1.sql vimdiff /data/backup/bak.sql /data/backup/bak1.sql 可以看出上面带-B是备份整个库，不带-B是备份库下面的所有表
对整个数据库下除了sys,performance,information，进行其他数据库下的所有表进行单独备份
select concat("mysqldump -uroot -p123 -S /tmp/mysql.sock ",table_schema," ",table_name," >/data
/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema
not in ('sys','information_schema','performance_schema') into outfile '/tmp/mysqldump.sh';
sh /tmp/mysqldump.sh
高级参数应用
特殊参数使用（必须要加）
-R（routines） 备份存储过程及函数 --triggers 备份触发器 -E(events) 备份事件 mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql
-F 在备份开始时,刷新一个新binlog日志
mysqldump -uroot -p -A -R --triggers -F >/bak/full.sql
--master-data=2
以注释的形式,保存备份开始时间点的binlog的状态信息 mysqldump -uroot -p -A -R --triggers --master-data=2 >/back/world.sql [root@db01 ~]# grep 'CHANGE' /backup/world.sql ###-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194; 功能： (1)在备份时，会自动记录，二进制日志文件名和位置号 0 默认值 1 以change master to命令形式，可以用作主从复制 2 以注释的形式记录，备份时刻的文件名+postion号 (2)自动锁表 (3)如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是实现快照备份。
--single-transaction
innodb 存储引擎开启热备(快照备份)功能 master-data可以自动加锁 (1)在不加--single-transaction ，启动所有表的温备份，所有表都锁定 (2)加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能
--set-gtid-purged=auto(on,off)
auto,on为默认值，可不加。 使用场景: 1. --set-gtid-purged=OFF,可以使用在日常备份参数中.解决备份时的警告 mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql 2. auto，on:在构建主从复制环境时需要的参数配置 mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql
--max-allowed-packet=#M
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF
--max-allowed-packet=256M >/data/backup/full.sql
--max-allowed-packet=# 服务端接受的最大的包的的大小
备份必加参数
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
实现所有表的单独备份
提示：
information_schema.tables
mysqldump -uroot -p123 world city >/backup/world_city.sql
select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2
--single-transaction --set-gtid-purged=0 -R -E --triggers>/backup/",table_schema,"_",
table_name,".sql") from information_schema.tables where table_schema not in ('sys',
'information_schema','performance_schema');
备份时优化参数
(1) max_allowed_packet 最大的数据包大小 mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 max_allowed_packet=128M --single-transaction|gzip > /backup/full_$(date +%F).sql.gz (2) 增加key_buffer_size (临时表有关) (3) 分库分表并发备份 (作业) (4) 架构分离,分别备份 (架构拆分,分布式备份)
mysqldump全库备份过程中怎样加锁？加了什么锁？
对于非innodb表（FTWRL）全局锁，对于mysql库下系统表备份的时候，会加global read lock