记录一下MySQL8.x 主从数据库搭建

1. 准备工作-安装MySQL

确保手中有多台机器【在同一个电脑里面，创建多个虚拟机也可以----本文linux系统是centos7】。同时，这些机器之间可以互相通信！

此外，需要在这些机器上面安装相同版本的MySQL。

安装注意点：

感谢这篇文章：

linux安装MySQL8的问题

https://blog.csdn.net/G502770782/article/details/131216466

由于Centos7不支持libncurses.so.6，因此无法安装使用glic2.17以上的MySQL8，所以Centos7只能选择中glibc2.12版本的MySQL8。

【安装见这两篇文章】--

安装MySQL8

https://www.cnblogs.com/MrYoodb/p/15811199.html https://blog.csdn.net/qq_36408717/article/details/126705287

环境清除：

【1】最开始，检查系统是否安装了mariadb数据库， mariadb数据库是mysql的分支，是免费开源的。 mariadb和msyql会有冲突。首先要检查安装了mariadb, 如果有，需要卸载掉。检查命令：

使用：yum list installed | grep mariadb 或 rpm -qa | grep mariadb

如果有，会显示名字的，删掉：

执行命令：yum -y remove xxx[上面显示的名字]

或者执行：rpm -e [xx上面显示的名字] --nodeps

【2】最好删除电脑已存在的mysql

安装

在官网下载mysql的压缩包：【例如】mysql-8.0.26-linux-glibc2.28-x86_64.tar.xz

然后上传到你的机器上面去安装MySQL。

tar xvJf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

解压完成之后，重命名为mysql8。【纯属个人喜好】然后进去创建data目录;

配置好mysql环境变量 /etc/profile;

export PATH=$PATH:/usr/local/mysql8/bin [这个是你mysql的位置，因人而异]

# 如果设置这个不管用，可以再设置下面的：

# /etc/bashrc 是系统级的 bash 交互配置文件，无论登录 shell 还是交互式非登录 shell 都会加载它（针对 bash），因此将 PATH 设置添加到这里可以确保每次打开终端自动生效。

# 在文件末尾添加你的 PATH 配置（和/etc/profile中一致）：

然后编写mysql配置文件：/etc/my.cnf

[client]

port = 3306

socket = /usr/local/mysql8/data/mysql.sock

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

# 这个切记！！ 每个MySQL实例一定要唯一！！！！！！

# 比如master是1， slave就不能是1

server-id=1

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_general_ci

init_connect = 'SET NAMES utf8mb4'

port = 3306

socket = /usr/local/mysql8/data/mysql.sock

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

basedir = /usr/local/mysql8

datadir = /usr/local/mysql8/data

# 错误日志文件

log-error=/usr/local/mysql8/data/mysqld.log

# 进程 ID 文件

pid-file=/usr/local/mysql8/data/mysqld.pid

# 自定义二进制日志文件存放路径

log-bin=/usr/local/mysql8/data/mysql-bin

# 启用二进制日志（已在 log-bin 中启用，无需重复）

# log-bin=mysql-bin

# 设置二进制日志过期时间（秒）

binlog_expire_logs_seconds=2592000 # 30天

# 设置单个二进制日志文件的最大大小（例如100MB）

max_binlog_size=100M

#lower_case_table_names=1

#如果要设置lower_case_table_names可以在初始化里面设置 ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql8/data --basedir=/usr/local/mysql8 --lower_case_table_names=1

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

新建mysql用户组和用户：

groupadd mysql

useradd -r -g mysql mysql

# 授予权限

chown -R mysql:mysql /usr/local/mysql8

chmod -R 755 /usr/local/mysql8

用该配置文件初始化：[ 进入bin目录 ] -- 记住初始化密码

./mysqld --initialize --user=mysql --datadir=/usr/local/mysql8/data --basedir=/usr/local/mysql8

/*

如果记不住的话，可以暂时跳过权限认证，以root进入mysql

*/

flush privileges;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

然后去，mysql8里面的support-files目录里面就可以启动mysql了：

./mysql.server start

./mysql.server restart

