[TOC] ### 1. mysqldump 参考：http://www.cnblogs.com/chenmh/p/5300370.html * * * * * **可以直接被分成压缩文件，省空间** ~~~ mysqldump --single-transaction -hlocalhost --all-databases --triggers --routines --events -P3306 -uroot -pxxxx |gzip >test.tar.gz # 备份 gunzip < test.tar.gz |mysql -hlocalhost -uroot -pxxxxx # 还原 ~~~ > * 在日常维护工作当中经常会需要对数据进行导出操作，而mysqldump是导出数据过程中使用非常频繁的一个工具； 1. 导出所有数据库，该命令会导出包括系统数据库在内的所有数据库 ~~~ mysqldump -uroot -proot --all-databases >/tmp/all.sql ~~~ 2. 导出db1、db2两个数据库的所有数据 ~~~ mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql ~~~ 3. 导出db1中的a1、a2表 > 注意导出指定表只能针对一个数据库进行导出，且导出的内容中和导出数据库也不一样，导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据 ~~~ mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql ~~~ 4. 条件导出，导出db1表a1中id=1的数据 > 如果多个表的条件相同可以一次性导出多个表字段是整形 ~~~ mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' >/tmp/a1.sql ~~~ 5. 生成新的binlog文件,-F > 有时候会希望导出数据之后生成一个新的binlog文件,只需要加上-F参数即可 ~~~ mysqldump -uroot -proot --databases db1 -F >/tmp/db1.sql ~~~ 6. 只导出表结构不导出数据，--no-data ~~~ mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql ~~~ 7. 跨服务器导出导入数据 > 将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错, 加上-C参数可以启用压缩传递 ~~~ # 全量复制 mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2 # 增量复制 mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test ~~~ 8. 将主库的binlog位置和文件名追加到导出数据的文件中，--dump-slave > 注意：--dump-slave命令如果当前服务器是从服务器那么使用该命令会执行stop slave来获取master binlog的文件和位置，等备份完后会自动执行start slave启动从服务器。但是如果是大的数据量备份会给从和主的延时变的更大，使用--dump-slave获取到的只是当前的从服务器的数据执行到的主的binglog的位置是（relay_mater_log_file,exec_master_log_pos),而不是主服务器当前的binlog执行的位置，主要是取决于主从的数据延时。 > 该参数在在从服务器上执行，相当于执行show slave status。当设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释,该选项将会打开--lock-all-tables，除非--single-transaction被指定。在执行完后会自动关闭--lock-tables选项。--dump-slave默认是1 ~~~ mysqldump -uroot -proot --dump-slave=1 --databases db1 >/tmp/db1.sql ~~~ 9. 将当前服务器的binlog的位置和文件名追加到输出文件，--master-data > 该参数和--dump-slave方法一样，只是它是记录的是当前服务器的binlog，相当于执行show master status，状态（file,position)的值。注意：--master-data不会停止当前服务器的主从服务 10. --opt 等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用. mysqldump -uroot -p --host=localhost --all-databases --opt 11. 保证导出的一致性状态--single-transaction > 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎（它不显示加锁通过判断版本来对比数据），仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。 > --quick, -q > 不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。 12. --lock-tables, -l > 开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。 > 请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。 13. 导出存储过程和自定义函数--routines, -R ~~~ mysqldump -uroot -p --host=localhost --all-databases --routines ~~~ 14. 压缩备份 ~~~ mysqldump -uroot -proot --databases abc 2>/dev/null |gzip >/abc.sql.gz ~~~ 压缩备份 还原 ~~~ gunzip -c abc.sql.gz |mysql -uroot -proot abc ~~~ > 1 mysqldump导出数据主要有两种控制：一种是导出的全过程都加锁 lock-all-tables, 另一种则是不加。前者会在导出开始时执行 FLUSH TABLES WITH READ LOCK; 也就是加全局读锁，会阻塞其它写操作，以保证导出是一致性的；因此只有在导出测试数据时或导出时没有业务连接操作时可不加 lock-all-tables . > 至于说一致性导出的另一种方式 single-transaction, 则是有适用范围的，见下边。 > 2 single-transaction 选项和 lock-all-tables 选项是二选一的，前者是在导出开始时设置事务隔离状态并使用一致性快照开始事务,而后马上unlock tables，然后执行导出,导出过程不影响其它事务或业务连接，但只支持类似innodb多版本特性的引擎，因为必须保证即使导出期间其它操作(事务点t2)改变了数据，而导出时仍能取出导出开始的事务点t1时的数据。而lock-all-tables则一开始就 FLUSH TABLES WITH READ LOCK; 加全局读锁，直到dump完毕。 > -- 关于一致性快照，简单地说，就是通过回滚段能记录不同的事务点的各版本数据 > -- single-transaction 的流程如下： > SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ > START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ > SHOW MASTER STATUS -- 这一步就是取出 binlog index and position > UNLOCK TABLES > ...dump... > 3 master_data 选项开启时默认会打开lock-all-tables，因此同时实现了两个功能，一个是加锁，一个是取得log信息。 > master_data取1和取2的区别，只是后者把 change master ... 命令注释起来了，没多大实际区别； > 4 当master_data和 single_transaction 同时使用时，先加全局读锁，然后设置事务一致性和使用一致性快照开始事务，然后马上就取消锁，然后执行导出。过程如下 > FLUSH TABLES WITH READ LOCK > SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ > START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ > SHOW MASTER STATUS -- 这一步就是取出 binlog index and position > UNLOCK TABLES > ...dump... > 5 总结，了解了这些选项作用后，使用起来就明确了. > 如果需要binlog信息则使用 master_data; > 如果不想阻塞同时表是innodb引擎可使用 single_transaction 取得一致性快照(取出的数据是导出开始时刻事务点的状态) > 如果表不支持多版本特性，则只能使用 lock-all-tables 阻塞方式来保证一致性的导出数据。 > 当然，如果能保证导出期间没有任何写操作，可不加或关闭 lock-all-tables > 追问 > ok,谢谢,我已经找到答案了... > 做一下测试..然后看一下日志..我就大概明白了...一般情况下..两个参数用在一起是最好的...即可以保证数据的一致性,又可以记录日志点....对于增量备份,主从搭建都很好... > 不过,还是谢谢你的详细解答... * * * * * ### 2. 新建定时任务 1. 备份脚本 ~~~ #!/bin/bash set -x source $1 DATE=`date +'%Y-%m-%d-%H:%M'` #p2="$3" result=`echo "$3" |grep "day"` if [ "$result" != "" ];then date=`date -d $3 +%Y%m%d` else date=`date -d $3 +%Y%m` fi echo "date:$date" backUpOneParam(){ mysqldump --host=$FROM_IP -u$FROM_USER -p$FROM_PASSWD --databases $FROM_DATABASE --table $1 > /tmp/$DATE-$1.sql \ && mysql --host=$TO_IP -u$TO_USER -p$TO_PASSWD -D$TO_DATABASE < /tmp/$DATE-$1.sql } backUpTwoParam(){ mysqldump --host=$FROM_IP -u$FROM_USER -p$FROM_PASSWD --databases $FROM_DATABASE --table "$1_$date" > /tmp/$DATE-$1_$date.sql \ && mysql --host=$TO_IP -u$TO_USER -p$TO_PASSWD -D$TO_DATABASE < /tmp/$DATE-$1_$date.sql } if [ $# -eq 2 ];then backUpOneParam $2 elif [ $# -eq 3 ];then backUpTwoParam $2 $3 else echo "USAGE: $0 <config_file> <TABLENAME> <interval>day|month" echo "e.g.: $0 <transfer.properties> device_training_record -1day" exit 1; fi ~~~ 2. 配置文件 ~~~ #源数据库 FROM_IP='192.168.2.21' FROM_USER='timing' FROM_PASSWD='**' FROM_DATABASE='****' #目标数据库 TO_IP='192.168.2.21' TO_USER='manage' TO_PASSWD='*****' TO_DATABASE='managedb' #定时任务 #*/1 * * * * /home/tuna/shelles/mysql/backup.sh /home/tuna/shelles/mysql/backup.properties #测试 #./transfer.sh ./transfer.properties device_training_record +0day ~~~ 2. 建立定时任务 * source或者点去加载配置文件，会读取不到配置！！！！！ * * * * * ### 3. 完全卸载mysql 1.删除客户端服务器 sudo apt-get autoremove --purge MySQL-server-5.6(5.6是你的版本号） sudo apt-get autoremove --purge mysql-client-5.6(5.6是你的版本号） sudo apt-get remove mysql-common (非常重要) 2.清理残留数据 dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P 3.清除数据库旧数据 mysql的安装文件夹删除 4.重新安装 sudo apt-get install mysql-server-5.6(5.6是你的版本号） * * * * * ### 4. mysql 备份、还原 > mysqldump+binlo：周一到周六增量备份，周日全量备份 #### 4.1 开启binlog 1. 修改mysqld.cnf ~~~ root@docker02:/etc/mysql/mysql.conf.d# pwd /etc/mysql/mysql.conf.d root@docker02:/etc/mysql/mysql.conf.d# vim mysqld.cnf # 做以下修改 log-bin=mysql-bin # 这个随便起名 ~~~ 2. 查看binlog信息  3. 查看binlog文件（在mysql的datadir下，从mysql.cnf获取）  多了两个这个文件 mysql-bin.000001 没mysql重启一次，会增加一个这个文件，如mysql-bin.000002 4. 查看binlog文件 SHOW BINLOG EVENTS IN 'mysql-bin.000005' \G  #### 4.2 增量与全量备份脚本 1. 全量备份脚本 ~~~ #!/bin/bash set -x set -e D_USER=root D_PASSWD=tuna BAK_DIR=/data/mysql/backup/full LOGFILE=/data/mysql/backup/bak.log OLDBINLOG=/data/mysql/backup/daily deleteLogs(){ if [ -d $OLDBINLOG ];then rm -f $OLDBINLOG/* fi } if [ ! -d $BAKDIR ];then mkdir -p $BAKDIR elif [ ! -f $LOGFILE ];then touch $LOGFILE fi DATE=`date +'%Y%m%d-%H:%M'` BEGIN=`date +"%Y年%m月%d日 %H:%M"` mysqldump -uroot -ptuna --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction > $BAK_DIR/$DATE.sql deleteLogs LAST=`date +"%Y年%m月%d日 %H:%M:%S"` echo "开始:$BEGIN 结束:$LAST $DATE.sql.tgz succ" >> $LOGFILE ~~~ 2. 增量备份脚本 ~~~ #!/bin/bash BakDir=/data/mysql/backup/daily #增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录 BinDir=/var/lib/mysql #mysql的数据目录 LogFile=/data/mysql/backup/bak.log BinFile=$BinDir/mysql-bin.index #mysql的index文件路径，放在数据目录下的 set -x set -e NextNum=0 # flush the log to the disk and generate a new mysql-bin.00000* file if [ ! -d $BakDir ];then mkdir -p $BakDir fi mysqladmin -uroot -ptuna flush-logs Counter=`wc -l $BinFile |awk '{print $1}'` for file in `cat $BinFile` do base=`basename $file` #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./ NextNum=`expr $NextNum + 1` if [ $NextNum -eq $Counter ] then echo $base skip! >> $LogFile else dest=$BakDir/$base if(test -e $dest) #test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去 then echo $base exist! >> $LogFile else `mysqlbinlog $BinDir/$base >$BakDir/$base` echo $base copying >> $LogFile fi fi done echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile ~~~ ### 5. xtrabackup ~~~ # 全量复制 innobackupex --user=root --password=tuna /data/mysql/backup/full/ # 在上一次全量或者增量的基础上进行增量复制 innobackupex --user=root --password=tuna --incremental /data/mysql/backup/incr/ --incremental-basedir=/data/mysql/backup/full/2017-08-14_10-54-45 ~~~ 创建好的完整备份不能直接用来还原数据库，需要对数据做一些准备工作，其中包括一些已经提交的事务的重放，未提交事务的回滚。 –apply-log 准备还原备份的选项 –use-memory=4G 设置准备还原数据时使用的内存，可以提高准备所花费的时间 增量备份的恢复需要有3个步骤 恢复完全备份 恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only参数，到最后一次增量备份要去掉--redo-only) 对整体的完全备份进行恢复，回滚未提交的数据 ~~~ # 准备一个全备 innobackupex --apply-log --redo-only /data/mysql/backup/full/2017-08-14_10-54-45 # 将增量应用到完全备份，注意最后一个增量不加 --redo-only 参数了，之前的增量一定要加 # 增量1 innobackupex --apply-log --redo-only /data/mysql/backup/full/2017-08-14_17-05-53 --incremental-dir=/data/mysql/backup/incr/2017-08-14_17-06-54 # 增量2，可不加--read-only innobackupex --apply-log --redo-only /data/mysql/backup/full/2017-08-14_17-05-53 --incremental-dir=/data/mysql/backup/incr/2017-08-14_17-06-54 # 把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据## innobackupex --apply-log /data/mysql/backup/full/2017-08-14_10-54-45/ innobackupex --copy-back --rsync /data/mysql/backup/full/2017-08-14_10-54-45 ~~~ * 加上时间点还原具体的数据，我们通常也要备份binlog，在最后一次增量备份的目录中，xtrabackup_binlog_info文件记载了最后一次增量备份在binlog的开启位置，从这个位置开始还原全部数据。  ~~~ # 从mysql-bin.000001 的552位置开始还原 mysqlbinlog /tmp/mysql-bin.000001 --start-position=552 | mysql -uroot -ptuna ~~~ * 改数据权限 ~~~ cd /var/lib/mysql chown -R mysql.mysql * service mysql start ~~~ #### 6. 问题 问题1. ~~~ ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ~~~ 解决办法： ~~~ service mysql stop service mysql start ~~~