Linux搭建MySQL主从复制架构

Linux搭建MySQL主从复制架构

1.1主从复制概述

在实际生产中，数据的重要性不言而喻
如果我们的数据库只有一台服务器，那么很容易产生单点故障的问题，比如这台服务器访问压力过大而没有响应或者奔溃，那么服务就不可用了，再比如这台服务器的硬盘坏了，那么整个数据库的数据就全部丢失了，这是重大的安全事故.
为了避免服务的不可用以及保障数据的安全可靠性，我们至少需要部署两台或两台以上服务器来存储数据库数据，也就是我们需要将数据复制多份部署在多台不同的服务器上，即使有一台服务器出现故障了，其他服务器依然可以继续提供服务.
MySQL提供了主从复制功能以提高服务的可用性与数据的安全可靠性.
主从复制是指服务器分为主服务器和从服务器，主服务器负责读和写，从服务器只负责读，主从复制也叫 master/slave，master是主，slave是从，但是并没有强制，也就是说从也可以写，主也可以读，只不过一般我们不这么做。
主从复制可以实现对数据库备份和读写分离

1.2主从复制架构

1.2.1一主多从架构

1.2.2双主双从架构

1.3主从复制原理（流程|步骤）

当 master 主服务器上的数据发生改变时，则将其改变写入二进制事件日志文件中
salve 从服务器会在一定时间间隔内对 master 主服务器上的二进制日志进行探测，探测其是否发生过改变，如果探测到 master 主服务器的二进制事件日志发生了改变，则开始一个 I/O Thread 请求 master 二进制事件日志
同时 master 主服务器为每个 I/O Thread 启动一个dump Thread，用于向其发送二进制事件日志
slave 从服务器将接收到的二进制事件日志保存至自己本地的中继日志文件中
salve 从服务器将启动 SQL Thread 从中继日志中读取二进制日志，在本地重放，使得其数据和主服务器保持一致；
最后 I/O Thread 和 SQL Thread 将进入睡眠状态，等待下一次被唤醒
注意：主从复制的过程会有很小的延迟，基本没有影响

1.4MySQL主从复制流程图

第2章MySQL多实例搭建

2.1多实例概述

MySQL多实例是指安装MySQL之后，在一台Linux服务器上同时启动多个MySQL数据库（实例），不需要安装多个MySQL（适合技术研究和学习的场景）
如果是有多台Linux服务器，那么我们需要每台服务器都分别安装MySQL（适用于实际线上生产环境）
我们此处计划在一台Linux上启动多个MySQL，这样适合我们的技术研究和学习，如果要在多台Linux分别启动MySQL，这个与我们在一台机器上的配置与操作都是完全一样的。
如何实现在一台Linux服务器上同时启动多个MySQL数据库（实例）？
通过为各个数据库实例配置独立的配置文件来实现，即每个数据库实例有自己单独的配置文件

2.2多实例配置

2.2.1在MySQL安装主目录下创建四个实例存放数据的目录

/data/3307, /data/3308, /data/3309, /data/3310

2.2.2执行数据库初始化

在MySQL的/usr/local/mysql-5.7.24/bin目录下执行命令

./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.24 --datadir=/usr/local/mysql-5.7.24/data/3307 --user=mysql

./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.24 --datadir=/usr/local/mysql-5.7.24/data/3308 --user=mysql

./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.24 --datadir=/usr/local/mysql-5.7.24/data/3309 --user=mysql

./mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.24 --datadir=/usr/local/mysql-5.7.24/data/3310 --user=mysql

initialize-insecure 表示不生成MySQL数据库root用户的随机密码，即root密码为空

2.2.3配置四个MySQL数据库服务的my.cnf文件

（1）为了方便操作，在Xshell中打开4个选项卡，去连接不同的是MySQL实例

（2）在/data/3307, /data/3308, /data/3309, /data/3310 四个目录下分别创建一个my.cnf文件，在四个my.cnf文件分别配置如下内容
注意：不同的实例下配置要修改端口号

[client]
port        = 3307
socket      = /usr/local/mysql-5.7.24/data/3307/mysql.sock
default-character-set=utf8

[mysqld]
port    = 3307
socket  = /usr/local/mysql-5.7.24/data/3307/mysql.sock
datadir = /usr/local/mysql-5.7.24/data/3307
log-error = /usr/local/mysql-5.7.24/data/3307/error.log
pid-file = /usr/local/mysql-5.7.24/data/3307/mysql.pid

