Mysql复制指南（一）

基础理论实践

复制让一台主库的数据同步到多台备库上，备库本身也可以作为主库，因此很容易组合多样化的复制拓扑。

复制是Mysql构建集群架构的基础，用途主要有：

1.一定程度上的水平扩展。

2.备份以及灾难恢复，简称“备灾”。

3.高可用的基础

一、复制原理

原理很简单，通过Mysql的binlog实现。当主库打开binlog，Mysql会在数据变更时将变更日志以二进制的形式记下来（binlog），从库异步将主库的binlog拉取下来重放到自身，即可实现数据上的同步。

1. 复制方式

Mysql提供三种复制方式，语句复制、行复制和混合复制。语句复制和行复制各自都有优缺点，因此Mysql提供两者混合的复制模式即混合复制（Mysql默认为混合复制）。

语句复制

顾名思义，就是在备库上把主库执行过的语句再执行一遍。

优点是不会占用太多网络带宽，同时查看binlog会比较友好。

缺点有很多，比如sql里携带一些函数CURRENT_TIMESTAMP()就无法被正确复制。另外一些耗时语句也会让备库吃力，比如INSERT INTO table1(col1) SELECT count(*) from table2。

行复制

行复制恰巧解决了语句复制的一些问题。行复制模式在数据库运行中会将变更的实际数据记录到binlog。

优点是相比语句复制他能正确地复制每一行，同时能解决耗时语句给备库带来的压力。

缺点是某些语句复制开销会很大mssql复制，无论是存储、带宽还是重放。例如全表更新UPDATE table1 SET col1='foo'。

混合复制

以上复制都不完美，因此混合模式下会优先使用语句复制，如果发现语句复制不能正确执行，就切换到行复制。

2. 复制工作图主-备

image.png

主-备-备

image.png

二、实战配置1. 本地初始化运行两台空的Mysql下载Mysql

这里我们是用Linux - Generic方式安装，版本5.7，macOS版。下载地址

下载截图

解压到目录/usr/local/mysql下

$ tar -zxvf mysql-5.7.29-macos10.14-x86_64.tar.gz
$ sudo mv mysql-5.7.29-macos10.14-x86_64 /usr/local/
$ sudo ln -s /usr/local/mysql-5.7.29-macos10.14-x86_64 /usr/local/mysql

初始化实例

$ bin/mysqld --initialize-insecure --datadir=/usr/local/mysql/data/

添加实例配置文件/usr/local/mysql/etc/my.cnf

[mysqld]
port=3306
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=/usr/local/mysql/data/error.log
server-id=1 # 集群中server-id必须唯一
log-bin=mysql-bin # 开启binlog
sync_binlog=1 # binlog在事务提交时立即fsync到磁盘
innodb_flush_log_at_trx_commit=1 # 事务日志在事务提交时立即flush到磁盘
innodb_support_xa=1
[mysqld_safe]
log-error=/usr/local/mysql/data/error_safe.log

运行实例

$ bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf

连接实例

$ mysql -h 127.0.0.1 -u root -p

安装完成截图

停止实例

$ mysqladmin --socket=/tmp/mysql.sock -u root -p shutdown

至此已经顺利安装并运行一台mysql实例，我们将它用作主库。

用以上步骤再运行一台备库，可以将其安装在/usr/local/mysql2做区分。从库配置文件内容会多一点：

[mysqld]
[mysqld]
port=3307
socket=/tmp/mysql2.sock
basedir=/usr/local/mysql2
datadir=/usr/local/mysql2/data
log-error=/usr/local/mysql2/data/error.log
server-id=2 # 集群中server-id不能重复
log-bin=mysql-bin
sync_binlog=1
innodb_flush_log_at_trx_commit=1
innodb_support_xa=1
log_slave_updates=1# 确保重放relaylog的同时记录binlog
relay_log=/usr/local/mysql2/data/mysql-relay-bin # 配置relaylog
skip-slave-start=1 # 启动时不要自动开始复制，需手动启动复制。
read_only=1# 从库只读
sync_master_info=1
sync_relay_log=1
sync_relay_log_info=1
[mysqld_safe]
log-error=/usr/local/mysql2/data/error_safe.log

2. 配置复制主库

从库的IO线程会主动连接主库拉取binlog，对于主库来讲就和处理一个普通的客户端连接没什么区别，只不过该连接具有复制权限而已。这里配置一个拥有复制权限的账号：

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'p4ssword';

或者分开写

CREATE USER 'repl'@'%' IDENTIFIED BY 'p4ssword';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';

记录主库的binlog文件和文件偏移量。我的是mysql-bin.000002和457。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      457 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库

连接从库，从库同样需要配置一个拥有复制权限的账号，操作和主库一样。然后为该从库设置复制源（主库）、复制账号、要复制的binlog文件和偏移量。

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='p4ssword',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=457;
Query OK, 0 rows affected, 1 warning (0.25 sec)

我们来看看设置是否成功

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 457
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No

这里是设置成功了。可以看到Slave_IO_State是空，Slave_IO_Running和Slave_SQL_Running都是NO，这是因为复制还没启动，接下来启动复制：

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

再来看一下状态，就很香了

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 457
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

（编辑：开发网_商丘站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!