Mysql集群（一）-----Mysql双主双从复制

一主多从架构可以缓解读的压力，但是一旦主宕机了，就不能写了。所以我们可以采用双主双从架构

1 Mysql双主双从的部署

（1）源码编译安装mysql5.7参考博客：https://blog.csdn.net/qq_41582883/article/details/112396592

（2）编辑mysql配置文件：/etc/my.cnf

• server1的配置文件

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
basedir=/usr/local/mysql
# 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

#[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
server-id=1 ## 主服务器唯一id	
log-bin=binlog  ## 启用二进制日志
binlog-ignore-db=mysql ## 不复制的数据库，也可以有多个
binlog-do-db=testdb   ## 需要复制的数据库，也可以有多个
binlog_format=STATEMENT  ## 设置binlog的格式
auto_increment_increment=2 ## 表示自增长字段每次递增的量，指自增段的起始值，默认值为1，取之范围1～65535
auto_increment_offset=1  ## 表示自增字段从哪个数字开始，至字段一次递增多少，取之范围1～65535
log-slave-updates ## 在作为从数据库的时候，有写入操作也要更新二进制日志文件
sync_binlog=1  ##每经过1次日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)

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

• server2的配置文件

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
basedir=/usr/local/mysql
#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

#[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
server-id=2 ## 主服务器唯一id	
log-bin=binlog
binlog-ignore-db=mysql
binlog-do-db=testdb
binlog_format=STATEMENT
auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1

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

server3的配置文件

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
basedir=/usr/local/mysql
#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

#[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
server-id=3
relay-log=mysql-relay
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

server4的配置文件

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
basedir=/usr/local/mysql
# 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

#[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
server-id=4
relay-log=mysql-relay
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

（3）重启mysql：/etc/init.d/mysqld restart

• 查看主库的master的状态

（4）然后分别在两台主服务器上创建数据的账号并且授权

• 创建远程用户marry，密码为westos（server1、server2）

grant replication slave on *.* to marry@'%' identified by 'westos';

*.* ： 其中第一个*表示所有数据库名，第二个*表示所有的数据库表
'copy'@'%'：copy表示用户名，%表示ip地址，可以具体到某个ip 如：copy@127.0.0.1

（5）查看两台主库的状态：

（7）在从库(server3、server4)设置它的master，这里的master_log_file和master_log_pos对应刚才show master status记下的参数

server3为server1的slave

reset slave;
change master to master_host='172.25.12.1', master_user='marry',master_password='westos',master_log_file='binlog.000001',master_log_pos=443;
start slave;

server4为server2的slave

reset slave;
change master to master_host='172.25.12.2', master_user='marry',master_password='westos',master_log_file='binlog.000001',master_log_pos=443;
start slave;

查看slave的状态：show slave status;

slave_IO_Running和Slave_SQL_Running均为yes说明数据库的主从配置成功

（6）两个主机互相复制 change master

因为是双主双从，所以要配置 Master2 复制 Master1， Master1 复制 Master2

server1:

reset slave;
change master to master_host='172.25.12.2', master_user='marry',master_password='westos',master_log_file='binlog.000001',master_log_pos=443;
start slave;

server2:

reset slave;
change master to master_host='172.25.12.1', master_user='marry',master_password='westos',master_log_file='binlog.000001',master_log_pos=443;
start slave;

2 双主双从测试

（1）在server2的mysql中创建库和表，插入数据；数据库会同步到server1、server3和server4的数据库中

mysql> create database testdb;## 同步的数据库名
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testdb;
Database changed
mysql> create table student(name varchar(12) not null, passwd varchar(20) not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into student values('hello','123');
Query OK, 1 row affected (0.01 sec)

mysql> select * from testdb.student;
+-------+--------+
| name  | passwd |
+-------+--------+
| Tom   | 123    |
+-------+--------+
1 row in set (0.00 sec)

（2）在server1中的testdb库的student表插入数据：

数据同步到server2、server3、server4：