XtraBackup备份MySQL不锁库做主从

2018-06-29

Mysql扩展知识

MySQL做主从时候是需要主上锁表在把数据拷贝到从上的。为了让主不锁表（在线）把数据拷贝到从上，可以使用XtraBackup工具，让他把数据做在线备份再传输到从上还原数据。
XtraBackup工具主要是用来备份数据的当数据大到几百G时候使用MySQLdump会非常乏力，而使用XtraBackup来备份十分轻松，而且还支持全量，增量备份等。

XtraBackup优点
- 1.不需要锁表即可进行数据备份
- 2.备份完成快速、可靠
- 3.备份MySQL时不会增大服务器负载

我们这里以192.168.80.104为主 192.168.80.105为从来做主从配置

配置主

1.修改MySQL配置文件my.cnf

1	[root@localhost ~]# vim /etc/my.cnf

添加下面参数

1 2	server-id=1 log_bin=bak

2.重启MySQL并查看主的binlog

[root@localhost ~]# service mysqld restart

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bak.000001 |      120 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.安装XtraBackup工具

1 2	[root@localhost ~]# rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm [root@localhost ~]# yum install -y percona-xtrabackup

4.在MySQL中创建备份用户和复制用户并授予权限

[root@localhost ~]# mysql -uroot -p38003800

mysql> grant reload,lock tables,replication client on *.* to 'backup'@'192.168.80.%' identified by '38003800'; ##备份用户

mysql> grant replication slave on *.* to 'repl'@192.168.80.105 identified by '38003800'; ##主从复制用户

mysql> flush privileges;

4.建立备份目录

1	[root@localhost ~]# mkdir /back

5.备份MySQL的所有数据

1	[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --host=192.168.80.104 --port=3306 --user=backup --password='38003800' /back/

6.打包备份文件

1	[root@localhost ~]# tar -cvf back /back/2018-06-29_22-55-57/

配置从

1.把主上的备份文件拷贝过来

1
2
3

[root@localhost ~]# scp 192.168.80.104:/root/back back
root@192.168.80.104's password:
back                                                                           100%   14MB  38.6MB/s   00:00

2.解包备份文件

1	[root@localhost ~]# tar -xvf back -C /tmp/

3.停止MySQL的数据库并删除datadir（可以选择移走）

1 2	[root@localhost back]# service mysqld stop [root@localhost back]# rm -rf /data/*

4.安装XtraBackup工具

1 2	[root@localhost ~]# rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm [root@localhost ~]# yum install -y percona-xtrabackup

5.进入备份目录并初始化

1
2
3

[root@localhost ~]# cd /tmp/back/

[root@localhost back]# innobackupex --use-memory=512M --apply-log 2018-06-29_22-55-57/

6.恢复数据

1	[root@localhost back]# innobackupex --defaults-file=/etc/my.cnf --copy-back 2018-06-29_22-55-57/

7.更改dadir权限

1	[root@localhost back]# chown -R mysql:mysql /data/

8.修改MySQL配置文件my.cnf并启动MySQL

1	[root@localhost ~]# vim /etc/my.cnf

添加下面参数

1	server-id=2

1	[root@localhost back]# service mysqld start

9.进入MySQL进行主从连接

[root@localhost back]# mysql -uroot -p38003800

mysql> stop slave;

mysql> change master to master_host='192.168.80.104',master_user='repl',master_password='38003800',master_log_file='bak.000001',master_log_pos=120;

mysql> start slave;

10.查看从的状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.80.104
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bak.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 277
        Relay_Master_Log_File: bak.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

使用XtraBackup备份数据来做主从成功了

参考文章 http://seanlook.com/2015/12/14/mysql-replicas/