character-set-server=utf8
lower_case_table_names=1
autocommit = 1

2.3多实例启动

切换到/usr/local/mysql-5.7.24/bin目录下，使用 msyqld_safe 命令指定配置文件并启动MySQL服务：

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3307/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3308/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3309/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3310/my.cnf &

其中 --defaults-file 是指定配置文件，& 符合表示后台启动

2.4启动后配置

2.4.1登录进入每一个MySQL实例

在mysql-5.7.24/bin目录下执行命令
（1）方式一：使用用端口、主机登录（推荐使用该方式）
./mysql -uroot -p -P3307 -h127.0.0.1
（2）方式二：使用socket文件
./mysql -uroot -p -S /usr/local/mysql-5.7.24/data/3307/mysql.sock
-p 是指定密码，如果没有密码则可以不写 -p，
-S是指定sock文件，mysql.sock文件是服务器与本机客户端进行通信的ip与端口文件
2.4.2修改mysql的密码
alter user ‘root’@‘localhost’ identified by ‘940614’;
其中123456是我们设置的密码
2.4.3授权远程访问（这样远程客户端才能访问）
grant all privileges on . to root@’%’ identified by ‘940614’;
其中*.* 的第一个表示所有数据库名，第二个表示所有的数据库表
root@’%’ 中的root表示用户名
%表示所有ip地址，%也可以指定具体的ip地址，比如root@localhost，root@192.168.10.129

2.4.4刷新权限

flush privileges;

2.5多实例关闭

切换到/usr/local/mysql-5.7.24/bin目录下，使用 mysqladmin 命令 shutdown
（1）方式一：使用端口、主机关闭 （推荐）
./mysqladmin -uroot -p -P3307 -h127.0.0.1 shutdown
（2）方式二:使用socket文件
./mysqladmin -uroot -p -S /usr/local/mysql-5.7.24/data/3307/mysql.sock shutdown
（3）方式三: 进入MySQL的客户端命令行，直接执行 shutdown
（4）退出MySQL命令行
在客户端命令行下执行 exit

2.6开通防火墙

（1）vi /etc/sysconfig/iptables
-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT
（允许3306端口通过防火墙）
（2）/etc/init.d/iptables restart（重启防火墙使配置生效）
或者 service iptables restart
（3）或者直接关闭防火墙；

2.7在Navicat中创建多个连接，分别连接主从库

第3章一主多从环境搭建

3.1概述

当系统对数据的读取比较多时，为了分摊读的压力，可以采用一主多从架构，实现读写分离

3.2一主三从架构架构图

3.3环境配置（先将各个服务器停掉,删除掉中文注释）

3.3.1在MySQL主服务器(3307)配置文件my.cnf里面加入

log-bin=mysql-bin   	#表示启用二进制日志
server-id=3307 		#表示server编号，编号要唯一

3.3.2在MySQL从服务器(3308)配置文件my.cnf里面加入

server-id=3308        #表示server编号，编号要唯一

3.3.3在MySQL从服务器(3309)配置文件my.cnf里面加入

server-id=3309        #表示server编号，编号要唯一

3.3.4在MySQL从服务器(3310)配置文件my.cnf里面加入

server-id=3310        #表示server编号，编号要唯一

3.4服务启动

进入/usr/local/mysql-5.7.24/bin目录，重启四个MySQL服务，启动时指定配置文件

---- 此时如果服务已经启动，需要先关闭服务，再去启动服务！

./mysqladmin -uroot -p -P3307 -h127.0.0.1 shutdown

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3307/my.cnf
& ./mysqld_safe
–defaults-file=/usr/local/mysql-5.7.24/data/3308/my.cnf & ./mysqld_safe
–defaults-file=/usr/local/mysql-5.7.24/data/3309/my.cnf & ./mysqld_safe
–defaults-file=/usr/local/mysql-5.7.24/data/3310/my.cnf &

3.5主从设置（重要）

3.5.1主服务器（3307）设置

需要登录到主服务器3307的客户端
在/usr/local/mysql-5.7.24/bin目录下执行

./mysql -uroot -p -P3307 -h127.0.0.1

（1）在主服务器上创建复制数据的账号并授权
show master status;
注意：这是在主服务器，主数据库特有的！

