1.建ç«ä¸é¨ç¨äºReplicationçè´¦æ·
é¦å
Replicationæä½ä¼æ¶åå°ç两个éè¦æéï¼è¿éå
åä¸ä¸è¯´æï¼
The REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS.
REPLICATION CLIENT 使å¾ç¨æ·å¯ä»¥ä½¿ç¨SHOW MASTER STATUSåSHOW SLAVE STATUSå½ä»¤ï¼ä¹å°±æ¯è¯´è¿ä¸ªæéæ¯ç¨äºæäºè´¦æ·çè§Replicationç¶åµçæåã
The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.
REPLICATION SLAVEåæ¯ä¸ä¸ªå¿
é¡»èåºæ¬çæéï¼å®ç´æ¥æäºslaveæå¡å¨ä»¥è¯¥è´¦æ·è¿æ¥masteråå¯ä»¥æ§è¡replicateæä½çæå©ã
ä¸è¬æ¥è¯´ï¼æ们ä¼åç¬å¨ä¸»æå¡å¨å建ä¸ä¸ªä¸é¨ç¨äºReplicationçè´¦æ·ãè¿ä¸ªè´¦æ·å¿
é¡»å
·æREPLICATION SLAVEæéï¼é¤æ¤ä¹å¤æ²¡æå¿
è¦æ·»å ä¸å¿
è¦çæéï¼ä¿è¯è¯¥ç¨æ·çèè´£åä¸ãåå®æ们è¦å»ºç«çè¿ä¸ªè´¦æ·ä¸ºrepl,å¯ç 为repl,é£ä¹è¿ä¸æä½çå½ä»¤å¦ä¸ï¼
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'repl';
å
¶ä¸è¦ç¹å«è¯´æä¸ä¸192.168.0.%ï¼è¿ä¸ªé
ç½®æ¯ææreplç¨æ·æå¨æå¡å¨ï¼è¿é%æ¯éé
符ï¼è¡¨ç¤º192.168.0.0-192.168.0.255çServeré½å¯ä»¥ä»¥replç¨æ·ç»é主æå¡å¨ãå¦æ没æ使ç¨éé
符ï¼è访é®çæå¡å¨åä¸å¨ä¸è¿°é
å¶éï¼é£ä¹ä½ å°æ æ³ä½¿ç¨è¯¥è´¦æ·ä»ä½ çæå¡å¨replicate主æå¡å¨.
å¦å¤å¨ãHight Performance MySqlãä¸ä¹¦ä¸å¯¹ç¨æ·æéç设置ææä¸åï¼ä½è
建议å¨ä¸»æºåä»æºä¸é½é
ç½®replè´¦æ·ï¼å¹¶åæ¶èµäºREPLICATION SLAVEåREPLICATION CLIENTæéï¼å½ä»¤å¦ä¸ï¼
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'repl';
ä½è
解éäºè¿æ ·åç好å¤ï¼ä¸æ¹é¢ä½¿ç¨åä¸è´¦æ·å¯¹Replicationè¿è¡çè§ç®¡çä¼å¾æ¹ä¾¿ï¼ä¸å¿
åºåslave,masterï¼å¦ä¸æ¹é¢ï¼replè´¦æ·å¨slaveåmasterä¸çé
å¶æ¯ä¸æ ·çï¼è¿æ ·å¦ææ们åæ¢slaveåmasterï¼è´¦æ·ä¸éè¦åä»»ä½æ¹å¨ã
2.é
置主ä»æå¡å¨
主ä»æå¡å¨çé
ç½®é½æ¯éè¿æ¹åmy.cnf/my.iniæ件æ¥å®æçã
ä¸é¢æ¯ä¸»ä»æå¡å¨çå¿
é¡»çé
置项ï¼
主æºå¿
é¡»çé
置项ï¼
log-bin //èªå®ä¹ï¼æ¯å¦ log-bin=mysql-bin
server_id //为serverèµ·ä¸ä¸ªå¯ä¸çidï¼é»è®¤æ¯1ï¼æ¨è使ç¨IPçæåä¸èã
ä»æºå¿
é¡»çé
置项ï¼
server_id //为serverèµ·ä¸ä¸ªå¯ä¸çidï¼é»è®¤æ¯1ï¼æ¨è使ç¨IPçæåä¸è.
注æï¼ä¸è¬ï¼æ们ä¹ä¼ä¸ºä»æºè®¾å®log-bin,è¿æ¯å 为é»è®¤çlog-binæ件æ¯æ ¹æ®ä¸»æºåå½åçï¼ä¸æ¦æºå¨æ´æ¹ä¸»æºåå°±ä¼åºé®é¢ï¼åè
ä¿æ主ä»æºçé
å¶ä¸è´ä¹æ¹ä¾¿å主ä»æºåæ¢ï¼
主æºå¯éçé
置项ï¼ï¼ç¨äºé
置主æºåªäºåºä¼åäºè¿å¶æ¥å¿ç¨ä»¥Replicateï¼
binlog-do-db
binlog-ignore-db
ä»æºå¯éçé
置项ï¼ï¼ç¨äºé
ç½®ä»æºä¼Replicateåªäºåºå表ï¼
replicate-do-db, replicate-ignore-db
replicate-do-table, replicate-ignore-table
replicate-wild-do-table
replicate-wild-ignore-table
注æï¼ä¸æ¡å»ºè®®æ¯ä¸è¦å¨my.cnf/my.iniä¸é
å¶master_hostçé项ï¼èåºè¯¥ä½¿ç¨CHANGE MASTER TOå½ä»¤æ¥å¨æ设置ï¼
对äºMaster端ï¼æåªéç®åå°è®¾ç½®server_idålog_bin两项å³å¯ï¼å¯¹äºSlave端å
¶å®åªéè¦è®¾ç½®server_idï¼ä½æ¯è¿æä¸äºæ¨èç设置项ã以ä¸æ¯ãHight Performance MySqlãä¸ä¹¦ä¸ç»åºçSlave端çæ¨è设置
# SLAVE-END replication-related configuration.
# The only required option for slave-end is server_id.
# The other options are recommanded on P 349 ofãHight Performance MySqlã
server_id=234
log_bin=mysql_bin_log
relay_log = mysql_relay_bin_log
log_slave_updates = 1
read_only = 1
3.è¿æ¥ä»æå¡å¨è³ä¸»æå¡å¨è¿è¡Replicate
éè¿å¨ä»æå¡å¨ä¸è¾å
¥CHANGE MASTER TOå½ä»¤å¯ä»¥ä½¿ä»æå¡è¿æ¥å°æ个主æå¡å¨ä¸è¿è¡replication.
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.246',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;
è¾å
¥ä¸è¿°å½ä»¤åå³å®æäºå
¨é¨é
置工ä½ï¼éè¿ï¼
start slave;
å¯å¨ä»æå¡çreplicationå·¥ä½ï¼è¿æ ·ä¸»ä»æå¡å¨å°±å¼å§åæ¥äºãä½ å¯ä»¥éè¿ï¼
SHOW SLAVE STATUS/G;
å½ä»¤æ¥æ¥çä»æå¡å¨çç¶æï¼å¦ææ¯Slave_IO_Stateä¸é¡¹æ¾ç¤ºï¼Waiting for master to send eventï¼è¡¨ç¤ºææå·¥ä½å·²ç»å°±ç»ªã
温馨提示:答案为网友推荐,仅供参考