1.grants
GRANT REPLICATION SLAVE,FILE ON *.* TO 'repl1'@'192.168.56.221' IDENTIFIED
BY '123456';
FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE,FILE ON *.* TO 'repl2'@'192.168.56.77' IDENTIFIED
BY '123456';
FLUSH PRIVILEGES;
2.
(56.77)
log-bin=mysql-bin #slave会基于此log-bin来做replication
server-id=1 #master的标示
binlog-do-db=tongchao
binlog-ignore-db=mysql
replicate-do-db=tongchao
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
#user = mysql #未知
#log-bin=mysql-bin #在从服务器上启动日志记录，不是必须，但是官方建议
#server-id= 1 #服务器编号,唯一
#binlog-do-db=asteriskcdrdb #要同步的数据库
#binlog-ignore-db=mysql #不同步的数据?
#replicate-do-db=asteriskcdrdb #复制的数据库
#replicate-ignore-db=mysql #未知
#log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。
#slave-skip-errors=all #
#sync_binlog=1 #同步时钟
#auto_increment_increment=2 #
#auto_increment_offset=1 #自增涨增量
(56.221)
log-bin=mysql-bin
server-id= 2
binlog-do-db=tongchao
binlog-ignore-db=mysql
replicate-do-db=tongchao
replicate-ignore-db=mysql
log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
#sync_binlog=1
#auto_increment_increment=2
#auto_increment_offset=2
3.重启mysql
4.
进入MYSQL的SHELL。
flush tables with read lock
show master status
56.77
*************************** 1. row ***************************
File: mysql-bin.000012
Position: 106
Binlog_Do_DB: tongchao
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
56.221
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 98
Binlog_Do_DB: tongchao
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
5.在各自机器上执行CHANGE MASTER TO命令
56.77
change master to
master_host='192.168.56.221',
master_user='repl2',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=98;
start slave;
56.221
change master to
master_host='192.168.56.77',
master_user='repl1',
master_password='123456',
master_log_file='mysql-bin.000012',
master_log_pos=106;
start slave;
6、查看各自机器上的IO进程和 SLAVE进程是否都开启。
show processlist\G
7、释放掉各自的锁，然后进行插数据测试。
unlock tables;
MYSQL 的 MASTER到MASTER的主主循环同步
环境:
A 192.168.104.2
B 192.168.104.3
A
grant replication slave,file on *.* to ‘repl1′@’192.168.104.3′ identified by ’123456′;
FLUSH PRIVILEGES ;
B
GRANT REPLICATION SLAVE , FILE ON * . * TO ‘repl2′@’192.168.104.2′ IDENTIFIED BY ’123456′;
FLUSH PRIVILEGES ;
关闭两台服务器的msyql服务
mysqladmin -u root -p shutdown
A：
user = mysql #未知
log-bin=mysql-bin #在从服务器上启动日志记录，不是必须，但是官方建议
server-id= 1 #服务器编号,唯一
binlog-do-db=asteriskcdrdb #要同步的数据库
binlog-ignore-db=mysql #不同步的数据?
replicate-do-db=asteriskcdrdb #复制的数据库
replicate-ignore-db=mysql #未知
log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。
slave-skip-errors=all #
sync_binlog=1 #同步时钟
auto_increment_increment=2 #
auto_increment_offset=1 #自增涨增量
B:
user = mysql
log-bin=mysql-bin
server-id= 2
binlog-do-db=asteriskcdrdb
binlog-ignore-db=mysql
replicate-do-db=asteriskcdrdb
replicate-ignore-db=mysql
log-slave-updates #如果一个MASTER 挂掉的话，另外一个马上接管。
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
#sync_binlog=1
#auto_increment_increment=2
#auto_increment_offset=2
#服务器频繁的刷新日志。这个保证了在其中一台挂掉的话，日志刷新到另外一台。从而保证了数据的同步 。
重启这两台mysql server
然后执行/usr/bin/mysqld_safe & #把mysqld_safe放入后台执行 如果不能执行先stop mysqld
分别进入两台mysql 的shell控制台
A:
mysql> flush tables with read lock\G;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 98
Binlog_Do_DB: asteriskcdrdb,asteriskcdrdb
Binlog_Ignore_DB: mysql,mysql
1 row in set (0.00 sec)
B:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 98
Binlog_Do_DB: asteriskcdrdb,asteriskcdrdb
Binlog_Ignore_DB: mysql,mysql
1 row in set (0.00 sec)
然后保证两台数据库的记录一致,然后继续往下:
在各自机器上执行CHANGE MASTER TO命令。
A:
mysql> change master to
-> master_host=’192.168.104.3′,
-> master_user=’repl2′,
-> master_password=’123456′,
-> master_log_file=’mysql-bin.000002′, #同B服务器当中File: mysql-bin.000002
-> master_log_pos=98; #同B服务器录中Position: 98
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
B:
mysql> change master to
-> master_host=’192.168.104.2′,
-> master_user=’repl1′,
-> master_password=’123456′,
-> master_log_file=’mysql-bin.000002′, #同A服务器当中File: mysql-bin.000002
-> master_log_pos=98; #同B服务器录中Position: 98
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看各自机器上的IO进程和 SLAVE进程是否都开启。
A:
mysql>show processlist\G
mysql> show processlist\G
*************************** 1. row ***************************
Id: 74
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 159
User: system user
Host:
db: NULL
Command: Connect
Time: 100
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 160
User: system user
Host:
db: NULL
Command: Connect
Time: 100
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 4. row ***************************
Id: 169
User: repl1
Host: 192.168.104.3:4240
db: NULL
Command: Binlog Dump
Time: 55
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)
B:
mysql>show processlist\G
mysql> show processlist\G
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 7
User: repl2
Host: 152.104.141.19:58615
db: NULL
Command: Binlog Dump
Time: 51
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
Id: 8
User: system user
Host:
db: NULL
Command: Connect
Time: 40
State: Waiting for master to send event
Info: NULL
*************************** 4. row ***************************
Id: 9
User: system user
Host:
db: NULL
Command: Connect
Time: 40
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
4 rows in set (0.01 sec)
如果出错误,查看/var/log/mysqld.log 当中的日志,注意权限、访问控制、master_log_file、master_log_pos 等值
释放掉各自的锁，然后进行插数据测试。
A:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
B:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
下面可以通过添加表，删除表，添加记录，删除记录等方法测试双master备份。
根据测试结果，失败再看日志，成功就大功告成
