双主同步
(1)首先确保两台Mysql服务器上的Mysql版本相同
(2)在两台mysql服务器上,分别设置一个从数据库的账户,使用REPLICATION SLAVE赋予权限,如:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY # 这边 “%” 可以换成互为主从的ip地址
'123123';
Query OK, 0 rows affected (0.13 sec)
mysql> FLUSH PRIVILEGES;
(3)修改my.cnf
master下配置
[mysqld]
server_id=1 #要保持 id 唯一
log-bin=mysql-bin #二进制日志
binlog-do-db=ultrax # 要同步的数据库
log_bin=mysql-bin
master-host=10.10.10.2 #master ip
master-user=slave #用来跟master同步的用户
master-password=123123 #用户密码
master-port=3306 #master 的端口号
replicate-do-db=ultrax #要同步的数据库
log-slave-updates #添加更新的记录些到二进制文件里
重启Mysql服务器
slave下配置
[mysqld]
server-id=2 #要保持 id 唯一
log_bin=mysql-bin #二进制日志
master-host=10.10.10.1 #master ip
master-user=slave #用来跟master同步的用户
master-password=123123 #用户密码
master-port=3306 #master 的端口号
replicate-do-db=ultrax #要同步的数据库
log-slave-updates #添加更新的记录些到二进制文件里
log-bin=mysql-bin #二进制日志
binlog-do-db=ultrax # 要同步的数据库
重启Mysql服务器
分别在两台mysql服务器上查看 slave 和 master 信息
在A机器上面查看
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 106
Binlog_Do_DB: ultrax
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 103028
Relay_Log_File: mysqld-relay-bin.000021
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #确保这两个都是 "yes"
Replicate_Do_DB: ultrax
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 103028
Relay_Log_Space: 552
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
在B机器上查看
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000009
Position: 103028
Binlog_Do_DB: ultrax
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #确保这两个一定是“yes”
Replicate_Do_DB: ultrax
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 101631
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
错误：
Slave_IO_Running: no
表示配置文件有问题
Slave_SQL_Running: no
表示 Read_Master_Log_Pos: 125081这个值跟master 的不一样，要手动在slave下：
mysql>slave stop;
mysql>change master to master_host='10.10.10.2',master_user='slave',master_password='123123', master_log_file='mysql-bin.000015' ,master_log_pos=19611;
在master上查看
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000015
Position: 19611
Binlog_Do_DB: ultrax
Binlog_Ignore_DB:
1 row in set (0.00 sec)
