MySQL 备份及主从恢复

发表于 更新于 上层目录 阅读次数：

环境信息

  • Centos 7
  • Mysql 5.7
  • Percona-XtraBackup-2.4.4

恢复方法1：mysqldump 主库锁表备份恢复

前提 ： 接受主库锁表操作，备份恢复过程中主库无法写入数据

从库停止slave进程

登录 mysql 从库，执行以下命令，停止 slave 进程

stop slave;

主库锁表

登录 mysql 主库，执行以下命令，对主库进行锁表操作

FLUSH TABLES WITH READ LOCK;

执行锁表操作后，不要退出 mysql 命令行，否则会自动解锁。

执行以下命令，查看主库锁表后的 binlog 位置信息

SHOW MASTER STATUS\G

备份主库数据库数据

执行以下命令备份数据库 db1

mysqldump -uroot -p --master-data=2 --databases db1 > db1.sql
  • --master-data=2 将日志名和位置写入到备份结果并注释，可以从备份文件中搜索 change master to 查看 binlog 文件名和 position

主库备份完成后，可以解锁：

UNLOCK TABLES;

从库恢复主库的备份数据

将主库的备份数据拷贝到从库，使用备份数据覆盖恢复从库

mysql -uroot -p db1 < db1.sql

从库恢复备份完成后，和主库建立主从连接

change master to master_host='1.1.1.1',master_user='repl',master_password='pswd',master_port=3306,master_log_file='mysql-bin.000196',master_log_pos=1037640668;
start slave

配置完成后，通过以下命令，查看主从状态

show slave status\G

主要关注以下项：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

恢复方法2：mysqldump 主库不锁表备份恢复

从库停止 slave 进程

登录 mysql 从库，执行以下命令，停止 slave 进程

stop slave;

备份主库数据库数据

mysqldump -uroot -p --master-data=2 --single-transaction -R --databases db01 > db01.sql
  • --master-data=2 : 实现将日志名和位置写入到备份结果并注释,db01.sql 中会包含当前日志的文件名以及位置
  • --routines, -R：导出存储过程和函数

从库导入备份

将主库的备份数据拷贝到从库，并进行恢复

mysql -uroot -p db01 < db01.sql

备份文件中查看binlog日志文件名和position

在备份文件(db01.sql)中搜索 CHANGE MASTER TO , 结果中可以获取到 binlog 日志文件名和 position

建立主从连接

根据上一步获取到的 binlog 文件名和 `position 信息，建立主从连接并启动

change master to master_host='1.1.1.1',master_user='repl',master_password='pswd',master_port=3306,master_log_file='mysql-bin.000196',master_log_pos=1037640668;
start slave

通过命令 show slave status\G 检查主从同步状态

恢复方法3： xbackup 整库备份恢复

xbackup安装

yum install -y libaio-devel.x86_64 bzip2
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz
tar -zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz
cp percona-xtrabackup-2.4.4-Linux-x86_64/bin/* /usr/bin

主库整库备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=PASSWD --port=3306 --socket=/var/lib/mysql/mysql.sock  [--databases  "db1 db2 "] ${backup_data_dir}

从库整库恢复

拷贝主库备份数据到从库,在从库上执行以下命令恢复数据

cd ${backup_data_dir}

innobackupex --defaults-file=/etc/my.cnf --apply-log --use-memory=20000M ${backup_data_dir}

cp -r ${backup_data_dir}/* ${mysql_base_dir}/data/

建立主从连接

备份数据中的 xtrabackup_binlog_info 文件包含了从库建立主从连接时需要的 binlog 信息，根据此文件中的信息，在从库执行命令，建立主从连接

change master to master_host='1.1.1.1',master_user='repl',master_password='pswd',master_port=3306,master_log_file='mysql-bin.000196',master_log_pos=1037640668;
start slave

通过命令 show slave status\G 检查主从同步状态