linux mysql主從複製配置

標籤：二進位檔案   線程狀態   不同   mysq   根據   就是   使用者   備份   企業   

1、設定主庫master的servie-id值並且開啟bin-log功能參數
vi /etc/my.cnf
修改my.cnf的參數：
[mysqld]
server-id=1 //每一個庫的server-id必須都不一樣
log-bin=mysql-bin

2、檢查參數是否成功設定並重新啟動
egrep "server-id|log-bin" /etc/my.cnf
重啟命令見：mysql安裝或mysql資料庫多執行個體

3、登入資料庫檢查參數的更改情況
show variables like ‘server_id‘;
show variables like ‘log_bin‘;

4、在主庫上建立用於從庫複製的帳號
從庫要想從主庫複製資料，必須要有一個主庫的帳號允許從庫訪問
mysql -uroot -p‘zjl123‘ 
mysql> grant replication slave on *.* to ‘rep‘@‘10.0.0.%‘ identified by ‘zjl123‘;
mysql> flush privileges;
這裡*.*表示所有資料庫的所有表，也可以指示某個資料庫的某個表
rep 為同步的帳號，10.0.0.% 為授權的主機網段，使用了%表示允許整個10.0.0.0的網段可以訪問rep使用者
replication slave 就是主從複製需要的許可權

5、檢查主庫建立的rep複製帳號是否成功
mysql>select user,host from mysql.user;
mysql>select user,host from mysql.user where user = ‘rep‘;
mysql>show grants for [email protected]‘10.0.0.%‘;

實現對主庫鎖表唯讀，用於備份主庫的資料到從庫，使同步前資料一致，鎖表時如果超過設定時間不操作會自動解鎖
6、資料庫鎖表並查看自動解鎖時間長度參數：
mysql>flush table with read lock;
show variables like ‘%timeout%‘

7、查看主庫當前binlog檔案名稱和備份點，從程式庫要求複製時就從這個點開始
mysql>show master status;
或者
mysql -u root -p ‘zjl123‘ -e "show master status"

鎖表後一定要單開一個SSH視窗，匯出主庫資料，如果資料很大（50G以上），並且允許停機，可以停庫直接打包資料檔案進行遷移，那樣更快。
8、備份主庫資料：
mkdir /server/backup/ -p
mysqldump -u root -p ‘zjl123‘ --events -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
ls -l /server/backup/mysql_bak.$(date +F%).sql.gz
或者：
mysqldump -u root -p ‘zjl123‘ --events -A -B -x --master-data=1 |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
--master-data=1參數會在備份資料裡增加如下語句
--position to start replication or point -in-time recovery from change master to master_log_file=‘mysql-bin-000005‘,master_log_post=107;
此時從庫在change master 時就無需指定binlog檔案名稱和位置了，因為在備份時已經備份了該語句

9、為了確保匯出資料期間資料庫沒有資料插入，導庫完畢時可以再次檢查主庫的狀態資訊，結果如下：
mysql -u root -p ‘zjl123‘  -e "show master status"
若無特殊情況，binlog檔案及位置點和鎖表後匯出資料前是一致的

10、開鎖資料庫表
mysql>unlock tables;

以下是從庫上執行的操作
11、設定server-id值並關閉binlog功能
資料庫的server-id一般在一套主從複製體系內是唯一的，這裡從庫的server-id要和主庫及其他的從庫的不同，並且要注釋掉從誤國的binlog參數配置，如果從庫不需要做級聯複製，並且不做備份用，就不需要開啟binlog功能，開啟了反而會增加從庫磁碟的i/o壓力。
但是，有以下兩種情況需要開啟從庫的binlog功能，記錄資料庫更新的sql語句：
1、作為形如：A-->B-->C這樣的級聯同步，中間的B資料庫，就要開啟binlog功能。
2、在從庫需要備份時要開啟binlog功能，因為Database Backup必須要有，全備和binlog日誌，才是完整的備份。

