Mysql主從同步

1、Mysql主從同步Mysql主從同步（Linux環(huán)境）0、 配置介紹1Linux(CentOS)2Mysql5.0 以上3A服務(wù)器： 192.168.1.2 主服務(wù)器master4B服務(wù)器： 192.168.1.3 從服務(wù)器slave1、 Master配置#mysql u root p mysql> GRANT REPLICATION SLAVE ON *.* TO slave192.168.1.3 IDENTIFIED BY slave;mysql>FLUSH PRIVILEGES;mysql>exit;上面是Master開放一個(gè)賬號slave密碼slave給IP:192.

2、168.1.3有檔案處理的權(quán)限# mysqladmin u root p shutdown 在A機(jī)器上修改/etc/f 在mysqld區(qū)段內(nèi)加入?yún)?shù) log-bin server-id=1 sql-bin-update-same binlog-do-db=vbb重啟A服務(wù)器mysql此時(shí)因?yàn)橛屑尤雔og-bin參數(shù),因此開始有index產(chǎn)生了,在/var/lib/mysql目錄下有.index檔案紀(jì)錄數(shù)據(jù)庫的異動(dòng)log.查看主機(jī)狀態(tài)mysql>show master status G;2、 Slave配置設(shè)定/etc/f 在mysqld區(qū)段加入 master-host=192.168.1

3、.2 master-user=slave master-password=slave master-port=3306 server-id=2 注意確保唯一性master-connect-retry=60 預(yù)設(shè)重試間隔60秒 replicate-do-db=vbb 告訴slave只做vbb數(shù)據(jù)庫的更新 啟動(dòng)同步：mysql>start slave;查看從機(jī)狀態(tài)mysql>show slave status G；3、 常見問題處理3.1、查看從機(jī)狀態(tài)發(fā)現(xiàn) Slave_SQL_Running :nomysql> show slave statusGSlave_IO_Running

4、: YesSlave_SQL_Running: NoLast_Errno: 1062.Seconds_Behind_Master:NULL原因：1.程序可能在slave上進(jìn)行了寫操作 2.也可能是slave機(jī)器重起后，事務(wù)回滾造成的.解決辦法I：1.首先停掉Slave服務(wù)：slave stop2.到主服務(wù)器上查看主機(jī)狀態(tài)：記錄File和Position對應(yīng)的值。mysql> show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000020

5、 | 135617781 | | |+-+-+-+-+1 row in set (0.00 sec)3.到slave服務(wù)器上執(zhí)行手動(dòng)同步：mysql> change master to > master_host='master_ip',> master_user='user', > master_password='pwd', > master_port=3307, > master_log_file='mysql-bin.000020', > master_log_pos=135617

6、781;1 row in set (0.00 sec)mysql> slave start;1 row in set (0.00 sec)再次查看slave狀態(tài)發(fā)現(xiàn)：Slave_IO_Running: YesSlave_SQL_Running: Yes.Seconds_Behind_Master: 0解決辦法II：mysql> slave stop;mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;mysql> slave start; 自己的使用體會:方法一是強(qiáng)制性從某一個(gè)點(diǎn)開始同步,會有部分沒有同步的數(shù)據(jù)丟失,后續(xù)主服務(wù)器上刪除記

7、錄同步也會有一些錯(cuò)誤信息,不會影響使用.方法二不一定會有效果.3.2、查看從機(jī)狀態(tài)發(fā)現(xiàn) Slave_IO_Running :noMaster slave 復(fù)制錯(cuò)誤Description：Slave_IO_Running:NOSlave_SQL_Running:YesSeconds_Behind_Master: NULL本人遇到的Slave_IO_Running:NO的情況有下面兩種：1. 在配置slave同步時(shí)因?yàn)閟lave訪問master沒有權(quán)限導(dǎo)致；2. master上的mysql-bin.xxxxxx文件全被我誤刪除了；對于第一種情況，仔細(xì)檢查數(shù)據(jù)庫訪問權(quán)限即可解決；對于第二種情況，下面

8、稍微詳細(xì)介紹一下：mysql> show slave statusG* 1. row * Slave_IO_State: Master_Host: 192.168.3.21 Master_User: slave Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000016 Read_Master_Log_Pos: 173 Relay_Log_File: mysqld-relay-bin.000008 Relay_Log_Pos: 98 Relay_Master_Log_File: mysql-bin.00001

9、6 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: br> Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 173 Relay_Log_Space: 98 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No

10、Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL1 row in set (0.00 sec)rootslave mysql# tail /var/log/mysqld.log081223 15:51:50 InnoDB: Started; log sequence number 0 43655081223 15:51:51 Warning Neither -relay-log nor -relay-log

11、-index were used; so replication may break whenthis MySQL server acts as a slave and has his hostname changed! Please use '-relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.081223 15:51:51 Note /usr/libexec/mysqld: ready for connections.Version: '5.0.45-log' socket:

12、'/var/lib/mysql/mysql.sock' port: 3307 Source distribution081223 15:51:51 Note Slave SQL thread initialized, starting replication in log 'mysql-bin.000016' atposition 173, relay log '/var/run/mysqld/mysqld-relay-bin.000007' position: 98081223 15:51:51 Note Slave I/O thread: c

13、onnected to master 'slave192.168.3.21:3307', replication startedin log 'mysql-bin.000016' at position 173081223 15:51:51 ERROR Error reading packet from server: Could not find first log file name in binary logindex file ( server_errno=1236)081223 15:51:51 ERROR Got fatal error 1236:

14、'Could not find first log file name in binary log index file'from master when reading data from binary log081223 15:51:51 Note Slave I/O thread exiting, read up to log 'mysql-bin.000016', position 173081223 15:51:58 Note Error reading relay log event: slave SQL thread was killed解決步驟：

15、重啟master庫：service mysqld restartmysql> show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000001 | 98 | | | +-+-+-+-+mysql> slave stop;mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;mysql> slave star

16、t;mysql> show slave statusG* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.3.21 Master_User: slave Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 98 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 235 Relay_Master

17、_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 E