MySQL主从复制、读写分离

 

 

1.主从复制及读写分离

1.1MySQL复制的应用常见场景

• 读写分离，提高查询访问性能，有效减少主数据库访问压力

• 实时灾备，主数据出现故障时，可快速切换到从数据库

• 数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。

1.2MySQL主从复制原理

传统的MySQL复制提供一个种简单的主-从复制方法。有一个主，以及一个或多个从。主节点执行和提交事务，然后将它们（异步地）发送到从节点，以重新执行（在基于语句的复制中）或应用（在基于行的复制中）。这是一个shared-nothing的系统，默认情况下所有server成员都有一个完整的数据副本

还有一个半同步复制，它在协议中添加了一个同步步骤。这意味着主节点在提交时需要等待从节点确认它已经接收事务。只有这样，主节点才能继续提交操作。

在上面的两个图片中，可以看到传统异步MySQL复制协议（以及半同步）的图形展示。蓝色箭头表示在不同server之间或者server与client应用之间的信息交互。

1.3MySQL主从复制过程

• 开启binlog日志，通过把主库的binlog传送到从库，重新解析应用到从库。

• 复制需要3个线程（dump、io、sql）完成。

• 复制是异步的过程。主从复制是异步的逻辑SQL语句级的复制。

1.4MySQL主从复制前提

• 主服务器一定要打开二进制日志

• 必须两台服务器（或者是多个实例）

• 从服务器需要一次数据初始化

• 如果主从服务器都是新搭建的话，可以不做初始化

• 如果主服务器已经运行很长时间了，可以通过备份将主库数据恢复到从库

• 主库必须要有对从库复制请求的用户

• 从库需要有relay-log设置，存放从主库传送过来的二进制日志show variables like '%relay%';

• 在第一次的时候，从库需要change master to去连接主库。

• change master信息需要存放到master.info中show variables like '%master_info%';

• 从库怎么知道，主库发生了新的变化？通过relay-log.info记录的已经应用过的relay-log信息。

• 在复制过程中涉及到的线程

  • 从库会开启一个IO thread（线程），负责连接主库，请求binlog，接收binlog并写入relay-log

  • 从库会开启一个SQL thread（线程），负责执行relay-log中的事件

  • 主库会开启一个dump thread（线程），负责响应从IO thread的请求

1.5MySQL主从复制实现

• 通过二进制日志

• 至少两台（主、从）

• 主服务器的二进制日志“拿”到从服务器上再运行一遍

• 通过网络连接两台机器，一般都会出现延迟的状态。也可以说是异步。

• 从库通过手工执行change master to语句连接主库，提供连接的用户一切条件user、password、port、ip

• 并且让从库知道，二进制日志的起点位置（file名 position号）

• 启动从库同步服务start slave

• 从库的IO和主库的dump线程建立连接

• 从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求

• 主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程

• 从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中

• 从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info，默认情况下，已经应用过的relay会自动被清理purge

1.6MySQL复制有三种核心格式

• 复制的工作原理是数据库修改记录到bin log日志并传递到slave，然后slave在本地还原的过程。而时间记录到bin log的格式会有所不同。

6.1基于语句的复制（statement based replication）

• 基于主库将SQL语句写入到bin log中完成复制

6.2基于行数据的复制（row based replication）

• 基于主库将每一行数据变化的信息作为时间写入到bin log完成日志。默认就是基于行级别的复制，因为它相对语句复制逻辑更为严谨

6.3混合复制（mixed based replication）

• 上述两者的结合。默认情况下优先使用基于语句的复制，只有当部分语句如果基于语句复制不完全的情况下才会自动切换为基于行数据的复制

# mysql -uroot -p
> show variables like '%binlog_format%';    //默认基于行复制
variable_name   value
binlog_format   ROW
> SET binlog_format='STATEMENT';    //改为基于语句复制

　　

• 一般建议用基于ROW的复制方式

2.MySQL传统主从同步配置

• 环境

  • master：192.168.1.10

  • slave：192.168.1.11

  • 端口3306

  • master，slave按照一下步骤安装mysql数据库

• MySQL5.7初始为root用户随机生成一个密码

