mysql 主主
一、环境描述
mysql master1 10.4.7.13
mysql master2 10.4.7.14
Mysql版本：5.6
System OS：CentOS 7
二、配置mysql主主同步
1、分别在mysql master1和mysql master2上安装mysql
(1) 检测mysql和mariadb是否安装，有的话删掉
rpm -qa | grep mysql
rpm -qa|grep -i mariadb
yum -y remove mysql maridb(2) 下载mysql安装包并安装yum源
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm(3) 查看可用mysql安装文件并安装mysql
yum repolist all | grep mysql
yum install mysql-server(4) 启动mysql并设置密码
systemctl start mysqld.service #启动 mysql
systemctl restart mysqld.service #重启 mysql
systemctl stop mysqld.service #停止 mysql
systemctl enable mysqld.service #设置 mysql 开机启动
mysqladmin -uroot password 'Clouddeep@8890'(5) 设置允许远程登录
mysql> GRANT ALL PRIVILEGES ON *.* TO 'wenqiang'@'10.4.7.%' IDENTIFIED BY 'Clouddeep@8890';
-----------------------------------------------------------------
GRANT ALL PRIVILEGES ON *.* # 授权拥有所有数据库的所有权限
'root@'172.16.10.%' IDENTIFIED BY 'Clouddeep@8890' # 允许来自10.4.7.0网段的wenqiang用户使用'Clouddeep@8890'密码登录数据库 (6) 测试远程登陆
mysql -u wenqiang -p -h 10.4.7.13 -P 3306
2、配置mysql master1的参数
# vim /etc/my.cnf
[mysqld]
server-id = 1 # 唯一性用来表示主主/主从关系，master1设置为1
log-bin = mysql-bin # 开启binlog日志
expire_logs_days=7 # binlog日志保存7天
sync_binlog = 1 # 每往binlog中写入一条数据就往磁盘上刷新一次
binlog_checksum = none # binlog效验功能，none表示关闭
binlog_format = mixed # mysql复制模式，mixed为混合模式
auto-increment-increment = 2 # mysql主主复制自增长字段，两边都为2
auto-increment-offset = 1 # mysql主主复制自增长字段，master1设置为1
# 自动跳过主主/主从同步中的错误
slave_skip_errors=1062,1053
slave_skip_errors=all
slave_skip_errors=ddl_exist_errors
----------------------------------------------------------------------------
ddl_exist_errors #表示1007,1008,1050,1051,1054,1060,1061,1068,1094,1146错误
一些error code代表的错误如下：1007：数据库已存在，创建数据库失败1008：数据库不存在，删除数据库失败1050：数据表已存在，创建数据表失败1051：数据表不存在，删除数据表失败1054：字段不存在，或程序文件跟数据库有冲突1060：字段重复，导致无法插入1061：重复键名1068：定义了多个主键1094：位置线程ID1146：数据表缺失，请恢复数据库1053：复制过程中主服务器宕机1062：主键冲突 Duplicate entry '%s' for key %d
3、配置mysql master2的参数
# vim /etc/my.cnf
[mysqld]
server-id = 2 # master2设置2
log-bin = mysql-bin
expire_logs_days=7
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 2 # master2设置为2
slave_skip_errors=1062,1053
slave_skip_errors=all
slave_skip_errors=ddl_exist_errors
4、分别在master1和master2上配置mysql同步用户
mysql> grant replication slave on *.* to repl@'10.4.7.%' identified by 'Clouddeep@8890'; # 这里密码复杂一点要不然报错
-------------------------
grant replication # 授权复制权限
grant slave # 授权查看状态权限
4、分别在master1和master2上锁表，待同步配置完成后再解锁
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
5、分别在master1和master2上查看当前的binlog名称和binlog位置
# master1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 739 | | | |
+------------------+----------+--------------+------------------+-------------------+# master2
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 532 | | | |
+------------------+----------+--------------+------------------+-------------------+
6、分别在master1上和master2上同步对方的数据
# master1
mysql> unlock tables; # 解锁
mysql> stop slave; # 关闭同步功能
mysql> change master to master_host='10.4.7.14',master_user='repl',master_password='Clouddeep@8890',master_log_file='mysql-bin.000001',master_log_pos=532;
--------------------------------------------------
change master # 表示对master2进行复制
master_host # 指定master2的ip
master_user # 指定登录master2的用户
master_password # 指定登录master2的密码
master_log_file # 指定master2的binlog文件名称
master_log_pos # 指定master2的binlog文件位置mysql> start slave; # 启动同步功能
mysql> show slave status\G; # 查看同步状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes# master2
mysql> unlock tables; # 解锁
mysql> stop slave; # 关闭同步功能
mysql> change master to master_host='10.4.7.13',master_user='repl',master_password='Clouddeep@8890',master_log_file='mysql-bin.000001',master_log_pos=739;
mysql> start slave; # 启动同步功能
mysql> show slave status\G; # 查看同步状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7、分别在master1和master2上写入数据，查看是否同步
mysql> create database wenqiang1;
mysql> show databases;