将MySQL变为系统服务：

添加mysqld服务到系统中（注意在mysql8文件下执行）,是 将 MySQL 的服务控制脚本复制到系统的 init.d 目录

cp -a ./support-files/mysql.server /etc/init.d/mysql

部分

说明

cp -a复制时保留文件的所有属性（权限、所有者、时间戳等）

./support-files/mysql.server源文件路径：当前目录下的 support-files/mysql.server（MySQL自带的启动脚本）

/etc/init.d/mysql目标路径：系统服务管理目录，复制后改名为 mysql

授权以及添加服务

chmod +x /etc/init.d/mysql

chkconfig --add mysql

检查binlog是否开启mysql启动好之后，查看binlog位置及其状态

SHOW VARIABLES like '%log_bin%'

--

log_bin ON

log_bin_basename /usr/local/mysql8/data/mysql-bin

log_bin_index /usr/local/mysql8/data/mysql-bin.index

show variables like '%binlog%'

--

binlog_cache_size 32768

binlog_checksum CRC32

binlog_direct_non_transactional_updates OFF

binlog_encryption OFF

binlog_error_action ABORT_SERVER

binlog_expire_logs_seconds 2592000

binlog_format ROW # binlog日志格式

2. 开始搭建

在两个电脑上安装好mysql并且启动完成之后，就可以着手搭建主从了。

对了，记得开放端口3306

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

首先来一个别人的图片：【https://blog.51cto.com/lenglingx/13601901】

从上面的图我们可以知道mysql主从的大致流程。

首先，在主节点上操作

要配置一下哪些同步哪些不同步呢，这步可以跳过的：[ 可以加上这些东西，动了my.cnf的话，记得|重启或者启动|一下Mysql服务喔]

[mysqld]

# 需要同步的数据库

# binlog-do-db=your_database

# 不需要同步的数据库

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

主节点创建用于主从复制的数据库Mysql账号【如下所示创建了 slave1, 密码是123456】

mysql> create user 'slave1'@'%' identified by '123456';

Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'slave1'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'slave1'@'%' identified with mysql_native_password by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

查看主节点的二进制日志情况：其中File和Position两个参数需要在从库配置中使用

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

mysql-bin.000004 | 27147 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

-- 如果这个时候，往主库里面插入一条数据position会变喔，重启【都会变】

然后，配置从节点

从这一节最开始的那个图中，我们可以知道，从节点是需要中继日志的！！所以，我们要把从节点的配置文件修改一下：/etc/my.cnf

[mysqld]

#配置唯一的服务器ID

server-id=2

#加上这个 开启中继日志，从主服务器上同步日志文件记录到本地

relay-log=relay-log-bin

重启一下mysql服务。然后用数据库的root登录slave数据库实例

mysql> change master to master_host='192.168.110.128',master_user='slave1',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=156;

Query OK, 0 rows affected, 8 warnings (0.01 sec)

#语法

change master to

master_host='主节点IP',

master_user='主节点用户名',

master_password='用户密码',

master_log_file='mysql-bin.具体数字',

master_log_pos=具体值;

在从节点开启slave同步，查看同步状态:

mysql> change master to master_host='192.168.110.128',master_user='slave1',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=156;

Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for source to send event

Master_Host: 192.168.110.128

Master_User: slave1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 156

Relay_Log_File: relay-log-bin.000002

Relay_Log_Pos: 324

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

• Slave_IO_Running：IO线程，负责与主机的io通信

• Slave_SQL_Running：SQL线程，责自己的slave MySQL进程

#开启slave同步

mysql> start slave;

#关闭slave同步

mysql> stop slave;

#重设slave同步

mysql> reset slave;

3. 主库已经运行一段时间

第二步在主库，从库都是新的实例，然后数据库没有其他数据的情况下，一搭建就会ok。

但是如果是主库运行了一段时间，中间需要弄主从，按照上面过程搭建完成之后，然后往主库里面插入数据，从库会报错的，说：找不到该数据库。

这种情况，就需要尽可能不停止主库，然后将数据在从库还原一份儿，这样主从复制就会没有问题了。

