mysql 主主配置搭建

环境：CENTOS6 两台  mysql 5. 6

服务器1ip地址：192.168.31.129

服务器2ip地址：192.168.31.130

其实mysql主主和主从差不多，如果掌握主从配置的话，相信会很快就能搭建主主，没掌握也没关系。跟着步骤一步一步来。

首先两台服务器要安装好mysql  关闭防火墙  selinux  保证两台服务器ping 得通

服务器1：

                如果这台服务器已经启用binlog，建议对日志做一次重置。

                mysql -u root -p

                Enter password:

                    .......

                 reset master;

                  quit

                  接下来备份服务器1数据到服务器2。（下列只配分服务器1中的其中一个库）

                   服务器1操作：mysqldump -uroot -p newdb > /root/newdb.sql

                   服务器2操作：mysql -uroot -p

                                          create databases newdb default character set utf8 collate utf8_general_ci;

                                           use newdb;

                                           source /root/newdb.sql

                   服务器1操作：vim /etc/my.cnf

                                          [mysqld]

                                           log_bin=dbsrv1-bin

                                           server_id=10

                                           重启mysql服务

                                           service mysqld restart

                     服务器2操作：vim /etc/my.cnf

                                            [mysqld]

                                            log_bin=dbsrv2-bin

                                            server_id=20

                                            重启mysql服务

                                            service mysqld restart

                      服务器1操作：

                                             mysql -uroot -p

                                              grant replication slave on *.* to 'xxx'@'192.168.31.130' identified by '123';

                                              show master status\G

                                              记下File和pos 

                                               

                        服务器2操作:

                                                    mysql -uroot -p

                                                    change master to master_host='192.168.31.129',

                                                       master_user='xxx',

                                                       master_password='123',

                                                       master_log_file='dbsrv1-bin.000001',         #这里填写的，就是服务器1记下的file

                                                       master_log_pos=154;                                 #这里填写的，就是服务器2记下的pos

                                                        start slave

                                                        show slave status\G

                                                         这里要有两个yes

                                                          

                                                         

                                                          

                                                  grant replication slave on *.* to 'qqq'@'192.168.31.129' identified by '123';

                                                  show master status\G

                                                  记下File和pos 

                          服务器1操作：

                                                   

                                                       change master to master_host='192.168.31.130',

                                                       master_user='qqq',

                                                       master_password='123',

                                                       master_log_file='dbsrv1-bin.000001',    #这里填写的  就是刚才在服务器2上记下的file

                                                       master_log_pos=154;                           #这是填写的   就是刚才服务器2记下的pos

                                                        start slave

                                                        show slave status\G

                                                         这里要有两个yes

到这里基本完成了主主复制，可以show master status\G查看

注意  跨库建立表格、库等 可能会导致主主中断

则:

stop slave 

set global sql_slave_skip_counter=1

start slave