数据库之 Mysql主从异步复制

原创

ghbSunny 2018-01-24 10:20:11 博主文章分类：数据库维护 ©著作权

©著作权归作者所有：来自51CTO博客作者ghbSunny的原创作品，请联系作者获取转载授权，否则将追究法律责任

1 概述

master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

主从服务器，所有的服务器的server id都要不一样

从服务器通过IO thread复制数据，主服务器上授权复制的账号需要拥有replication client 和replication slave的权限。

从服务器启用的时间点很关键，如果主从服务器都是从干净的服务器开始运行，则从服务器不需要做其他操作，如果是主服务器运行一段时间后，从服务器才开始运行，此时主服务器上已经有数据，需要在主节点上做备份，然后把备份在从服务器上恢复，并把没有备份的二进制文件重放到从服务器上，实现数据同步

2 配置

时间同步；

复制的开始位置：

从0开始；

从备份中恢复到从节点后启动的复制，复制的起始点备份操作时主节点所处的日志文件及其事件位置；

主从服务器mysqld程序版本不一致？

从服务器的版本号高于主服务器的版本号；

主服务器：

配置文件my.cnf

server_id=#

log_bin=log-bin

启动服务：

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';

mysql> FLUSH PRIVILEGES;

从服务器：

配置文件my.cnf

server_id=#

relay_log=relay-log

read_only=ON #将从服务器设定为只读模式，#注意，从服务器建议启用read_only：但仅对没有管理权限的用户有效，如对root账号不生效

启动服务：

mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;

mysql> START SLAVE [IO_THREAD|SQL_THREAD];

mysql> SHOW SLAVE STATUS;

3 例子

3.1 主从异步复制，数据库都是初始化状态，仅实现异步复制

主服务器：192.168.1.71

从服务器：192.168.1.73

服务器同步时间，如果skip_name_resolve开启，彼此要能解析主机名

主从复制的示例

执行如下操作，模拟主从都是干净的数据库,即路径/var/lib/mysql下没有文件

[root@CentOS7A ~]#systemctl stop mariadb

[root@CentOS7A ~]#rm -rf /var/lib/mysql/*

配置文件

主服务器，定义二进制文件路径，不特别指定，就在默认的数据目录下/var/lib/mysql下，和显示定义server_id,主从上server_id不能一样

[root@CentOS7A mysql]#vim /etc/my.cnf.d/server.cnf

[server]

skip_name_resolve = ON

innodb_file_per_table = ON

max_connections = 20000

log_bin = master-log

server_id = 1

启动服务

[root@CentOS7A ~]#systemctl start mariadb;

从节点配置，启用中继日志和显示定义server_id，和主服务器上不一致即可，如果不需要复制数据给其他主机，则从服务器不需要启用二进制日志文件，从服务上启用二进制是允许的，但是不做级联复制，不需要启用二进制log，否则会造成服务器压力很大，因为IO访问大

[root@CentOS7C ~]#vim /etc/my.cnf.d/server.cnf

[server]

skip_name_resolve = ON

innodb_file_per_table = ON

max_connections = 2000

relay_log = relay-log

server_id = 2

启动服务

[root@CentOS7C ~]#systemctl start mariadb

主节点71创建账号，该账号用来授权复制操作

MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'192.168.1.%' identified by "Pass123456";

MariaDB [(none)]> flush privileges;

主服务上查看当前二进制文件的位置，一般是从245开始，这里是创建了一个账号，所以从497开始，如果从服务器不需要这个账号，复制时直接从497开始复制就可以，如果需要该账号，可以从245字节开始复制

MariaDB [(none)]> show master status;

+-------------------+----------+--------------+------------------+

+-------------------+----------+--------------+------------------+

| master-log.000001 | 497 | | |

+-------------------+----------+--------------+------------------+

1 row in set (0.01 sec)

mysql上查看帮助的命令直接用help，如change master to的帮助文档，查看相关参数，如选项等

MariaDB [(none)]> help change master to;

