MySQL 主从复制(实时热备)原理与配置

MySQL是现在普遍使用的数据库，但是如果宕机了必然会造成数据丢失。为了保证MySQL数据库的可靠性，就要会一些提高可靠性的技术。MySQL主从复制可以做到实时热备数据。本文介绍MySQL主从复制原理及其配置过程。

术语：主从复制——master slave replication（M-S模式）。

用途

备份
读写分离
高可用和故障切换（需要做HA）
MySQL升级测试

原理

实际上，它的原理思路很简单。MySQL 中有一种日志叫做 bin 日志（二进制日志），这个日志会记录下所有修改了数据库的SQL语句。主从复制的原理其实就是"从"服务器向"主"服务器请求这个日志文件，"主"服务器会把这个 bin 日志复制到"从"服务器上执行一遍，这样"从"服务器上的数据就和"主"服务器上的数据相同了。

主从复制原理
图解：
1.主服务器必须启用二进制日志（log-bin），记录任何修改了数据库数据的事件；
2.从服务器开启一个线程（I/O Thread)把自己扮演成 MySQL 的客户端，通过 MySQL 协议，请求主服务器的二进制日志文件中的事件；
3.主服务器启动一个线程（Dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主服务器就会从第一个日志文件中的第一个事件一个一个发送给从服务器；
4.从服务器接收到主服务器发送过来的数据把它放置到中继日志（relay log）文件中。并记录该次请求到主服务器的具体哪一个二进制日志文件内部的哪一个位置（主服务器中的二进制文件会有多个，其名结尾以6位数递增）；
5.从服务器启动另外一个线程（SQL Thread ），把relay log中的事件读取出来，并在本地再执行一次。

配置过程

主服务器（Master）
1.启用二进制日志log-bin；
2.设置一个全局唯一的 server_id；
3.提前准备好一个有复制权限（replication slave，replication client）的用户。
从服务器（Slave）
1.启动中继日志 relay-log；
2.设置一个全局唯一的 server_id；
3.使用主服务器提供的有复制权限的用户连接至 Master；
启动复制线程。

配置演示

master 配置

1.修改配置文件my.cnf
在mysqld中添加如下内容:

Removerver-id=1
log_bin=master-bin 
log_bin_index=master-bin.index
binlog_do_db=dev

#server-id 服务器唯一标识。
#log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
#binlog_do_db 指定记录需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
#binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

2.在主服务器上创建从服务器的用户和权限
登录mysql

mysql -uroot -p

创建从数据库的masterbackup用户和权限

grant replication slave on *.* to masterbackup@'203.195.224.113' identified by '123456'

退出

exit

3.重启mysql服务

service mysql restart

4.查看主服务器状态
show master status

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 | test         |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

image.png

slave从服务器的配置

1.配置文件my.cnf的修改

server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db=dev

#server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
#relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
#replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
#replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

2.重启MySQL

service mysql restart

3.在从服务器上连接master主服务器

change master to master_host='49.235.110.134',master_port=3306,master_user='masterbackup',master_password='123456',master_log_file='master-bin.000001',master_log_pos=154;

4.启动slave同步数据

start slave

5.查看slave信息

show slave status\G

如图:两个yes是成功

slave数据库配置
本文来自https://www.cnblogs.com/qtt1994/p/11010274.html,亲测有效。