基于二进制包最小化安装传统位置点主从复制主库192.168.195.141从库192.168.195.1421. 环境信息项目主库 (Master)从库 (Slave)IP192.168.195.141192.168.195.142OSKylin Linux Advanced Server V10 (Halberd)Kylin Linux Advanced Server V10 (Halberd)MySQL版本8.0.35二进制包glibc2.178.0.35二进制包glibc2.17server-id12角色MasterSlave2. 安装 MySQL两台均执行2.1 创建用户与组groupaddmysqluseradd-gmysql mysql验证idmysql# 用户id1003(mysql) 组id1000(mysql) 组1000(mysql)2.2 解压二进制包cd/usr/local/wgethttps://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xztarxvf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xzln-smysql-8.0.35-linux-glibc2.17-x86_64 mysql验证ls-la/usr/local/mysql# lrwxrwxrwx 1 root root 35 7月 19 17:32 /usr/local/mysql - mysql-8.0.35-linux-glibc2.17-x86_64/usr/local/mysql/bin/mysql--version# /usr/local/mysql/bin/mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)3. 编辑配置文件3.1 主库 /etc/my.cnf141[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system log-bin mysql-bin server-id 1参数解释log-bin mysql-bin开启 binlog二进制日志主库必须开启用于记录所有更改数据的操作从库通过读取主库的 binlog 来同步数据。server-id 1服务器唯一标识主从环境中每个实例必须不同。修改 server-id 无需重启实例。3.2 从库 /etc/my.cnf142[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system server-id 2与主库的区别server-id 2从库的 server-id 必须与主库不同。从库不需要配置log-bin。MySQL 8.0 中 binlog 默认是开启的如果要关闭需设置skip_log_bin或disable_log_bin。当前从库未显式设置log-binMySQL 8.0 会默认开启。4. 创建数据目录两台均执行mkdir-p/data/mysql/3306/datachownmysql.mysql /data/mysql/3306/data/5. 初始化实例两台均执行/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf--initialize如果没有错误输出就意味着实例初始化成功。错误日志输出2026-07-19T17:33:01.67174808:00 6 [Note] [MY-010454] [Server] A temporary password is generated for rootlocalhost: th-VSde4e?i56. 配置 systemd 服务两台均执行6.1 创建 /etc/systemd/system/mysqld.service[Unit] DescriptionMySQL Server Documentationman:mysqld(8) Documentationhttp://dev.mysql.com/doc/refman/en/using-systemd.html Afternetwork.target Aftersyslog.target [Install] WantedBymulti-user.target [Service] Usermysql Groupmysql Typeforking PIDFile/data/mysql/3306/data/mysqld.pid TimeoutSec0 ExecStart/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf --pid-file/data/mysql/3306/data/mysqld.pid --daemonize $MYSQLD_OPTS EnvironmentFile-/etc/sysconfig/mysql LimitNOFILE 65535 Restarton-failure RestartPreventExitStatus1 PrivateTmpfalse6.2 创建 /etc/sysconfig/mysqlMYSQLD_OPTS7. 启动实例两台均执行systemctl daemon-reload systemctl start mysqld systemctlenablemysqld验证systemctl status mysqld# Active: active (running)systemctl is-enabled mysqld# enabled8. 修改 root 密码两台均执行# 获取临时密码greppassword /data/mysql/3306/data/mysqld.err# 2026-07-19T17:33:01.67174808:00 6 [Note] [MY-010454] [Server] A temporary password is generated for rootlocalhost: th-VSde4e?i5# 登录并修改密码/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock-palteruseruser()identifiedbyRoot123456;9. 搭建主从复制9.1 主库创建复制用户-- 在主库141执行CREATEUSERrepl%IDENTIFIEDBY123456;GRANTREPLICATIONSLAVEON*.*TOrepl%;说明复制用户用于从库连接主库拉取 binlog。密码使用123456与课程文档一致。从库上进行登录测试# 在从库142执行mysql-h192.168.195.141-urepl-p1234569.2 备份主库以 mysqldump 为例# 在主库141执行mysqldump-S/data/mysql/3306/data/mysql.sock --single-transaction-uroot-p--master-data2-E-R--triggers-A/tmp/full_backup.sql参数解释--single-transactionInnoDB 一致性快照备份不锁表。--master-data2备份文件中写入 CHANGE MASTER TO 语句注释形式记录备份时的 binlog 位置点。值为2表示注释掉值为1表示不注释。-E导出事件。-R导出存储过程和函数。--triggers导出触发器。-A导出所有数据库。备份集通过 scp 远程拷贝到从库上scp/tmp/full_backup.sql root192.168.195.142:/home/9.3 基于主库的备份恢复从库# 在从库142执行mysql-S/data/mysql/3306/data/mysql.sock-uroot-p/home/full_backup.sql9.4 获取位置点信息# 在从库142执行grep-m1CHANGE MASTER TO/home/full_backup.sql# -- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000002, MASTER_LOG_POS1789;命令行中的-m 1是只输出第一个匹配项。9.5 建立主从复制-- 在从库142执行CHANGE MASTERTOMASTER_HOST192.168.195.141,MASTER_USERrepl,MASTER_PASSWORD123456,MASTER_LOG_FILEmysql-bin.000002,MASTER_LOG_POS1789,GET_MASTER_PUBLIC_KEY1;参数解释MASTER_HOST主库的主机信息。MASTER_PORT主库端口不指定则默认为 3306。MASTER_USER复制用户。MASTER_PASSWORD复制用户的密码。MASTER_LOG_FILE从库 IO 线程启动时从主库的哪个 binlog 文件开始读取。MASTER_LOG_POS从主库 binlog 的哪个位置点开始读取。GET_MASTER_PUBLIC_KEY 1MySQL 8.0 默认使用 caching_sha2_password 认证插件非 SSL 连接时需获取主库公钥完成认证。使用 mysql_native_password 认证插件的不需要设置。9.6 开启主从复制-- 在从库142执行startslave;showslavestatus\G验证输出Slave_IO_State: Waiting for source to send event Master_Host: 192.168.195.141 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1789 Relay_Log_File: MYSQL142-relay-bin.000002 Relay_Log_Pos: 326 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1789 Relay_Log_Space: 539 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Seconds_Behind_Master: 0 Last_IO_Error: Last_SQL_Error: Master_Server_Id: 1 Master_UUID: d5728692-8354-11f1-8acf-000c29048d1f Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Auto_Position: 0 Get_master_public_key: 1重点关注Slave_IO_Running和Slave_SQL_Running两个均为 “Yes” 代表主从复制搭建成功。10. 验证主从复制10.1 主库 show processlist-- 在主库141执行showprocesslist;----------------------------------------------------------------------------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ----------------------------------------------------------------------------------------------------------------------------------------- | 5 | event_scheduler | localhost | NULL | Daemon | 408 | Waiting on empty queue | NULL | | 20 | repl | 192.168.195.142:45256 | NULL | Binlog Dump | 23 | Source has sent all binlog to replica; waiting for more updates | NULL | | 21 | root | localhost | NULL | Query | 0 | init | show processlist | -----------------------------------------------------------------------------------------------------------------------------------------Id 为 20 的连接对应的即是Binlog Dump 线程。10.2 从库 show processlist-- 在从库142执行showprocesslist;----------------------------------------------------------------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ----------------------------------------------------------------------------------------------------------------------------- | 5 | event_scheduler | localhost | NULL | Daemon | 410 | Waiting on empty queue | NULL | | 13 | system user | connecting host | NULL | Connect | 30 | Waiting for source to send event | NULL | | 14 | system user | | NULL | Query | 30 | Replica has read all relay log; waiting for more updates | NULL | | 15 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 16 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 17 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 18 | system user | | NULL | Connect | 30 | Waiting for an event from Coordinator | NULL | | 20 | root | localhost | NULL | Query | 0 | init | show processlist | -----------------------------------------------------------------------------------------------------------------------------13是IO 线程Waiting for source to send event14是协调线程Replica has read all relay log; waiting for more updates15-18是SQL 工作线程Waiting for an event from Coordinator10.3 数据同步测试主库写入测试数据-- 在主库141执行CREATEDATABASEtest_rep;USEtest_rep;CREATETABLEt1(idINTPRIMARYKEY,nameVARCHAR(20));INSERTINTOt1VALUES(1,master_data);SELECT*FROMt1;-- ------------------- | id | name |-- ------------------- | 1 | master_data |-- -----------------从库查询验证-- 在从库142执行SELECT*FROMtest_rep.t1;-- ------------------- | id | name |-- ------------------- | 1 | master_data |-- -----------------数据一致主从复制工作正常。11. 查看复制用户密码-- 在从库142执行select*frommysql.slave_master_info\G*************************** 1. row *************************** Number_of_lines: 33 Master_log_name: mysql-bin.000002 Master_log_pos: 1789 Host: 192.168.195.141 User_name: repl User_password: 123456 Port: 3306 Connect_retry: 60 Enabled_ssl: 0 Ssl_verify_server_cert: 0 Heartbeat: 30 Retry_count: 86400 Enabled_auto_position: 0 Get_public_key: 1 Master_compression_algorithm: uncompressed Master_zstd_compression_level: 3 Source_connection_auto_failover: 0 Gtid_only: 012. 查看从库重放信息-- 在从库142执行select*frommysql.slave_relay_log_info\G*************************** 1. row *************************** Number_of_lines: 14 Relay_log_name: ./MYSQL142-relay-bin.000002 Relay_log_pos: 1051 Master_log_name: mysql-bin.000002 Master_log_pos: 2514 Sql_delay: 0 Number_of_workers: 4 Id: 1 Assign_gtids_to_anonymous_transactions_type: OFF Assign_gtids_to_anonymous_transactions_value:13. 连接信息# 主库141/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456# 从库142/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot12345614. 复制原理复制的流程从库开启主从复制start slave会创建一个IO thread来连接主库。主库接受到连接请求后会创建一个Binlog Dump thread来响应它。这个线程会读取 binlog 里面的二进制日志事件发送给 Slave IO thread。Slave IO thread 接收到事件后会将其写入到本地的Relay log中。从库的SQL thread会读取 Relay log 中的二进制日志事件然后重放。复制的作用横向扩展读能力在从服务器可以执行查询工作降低主服务器压力主库写从库读降压——读写分离。很多关键的日常操作可放到从库进行如备份、数据分析等。数据灾备在从服务器进行备份避免备份期间影响主服务器服务——容灾。有丰富的生态圈。当主服务器出现问题时可以切换到从服务器——提高可用性。