1： 首先確定Master和Slave的數(shù)據(jù)庫版本，Master數(shù)據(jù)庫的版本不能高于Slave數(shù)據(jù)的版本。
這里我是使用MySql 5.0.27 作為Master數(shù)據(jù)庫，MySql 6.0.3（alpha）作為Slave進(jìn)行測試。
2：首先修改Master數(shù)據(jù)庫的配置文件my.ini (windows), /etc/my.cnf(linux)里
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=173.45.243.171 #master server ipadreess
port=3306
set-variable = connect_timeout=10
set-variable = key_buffer_size=50M
set-variable = thread_cache_size=950
set-variable = table_cache=4200
set-variable = max_connect_errors=999999999
set-variable = max_connections=800
set-variable = tmp_table_size=10M
set-variable = wait_timeout=120
set-variable = max_write_lock_count=120
set-variable = query_cache_type=1
set-variable = query_cache_size=50M
log-slow-queries
memlock
skip-innodb
skip-locking
skip-name-resolve
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=16
#########################
# start master settings #
#########################
#binlog-do-db=database
#binlog-ignore-db=db_name
set-variable = max_binlog_size=10M
log-bin=/var/log/mysql/mysqlbinlog/pts1-log-bin //使用的二進(jìn)制日志文件名
log-bin-index=/var/log/mysql/mysqlbinlog/pts1-log-bin.index
server-id=1 //服務(wù)器編號
#######################
# end master settings #
#######################
[mysql.server]
user=mysql
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/log/mysql/mysqld.pid
在Slave數(shù)據(jù)庫的配置文件my.ini, or my.cnf里添加server-id 項(xiàng)
eg:[mysqld]
server-id = 2
(這里需要理解的是Slave本身也是一個(gè)獨(dú)立的服務(wù)器，它作為‘從數(shù)據(jù)庫’是從它通過‘主服務(wù)器’日志更新數(shù)據(jù)角度上理解的。可以把 server-id 想象成為IP地址：這些ID標(biāo)識了整個(gè)同步組合中的每個(gè)服務(wù)器。如果沒有指定 server-id 的值，如果也沒定義 master-host，那么它的值就為1，否則為2。注意，如果沒有設(shè)定 server-id，那么master就會拒絕所有的slave連接，同時(shí)slave也會拒絕連接到master上。)
整個(gè)修改如下：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=173.45.243.96 #slave server ipadreess
port=3306
set-variable = connect_timeout=10
set-variable = key_buffer_size=50M
set-variable = thread_cache_size=950
set-variable = table_cache=4200
set-variable = max_connect_errors=999999999
set-variable = max_connections=800
set-variable = tmp_table_size=10M
set-variable = wait_timeout=120
set-variable = max_write_lock_count=120
set-variable = query_cache_type=1
set-variable = query_cache_size=50M
log-slow-queries
memlock
skip-innodb
skip-locking
skip-name-resolve
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=16
#########################
# start master settings #
#########################
#set-variable = max_binlog_size=10M
#log-bin=/var/log/mysql/mysqlbinlog/pts2-log-bin
#log-bin-index=/var/log/mysql/mysqlbinlog/pts2.index
#server-id=1
#######################
# end master settings #
#######################
#########################
# start slave settings #
#########################
relay-log-space-limit=50M
set-variable = max-relay-log-size=10M
set-variable = report-host=173.45.243.96:3306
master-host=173.45.243.171
master-user=repl
master-password=repl12
master-port=3306
master-connect-retry=60
server-id=1562
#replicate-do-db=test #let slave only do test db's update
#######################
# end slave settings #
#######################
[mysql.server]
user=mysql
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/log/mysql/mysqld.pid
3：修改配置后啟動Master數(shù)據(jù)服務(wù)。在Master數(shù)據(jù)庫上建立一個(gè)用戶，用于Slave數(shù)據(jù)連接以便同步數(shù)據(jù)。一般來說Slave數(shù)據(jù)只用于同步數(shù)據(jù)，所以我們在建立這個(gè)用戶時(shí)只授予它REPLICATION SLAVE 權(quán)限。
eg: GRANT REPLICATION SLAVE ON *.* TO 'repl'@173.45.243.96 IDENTIFIED BY ‘repl12’;
4：在Master數(shù)據(jù)庫上執(zhí)行 FLUSH TABLES WITH READ LOCK; 命令以刷新數(shù)據(jù)并阻止對Master數(shù)據(jù)的寫入操作。然后將Master數(shù)據(jù)的data目錄復(fù)制一份覆蓋Slave數(shù)據(jù)庫的data目錄，這樣Master和Slaver就有了相同的數(shù)據(jù)庫了。在復(fù)制時(shí)可能不需要同步 mysql 數(shù)據(jù)庫，因?yàn)樵?/span>slave上的權(quán)限表和master不一樣。這時(shí)，復(fù)制的時(shí)候要排除它。同時(shí)不能包含任何`master.info~ 或 `relay-log.info` 文件。覆蓋好后執(zhí)行 UNLOCK TABLES; 釋放鎖定。
5：在Master數(shù)據(jù)庫上執(zhí)行SHOW MASTER STATUS; 查看當(dāng)前Master數(shù)據(jù)庫上的一些我們將要使用的信息：
File 表示 Master用于記錄更新數(shù)據(jù)操作的日志文件，Position 表示當(dāng)前日志的記錄位置，這也是Slave 需要開始同步數(shù)據(jù)的位置。
6：啟動Slave數(shù)據(jù)庫 執(zhí)行：（這點(diǎn)連接Master數(shù)據(jù)庫所要的參數(shù)）
mysql> stop slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST='173.45.243.171', //Master服務(wù)器地址（if是在本機(jī)上安裝兩個(gè)數(shù)據(jù)庫的, 則用127.0.0.1）
-> MASTER_USER='slaver, //Slave服務(wù)器更新時(shí)連接Master使用的用戶名
-> MASTER_PASSWORD='slaver', // Slave服務(wù)器更新時(shí)連接Master使用的密碼
-> MASTER_LOG_FILE='mysql-bin.000004', //更新操作日志
-> MASTER_LOG_POS=837016; //同步數(shù)據(jù)的開始位置
上面命令執(zhí)行完畢后，執(zhí)行START SLAVE; 命令啟動數(shù)據(jù)更新。在Slave 數(shù)據(jù)庫上執(zhí)行:
SHOW SLAVE STATUS; 查看從數(shù)據(jù)跟主數(shù)據(jù)庫的連接狀態(tài)是否正常，如果顯示的信息中
的 Slave-IO-Running 和 Slave_SQL_Running 值為 yes，表示用于數(shù)據(jù)同步的 io線程和sql操作線程已經(jīng)成功啟動。
eg:
mysql> show slave status;
+----------------------------------+----------------+-------------+-------------+---------------+---------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+----------------+-------------+-------------+---------------+---------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 173.45.243.171 | root | 3306 | 60 | pts1-log-bin.000005 | 839 | mysqld-relay-bin.000005 | 979 | pts1-log-bin.000005 | Yes | Yes | | | | | | | 0 | | 0 | 839 | 979 | None | | 0 | No | | | | | | 0 |
+----------------------------------+----------------+-------------+-------------+---------------+---------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)
一般情況下，重新啟動SLAVE的MYSQL就可以了。
如果Slave-IO-Running＝No, 重新啟動MASTER和SLAVE的MYSQL再試一下。
7：到此已經(jīng)建立Master和Slave數(shù)據(jù)庫的同步了。你可以在Master數(shù)據(jù)庫上更新一個(gè)表的數(shù)據(jù)，然后查看Slave數(shù)據(jù)庫上對應(yīng)表是否做了相應(yīng)的更改。
注： slave開始同步后，就能在數(shù)據(jù)文件目錄下找到2個(gè)文件 `master.info` 和`relay-log.info`。slave利用這2個(gè)文件來跟蹤處理了多少master的二進(jìn)制日志。master.info 記錄了slave 連接master進(jìn)行數(shù)據(jù)同步的參數(shù)，relay-log.info 記錄了slave進(jìn)行數(shù)據(jù)更新使用的中續(xù)日志的的信息。
Master 與 Slave 數(shù)據(jù)同步后對性能進(jìn)行粗略的測試：
(1). 在Master數(shù)據(jù)庫上建立一個(gè)新的數(shù)據(jù)庫testdata，并建立表testdata。建立腳本如下
CREATE DATABASE `testdata` /**//*!40100 DEFAULT CHARACTER SET utf8 */;
DROP TABLE IF EXISTS `testdata`.`testms`;
CREATE TABLE `testdata`.`testms` (
`name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意：由于上面我們建立的Master和Slave之間的數(shù)據(jù)同步是針對所有數(shù)據(jù)庫的，所以當(dāng)你在Master上建立數(shù)據(jù)庫和表后，Slave
上自動也會建立對應(yīng)的數(shù)據(jù)庫和表，如果發(fā)現(xiàn)Slave上沒有對應(yīng)的數(shù)據(jù)庫和表則必須檢查是否Master和Slave是否正常同步數(shù)據(jù)。
(2). 測試代碼如下（Java）：
分別調(diào)用兩個(gè)不同的方法，進(jìn)行兩種類型的測試
package com.ckcs.test;
/**//*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
/** *//**
*
* @author Administrator
*/
public class TestMS ...{
/** *//**
* 對master數(shù)據(jù)庫進(jìn)行數(shù)據(jù)插入和查詢操作
*/
private void testSingleDB() ...{
try ...{
//Master數(shù)據(jù)庫
final String singleUrl = "jdbc:mysql://localhost:3306/testdata?characterEncoding=utf-8&user=super&password=iamasuper006";
Class.forName("com.mysql.jdbc.Driver");
new Thread() ...{
public void run() ...{
long start = System.currentTimeMillis();
try ...{
Connection updateCon = DriverManager.getConnection(singleUrl);
for (int i = 0; i < 1000; i++) ...{
Statement state = updateCon.createStatement();
//插入數(shù)據(jù)
state.execute("insert into testms values('wo de tian kong.')");
state.close();
}
} catch (SQLException ex) ...{
Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
}
System.out.println("更新用時(shí)： " + (System.currentTimeMillis() - start));
}
}.start();
new Thread() ...{
public void run() ...{
long start = System.currentTimeMillis();
try ...{
Connection queryCon = DriverManager.getConnection(singleUrl);
for (int j = 0; j < 100000; j++) ...{
Statement queryState = queryCon.createStatement();
//查詢數(shù)據(jù)
queryState.execute("select * from testms");
queryState.close();
}
} catch (SQLException ex) ...{
Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
}
System.out.println("查詢用時(shí)： " + (System.currentTimeMillis() - start));
}
}.start();
} catch (Exception ex) ...{
Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
}
}
/** *//**
* 對Master數(shù)據(jù)庫進(jìn)行數(shù)據(jù)的插入，對Slave數(shù)據(jù)庫進(jìn)行數(shù)據(jù)的查詢
*/
private void testMSDB() ...{
try ...{
//Master 數(shù)據(jù)庫
final String updateUrl = "jdbc:mysql://localhost:3306/testdata?characterEncoding=utf-8&user=super&password=iamasuper006";
Class.forName("com.mysql.jdbc.Driver");
new Thread() ...{
public void run() ...{
long start = System.currentTimeMillis();
try ...{
Connection updateCon = DriverManager.getConnection(updateUrl);
for (int i = 0; i < 1000; i++) ...{
Statement updateState = updateCon.createStatement();
//插入數(shù)據(jù)
updateState.execute("insert into testms values('wo de tian kong.')");
updateState.close();
}
} catch (SQLException ex) ...{
Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
}
System.out.println("更新用時(shí)： " + (System.currentTimeMillis() - start));
}
}.start();
//Slave數(shù)據(jù)庫
final String queryUrl = "jdbc:mysql://localhost:3307/testdata?characterEncoding=utf-8&user=super&password=iamasuper006";
new Thread() ...{
public void run() ...{
long start = System.currentTimeMillis();
try ...{
Connection queryCon = DriverManager.getConnection(queryUrl);
for (int j = 0; j < 100000; j++) ...{
Statement queryState = queryCon.createStatement();
//查詢數(shù)據(jù)
queryState.execute("select * from testms");
queryState.close();
}
} catch (SQLException ex) ...{
Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
}
System.out.println("查詢用時(shí)： " + (System.currentTimeMillis() - start));
}
}.start();
} catch (Exception ex) ...{
Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static void main(String[] args) ...{
TestMS test = new TestMS();
test.testMSDB();
}
}
(3).測試所得數(shù)據(jù)：
（1）測試數(shù)據(jù)比較
注： 查詢數(shù)據(jù)次數(shù)為 100000;， 插入數(shù)據(jù)次數(shù)為1000
|
測試
類型
測試次數(shù)
用時(shí)
|
對Master數(shù)據(jù)庫進(jìn)行插入和查詢操作
|
對Master數(shù)據(jù)庫進(jìn)行插入操作，對Slave數(shù)據(jù)庫進(jìn)行查詢操作
|
|
插入
|
查詢
|
插入
|
查詢
|
|
第一次用時(shí)
|
63141
|
18172
|
66078
|
10656
|
|
第二次用時(shí)
|
67875
|
20109
|
68969
|
11860
|
|
第三次用時(shí)
|
65796
|
18265
|
65672
|
10906
|
|
平均用時(shí)
|
65604
|
18848
|
66906
|
11140
|
（測試數(shù)據(jù)統(tǒng)計(jì)表）
雖然數(shù)據(jù)測試是很粗糙的，但確實(shí)反映出性能的一定改善。
posted on 2009-02-12 15:51
Blog of JoJo 閱讀(750)
評論(2) 編輯 收藏 所屬分類:
Linux 技術(shù)相關(guān)