• 启动过一次mysql才可以查看临时密码

2.1MySQL安装

# wget https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm
# yum -y install mysql57-community-release-el7-11.noarch.rpm    #安装mysql源
# yum -y install mysql-community-server mysql

# systemctl start mysqld
# systemctl status mysqld

# grep 'password'   /var/log/mysqld.log     //查看初始密码
xxxxxxxxxxx
# mysql -uroot -p'xxxxxxx'
> alter user 'root'@'localhost' identified by   'ABC123.com';   #修改密码

　　

设置简单密码策略(不建议)

> set global validate_password_policy=0;    //不要特殊符号
> set global validate_password_length=1;    //长度最少4位
> alter user 'root'@'localhost' identified by   'ABC123';   #修改密码

MySQL授权远程主机登录

> grant all privileges on *.* to slave@'192.168.%.%' identified by 'A.123com' with grant option; 
> flush privileges;

• WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。

2.2MySQL编辑配置文件

2.2.1Master配置文件

# hostnamectl --static set-hostname mysql-master
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mys
ql
socket=/var/lib/mysql/mysql.sock
defaults-storage-engine=INNODB
symbolic-links=0
server_id=6
log_bin=/var/log/mysql/mysql-bin

# mkdir -p /var/log/mysql
# chown -R mysql:mysql /var/log/mysql
# systemctl restart mysqld
# systemctl status mysqld

　　

2.2.2slave配置文件

# hostnamectl --static set-hostname mysql-slave
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
defaults-storage-engine=INNODB
symbolic-links=0
server_id=8
log_bin=/var/log/mysql/mysql-bin
relay_log=/var/log/mysql/mysql-relay

# mkdir -p /var/log/mysql
# chown -R mysql:mysql /var/log/mysql
# systemctl restart mysqld
# systemctl status mysqld

　　

2.3MySQL创建主从同步账号

• 在主库创建一个专门用来复制的数据库用户，所有从库都用这个用户连接主库，确保这个用户只有复制的权限

master

# mysql -uroot -p
> create user 'slave'@'192.168.%.%' identified by 'A.123com';
> grant replication slave on *.* to 'slave'@'192.168.%.%';
> show grants for 'slave'@'192.168.%.%';

• grant replication slave表示向某个用户授予复制主节点数据的

slaves上登录测试

用slave账号登录主库

# mysql -h192.168.1.10 -uslave -p'A.123com'
> show databases;
> select user,host from mysql.user;     //没有查询权限

　　

master上

> flush tables with read lock;  对主库锁表，停止修改，从库复制过程中主库不能执行update、delete、insert
> show master status;       //主库日志信息

　　

2.4备份主库数据

• --master-data 将二进制日志的位置和文件名写入到输出中

master上备份

# mysqldump -uroot -p'A.123com' --master-data --all-databases > master-all.sql
# cat master-all.sql
# scp master-all.sql 192.168.1.11:/root/

# mysql -uroot -p'A.123com'
> show master status;

• 主库数据备份完毕后，释放主库锁

slave上

导入主库数据

# mysql -uroot -p'A.123com' < master-all.sql
# mysql -uroot -p'A.123com'
> select user,host from mysql.user;

　　

2.5从库配置同步

• 在从库上建立复制关系，即从库指定主库的日志信息和链接信息

//先查看主库日志信息
> change master to
-> master_host='192.168.1.10'，
-> master_port=3306,
-> master_user='slave',
-> master_password='A.123com',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=617;

> start slave;      //启动
> show slave status\G
...
Slave IO Running: No
Slave SQL Runing: Yes
...
Last_IO_Error: Fatal error:... uuid与从库相同

　　

修改uuid（没问题可以忽略这步）

# vim /var/lib/mysql/auto.cnf

# mysql -uroot -p'A.123com'
> show slave status\G
...
Slave IO Running: Yes
Slave SQL Runing: Yes

ps：MySQL服务是主库复制过来的，uuid(/var/lib/mysql/auto.cnf)，server_id(/etc/my.cnf)相同，需修改

测试

master上

> create database test_slave;
> show databases;

　　

slave上

> show databases;