搭建MySQL主从
本文分享自天翼云开发者社区《搭建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 -y2）删除MySQL和mariadb
rpm -qa | grep mysql | xargs rpm -e --nodeps
rpm -qa | grep mariadb| xargs rpm -e --nodeps3）按顺序执行以下命令：
rpm -ivhmysql-community-common-5.7.43-1.el7.x86_64.rpm
rpm -ivhmysql-community-libs-5.7.43-1.el7.x86_64.rpm
rpm -ivhmysql-community-client-5.7.43-1.el7.x86_64.rpm
rpm -ivhmysql-community-server-5.7.43-1.el7.x86_64.rpm
rpm -ivhmysql-community-libs-compat-5.7.43-1.el7.x86_64.rpm
rpm -ivhmysql-community-devel-5.7.43-1.el7.x86_64.rpm4）命令成功执行后，则安装成功
4、修改my.cnf文件
# 修改my.cnf文件：
vim /etc/my.cnf
# 配置内容如下：
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.pid5、初始化MySQL密码
1）启动mysqld服务
systemctl start mysqld2）查看MySQL初始密码
cat /var/log/mysqld.log | grep localhost6、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-249、数据同步
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 Compressing /data/mysql_master_bak/2023-08-22_10-34-47/backup-my.cnf.qp
230822 10:34:48 ...done
230822 10:34:48 Compressing /data/mysql_master_bak/2023-08-22_10-34-47/xtrabackup_info.qp
230822 10:34:48 ...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_bak2）数据加载
从节点数据备份
以下操作在从节点上执行
# 安装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/mysql3）数据同步
从节点数据备份
以下操作在从节点上执行
# 启动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 –p databaseName > ;
# 导入
source ;
# 锁表（防止数据写入）
flush tables with read lock;
# 解表
unlock tables;
# 跳过错误，恢复主从状态
set global sql_slave_skip_counter=1;
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]