MySQL 8.0.15 主从复制配置

发表于 更新于 分类于 ， ， 本文字数： 2.4k 阅读时长 ≈ 9 分钟

MySQL 主从复制

BasicIn: MySQL 8.0.15 看过 Redis 的主从复制，其实 MySQL 的原理也差不多。都是通过一个中间文件(记录操作)进行传播，达到数据相同的结果。 开启主从复制有以下几个好处：

  1. 提高吞吐量，启动 MySQL 主从复制以后，写操作一般将是发生在主数据库(当然要求实时性特别高的读操作也会发生在主数据库上)，而读操作一般会发生在从数据库；
  2. 数据安全性，数据多了几个地方存储了，自然提高了数据丢失的安全性；
  3. 提升数据分析性能，数据分析一般都是占用较大的资源，我们可以把数据分析步骤放在其中一个或者多个从数据库上进行；
  4. 长距离的数据拷贝，无需与主数据库进行长连接的复制远程数据库的数据

    主从复制方式

MySQL 经历了这么多的版本迭代以来，已经支持多种方式进行主从数据复制了。 MySQL 支持以下方式进行：

  1. 同步复制：即主数据库数据发生变化的时候，需要所有的从数据库表示已经写入才返回，效率低
  2. 半同步复制：即主数据库数据发生变化的时候，至少一个从数据库表示已经写入才返回，效率较高，数据安全性也适中；
  3. 全异步复制：主库写入完成即返回，不关心从数据库是否成功。

    主从复制的格式

  • SBR：基于 SQL 语句描述的文档
  • RBR：基于数据行描述的文档
  • MBR：上面两者混合的文档

以上两种方式其实各有优缺点。

SBR 格式

优点：

  1. 妥当，毕竟已经经历很久的考验了
  2. 不需要记录大量的数据
  3. 文档包含了所有的 SQL 语句

缺点：

  1. 不是所有修改数据的语句都能够被复制，一些语句会被定义不安全性的，包含以下项目的将会被定义成不安全：
    • 需要使用自定义函数返回 SQL 参数的语句
    • UPDATE 和 DELETE 语句中使用了分页但是没有使用排序语句
    • 读写锁(SELECT ... FOR UPDATE and SELECT ... FOR SHARE)
    • 需要从数据库有确定性返回值的函数的时候
    • 使用以下函数的时候：LOAD_FILE()、UUID(), UUID_SHORT()、USER()、FOUND_ROWS()、SYSDATE() (unless both the master and the slave are started with the --sysdate-is-now option)、GET_LOCK()、IS_FREE_LOCK()、IS_USED_LOCK()、MASTER_POS_WAIT()、RAND()、RELEASE_LOCK()、SLEEP()、VERSION()；除了这些其他函数都能够被正确复制，包括 NOW()。如果 SQL 出现 [Warning] Statement is not safe to log in statement format. 也将不能被复制
  2. INSERT ... SELECT 会比 RBR 锁住更多的数据行
  3. update 语句需要全表扫描，会比 RBR 锁住更多的数据行
  4. InnoDB 下插入自增的数据会锁住
  5. 复杂语句下，从数据库都会重新执行一次
  6. 如果复杂语句执行时间过长，从数据库可能好报过长时间
  7. 执行过程函数执行 NOW() 的时候会产生同样的数据，如果存储过程要求实时性的话，那么会出现问题
  8. 函数都需要部署到从数据库
  9. 表格式主从数据库都需要相同

RBR 格式

优点：

  1. 所有的修改都会被查看到
  2. 使用以下语句会锁住主数据库的一些数据行
    • INSERT...SELECT
    • 自增插入
    • 没有使用列条件的 UPDATE 和 DELETE，或者不影响大多数行的语句

缺点：

  1. 当语句影响了很多行数据的时候，日志文档会变得很大，从数据库同步将会需要更长的时间
  2. 从数据库查看不了语句执行情况，不过可以查看 binlog 中数据的变化情况
  3. MyISAM 格式下，INSERT 语句会导致锁表。

实战主从复制

MySQL 安装

Linux系统：CentOSKU7 MySQL版本：8.0

第一步 安装数据库软件

下载 MySQL8.0 yum 仓库 文档，放入 root 文件夹中。然后在服务器上执行：

1
yum localinstall mysql80-community-release-el7-2.noarch.rpm

程序会自动更新服务器中的 yum 仓库，并且安装一些证书认证等等。 然后执行以下命令安装 MySQL 8.0

1
2
sudo yum install mysql-community-server
systemctl start mysqld.service

注意配置好防火墙配置，因为是开发环境我索性关闭了防火墙：

1
systemctl stop firewalld

第二步 修改root密码

接下来查看 MySQL 初次启动生成的密码：

1
grep 'temporary password' /var/log/mysqld.log

进入 MySQL 客户端修改数据库的密码：

1
2
mysql -uroot -pXXX
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

第三步 配置允许远程连接

