Mysql主从、主主部署

服务规划

该部署以Red Hat 7为例

前提准备

1.关闭防火墙和SELinux(所有设备)

systemctl stop firewalld.service

systemctl disable firewalld.service

setenforce 0               

vi /etc/selinux/config   

SELINUX=enforcing改为SELINUX=disabled

2.安装Mysql(所有设备)

可参考Mysql单机部署(二进制方式部署)

Mysql主从部署

1.修改master节点配置文件(192.168.36.134)

vi /etc/my.cnf

#开启二进制日志
log-bin=mysql-bin
#server id设备之间不可重复
server-id=1

2.修改slave节点配置文件(192.168.36.135)　

vi /etc/my.cnf

#从节电不需要开启二进制日至
server-id=2

3.重启Mysql服务(所有设备)

systemctl restart mysqld

4.配置master节点(192.168.36.134)

mysql -uroot -p

#创建一个做复制的mysql用户
create user 'mysql'@'%' identified by 'Cjz123.';
#授予从节点连接主节点的权限
grant replication slave on *.* to 'mysql'@'192.168.36.135' identified by 'Cjz123.'
flush privileges;

#查看binlog日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      849 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 5.配置slave节点(192.168.36.135)

mysql -uroot -p

#指定master节点IP地址，这里需要注意binlog文件指定到master的binlog名称和position
change master to master_host='192.168.36.134',master_user='mysql',master_password='Cjz123.',master_log_file='mysql-bin.000001',master_log_pos=849;
start slave；

查看slave状态

mysql> show slave status\G； #查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.36.134
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000001
          Read_Master_Log_Pos: 450
               Relay_Log_File: localhost-relay-bin.000001
               Relay_Log_Pos: 849
        Relay_Master_Log_File: mysql-bin-master.000001
              Slave_IO_Running: Yes
              Slave_SQL_Running: Yes

#最后两项为yes即配置成功

注：主从模式下，对主节点进行数据更改，从节点会进行同步，但是对从节点进行数据更改的话，主节点是不会同步的

Mysql主主部署

其实主主配置和主从的配置是一样的，只不过我们现在需要换过来主从配置，基于上方例子，这次换成master节点为192.168.36.135，slave节点为192.168.36.134

1.修改master节点配置文件(192.168.36.135)

vi /etc/my.cnf

#开启二进制日志
log-bin=mysql-bin

2.重启Mysql服务(192.168.36.135)

systemctl restart mysqld

3.配置master节点(192.168.36.135)

mysql -uroot -p

#创建一个做复制的mysql用户
create user 'mysql'@'%' identified by 'Cjz123.';
#授予从节点连接主节点的权限，这里指向我们原来的主节点
grant replication slave on *.* to 'mysql'@'192.168.36.134' identified by 'Cjz123.'
flush privileges;

#查看binlog日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      159 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 4.配置slave节点(192.168.36.134)

mysql -uroot -p

#这里我们要配置上192.168.36.135的binlog名称和position
change master to master_host='192.168.36.135',master_user='mysql',master_password='Cjz123.',master_log_file='mysql-bin.000001',master_log_pos=159;
start slave；

查看slave状态

mysql> show slave status\G； #查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.36.135
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000001
          Read_Master_Log_Pos: 450
               Relay_Log_File: localhost-relay-bin.000001
               Relay_Log_Pos: 159
        Relay_Master_Log_File: mysql-bin-master.000001
              Slave_IO_Running: Yes
              Slave_SQL_Running: Yes

#最后两项为yes即配置成功