MySQL主从复制

  

1 主从复制概述

  在实际生产中，数据的重要性不言而喻；如果数据库只有一台服务器，那么很容易产生单点故障的问题，比如这台服务器访问压力过大而没有响应或者奔溃，那么服务就不可用了，再比如这台服务器的硬盘坏了，那么整个数据库的数据就全部丢失了，这是重大的安全事故；

  为了避免服务的不可用以及保障数据的安全可靠性，我们至少需要部署两台或两台以上服务器来存储数据库数据，也就是我们需要将数据复制多份部署在多台不同的服务器上，即使有一台服务器出现故障了，其他服务器依然可以继续提供服务；

  MySQL提供了主从复制功能以提高服务的可用性与数据的安全可靠性；主从复制是指服务器分为主服务器和从服务器，主服务器负责读和写，从服务器只负责读，主从复制也叫 master/slave，master是主，slave是从；
  主从复制可以实现对数据库备份和读写分离；

2 主从复制架构

一主多从架构：

双主双从架构：

3 主从复制原理

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

4 MySQL环境搭建

4.1 下载

wget https://cdn.mysql.com/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

4.2 安装前环境检查

1、检查Linux是否安装了mariadb数据库，mariadb数据库是mysql的分支，执行命令：

yum list installed | grep mariadb 
rpm -qa | grep mariadb

2、若Linux中安装了mariadb数据库，先卸载掉，mariadb数据库与安装mysql会发生冲突，执行命令

yum -y remove mariadb-libs.x86_64
或者
rpm -e mariadb-libs-5.5.52-1.el7.x86_64
rpm -e postfix-2:2.10.1-6.el7.x86_64 

（其中mariadb-libs.x86_64是第1步搜索出来的mariadb软件包，不同机器可能不一样）

4.3 安装

1、解压下载下来的mysql软件压缩包，执行命令：

tar -zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
（其中 -C /usr/local 是指定解压到哪个目录下去）

2、将解压后的mysql-5.7.18-linux-glibc2.5-x86_64改名为mysql-5.7.18 或者 mysql，为了一眼看出mysql的版本，建议改名为mysql-5.7.18

mv mysql-5.7.18-linux-glibc2.5-x86_64 mysql-5.7.18

3、在mysql-5.7.18文件夹目录下创建一个/data/3306/文件夹，切换到mysql-5.7.18目录，执行命令；

mkdir data，然后再data目录下再创建 3306 文件夹

4、添加mysql用户及用户组，执行命令：

groupadd mysql
useradd mysql -g mysql
-g: 是指定用户所在组

5、切换到mysql-5.7.18/bin目录下执行：

./mysqld --initialize-insecure --user=mysql --datadir=/usr/local/mysql-5.7.18/data/3306 --basedir=/usr/local/mysql-5.7.18
其中 --initialize-insecure 表示不生成临时密码

6、在mysql-5.7.18/bin目录下执行命令：

./mysql_ssl_rsa_setup --datadir=/usr/local/mysql-5.7.18/data/3306

7、更改mysql-5.7.18整个文件夹目录权限所属，执行命令：

chown -R mysql:mysql /usr/local/mysql-5.7.18/

4.4 配置

在mysql-5.7.18/data/3306 目录下创建my.cnf文件

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

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

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

4.5 启动

启动MySQL服务，在mysql-5.7.18/bin目录下执行命令：

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3306/my.cnf &
（其中 & 符号表示后台启动）

4.6 启动后的注意事项

4.6.1 修改密码

1、登录进入mysql，在mysql-5.7.18/bin目录下执行命令：

./mysql -uroot -p -S /usr/local/mysql-5.7.18/data/3306/mysql.sock
其中 -p 是指定密码，没有密码，则可以不写 -p，-S是指定sock文件
./mysql -uroot -p -P3306 -h127.0.0.1

2、修改mysql的密码，执行：

alter user 'root'@'localhost' identified by '123456';
（其中123456是我们设置的密码）

4.6.2 远程访问

1、授权远程访问，执行命令：（这样远程客户端才能访问）

grant all privileges on *.* to root@'%' identified by '123456';

其中*.* 的第一个*表示所有数据库名，第二个*表示所有的数据库表；
root@'%' 中的root表示用户名，%表示ip地址，%也可以指定具体的ip地址，
比如root@localhost，root@192.168.10.129

2、执行以下如下命令刷新权限：

flush privileges; 

3、如果上面授权远程访问执行了之后依然远程连接不上mysq，可能是Linux上防火墙拦截了，可以将防火墙先关闭关闭防火墙命令：

