Mysql 主主复制
环境
系统：Centos 7.4 x64
服务版本：Mariadb 5.5
结构
主1:192.168.1.108
主2:192.168.1.109
主1、主2 安装Mariadb服务（两端操作相同）
1.下载yum源
wget -P /etc/yum.repos.d http://mirrors.aliyun.com/repo/Centos-7.repo
2.安装Mariadb服务
yum -y install mariadb-server-5.5.56-2.el7.x86_64 yum -y install mariadb-5.5.56-2.el7.x86_64 yum -y install mariadb-libs-5.5.56-2.el7.x86_64
3.启动mysql 加入开机自启动
# 1.启动mysql systemctl start mariadb
# 2.加入开机自启动 systemctl enable mariadb
4.初始化数据库
# 2.初始化mysql数据库。 mysql_secure_installation
5.创建用户
# 1.创建用户名可被任意主机连接 mysql -uroot -p123456 -e "grant all on *.* to "root"@"%" identified by "123456";" # 2.添加复制连接用户 GRANT REPLICATION SLAVE ON *.* TO 'RepUser'@'%'identified by 'beijing'; # 3.刷新 flush privileges;
配置主主复制（两端操作相同）
1.修改主配置文件
[mysqld] # 默认 端口自定义 port=3307 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock security risks symbolic-links=0 # 添加 开启二进制文件 log-bin=mysql-bin # 添加 主1 = 1 主2 = 2 server-id = 1 # 添加 避免主键冲突 auto-increment-offset= 1 auto-increment-increment= 2
[mysqld] # 默认 端口自定义 port=3307 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock security risks symbolic-links=0 # 添加 开启二进制文件 log-bin=mysql-bin # 添加 主1 = 1 主2 = 2 server-id = 2 # 添加 避免主键冲突 auto-increment-offset= 2 auto-increment-increment= 2
2.重启mysql
systemctl restart mariadb.service
3.查看二进制文件
# 进入主1数据库 mysql -uroot -p123456 MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 245 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) # 进入主2数据库 mysql -uroot -p123456 MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 335 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
4.通过关联用户 关联两端position号 与 二进制文件 实现主主复制
# 主1 MariaDB [(none)]> CHANGE MASTER TO MASTER_USER='RepUser',MASTER_HOST='192.168.1.109',MASTER_PASSWORD='beijing',MASTER_PORT=3307,MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=335; # 主2 MariaDB [(none)]> CHANGE MASTER TO MASTER_USER='RepUser',MASTER_HOST='192.168.1.108',MASTER_PASSWORD='beijing',MASTER_PORT=3307,MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=245;
5.两端开启复制功能
MariaDB [(none)]> start slave;
6.查看主主复制状态，双yes 为成功！！！
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.109 Master_User: RepUser Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 335 Relay_Log_File: mariadb-relay-bin.000015 Relay_Log_Pos: 619 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 335 Relay_Log_Space: 1199 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 1 row in set (0.00 sec)
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.108 Master_User: RepUser Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000015 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: 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: 245 Relay_Log_Space: 1109 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
