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

—— 本文基于MySQL 5.7.36進(jìn)行演示

1、下載MySQL安裝包

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

2、解壓MySQL安裝包

將以下包上傳至服務(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

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）按順序執(zhí)行以下命令：

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）命令成功執(zhí)行后，則安裝成功

4、修改my.cnf文件

# 修改my.cnf文件：
vim /etc/my.cnf
?
# 配置內(nèi)容如下：
?
[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 #主從節(jié)點(diǎn)的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）啟動(dòng)mysqld服務(wù)

systemctl start mysqld

2）查看MySQL初始密碼

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

6、MySQL權(quán)限控制

1）登錄MySQL客戶端

sudo mysql -uroot -p;

2）修改密碼

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

3）切換數(shù)據(jù)庫(kù)

use mysql;

4）設(shè)置允許遠(yuǎn)程訪問(wèn)

UPDATE user SET host = '%' WHERE user = 'root';
?
flush privileges; 
?
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; 
?
flush privileges;

5）新建戶mysql用戶（用于建立主從狀態(tài)）

# 生產(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;

7、配置主從（在從庫(kù)執(zhí)行以下命令）

1）登錄MySQL客戶端

sudo mysql -umysql -p;

2）切換至mysql數(shù)據(jù)庫(kù)，并建立主從連接

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 TO
MASTER_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;

3）啟動(dòng)slave

start slave;

4）查看主從狀態(tài)

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

8、安裝數(shù)據(jù)同步工具-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、數(shù)據(jù)同步

1）數(shù)據(jù)備份

主節(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]        ...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!
?
# 主庫(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

2）數(shù)據(jù)加載

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

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

# 安裝qpress
yum install qpress -y
?
# 解壓縮
nohup innobackupex --decompress --parallel=20 /data/mysql_slave_bak/xxx &
?
# 數(shù)據(jù)恢復(fù)
innobackupex --apply-log /data/mysql_slave_bak/xxx
?
# 停止slave
stop slave;
?
# 停止mysql
systemctl 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

3）數(shù)據(jù)同步

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

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

# 啟動(dòng)mysql
systemctl start mysqld
?
# 恢復(fù)主從狀態(tài)
mysql -umysql -p
?
CHANGE MASTER TO
MASTER_HOST = '主節(jié)點(diǎn)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;
?
# 啟動(dòng)slave
start slave;
?
# 通過(guò)查看主數(shù)據(jù)庫(kù)的條數(shù)來(lái)判斷數(shù)據(jù)是否同步
select count(*) from xxx;

4）數(shù)據(jù)回滾

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

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

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

10、常用命令

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