-+-------+
| id | name |
+----+-------+
| 1 | andy |
| 11 | taoYe |
+----+-------+
2 rows in set (0.00 sec)
mysql> insert into mm_repl(name) values("andy"),("taoYe");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from mm_repl;
+----+-------+
| id | name |
+----+-------+
| 1 | andy |
| 11 | taoYe |
| 12 | andy |
| 22 | taoYe |
+----+-------+
4 rows in set (0.00 sec)
-- 新从库检查同步复制
mysql> select * from mm_repl;
+----+-------+
| id | name |
+----+-------+
| 1 | andy |
| 11 | taoYe |
| 12 | andy |
| 22 | taoYe |
+----+-------+
4 rows in set (0.00 sec) >主主同步测试成功
##################################################
#至此双主复制已经配置完成，下面配置主A到从C的复制。#
##################################################
主1从1搭建：
1、 停止从库mysql
[root@mysql01 ~]# service mysql stop
[root@mysql01 ~]# netstat -nltp|grep mysql|grep 3606
2、 从库参数调整
-- 调整 my.cnf 参数
[root@mysql01 ~]# cat /etc/my.cnf
[root@mysql03 mysql]# cat /etc/my.cnf
[client]
password = oracle
port = 3306
socket = /data/mysql/mysql.sock
[mysqld]
server-id=26
port = 3306
socket = /data/mysql/mysql.sock
character_set_server = utf8
character_set_client = utf8
collation-server=utf8_general_ci
lower_case_table_names = 1
max_connections = 1000
datadir = /data/mysql
log_bin = /data/mysql/binarylog/binlog
log_bin_index = /data/mysql/binarylog/binlog
relay-log = /data/mysql/relaylog/relay
relay-log-index = /data/mysql/relaylog/relay
relay_log_purge = on
read-only = 1
[mysql]
default-character-set = utf8
说明：从库必须配置的参数
server-id、log_bin、relay-log、read-only
3、 主库备份
-- 主库备份目录
[root@mysql02 full]# pwd
/xtrabackup/full
-- 主库 innobackupex 备份
[root@mysql02 ~]# innobackupex --user=root --password=oracle --port=3606 /xtrabackup/full/
170611 23:55:25 Backup created in directory '/xtrabackup/full/2017-06-11_23-55-20/'
MySQL binlog position: filename 'binlog.000017', position '980'
....
170611 23:55:25 completed OK!
-- 查看备份 binlog编号 与 截止 position
[root@mysql02 2017-06-11_23-55-20]# cat xtrabackup_binlog_info
binlog.000017 980
4、 从库创建与主库相同的备份目录
[root@mysql01 ~]# mkdir -p /xtrabackup/full
[root@mysql01 ~]# chown -R mysql:mysql /xtrabackup/full/
5、 主库将备份 scp 到从库
[root@mysql02 full]# pwd
/xtrabackup/full
[root@mysql02 full]# scp -r 2017-06-11_23-55-20 10.219.24.26:/xtrabackup/full
6、 从库查看scp过来的备份
[root@mysql03 ~]# cd /xtrabackup/full/2017-06-11_23-55-20/
[root@mysql03 2017-06-11_23-55-20]# ll
total 12328
-rw-r-----. 1 root root 419 Jun 11 23:57 backup-my.cnf
-rw-r-----. 1 root root 12582912 Jun 11 23:57 ibdata1
drwxr-x---. 2 root root 4096 Jun 11 23:57 mm_repl
drwxr-x---. 2 root root 4096 Jun 11 23:57 mysql
drwxr-x---. 2 root root 4096 Jun 11 23:57 performance_schema
drwxr-x---. 2 root root 4096 Jun 11 23:57 repl
drwxr-x---. 2 root root 4096 Jun 11 23:57 test
-rw-r-----. 1 root root 18 Jun 11 23:57 xtrabackup_binlog_info
-rw-r-----. 1 root root 113 Jun 11 23:57 xtrabackup_checkpoints
-rw-r-----. 1 root root 482 Jun 11 23:57 xtrabackup_info
-rw-r-----. 1 root root 2560 Jun 11 23:57 xtrabackup_logfile
7、 主库创建同步用户
mysql> GRANT replication slave ON *.* TO 'slave26'@'%' IDENTIFIED BY 'oracle';
Query OK, 0 rows affected (0.05 sec)
8、 从库恢复主库数据
-- 从库将原有datadir文件夹重命名到新位置，并创建原文件夹
[root@mysql01 ~]# mv /data/mysql /data/mysqlbak
[root@mysql01 ~]# mkdir -p /data/mysql
-- innobackupex apply-log
[root@mysql01 ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log --user=oracle \
--password=oracle --port=3606 /xtrabackup/fu