首先：使用mysqldump工具：【不是在mysql里面执行的，是在shell里面】

-- 所有数据库

mysqldump -uroot -p --single-transaction --source-data=2 --all-databases > full_backup.sql

-- 部分

mysqldump -uroot -p --single-transaction --source-data=2 [tbname ...] > xx.sql

-- 示例

mysqldump -uroot -p --single-transaction --source-data=2 ytw_db > /usr/local/full_backup.sql

-- source-data=2 会在备份文件中注释 CHANGE MASTER 所需的 binlog 文件名和位置

-- 避免锁表：--single-transaction 适用 InnoDB 引擎，非 InnoDB 表需 --lock-all-tables

然后把这个sql导入从库。

如果说数据库有点儿大，优化mysqldump的可行方案：

mysqldump -u user -p --single-transaction --quick dbname table1 > table1.sql

-- quick：禁用缓存，减少内存占用

还可以考虑压缩：

mysqldump -u user -p dbname | gzip > backup.sql.gz # 备份时压缩

gunzip < backup.sql.gz | mysql -u user -p dbname # 恢复时解压流式导入

还可以用mydumper（备份）/myloader（恢复）。

如果说，数据还是大，比如说达到了几百gb，上tb了。。。【这个Xtrabackup】

这个不在本文讨论范畴。。

end. 补充点

e.1 binlog的position

这个东西是干什么的呢？

Binlog Position（二进制日志位置） 是确保数据一致性和复制准确性的核心机制。他可以明确告知从库应从主库的哪个binlog文件及具体位置开始同步数据。在主库执行 SHOW MASTER STATUS; 获取当前binlog位置（如 mysql-bin.000003 和 154），然后在从库配置时通过 CHANGE MASTER TO ... MASTER_LOG_FILE='...', MASTER_LOG_POS=... 指定该位置。

比如说，binlog保存7天。如果说有这样一个场景，我得到了主库2025-06-01的数据备份并且同时记录了binlog的position=150，将其还原到了salve数据库实例中。然后再2025-06-02搭建好了主从复制，position是主库的150开始，然后在从库里面start slave命令的时候，从库会自动还原06-01到06-02中间的所有数据变更。

初始状态（2025-06-01）：主库：数据状态A（position=150），从库：通过备份还原到状态A

启动复制（2025-06-02）

CHANGE MASTER TO

MASTER_LOG_FILE='binlog.00000X', -- 备份时记录的binlog文件名

MASTER_LOG_POS=150; -- 备份时的position

START SLAVE;

同步过程：从库IO线程向主库请求从position=150开始的binlog，主库发送2025-06-01 15:00（position=150）→ 2025-06-02的所有binlog事件，从库SQL线程按顺序重放这些事件，最终从库达到与主库完全一致的状态；

时间范围

主库binlog事件

从库动作

2025-06-01 15:00

INSERT/UPDATE/DELETE (pos=150)

重放第一个事件

2025-06-01 15:01

新事件 (pos=151)

重放第二个事件

2025-06-02 10:00

最新事件 (pos=XXXX)

持续重放直到追上主库

主从复制其他重点参数（通过 SHOW SLAVE STATUS\G 查看）：

• Read_Master_Log_Pos：从库已读取的主库binlog位置（IO线程状态）。

• Exec_Master_Log_Pos：从库已执行的主库binlog位置（SQL线程状态）

延迟判断：若 Exec_Master_Log_Pos 长期落后于 Read_Master_Log_Pos，表明SQL线程处理速度不足，存在复制延迟（Seconds_Behind_Master > 0）。

针对上面的问题，可以

MySQL配置优化

启用并行复制：

# my.cnf (MySQL 5.7+)

slave_parallel_workers = 8 # 建议设置为CPU核数的2倍

slave_parallel_type = LOGICAL_CLOCK

slave_preserve_commit_order = 1 # 保持事务顺序(8.0默认)

# MySQL 8.0 增强

binlog_transaction_dependency_tracking = WRITESET

增大复制缓冲区:

# 专用于复制线程的内存

