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

當你的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;

⇩⇩⇩!謝感,去下營經續繼費經夠足有才告廣擊點人五有要需少最天一,告廣擊點我幫請,話的您到助幫有章文的我果如 如果我的文章有幫助到您的話,請幫我點擊廣告,一天最少需要有五人點擊廣告才有足夠經費繼續經營下去,感謝! ⇧⇧⇧

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *