笔记：Mysql数据库备份

计划任务

21 13 * * * /home/mysql/dayback.sh >> /home/mysql/dayback.log 2>&1

实例1

#!/bin/bash


#0是备份所有库1是备份部分数据库
FLAG='0'
databases=(db1 db2 db3 db4 mysql)

basepath='/data/mysqlbackup/'

if [ ! -d "$basepath" ]; then
  
   mkdir -p "$basepath"

fi

if [ $FLAG -eq 0 ]; then
mysqldump -uroot --master-data=2  -R --single-transaction -A -phello  > $basepath-$(date +%Y%m%d).sql
tar zPcf $basepath-$(date +%Y%m%d).sql.tar.gz  $basepath-$(date +%Y%m%d).sql
find $basepath  -mtime +3 -name "*.sql.tar.gz" -exec rm -rf {} \;
 else

for db in ${databases[*]}

 do 

mysqldump -uroot -p*******  --databases $db > $basepath$db-$(date +%Y%m%d).sql

    tar zPcf $basepath-$(date +%Y%m%d).sql.tar.gz  $basepath-$(date +%Y%m%d).sql

    find $basepath  -mtime +3 -name "*.sql.tar.gz" -exec rm -rf {} \;

 done


fi
rm -rf $basepath/*.sql
scp -r /data/mysqlbackup/*.gz 1.1.1.1:/home/mysqlback/
rm -rf $basepath/*.gz

备份参数

--master-data指定为2指的是会在备份文件中生成CHANGE MASTER的注释。具体在本例中，指的是
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000049', MASTER_LOG_POS=587;
如果该值设置为1，则生成的是CHANGE MASTER的命令，而不是注释。
-R 备份存储过程与函数
--single-transaction 获取InnoDB表的一致性备份。
-A 相当于--all-databases。

实例2

配置scp免密
源主机生成公秘钥：ssh-keygen -t rsa
公钥复制到目标主机：ssh-copy-id user@目标主机IP


#!/bin/bash

mysqldump -uroot --master-data=2  -R --single-transaction -A -phello  > /mysqlbak/baktmp.sql
tar zPcf /mysqlbak/$(date +%Y%m%d).sql.tar.gz  /mysqlbak/baktmp.sql
rm -rf /mysqlbak/baktmp.sql
scp -r /mysqlbak/*.gz 1.1.1.1:/mysql_backup/1111/
rm -rf /mysqlbak/*.gz


find /mysql_backup/1111/  -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;

mysqldump备份过程

1.调用FWRL（flush tables with read lock）,全局禁止读写
2.开启快照读，获取此期间的快照（仅仅对innodb起作用）
3.备份非innodb表数据（*.frm,*.myi,*.myd等）
4.非innodb表备份完毕之后，释放FTWRL
5.逐一备份innodb表数据
6.备份完成

图解

1. mysqldump的本质是通过select * from tab来获取表的数据的。
2. START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */必须放到FLUSH TABLES WITH READ LOCK和UNLOCK TABLES之间，放到之前会造成START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */和FLUSH TABLES WITH READ LOCK之间执行的DML语句丢失，放到之后，会造成从库重复插入数据。
3. mysqldump只适合放到业务低峰期做，如果备份的过程中数据操作很频繁，会造成Undo表空间越来越大，undo表空间默认是放到共享表空间中的，而ibdata的特性是一旦增大，就不会收缩。
4. mysqldump的效率还是比较低下，START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */只能等到所有表备份完后才结束，其实效率比较高的做法是备份完一张表就提交一次，这样可尽快释放Undo表空间快照占用的空间。但这样做，就无法实现对所有表的一致性备份。
5. 为什么备份完成后没有commit操作
  /*
    No reason to explicitely COMMIT the transaction, neither to explicitely
    UNLOCK TABLES: these will be automatically be done by the server when we
    disconnect now. Saves some code here, some network trips, adds nothing to
    server.
  */

备份日志分析

第一步：
  FLUSH /*!40101 LOCAL */ TABLES
# 这里是刷新表


第二步：
  FLUSH TABLES WITH READ LOCK
# 因为开启了--master-data=2，这时就需要flush tables with read lock锁住全库，
  记录当时的master_log_file和master_log_pos点
  这里有一个疑问？
  执行flush tables操作，并加一个全局读锁，那么以上两个命令貌似是重复的，
  为什么不在第一次执行flush tables操作的时候加上锁呢？
  简而言之，是为了避免较长的事务操作造成FLUSH TABLES WITH READ LOCK操作迟迟得不到
  锁，但同时又阻塞了其它客户端操作。
  
  
第三步：
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
#  --single-transaction参数的作用，设置事务的隔离级别为可重复读，
  即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，
  也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，
  对该dump线程的数据并无影响，然而这个还不够，还需要看下一条


第四步：
  START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
# 获取当前数据库的快照，这个是由mysqldump中--single-transaction决定的。
# WITH CONSISTENT SNAPSHOT能够保证在事务开启的时候，第一次查询的结果就是
  事务开始时的数据A，即使这时其他线程将其数据修改为B，查的结果依然是A。简而言之，就是开启事务并对所有表执行了一次SELECT操作，这样可保证备份时，
  在任意时间点执行select * from table得到的数据和
  执行START TRANSACTION WITH CONSISTENT SNAPSHOT时的数据一致。
 【注意】，WITH CONSISTENT SNAPSHOT只在RR隔离级别下有效。

第五步：
  SHOW MASTER STATUS
# 这个是由--master-data决定的，记录了开始备份时，binlog的状态信息，
  包括MASTER_LOG_FILE和MASTER_LOG_POS

这里需要特别区分一下master-data和dump-slave
master-data:
--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；
dump-slave
--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，
  记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；
--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，
  记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；
 
第六步：
  UNLOCK TABLES
# 释放锁。