slave_pending_jobs_size_max = 256M # 5.6+默认16M，建议提升

slave_preserve_commit_order = 1 # 并行复制保序

主库大事务拆分、写操作尽量改成批量的。

e.2 配置文件参数解释

[client]

port=3306

# 设置 MySQL 客户端默认字符集

default-character-set=utf8mb4

# 指定 MySQL 客户端用来与 MySQL 服务器通信的本地 socket 文件的位置。

socket=/var/lib/mysql/mysql.sock

[mysql]

# 设置 MySQL 客户端默认字符集

default-character-set=utf8mb4

# 禁用反向 DNS 解析，以提高连接性能。

skip-name-resolve

# 指定 MySQL 客户端连接的端口号。

port=3306

# 指定 MySQL 客户端使用的 UNIX 域套接字文件的位置。

socket=/var/lib/mysql/mysql.sock

[mysqld]

# 服务器端口

port=3306

# MySQL 的安装目录

basedir=/usr/local/mysql8

# MySQL 的数据目录

datadir=/var/lib/mysql

# 错误日志文件

log-error=/var/log/mysql/mysqld.log

# 进程 ID 文件

pid-file=/var/run/mysqld/mysqld.pid

# 自定义二进制日志文件存放路径

log-bin=/var/lib/mysql/mysql-bin

# 启用二进制日志（已在 log-bin 中启用，无需重复）

# log-bin=mysql-bin

# 设置二进制日志过期时间（秒）

binlog_expire_logs_seconds=2592000 # 30天

# 设置单个二进制日志文件的最大大小（例如100MB）

max_binlog_size=100M

# 最大连接数

max_connections=1000

# 允许的最大包大小

max_allowed_packet=512M

# 打开表缓存的大小

table_open_cache=256

# 排序缓冲区大小

sort_buffer_size=256K

# 读取缓冲区大小

read_buffer_size=256K

# 读取临时表缓冲区大小

read_rnd_buffer_size=512K

# 连接缓冲区大小

join_buffer_size=256K

# 指定 MySQL 内部临时表的最大大小

tmp_table_size=1G

max_heap_table_size=1G

# InnoDB 使用缓冲池来缓存数据和索引

innodb_buffer_pool_size=40G

# InnoDB 使用日志文件来记录所有的修改操作

innodb_log_file_size=4G

# 每个表的数据和索引存储在单独的文件中

innodb_file_per_table=ON

# 索引缓存大小（适用于 MyISAM）

key_buffer_size=64M

# 默认存储引擎

default-storage-engine=InnoDB

# 设置服务器ID（适用于主从复制）

server-id=1

# SQL 模式

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 开启慢查询日志，慢查询阈值设为 2秒

slow_query_log=1

long_query_time=5

# 慢查询日志文件

slow_query_log_file=/var/log/mysql/slow.log

# 连接超时设置（秒）

wait_timeout=600

interactive_timeout=600

# 线程缓存大小

thread_cache_size=200

# 查询缓存大小（MySQL 8.0 已移除查询缓存，无需配置）

# 表定义缓存

table_definition_cache=400

# 表打开缓存

table_open_cache_instances=4

# InnoDB 相关优化

innodb_flush_log_at_trx_commit=1

innodb_flush_method=O_DIRECT

innodb_io_capacity=2000

innodb_io_capacity_max=4000

innodb_read_io_threads=8

innodb_write_io_threads=8

innodb_thread_concurrency=0

innodb_adaptive_hash_index=ON

innodb_buffer_pool_instances=8

# 日志刷新频率

sync_binlog=1

innodb_doublewrite=ON

# 错误日志缓冲

log_error_verbosity=3

# 临时表相关设置

tmp_table_size=1G

max_heap_table_size=1G

# 字符集和排序规则

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

# 安全相关设置

skip_symbolic_links

local-infile=0

# 资源限制

open_files_limit=65535

# 其他优化

innodb_strict_mode=ON

innodb_old_blocks_time=1000

原谅来源：https://mp.weixin.qq.com/s/owrnPyTHVcErpXCNXOxj-A?click_id=11