systemctl stop firewalld
（该命令是临时关闭防火墙，重启centos后失效） 

4.7 关闭

关闭MySQL服务，在mysql-5.7.18/bin目录下执行：

./mysqladmin -uroot -p shutdown -S /usr/local/mysql-5.7.18/data/3306/mysql.sock 
输入密码关闭

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

4.8 CentOS6.9安装后注意事项

1、修改 /etc/my.cnf 文件，把里面的路径全部指向到mysql安装主目录的data目录下，即：/usr/local/mysql-5.7.18/data，见如下示例：

[mysqld]
datadir=/usr/local/mysql-5.7.18/data
socket=/usr/local/mysql-5.7.18/data/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/usr/local/mysql-5.7.18/data/mysqld.log
pid-file=/usr/local/mysql-5.7.18/data/mysqld.pid

2、建立软链接，执行命令：

ln -s /usr/local/mysql-5.7.18/data/mysql.sock /tmp/mysql.sock

5 MySQL多实例搭建

5.1 多实例概述

  MySQL多实例是指安装MySQL之后，在一台Linux服务器上同时启动多个MySQL数据库（实例），不需要安装多个MySQL（适合研究学习的场景）；如果是有多台Linux服务器，那么我们需要每台服务器都分别安装MySQL；（适用于实际线上生产环境）,在一台Linux上启动多个MySQL，这样适合研究和学习，如果要在多台Linux分别启动MySQL，与在一台机器上的配置与操作都是完全一样的；

5.2 多实例配置

1、在MySQL安装主目录下创建 /data/3307, /data/3308, /data/3309, /data/3310 四个目录；
2、执行数据库初始化，在MySQL的/usr/local/mysql-5.7.18/bin目录下执行命令：

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

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

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

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

其中 initialize-insecure 表示不生成MySQL数据库root用户的随机密码，即root密码为空；
3、在/data/3307, /data/3308, /data/3309, /data/3310 四个目录下分别创建一个my.cnf文件；

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

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

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

5.3 多实例启动

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

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3307/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3308/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3309/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3310/my.cnf &

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

5.4 启动后配置

1、登录进入mysql，在mysql-5.7.18/bin目录下执行命令：使用用端口、主机登录

./mysql -uroot -p -P3307 -h127.0.0.1 登录进入MySQL

2、修改mysql的密码，执行：

alter user 'root'@'localhost' identified by '123456';

（其中123456是我们设置的密码）
3、授权远程访问，执行命令：（这样远程客户端才能访问）

grant all privileges on *.* to root@'%' identified by '123456';

其中*.* 的第一个表示所有数据库名，第二个表示所有的数据库表；
root@’%’ 中的root表示用户名，%表示所有ip地址，%也可以指定具体的ip地址，比如root@localhost，root@192.168.10.129
4、执行以下如下命令刷新权限：

flush privileges; 

5.5 多实例关闭

切换到/usr/local/mysql-5.7.18/bin目录下，使用 mysqladmin 命令 shutdown
方式一：./mysqladmin -uroot -p -P3307 -h127.0.0.1 shutdown
方式二: 如果已经进入到了MySQL的命令行，可以直接执行 shutdown; 
退出MySQL命令行，执行： exit

6 MySQL多实例搭建

6.1 概述

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

6.2 环境配置

主(3307)里面加入
log-bin=mysql-bin   #表示启用二进制日志
server-id=3307        #表示server编号，编号要唯一

从(3308)里面加入
server-id=3308        #表示server编号，编号要唯一

从(3309)里面加入
server-id=3309        #表示server编号，编号要唯一

从(3310)里面加入
server-id=3310        #表示server编号，编号要唯一

6.4 服务启动

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3307/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3308/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3309/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3310/my.cnf &

6.5 主从设置

主服务器需要进行:

1、在主服务器上创建复制数据的账号并授权：
	grant replication slave on *.* to 'copy'@'%' identified by '123456';
2、在主服务器上执行命令，查看主服务器状态：
	show master status;
	mysql初始值：File  mysql-bin.000001 Position 154 
	Position表示从哪个位置开始复制
3、如果主服务状态不是初始状态，建议重置状态，执行命令：
	reset master;

从服务器需要进行:

4、查看从服务器状态：
	show slave status;
	mysql初始值：Empty set 
5、如果从服务器不是初始状态，建议重置一下：
	stop slave;
	reset slave;
