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: