mysql 學習筆記之主從復制 Replication

1. 主從復制 Replication

3.1 架構圖

3.2 參數配置

3.2.1 創建啟閉腳本

login.sh

#!/bin/bash
mysql -h227.0.0.1 -uroot -P3306 -p'Yxc@3306'

start.sh

#!/bin/bash
mysqld_safe --defaults-file=/data/mysql/3306/my3306.cnf --user=mysql &

shutdown.sh

#!/bin/bash
/usr/local/mysql/bin/mysqladmin -uroot -P3306 -p'Yxc@3306' -S /data/mysql/3306/mysql3306.sock shutdown

3.2.2 編輯主節點配置文件

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
port=3306
socket=/data/mysql/3306/mysql3306.sock
character-set-server=utf8
log-error=/data/mysql/3306/log/error3306.log
pid-file=/data/mysql/3306/mysqld3306.pid
log_bin=mysql_bin
binlog-format=Row
server-id=51

3.2.3 創建從節點配置文件

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
port=3306
socket=/data/mysql/3306/mysql3306.sock
character-set-server=utf8
log-error=/data/mysql/3306/log/error3306.log
pid-file=/data/mysql/3306/mysqld3306.pid
log-bin=mysql_bin
server-id=52
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

3.3 創建用戶

create user 'repl'@'%' identified by 'repl';                  grant replication slave on *.* to 'repl'@'%';

3.4 配置復制

主庫上執行命令，獲取當前binlog日志及位置

show master status;

獲取file，position信息，在兩個從庫上分別執行如下腳本

CHANGE MASTER TO
MASTER_HOST='192.168.56.5',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql_bin.000002',
MASTER_LOG_POS=154;

從庫上，啟動復制，并查看復制狀態

start slave;
show slave status;

^說明: 如出現如圖字樣，表示成功。關于slave的命令，可以通過help slave;