手把手教你實(shí)現(xiàn)MySQL雙機(jī)數(shù)據(jù)同步

1、手把手教你實(shí)現(xiàn)MySQL雙機(jī)數(shù)據(jù)同步假設(shè)目前有兩臺 MySQL 數(shù)據(jù)庫服務(wù)器，如何實(shí)現(xiàn)這兩臺機(jī)器的數(shù)據(jù)同步問題？很多朋友一開始接觸MySQL雙機(jī)同步需求的時(shí)候可能會(huì)感到不知道從哪里入手，事實(shí)上這是MySQL本身就支持的功能之一。本文提供有關(guān)MySQL主從同步的初步思路，供大家參考。AD： 編者按：很多朋友一開始接觸MySQL雙機(jī)同步需求的時(shí)候可能會(huì)感到不知道從哪里入手，事實(shí)上這是MySQL本身就支持的功能之一。本文提供有關(guān)MySQL主從同步的初步思路，供大家參考。一.需求問題假設(shè)目前有兩臺 MySQL 數(shù)據(jù)庫服務(wù)器，如何實(shí)現(xiàn)這兩臺機(jī)器的數(shù)據(jù)同步問題？即在一臺機(jī)器上修改數(shù)據(jù)庫后，另一臺機(jī)器會(huì)同步

2、更新所修改的信息。二.解決方案查資料發(fā)現(xiàn) MySQL 支持單向，異步復(fù)制，復(fù)制過程中一個(gè)服務(wù)器充當(dāng)主服務(wù)器，而另一個(gè)或多個(gè)其他服務(wù)器充當(dāng)從服務(wù)器。原理是這樣的：主服務(wù)器將更新寫入二進(jìn)制日志文件，并維護(hù)文件的一個(gè)索引來跟蹤日志循環(huán)。這些日志可以記錄發(fā)送到從服務(wù)器的更新。當(dāng)一個(gè)從服務(wù)器連接主服務(wù)器時(shí)， 它通知主服務(wù)器從服務(wù)器在日志中讀取的最后一次成功更新的位置。從服務(wù)器接受從那時(shí)起發(fā)生的任何更新，然后封鎖并等待主服務(wù)器通知新的更新。2.1 測試環(huán)境1. Master : 7 (CentOS 5.5  x86_64&#

3、160;)   MySQL Version  :  5.0.77  2. Slave: 03 (CentOS 5.3 i386)  MySQL  Version : 5.0.45 備注：Master 和 slave 端的 MySQL 版本最好要一樣的，或者 Master 端的版本高于 Slave 端2.2 配置過程2.2.1 Master 端設(shè)置開啟 MySQL 服務(wù)并新建

4、一個(gè)測試數(shù)據(jù)庫 abc:1. rootcamlit : /etc/init.d/mysqld start   2. jian.macamlit  : mysql -u root -p   3. Enter password: xxxx  4. Welcome to the MySQL monitor.  Commands end with 

5、0;or g.   5. Your MySQL connection id is 3   6. Server version: 5.0.77 Source distribution   7.  8. Type 'help;' or 'h' for help. Type 'c' to clea

6、r the buffer.   9.  10. mysql> create database abc;   11. Query OK, 1 row affected (0.31 sec)  12.  13. #創(chuàng)建一個(gè)用來同步的用戶，指定只能在 03 登錄  14. #REPLICATION SLAVE: Enable repl

7、ication slaves to read binary log events from the master  15.  16. mysql> grant replication slave on *.* to 'test1''03' identified by 'test1'   17

8、. Query OK, 0 rows affected (0.16 sec)  修改配置文件：1. rootcamlit : vi /etc/f 備注：在修改配置文件之前做好該文件的備份工作。1. mysqld   2. datadir=/var/lib/mysql   3. socket=/var/lib/mysql/mysql.sock   4. user=mysql   5. old_pas

9、swords=1   6.  7. #增加下面內(nèi)容  8. server_id=1#1 表示 master, 2 表示 slave   binlog-do-db=abc #需要同步的數(shù)據(jù)庫，如果有多個(gè)數(shù)據(jù)庫，每個(gè)數(shù)據(jù)庫一行  binlog-ignore-db=mysql#不需要同步的數(shù)據(jù)庫 log-bin=mysql-bin   9.    10. mysqld_safe   

