mysql8.0主從複製搭建與設定方案

2022-10-02 14:01:47

mysql主從搭建

環境：ubuntu20.04.1，mysql：8.0.22。
主：192.168.87.3
備：192.168.87.6

安裝資料庫

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

資料庫設定

設定資料庫密碼

首次安裝後，使用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

更改完後重啟資料庫

systemctl restart mysql.service

建立同步賬號

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為從資料庫的IP。

檢視設定是否生效

建立資料快照

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

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

設定遠端存取

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

如果此時仍無法存取，檢視防火牆是否關閉。關閉命令：

sudo ufw disable

或者開放3306埠號。

從資料庫設定

設定/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

同步資料

在主庫上dump的檔案scp到從庫上，然後登入mysql並執行如下命令：

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 複製全螢幕