此文檔包含兩部分：
一、MySQL主從庫(kù)的配置???? 二、常見問題以及解決方法
一、MySQL主從庫(kù)的配置
說明：
???????? 1、此文檔記錄的是MySQL主從庫(kù)最常用的配置方法，即：一主一從（或一主多從）。
?????????????????? 切記：此種配置一定是主寫從讀。
???????? 2、MySQL主從庫(kù)的原理，這里就不介紹了，可以自行百度，這個(gè)網(wǎng)址也有詳細(xì)說明：
?????????????????? http://blog.csdn.net/hguisu/article/details/7325124/
???????? 3、本次配置環(huán)境：
?????????????????? （1）VMware虛擬機(jī)；
?????????????????? （2）虛擬兩臺(tái)主機(jī)：
?????????????????? ???????? 主機(jī)（master）：系統(tǒng)：ubuntu-16.04-desktop-amd64.iso； IP：192.168.142.166
?????????????????? ???????? 主機(jī)（slave）：? 系統(tǒng)：ubuntu-16.04-desktop-amd64.iso； IP：192.168.142.167
?????????????????? （3）MySQL版本：mysql-server_5.6.26-1ubuntu14.04_amd64.deb-bundle.tar
???????? 4、若僅想看主從庫(kù)的配置，可直接查看第五步、第六步。
第一步：安裝MySQL（已安裝則忽略）
???????? 分別在兩臺(tái)主機(jī)安裝MySQL：
???????? 1、cd
?????? 2、mkdir mysql-deb
?????? 3、cd mysql-deb
?????? 4、下載mysql-server_5.6.26-1ubuntu14.04_amd64.deb-bundle.tar到當(dāng)前目錄
?????? 5、tar -xf mysql-server_5.6.26-1ubuntu14.04_amd64.deb-bundle.tar
?????? 6、安裝支持包：sudo apt-get install libaio1
?????? 7、sudo dpkg -i mysql-common_5.6.26-1ubuntu14.04_amd64.deb
?????? 8、sudo dpkg -i mysql-community-server_5.6.26-1ubuntu14.04_amd64.deb
?????? 9、sudo dpkg -i mysql-community-client_5.6.26-1ubuntu14.04_amd64.deb
?????? 啟動(dòng)、停止MySQL命令：
?????? /etc/init.d/mysqlstart
?????? /etc/init.d/mysqlstop
?????? 在主庫(kù)（192.168.142.166）配置文件中注釋bind-address = 127.0.0.1 （否則從???????? 庫(kù)不能遠(yuǎn)程登錄主庫(kù)）
?????? cd/etc/mysql
?????? vi my.cnf
?????? # bind-address= 127.0.0.1
第二步：在主庫(kù)建立數(shù)據(jù)庫(kù)（已有庫(kù)則忽略）
???????? 1、mysql??-uroot??-p
???????? 2、create database osyunweidb CHARACTER SET'utf8mb4' COLLATE?????????????????????????? 'utf8mb4_general_ci';
?????? 3、use osyunweidb;
?????? 4、CREATE TABLE `userinfo` (
???????????? ?`id` int(20) NOT NULL AUTO_INCREMENT,
???????????? ?`name` char(20) DEFAULT NULL,
???????????? ?`age` int(11) DEFAULT NULL,
????????????? ?`sex` char(2) DEFAULT NULL,
????????????? ?PRIMARY KEY (`id`)
????????????? );
???????? 5、INSERT INTO `userinfo`(name,age,sex) VALUES ( 'adai',18,'1');
第三步：建立同步用戶——從庫(kù)連接登陸主庫(kù)的用戶（已有用戶則忽略）
???????? 1、mysql??-uroot??-p
???????? 2、創(chuàng)建用戶：
???????? GRANTUSAGE ON *.* TO 'osyunweidbbak '@'192.168.142.167' IDENTIFIED BY '123456'????????? WITH GRANT OPTION;
create user 'osyunweidbbak'@'192.168.142.167' identified by 'qwe123';? —— 8.0；
???????? 用戶：osyunweidbbak
???????? 密碼：123456
???????? 只能從主機(jī)192.168.142.167（從庫(kù)IP）遠(yuǎn)程登錄
???????? 3、查看用戶權(quán)限：
???????? showgrants for osyunweidbbak @'192.168.142.167';
???????? 顯示：
只有 USAGE權(quán)限：只允許登錄--其它什么也不允許做。
?????? 4、更改用戶osyunweidbbak的權(quán)限：
???????? grantreplication slave on *.* to 'osyunweidbbak '@'192.168.142.167' identified by'123456'?? with grant option;
grant replication slave on *.* to 'osyunweidbbak'@'192.168.142.167';——8.0
???????? 顯示：
授權(quán)用戶osyunweidbbak只能從192.168.142.167這個(gè)IP訪問主庫(kù)（192.168.142.166）???? 的數(shù)據(jù)庫(kù)，并且只具有數(shù)據(jù)庫(kù)備份的權(quán)限。
???????? 5、刷新系統(tǒng)授權(quán)表
?????????????????? flushprivileges;?????
flush privileges;——8.0
?????? 6、測(cè)試在從庫(kù)服務(wù)器上登錄到主庫(kù)
???????? mysql-u?osyunweidbbak -h 192.168.142.166?-p
第四步：把主庫(kù)的數(shù)據(jù)庫(kù)導(dǎo)入到從庫(kù)中
???????? 1、在主庫(kù)導(dǎo)出數(shù)據(jù)庫(kù)（192.168.142.166）
???????? 進(jìn)入mysql 客戶端程序和腳本目錄
???????? cd/usr/bin
?????? flushtables with read lock;
?????? mysqldump-u root -p?osyunweidb > /home/osyunweidbbak.sql
?????? unlocktables;
???????? 2、導(dǎo)入數(shù)據(jù)到從庫(kù)（192.168.142.167）
???????? mysql??-u root -p
?????? source??/home/osyunweidbbak.sql
第五步：配置主庫(kù)（192.168.142.166）的my.cnf文件
???????? 1、cd /etc/mysql
???????? 2、vi my.cnf
???????? 配置如圖：
說明：
???????? （1）server-id=1
???????? #設(shè)置服務(wù)器id，1表示主服務(wù)器（主庫(kù)），注意：如果原來的配置文件中已經(jīng)有這一行，????? 就不用再添加了。
（2）log_bin=mysql-bin
???????? #啟動(dòng)MySQ二進(jìn)制日志系統(tǒng)，注意：如果原來的配置文件中已經(jīng)有這一行，就不用再?? 添加了。
（3）binlog-do-db=osyunweidb
???????? #需要同步的數(shù)據(jù)庫(kù)名，如果有多個(gè)數(shù)據(jù)庫(kù)，可重復(fù)此參數(shù)，每個(gè)數(shù)據(jù)庫(kù)一行。
（4）binlog-ignore-db=mysql
???????? #不需要同步的數(shù)據(jù)庫(kù)名，如果有多個(gè)數(shù)據(jù)庫(kù)，可重復(fù)此參數(shù)，每個(gè)數(shù)據(jù)庫(kù)一行。
???????? 3、重啟MySQL
???????? /etc/init.d/mysql restart
???????? 4、查看主庫(kù)信息
???????? mysql -u root -p
???????? show master status;
???????? 顯示：
第六步：配置從庫(kù)（192.168.142.167）的my.cnf文件
???????? 1、cd /etc/mysql
???????? 2、vi my.cnf
???????? 配置如圖：
注意：紅色框部分。
???????? 說明：
???????? （1）server-id=2
???????? #設(shè)置服務(wù)器id，2表示從服務(wù)器（從庫(kù)），注意：如果原來的配置文件中已經(jīng)有這一行，????? 就不用再添加了。
（2）replicate_wild_do_table = osyunweidb.%
???????? #需要同步的數(shù)據(jù)庫(kù)名，如果有多個(gè)數(shù)據(jù)庫(kù)，可重復(fù)此參數(shù)，每個(gè)數(shù)據(jù)庫(kù)一行。
（3）replicate_wild_ignore_table=mysql?.%
???????? #不需要同步的數(shù)據(jù)庫(kù)名，如果有多個(gè)數(shù)據(jù)庫(kù)，可重復(fù)此參數(shù)，每個(gè)數(shù)據(jù)庫(kù)一行。
???????? （4）特別注意，注釋掉的兩行：
???????? replicate-do-db=osyunweidb
???????? replicate-ignore-db=mysql
???????? 從庫(kù)的配置文件不可用這兩個(gè)參數(shù)，在同步數(shù)據(jù)的時(shí)候會(huì)有隱患，參考“二、常見問???? 題以及解決方法”的說明。
???????? （5）從庫(kù)不需要開啟二進(jìn)制日志，除非有必要：如，此從庫(kù)又作為別的從庫(kù)的主庫(kù)，? 或者此從庫(kù)需要增量備份。
???????? （6）MySQL 5.1.7版本之后，已經(jīng)不支持把主庫(kù)（master）配置屬性，如：
?????? master_host='192.168.142.166', master_user='osyunweidbbak',
???????? master_password='123456', master_log_file='mysql-bin.000006'?,
???????? master_log_pos=44884752
???????? 寫入my.cnf配置文件中了,只把要同步的數(shù)據(jù)庫(kù)和要忽略的數(shù)據(jù)庫(kù)寫入my.cnf即可。
???????? 3、重啟MySQL
???????? /etc/init.d/mysqlrestart
???????? 4、mysql -uroot -p
???????? 5、停止slave同步進(jìn)程
???????? stop slave;
???????? 6、執(zhí)行同步語句（設(shè)置主庫(kù)IP、登錄用戶名密碼、同步文件、同步點(diǎn)）
???????? change master to? master_host='192.168.142.166',master_user='osyunweidbbak',master_password='123456',???????? master_log_file=' mysql-bin.000006'?,master_log_pos=44884752;
???????? 注意：這里的master_log_file=' mysql-bin.000006'?,master_log_pos=44884752 就是前面在????????? 主庫(kù)查看并記錄下來的File和Position的值。
???????? 7、start slave;
???????? 8、查看slave同步信息
???????? show slave status\G? （用\G結(jié)尾而不是分號(hào)結(jié)尾，按行顯示結(jié)果）
???????? 顯示：
注意查看：
?????? Slave_IO_Running: Yes
?????? Slave_SQL_Running: Yes
??????
以上這兩個(gè)參數(shù)的值為Yes，即說明配置成功，而正在運(yùn)行。
?????? 注意核對(duì)其他信息是否正確。
出現(xiàn)錯(cuò)誤：ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
解決：加參數(shù)：
mysql -u root -p --get-server-public-key
change master to? master_host='192.168.142.166',master_user='osyunweidbbak',master_password='123456',???????? master_log_file=' mysql-bin.000006'?,master_log_pos=44884752,?get_master_public_key=1;
第七步：測(cè)試同步是否成功以及一般穩(wěn)定性
???????? 手工測(cè)試只能簡(jiǎn)單的插入、刪除或更新幾條數(shù)據(jù)，所以可以寫幾行代碼隨機(jī)、長(zhǎng)時(shí)間、???????? 大批量的對(duì)主庫(kù)插入、刪除或更新數(shù)據(jù)；然后查看主庫(kù)和從庫(kù)的數(shù)據(jù)一致性（簡(jiǎn)單的只???? 能看總記錄數(shù)是否一致了）。
???????? 結(jié)果如下（從前一天晚上6點(diǎn)運(yùn)行到次日8點(diǎn)）：
可以看到，不管是插入數(shù)據(jù)還是刪除數(shù)據(jù)操作，主庫(kù)、從庫(kù) userinfo表的記錄數(shù)都保???? 持一致。數(shù)據(jù)量已經(jīng)有28萬了。
OK，主從庫(kù)配置完畢。
二、常見問題以及解決方法
1、從庫(kù)沒有同步主庫(kù)的數(shù)據(jù)
分兩種情況：
（1）從庫(kù)在執(zhí)行SQL語句時(shí)發(fā)生錯(cuò)誤
默認(rèn)下從庫(kù)在同步數(shù)據(jù)執(zhí)行SQL語句時(shí)，只要發(fā)生錯(cuò)誤，就會(huì)停止同步，不會(huì)跳過；
（當(dāng)然主庫(kù)本就沒有執(zhí)行成功的SQL語句，不會(huì)影響從庫(kù)同步）。
查看方法：
①主服務(wù)器（主庫(kù)）查看進(jìn)程是否Sleep太多：
show processlist;
?②查看主庫(kù)狀態(tài)是否正常：
show master status;
③查看從庫(kù)狀態(tài)信息：
show slave status\G
顯示：
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Error: Error 'PROCEDURE BK.zoucmdoes not exist' on query. Default database: 'BK'. Query: 'drop procedure zoucm'
可見slave沒有同步，SQL報(bào)錯(cuò)。
解決方法：
如果數(shù)據(jù)可控的情況下，可以忽略錯(cuò)誤，繼續(xù)同步：
1、停止slave服務(wù)器的主從同步:
stop slave;
2、跳過一步錯(cuò)誤，數(shù)字表示跳過多少步:
set global sql_slave_skip_counter =1;
3、開啟slave服務(wù):
start slave;
如果數(shù)據(jù)已經(jīng)不可控，可以考慮重做主從庫(kù)：
1.先進(jìn)入主庫(kù)（192.168.142.166），鎖表，防止數(shù)據(jù)寫入
flush tables withread lock;
2、備份主庫(kù)數(shù)據(jù)：
mysqldump -uroot-p? > mysqlmaster.bak.sql
3、重置主庫(kù)maste
RESET MASTER;
4、查看master狀態(tài)，并記錄file和postion
show master status;
5、對(duì)主庫(kù)（192.168.142.166）解鎖表
UNLOCK TABLES;
6、進(jìn)入從庫(kù)（192.168.142.167），停止從庫(kù)的同步狀態(tài)
stop slave;
或者直接停止從庫(kù)mysql服務(wù)
/etc/init.d/mysql stop
7、找到從庫(kù)的中繼日志，把中繼日志相關(guān)的文件都刪除
find / -name mysqld-relay-bin.*
rm -rf ...
7、（在從庫(kù)操作）導(dǎo)入備份數(shù)據(jù)庫(kù)
source mysqlmaster.bak.sql
8、清除同步信息
reset slave all;
9、（在從庫(kù)操作）配置從庫(kù)同步，注意同步點(diǎn)，即第4步查看的file和position信息
change master to master_host='192.168.142.166',master_user='osyunweidbbak',master_password='123456',master_log_file='mysql-bin.000006'?,master_log_pos=44884752;
注意：這里的master_log_file=' mysql-bin.000006'?,master_log_pos=44884752
要按實(shí)際的填寫。
10、重新開啟從庫(kù)同步
START SLAVE;
11、（在從庫(kù)操作）查看同步狀態(tài)
show slave status\G
顯示：
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
（2）從庫(kù)配置文件中使用參數(shù)replicate-do-db 和replicate-ignore-db
在配置從庫(kù)的my.cnf文件時(shí)，如果使用參replicate-do-db 和replicate-ignore-db 會(huì)導(dǎo)致從庫(kù)同步主庫(kù)數(shù)據(jù)時(shí)忽略掉主庫(kù)的跨庫(kù)更新SQL語句。例如，主庫(kù)：
usedb1;
insert intodb2.userinfo(name,age,sex) values ( 'adai',18,'1');
那么從庫(kù)同步時(shí)會(huì)忽略此語句，即不會(huì)同步此數(shù)據(jù)。
所以為了避免此問題，應(yīng)該使用參數(shù)：
replicate_wild_do_table = osyunweidb.%
replicate_wild_ignore_table=mysql?.%
配置需要同步和不需要同步的數(shù)據(jù)庫(kù)。
2、從庫(kù)同步狀態(tài)顯示：Slave_IO_Running 為connecting
這是從庫(kù)無法登陸主庫(kù)。
主要有三個(gè)原因：
（1）網(wǎng)絡(luò)不通
（2）登陸主庫(kù)的賬號(hào)密碼不對(duì)或者權(quán)限問題
（3）position的位置不對(duì)
（4）主庫(kù)的配置文件my.cnf 沒有注釋bind-address = 127.0.0.1
一一檢查即可。
完畢。
