UCloud MySQL与自建MySQL搭建主从同步

1. MySQL主从复制原理

1.1 主从复制简介

在实际的生产中，为了解决MySQL的单点故障，提高MySQL的整体服务性能，一般都会采用「主从复制」。比如：在复杂的业务系统中，有一条SQL执行后导致锁表，并且这条SQL的的执行时间又比较长，那么此SQL执行期间就会导致服务不可用，这样就会严重影响用户的体验度。

主从复制中分为「主服务器（master）「和」从服务器（slave）」，「主服务器负责写，而从服务器负责读」，MySQL的主从复制的过程是一个「异步的过程」。这样读写分离的过程，能够使整体的服务性能大大提升，即使写操作时间比较长，也不影响读操作的进行。

1.2 主从复制的原理

首先放一张MySQL主从复制的原理图，总的来说MySQL的主从复制原理还是比较好理解的，原理非常的简单。

MySQL的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

master（binlog dump thread）主要负责Master库中有数据更新的时候，会按照binlog格式，将更新的事件类型写入到主库的binlog文件中。并且，Master会创建log dump线程通知Slave主库中存在数据更新，这就是为什么主库的binlog日志一定要开启的原因。I/O thread线程在Slave中创建，该线程用于请求Master，Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置的副本。然后，将binlog保存在 「relay log（中继日志）」 中，中继日志也是记录数据更新的信息。SQL线程也是在Slave中创建的，当Slave检测到中继日志有更新，就会将更新的内容同步到Slave数据库中，这样就保证了主从的数据的同步。

以上就是主从复制的过程，当然，主从复制的过程有不同的策略方式进行数据的同步，主要包含以下几种：
(1)「全同步策略」：Master会等待所有的Slave都回应ACK后才会提交事务，这个主从的同步的性能会严重的影响。
(2)「半同步策略」：Master至少会等待一个Slave回应ACK后提交事务。
(3)「异步策略」：Master不用等待Slave回应ACK就可以提交事务。
(4)「延迟策略」：Slave要落后于Master指定的时间后再进行数据同步。

对于不同的业务需求，有不同的策略方案，但是一般都会采用最终一致性，不会要求强一致性，毕竟强一致性会严重影响性能。

1.3 数据同步有关时间点-同步延迟

（1）主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1；
（2）之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2；
（3）备库B执行完成这个事务，我们把这个时刻记为T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是T3-T1。

2. UDB MySQL与自建MySQL搭建主从同步

2.1 准备工作

文章推荐：MySQL YUM安装教程

2.1.1 准备一台UDB数据库和一台云主机自建数据库

MySQL [(none)]>show variables like 'server_id';

2.1.2 下载测试库

测试库下载官网：https://launchpad.net/test-db/

[root@slave ~]# wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
[root@slave ~]# tar -xvjf employees_db-full-1.0.6.tar.bz2
employees_db/._load_departments.dump
employees_db/load_departments.dump
employees_db/load_dept_emp.dump
employees_db/._load_dept_manager.dump
employees_db/load_dept_manager.dump
employees_db/._load_employees.dump
employees_db/load_employees.dump
employees_db/._load_salaries.dump
employees_db/load_salaries.dump
employees_db/._load_titles.dump
employees_db/load_titles.dump
employees_db/._employees.sql
employees_db/employees.sql
employees_db/employees_partitioned.sql
employees_db/._employees_partitioned2.sql
employees_db/employees_partitioned2.sql
employees_db/employees_partitioned3.sql
employees_db/objects.sql
employees_db/test_employees_md5.sql
employees_db/test_employees_sha.sql
employees_db/Changelog
employees_db/._README
employees_db/README

文章推荐：MySQL修改非事务引擎

[root@slave ~]#  cd employees_db/
[root@slave employees_db]# vim employees.sql
set storage_engine = InnoDB;改为
set default_storage_engine = InnoDB;（有3处需要修改,as follows:）

2.2 向UDB MySQL中导入数据

文章推荐：MySQL数据的导入导出

[root@slave ~]#  mysql -uroot -h 10.25.231.152 -p$password -t < employees.sql

2.2.1 导出UDB MySQL数据

[root@slave ~]#  mysqldump -uroot -h10.25.231.152 -p$password --quick --routines --master-data=2 --single-transaction --databases employees  > data.sql

# 对于使用MyISAM存储引擎库表使用：
[root@slave ~]#  mysqldump -h10.25.231.152 -uroot -p$password --master-data=2 -l --databases  db1 > data1.sql

2.2.2 导入数据到自建

[root@slave ~]# mysql -uroot -h10.23.178.34 -p$password < data.sql

2.3 设置数据同步

2.3.1 修改从库配置文件

# 从库配置文件修改（my.cnf）在[mysqld]下增加参数server-id=1（值与主库不一样即可）执行以下命令，使修改生效
[root@slave ~]# vim /etc/my.cnf
server-id=1
innodb_file_per_table=ON
skip_name_resolve=ON
log_bin=mysql-bin

[root@slave ~]# service mysqld reload
[root@slave ~]# systemctl restart mysqld.service
mysql> show global variables like '%log%';

2.3.2 主库授权从库复制账户

# 登陆到主库中创建并授权主从复制用户。

MySQL [(none)]> create user ' slavebackup '@'%' identified by 'ucloudbackup';
MySQL [(none)]> grant replication slave on *.* to slavebackup@'%' identified by 'ucloudbackup';
MySQL [(none)]> flush privileges;

2.3.3 配置主从复制

# 在导出的备份文件data.sql中找到change master to如：（主库信息）
[root@slave ~]# vim data.sql

MySQL [(none)]> SHOW GLOBAL VARIABLES LIKE "%server%";

# 将此语句复制后，登录从库，并补齐为如下命令 :（从库执行）

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', \
MASTER_LOG_POS=234750619, \
MASTER_HOST='10.25.231.152', \
MASTER_PORT=3306, \
MASTER_USER='slavebackup', \
MASTER_PASSWORD='ucloudbackup';

# 从库执行命令

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', \
MASTER_LOG_POS=234750619, \
MASTER_HOST='10.25.231.152', \
MASTER_PORT=3306, \
MASTER_USER='slavebackup', \
MASTER_PASSWORD='ucloudbackup';

master_host             # 主服务器地址;
master_port             # 主服务器端口（不要指定双引号）;
master_log_file       # 指定从主服务器哪个二进制日志文件开始复制;
master_log_pos      # 指定从主服务器哪个二进制日志文件的位置开始复制(不需要双引号);
master_user            # 连接到主服务器的用户;
master_password   # 连接到主服务器的用户密码;

# 设置从库延迟3600s从主库复制

CHANGE MASTER TO MASTER_DELAY = 3600, \
MASTER_LOG_FILE='mysql-bin.000002', \
MASTER_LOG_POS=234750619, \
MASTER_HOST='10.25.231.152', \
MASTER_PORT=3306, \
MASTER_USER='slavebackup', \
MASTER_PASSWORD='ucloudbackup';

2.3.4 启动slave

# PORT默认3306时可以不要指定，然后执行slave start。

mysql> start slave;
mysql> show slave status\G;

# 通过排查从库日志发现GTID未开启

[root@slave ~]# tail /var/log/mysqld.log;

文章推荐：MySQL GTID全局事物标识（开启slave节点GTID）

【注】开启GTID后，重新执行start slave即可恢复正常。

2.4 主从复制从库是否记录binlog

MySQL [(none)]> show variables like "log_slave_updates";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

注：从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。

作者：UStarGao
链接：https://www.starcto.com/mysql/112.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面