grant replication slave on . to ‘copy’@’%’ identified by ‘940614’;

注意：该语句可完成授权、创建用户、修改密码操作

此时由于写入特殊账号copy，已经生成日志，所以需要重置状态！

show master status;
执行命令：
reset master;

（2）查看主服务器状态

show master status;

mysql主服务器默认初始值：
		File：mysql-bin.000001
		Position：154

（3）如果主服务状态不是初始状态，需要重置状态
执行命令：

reset master;

3.5.2从服务器（3308|3309|3310）设置

需要登录到主服务器3308|3309|3310的客户端
在/usr/local/mysql-5.7.24/bin目录下执行

./mysql -uroot -p -P3308|3309|3310 -h127.0.0.1

（1）查看从服务器状态

show slave status;

初始状态：Empty set 

（2）如果从服务器不是初始状态，建议重置一下

stop slave; 	#停止复制，相当于终止从服务器上的IO和SQL线程
reset slave;

（3）设置从服务器的master
在从服务器客户端执行

change master to
master_host=‘192.168.235.128-主的IP’,master_user=‘copy’,

master_port=3307-主的端口号,master_password=‘940614’,

master_log_file=‘mysql-bin.000001’,master_log_pos=154;

（4）在从机器上执行开始复制命令

start slave;

（5）至此一个一主多从搭建完成

3.6主从验证

3.6.1检查从服务器复制功能状态

在从服务器的客户端执行以下命令：show slave status \G;
\G表示格式化输出
如果 Slave_IO_Running 和 Slave_SQL_Running 均为 YES，则表示主从关系正常

3.6.2在主服务器上创建数据库、表、数据，然后在从服务器上查看是否已经复制

在Navicat主服务器上创建库test，查看从服务器情况
在Navicat主服务器上test库中创建表user(id,name)，查看从服务器情况
在Navicat主服务器上test库的user中添加数据，查看从服务器情况
在Navicat主服务器上test库的user中修改数据，查看从服务器情况
如果以上操作过程若显示正常，则主从服务器配置完成

3.6.3查看主从复制binlog日志文件内容

在主服务器客户端执行：show binlog events in ‘mysql-bin.000001’\G;

3.6.4问题：在从库写入数据会怎么样？

假如在从库主动写入数据，主库是不会去从库获取数据的，结果就是主从关系断开，主从效果失效。

解决办法：
方法1：先停了从库slave，再重启从库（无需重置状态reset slave）；
方法2：先停从库slave，再重置状态reset slave; 最后把从库里异常的数据库删除，再重新指定主库。
在从服务器客户端执行

change master to
master_host=‘192.168.235.128-主的IP’,master_user=‘copy’,

master_port=3307-主的端口号,master_password=‘940614’,

master_log_file=‘mysql-bin.000001’,master_log_pos=154;

第4章双主双从环境搭建

4.1概述

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

4.2 双主双重架构图

架构规划
主master 3307 —> 从slave 3309
主master 3308 —> 从slave 3310
3307 <—> 3308 互为主从
2个写节点，每个写节点下又是2个读节点

4.3环境配置（不要含有中文注释）

4.3.1在MySQL主服务器(3307)配置文件my.cnf里面加入（已做）

log-bin=mysql-bin
server-id=3307

4.3.2在MySQL主服务器(3308)配置文件my.cnf里面加入

log-bin=mysql-bin
server-id=3308

4.3.3在MySQL从服务器(3309)配置文件my.cnf里面加入（已做）

server-id=3309

4.3.4在MySQL从服务器(3310)配置文件my.cnf里面加入（已做）

server-id=3310

4.3.5在第一台主服务器3307的my.cnf文件增加如下配置

auto_increment_increment=2
auto_increment_offset=1 #不一样的点 相当于起始值
log-slave-updates
sync_binlog=1

4.3.6在第二台主服务器3308的my.cnf文件增加如下配置

auto_increment_increment=2
auto_increment_offset=2 #不一样的点 相当于起始值
log-slave-updates
sync_binlog=1

4.3.7配置项说明

