赞
踩
主机名 | 系统环境 | 软件 | IP |
---|---|---|---|
DB1 | CentOS7.6 | MySQL5.7 ,keepalived | 192.168.66.63 |
DB2 | CentOS7.6 | MySQL5.7 ,keepalived | 192.168.66.64 |
Client | CentOS7.6 | MySQL5.7 | 192.168.66.61 |
VIP | 192.168.66.200 |
DB1、DB2都已安装MySQL,安装过程省略
MySQL安装见之前的文章:MySQL安装
在/etc/my.ccnf文件中的[mysqld]段加配置信息
DB1:
[root@DB1 ~]# vim /etc/my.cnf #mysql双主增加信息 #节点标识,每台的server—id不能一样,全局唯一 server-id=1 #开启binlg日志,用于主从数据复制 log-bin=mysql-bin #开启relay-log日志 relay-log=mysql-relay-bin #复制过滤选项 replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.% #字段一次递增多少 auto-increment-increment = 2 #自增字段的起始值:1、3、5、7、等奇数 auto-increment-offset = 1 slave-skip-errors = all #保存配置,重启MySQL服务 [root@DB1 ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS!
DB2:
[root@dg ~]# vim /etc/my.cnf #mysql双主新增内容 server-id=2 log-bin=mysql-bin relay-log=mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.% auto-increment-increment = 2 auto-increment-offset = 2 slave-skip-errors = all #保存配置,重启服务 [root@dg ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS!
DB1:
[root@DB1 ~]# mysql -u root -p
Enter password:
mysql> show master status;
DB2:
[root@dg ~]# mysql -uroot -p
Enter password:
mysql> show master status;
DB1:
#创建DB2的复制用户并授权
mysql> grant replication slave on *.* to 'cproot'@'192.168.66.64' identified by 'cp123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)
#刷新并查看log bin日志和pos位置
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
DB2:
mysql> grant replication slave on *.* to 'cproot'@'192.168.66.63'identified by 'cp123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
注意:master_log_file和master_log_pos是对方服务器最新的数据
DB1:
mysql> change master to master_host='192.168.66.64',master_user='cproot',master_password='cp123456',master_log_file='mysql-bin.000001',master_log_pos=605;
Query OK, 0 rows affected, 2 warnings (0.20 sec)
DB2:
mysql> change master to master_host='192.168.66.63',master_user='cproot',master_password='cpp123456',master_log_file='mysql-bin.000001',master_log_pos=605;
Query OK, 0 rows affected, 2 warnings (0.17 sec)
DB1:
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
DB2:
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
这两个地方都为yes说明主主同步成功,如果不是yes的话有以下几种排查思路:
1,网络不通
2,密码不正确
3,pos不对
4,防火墙没关
在DB1服务器上新建数据库one,然后在DB2上查看是否同步
DB2删除one,查看DB1是否还存在
DB1:
mysql> create database one;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| one |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
DB2:
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。