2023年7月30日发(作者：)

MySQL定时备份（全量备份+增量备份）增量备份的优点是没有重复数据，备份量不⼤，时间短。但缺点也很明显，需要建⽴在上次完全备份及完全备份之后所有的增量才能恢复。MySQL没有提供直接的增量备份⽅法，但是可以通过mysql⼆进制⽇志间接实现增量备份。⼆进制⽇志对备份的意义如下：⼆进制⽇志保存了所有更新或者可能更新数据的操作⼆进制⽇志在启动MySQL服务器后开始记录，并在⽂件达到所设⼤⼩或者收到flush logs 命令后重新创建新的⽇志⽂件只需定时执⾏flush logs ⽅法重新创建新的⽇志，⽣成⼆进制⽂件序列，并及时把这些⽂件保存到⼀个安全的地⽅，即完成了⼀个时间段的增量备份。全量备份mysqldump --lock-all-tables --flush-logs --master-data=2 -u root -p test > backup_sunday_1_参数 --lock-all-tables对于InnoDB将替换为 --single-transaction。该选项在导出数据之前提交⼀个 BEGIN SQL语句，BEGIN 不会阻塞任何应⽤程序且能保证导出时数据库的⼀致性状态。它只适⽤于事务表，例如 InnoDB 和 BDB。本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出⼤表的话，应结合使⽤ --quick 选项。参数 --flush-logs，结束当前⽇志，⽣成并使⽤新⽇志⽂件参数 --master-data=2，该选项将会在输出SQL中记录下完全备份后新⽇志⽂件的名称，⽤于⽇后恢复时参考，例如输出的备份SQL⽂件中含有：CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002',MASTER_LOG_POS=106;参数 test，该处的test表⽰数据库test，如果想要将所有的数据库备份，可以换成参数 --all-databases参数 --databases 指定多个数据库参数 --quick或-q，该选项在导出⼤表时很有⽤，它强制 MySQLdump 从服务器查询取得记录直接输出⽽不是取得所有记录后将它们缓存到内存中。参数 --ignore-table，忽略某个数据表，如 --ignore-table 忽略数据库test⾥的user表更多mysqldump 参数，请参考⽹址全量备份脚本shell#!/bin/bash# mysql 数据库全量备份

# ⽤户名、密码、数据库名username="root"password="tencns152"dbName="goodthing"

beginTime=`date +"%Y年%m⽉%d⽇ %H:%M:%S"`# 备份⽬录bakDir=/home/mysql/backup# ⽇志⽂件logFile=/home/mysql/backup/# 备份⽂件nowDate=`date +%Y%m%d`dumpFile="${dbName}_${nowDate}.sql"gzDumpFile="${dbName}_${nowDate}."

cd $bakDir# 全量备份（对所有数据库备份，除了数据库goodthing⾥的village表）/usr/local/mysql/bin/mysqldump -u${username} -p${password} --quick --events --databases ${dbName} --ignore-table=e --ignore-table= --flush# 打包/bin/tar -zvcf $gzDumpFile $dumpFile/bin/rm $dumpFile

endTime=`date +"%Y年%m⽉%d⽇ %H:%M:%S"`echo 开始:$beginTime 结束:$endTime $gzDumpFile succ >> $logFile

# 删除所有增量备份cd $bakDir/daily/bin/rm -f *这⾥全量备份只备份了⼀个数据库，因为如果所有数据库都备份的话，⽂件太⼤了。这⾥的取舍我也不是很清楚，毕竟⾃⼰还在学习阶段，没有实际的操作经验。增量备份1. 检查log_bin是否开启进⼊mysql命令⾏，执⾏ show variables like '%log_bin%'mysql> show variables like '%log_bin%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| log_bin | OFF || log_bin_basename | || log_bin_index | || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+-------+6 rows in set (0.01 sec)如上所⽰，log_bin 未开启；如果log_bin开启，则跳过第2步，直接进⼊第3步2. 开启 log_bin，并重启mysql编辑 mysql 的配置⽂件 vim /etc/，在 mysqld 下⾯添加下⾯2条配置[mysqld]log-bin=/var/lib/mysql/mysql-binserver_id=152Tip1: ⼀定要加 server_id，否则会报错。⾄于server_id的值，随便设就可以。Tip2: log_bin 中间可以下划线_相连，也可以-减号相连。同理server_id也⼀样。重启mysqlservice mysqld restart再次在mysql命令⾏中执⾏ show variables like '%log_bin%'mysql> show variables like '%log_bin%';+---------------------------------+--------------------------------+| Variable_name | Value |+---------------------------------+--------------------------------+| log_bin | ON || log_bin_basename | /var/lib/mysql/mysql-bin || log_bin_index | /var/lib/mysql/ || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+--------------------------------+6 rows in set (0.01 sec)3. 备份进⼊mysql命令⾏，执⾏ show master status;mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 | 430 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)当前正在记录⽇志的⽂件名是 mysql-bin.000003⽐如当前数据库test的bk_user只有2条记录mysql> select * from _user;+----+------+------+------+| id | name | sex | age |+----+------+------+------+| 1 | ⼩明 | 男 | 25 || 2 | ⼩红 | ⼥ | 21 |+----+------+------+------+2 rows in set (0.00 sec)插⼊⼀条新的记录mysql> insert into _user(name, sex, age) values('⼩强', '男', 24);Query OK, 1 row affected (0.02 sec)mysql> select * from _user;+----+------+-----+-----+| id | name | sex | age |+----+------+-----+-----+| 1 | ⼩明 | 男 | 25 || 2 | ⼩红 | ⼥ | 21 || 5 | ⼩强 | 男 | 24 |+----+------+-----+-----+3 rows in set (0.03 sec)执⾏命令mysqladmin -uroot -p密码 flush-logs，⽣成并使⽤新的⽇志⽂件再次查看当前使⽤的⽇志⽂件，已经变为 mysql-bin.000004 了。mysql-bin.000003 则记录着刚才执⾏的 insert 语句的⽇志。mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000004 | 154 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)到这⾥，其实已经完成了增量备份了。恢复增量备份⾸先假装误删数据库记录mysql> delete from _user where id=4;Query OK, 1 row affected (0.01 sec)

