Ubuntu 下 mysql 8.0 基于二进制日志主从配置
网上关于mysql 主从复制的文章很多,因为系统不同或 mysql 版本不同,看着总是发怵,必须找到官方文档,并且照着操作才放心!
官方文档
MySQL 8.0 参考手册 : 第十七章复制
文档其实写的很详细,但是,总有一种跳来跳去的感觉,来回看了几遍,才捋出来思路,其实就是很简单的几个步骤
先学习这个
17.1.2 设置基于二进制日志文件位置的复制
我的环境
主服务器
Ubuntu 20.04 :
192.1568.0.101
$ mysql -V
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
从服务器
Ubuntu 20.04 :
192.168.0.103
$ mysql -V
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
2 个服务器上的mysql 都是直接使用 apt 安装后,都是缺省配置
ubuntu 安装、卸载重装mysql 8.0
以下假设所有密码都是 Password@123
操作步骤
抄录一下官方文档实现步骤
17.1.2.1 设置复制源配置17.1.2.2 设置副本配置17.1.2.3 为复制创建用户17.1.2.4 获取复制源二进制日志坐标17.1.2.5 选择数据快照的方法17.1.2.6 设置副本17.1.2.7 在副本上设置源配置最后一步17.1.2.8 将副本添加到复制环境暂时不用管他,后续再学习!
完全按照以上步骤,一步一步的进行
-
17.1.2.1 设置复制源配置
要将源配置为使用基于二进制日志文件位置的复制,您必须确保启用二进制日志记录,并建立唯一的服务器 ID
动态修改 server_id ,重启之后会丢失
SET GLOBAL server_id = 101;静态修改 server_id
my.cnf 只是简单的 include ,实际的配置在 /etc/mysql/mysql.conf.d$ cd /etc/mysql/mysql.conf.d
$ cat mysqld.cnf...[mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqllog-error = /var/log/mysql/error.log# add by wzh 20230413server-id=101
重启 mysql 生效
$ sudo systemctl restart mysql.servicemysql> show variables like ‘server_id’;
+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id | 101 |+---------------+-------+1 row in set (0.01 sec)
-
17.1.2.2 设置副本配置
同上一步设置从服务器 (192.168.0.103)的 server_id -
17.1.2.3 为复制创建用户
每个副本都使用 MySQL 用户名和密码连接到源,因此源上必须有一个用户帐户可供副本用于连接mysql> CREATE USER ‘repl’@‘%’ IDENTIFIED BY ‘Password@123’;
mysql> GRANT REPLICATION SLAVE ON . TO ‘repl’@‘%’; -
17.1.2.4 获取复制源二进制日志坐标
要将副本配置为在正确的点开始复制过程,您需要在其二进制日志中记录源的当前坐标。警告
此过程使用FLUSH TABLES WITH READ LOCK, 它会阻止 COMMIT对 InnoDB表的操作。如果您计划关闭源以创建数据快照,您可以选择跳过此过程,而是将二进制日志索引文件的副本与数据快照一起存储
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000008 | 2181 | | | |+---------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
记下 binlog.000008 和 2181
-
17.1.2.5 选择数据快照的方法
在主服务器(192.168.0.101)上导出所有数据库
$ mysqldump -uroot -pPassword@123 --all-databases --source-data > dbdump101-20230414.db
mysql> UNLOCK TABLES;
创建数据库的存档或副本后,在开始复制过程之前将文件复制到每个副本。
将备份文件复制到从服务器 (192.168.0.103)
我这里scp 传到桌面电脑,再 scp 到从服务器 ,因为主从服务器之间不能 ssh
$ scp dhbm@192.168.0.101:/home/dhbm/dbdump101-20230414.db .$ scp dbdump101-20230414.db dhbm@192.168.0.103:/home/dhbm/.
-
17.1.2.6 设置副本
使用现有数据设置复制时,在开始复制之前将快照从源传输到副本。导入数据库到从服务器
因为昨天已经操作过,slave 已经开启了,使用必须先 stop slave
否则导入时报错ERROR 3021 (HY000) at line 24: This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
mysql> stop slave;
mysql -uroot -pPassword@123 < dbdump101-20230414.db
-
17.1.2.7 在副本上设置源配置
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST=‘192.168.0.101’,SOURCE_USER=‘repl’,SOURCE_PASSWORD=‘Password@123’,SOURCE_LOG_FILE=‘binlog.000008’,SOURCE_LOG_POS= 2181;
开启 slave
mysql> start slave;
查看确认 slave 状态
mysql> show slave status\\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.0.101Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000008Read_Master_Log_Pos: 4328Relay_Log_File: ubuntu2004-103-relay-bin.000002Relay_Log_Pos: 2470Relay_Master_Log_File: binlog.000008Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: ......
主要确认 Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes
到这里就算完成了!
简单测试一下
-
在主服务器 (192.168.0.101)上,新建一个简单数据库 test_20230414 ,新建一个 table ,insert 2 条数据
mysql> CREATE DATABASE
test_20230414
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci / /!80016 DEFAULT ENCRYPTION=‘N’ */;mysql> use test_20230414;
mysql> CREATE TABLE
test1
(
->name
varchar(20) DEFAULT NULL,
->age
int NOT NULL,
->indb
date NOT NULL,
->outdb
date NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=‘test 20230414’ ;mysql> INSERT INTO
test1
(name
,age
,indb
,outdb
) VALUES (‘wzh2’, ‘33’, ‘2021-04-01’, ‘2023-04-14’) ;mysql> INSERT INTO
test1
(name
,age
,indb
,outdb
) VALUES (‘wzh3’, ‘33’, ‘2021-04-03’, ‘2023-04-23’);在从服务器 (192.168.0.103)上查看是否同步过来了
mysql> use test_20230414;
mysql> select * from test1;
+------+-----+------------+------------+| name | age | indb | outdb |+------+-----+------------+------------+| wzh2 | 33 | 2021-04-01 | 2023-04-14 || wzh3 | 33 | 2021-04-03 | 2023-04-23 |+------+-----+------------+------------+2 rows in set (0.00 sec)
-
在主服务器 (192.168.0.101)上,dele te 1 条
mysql> delete from test1 where name = ‘wzh2’;
在从服务器 (192.168.0.103)上看卡是否也 delete 了
mysql> select * from test1;
+------+-----+------------+------------+| name | age | indb | outdb |+------+-----+------------+------------+| wzh3 | 33 | 2021-04-03 | 2023-04-23 |+------+-----+------------+------------+1 row in set (0.00 sec)
-
在主服务器 (192.168.0.101)上,drop 这个数据库
mysql> drop database test_20230414;mysql> show databases;
在从服务器 (192.168.0.103)上看看是否也 drop 了
mysql> show databases;