文章目录
MySQL读写分离
一个数据库服务器用来写入，另外一个数据库或多个数据库用来查询。来增强数据库的性能。
这就要求数据库中的数据，要从主数据库同步到其他从数据库。MySQL的这种主从的数据同步，是通过mysql的binlog来实现的。通过把主库上面的binlog复制到其他从库中，在从库中对复制过来的binlog日志进行重放，数据就从主库被复制到从库了。
目前的复制方式有以下两种：
- 基于日志点的复制
- 基于GTID的复制
基于日志点的复制
主从服务器的配置
创建同步用的用户
下面的操作需要在主数据库中执行，创建一个用户，并且给这个用户授予同步的权限。
该用户的作用就是让从库通过这个用户来连接到主库中执行同步任务。
create user 'repl'@'%' identified by '123456';
grant replication slave, replication client on *.* to 'repl'@'%';
备注：之所以增加“replication client”的权限的目的是为了以后方便组从切换，以备不时只需。
主服务器配置
修改配置主数据库的/etc/my.cnf文件。需要主库开启binlog，否则不能实现读写分离主从复制。
[mysqld]
#集群中的数据库唯一标识
server-id=1
#binlog的位置和名字，建议主库和从库的binlog的文件名称要一致，避免以后可能需要来回切换或者克隆新库到时候使用不同的binlog日志名称而出现的各种问题。
log-bin=/var/lib/mysql/mysql-bin
#中继日志目录和文件
relay_log=/var/lib/mysql/mysql-relay-bin
#binlog的格式
binlog_format=row
#binlog记录的方式只记录修改的字段。不修改的字段不记录。
binlog_row_image=minimal
注意：当配置主从同步的时候，建议为每一个服务器不管是主还是从，只要涉及到binlog或者relaylog，都为这些log配置具体的路径和日志文件的base-name，避免日后这些log可能会用来创建新的备库或者主从切换。
从服务器配置
[mysqld]
#集群中的数据库唯一标识
server-id=2
#binlog的位置和名字，从库可以不启用binlog，为了提高从库同步主库数据的效率。但是如果从库需要作为另外一个从库的主库，则需要打开binlog。
#log-bin=/var/lib/mysql/mysql-bin
#binlog的格式，
#binlog_format=row
#binlog_row_image=minimal
#如果从数据库，要作为另外一个数据库的主库，打开下面的选项，该选项的含义是讲从库上重放主库的SQL事件记录到自己的二进制日志中。启用该参数的前提是，启用了该从库的二进制日志功能。
#log_slave_updates=on
#中继日志目录和文件
relay_log=/var/lib/mysql/mysql-relay-bin
#设置数据库为只读模式，不能有插入修改操作。从库中的super权限的用户不受下面的readonly选项的影响，仍然可以写。
read_only=on
历史数据的同步
在主库已经存在很多数据，则需要把数据从主库中导出来。然后在导入到从库中去。然后再开始同步数据。
把数据从主库中导出
如果从库数据库版本和主库不一致，则只能导出我们自己的业务数据。mysql自带的系统数据库，不建议导出。不然在向从库中导入的时候可能会产生版本问题。
mysqldump -uroot -p123456 \
--master-data=2 \
--single-transaction \
--flush-logs \
--triggers \
--routines \
--events \
-B mydb1 > mydb1.sql
如果主从数据库版本一致，可以导出所有。如下：
mysqldump -uroot -p123456 \
--master-data=2 \
--single-transaction \
--flush-logs \
--triggers \
--routines \
--events \
--all-databases > alldb.sql
在从库中导入主库的数据
把待导入的数据，传到从库的服务器上(可以考虑使用scp的命令把待导入的SQL文件拷贝到从服务器上)。
然后导入主库中的数据。
mysql -uroot -p123456 < mydb1.sql
配置和启动同步链路
配置同步链路
在从库数据库中执行如下语句
change master to master_host='172.31.56.193',
master_user='repl',
master_password='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154;
其中各个参数含义如下：
- master_host：主库的IP地址
- master_user：用于执行同步任务的主库中的用户名
- master_password：用于执行同步任务的主库中的用户密码
- master_log_file：从主库的那个binlog文件开始同步，这个binlog文件到底是哪一个，可以从同步主库历史数据到从库的时候，使用mysqldump手动导出数据的时候，生成的.sql文件中看到。要求在mysqldump导出的时候，使用了–master-data=2这个参数。这样才会在生成的.sql文件中包含master_log_file参数所需要的值。
- master_log_post：同上面的master_log_file参数的获取方式。
启动同步链路
在从数据库中执行如下语句
- 查看同步任务状态
show slave status \G
- 启动同步任务
start slave;
- 查看同步任务状态
show slave status \G
日志点复制的优缺点
日志点复制的优点
- 对mysql中只用对任何sql语句都支持，基于GTID的复制，并不是支持所有的sql语句。
- 比较成熟，bug相对少。
- 出现同步的故障处理方式比较多。
日志点复制的缺点
- 发生主从同步问题的时候，不容易找到每一个从库的日志点应该从哪里开始。
- 一主多从多数据库环境中，这个问题对每一个从库都要重新定位。
基于GTID的复制
从MySQL5.6版本才支持。
GTID：全局事物ID。
GTID=source_id:transaction_id
主从服务器的配置
创建同步用的用户
下面的操作需要在主数据库中执行，创建一个用户，并且给这个用户授予同步的权限。
该用户的作用就是让从库通过这个用户来连接到主库中执行同步任务。
create user 'repl'@'%' identified by '123456';
grant replication slave, replication client on *.* to 'repl'@'%';
主服务器配置
修改配置主数据库的/etc/my.cnf文件。需要主库开启binlog，否则不能实现读写分离主从复制。
[mysqld]
#集群中的数据库唯一标识
server-id=1
#binlog的位置和名字
log-bin=/var/lib/mysql/mysql-bin
#启用gtid
gtid_mode=on
#下面的参数决定不能使用 creat table as select * from 和 create temporty table
enforce_gtid_consistency=on
#gtid模式必须配置下面为on，5.7以后的版本可以不配置该参数，5.6的版本比较开启该功能。
#log_slave_updates=on
#中继日志文件
relay_log=/var/lib/mysql/mysql-relay-bin
#binlog的格式
binlog_format=row
#binlog记录的方式只记录修改的字段。不修改的字段不记录。
binlog_row_image=minimal
从服务器配置
[mysqld]
#集群中的数据库唯一标识
server-id=2
#binlog的位置和名字
log-bin=/var/lib/mysql/mysql-bin
#启用gtid
gtid_mode=on
#下面的参数决定不能使用 creat table as select * from 和 create temporty table
enforce_gtid_consistency=on
#gtid模式必须配置下面为on，5.7以后的版本可以不配置该参数，5.6的版本比较开启该功能。
#log_slave_updates=on
#建议下面这两个也配置上，主从同步的信息记录在表中，而不是文件中。当任务出现问题的时候，从哪里同步可以参考这两个表。这两张表默认在mysql这个schema下面。对应的表分别为mysql.slave_master_info与mysql.slave_relay_log_info，且这两个表均为innodb引擎表。
master_info_repository=TABLE
relay_log_info_repository=TABLE
#binlog的格式
binlog_format=row
binlog_row_image=minimal
#中继日志文件
relay_log=/var/lib/mysql/mysql-relay-bin
#如果从数据库，要作为另外一个数据库的主库，打开下面的选项
#log_slave_update=on
#read_only=on
- master_info_repository=TABLE，增加这个配置后，会在mysql这个schema下面创建一个slave_master_info表，用于记录master的信息。如果不配置该选项，则默认在MySQL的datadir变量所指向的目录下面创建一个master.info的配置文件，在里面记录master的信息。
- relay_log_info_repository=TABLE，增加这个配置后，会在mysql这个schema下面创建一个slave_relay_log_info表，用于记录relay-log的信息。如果不配置该选项，则默认在MySQL的datadir变量所指向的目录下面创建一个relay-log.info的配置文件，在里面记录relay-log的信息。
历史数据的同步
在主库已经存在很多数据，则需要把数据从主库中导出来。然后在导入到从库中去。然后再开始同步数据。
把数据从主库中导出
如果从库数据库版本和主库不一致，则只能导出我们自己的业务数据。mysql自带的系统数据库，不建议导出。不然可能会产生版本问题，在向从库中导入的时候。
mysqldump -uroot -p123456 --master-data=2 --single-transaction -B mydb1 > mydb1.sql
如果主从数据库版本一致，可以导出所有。如下：
mysqldump -uroot -p123456 --master-data=2 --single-transaction --all-databases > alldb.sql
在从库中导入主库的数据
把待导入的数据，传到从库的服务器上。然后导入主库中的数据。
mysql -uroot -p123456 < mydb1.sql
配置和启动同步链路
配置同步链路
在从库数据库中执行如下语句
change master to master_host='172.31.56.193',
master_user='repl',
master_password='123456',
master_auto_potision=1;
其中各个参数含义如下：
- master_host：主库的IP地址
- master_user：用于执行同步任务的主库中的用户名
- master_password：用于执行同步任务的主库中的用户密码
- master_auto_postision：同步开始的日志点，这个是GTID复制和日志点复制重要的一个区别。
启动同步链路
在从数据库中执行如下语句
查看同步任务状态
show slave status \G;
启动同步任务
start slave;
GTID复制的优缺点
GTID复制的优点
- 可以方便的进行故障转移
当主库挂掉后，在一主多从的集群中，选择一个从库来作为主库，其他从库可以很明确的确认应该从新的主库的二进制日志文件的什么位置开始同步。这个是基于日志同步的时候所不能做到的。 - 从库不会丢失主库的任何修改
是基于事物ID来进行同步和复制的。
GTID复制的缺点
- 故障处理比较麻烦
- 对主库中执行的SQL语句有限制
- 不能在同一个事物中执行
- 在主库中不能使用下的这样的SQL语句，如果应用中有这样的SQL就有问题了。
#从一个表作为模版创建另外一张表，只能替换为先创建表，再 insert into select 的方式来实现。
create table A as select * from B;
#创建临时表也不能使用了。
create temporary table C(id int, name varchar(16));
- 使用关联更新事物表和非事物表不再支持
微信搜索“coder-home”或扫一扫下面的二维码，
关注公众号，第一时间了解更多干货分享，还有各类视频教程资源。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
