在MySql上实现Replication(Master 与 Slave 数据同步) _MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2332 | 回复: 0

主题： 在MySql上实现Replication(Master 与 Slave 数据同步)

huan.liu

注册用户

等级：新兵
经验：72
发帖：58
精华：0
注册：2011-11-23
状态：离线
发送短消息息给huan.liu

加好友发送短消息息给huan.liu

发消息

发表于：

2014-12-23 9:52:54 | [全部帖] [楼主帖]

楼主

1：首先确定Master和Slave的数据库版本，Master数据库的版本不能高于Slave数据的版本。

这里我是使用MySql 5.0.27 作为Master数据库，MySql 6.0.3（alpha）作为Slave进行测试。

2：首先修改Master数据库的配置文件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 //使用的二进制日志文件名
log-bin-index=/var/log/mysql/mysqlbinlog/pts1-log-bin.index
server-id=1 //服务器编号
#######################
# end master settings #
#######################
[mysql.server]
user=mysql
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/log/mysql/mysqld.pid

在Slave数据库的配置文件my.ini, or my.cnf里添加server-id 项

eg:[mysqld]
server-id = 2
(这里需要理解的是Slave本身也是一个独立的服务器，它作为‘从数据库’是从它通过‘主服务器’日志更新数据角度上理解的。可以把 server-id 想象成为IP地址：这些ID标识了整个同步组合中的每个服务器。如果没有指定 server-id 的值，如果也没定义 master-host，那么它的值就为1，否则为2。注意，如果没有设定 server-id，那么master就会拒绝所有的slave连接，同时slave也会拒绝连接到master上。)

整个修改如下：

[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数据服务。在Master数据库上建立一个用户，用于Slave数据连接以便同步数据。一般来说Slave数据只用于同步数据，所以我们在建立这个用户时只授予它REPLICATION SLAVE 权限。

eg: GRANT REPLICATION SLAVE ON *.* TO 'repl'@173.45.243.96 IDENTIFIED BY ‘repl12’;

4：在Master数据库上执行 FLUSH TABLES WITH READ LOCK; 命令以刷新数据并阻止对Master数据的写入操作。然后将Master数据的data目录复制一份覆盖Slave数据库的data目录，这样Master和Slaver就有了相同的数据库了。在复制时可能不需要同步 mysql 数据库，因为在slave上的权限表和master不一样。这时，复制的时候要排除它。同时不能包含任何`master.info~ 或 `relay-log.info` 文件。覆盖好后执行 UNLOCK TABLES; 释放锁定。

5：在Master数据库上执行SHOW MASTER STATUS; 查看当前Master数据库上的一些我们将要使用的信息：

File 表示 Master用于记录更新数据操作的日志文件，Position 表示当前日志的记录位置，这也是Slave 需要开始同步数据的位置。

6：启动Slave数据库执行：（这点连接Master数据库所要的参数）

mysql> stop slave;
mysql> CHANGE MASTER TO
->  MASTER_HOST='173.45.243.171',  //Master服务器地址（if是在本机上安装两个数据库的, 则用127.0.0.1）
->  MASTER_USER='slaver,  //Slave服务器更新时连接Master使用的用户名
->  MASTER_PASSWORD='slaver',  // Slave服务器更新时连接Master使用的密码
->  MASTER_LOG_FILE='mysql-bin.000004', //更新操作日志
->  MASTER_LOG_POS=837016;   //同步数据的开始位置

上面命令执行完毕后，执行START SLAVE; 命令启动数据更新。在Slave 数据库上执行:

SHOW SLAVE STATUS; 查看从数据跟主数据库的连接状态是否正常，如果显示的信息中

的 Slave-IO-Running 和 Slave_SQL_Running 值为 yes，表示用于数据同步的 io线程和sql操作线程已经成功启动。

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：到此已经建立Master和Slave数据库的同步了。你可以在Master数据库上更新一个表的数据，然后查看Slave数据库上对应表是否做了相应的更改。

注： slave开始同步后，就能在数据文件目录下找到2个文件 `master.info` 和`relay-log.info`。slave利用这2个文件来跟踪处理了多少master的二进制日志。master.info 记录了slave 连接master进行数据同步的参数，relay-log.info 记录了slave进行数据更新使用的中续日志的的信息。

Master 与 Slave 数据同步后对性能进行粗略的测试：

(1). 在Master数据库上建立一个新的数据库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之间的数据同步是针对所有数据库的，所以当你在Master上建立数据库和表后，Slave 上自动也会建立对应的数据库和表，如果发现Slave上没有对应的数据库和表则必须检查是否Master和Slave是否正常同步数据。

(2). 测试代码如下（Java）：

分别调用两个不同的方法，进行两种类型的测试

package com.ckcs.test;
/**/
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;
/** */
public class TestMS ...

(3).测试所得数据：

（1）测试数据比较

注：查询数据次数为 100000;，插入数据次数为1000
北京联动北方科技有限公司

（测试数据统计表）

虽然数据测试是很粗糙的，但确实反映出性能的一定改善。