本文分享自天翼云開(kāi)發(fā)者社區(qū)《搭建MySQL主從》，作者：2****m

官網(wǎng)網(wǎng)址：https://downloads.mysql.com/archives/community

將以下包上傳至服務(wù)器：

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

yum install net-tools -y

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

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

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

# 修改my.cnf文件：vim /etc/my.cnf# 配置內(nèi)容如下：[mysqld]port=9001max_connections=1000max_connect_errors=10character-set-server=UTF8MB4default-storage-engine=INNODBdefault_authentication_plugin=mysql_native_password
server-id = 20001 #主從節(jié)點(diǎn)的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

systemctl start mysqld

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

sudo mysql -uroot -p;

# 生產(chǎn)環(huán)境必須設(shè)置強(qiáng)密碼！??！ALTER USER "root"@"localhost" IDENTIFIED BY "密碼";

flush privileges;

use mysql;

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

flush privileges; 

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

flush privileges;

# 生產(chǎn)環(huán)境必須設(shè)置強(qiáng)密碼！??！CREATE USER 'mysql'@'%' IDENTIFIED WITH mysql_native_password BY '密碼';GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' WITH GRANT OPTION;

flush privileges;

sudo mysql -umysql -p;

use mysql;# 生產(chǎn)環(huán)境必須設(shè)置強(qiáng)密碼?。?！# MASTER_LOG_FILE和MASTER_LOG_POS可在master節(jié)點(diǎn)通過(guò) show master status 命令查看。CHANGE MASTER TOMASTER_HOST = '主節(jié)點(diǎn)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;

start slave;

# 如果輸出結(jié)果中Slave_IO_Running和Slave_SQL_Running值都為YES，則主從狀態(tài)正常show slave status;

# 上傳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

主節(jié)點(diǎn)數(shù)據(jù)備份

以下操作在主節(jié)點(diǎn)上執(zhí)行

# 創(chuàng)建數(shù)據(jù)備份目錄mkdir -p /data/mysql_master_bak# 創(chuàng)建備份用戶CREATE USER 'mysql_bak'@'%' IDENTIFIED WITH mysql_native_password BY '密碼';
GRANT ALL PRIVILEGES ON *.* TO 'mysql_bak'@'%' WITH GRANT OPTION;
flush privileges; 

# 數(shù)據(jù)備份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# 數(shù)據(jù)驗(yàn)證，查看是否有報(bào)錯(cuò)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]        ...done230822 10:34:48 [00] Compressing /data/mysql_master_bak/2023-08-22_10-34-47/xtrabackup_info.qp
230822 10:34:48 [00]        ...donextrabackup: Transaction log of lsn (2797339) to (2797348) was copied.
230822 10:34:50 completed OK!# 主庫(kù)數(shù)據(jù)同步至備機(jī)nohup rsync -e ssh -avr /data/mysql_master_bak/xxx/ 從節(jié)點(diǎn)ip:/data/mysql_slave_bak/xxx# 數(shù)據(jù)驗(yàn)證，查看是否有報(bào)錯(cuò)cat nohup.out

從節(jié)點(diǎn)數(shù)據(jù)備份

以下操作在從節(jié)點(diǎn)上執(zhí)行

# 創(chuàng)建數(shù)據(jù)備份目錄mkdir -p /data/mysql_slave_bak

從節(jié)點(diǎn)數(shù)據(jù)備份

以下操作在從節(jié)點(diǎn)上執(zhí)行

# 安裝qpressyum install qpress -y# 解壓縮nohup innobackupex --decompress --parallel=20 /data/mysql_slave_bak/xxx &# 數(shù)據(jù)恢復(fù)innobackupex --apply-log /data/mysql_slave_bak/xxx# 停止slavestop slave;# 停止mysqlsystemctl stop mysqld# 原始目錄備份mv /data/mysql /data/mysql_bak# 數(shù)據(jù)源切換mv /data/mysql_slave_bak/xxx /data/mysql# 給數(shù)據(jù)源賦權(quán)chown -R mysql:mysql /data/mysql

從節(jié)點(diǎn)數(shù)據(jù)備份

以下操作在從節(jié)點(diǎn)上執(zhí)行

# 啟動(dòng)mysqlsystemctl start mysqld# 恢復(fù)主從狀態(tài)mysql -umysql -p

CHANGE MASTER TOMASTER_HOST = '主節(jié)點(diǎn)ip',
MASTER_USER = 'mysql',
MASTER_PASSWORD = '密碼',
MASTER_PORT = 9001,MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx, # 填寫(xiě)第一步驟查看到的值
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;# 啟動(dòng)slavestart slave;# 通過(guò)查看主數(shù)據(jù)庫(kù)的條數(shù)來(lái)判斷數(shù)據(jù)是否同步select count(*) from xxx;

從節(jié)點(diǎn)數(shù)據(jù)備份

以下操作在從節(jié)點(diǎn)上執(zhí)行

# 將數(shù)據(jù)源進(jìn)行更換，重復(fù)第二步驟即可。

# 導(dǎo)出mysqldump –u [username] –p [password] databaseName > [objectName];# 導(dǎo)入source [objectName];# 鎖表（防止數(shù)據(jù)寫(xiě)入）flush tables with read lock;# 解表unlock tables;# 跳過(guò)錯(cuò)誤，恢復(fù)主從狀態(tài)set global sql_slave_skip_counter=1;