mysql数据库复制--主从复制配置教程

复制功能：

• 数据分布
• 负载均衡（读）
• 备份
• 高可用和故障切换
• MySQL升级测试

复制方式：

• 主–从复制

复制原理：

Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。
主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

复制流程图：

复制过程：

• 主节点必须启用二进制日志，记录任何修改了数据库数据的事件。
• 从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件
• 主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
• 从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个，在后面详细讲解）。
• 从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。

复制中线程的作用：

从节点：

• I/O Thread: 从 Master 节点请求二进制日志事件，并保存于中继日志中。
• Sql Thread: 从Relay log 中读取日志事件并在本地完成重放。

主节点

• Dump Thread:为每个 Slave 的 I/O Thread 启动一个 dump 线程，用于向从节点发送二进制事件。

思考：从节点需要建立二进制日志文件吗？

看情况，如果从节点需要作为其他节点的主节点时，是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点，则不需要创建二进制文件。

Mysql复制特点：

• 异步复制：主节点中一个用户请求一个写操作时，主节点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成，再响应用户。主节点只需要把写入操作在本地完成，就响应用户。但是，从节点中的数据有可能会落后主节点，可以使用（很多软件来检查是否落后）
• 主从数据不一致。

主从复制配置过程：

主节点：

• 启用二进制日志。
• 为当前节点设置一个全局唯一的server_id。
• 创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

• 启动中继日志。
• 为当前节点设置一个全局唯一的server_id。
• 使用有复制权限的用户账号连接至主节点，并启动复制线程。

配置演示：

测试环境：
主节点：192.168.45.128
从节点：192.168.45.129
mysql版本：mysql-5.7.29
linux：centos7.4

1：编辑主节点配置文件

[root@localhost softweb]# vim /etc/my.cnf

2：启用二进制日志文件，在指定位置加入以下代码并保存修改

log-bin = mysql-bin
server-id =1
innodb-file-per-table =ON
skip_name_resolve=ON

3：重启mysql，下面是centos7以上的重启命令，以下版本的请自行查找

[root@localhost softweb]# systemctl restart mysqld

4：查看二进制日志是否开启

1.输入账号密码进入mysql

[root@localhost softweb]# mysql -uroot -p你的mysql用户密码

2.查看我们的二进制日志是否开启，看见log_bin为on则是开启的状态

mysql> SHOW GLOBAL VARIABLES LIKE '%log%';

3.查看主节点二进制日志列表

mysql> show master logs;

4.查看主节点的server id

mysql> show global variables like '%server%';

5.在主节点上创建有复制权限的用户。REPLIACTION SLAVE ，REOPLIATION CLIENT

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '从机用户名'@'从机ip' IDENTIFIED BY '从机用户名对应的密码';

6.刷新

mysql> FLUSH PRIVILEGES;

主服务器配置完成。

注意：确保主服务器的iptables没有阻断3306的访问端口。

5：编辑从节点配置文件

[root@localhost softweb]# vim /etc/my.cnf

新增以下配置信息

lower_case_table_names=1
relay-log=relay-log
relay-log-index=relay-log.index
server-id=2
innodb_file_per_table=ON
skip_name_resolve=ON

重启mysql

[root@localhost /]# systemctl restart mysqld

1.查看从节点日志信息

mysql> SHOW GLOBAL VARIABLES LIKE '%log%';

2.查看从节点server 相关信息

mysql> show global variables like '%server%';

3.在从节点配置访问主节点的参数信息
添加 主节点主机，访问主节点的用户名及密码，主节点二进制文件信息。
注意：主节点的二进制文件一定要是二进制列表中的最后一个二进制文件。

mysql> CHANGE MASTER TO MASTER_HOST='主机ip',MASTER_USER='主机用户名',MASTER_PASSWORD='主机用户名对应的密码', MASTER_LOG_FILE='主机二进制制列表最后一个二进制文件名',MASTER_LOG_POS=对应File_size;

6.启动线程复制

mysql> START SLAVE;

START SLAVE 可以指定线程类型：IO_THREAD ,SQL_THREAD, 如果不指定，则两个都启动。

至此，mysql的主----从复制配置完成。

7.测试使用

在主机里面新建数据库和表并插入一定数据

查看从机数据库是否与主机数据库和表结构一致，一致则代表主从复制配置成功

主从复制架构中应注意的问题：

从节点要设置某些限定使得它不能进行写操作,才能保证复制当中的数据一致。

1：限制从服务器为只读

在从服务器上设置：
read_only = ON,但是此限制对拥有SUPER权限 的用户均无效。
阻止所有用户：

mysq>FLUSH TABLES WITH READ LOCK;

2:如何保证主从复制时的事物安全？

1：在主节点设置参数

sync_binlog=1： Mysql开启bin-log日志使用bin-log时，默认情况下，并不是每次执行写入就与硬盘同步，这样在服务器崩溃时，就可能导致bin-log最后的语句丢失。可以通过这个参数来调节，sync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。
如果用到innode 存储引擎:
innodb_flush_logs_at_trx_commit=ON（刷写日志：在事务提交时，要将内存中跟事务相关的数据立即刷写到事务日志中去。）
innodb_support_xa=ON (分布式事务：基于它来做两段式提交功能)
sync_master_info=1：每次给从节点dump一些事件信息之后，主节点的master info 信息会立即同步到磁盘上。让从服务器中的 master_info 及时更新。

2：在每个slave节点

skip_slave_start =ON (跳过自动启动，使用手动启动。)
relay_log也会在内从中先缓存，然后在同步到relay_log中去，可以使用下面参数使其立即同步。
sync_relay_log =1 ，默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。
sync_relay_log_info=1每间隔多少事务刷新relay-log.info，如果是table（innodb）设置无效，每个事务都会更新

注： 在从节点中 master.info是记录在主节点复制位置的文件。
relylog_info: 本地将来至于主节点的哪一个二进制文件中position并且保存文本地哪一个中继日志中的哪一个postion. 从节点启动时也需要根据relay-log.info定位本地relay-log.