mysql> select * from _user;+----+------+------+------+| id | name | sex | age |+----+------+------+------+| 1 | ⼩明 | 男 | 25 || 2 | ⼩红 | ⼥ | 21 |+----+------+------+------+2 rows in set (0.00 sec)从备份的⽇志⽂件mysql-bin.000003中恢复数据[root@centos56 ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p testEnter password:

ERROR 1032 (HY000) at line 36: Can't find record in 'bk_user'如果你也遇到这个问题的话，不妨修改 /etc/ 配置试试。我在server_id那⼀⾏下添加了 slave_skip_errors=1032 ，然后就执⾏成功了，不再报错。mysql> select * from _user;+----+------+------+------+| id | name | sex | age |+----+------+------+------+| 1 | ⼩明 | 男 | 25 || 2 | ⼩红 | ⼥ | 21 || 5 | ⼩强 | 男 | 24 |+----+------+------+------+3 rows in set (0.00 sec)增量备份的shell脚本#!/bin/bash

# 增量备份时复制mysql-bin.00000*的⽬标⽬录，提前⼿动创建这个⽬录BakDir=/home/mysql/backup/daily# ⽇志⽂件LogFile=/home/mysql/backup/

# mysql的数据⽬录BinDir=/var/lib/mysql-bin# mysql的index⽂件路径，放在数据⽬录下的BinFile=/var/lib/mysql-bin/

# 这个是⽤于产⽣新的mysql-bin.00000*⽂件/usr/local/mysql/bin/mysqladmin -uroot -ptencns152 flush-logs

Counter=`wc -l $BinFile | awk '{print $1}'`NextNum=0# 这个for循环⽤于⽐对$Counter,$NextNum这两个值来确定⽂件是不是存在或最新的for file in `cat $BinFile`do base=`basename $file` NextNum=`expr $NextNum + 1` if [ $NextNum -eq $Counter ] then echo $base skip! >> $LogFile else dest=$BakDir/$base #test -e⽤于检测⽬标⽂件是否存在，存在就写exist!到$LogFile去 if(test -e $dest) then echo $base exist! >> $LogFile else cp $BinDir/$base $BakDir echo $base copying >> $LogFile fi fidone

echo `date +"%Y年%m⽉%d⽇ %H:%M:%S"` $Next Bakup succ! >> $LogFile定时备份执⾏命令 crontab -e，添加如下配置# 每个星期⽇凌晨3:00执⾏完全备份脚本0 3 * * 0 /bin/bash -x /root/bash/ >/dev/null 2>&1

# 周⼀到周六凌晨3:00做增量备份0 3 * * 1-6 /bin/bash -x /root/bash/ >/dev/null 2>&1遇到的问题Can't connect to local MySQL server through socket '/tmp/'mysqladmin: connect to server at 'localhost' failederror: 'Can't connect to local MySQL server through socket '/tmp/' (2)'Check that mysqld is running and that the socket: '/tmp/' exists去修改mysql的配置⽂件，添加[mysqladmin]# 修改为相应的socksocket=/var/lib/mysql/执⾏mysqldump时遇到 Unknown table 'column_statistics' in information_schema (1109)[root@centos56 bash]# /usr/local/mysql/bin/mysqldump -uroot -ptencns152 --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction > /home/mysmysqldump: [Warning] Using a password on the command line interface can be ump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema如果使⽤MySQL 8.0+版本提供的命令⾏⼯具mysqldump来导出低于8.0版本的MySQL数据库到SQL⽂件，会出现Unknown table 'column_statistics' in information_schema的错误，因为早期版本的MySQL数据库的information_schema数据库中没有名为COLUMN_STATISTICS的数据表。解决问题的⽅法是，使⽤8.0以前版本MySQL附带的mysqldump⼯具，最好使⽤待备份的MySQL服务器版本对应版本号的mysqldump⼯具，mysqldump可以独⽴运⾏，并不依赖完整的MySQL安装包，⽐如在Windows中，可以直接从MySQL安装⽬录的bin⽬录中将复制到其他⽂件夹，甚⾄从⼀台电脑复制到另⼀台电脑，然后在CMD窗⼝中运⾏。当前使⽤是的MySQL 5.7.22。把5.7.20的 MYSQL_HOME/bin/mysqldump 替换掉 5.7.22的，接着就能顺利执⾏mysqldump了，也真是奇了怪了。