使用二进制热备份创建MySQL主从复制

机器说明

Master 190: 192.168.1.190 (开启binlog, server-id=1)
Slave 191: 192.168.1.191
Slave 192: 192.168.1.192
以上三台机器都安装了MySQL 5.5.29, Percona XtraBackup 2.06

在Master 190上创建Slave 191用于MySQL复制的账号

mysql> create user 'newrepl'@'192.168.1.191' identified by '123456';

mysql> grant replication slave on *.* to 'newrepl'@'192.168.1.191';

mysql> flush privileges;

将Master 190的MySQL数据直接远程热备份到Slave 191

[root@CentOS190 ~]# innobackupex --user=backup --password=123456 --parallel=4 --stream=tar ./ | ssh [email protected] "tar -ixf - -C /var/lib/mysql/data"

在Slave 191上应用备份,并对备份数据目录做相应权限设置

[root@centos191 ~]# innobackupex --apply-log --use-memory=4G /var/lib/mysql/data
[root@centos191 ~]# chown -R mysql:mysql /var/lib/mysql/data

Slave 191上停止MySQL,进行MySQL配置,然后启动MySQL

[root@centos191 mysql]# service mysql stop
[root@centos191 mysql]# scp [email protected]:/etc/my.cnf /etc/my.cnf

修改/etc/my.cnf:

...
[mysqld]
datadir             = /var/lib/mysql/data
server-id           = 2 # Master的server-id = 1
relay-log           = slave-relay-bin
relay-log-index     = slave-relay-bin.index
...

配置完后,启动MySQL:

[root@centos191 mysql]# service mysql start
Starting MySQL....                                         [  OK  ]

建立复制连接开始复制,并检查slave运行状态

查看热备份的binlog文件名和偏移位置信息:

[root@centos191 mysql]# cat /var/lib/mysql/data/xtrabackup_binlog_info 
master-bin.000005	107

建立复制连接并检查是否成功:

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.190',
    -> MASTER_USER='newrepl',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000005',
    -> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.96 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like 'Slave_running';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON    |
+---------------+-------+
1 row in set (0.00 sec)

在Master 190上创建Slave 192用于MySQL复制的账号

[root@centos191 mysql]# mysql -uroot -p -h192.168.1.190
...
mysql> create user 'newrepl'@'192.168.1.192' identified by '123456';

mysql> grant replication slave on *.* to 'newrepl'@'192.168.1.192';

mysql> flush privileges;

将Slave 191的MySQL数据直接远程热备份到Slave 192

# innobackupex --user=backup --password=123456 --parallel=4 --slave-info --safe-slave-backup --stream=tar ./ | ssh [email protected] "tar -ixf - -C /var/lib/mysql/data"

注:这里备份使用--slave-info可以将Master的binary log的文件名和偏移位置记录到xtrabackup_slave_info文件中.而使用--safe-slave-backup会暂停Slave的SQL线程直到备份结束,这样可以确保一致性的复制状态.

在新Slave 192上应用备份,并对备份数据目录做相应权限设置

[root@centos192 ~]# innobackupex –apply-log –use-memory=4G /var/lib/mysql/data
[root@centos192 ~]# chown -R mysql:mysql /var/lib/mysql/data

新Slave 192上停止MySQL,进行MySQL配置,然后启动MySQL

[root@centos192 mysql]# service mysql stop
[root@centos192 mysql]# scp [email protected]:/etc/my.cnf /etc/my.cnf

修改/etc/my.cnf:

...
[mysqld]
server-id  = 3
skip-slave-start
...

启动MySQL:

[root@centos192 mysql]# service mysql start
Starting MySQL...                                          [  OK  ]

建立复制连接开始复制,并检查slave运行状态

查看Slave热备份的binlog文件名和偏移位置信息:

[root@centos192 mysql]# cat /var/lib/mysql/data/xtrabackup_slave_info 
CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=614672

建立复制连接并检查是否成功:

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.190',
    -> MASTER_USER='newrepl',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000005',
    -> MASTER_LOG_POS=614672;
Query OK, 0 rows affected (0.33 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
   ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
  ... 
        Seconds_Behind_Master: 0
  ...

修改/etc/my.cnf,注释掉”skip-slave-start”,然后重启MySQL即可.

# sed -i 's/skip-slave-start/#skip-slave-start/g' /etc/my.cnf
# service mysql restart