Mysql主从、主主部署

服务规划

该部署以Red Hat 7为例

前提准备

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

systemctl stop firewalld.service

systemctl disable firewalld.service

setenforce 0               

vi /etc/selinux/config   

?

1	SELINUX=enforcing改为SELINUX=disabled

2.安装Mysql(所有设备)

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

Mysql主从部署

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

vi /etc/my.cnf

?

1 2 3 4

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

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

vi /etc/my.cnf

?

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

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

systemctl restart mysqld

4.配置master节点(192.168.36.134)

mysql -uroot -p

?

1 2 3 4 5

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

#查看binlog日志

?

1 2 3 4 5 6 7

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

?

1 2 3

#指定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状态

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

?

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

2.重启Mysql服务(192.168.36.135)

systemctl restart mysqld

3.配置master节点(192.168.36.135)

mysql -uroot -p

?

1 2 3 4 5

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

#查看binlog日志

?

1 2 3 4 5 6 7

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

?

1 2 3

#这里我们要配置上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状态

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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即配置成功