搭建MySQL主从 - HelloWorld开发者社区

本文分享自天翼云开发者社区《搭建MySQL主从》，作者：2****m

—— 本文基于MySQL 5.7.36进行演示

1、下载MySQL安装包

官网网址：https://downloads.mysql.com/archives/community

2、解压MySQL安装包

将以下包上传至服务器：

mysql-community-common-5.7.43-1.el7.x86_64.rpm

mysql-community-libs-5.7.43-1.el7.x86_64.rpm

mysql-community-client-5.7.43-1.el7.x86_64.rpm

mysql-community-server-5.7.43-1.el7.x86_64.rpm

mysql-community-libs-compat-5.7.43-1.el7.x86_64.rpm

mysql-community-devel-5.7.43-1.el7.x86_64.rpm

3、安装MySQL

1）安装net-tools

yum install net-tools -y

2）删除MySQL和mariadb

rpm -qa | grep mysql | xargs rpm -e --nodeps
rpm -qa | grep mariadb| xargs rpm -e --nodeps

3）按顺序执行以下命令：

rpm -ivh  mysql-community-common-5.7.43-1.el7.x86_64.rpm
rpm -ivh  mysql-community-libs-5.7.43-1.el7.x86_64.rpm
rpm -ivh  mysql-community-client-5.7.43-1.el7.x86_64.rpm
rpm -ivh  mysql-community-server-5.7.43-1.el7.x86_64.rpm
rpm -ivh  mysql-community-libs-compat-5.7.43-1.el7.x86_64.rpm
rpm -ivh  mysql-community-devel-5.7.43-1.el7.x86_64.rpm

4）命令成功执行后，则安装成功

4、修改my.cnf文件

# 修改my.cnf文件：
vim /etc/my.cnf
# 配置内容如下：
[mysqld]
port=9001
max_connections=1000
max_connect_errors=10
character-set-server=UTF8MB4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
server-id = 20001 #主从节点的server-id不同
log-bin=mysql-bin
auto_increment_offset=1
auto_increment_increment=2
sync_binlog=1
innodb_flush_log_at_trx_commit=1
binlog_format=MIXED
log-slave-updates=true
open_files_limit=655350
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

5、初始化MySQL密码

1）启动mysqld服务

systemctl start mysqld

2）查看MySQL初始密码

cat /var/log/mysqld.log | grep localhost

6、MySQL权限控制

1）登录MySQL客户端

sudo mysql -uroot -p;

2）修改密码

# 生产环境必须设置强密码！！！
ALTER USER "root"@"localhost" IDENTIFIED BY "密码";
flush privileges;

3）切换数据库

use mysql;

4）设置允许远程访问

UPDATE user SET host = '%' WHERE user = 'root';
flush privileges; 

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; 

flush privileges;

5）新建户mysql用户（用于建立主从状态）

# 生产环境必须设置强密码！！！
CREATE USER 'mysql'@'%' IDENTIFIED WITH mysql_native_password BY '密码';

GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' WITH GRANT OPTION;

flush privileges;

7、配置主从（在从库执行以下命令）

1）登录MySQL客户端

sudo mysql -umysql -p;

2）切换至mysql数据库，并建立主从连接

use mysql;

# 生产环境必须设置强密码！！！
# MASTER_LOG_FILE和MASTER_LOG_POS可在master节点通过 show master status 命令查看。
CHANGE MASTER TO
MASTER_HOST = '主节点ip',
MASTER_USER = 'mysql',
MASTER_PASSWORD = '密码',
MASTER_PORT = 9001,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=528,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;

3）启动slave

start slave;

4）查看主从状态

# 如果输出结果中Slave_IO_Running和Slave_SQL_Running值都为YES，则主从状态正常
show slave status;

8、安装数据同步工具-percona

# 上传libev-4.15-7.el7.x86_64.rpm安装包

# 下载路径：http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4%28%29%2864bit%29&submit=Search+...&system=&arch=
cd /usr/local/app_isntall

# 安装libev
rpm -ivh libev-4.15-7.el7.x86_64.rpm

# 安装percona
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 安装percona-xtrabackup-24
yum install -y percona-xtrabackup-24

9、数据同步

1）数据备份

主节点数据备份以下操作在主节点上执行

# 创建数据备份目录
mkdir -p /data/mysql_master_bak
# 创建备份用户
CREATE USER 'mysql_bak'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
GRANT ALL PRIVILEGES ON *.* TO 'mysql_bak'@'%' WITH GRANT OPTION;
flush privileges; 
# 数据备份
innobackupex --defaults-file=/etc/my.cnf --user=mysql_bak --password='密码'  --compress --parallel=20 --throttle=20 --rsync /data/mysql_master_bak 2>/data/mysql_master_bak/error.log
# 数据验证，查看是否有报错
cat /data/mysql_master_bak/error.log
# error.log日志中有记录MASTER_LOG_FILE和MASTER_LOG_POS的值。
230822 10:34:48 Backup created in directory '/data/mysql_master_bak/2023-08-22_10-34-47/'
MySQL binlog position: filename 'mysql-bin.000002', position '16007'
230822 10:34:48 [00] Compressing /data/mysql_master_bak/2023-08-22_10-34-47/backup-my.cnf.qp
230822 10:34:48 [00]        ...done
230822 10:34:48 [00] Compressing /data/mysql_master_bak/2023-08-22_10-34-47/xtrabackup_info.qp
230822 10:34:48 [00]        ...done
xtrabackup: Transaction log of lsn (2797339) to (2797348) was copied.
230822 10:34:50 completed OK!

# 主库数据同步至备机
nohup rsync -e ssh -avr /data/mysql_master_bak/xxx/ 从节点ip:/data/mysql_slave_bak/xxx

# 数据验证，查看是否有报错
cat nohup.out
从节点数据备份

以下操作在从节点上执行

# 创建数据备份目录
mkdir -p /data/mysql_slave_bak

2）数据加载

从节点数据备份以下操作在从节点上执行

# 安装qpress
yum install qpress -y

# 解压缩
nohup innobackupex --decompress --parallel=20 /data/mysql_slave_bak/xxx &

# 数据恢复
innobackupex --apply-log /data/mysql_slave_bak/xxx

# 停止slave
stop slave;

# 停止mysql
systemctl stop mysqld

# 原始目录备份
mv /data/mysql /data/mysql_bak

# 数据源切换
mv /data/mysql_slave_bak/xxx /data/mysql

# 给数据源赋权
chown -R mysql:mysql /data/mysql

3）数据同步

从节点数据备份以下操作在从节点上执行

# 启动mysql
systemctl start mysqld

# 恢复主从状态
mysql -umysql -p

CHANGE MASTER TO
MASTER_HOST = '主节点ip',
MASTER_USER = 'mysql',
MASTER_PASSWORD = '密码',
MASTER_PORT = 9001,
MASTER_LOG_FILE='mysql-bin.xxx',
MASTER_LOG_POS=xxx, # 填写第一步骤查看到的值
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;

# 启动slave
start slave;

# 通过查看主数据库的条数来判断数据是否同步
select count(*) from xxx;

4）数据回滚

从节点数据备份以下操作在从节点上执行

# 将数据源进行更换，重复第二步骤即可。

10、常用命令

# 导出
mysqldump –u [username] –p [password] databaseName > [objectName];
# 导入
source [objectName];
# 锁表（防止数据写入）
flush tables with read lock;
# 解表
unlock tables;
# 跳过错误，恢复主从状态
set global sql_slave_skip_counter=1;