手把手教你實現MySQL雙機數據同步
作者：sync
假設目前有兩臺 MySQL 數據庫服務器，如何實現這兩臺機器的數據同步問題？很多朋友一開始接觸MySQL雙機同步需求的時候可能會感到不知道從哪里入手，事實上這是MySQL本身就支持的功能之一。本文提供有關MySQL主從同步的初步思路，供大家參考。
編者按：很多朋友一開始接觸MySQL雙機同步需求的時候可能會感到不知道從哪里入手，事實上這是MySQL本身就支持的功能之一。本文提供有關MySQL主從同步的初步思路，供大家參考。
一.需求問題
假設目前有兩臺 MySQL 數據庫服務器，如何實現這兩臺機器的數據同步問題？即在一臺機器上修改數據庫后，另一臺機器會同步更新所修改的信息。
二.解決方案
查資料發現 MySQL 支持單向，異步復制，復制過程中一個服務器充當主服務器，而另一個或多個其他服務器充當從服務器。
原理是這樣的：
主服務器將更新寫入二進制日志文件，并維護文件的一個索引來跟蹤日志循環。這些日志可以記錄發送到從服務器的更新。當一個從服務器連接主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置。從服務器接受從那時起發生的任何更新，然后封鎖并等待主服務器通知新的更新。
2.1 測試環境
- Master : 192.168.7.67 (CentOS 5.5 x86_64 ) MySQL Version : 5.0.77
- Slave: 192.168.56.103 (CentOS 5.3 i386) MySQL Version : 5.0.45
備注：
Master 和 slave 端的 MySQL 版本最好要一樣的，或者 Master 端的版本高于 Slave 端
2.2 配置過程
2.2.1 Master 端設置
開啟 MySQL 服務并新建一個測試數據庫 abc:
- root@camlit ~: /etc/init.d/mysqld start
- jian.ma@camlit ~: mysql -u root -p
- Enter password: xxxx
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 3
- Server version: 5.0.77 Source distribution
- Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
- mysql> create database abc;
- Query OK, 1 row affected (0.31 sec)
- ###創建一個用來同步的用戶，指定只能在 192.168.56.103 登錄
- ###REPLICATION SLAVE: Enable replication slaves to read binary log events from the master
- mysql> grant replication slave on *.* to 'test1'@'192.168.56.103' identified by 'test1';
- Query OK, 0 rows affected (0.16 sec)
修改配置文件：
- root@camlit ~: vi /etc/my.cnf
備注：在修改配置文件之前做好該文件的備份工作。
- [mysqld]
- datadir=/var/lib/mysql
- socket=/var/lib/mysql/mysql.sock
- user=mysql
- old_passwords=1
- ##增加下面內容
- server_id=1###1 表示 master, 2 表示 slave binlog-do-db=abc ###需要同步的數據庫，如果有多個數據庫，每個數據庫一行 binlog-ignore-db=mysql###不需要同步的數據庫 log-bin=mysql-bin
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
重啟服務：
- root@camlit ~: /etc/init.d/mysqld restart
2.2.2 Slave 端設置
和 master 端一樣創建一個相同的數據庫: abc
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 5
- Server version: 5.0.45-log Source distribution
- Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
- mysql> create database abc;
- Query OK, 1 row affected (0.31 sec)
修改配置文件：
- root@test2 ~: vi /etc/my.cnf
- [mysqld]
- datadir=/var/lib/mysql
- socket=/var/lib/mysql/mysql.sock
- user=mysql
- old_passwords=1
- ###增加下面內容
- server_id=2 log-bin=mysql-bin master-host=192.168.7.67 master-user=test1 master-password=test1 master-port=3306 master-connect-retry=10 ###連接次數 replicate-do-db=abc ###接受的數據庫名 replicate-ignore-db=mysql ###不要接受的數據庫
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
重啟服務：
- root@test2~: /etc/init.d/mysqld restart
備注：
配置成功 后會在 mysql 目錄(/var/lib/mysql/)下生成 master.info 文件，如果要更改 slave 設置，要先將該文件刪除才會起作用。
進入 mysql，輸入下面命令：
- root@test2~: mysql -u root -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 4
- Server version: 5.0.45-log Source distribution
- Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
- mysql> slave start;
- Query OK, 0 rows affected, 1 warning (0.00 sec)
- ###查看同步情況
- mysql > show slave status; 或 show master status;
2.3 結果測試
在 Master 端進行數據庫 abc 的一些操作，如下所示：
- jian.ma@camlit ~: mysql -u root -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 3
- Server version: 5.0.77-log Source distribution
- Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
- mysql> use abc;
- Database changed
- mysql> create table test1 (IP VARCHAR(20),USER VARCHAR(100), MAIL
- VARCHAR(100));
- Query OK, 0 rows affected (1.20 sec)
- mysql> insert into test1(IP,USER,MAIL) values('192.168.7.66', 'test', 'test@test.com.cn');
- Query OK, 1 row affected (0.06 sec)
在 Slave 端查看是否能夠更新：
- root@test2 ~: mysql -u root -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 6
- Server version: 5.0.45-log Source distribution
- Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | foo|
- | mysql |
- | test |
- |abc |
- +--------------------+
- 5rows in set (0.00 sec)
- mysql> use abc;
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> show tables;
- +---------------+
- | Tables_in_abc |
- +---------------+
- | test1 |
- +---------------+
- 1 row in set (0.03 sec)
- mysql> select * from test1;
- +--------------+------+------------------+
- | IP | USER | MAIL |
- +--------------+------+------------------+
- | 192.168.7.66 | test | test@test.com.cn |
- +--------------+------+------------------+
- 1 row in set (0.00 sec)
從上面的結果可以看到 Master 端的數據可以同步到 Slave 端里面。說明此時主從數據庫的同步問題已經成功解決。
#p#
三.補充資料
關于如何連接到遠程 MySQL 問題，可以采取下面的步驟：
首先先登錄到遠程機器：
- jian.ma@camlit ~: ssh root@192.168.56.103
- password: xxx
- root@test2 ~:
編輯配置文件：
- root@test2 ~: vi /etc/my.cnf
增加下面一行內容：
- [mysqld]
- datadir=/var/lib/mysql
- socket=/var/lib/mysql/mysql.sock
- user=mysql
- old_passwords=1
- bind-address=192.168.56.103###此 IP 地址為 MySQL 本機的 IP 地址
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pi
重啟服務：
- root@test2 ~: /etc/init.d/mysqld restart
創建測試數據庫：
- root@test2 ~: mysql -u root -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 2
- Server version: 5.0.45 Source distribution
- Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
- mysql> create database foo ;
- Query OK, 1 row affected (0.00 sec)
- ###增加用戶 test123 從任何主機登錄到 MySQL
- mysql> grant all privileges on *.* to 'test123'@'%' identified by 'test123' with grant option;
- Query OK, 0 rows affected (0.00 sec)
- ###增加用戶 test1 從 192.168.7.67 主機登錄到 MySQL
- mysql> grant all privileges on foo.* to 'test1'@'192.168.7.67' identified by 'test1' with grant option;
- Query OK, 0 rows affected (0.00 sec)
如果有防火墻的設置的話，可以如下設置：
- root@test2 ~: iptables -A INPUT -i eth0 -s 192.168.7.67 -p tcp --dport 3306 -j ACCEPT
- root@test2~: /etc/init.d/iptales save
最后在客戶端就可以輸入下面命令來遠程進入 MySQL 數據庫：
- jian.ma@camlit ~: mysql -u test1 -h 192.168.56.103 -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 13
- Server version: 5.0.45 Source distribution
- Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysqld.pl內容如下：
- #!/usr/bin/perl
- #This script is used to check if the mysql replication is ok
- use strict;
- use DBI;
- use POSIX "strftime";
- my $host = "192.168.56.103";
- my $user = "test1";
- my $passwd = "test1";
- my $port = "3306";
- my $max_behind = "120";
- my $check_log = "./mysql_check.log";
- #Open the log file
- open (FH, ">> $check_log") or die $!;
- #Connect the mysql server
- my $dbh = &MysqlConnect ($host, $port, $user, $passwd);
- #Get slave sql status
- my $slave_status = &MysqlQuery( $dbh, 'show slave status');
- print FH "Error: SQL Query Error:" . $dbh->errstr;
- my $slave_IO = $slave_status->{Slave_IO_Running};
- my $slave_SQL = $slave_status->{Slave_SQL_Running};
- my $seconds_behind_master = $slave_status->{Seconds_Behind_Master};
- my $now_time = POSIX::strftime ("[%Y-%m-%d %H:%M:%S]", localtime);
- print "Check the Slave MySQL stauts....\n";
- print "_" x 50, "\n";
- print "Time:\t\t\t$now_time\n";
- print "Slave IO Running:\t\t$slave_IO\n";
- print "Slave SQL Running::\t\t$slave_SQL\n";
- print "Behind Master Seconds:\t\t$seconds_behind_master\n";
- if ($seconds_behind_master > $max_behind){
- print "Slave SQL Server is far behind master ";
- }
- #---Functions----#
- sub MysqlConnect {
- my ($host, $port, $user, $passwd) = @_;
- my $dsn = "DBI:mysql:host=$host;port=$port";
- return DBI->connect($dsn, $user, $passwd, {RaiseError => 1});
- }
- sub MysqlQuery {
- my ($dbh , $query) = @_;
- my $sth = $dbh->prepare($query);
- my $res = $sth->execute;
- return undef unless $res;
- my $row = $sth->fetchrow_hashref;
- $sth->finish;
- return $row;
- }
【編輯推薦】
責任編輯：yangsai
來源：
Linux運維趨勢讀者投遞
