MySQL互为主从-CentOS.7

主：192.168.124.71
从：192.168.124.72
MySQL：5.5.64-MariaDB

主

#安装MySQL
yum -y install mariadb mariadb-server

#开启二进制 和 中继日志
vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

#添加以下三行内容
server-id=1
log-bin=mysql-bin
log_slave_updates=true

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory
!includedir /etc/my.cnf.d

#重启并登陆MySQL
systemctl restart mariadb
mysql -uroot -p

MariaDB [(none)]> grant replication slave on *.* to 'rep'@'192.168.124.72' identified by '123';
MariaDB [(none)]> stop slave;
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      396 |              |                  |
+------------------+----------+--------------+------------------+

#写从的信息
MariaDB [(none)]> change master to
	 master_host='192.168.124.72',
	 master_user='rep',
	 master_password='123',
	 master_log_file='mysql-bin.000001',
	 master_log_pos=396;
	 
MariaDB [(none)]> start salve;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.124.72
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 396
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
#双Yes表示成功          
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

从

#安装MySQL
yum -y install mariadb mariadb-server

#开启二进制 和 中继日志
vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

#添加以下三行内容
server-id=2
log-bin=mysql-bin
log_slave_updates=true

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory
!includedir /etc/my.cnf.d

#重启MySQL并登陆MySQL
systemctl restart mariadb
mysql -uroot -p

MariaDB [(none)]> grant replication slave on *.* to 'rep'@'192.168.124.71' identified by '123';
MariaDB [(none)]> stop slave;
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      396 |              |                  |
+------------------+----------+--------------+------------------+

#写主的信息
MariaDB [(none)]> change master to
	-> master_host='192.168.124.71',
	-> master_user='rep',
	-> master_password='123',
	-> master_log_file='mysql-bin.000002',
	-> master_log_pos=396;
	
MariaDB [(none)]> start salve;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.124.71
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 396
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000002
#双Yes表示成功        
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试

在任意一台上增、删、改另一台会自动执行相应操作

#192.168.124.71
MariaDB [(none)]> create database www;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| www                |
+--------------------+

#192.168.124.72
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| www                |
+--------------------+