10、11. log-error=/var/log/mysqld.log   12. pid-file=/var/run/mysqld/mysqld.pid  重啟服務(wù)：1. rootcamlit : /etc/init.d/mysqld restart  2.2.2  Slave 端設(shè)置和 master 端一樣創(chuàng)建一個(gè)相同的數(shù)據(jù)庫: abc1. Enter password:   2. Welcome to the MySQL m

11、onitor.  Commands end with  or g.   3. Your MySQL connection id is 5   4. Server version: 5.0.45-log Source distribution   5.    6. Type 'help;' or 'h

12、9; for help. Type 'c' to clear the buffer.   7.    8. mysql> create database abc;   9. Query OK, 1 row affected (0.31 sec) 修改配置文件：1. roottest2 : vi /etc/f

13、 1. mysqld   2. datadir=/var/lib/mysql   3. socket=/var/lib/mysql/mysql.sock   4. user=mysql   5. old_passwords=1   6.    7. #增加下面內(nèi)容  8. server_id=2   log-bin=mysql-bin   master-host=7 

14、;  master-user=test1   master-password=test1   master-port=3306   master-connect-retry=10  #連接次數(shù)  replicate-do-db=abc   #接受的數(shù)據(jù)庫名  replicate-ignore-db=mysql  #不要接受的數(shù)據(jù)庫  9.    1

15、0. mysqld_safe   11. log-error=/var/log/mysqld.log   12. pid-file=/var/run/mysqld/mysqld.pid  重啟服務(wù)：1. roottest2: /etc/init.d/mysqld restart  備注：配置成功 后會(huì)在 mysql 目錄(/var/lib/mysql/)下生成 文件，如果要更改 slave 設(shè)置，要先將該文件刪除才會(huì)起作用。進(jìn)入 mysql，輸入下面命令：1. roott

16、est2: mysql -u root -p  2. Enter password:   3. Welcome to the MySQL monitor.  Commands end with  or g.   4. Your MySQL connection id is 4   5. Server vers

17、ion: 5.0.45-log Source distribution   6.    7. Type 'help;' or 'h' for help. Type 'c' to clear the buffer.   8.    9. mysql> slave start;   10.

18、 Query OK, 0 rows affected, 1 warning (0.00 sec)   11. #查看同步情況  12. mysql > show slave status;  或 show master status;  2.3 結(jié)果測試在 Master 端進(jìn)行數(shù)據(jù)庫 abc 的一些操作，如下所示：1. jian.macamlit : 

19、mysql  -u root -p   2. Enter password:   3. Welcome to the MySQL monitor.  Commands end with  or g.   4. Your MySQL connection id is 3   5. Server ver

20、sion: 5.0.77-log Source distribution   6.    7. Type 'help;' or 'h' for help. Type 'c' to clear the buffer.   8.    9. mysql> use abc;   10. Da

