mysql8.0主從復制搭建與配置方案

更新時間：2022年09月30日 08:32:38 作者：天山琴子

MySQL主從復制是一個異步的復制過程，就是有兩個數(shù)據(jù)庫服務器,一個是主(master)數(shù)據(jù)庫服務器,另一個是從(slave)數(shù)據(jù)庫服務器。

mysql主從搭建

環(huán)境：ubuntu20.04.1，mysql：8.0.22。
主：192.168.87.3
備：192.168.87.6

安裝數(shù)據(jù)庫

sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev

數(shù)據(jù)庫配置

設置數(shù)據(jù)庫密碼

首次安裝后，使用sudo mysql -uroot -p直接進入，更改root密碼操作如下：

use mysql；
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
FLUSH PRIVILEGES;

主庫設置

配置/etc/mysql/mysql.conf.d/mysqld.cnf如下：

[mysqld]

user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket	= /var/run/mysqld/mysqld.sock
port		= 3306
datadir	= /var/lib/mysql


bind-address		= 192.168.87.3 # 本機ip
mysqlx-bind-address	= 127.0.0.1


key_buffer_size		= 16M

myisam-recover-options  = BACKUP

max_connections        = 1000

log_error = /var/log/mysql/error.log

server-id		= 1
log_bin			= /var/log/mysql/mysql-bin.log
max_binlog_size   = 100M
binlog_do_db          = test
binlog_ignore_db      = mysql
binlog_format = row
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

更改完后重啟數(shù)據(jù)庫

systemctl restart mysql.service

創(chuàng)建同步賬號

CREATE USER 'sync'@'192.168.87.6' IDENTIFIED WITH mysql_native_password BY 'sync';
grant replication slave on *.* to 'sync'@'192.168.87.6';

192.168.87.6為從數(shù)據(jù)庫的IP。

查看配置是否生效

創(chuàng)建數(shù)據(jù)快照

mysqldump --all-databases --master-data &gt; dbdump.db

–master-data這個選項會自動加上CHANGE_MASTER_TO給從機來開始復制過程。在備份時使用–databases(備份特定的數(shù)據(jù)庫)和–ignore-tables(排除備份特定的表) 選項，各個數(shù)據(jù)庫和表名之間用空格隔開。

設置遠程訪問

use mysql;
update user set host='%' where user = 'root';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

如果此時仍無法訪問，查看防火墻是否關閉。關閉命令：

sudo ufw disable

或者開放3306端口號。

從數(shù)據(jù)庫配置

配置/etc/mysql/mysql.conf.d/mysqld.cnf如下：

[mysqld]

user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket	= /var/run/mysqld/mysqld.sock
port		= 3306
datadir	= /var/lib/mysql

bind-address		= 192.168.87.6
mysqlx-bind-address	= 127.0.0.1
key_buffer_size		= 16M

myisam-recover-options  = BACKUP

log_error = /var/log/mysql/error.log

server-id		= 2
log_bin			= /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds	= 2592000
max_binlog_size   = 100M
binlog_do_db		= test
binlog_ignore_db	= mysql

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

在主庫上dump的文件scp到從庫上，然后登錄mysql并執(zhí)行如下命令：

set sql_log_bin=0;
source /home/shitianming/Documents/dbdump.db

配置slave

CHANGE MASTER TO
MASTER_HOST='192.168.87.3',
MASTER_USER='sync',
MASTER_PASSWORD='sync',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=730;

SHELL 復制全屏