12、把主庫msqldump的資料匯入到從庫
cd /server/backup/
ls -l
ll
mysql -uroot -p‘zjl123‘ -S /data/3307/mysql.sock < mysql_bak.2015-07-27.sql
提示：如果備份時用了-A參數，則在還原到3307的時候，登入3307執行個體的密碼也會和3306主庫的一致，因為3307執行個體的授權表mysql也被覆蓋了。

13、登入3307資料庫後，配置複製參數
CHANGE MASTER TO
MASTER_HOST=‘10.0.0.7‘, #主庫的IP
MASTER_PORT=3306,#主庫的連接埠
MASTER_USER=‘rep‘,#主庫上建立的用於複製的使用者rep
MASTER_PASSWORD=‘zjl123‘,#rep的密碼
MASTER_LOG_FILE=‘mysql-bin.000008‘,#binlog二進位檔案 的名稱
MASTER_LOG_POS=342;#binlog二進位檔案 的位移量，即複製開始點

以上命令需要登入mysql之後在mysql裡執行

也可以通過不登入資料庫的內部命令列實現，如在命令列中輸入命令如下：
mysql -uroot -p‘zjl123‘ -S /data/3307/mysql.sock<< EOF
CHANGE MASTER TO
MASTER_HOST=‘10.0.0.7‘,
MASTER_PORT=3306,
MASTER_USER=‘rep‘,
MASTER_PASSWORD=‘zjl123‘,
MASTER_LOG_FILE=‘mysql-bin.000008‘,
MASTER_LOG_POS=342;
EOF

上述的操作原理是把使用者、密碼、主庫連接埠、IP等資訊寫入從庫的master.info檔案中。
查看寫入結果如下：
ll /data/3307/data/master.info
cat /data/3307/data/master.info

14、啟動從庫同步開關，並查看同步狀態
mysql -uroot -p‘zjl123‘ -S /data/3307/mysql.sock -e "start slave;"
mysql -uroot -p‘zjl123‘ -S /data/3307/mysql.sock -e "show slave status\G;"

主從複製是否成功主要看以下3項的狀態
mysql -uroot -p‘zjl123‘ -S /data/3307/mysql.sock -e "show slave status\G;"|grep "IO_Running|SQL_Running|_Behind_Master"
分別應該顯示的內容為： yes yes 0

Slave_IO_Running: yes 這個是IO線程狀態，IO線程負責從從庫到主庫讀取binlog日誌，並寫入從庫中繼日誌，狀態為yes表示IO線程工作正常。

Slave_SQL_Running： yes 這個是SQL線程狀態，SQL線程負責讀取中繼日誌（relay-log）中的資料並轉換為SQL語句應用到從資料庫中，狀態為yes表示sql線程工作正常。

Seconds_Behind_Master： 0，這個是複製過程中從庫比主庫延遲的秒數，這個參數很重要，但企業時更準確的判斷主從複寫延遲的方法為：在主庫寫時間戳記，然後從庫讀取時間戳記，和當前資料庫時間進行比較，從而認定是否有延遲。

以上是主從複製的全部內容，測試主從複製，只需要在主庫做修改操作，查看從庫中是否做出了相同的改變即可。

15、查看mysql主從複製I/O線程狀態
mysql>show processlist\G

16、問題一
當主庫語句在從庫執行時產生了衝突時，比如主庫建立了一張表，從庫已經存在該名稱的表，就會建立不成功，這時候，主從同步就無法繼續進行
解決：
show slave status\G
stop slave;#臨時停止同步形狀
set global sql-slave-skip-counter=1;#將同步指標移動一個，如果還不能同步，可以重複該操作
start slave;
set global sql-slave-skip-counter=n;#n>0，忽略執行n個更新
解決二：
可以根據錯誤號碼事先在設定檔中配置，跳過指定的不影響業務資料的錯誤，例如：
grep slave-skip /data/3306/my.cnf
slave-skip-errors = 1032,1062,1007

17從庫需要記錄binlog日誌的方法：
在從庫的my.cnf中加入如下參數，然後重啟服務即可
log-slave-updates#必須要有這個參數
log-bin=/data/3307/mysql-bin
expire_logs_days=7#相當於find /data/3307/ -type f -name " mysql-bin.000*" -mtime +7 | xargs rm -f

 

linux mysql主從複製配置