mysql 主从单库单表同步 binlog-do-db replicate-do-db
方案一:兩邊做主從。 SELECT SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) FROM information_schema.tables WHERE TABLE_SCHEMA='(數(shù)據(jù)庫(kù)名大小為K除去1048576為M)';查看庫(kù)容量
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='' AND TABLE_NAME='';?
lqc_msg =? 16651450340?? =15,880M??? 導(dǎo)出來(lái)13G????????????????????????????????????? 16708081764
?
SHOW TABLE STATUS; 查看自增IP
導(dǎo)出數(shù)據(jù)庫(kù): mysqldump -uroot -p'' --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases | gzip > /mnt/l.sql.gz
復(fù)制數(shù)據(jù) rsync -a /mnt/lqc_msg.sql.gz root@:/data/
導(dǎo)入數(shù)據(jù)庫(kù): mysqldump -uroot -p data < /data/lqc.sql &
SELECT SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) FROM information_schema.tables WHERE TABLE_SCHEMA='lqc'
grep -i "change master" lqc.sql
開(kāi)始主從同步 stop slave change master to master_host='',master_user='',master_password='',master_log_file='mysql-bin.000002',master_log_pos=107; start slave;
show slave status\G;
主從報(bào)錯(cuò);
vi /etc/my.cnf [mysqld] #slave-skip-errors=1062,1053,1146 #跳過(guò)指定error no類型的錯(cuò)誤 #slave-skip-errors=all #跳過(guò)所有錯(cuò)誤
1.跳過(guò)指定數(shù)量的事務(wù): mysql>slave stop; mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1??????? #跳過(guò)一個(gè)事務(wù) mysql>slave start
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='lqc_msg' AND TABLE_NAME='ecm_easemob_message'; mysql> SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='lqc_msg' AND TABLE_NAME='ecm_easemob_message'; +---------------------+--------------------------+------------+ | TABLE_NAME????????? | DATA_LENGTH+INDEX_LENGTH | TABLE_ROWS | +---------------------+--------------------------+------------+ | ecm_easemob_message |?????????????? 9751330816 |??? 8283701 | +---------------------+--------------------------+------------+ row in set (0.08 sec) ecm_easemob_message 9743584 ecm_easemob_message_offline 10128638 ecm_notice_queue lq_hx_message_log 527
alter table msg_group_notice_relations AUTO_INCREMENT=100001;
alter table msg_hx_group AUTO_INCREMENT=100120; alter table msg_hx_group_member AUTO_INCREMENT=105919; alter table msg_hx_group_notice AUTO_INCREMENT=100072;
insert into t_user(id, username) values(10, "hehehe"); delete from tablename where
方案二:提高自增ID遷移。
先把表結(jié)構(gòu)拷貝過(guò)去: 查看表自增ID:select max(id) from ecm_easemob_message 設(shè)置新的位+10萬(wàn),然后連接遷移過(guò)去:alter table msg_group_notice_relations AUTO_INCREMENT=100001; 插入數(shù)據(jù)測(cè)試:insert into t_user(id, username) values(10, "hehehe"); 然后遷移過(guò)去,再把舊的數(shù)據(jù)導(dǎo)入。
?
在主服務(wù)器上為從服務(wù)器建立一個(gè)用戶:
grant replication slave on *.* to '用戶名'@'主機(jī)' identified by '密碼';
如果使用的是MySQL 4.0.2之前的版本,則用file權(quán)限來(lái)代替replication slave
編輯主服務(wù)器的配置文件:/etc/my.cnf
server-id=1
log-bin
binlog-do-db=需要復(fù)制的數(shù)據(jù)庫(kù)名,如果復(fù)制多個(gè)數(shù)據(jù)庫(kù),重復(fù)設(shè)置這個(gè)選項(xiàng)即可
binlog-ignore-db=不需要復(fù)制的數(shù)據(jù)庫(kù)苦命,如果復(fù)制多個(gè)數(shù)據(jù)庫(kù),重復(fù)設(shè)置這個(gè)選項(xiàng)即可
注意:如果你想做一個(gè)復(fù)雜點(diǎn)的結(jié)構(gòu):比如說(shuō),A->B->C,其中B是A的從服務(wù)器,同時(shí)B又是C的主服務(wù)器,那么B服務(wù)器除了需要打開(kāi)log-bin之外,還需要打開(kāi)log-slave-updates選項(xiàng),你可以再B上使用“show variables like 'log%';”來(lái)確認(rèn)是否已經(jīng)生效。
編輯從服務(wù)器的配置文件:/etc/my.cnf
server-id=2
master-host=主機(jī)
master-user=用戶名
master-password=密碼
master-port=端口
replicate-do-db=需要復(fù)制的數(shù)據(jù)庫(kù)名,如果復(fù)制多個(gè)數(shù)據(jù)庫(kù),重復(fù)設(shè)置這個(gè)選項(xiàng)即可
replicate-ignore-db=不需要復(fù)制的數(shù)據(jù)庫(kù)名,如果復(fù)制多個(gè)數(shù)據(jù)庫(kù),重復(fù)設(shè)置這個(gè)選項(xiàng)即可
配置主從服務(wù)器的my.cnf時(shí),留心各自的server-id一定要彼此獨(dú)立,不能重復(fù),否則,會(huì)出現(xiàn)如下錯(cuò)誤:
Slave: received end packet FROM server, apparent master shutdown
?
另一個(gè)需要注意的是最好在從服務(wù)器的my.cnf里設(shè)置read_only選項(xiàng),防止發(fā)生意外(連接用戶不能有SUPER權(quán)限,否則無(wú)效)。
記得先手動(dòng)同步一下主從服務(wù)器,數(shù)據(jù)量小的話可以用mysqldump,它有一個(gè)master-data參數(shù)很有用,通過(guò)使用此參數(shù),導(dǎo)出的SQL文件里會(huì)自動(dòng)包含CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;,這樣創(chuàng)建從服務(wù)器就更方便了。
如果數(shù)據(jù)量大的話不太適合使用mysqldump(慢),如果是myisam表的話,加上--lock-all-tables參數(shù),如果是innodb表的話,加上--single-transaction參數(shù)。
而應(yīng)該采用拷貝文件的方式,請(qǐng)按如下操作步驟:
先在主服務(wù)器上鎖定所有的表,以免在復(fù)制過(guò)程中數(shù)據(jù)發(fā)生變化:
mysql> flush tables with read lock;
然后在主服務(wù)器上查詢當(dāng)前二進(jìn)制文件的文件名及偏移位置:
mysql > show master status;
然后停止主服務(wù)器上的MySQL服務(wù):
shell> mysqladmin -u root shutdown
注意:如果僅是MyISAM的話,可以不停止MySQL服務(wù),但要在復(fù)制數(shù)據(jù)文件的過(guò)程中保持只讀鎖,如果是InnoDB的話,必須停止MySQL服務(wù)。
再拷貝數(shù)據(jù)文件:
shell> tar -cvf /tmp/mysql-snapshot.tar .
拷貝完別忘了啟動(dòng)主服務(wù)上的MySQL服務(wù)了。
然后把數(shù)據(jù)文件應(yīng)用到從服務(wù)器上,再次啟動(dòng)slave的時(shí)候使用,記得啟動(dòng)時(shí)加上skip-slave-start選項(xiàng),使之不會(huì)立刻去連接master,再在從服務(wù)器上設(shè)置相關(guān)的二進(jìn)制日志信息:
mysql>?CHANGE MASTER TO
->???? MASTER_HOST='master_host_name',
->???? MASTER_USER='replication_user_name',
->???? MASTER_PASSWORD='replication_password',
->???? MASTER_LOG_FILE='recorded_log_file_name',
->???? MASTER_LOG_POS=recorded_log_position;
啟動(dòng)從服務(wù)器上的復(fù)制線程:
mysql> start slave;
驗(yàn)證主從設(shè)置是否已經(jīng)成功,可以輸入如下命令:
mysql> show slave status\G
會(huì)得到類似下面的列表:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果這兩個(gè)選項(xiàng)不全是Yes,那就說(shuō)明你前面某個(gè)步驟配置錯(cuò)了。
應(yīng)該保證從服務(wù)器上任何數(shù)據(jù)的修改都是通過(guò)從主服務(wù)器上復(fù)制操作獲取的,換句話說(shuō),從服務(wù)器應(yīng)該是只讀的,如果不能保證這一點(diǎn),則可能造成主從數(shù)據(jù)不一致。可以在從服務(wù)器的my.cnf里加入read-only參數(shù)來(lái)實(shí)現(xiàn)這一點(diǎn),唯一需要注意的一點(diǎn)事read-only僅對(duì)沒(méi)有super權(quán)限的用戶有效。所以最好核對(duì)一下連接從服務(wù)器的用戶,確保其沒(méi)有super權(quán)限。
從理想角度看,主從數(shù)據(jù)庫(kù)應(yīng)該無(wú)故障的運(yùn)轉(zhuǎn)下去,可以有時(shí)候還是會(huì)出現(xiàn)一些莫名其妙的問(wèn)題,比如說(shuō)即便從未在從服務(wù)器上手動(dòng)更新過(guò)數(shù)據(jù),但還是可能遇到“Error: 1062 Duplicate entry”錯(cuò)誤,具體原因不詳,可能是MySQL本身的問(wèn)題。遇到這類問(wèn)題的時(shí)候,從服務(wù)器會(huì)停止復(fù)制操作,我們只能手動(dòng)解決問(wèn)題,具體的操作步驟如下:
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
同樣的操作可能需要進(jìn)行多次,也可以設(shè)置自動(dòng)處理此類操作,在從服務(wù)器的my.cnf里設(shè)置:
slave-skip-errors=1062
最后再嘮叨一下日志的問(wèn)題:時(shí)間長(zhǎng)了,數(shù)據(jù)庫(kù)服務(wù)器上的二進(jìn)制文件會(huì)越來(lái)越多,清理是必要的,你可以設(shè)置自動(dòng)清理,相關(guān)參數(shù)是expire_logs_days,也可以使用手動(dòng)刪除的方式,但這里說(shuō)的手動(dòng)不是指rm,而是指PURGE BINARY LOGS,刪除任何日志前,最好在所有的從服務(wù)器上通過(guò)show slave status命令確認(rèn)一下相關(guān)日志是否已經(jīng)無(wú)用。
更詳細(xì)的介紹參考官方文檔:How to Set Up Replication,不喜歡英文的話可以看老葉同志的中文翻譯。
補(bǔ)充:[ERROR] Error in Log_event::read_log_event(): 'Event too big'
在使用主從復(fù)制的時(shí)候,出現(xiàn)的問(wèn)題多半是和日志(主服務(wù)器的二進(jìn)制日志,從服務(wù)器的延遲日志)相關(guān)的。比如說(shuō)加入你遇到了上面的錯(cuò)誤,你可以根據(jù)錯(cuò)誤日志的信息在主從數(shù)據(jù)庫(kù)服務(wù)器上分別執(zhí)行:
mysqlbinlog 日志文件 > /dev/null
查看錯(cuò)誤,如果沒(méi)有錯(cuò)誤,則不會(huì)有任何輸出,反之會(huì)輸出錯(cuò)誤信息,如果確定了錯(cuò)誤是出現(xiàn)在主服務(wù)器二進(jìn)制日志上,可以跳過(guò)適當(dāng)?shù)奈恢?#xff0c;再在從服務(wù)器上重新設(shè)定LOG_POS,如果確定了錯(cuò)誤是出現(xiàn)在從服務(wù)器延遲日志上,則可以刪除從服務(wù)器的延遲日志(使用CHANGE TO MASTER的時(shí)候,除非設(shè)定了延遲日志信息,否則會(huì)自動(dòng)刪除延遲日志),并在從服務(wù)器上重新設(shè)定LOG_POS。期間也可以考慮手動(dòng)執(zhí)行不能自動(dòng)執(zhí)行的SQL日志。
補(bǔ)充:配置的時(shí)候如果版本允許最好打開(kāi)sync_binlog選項(xiàng)。
補(bǔ)充:有時(shí)候,從服務(wù)器延遲日志可能已經(jīng)損壞,這時(shí)需要執(zhí)行CHANGE MASTER TO設(shè)置新的日志文件信息,但是在從服務(wù)器上SHOW SLAVE STATUS會(huì)顯示很多日志信息,他們的含義有所不同:
Master_Log_File:Read_Master_Log_Pos 是IO相關(guān)的日志信息
Relay_Master_Log_File:Exec_Master_Log_Pos 是SQL相關(guān)的日志信息
從服務(wù)器需要設(shè)置的是SQL相關(guān)的日志信息:
slave stop;
change master to master_log_file=’(binlog name in relay_master_log_file)’, master_log_pos=(exec_master_log_pos number);
slave start;
1) When you are using the master as a consistent snapshot, use SHOW MASTER STATUS to determine the position.
2) When you are using a slave as a consistent snapshot, use SHOW SLAVE STATUS and Exec_Master_Log_Pos.
?
https://my.oschina.net/u/1036767/blog/207301
?
轉(zhuǎn)載于:https://www.cnblogs.com/cp-miao/p/6054249.html
總結(jié)
以上是生活随笔為你收集整理的mysql 主从单库单表同步 binlog-do-db replicate-do-db的全部?jī)?nèi)容,希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇: 14.8.4 Moving or Cop
- 下一篇: php fastcgi_finish_r