查看原有的用户列表以及允许连接的 host，可以发现，目前 root 命令只允许 localhost 连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host from user
    -> ;
+------------------+-----------+
 user              host      
+------------------+-----------+
 mysql.infoschema  localhost 
 mysql.session     localhost 
 mysql.sys         localhost 
 root              localhost 
+------------------+-----------+
4 rows in set (0.00 sec)

通过以下指令进行修改：

1
2
3
4
5
6
7
8
9
10
11
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH   PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

然后我这里陷入一个坑，就是我习惯性使用 IDEA 连接数据库死活连不上去，以为是我数据库的配置出现了问题。后来换个 Navicat 就连上去了。驱动也是换了，IDEA 就是连不上去。

主数据库配置

第一步 查看logbin有没有开启

查看数据库的 log_bin 功能有没有开启：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show VARIABLES  LIKE '%log_bin%';
+---------------------------------+-----------------------------+
 Variable_name                    Value                       
+---------------------------------+-----------------------------+
 log_bin                          ON                          
 log_bin_basename                 /var/lib/mysql/binlog       
 log_bin_index                    /var/lib/mysql/binlog.index 
 log_bin_trust_function_creators  OFF                         
 log_bin_use_v1_row_events        OFF                         
 sql_log_bin                      ON                          
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

如上所示，第一个数据表示 ON 则已经开启了。 如果没有开启，需要在 /etc/my.cnf 中指定 log-bin=mysql-bin 进行开启

第二步 设置数据库的server_id

同样是在 /etc/my.cnf 文件中，加入 server-id=1。可接受范围是 1 到 2^32−1，设置其他值可能会出现不必要的坑。建议可以设置成当前数据库所在服务器 ip 最后一个字段的值。

1
2
3
4
5
6
7
...省略其他设置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=127

OK，这两个步骤做完主数据库的设置也就好了。接下来配置从数据库连接主数据库即可。

第三步 准备一个同步的账号

1
2
3
4
# 官方示例
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'Liweidan1@#';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;

从数据库配置

第一步 配置server_id

同上面 Master 的配置。

1
2
3
4
5
6
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=154

第二步 在主数据库准备一个用来同步的账号

第三步 导出主库数据

使用 FLUSH TABLES WITH READ LOCK; 锁住表格。然后使用上面的两个工具将数据进行同步。并且使用 SHOW MASTER STATUS; 来查看数据执行的位置。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 主数据库操作
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
 File           Position  Binlog_Do_DB  Binlog_Ignore_DB  Executed_Gtid_Set 
+---------------+----------+--------------+------------------+-------------------+
 binlog.000004       846                                                    
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 导出数据
[root@localhost ~]# mysqldump -uroot -p'Liweidan1@#' -S /var/lib/mysql/mysql.sock --all-databases > ~/mysql_bak.$(date +%F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll
total 908
-rw-------. 1 root root   1225 Mar  7 03:40 anaconda-ks.cfg
-rw-r--r--. 1 root root  25892 Mar 19 06:29 mysql80-community-release-el7-2.noarch.rpm
-rw-r--r--. 1 root root 894115 Mar 19 11:31 mysql_bak.2019-03-19.sql

# 传递给从服务器
[root@localhost ~]scp -r ~/mysql_bak.2019-03-19.sql root@192.168.1.154:~/

第四步 从库导入主库数据

1
mysql -uroot -p'Liweidan1@#' -S /var/lib/mysql/mysql.sock < ~/mysql_bak.2019-03-19.sql

第五步 配置从库同步

1
2
3
4
5
6
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.1.127', # 主库IP
    ->     MASTER_USER='repl', # 主库配置给从库的用户名
    ->     MASTER_PASSWORD='Liweidan1@#', # 主库中的从库密码
    ->     MASTER_LOG_FILE='binlog.000004', # 上面查询主库binlog的文件名
    ->     MASTER_LOG_POS=846; # 上面查询主库binlog的Position

完整命令：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CHANGE MASTER TO MASTER_HOST='192.168.1.127',MASTER_USER='repl',MASTER_PASSWORD='Liweidan1@#',MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=846;
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.127
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 4486
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 319
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes (这一项和下面一项如果都是YES表示主从设置成功)
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
......

第六步 启动从库同步进程

1
start slave;

第七步 释放主库的读锁

1
2
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

测试同步

遇到的问题

从数据库同步的状态，如果发现同步有问题，可以通过以上的 show slave status\G 来查看日志问题。 这里说我遇到的问题，因为我是使用虚拟机来做的主从服务器，所以从数据库是直接从主数据库下克隆出来的镜像，这里就导致了出现同步的问题：The slave I/O thread stops because master and slave have equal MySQL server UUIDs; 即使我们设置 server_id 但是这两个值也不可以一致。解决也很简单，关闭从 mysql，删除 mysql Data 目录下（SHOW VARIABLES LIKE '%datadir%' 可以查看 Data 目录的路径）的 auto.cnf ，启动 MySQL 服务即可。

参考资料

Mysql主从同步（复制）