本文分享自天翼云开发者社区《搭建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
2）删除 MySQL 和 mariadb
rpm -qa | grep mysql | xargs rpm -e --nodepsrpm -qa | grep mariadb| xargs rpm -e --nodeps
复制代码
3）按顺序执行以下命令：
rpm -ivh mysql-community-common-5.7.43-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-5.7.43-1.el7.x86_64.rpmrpm -ivh mysql-community-client-5.7.43-1.el7.x86_64.rpmrpm -ivh mysql-community-server-5.7.43-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-compat-5.7.43-1.el7.x86_64.rpmrpm -ivh mysql-community-devel-5.7.43-1.el7.x86_64.rpm
复制代码
4）命令成功执行后，则安装成功
4、修改 my.cnf 文件
# 修改my.cnf文件：vim /etc/my.cnf# 配置内容如下：[mysqld]port=9001max_connections=1000max_connect_errors=10character-set-server=UTF8MB4default-storage-engine=INNODBdefault_authentication_plugin=mysql_native_passwordserver-id = 20001 #主从节点的server-id不同log-bin=mysql-binauto_increment_offset=1auto_increment_increment=2sync_binlog=1innodb_flush_log_at_trx_commit=1binlog_format=MIXEDlog-slave-updates=trueopen_files_limit=655350sql_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/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
复制代码
5、初始化 MySQL 密码
1）启动 mysqld 服务
2）查看 MySQL 初始密码
cat /var/log/mysqld.log | grep localhost
复制代码
6、MySQL 权限控制
1）登录 MySQL 客户端
2）修改密码
# 生产环境必须设置强密码！！！ALTER USER "root"@"localhost" IDENTIFIED BY "密码";flush privileges;
复制代码
3）切换数据库
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 客户端
2）切换至 mysql 数据库，并建立主从连接
use mysql;# 生产环境必须设置强密码！！！# MASTER_LOG_FILE和MASTER_LOG_POS可在master节点通过 show master status 命令查看。CHANGE MASTER TOMASTER_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
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# 安装libevrpm -ivh libev-4.15-7.el7.x86_64.rpm# 安装perconayum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm# 安装percona-xtrabackup-24yum 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.qp230822 10:34:48 [00] ...done230822 10:34:48 [00] Compressing /data/mysql_master_bak/2023-08-22_10-34-47/xtrabackup_info.qp230822 10:34:48 [00] ...donextrabackup: 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）数据加载
从节点数据备份
以下操作在从节点上执行
# 安装qpressyum install qpress -y# 解压缩nohup innobackupex --decompress --parallel=20 /data/mysql_slave_bak/xxx &# 数据恢复innobackupex --apply-log /data/mysql_slave_bak/xxx# 停止slavestop slave;# 停止mysqlsystemctl stop mysqld# 原始目录备份mv /data/mysql /data/mysql_bak# 数据源切换mv /data/mysql_slave_bak/xxx /data/mysql# 给数据源赋权chown -R mysql:mysql /data/mysql
复制代码
3）数据同步
从节点数据备份
以下操作在从节点上执行
# 启动mysqlsystemctl start mysqld# 恢复主从状态mysql -umysql -pCHANGE MASTER TOMASTER_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;# 启动slavestart 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;
复制代码
评论