麒麟操作系统 MySQL 主从搭建

MySQL rpm64 架构搭建主从

环境介绍

一主两从

操作系统	Kylin Linux Advanced Server V10 (Lance)	Kylin Linux Advanced Server V10 (Lance)	Kylin Linux Advanced Server V10 (Lance)
内核版本	Linux 4.19.90-52.22.v2207.ky10.aarch64	Linux 4.19.90-52.22.v2207.ky10.aarch64	Linux 4.19.90-52.22.v2207.ky10.aarch64
IP	192.168.31.82 (主)	192.168.31.83 （从）	192.168.31.84（从)
Mysql 版本	5.7.27	5.7.27	5.7.27

1.检查操作系统

# 一定要注意查看本机的操作系统，是amd(x86)还是arm(aarch)架构
[root@192.168.31.82 ~]# uname -a
Linux 192.168.31.82 4.19.90-52.22.v2207.ky10.aarch64 #1 SMP Tue Mar 14 11:52:45 CST 2023 aarch64 aarch64 aarch64 GNU/Linux[root@192.168.31.821 ~]# cat /etc/os-release
NAME="Kylin Linux Advanced Server"
VERSION="V10 (Lance)"
ID="kylin"
VERSION_ID="V10"
PRETTY_NAME="Kylin Linux Advanced Server V10 (Lance)"
ANSI_COLOR="0;31"# 注意：MsSQL8.0开始才支持arm架构，我们可以去第三方下载编译好的安装包，或者可以采取docker安装

2.配置基础环境

# 关闭防火墙禁止开机自动启动
systemctl stop firewalld.service && systemctl disable firewalld.service
# 关闭SELINUX
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux
# 创建mysql用户和组，mysql用户不能登录系统选项，不创建用户的主目录。
#groupadd -r mysql && useradd -r -g mysql -s /sbin/nologin -M mysql
# 卸载系统自带的依赖包，会存在冲突
rpm -qa | grep mariadb
# --nodeps 排除依赖，否则别的软件依赖mysql,无法删除
rpm -e --nodeps mariadb-connector-c-3.0.6-8.ky10.aarch64
rpm -qa | grep mysql
rpm -e --nodeps 删除已经安装的MYSQL包

3.下载软件并安装

# 前往官方地址下载adm（x86）包
https://downloads.mysql.com/archives/community/# #前往华为镜像站下载arm(aarch) rpm包
https://mirrors.huaweicloud.com/kunpeng/yum/el/7/aarch64/Packages/database/mysql-5.7.27-1.el7.aarch64.rpm
# https://obs.cn-north-4.myhuaweicloud.com/obs-mirror-ftp4/database/mysql-5.7.27-aarch64.tar.gz  tar包# 安装mysql
rpm -ivh  mysql-5.7.27-1.el7.aarch64.rpm# 配置环境变量
vi /etc/profile.d/mysql.sh#!bin/bash
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin# 生效
source /etc/profile.d/mysql.sh# 查看mysql版本
mysqladmin --version# 如果 mysqladmin --version 出现mysqladmin: error while loading shared libraries: libatomic.so.1: cannot open shared object file: No such file or directory  请执行 yum install -y libatomic # 再次查看
mysqladmin --version
[root@192.168.31.82 ~]# mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.7.27, for Linux on aarch64# 设置开机自启动
设置开机启动
cp -rf /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
systemctl enable mysqld

4. 服务初始化