21、tabase changed   11. mysql> create table test1 (IP VARCHAR(20),USER VARCHAR(100), MAIL   12. VARCHAR(100);   13. Query OK, 0 rows affected (1.20 sec)   14. mysql> insert into

22、 test1(IP,USER,MAIL) values('6', 'test', 'test');   15. Query OK, 1 row affected (0.06 sec)  在 Slave 端查看是否能夠更新：1. roottest2 : mysql -u root -p   2. Enter p

23、assword:   3. Welcome to the MySQL monitor.  Commands end with  or g.   4. Your MySQL connection id is 6   5. Server version: 5.0.45-log Source distribution   6

24、.    7. Type 'help;' or 'h' for help. Type 'c' to clear the buffer.   8.    9. mysql> show databases;   10. +-+   11. | Database   | 

25、  12. +-+   13. | information_schema |   14. | foo|   15. | mysql  |   16. | test   |   17. |abc  |   18. +-+   19. 5rows in set (0.00 sec) 

26、  20. mysql> use abc;   21. Reading table information for completion of table and column names   22. You can turn off this feature to get a quicker startup with&#

27、160;-A   23. Database changed   24. mysql> show tables;   25. +-+   26. | Tables_in_abc |   27. +-+   28. | test1 |   29. +-+   30. 1 row in set (0.03 sec)&#

28、160;  31.  mysql> select * from test1;   32. +-+-+-+   33. | IP   | USER | MAIL |   34. +-+-+-+   35. | 6 | test | test |   36. +-+-+-+&

29、#160;  37. 1 row in set (0.00 sec)  從上面的結(jié)果可以看到 Master 端的數(shù)據(jù)可以同步到 Slave 端里面。說明此時(shí)主從數(shù)據(jù)庫的同步問題已經(jīng)成功解決。三.補(bǔ)充資料關(guān)于如何連接到遠(yuǎn)程 MySQL 問題，可以采取下面的步驟：首先先登錄到遠(yuǎn)程機(jī)器：1. jian.macamlit : ssh root03   2. password: xxx  3. roottest2 :

30、60; 編輯配置文件：1. roottest2 : vi /etc/f  增加下面一行內(nèi)容：1. mysqld   2. datadir=/var/lib/mysql   3. socket=/var/lib/mysql/mysql.sock   4. user=mysql   5. old_passwords=1   6. bind-address=03#此 IP 地址為 MySQ

31、L 本機(jī)的 IP 地址  7. mysqld_safe   8. log-error=/var/log/mysqld.log   9. pid-file=/var/run/mysqld/mysqld.pi 重啟服務(wù)：1. roottest2 : /etc/init.d/mysqld restart  創(chuàng)建測試數(shù)據(jù)庫：1. roottest2 : mysql -u root -p  2. Enter

32、 password:   3. Welcome to the MySQL monitor.  Commands end with  or g.   4. Your MySQL connection id is 2   5. Server version: 5.0.45 Source distribution  

33、0;6.    7. Type 'help;' or 'h' for help. Type 'c' to clear the buffer.   8.    9. mysql> create database foo   10. Query OK, 1 row affected

34、0;(0.00 sec)   11. #增加用戶 test123 從任何主機(jī)登錄到 MySQL  12. mysql> grant all privileges on *.* to 'test123''%' identified by 'test123' with grant  option;   13. Quer

35、y OK, 0 rows affected (0.00 sec)   14. #增加用戶 test1 從 7 主機(jī)登錄到 MySQL  15. mysql> grant all privileges on foo.* to 'test1''7' identified by &#

36、39;test1' with grant   option;   16. Query OK, 0 rows affected (0.00 sec)  如果有防火墻的設(shè)置的話，可以如下設(shè)置：1. roottest2 : iptables -A INPUT -i eth0 -s 7 -p tcp -dport&#

37、160;3306 -j ACCEPT   2. roottest2: /etc/init.d/iptales save  最后在客戶端就可以輸入下面命令來遠(yuǎn)程進(jìn)入 MySQL 數(shù)據(jù)庫：1. jian.macamlit : mysql -u test1 -h 03 -p   2. Enter password:   3. Welcome to the

38、60;MySQL monitor.  Commands end with  or g.   4. Your MySQL connection id is 13   5. Server version: 5.0.45 Source distribution   6. Type 'help;' or 'h'&#

39、160;for help. Type 'c' to clear the buffer. mysqld.pl內(nèi)容如下：1. #!/usr/bin/perl 2.  3. #This script is used to check if the mysql replication is ok 4.  5. use strict; 6.

40、use DBI; 7. use POSIX "strftime" 8.  9. my $host           = "03" 10. my $user           = "test1&

41、quot; 11. my $passwd         = "test1" 12. my $port           = "3306" 13. my $max_behind     = "120"

42、; 14. my $check_log      = "./mysql_check.log" 15.  16.  17. #Open the log file  18. open (FH, ">> $check_log") or die $!; 19.  20. #Connect the

43、 mysql server  21. my $dbh = &MysqlConnect ($host, $port, $user, $passwd); 22.  23.  24. #Get slave sql status 25. my $slave_status = &MysqlQuery( $dbh, 'show slave

44、0;status'); 26. print FH "Error: SQL Query Error:" . $dbh->errstr; 27.  28.  29. my $slave_IO              = $slave_status->Slave_IO_Running;

45、0;30. my $slave_SQL             = $slave_status->Slave_SQL_Running; 31. my $seconds_behind_master = $slave_status->Seconds_Behind_Master; 32. my $now_time      

46、;        = POSIX:strftime ("%Y-%m-%d %H:%M:%S", localtime); 33.  34.  35. print "Check the Slave MySQL stauts.n" 36. print "_" x 50, "n" 37.