（1）auto_increment_increment
控制主键自增的自增步长，用于防止Master与Master之间复制出现重复自增字段值，通常auto_increment_increment=n，有多少台主服务器，n 就设置为多少
（2）auto_increment_offset＝1
设置自增起始值，这里设置为1，这样Master的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID
注意auto_increment_offset的设置，不同的master设置不应该一样，否则就容易引起主键冲突，比如master1的offset=1，则master2的offset=2，master3的offset=3
（3）log-slave-updates
在双主模式中，log-slave-updates 配置项一定要配置，否则在master1（3307）上进行了更新数据，在master2（3308）和slave1（3309）上会更新，但是在slave2（3310）上不会更新
（4）sync_binlog
表示每几次事务提交，MySQL把binlog缓存刷进日志文件中，默认是0，最安全的是设置为1
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
（5）注意
从库只开启log-bin功能，不添加log-slave-updates参数，从库从主库复制的数据不会写入log-bin日志文件里。
开启log-slave-updates参数后，从库从主库复制的数据会写入log-bin日志文件里。这也是该参数的功能。
直接向从库写入数据时，是会写入log-bin日志的
在自动生成主键的时候，会在已生成主键的基础上按照规则生成，即比存在的值大

4.4服务启动

进入/usr/local/mysql-5.7.24/bin目录，重启四个MySQL服务，启动时指定配置文件

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3307/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3308/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3309/my.cnf &
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.24/data/3310/my.cnf &

---- 此时如果服务已经启动，需要先关闭服务，再去启动服务！

4.5主从设置（重要）

4.5.1主服务器（3307|3308）设置

需要登录到主服务器3307|3308的客户端
在/usr/local/mysql-5.7.24/bin目录下登陆实例：
执行

./mysql -uroot -p -P3307|3308 -h127.0.0.1

（1）在两台主服务器（3307|3308）上创建复制数据的账号并授权
3307已经做过，只需要在3308上执行即可

grant replication slave on *.* to 'copy'@'%' identified by '940614';

（2）在两台主服务器（3307|3308）上停止复制并重置服务器状态

show master status;  

	mysql主服务器默认初始值：
		File：mysql-bin.000001
		Position：154

（3）如果主服务状态不是初始状态，需要重置状态
执行命令：

reset master;

（4）因为之前3308机器设置过从，所以该3308机器应该执行

stop slave;
reset slave;

4.5.2从服务器（3307|3308|3309|3310）设置

需要登录到主服务器3308|3309|3310的客户端
在/usr/local/mysql-5.7.24/bin目录下执行

./mysql -uroot -p -P3308|3309|3310 -h127.0.0.1

（1）在从服务器上（3309、3310）停止复制并重置服务器状态：

stop slave;
reset slave;

（2）设置从服务器的master（相当于是4台都需要设置）
注意：这里的IP和端口要根据自己的实际情况修改
A、设置从服务器3308、3309的主，他们的主均为3307
即从服务器3308和3309客户端上执行如下操作

change master to master_host=‘192.168.235.128’,master_user=‘copy’,

master_port=3307,master_password=‘123456’,

master_log_file=‘mysql-bin.000001’,master_log_pos=154;

B、设置从服务器3307、3310的主，他们的主均为3308
即从服务器3307和3310客户端上执行如下操作

change master to master_host=‘192.168.235.128’,master_user=‘copy’,

master_port=3308,master_password=‘123456’,

master_log_file=‘mysql-bin.000001’,master_log_pos=154;

（3）在从机器上执行开始复制命令(4台MySQL上都执行)
start slave;
（4）至此双主双从就搭建好了

4.6双主双从验证

4.6.1检查从服务器复制功能状态

在从服务器的客户端执行以下命令：show slave status \G;
\G表示格式化输出
如果 Slave_IO_Running 和 Slave_SQL_Running 均为 YES，则表示主从关系正常

4.6.2在主服务器上操作测试数据的复制和同步情况

4.6.3问题：在从库写入数据会怎么样？

假如在从库主动写入数据，主库是不会去从库获取数据的，结果就是主从关系断开，主从效果失效。

解决办法：
方法1：先停了从库slave，再重启从库（无需重置状态reset slave）；
方法2：先停从库slave，再重置状态reset slave; 最后把从库里异常的数据库删除，再重新指定主库。
在从服务器客户端执行

change master to
master_host=‘192.168.235.128-主的IP’,master_user=‘copy’,

master_port=3307-主的端口号,master_password=‘940614’,

master_log_file=‘mysql-bin.000001’,master_log_pos=154;