搭建环境说明
机器说明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的二进制热备份建立新slave
在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 root@192.168.1.191 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/dataslave 191上停止mysql,进行mysql配置,然后启动mysql[root@centos191 mysql]# service mysql stop[root@centos191 mysql]# scp root@192.168.1.190:/etc/my.cnf /etc/my.cnf修改/etc/my.cnf:
...[mysqld]datadir= /var/lib/mysql/dataserver-id= 2 # master的server-id = 1relay-log= slave-relay-binrelay-log-index= slave-relay-bin.index...配置完后,启动mysql:
[root@centos191 mysql]# service mysql startstarting 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)基于slave的二进制热备份建立新slave
在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 root@192.168.1.192 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 root@192.168.1.191:/etc/my.cnf /etc/my.cnf修改/etc/my.cnf:
...[mysqld]server-id = 3skip-slave-start...启动mysql:
[root@centos192 mysql]# service mysql startstarting 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: yesslave_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推荐阅读：
ubuntu下nginx做负载实现高性能web服务器5---mysql主主同步
生产环境mysql主主同步主键冲突处理
mysql主从失败 错误got fatal error 1236
mysql主从复制，，单台服务器上实施