mysql主從數(shù)據(jù)庫備份配置過程
注意：
1.mysql版本一樣
2.master和slave的hostname不能相同
3.兩臺服務(wù)器的時(shí)間同步
4.mysql內(nèi)網(wǎng)備份，建議關(guān)閉iptables
5.一定要打開mysql的log-bin
6.主從切換比較危險(xiǎn)荷愕，不到萬不得已不推薦執(zhí)行
7.在啟動(dòng)輔庫的時(shí)候必須先把數(shù)據(jù)同步蔫缸，并刪除日志目錄下的：master.info文件
環(huán)境：centos
數(shù)據(jù)庫目錄及其它
my.cnf配置文件? /usr/local/mysql/my.cnf
MySQL數(shù)據(jù)庫位置? /usr/local/mysql/data/
主數(shù)據(jù)庫：192.168.4.191
從數(shù)據(jù)庫：192.168.4.192
1.安裝略過
一已骇、設(shè)置主庫
1.修改my.cnf主要是設(shè)置個(gè)不一樣的id和log-bin
vi my.cnf
expire_logs_day=10 #定時(shí)清理binlog
log-bin=binlog? #啟動(dòng)二進(jìn)制日志系統(tǒng)
#binlog-do-db=game1? #二進(jìn)制需要同步的數(shù)據(jù)庫名
binlog-ignore-db=mysql? # 避免同步mysql用戶配置钞它，以免不必要的麻煩
log-slave-updates
slave-skip-errors #是跳過錯(cuò)誤，繼續(xù)執(zhí)行復(fù)制操作?
server-id = 1? #本機(jī)數(shù)據(jù)庫ID 標(biāo)示為主
2柿扣、啟動(dòng)主庫生效章贞，并賦予從庫權(quán)限帳號祥绞，允許用戶在主庫上讀取日志
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave.123';
flush privileges;
select host,user,password from mysql.user;
3.鎖主庫表
flush tables with read lock;
4.記錄主庫信息
記下file和position，這個(gè)界面不要關(guān)閉
show master status;
5.另開一個(gè)終端鸭限，準(zhǔn)備備份
mysqldump -uroot -p --master-data=1 --databases xxx xxx xxx > all.sql
6.解鎖
unlock tables
7.將備份的數(shù)據(jù)庫scp到從服務(wù)器上
scp *.sql root@192.168.7.192:~/root/mysqlback
二就谜。設(shè)置從庫
1.修改從庫my.cnf
vi my.cnf
[mysqld]?
port? ? ? ? ? ? = 3306
socket? ? ? ? ? = /tmp/mysql3306.sock
user=mysql
datadir=/home/salve_data/
pid-file=/home/salve_data/mysql.pid
log-bin=mysql-bin-1
binlog_format=mixed
log-error=/home/salve_data/error-67.log
expire_logs_day=10
slave-skip-errors
server-id? ? ? = 2(不能與主服務(wù)器id相同,建議為末位IP)
master-host? ? = 192.168.4.191 (主服務(wù)器IP)
master-user? ? = slave
master-password = slave.123
master-port? ? = 3306
master-connect-retry=60
master-info-file=master-1.info
#replicate-do-db=game1? #多個(gè)DB用逗號隔開
replicate-ignore-db=mysql,information_schema
log-slave-updates
2.重啟數(shù)據(jù)庫
/etc/init.d/mysqld restart
3.驗(yàn)證連接主庫
mysql -h 192.168.4.191 -uroot? -p
show grants for slave@192.168.4.191;
4.給scp過來的sql文件權(quán)限
chown -R mysql.mysql /root/mysqlback
5.同步
stop slave;
change master to master_host='192.168.4.191',master_user='slave',master_password='slave.123',master_port=3306,master_log_file='binlog.000004',master_log_pos=98;
注：master_log_file='binlog.000003',master_log_pos=98根據(jù)剛才主服務(wù)器記錄的數(shù)據(jù)修改一下
start slave;
show slave status\G;
如果Slave_IO_Running、Slave_SQL_Running狀態(tài)為Yes則表明設(shè)置成功里覆。
6.測試
create database hi_db;
use hi_db;
create table hi_tb(id int(3),name char(10));
insert into hi_tb values(001,'bobu');
show databases;
在從表中馬上看到了效果丧荐，主從同步成功了；
為了更進(jìn)一步驗(yàn)證在從庫上輸入
show slave status\G;
可以查看Slave_IO_Running喧枷、Slave_SQL_Running狀態(tài)是否為yes
show full processlist虹统；觀察其狀態(tài)
三弓坞。常見問題處理方法
1：在從庫上面show slave status\G;出現(xiàn)下列情況，
? ? ? ? ? Slave_IO_Running: Yes
? ? ? ? ? Slave_SQL_Running: No
? ? ? ? ? Seconds_Behind_Master: NULL
原因：
a.程序可能在slave上進(jìn)行了寫操作
b.也可能是slave機(jī)器重起后车荔，事務(wù)回滾造成的.
備注：在啟動(dòng)輔庫的時(shí)候必須先把數(shù)據(jù)同步渡冻，并刪除日志目錄下的：master.info文件
解決方法：
進(jìn)入master
mysql> show master status;
然后到slave服務(wù)器上執(zhí)行手動(dòng)同步
slave stop;
change master to
master_host='192.168.1.191',
master_user='slave',
master_password='slave.123',
master_port=3306,
master_log_file='mysql-bin.000040',
master_log_pos=324;
slave start;
show slave status\G;
2、現(xiàn)象：從數(shù)據(jù)庫無法同步忧便，show slave status顯示Slave_IO_Running為No,Seconds_Behind_Master
為null.出現(xiàn)此問題族吻，必須要要重啟master數(shù)據(jù)庫
? ? ? 解決：重啟主數(shù)據(jù)庫
? ? ? ? ? ? service mysql restart
? ? ? ? ? ? mysql> show master status;
在從庫上
slave stop;
change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98
slave start;
或是這樣：
stop slave;
set global sql_slave_skip_counter =1;
start slave;
3.如果主MySQL發(fā)生錯(cuò)誤，如何做主從切換呢珠增？
1超歌、保證所有從數(shù)據(jù)庫都已經(jīng)執(zhí)行了relay log中的全部更新，在從服務(wù)器中執(zhí)行stop slave io_thread,用show processlist檢查蒂教，查看狀態(tài)是否是Has read all relay log,表示更新完成
stop slave io_thread;
#確保Has read allrelay log
show processlist;
2巍举、在從服務(wù)器上執(zhí)行stop slave，reset master命令凝垛，重置成主數(shù)據(jù)庫
stop slave;
reset master;
3懊悯、刪除新的主服務(wù)器數(shù)據(jù)庫目錄中的master.info和relay-log.info文件，否則下次重啟時(shí)還會(huì)按照從服務(wù)器來啟動(dòng)
4.：“Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'”等或由于清數(shù)據(jù)導(dǎo)致主從庫不同步了梦皮，解決辦法如下：
先進(jìn)入slave中執(zhí)行:"slave stop;"來停止從庫同步炭分；
再去master中執(zhí)行:"flush logs;"來清空日志；
然后在master中執(zhí)行:"show master status;"查看下主庫的狀態(tài)剑肯，主要是日志的文件和position捧毛；
然后回到slave中，執(zhí)行:"CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000004',MASTER_LOG_POS=106;"退子，文件和位置對應(yīng)master中的;
最后在slave中執(zhí)行:"slave start;"來啟動(dòng)同步。
5型将、在master上刪除一條記錄寂祥，在slave上找不到。
Last_SQL_Error.Could not execute Delete_rows event on table ….Error_code:1032……
解決： master要?jiǎng)h除一條記錄七兜，而slave上找不到相應(yīng)記錄而報(bào)錯(cuò)丸凭。Master上已經(jīng)刪除，slave 并沒有改記錄腕铸，可以直接跳過：
Stop slave ; set global sql_slave_skip_counter=1;start slave;
6惜犀、主鍵沖突，在slave已經(jīng)有該記錄狠裹，? ? 又在master上插入了同一條記錄
Last_SQL_Error.Could not execute Write_rows event on table…… Error_Code :1062……
解決：對于重復(fù)的記錄虽界，需要?jiǎng)h除重復(fù)的鍵值。涛菠！
7莉御、在master上更新一條記錄撇吞，在slave上找不到數(shù)據(jù)
Last_SQL_Error.Could not execute Update_rows event …… Error_Code: 1032…
解決：master更新的記錄，在slave上找不到礁叔。通過 Mysqlbinlog 分析出真實(shí)的語句牍颈，將slave缺少的記錄進(jìn)行手動(dòng)填充。琅关！
8煮岁、slave中繼日志損壞
Last_SQL_Error:Error initializing relay log postion: I/O error reading the header from the binary log
Last_SQL_Error:Error initializing relay log positon:Binlog has bad magic number:it’s not a binary log file that can be used by this version of MySQL.
Slave 宕機(jī)或非法關(guān)機(jī)，電源故障涣易、硬件故障画机，造成中繼日志損壞
找到salve 同步執(zhí)行到master的位置。重新做同步都毒。
? ? ? ? 查找變量：
? ? ? ? Relay_Master_Log_File:
? ? ? ? Exec_Master_Log_Pos:
? ? ? ? 從這兩個(gè)位置重新同步色罚。
運(yùn)維QQ交流群：171586999
