Mysql备份

使用mysqldump做逻辑备份

备份基本命令

mysqldump -u user -p DATABASES TABLE1 TABLE2... > backup.sql

选项说明

mysqldump 默认是锁表的，如果不需要锁表，请使用 skip-lock-tables
备份所有数据库使用 all-databases
恢复使用：mysql -uxxx -pxxx 要恢复的数据库名称 --one-database < 备份文件
排除数据表 ignore-table=dbname.tablename备份前刷新binlog日志flush-logs只导出数据结构no-data, -d导出时，包括创建数据库语句-B或databases

恢复命令

mysql -u user -p -e "source backup.sql" DATABASES

选项说明

• 进入数据库执行命令-e

通过binlog恢复

出现问题后先备份binlog，不要直接还原（会引入新binlog）

恢复命令的语法格式：(binlog里面包含了所有的数据库的日志)
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

如何查找pos(每个binlog都是独立的pos，对于多个binlog，可以使用时间恢复，但使用pos更精准)

1. 先查看配置，查找binlog文件位置：vim /etc/my.cnf
2. 查看当前binlog位置：show master status;
3. 根据特征SQL语句过滤binlog：mysql -e "show binlog events in 'mysql-bin.000003'\G;" | grep 'drop database xxxx' -A20 -B 30
4. 更细致的查询：show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];如：show binlog events IN 'mysql-bin.000007' FROM 592 LIMIT 1,10;

参考文章

补充：

使用命令mysqlbinlog --start-datetime="2023-7-19 17:50:00" --stop-position=1136 --database=mytest ./mysql-bin.000007 > /tmp/increat.sql导出的sql内容不是直接可读的语句，需要添加参数-vv

选择stop-position点应该是drop语句的position之前：

使用XtraBackup做物理备份

安装

1. 依赖：yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL rsync perl perl-Digest-MD5
2. 获取包：wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.13/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
3. 安装：rpm -ivh percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

全量备份

命令：

• innobackupex --user=XXX --password=XXX 全量备份目录 --no-timestamp

参数：

• 不使用时间戳 --no-timestamp（默认会自动在备份目录下生成时间戳文件夹，格式如：2019-02-22_10-55-47）

增量备份

命令：

• innobackupex --user=XXX --password=XXX --incremental-basedir 增量备份目录 --incremental 全量备份目录 --no-timestamp

备份恢复

命令：

service mysqld stop # 先停止mysql
mv /var/lib/mysql /var/lib/mysql.bak && mkdir /var/lib/mysql  # 备份原来的mysql目录(视my.cnf配置文件而定)
cd /usr/bin/  # 可能没有命令环境变量，需要进入目录使用命令
innobackupex --apply-log --redo-only /data/backup/2017-02-23/12/  # 先应用全量备份
innobackupex --apply-log --redo-only /data/backup/2017-02-23/12/ --incremental-dir=/data/backup/2017-02-23/13/  # 添加增量备份，顺序不能错。
innobackupex --apply-log --redo-only /data/backup/2017-02-23/12/ --incremental-dir=/data/backup/2017-02-23/14/  # 添加增量备份，顺序不能错。
innobackupex --apply-log /data/backup/2017-02-23/12/  # 最后使用这个整合所有备份
innobackupex  --copy-back /data/backup/2017-02-23/12/  # 复制回mysql数据目录
chown -R mysql.mysql /var/lib/mysql  # 修改数据库权限
/etc/init.d/mysqld start  # 开启Mysql服务

备注

• 恢复数据后mysql启动不了，提示Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist，使用如下命令就可以了：

  mysql_install_db
  

• 恢复后的mysql忘记密码，在配置文件中加入skip-grant-tables=1 跳过权限验证。
• 在恢复的mysql数据目录中，有xtrabackup_binlog_pos_innodb文件记录着binlog文件和开始节点，后续再利用binlog恢复。