建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
手把手教你實(shí)現(xiàn)MySQL雙機(jī)數(shù)據(jù)同步
編者按：很多朋友一開始接觸MySQL雙機(jī)同步需求的時(shí)候可能會(huì)感到不知道從哪里入手，事實(shí)上這是MySQL本身就支持的功能之一。本文提供有關(guān)MySQL主從同步的初步思路，供大家參考。
一.需求問題
假設(shè)目前有兩臺(tái) MySQL 數(shù)據(jù)庫服務(wù)器，如何實(shí)現(xiàn)這兩臺(tái)機(jī)器的數(shù)據(jù)同步問題？即在一臺(tái)機(jī)器上修改數(shù)據(jù)庫后，另一臺(tái)機(jī)器會(huì)同步更新所修改的信息。
二.解決方案
查資料發(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)境
- 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 端設(shè)置
開啟 MySQL 服務(wù)并新建一個(gè)測試數(shù)據(jù)庫 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)
- ###創(chuàng)建一個(gè)用來同步的用戶，指定只能在 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
- ##增加下面內(nèi)容
- 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
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
重啟服務(wù)：
- root@camlit ~: /etc/init.d/mysqld restart
2.2.2 Slave 端設(shè)置
和 master 端一樣創(chuàng)建一個(gè)相同的數(shù)據(jù)庫: 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
- ###增加下面內(nèi)容
- 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 ###連接次數(shù) replicate-do-db=abc ###接受的數(shù)據(jù)庫名 replicate-ignore-db=mysql ###不要接受的數(shù)據(jù)庫
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
重啟服務(wù)：
- root@test2~: /etc/init.d/mysqld restart
備注：
配置成功 后會(huì)在 mysql 目錄(/var/lib/mysql/)下生成 master.info 文件，如果要更改 slave 設(shè)置，要先將該文件刪除才會(huì)起作用。
進(jìn)入 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 結(jié)果測試
在 Master 端進(jìn)行數(shù)據(jù)庫 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)
從上面的結(jié)果可以看到 Master 端的數(shù)據(jù)可以同步到 Slave 端里面。說明此時(shí)主從數(shù)據(jù)庫的同步問題已經(jīng)成功解決。
#p#
三.補(bǔ)充資料
關(guān)于如何連接到遠(yuǎn)程 MySQL 問題，可以采取下面的步驟：
首先先登錄到遠(yuǎn)程機(jī)器：
- jian.ma@camlit ~: ssh root@192.168.56.103
- password: xxx
- root@test2 ~:
編輯配置文件：
- root@test2 ~: vi /etc/my.cnf
增加下面一行內(nèi)容：
- [mysqld]
- datadir=/var/lib/mysql
- socket=/var/lib/mysql/mysql.sock
- user=mysql
- old_passwords=1
- bind-address=192.168.56.103###此 IP 地址為 MySQL 本機(jī)的 IP 地址
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pi
重啟服務(wù)：
- root@test2 ~: /etc/init.d/mysqld restart
創(chuàng)建測試數(shù)據(jù)庫：
- 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 從任何主機(jī)登錄到 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 主機(jī)登錄到 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)
如果有防火墻的設(shè)置的話，可以如下設(shè)置：
- 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
最后在客戶端就可以輸入下面命令來遠(yuǎn)程進(jìn)入 MySQL 數(shù)據(jù)庫：
- 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內(nèi)容如下：
- #!/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;
- }
【編輯推薦】
- FreeBSD 8上的Rsync同步簡易教程
- 生產(chǎn)環(huán)境下的MySQL數(shù)據(jù)庫主從同步總結(jié)
- MySQL數(shù)據(jù)庫同步的實(shí)例演示大分享
文章名稱：手把手教你實(shí)現(xiàn)MySQL雙機(jī)數(shù)據(jù)同步
轉(zhuǎn)載注明：http://www.dlmjj.cn/article/ccopedd.html
