主从复制的实现

演示环境
--------------------------------------------------
主数据库:MySQL 5
从数据库:MySQL 8
数据库名:replication_db
--------------------------------------------------
重要提醒:本次演示的主库和从库版本是不同的,主从库应该使用相同版本来做主从复制。
 
 
 
 
 
先在主库和从库分别创建一个名为replication_db的空数据库
CREATE DATABASE `replication_db` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
 
 
 
 
 
打开主库的my.cnf文件,增加主从复制的配置参数
[root@localhost ~]# vim /program/mysql-5/my.cnf
 
…………(此处省略内容若干)…………
 
[mysqld]
 
#========== 主从复制配置·主库 ==========#
# 服务器ID号,区间为[1, 2^32 - 1]
server_id = 1
# 是否只读,可选值:OFF=可读写|ON=只读,通常主库可读写&从库只读
read_only = OFF
# 指定二进制日志文件路径
log_bin = /program/mysql-5/log/bin.log
# 指定需要同步的数据库,如果缺省则为所有数据库都需要同步
binlog_do_db = replication_db
 
…………(此处省略内容若干)…………
 
[root@localhost ~]# service mysql-5 restart # 重启主库使配置生效
 
 
 
 
 
在主库创建一个专属用户,让从库使用该专属用户连接主库执行复制操作
mysql> CREATE USER `replication_user`@`::1` IDENTIFIED WITH mysql_native_password BY '用户密码';
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT REPLICATION SLAVE ON *.* TO `replication_user`@`::1`;
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW MASTER STATUS;
+------------+----------+----------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB   | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+----------------+------------------+-------------------+
| bin.000001 |      154 | replication_db |                  |                   |
+------------+----------+----------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql>
说明①:由于主库和从库都在同一台服务器上,所以允许连接主库的主机地址限定在::1即可,也就是只允许本地连接。
说明②:这里需要记录“SHOW MASTER STATUS;”查出来的File和Position,配置从库时要使用。
 
 
 
 
 
打开从库的my.cnf文件,增加主从复制的配置参数
[root@localhost ~]# vim /program/mysql-8/my.cnf
 
…………(此处省略内容若干)…………
 
[mysqld]
 
#========== 主从复制配置·从库 ==========#
# 服务器ID号,区间为[1, 2^32 - 1]
server_id = 2
# 是否只读,可选值:OFF=可读写|ON=只读,通常主库可读写&从库只读
read_only = ON
 
…………(此处省略内容若干)…………
 
[root@localhost ~]# service mysql-8 restart # 重启从库使配置生效
 
 
 
 
 
在从库开启主从复制
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST = '::1', SOURCE_USER = 'replication_user', SOURCE_PORT = 3306, SOURCE_PASSWORD = '用户密码', SOURCE_LOG_FILE = 'bin.000001', SOURCE_LOG_POS = 154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
 
mysql> START REPLICA;
Query OK, 0 rows affected (0.01 sec)
 
mysql> SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: ::1
                  Source_User: replication_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: bin.000001
          Read_Source_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000008
                Relay_Log_Pos: 317
        Relay_Source_Log_File: bin.000001
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
…………(此处省略内容若干)…………
mysql>
说明:只要Replica_IO_Running和Replica_SQL_Running均为Yes,那么主从复制功能就正常了,如果有一个不是Yes可查看下面的Last_Error、Last_IO_Errno、Last_SQL_Error等内容,根据错误信息解决问题。
 
 
 
 
 
接下来实际验证一下主从复制功能是否正常,先在主库建立一张表并插入几条记录,然后再去从库查看是否把数据复制过来了,相关测试SQL语句如下:
DROP TABLE IF EXISTS `prefix_user`;
CREATE TABLE `prefix_user`
(
    `uid`  int unsigned NOT NULL AUTO_INCREMENT COMMENT '用户UID',
    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户姓名',
    PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
INSERT INTO `prefix_user` SET `name` = '刘一';
INSERT INTO `prefix_user` SET `name` = '陈二';
INSERT INTO `prefix_user` SET `name` = '张三';
 
 
 
 
 
上面是主库和从库都为空数据库的情况下实现主从复制,但大多数时候都是主库已经运行了一段时间(已产生大量数据),然后才有主从复制的需求,这种情况想实现主从复制就要求从库先导入主库的数据,然后才开启主从复制,具体流程如下:
(1) FLUSH TABLES WITH READ LOCK; -- 主库加读锁(只能读不能写,所有写操作都会被阻塞,直到释放主库读锁)
(2) SHOW MASTER STATUS; -- 查出主库的File和Position并记录下来
(3) 使用mysql/bin/mysqldump把主库导出
(4) UNLOCK TABLES; -- 释放主库读锁
(5) 把主库导入从库(这样从库就有了主库的初始数据)
(6) 执行CHANGE REPLICATION SOURCE语句配置复制源(只要把SOURCE_LOG_FILE和SOURCE_LOG_POS修改第二步查出来的File和Position即可)
(7) 执行START REPLICA开启主从复制
 
 
 
 
 
总结:主从复制的目的之一是读写分离,也就是主库负责写操作,从库负责读操作,从而减轻单库的压力。

Copyright © 2024 码农人生. All Rights Reserved