麒麟系统V10部署MySQL8及主从复制🫚
<h1><span style="color: rgba(22, 145, 121, 1)"><span style="color: rgba(186, 55, 42, 1)">麒麟系统V10部署MySQL8及主从复制</span>🫚</span></h1><hr>
<h1> </h1>
<p><span style="font-size: 16px">CPU架构是arm版，需下载对应的MySQL包。ARM 架构请用 <code data-start="896" data-end="905">aarch64</code> 包。</span></p>
<h2><span style="font-size: 24px; color: rgba(22, 145, 121, 1)">一. 下载安装包</span></h2>
<p><span style="font-size: 16px">官网：<span style="color: rgba(132, 63, 161, 1)">https://dev.mysql.com/downloads/mysql/8.0.html</span></span></p>
<pre class="language-bash highlighter-hljs"><code># wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.44-linux-glibc2.28-aarch64.tar.xz</code></pre>
<p><img src="https://img2024.cnblogs.com/blog/1513031/202512/1513031-20251223115420401-514121884.png" alt="image" height="403" width="704"></p>
<h2><span style="font-size: 24px; color: rgba(22, 145, 121, 1)">二. 安装部署</span></h2>
<p><span style="font-size: 24px; color: rgba(132, 63, 161, 1)">安装主库，从库也是如此，在此不赘述</span></p>
<hr>
<p>如有mariadb，先卸载再安装MySQL。</p>
<pre class="language-bash highlighter-hljs"><code># rpm -qa | grep -i mariadb
# rpm -e --nodeps $(rpm -qa | grep -i mariadb)</code></pre>
<p><span style="font-size: 16px">解压安装：</span></p>
<pre class="language-bash highlighter-hljs"><code># tar -xvf mysql-8.0.44-linux-glibc2.28-aarch64.tar.xz -C /usr/local/
# cd /usr/local/
# mv mysql-8.0.44-linux-glibc2.28-aarch64/ mysql-8.0.44</code></pre>
<p>创建mysql用户及组:</p>
<pre class="language-bash highlighter-hljs"><code># groupadd mysql
# useradd -r -g mysql -s /sbin/nologin mysql</code></pre>
<p>创建数据及日志目录：</p>
<pre class="language-bash highlighter-hljs"><code># mkdir /var/lib/mysql
# mkdir /var/lib/mysql/data
# chown -R mysql:mysql /var/lib/mysql
# mkdir /var/log/mysql
# chown -R mysql:mysql /var/log/mysql</code></pre>
<p>创建MySQL配置文件：</p>
<pre class="language-bash highlighter-hljs"><code>cat /etc/my.cnf
# 默认连接 MySQL 时使用的字符集
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
user=mysql
socket=/var/lib/mysql/mysql.sock
port=3306
pid-file=/var/lib/mysql/mysql.pid
basedir=/usr/local/mysql-8.0.44
datadir=/var/lib/mysql/data
lower_case_table_names = 1
#日志配置
log-error=/var/log/mysql/error.log
slow-query-log=1
slow-query-log-file=/var/log/mysql/mysql-slow.log
long_query_time=2
binlog_expire_logs_seconds=604800
# 字符集设置及排序规则
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
authentication_policy=mysql_native_password</code></pre>
<p>配置MySQL环境变量：</p>
<pre class="language-bash highlighter-hljs"><code># vim /etc/profile
export PATH=$PATH:/usr/local/mysql-8.0.44/bin
# source /etc/profile</code></pre>
<p>初始化MySQL：</p>
<pre class="language-bash highlighter-hljs"><code># /usr/local/mysql-8.0.44/bin/mysqld--defaults-file=/etc/my.cnf --initialize-insecure --user=mysql</code></pre>
<p> 使用system管理MySQL（推荐）：</p>
<pre class="language-bash highlighter-hljs"><code># vim /usr/lib/systemd/system/mysql.service
Description=MySQL Server
After=network.target remote-fs.target nss-lookup.target
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/local/mysql-8.0.44/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=65535
LimitCORE=0
Restart=on-failure
RestartSec=10
PrivateTmp=true
WantedBy=multi-user.target</code></pre>
<p>开机自启MySQL：</p>
<pre class="language-bash highlighter-hljs"><code># systemctl daemon-reload
# systemctl enable --now mysql</code></pre>
<p> </p>
<p><span style="font-size: 18px; color: rgba(132, 63, 161, 1)">登录MySQL并修改密码：</span></p>
<p>本地首次使用sock文件登录mysql是不需要密码的。</p>
<pre class="highlighter-hljs"><code># mysql -S /var/lib/mysql/mysql.sock
mysql> alter user 'root'@'localhost' identified by '123456';
mysql> flush privileges;</code></pre>
<p>修改密码后，就不能用sock方式登录，需要用以下方式登录：</p>
<pre class="language-bash highlighter-hljs"><code># mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.44 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> </code></pre>
<h2> </h2>
<hr>
<h2><span style="font-size: 24px; color: rgba(22, 145, 121, 1)">三. 配置主从复制</span></h2>
<p> </p>
<p><span style="font-size: 18px; color: rgba(132, 63, 161, 1)">采用<strong>GTID</strong>方式配置主从复制。</span></p>
<p data-path-to-node="0">GTID（Global Transaction Identifier，全局事务标识符）是 MySQL 5.6 引入，并在 8.0 版本中完善的一项核心技术。它为每一个提交的事务分配一个在全复制拓扑结构内<strong data-path-to-node="0" data-index-in-node="101">全球唯一</strong>的编号。</p>
<h3 data-path-to-node="1">1. GTID 的构成</h3>
<p data-path-to-node="2">一个 GTID 的格式通常如下： <code data-path-to-node="2" data-index-in-node="17">3E11FA47-71CA-11E1-9E33-C80AA9429562:23</code></p>
<p data-path-to-node="3">它分为两部分：</p>
<ul data-path-to-node="4">
<li>
<p data-path-to-node="4,0,0"><strong data-path-to-node="4,0,0" data-index-in-node="0">Source_ID (UUID):</strong> 产生该事务的源服务器的唯一标识（在 <code data-path-to-node="4,0,0" data-index-in-node="36">auto.cnf</code> 中定义）。</p>
</li>
<li>
<p data-path-to-node="4,1,0"><strong data-path-to-node="4,1,0" data-index-in-node="0">Transaction_ID:</strong> 在该服务器上产生的事务序列号，从 1 开始自增。</p>
</li>
</ul>
<h3 data-path-to-node="13">2. GTID 同步的工作原理</h3>
<ol start="1" data-path-to-node="14">
<li>
<p data-path-to-node="14,0,0"><strong data-path-to-node="14,0,0" data-index-in-node="0">产生标识：</strong> 当主库（Master）提交一个事务时，会自动给这个事务分配一个 GTID，并记录到 binlog 中。</p>
</li>
<li>
<p data-path-to-node="14,1,0"><strong data-path-to-node="14,1,0" data-index-in-node="0">传输日志：</strong> 事务传送到从库（Slave）并存储在 relay log 中。从库会读取这个 GTID 并将其设置为自己的 <code data-path-to-node="14,1,0" data-index-in-node="60">Retrieved_Gtid_Set</code>。</p>
</li>
<li>
<p data-path-to-node="14,2,0"><strong data-path-to-node="14,2,0" data-index-in-node="0">执行并排除：</strong> 从库的 SQL 线程读取事务。在执行前，它先检查这个 GTID 是否已经在自己的 <code data-path-to-node="14,2,0" data-index-in-node="48">Executed_Gtid_Set</code>（已执行清单）中。</p>
<ul data-path-to-node="14,2,1">
<li>
<p data-path-to-node="14,2,1,0,0">如果<strong data-path-to-node="14,2,1,0,0" data-index-in-node="2">没有</strong>：执行该事务。</p>
</li>
<li>
<p data-path-to-node="14,2,1,1,0">如果<strong data-path-to-node="14,2,1,1,0" data-index-in-node="2">已经有</strong>：说明该事务已经同步过，直接忽略（这种幂等性保证了数据不会重复）。</p>
</li>
</ul>
</li>
</ol>
<p> </p>
<p><strong><span style="color: rgba(132, 63, 161, 1); font-size: 18px">192.168.10.1（主库）</span></strong></p>
<p><strong><span style="color: rgba(132, 63, 161, 1); font-size: 18px">192.168.10.2（从库）</span></strong></p>
<h3 data-start="1061" data-end="1081"><span style="font-size: 24px">3.1 配置 my.cnf（重点）</span></h3>
<h3 data-start="1083" data-end="1110">Master 节点 <code data-start="1097" data-end="1110">/etc/my.cnf</code></h3>
<pre class="language-bash highlighter-hljs"><code># vim /etc/my.cnf
# 默认连接 MySQL 时使用的字符集
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
# ===== 基础配置 =====
user=mysql
port=3306
basedir=/usr/local/mysql-8.0.44
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/mysql.pid
# ===== 表名大小写 =====
lower_case_table_names=1
# ===== InnoDB核心配置 =====
innodb_buffer_pool_size=1G # 根据物理内存调整，一般占 60~70%
innodb_log_file_size=256M # 较大的 redo log 提升写入性能
innodb_file_per_table=ON # 每个表独立表空间，便于管理
innodb_flush_log_at_trx_commit=1 # 保证事务持久性（主库建议为 1）
sync_binlog=1 # 保证 binlog 与事务一致性（主库必须为 1）
# ===== 主从配置 =====
server-id=1
log-bin=mysql-bin # 使用相对路径，会在 datadir 下创建
binlog_format=ROW # 推荐使用 ROW 格式，保证复制一致性
binlog_row_image=FULL # 默认 FULL，保证数据完整性
relay-log=/var/lib/mysql/relay-bin # 如果主库也做级联复制，建议配置 relay-log
log_slave_updates=ON # 主库作为中间节点时需要开启
# ===== GTID 核心 =====
gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON
# ===== 日志配置 =====
log-error=/var/log/mysql/error.log
slow-query-log=1
slow-query-log-file=/var/log/mysql/mysql-slow.log
long_query_time=2
binlog_expire_logs_seconds=604800 # 7 天，和 expire_logs_days 二选一即可
# ===== 字符集配置 =====
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# ===== 认证策略 =====
authentication_policy=mysql_native_password</code></pre>
<h3 data-start="1519" data-end="1545">Slave 节点 <code data-start="1532" data-end="1545">/etc/my.cnf</code></h3>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<pre class="language-bash highlighter-hljs"><code># vim /etc/my.cnf
# 默认连接 MySQL 时使用的字符集
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
# ===== 基础配置 =====
user=mysql
port=3306
basedir=/usr/local/mysql-8.0.44
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/mysql.pid
# ===== 表名大小写 =====
lower_case_table_names=1
# ===== InnoDB核心配置 =====
innodb_buffer_pool_size=1G # 根据物理内存调整，一般占 60~70%
innodb_log_file_size=256M # 较大的 redo log 提升写入性能
innodb_file_per_table=ON # 每个表独立表空间，便于管理
innodb_flush_log_at_trx_commit=1 # 保证事务持久性（主库建议为 1）
sync_binlog=1 # 保证 binlog 与事务一致性（主库必须为 1）
# ===== 主从配置 =====
server-id=2
log-bin=mysql-bin # 使用相对路径，会在 datadir 下创建
binlog_format=ROW # 推荐使用 ROW 格式，保证复制一致性
binlog_row_image=FULL # 默认 FULL，保证数据完整性
relay-log=/var/lib/mysql/relay-bin # 如果主库也做级联复制，建议配置 relay-log
log_slave_updates=ON # 主库作为中间节点时需要开启
# ===== GTID 核心 =====
gtid_mode=ON
enforce_gtid_consistency=ON
log_replica_updates=ON
# ===== 日志配置 =====
log-error=/var/log/mysql/error.log
slow-query-log=1
slow-query-log-file=/var/log/mysql/mysql-slow.log
long_query_time=2
binlog_expire_logs_seconds=604800 # 7 天，和 expire_logs_days 二选一即可
# ===== 字符集配置 =====
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# ===== 认证策略 =====
authentication_policy=mysql_native_password</code></pre>
<p> </p>
<h3 class="overflow-y-auto p-4" dir="ltr"><span style="font-size: 24px">3.2 主库配置（Master）</span></h3>
<h4 data-start="2375" data-end="2388"><span style="font-size: 16px">1️⃣ 创建复制用户</span></h4>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="@w-xl/main:top-9 sticky top-">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">
<pre class="language-sql highlighter-hljs"><code>mysql> CREATE USER 'repl'@'192.168.10.%' IDENTIFIED BY 'Repl@123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.10.%';
mysql> FLUSH PRIVILEGES;</code></pre>
<h3> </h3>
<h3><span style="font-size: 24px">3.3 从库配置（Slave）</span></h3>
<h4><span style="font-size: 16px">1️⃣ 设置主库信息（不再指定 binlog）</span></h4>
<pre class="language-sql highlighter-hljs"><code>mysql> CHANGE MASTER TO \
MASTER_HOST='192.168.10.1', \
MASTER_USER='repl', \
MASTER_PASSWORD='Repl@123', \
MASTER_PORT=3306, \
SOURCE_AUTO_POSITION=1;</code></pre>
<h4><span style="font-size: 16px">2️⃣ 启动复制</span></h4>
<pre class="language-sql highlighter-hljs"><code>mysql> START SLAVE;</code></pre>
<h4><span style="font-size: 16px">3️⃣ 检查状态</span></h4>
<pre class="language-sql highlighter-hljs"><code>mysql> SHOW SLAVE STATUS\G</code></pre>
<p data-start="2699" data-end="2708"><strong data-start="2699" data-end="2708">必须看到：</strong></p>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-text">Slave_IO_Running: Yes
</code></div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-text">Slave_SQL_Running: Yes
</code></div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-text">Auto_Position: 1
</code></div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-text">Retrieved_Gtid_Set: uuid:1-xxx
</code></div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-text">Executed_Gtid_Set:uuid:1-xxx
</code></div>
<div class="overflow-y-auto p-4" dir="ltr"><code class="whitespace-pre! language-text">Seconds_Behind_Master: 0</code></div>
</div>
<pre class="language-sql highlighter-hljs"><code>mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.10.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 870
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 1086
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: 870
Relay_Log_Space: 2194
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bd6584ff-dfad-11f0-b121-00163e32e122
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
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: bd6584ff-dfad-11f0-b121-00163e32e122:1-6
Executed_Gtid_Set: bd6584ff-dfad-11f0-b121-00163e32e122:1-6
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)</code></pre>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="overflow-y-auto p-4" dir="ltr"> </div>
<div class="overflow-y-auto p-4" dir="ltr"> </div>
<h4 class="overflow-y-auto p-4" dir="ltr"><span style="font-size: 24px">3.4 验证 GTID 主从</span></h4>
<h2 data-start="2893" data-end="2898">主库</h2>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="overflow-y-auto p-4" dir="ltr">
<pre class="language-sql highlighter-hljs"><code>主库
mysql> CREATE DATABASE gtid_test;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE gtid_test.t1(id INT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO gtid_test.t1 VALUES (100);
Query OK, 1 row affected (0.01 sec)</code></pre>
</div>
</div>
<h2 data-start="3013" data-end="3018">从库</h2>
<div class="contain-inline-size rounded-2xl corner-superellipse/1.1 relative bg-token-sidebar-surface-primary">
<div class="@w-xl/main:top-9 sticky top-">
<div class="absolute end-0 bottom-0 flex h-9 items-center pe-2">
<div class="bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs">
<pre class="language-sql highlighter-hljs"><code>从库
mysql> SELECT * FROM gtid_test.t1;
+------+
| id |
+------+
|100 |
+------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| gtid_test |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)</code></pre>
</div>
</div>
</div>
<div class="overflow-y-auto p-4" dir="ltr"><span style="font-size: 18px"><strong><span style="color: rgba(22, 145, 121, 1)">验证成功！</span></strong></span></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div><br><br>
来源：https://www.cnblogs.com/haoee/p/19386401
頁:
[1]