MySQL Replication

• MySQL没有增量备份机制
• MySQL提供了主从备份机制以实现热备
• 双机热备需服务器版本高于3.2
• 从库版本可以高于主库版本，不得低于主库版本。
• 双机热备可采用自身的REPLICATION功能实现
• 双机热备也可采用Heartbeat开源软件实现

主从复制

• 主从同步复制操作是异步的
• 主从复制又称为A/B复制

主从复制原理

MySQL主从同步是基于二进制日志机制binlog，即主库使用二进制日志来记录数据库变动，从库通过读取和执行日志来保持和主库数据一致。

使用二进制日志时，主库所有操作都会被记录下来。从库会接收到改日志的副本，从库执行日志中指定类型的事件，比如只插入数据或只更新数据，默认会执行日志中所有语句。

1. master主库将数据改变记录在自身的二进制日志binary log中，即my.cnf配置文件中log-bin配置项指定的文件中，这种记录成为二进制日志事件binary log events。
2. slave从库通过I/O线程读取master主库中binary log events并写入到自身的中继日志relay log中
3. slave从库重做中继日志relay log中的事件，将中继日志中的记录一条条的在本地执行，完成数据的本地存储，从而实现将改变 反映到自身的数据中。(数据重做)

主从优势

• 主库执行写入和更新，从库提供读取，可动态调整从库数量来优化性能。
• 主库生成实时数据，从库分析数据以提供主库性能。

准备工作

• 查询master主库版本

$ mysqld --version
/usr/libexec/mysqld  Ver 8.0.26 for Linux on x86_64 (Source distribution)

• 查询slave从库版本

$ mysqld --version
/usr/local/mysql/bin/mysqld  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

• 主从数据库都必须配置唯一的ID编号，即server-id。
• 从库需通过CHANGE MASTER TO语句来配置要连接的主库的IP地址、日志文件名、日志位置。

配置流程

1. master主库开启二进制日志机制并配置独立ID

$ whereis my.cnf
my: /etc/my.cnf

$ cd /etc/my.cnf.d/
$ vim master.cnf
[mysqld]
log-bin = mysql-in
server-id = 1
binlog-ignore-db=mysql
binlog-do-db=gva

配置	描述
binlog-ignore-db	禁止记录的数据库
binlog-do-db	开启记录的数据库

重启服务并查看状态

$ systemctl restart mysqld.service
$ systemctl status mysqld.service

查看是否设置正确

$ mysql -u root -p
mysql> SHOW VARIABLES LIKE '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.01 sec)

2. slave从库配置独立且唯一的ID

$ mysqld --version
/usr/local/mysql/bin/mysqld  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

$ whereis my.cnf

$ yum install mlocate
$ updatedb
$ locate my.cnf

$ mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

$ vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=gva
replicate-ignore-db=mysql,information_schema,performance_schema

重启服务

$ systemctl restart mysqld.service
$ systemctl status mysqld.service

查看是否设置正确

$  mysql -u root -p
mysql> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

3. master主库创建一个专门用来同步的用户

$ mysql -u root -p
mysql> CREATE USER 'repl'@'192.168.1.223' IDENTIFIED WITH mysql_native_password BY 'r2e1p7';

将建立的repl账户授予REPLICATION SLAVE的权限，用来实现双机热备。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.223';
mysql> FLUSH PRIVILEGES;

查看用户

mysql> SELECT host,user,authentication_string,plugin FROM mysql.user;

查看权限

mysql> SHOW GRANTS FOR repl@'192.168.1.223';

在slave从库中验证是否可以登录master主库

$ mysql -h 192.168.1.100 -u repl -p

4. 获取master主节点当前binary log文件名和位置

mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
             File: binlog.000002
         Position: 3567676
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

注意File和Position两项是接下来需要的

5. 在slave从库上设置master参数

$ mysql -u root -p

首先停掉slave服务线程

mysql> STOP SLAVE;

在从库上使用CHANGE MASTER语句来指定同步的位置

mysql> CHANGE MASTER TO
    -> MASTER_HOST = '192.168.1.223',
    -> MASTER_USER = 'repl',
    -> MASTER_PASSWORD = 'r2e1p7',
    -> MASTER_LOG_FILE = 'binlog.000002',
    -> MASTER_LOG_POS = 3567676;

CHANGE MASTER TO MASTER_HOST = '192.168.1.223', MASTER_USER = 'repl', MASTER_PASSWORD = 'r2e1p7', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 156;

注意：MASTER_LOG_FILE是主库状态中的File值，MASTER_LOG_POS是主库状态中的Position值。

重启Slave线程

mysql> START SLAVE;

查看从库同步状态

mysql> SHOW SLAVE STATUS \G;

从库开启主从同步

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.223
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 3567676
               Relay_Log_File: 52JS-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

若Slave_IO_Running和Slave_SQL_Running都为Yes则表示从库设置成功。

互为主从

互为主从则两台主机都需要分别建立同步用户，即采用主-主方式。

主1配置

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-db-db=gva
binlog-ignore-db=mysql
# 主-主专用配置
log-slave-updates
sync-binlog=1
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db=gva
replicate-ignore-db=mysql,information_schema

主2配置

[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=gva
replicate-ignore-db=mysql,information_schema,performance_schema
# 主-主专用配置
binlog-do-db=gva
binlog-ignore-db=mysql

log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2

接着分别启动，并按主从方式来即可。

备份从库

将主库数据复制到从库后再备份从库数据，当数据量不大时可直接采用mysqldump命令 ，若数据量较大时则直接备份数据文件。

使用mysqldump命令备份

1. 暂停从库的复制进程

$ mysqladmin stop-slave

或者采用暂停SQL进程，这样从库仍然能接收二进制日志事件，但并不执行。这样重启SQL进程时可加快复制进度。

$ mysql -e 'stop slave sql_thread';

2. 导出数据

$ mysqldump --all-database > full.dump

3. 重启复制进程

$ mysqladmin start-slave

数据量较大直接复制数据文件来备份

1. 关闭从库

$  mysqladmin shutdown

2. 复制数据文件

$ tar cf /tmp/data.tar ./data