6、在从服务器上执行命令，设置从服务器的master
	change master to master_host='192.168.187.128',master_user='copy',
	master_port=3307,master_password='123456',
	master_log_file='mysql-bin.000001',master_log_pos=154;
7、在从机器上执行开始复制命令：
	start slave;

6.6 主从验证

检查从服务器复制功能状态：
show slave status \G
如果 Slave_IO_Running 和 Slave_SQL_Running 均为 YES，则表示主从关系正常；
在主服务器上创建数据库、表、数据，然后在从服务器上查看是否已经复制
以上操作过程若显示正常，则主从服务器配置完成；
查看主从复制binlog日志文件内容，可执行：
show binlog events in 'mysql-bin.000001';

7 双主双从环境搭建

7.1 概述

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

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

7.2 环境配置

1、与一主多从一样，所有主节点配置文件加上binlog配置
	3307:log-bin=mysql-bin
		 server-id=3307
	3308:log-bin=mysql-bin
		 server-id=3308
	3309:server-id=3309
	3310:server-id=3309

2、第一台主服务器3307的my.cnf文件增加如下配置：(Master 3307)
auto_increment_increment=2
auto_increment_offset=1
log-slave-updates
sync_binlog=1

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的
信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将
binlog_cache中的数据强制写入磁盘。

1、从库只开启log-bin功能，不添加log-slave-updates参数，从库从主库复制的数据不会写入
	log-bin日志文件里。
2、直接向从库写入数据时，是会写入log-bin日志的。
3、开启log-slave-updates参数后，从库从主库复制的数据会写入log-bin日志文件里。
	这也是该参数的功能。

第二台主服务器3308的my.cnf文件增加如下配置：(Master 3308)
auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1

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
3、注意auto_increment_offset的设置，不同的master设置不应该一样，否则就容易引起主键冲突，
比如master1的offset=1，则master2的offset=2，master3的offset=3
4、在双主模式中，log-slave-updates 配置项一定要配置，否则在master1（3307）上进行了
更新数据，在master2（3308）和slave1（3309）上会更新，但是在slave2（3310）上不会更新
5、sync_binlog表示每几次事务提交，MySQL把binlog缓存刷进日志文件中，默认是0，最安全的是设置为1；O不会立刻同步，1会立刻同步

7.3 服务启动

进入/usr/local/mysql-5.7.18/bin目录，重启四个MySQL服务，启动时指定配置文件启动：
./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3307/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3308/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3309/my.cnf &

./mysqld_safe --defaults-file=/usr/local/mysql-5.7.18/data/3310/my.cnf &

7.4 主从设置

主服务器需要进行

1、在两台主服务器上创建复制账号并授权：（3307、3308）
使用端口、主机登录进入两台主MySQL：
 ./mysql -uroot -p -P3308 -h127.0.0.1
 在主服务器上创建复制数据的账号并授权：
 grant replication slave on *.* to 'copy'@'%' identified by '123456';

2、在两台主服务器上停止复制并重置服务器状态：（3307、3308）
在MySQL命令行执行：
查看3307和3308两台主服务器的状态：show master status;  
mysql初始值：File  mysql-bin.000001 Position 154 
	Position表示从哪个位置开始复制
3、如果主服务状态不是初始状态，建议重置状态，执行命令：
	reset master;

由于之前3308机器设置过从，所以该3308机器应该执行：

stop slave;停止复制
reset slave;重置从服务器状态，恢复到出厂设置:stop slave;reset slave;

从服务器需要进行

3、在从服务器上停止复制并重置服务器状态：（3309、3310）
在MySQL命令行执行：stop slave;reset slave;
4、在Slave上设置Master（相当于是4台都需要设置）

设置从服务器3308、3309，他们的主均为3307，即在3308和3309上执行如下操作：
change master to master_host='192.168.187.128',
master_user='copy',
master_password='123456',
master_port=3307, 
master_log_file='mysql-bin.000001',
master_log_pos=154;

设置从服务器3307、3310，他们的主均为3308，即在3307和3310上执行如下操作：
change master to master_host='192.168.187.128',
master_user='copy',
master_password='123456',
master_port=3308, 
master_log_file='mysql-bin.000001',
master_log_pos=154;	

5、在四台MySQL服务器上执行：start slave;  --开始复制（MySQL命令行执行）

执行后即开始进入主从复制状态，至此双主双从就搭建好了

7.4 主从验证

检查从服务器复制功能状态，执行命令：
show slave status \G
如果Slave_IO_Running和Slave_SQL_Running的值均为Yes，
即表示复制功能配置正常；
测试数据的复制和同步情况