FreezeJ' Blog

Mysql备份

2019-04-28

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导出时,包括创建数据库语句-Bdatabases

恢复命令

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恢复。