从服务器73上执行复制操作，

设定复制参数（此时还没启动），指定复制的主服务器主机，账号，密码，和复制的二进制文件和从二进制的哪个位置开始复制

MariaDB [(none)]> change master to master_host='192.168.1.71',master_user='repluser',master_password='Pass1234',master_log_file='master-log.000001',master_log_pos=497;

查看从节点的复制状态；

MariaDB [(none)]> show slave status\G;

相关参数介绍如下

Slave_IO_Running 指明复制线程

Slave_SQL_Running指明数据重放线程

Seconds_Behind_Master指明数据落后于主服务器的时间，以此验证数据是否落后于主服务器

Exec_Master_Log_Pos二进制文件在本地重放的位置

Slave_IO_Running 指明IO_thread状态

Slave_SQL_Running指明sql_thread状态

启动线程操作，如果不指定线程，默认为io_thread,sql_thread，如果要停止，用stop slave进行停止

MariaDB [(none)]> start slave io_thread,sql_thread;

此时线程已经启动，当主服务器发生变化时，从服务器就会复制相关数据

在主服务器71上做修改，测试复制功能正常与否

MariaDB [(none)]> create database sunnydb;

从服务器上进行查看是否已经有sunnydb库，如果有，则复制正常

MariaDB [(none)]> show databases;

到这里，主从复制就完成。

3.2 主从异步复制，且实现数据备份恢复后同步

假设服务器已经运行一段时间，有数据，即71服务上已经有数据，但是从服务器73是干净的服务器

如果主服务器已经运行一段时间，数据量已经很大，而且二进制log很大，建议在主服务器上做全量备份，然后再到从服务器上恢复备份。然后再实现主从复制

配置主服务

[root@CentOS7A mysql]#vim /etc/my.cnf.d/server.cnf

[server]

skip_name_resolve = ON

innodb_file_per_table = ON

max_connections = 20000

log_bin = master-log

server_id = 1

启动服务

[root@CentOS7A ~]#systemctl start mariadb;

授权账号

MariaDB [(none)]> grant replication client,replication slave on *.* to 'sunnycopy'@'192.168.1.%' identified by 'Pass123456';

MariaDB [(none)]> flush privileges;

逻辑的全量备份，使用温备，记录二进制文件的位置--master-data

mysql服务器正常运行，在shell命令行输入如下的命令

[root@CentOS7A mysql]#mysqldump -uroot -pPass1234 --all-databases -x -R -E --triggers --flush-log --master-data=2 >/root/all71.sql

拷贝到73服务器上

[root@CentOS7A mysql]#scp /root/all71.sql 192.168.1.73:/root

在73上恢复数据

逻辑备份还原是先启动mysql服务后恢复

配置73为从服务器

[root@CentOS7C ~]#vim /etc/my.cnf.d/server.cnf

[server]

skip_name_resolve = ON

innodb_file_per_table = ON

max_connections = 2000

relay_log = relay-log

server_id = 2

read_only = ON

启动服务

[root@CentOS7C ~]#systemctl start mariadb

恢复数据，73是干净的服务器，root还没设置密码

[root@CentOS7C ~]#mysql < /root/all71.sql

数据恢复后，查看all71.sql这个文件中 CHANGE MASTER TO 的注释行中，下一个二进制文件复制的位置，如下

[root@CentOS7C ~]#head -30 all71.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000002', MASTER_LOG_POS=245;

这里显示是下一个复制要从文件master-log.000002的245位置开始复制

在73服务器上设置复制参数

MariaDB [(none)]> change master to master_host='192.168.1.71',master_user='sunnycopy',master_password='Pass123456',master_log_file='master-log.000002',master_log_pos=245

启动线程

MariaDB [(none)]> start slave io_thread,sql_thread;

查看状态

MariaDB [(none)]> show slave status\G;

测试，在主节点71上更改students表

MariaDB [sunny]> update students set name="test" where id=998;

在73上查看，如果id=998的name值被更改，则复制成功