[ Mysql ] – Mysql Master Slave Setting Mysql 主從設定

當你的mysql效能低落的時候除了修改設定檔之外，最直接好用的方法就是讀寫分離，設定的方式也還算簡單。

• Mysql Master 端 192.168.1.33
• Mysql Slave 端
• 重啟 mysql
• 防火牆

Mysql Master 端 192.168.1.33

修改設定檔

nano /etc/mysql/mysql.conf.d/mysqld.cnf

在[mysqld]下方新增以下資訊

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

重啟 mysql
指令：

sudo service mysql restart

登入 mysql 創建帳號給slave同步用

CREATE USER 'account'@'ip' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON . TO 'account'@'ip';
flush privileges;

查看 master 訊息
指令：

SHOW MASTER STATUS\G

File: mysql-bin.000001
Position: 245

這兩個值設slave會用到

Mysql Slave 端

修改設定檔

nano /etc/mysql/mysql.conf.d/mysqld.cnf

在[mysqld]下方新增以下資訊

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

重啟 mysql
指令：

sudo service mysql restart

登入 mysql 加入同步設定

CHANGE MASTER TO MASTER_HOST ='192.168.1.33', MASTER_USER ='account', MASTER_PASSWORD ='password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 245;

CHANGE MASTER TO MASTER_HOST =’主資料庫IP’, MASTER_USER =’主資料庫剛新增要來同步的帳號’, MASTER_PASSWORD =’abcdef’, MASTER_LOG_FILE = ‘主資料庫的二進制檔’, MASTER_LOG_POS = 記錄檔位置

start slave;

重啟 mysql

指令：

sudo service mysql restart

登入 mysql 查看 slave 訊息，如果有錯誤的話訊息會在這邊出現

show slave status\G

防火牆

AWS 安全組 入站 3306 192.168.1.33

一般主機就是 iptable 針對 192.168.1.33 開3306 port

2020-07-10
若slave端有error，通常是資料不一致時，master端的 Position 就會有異動，此時 slave端就要重新建立同步。

stop slave;
CHANGE MASTER TO MASTER_HOST ='192.168.1.33', MASTER_USER ='account', MASTER_PASSWORD ='password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 245;

CHANGE MASTER TO MASTER_HOST =’主資料庫IP’, MASTER_USER =’主資料庫剛新增要來同步的帳號’, MASTER_PASSWORD =’abcdef’, MASTER_LOG_FILE = ‘主資料庫的二進制檔’, MASTER_LOG_POS = 新的記錄檔位置;
start slave;

最後再重啟 slave mysql

service mysql restart;

2022-05-24
停止同步後的第二種處理方法

Master

show master status\g

+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.013632 | 23873964 | | | |
+——————+———-+————–+——————+——————-+

Slave

show slave status\g
//會出現為什麼停止同步的錯誤訊息

stop slave;

change master to master_log_file=’mysql-bin.013632′, master_log_pos=23873964;

start slave;

show slave status\g