vi /etc/my.cnf
[mysqld_safe]
log-error=/data/mysql/logs/mysqld.log
pid-file=/data/mysql/run/mysqld.pid
[mysqldump]
quick
[mysql]
no-auto-rehash
[client]
default-character-set=utf8
[mysqld]
basedir=/usr/local/mysql
socket=/data/mysql/run/mysql.sock
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
socket=/data/mysql/run/mysql.sock
port=3306
user=mysql
character-set-server=utf8
collation-server=utf8_general_ci
skip-grant-tables# 创建对应得目录
mkdir -p /data/mysqlmkdir -p /data/mysql/data
mkdir -p /data/mysql/tmp
mkdir -p /data/mysql/run
mkdir -p /data/mysql/logstouch /data/mysql/logs/mysqld.logchown -R mysql:mysql /data/mysql# 为了保证数据库目录为与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化：
# -- --defaults-file 指定配置文件路径
mysqld --initialize --user=mysql
[root@192.168.31.82 data]# mysqld --initialize --user=mysql
2024-09-14T09:54:43.665757Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-09-14T09:54:43.887195Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-09-14T09:54:43.908074Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-09-14T09:54:43.962353Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5e42cb27-727f-11ef-a6ab-5254004585b3.
2024-09-14T09:54:43.963217Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-09-14T09:54:43.963580Z 1 [Note] A temporary password is generated for root@localhost: Quw6#34yf19!touch /data/mysql/data/k8s-master-01.pid
chown -R mysql:mysql /data/mysql
# 另外 --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登录后你需要设置一个新的密码 
# 启动服务
systemctl start mysqld# 连接
mysql -h 127.0.0.1 -u root -p ALTER USER 'root'@'localhost' IDENTIFIED BY 'yuanfang_2024';# 再次登录
mysql -h 127.0.0.1 -u root -p [root@192.168.31.82 ~]# mysql -h 127.0.0.1 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.27 Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.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> # 确定可以远程连接的账户
select  * from mysql.user where user='root' and host='%'; mysql> select  * from mysql.user where user='root' and host='%';
Empty set (0.00 sec)# 添加一个用户
grant all privileges on *.*  to root@'%'  identified by 'yuanfang_2024';# 确认用户root的密码是正确的。如果密码错误，你需要重置密码：
SET PASSWORD FOR root@'%' = PASSWORD('yuanfang_2024');

5 主从搭建

5.1 主节点配置(192.168.31.82)

# 修改 /etc/my.cnf
[mysqld]
skip-character-set-client-handshake
skip-name-resolve
# 注意：skip-name-resolve一定要加，不然连接mysql会超级慢# 添加master主从复制部分配置
server_id=1
log-bin=mysql-bin # 二进制日志
read-only=0  #主库是写库
# 不用同步的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

5.2 从节点配置(192.168.31.83)

# 修改 /etc/my.cnf
[mysqld]
skip-character-set-client-handshake
skip-name-resolve
# 注意：skip-name-resolve一定要加，不然连接mysql会超级慢# 添加master主从复制部分配置
server_id=2
relay-log=mysql-relay
read-only=1  #从库是读库# 不用同步的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

5.3 从节点配置(192.168.31.84)

# 修改 /etc/my.cnf
[mysqld]
skip-character-set-client-handshake
skip-name-resolve
# 注意：skip-name-resolve一定要加，不然连接mysql会超级慢# 添加master主从复制部分配置
server_id=3
relay-log=mysql-relay
read-only=1  #从库是读库# 不用同步的库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

5.4 节点都重启

systemctl restart mysqld

5.5 在主机上建立帐户并授权slave

# 登录到主节点mysql上
mysql -h 127.0.0.1 -u root -p # 删除用户
# drop user root;# 创建用户
# create user root@'%' identified by 'yuanfang_2024';# 刷新权限
FLUSH PRIVILEGES;# 授权
grant all on *.* to root@'%' identified by 'yuanfang_2024' with grant option;# 添加用来同步的用户
grant replication slave on *.* to slave@'%' identified by 'YUANfang888';# 查看master状态
show master status;mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1080 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.6 salve 来同步master数据

# 登录到主节点mysql上
mysql -h 127.0.0.1 -u root -p # 设置主库连接
change master to master_host='192.168.31.82',master_user='slave',master_password='YUANfang888',master_log_file='mysql-bin.000001',master_log_pos=0,master_port=3306;# 启动从库同步
start slave;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)# 查看从库状态
show slave status;