mysql双击同步_MySQL双机主从同步与双向同步

MySQL双机主从同步与双向同步

(一)资料：

原理：m

aster把所有对数据库的更新、删除 等)都记录在二进制日志里。因此，想要启用同步机制，在master就必须启用二进制日志。每个slave接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。

slave同步失败主要问题：

二进制日志只是从启用二进制日志开始的时刻才记录更新操作的，所有的slave必须在启用二进制日志时把master上已经存在的数据拷贝过来。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。

数据库初始同步方式：

把master上的数据拷贝过来的方法之一实在slave上执行LOAD DATA FROM MASTER语句。不过要注意，LOAD DATA FROM MASTER是从MySQL4.0.0之后才开始可以用的，而且只支持master上的MyISAM类型表

暂停主服务器，导入数据文件，在从服务器上导入。

MySQL同步细节

MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行START SLAVE语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行SHOW PROCESSLIST语句后的结果中的Binlog Dump线程便是。slave上的I/O线程读取master的Binlog Dump线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是salve用它来读取中继日志，然后执行它们来更新数据。如上所述，每个mster/slave上都有3个线程。每个master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。在MySQL4.0.2以前，同步只需2个线程(master和slave各一个)。slave上的I/O和SQL线程合并成一个了，它不使用中继日志。slave上使用2个线程的优点是，把读日志和执行分开成2个独立的任务。执行任务如果慢的话，读日志任务不会跟着慢下来。例如，如果slave停止了一段时间，那么I/O线程可以在slave启动后很快地从master上读取全部日志，尽管SQL线程可能落后I/O线程好几的小时。如果slave在SQL线程没全部执行完就停止了，但I/O线程却已经把所有的更新日志都读取并且保存在本地的中继日志中了，因此在slave再次启动后就会继续执行它们了。这就允许在master上清除二进制日志，因为slave已经无需去master读取更新日志了。执行SHOW PROCESSLIST语句就会告诉我们所关心的master和slave上发生的情况。

(二)配置过程：

1.安装MYSQL服务端。

2.编辑配置文件my.cnf

3.启动MYSQL并进入MYSQL进行命令配置操作，主要工作是使得主从能同步(在双机热互备中只是将两者互成主从机，只要一方改变数据就会同步另一方。)

(三)主从复制配置：

1.安装相同版本的MYSQL服务器软件。

2.配置mysql配置文件：

1)主服务器：

a)编辑主服务器的配置文件：/etc/my.cnf

server-id = 1

log-bin

binlog-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可

binlog-ignore-db=不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可。

2)从服务器：

a)编辑从服务器的配置文件：/etc/my.cnf(或者通过scp从主服务器复制过来修改)

server-id=2(配置多个从服务器时依次设置id号)

master-host=主机

master-user=用户名

master-password=密码

master-port=端口

replicate-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可

3.启动MYSQL并进行配置

1)在主服务器上为从服务器建立一个用户(建立复制帐号权限)：grant replication slave on *.* to‘用户名identified by‘密码’;(在MySQL 4 dot 0.2以前，用FILE权限来代替REPLICATION SLAVE)

*如果打算在slave上执行LOAD TABLE FROM MASTER或LOAD DATA FROM MASTER语句，那么必须给该帐户授予附加权限：授予全局SUPER和RELOAD权限。授予对想要加载的所有表上的SELECT权限。在master上任何没有SELECT权限的表都会被LOAD DATA FROM MASTER略过。

2)将数据从主服务器中导出并导入从服务器

Mysql> flush tables with read lock; #锁定主服务器上的表为只读状态

Mysql>show master status; #查看主节点的状态

[root@server02]#

mysqldump -uroot -p --skip-opt --single-transaction --add-drop-table

--create-options --quick --extended-insert --set-charset --disable-keys

CPMS > cpms.sql #创建一个完整的备份

3)[root@server02]# scp cpms.sqlroot@172.20.86.27:/tmp#把数据传输到从节点上

[root@server02]# mysql CPMS < /tmp/cpms.sql #把数据导入到从节点

Mysql>change master to master_host = '主服务器IP', master_user = '刚才建立用来复制的用户名', master_password = '复制用户名的密码', master_log_file = 'mysql-bin.000004', master_log_pos =98; #设置从节点连接主节点(master_log_file和master_log_pos这个需要在主服务器中通过show master status;查看得来)

Mysql>slave start; #启动复制

4)show slave status\G查看复制状态

mysql有部分特别配置，主要是运行性能等：

主配置文件增加：

[client]

default-character-set=utf8

[mysqld]

default-character-set=utf8

skip-name-resolve

back_log = 500

max_connections = 1000

max_connect_errors = 100

binlog-do-db=CPMS

从配置文件增加：

[client]

default-character-set=utf8

[mysqld]

default-character-set=utf8

skip-name-resolve

back_log = 500

max_connections = 1000

max_connect_errors = 100

slave_skip_error=1062

server-id = 2

sync_binlog=1

binlog-do-db=CPMS

replicate-do-db=CPMS

(四)Mysql的实时同步-双机互备

设置方法：

步一 设

A服务服(10.6.6.21)上用户为copy, copy,同步的数据库为CPMS;

B服务服(10.6.6.22)上用户为copy, copy,同步的数据库为CPMS;

步二 配置mysql.cnf:

A服务器

#replication master

server-id= 21

log-bin

binlog-do-db=CPMS

binlog-do-db=

#replication slave

master-host=10.6.6.22

master-user=copy

master-password=copy

master-port=3306

replicate-do-db=CPMS

B服务器

#replication master

server-id=20

binlog-do-db=CPMS

#replication slave

master-host=10.6.6.21

master-user=copy

master-password=copy

master-port=3306

replicate-do-db=CPMS

=============================================================

解释:

3)binlog-do-db=test表示需要备份的数据库是CPMS这个数据库,

如果需要备份多个数据库，那么应该写多行,如下所示:

binlog-do-db=backup1

binlog-do-db=backup2

binlog-do-db=backup3

解释:

1) server-id=2表示本机器的序号, A,B的server-id不能相同;

2)log-bin表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提;

其中mysql_binary_log是日志文件的名称，mysql将建立不同扩展名，文件名为mysql_binary_log的几个日志文件.

3) master-host="192.168.1.23"表示A做slave时的master为192.168.1.23;

4) master-user=root这里表示master上开放的一个有权限的用户,使其可以从slave连接到master并进行复制;

5) master-password=123456表示授权用户的密码;

6) master-port=3306 master上MySQL服务Listen3306端口;

7) master-connect-retry=60同步间隔时间;

8) replicate-do-db=test表示同步backup数据库;

最后重新启动两台机器的mysql.

------------------------------------------------

查看状态 及调试

1,查看master的状态

SHOW MASTER STATUS;

Position不应为0

2,查看slave的状态

show slave status;

Slave_IO_Running | Slave_SQL_Running这两个字段 应为YES|YES.

show processlist;

会有两条记录与同步有关state为Has read all relay log; waiting for the slave I/O thread to update it

和s Waiting for master to send event .

3,错误日志

MySQL安装目录\data\Hostname.err

4,CHANGE MASTER TO

如果A的Slave未启动,Slave_IO_Running为No.

可能会是B的master的信息有变化,

查看B SHOW MASTER STATUS;

记录下File,Position字段.假设为'mysql_binary_log.000004',98 ;

在A下执行:

Stop Slave;

CHANGE MASTER TO

MASTER_LOG_FILE = 'mysql_binary_log.000004',

MASTER_LOG_POS = 98 ;

Start Slave;

5,SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;

如果A的Slave_SQL_Running为No.

Err文件中记录:

Slave: Error 'Duplicate entry '1' for key 1' on query....

可能是master未向slave同步成功,但slave中已经有了记录。造成的冲突.

可以在A上执行

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;

跳过几步。再

restart salve;

===========================================================

第一部分：主从关系的同步

做

1. 安装配置

两台

两台服务器的配置一样，我们把关键的配置文件拷贝

一下，默认的配置文件是在 /usr/local/MySQL/share/MySQL目录下，分别有 my-large.cnf,

my-medium.cnf,

my-small.cnf等几个文家，我们只是测试，使用my-medium.cnf就行了。MySQL安装完后，默认的配置文件是指定在数据库存放目录

下的，我们用的是4.1.X的，所以配置文件就应该在 /usr/local/MySQL/var 目录下，于是把配置文件拷贝过去：cp /usr/local/MySQL/share/MySQL/my-medium.cnf /usr/local/MySQL/var/my.cnf

两台服务器做相同的拷贝配置文件操作。

2. 配置Master服务器

我们要把192.168.0.1配置为主MySQL服务器(master)，那么我们就要考虑我们需要同步那个数据库，使用那个用户同步，我们这里为了简单起见，就使用root用户进行同步，并且只需要同步数据库abc。

打开配置文件：vi /usr/local/MySQL/var/my.cnf

找到一下信息：# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1 //1为master，2为salve

添加两行：

重启192.168.0.1的MySQL服务器：/usr/local/MySQL/bin/MySQLadmin shutdown

/usr/local/MySQL/bin/MySQLd_safe --user=MySQL &

3. 配置Slave服务器

我们的slave服务器主要是主动去master服务器同步数据回来，我们编辑配置文件：vi /usr/local/MySQL/var/my.cnf

找到下面类似的信息：# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1

把上面的server-id修改为2，同时添加一些信息：server-id = 2 //本MySQL是slave服务器

master-host = 192.168.0.1 //master服务器的IP

master-user = root //连接master服务器的用户

master-password = '' //连接master服务器的密码

master-port = 3306 //连接端口

master-connect-retry = 10 //重试次数

replicate-do-db = abc //要同步的数据库

log-slave-updates //同步的形式

重启192.168.0.2的MySQL服务器：/usr/local/MySQL/bin/MySQLadmin shutdown

/usr/local/MySQL/bin/MySQLd_safe --user=MySQL &

4. 测试安装

首先查看一下slave的主机日志：cat /usr/local/MySQL/var/xxxxx_err (xxx是主机名)

检查是否连接正常, 看到类似这样的信息就成功了051031 11:42:40 MySQLd started

051031 11:42:41 InnoDB: Started; log sequence number 0 43634

/usr/local/MySQL/libexec/MySQLd: ready for connections.

Version: '4.1.15-log' socket: '/tmp/MySQL.sock'

port: 3306 Source distribution

051031 11:42:41 [Note] Slave SQL thread initialized,

starting replication in log 'FIRST'

at position 0, relay log './new4-relay-bin.000001' position: 4

051031 11:43:21 [Note] Slave I/O

thread: connected to master 'root@192.168.0.1:3306',

replication started in log 'FIRST' at position 4

在Master查看信息/usr/local/MySQL/bin/MySQL -u root

查看master状态：MySQL> show master status;

查看Master下MySQL进程信息：MySQL> show processlist;

在slave上查看信息：/usr/local/MySQL/bin/MySQL -u root

查看slave状态：MySQL> show slave status;

查看slave下MySQL进程信息：MySQL> show processlist;

你再在master的abc库里建立表结构并且插入数据，然后检查slave有没有同步这些数据，就能够检查出是否设置成功。

第二部分：双向关系的同步

master端 192.168.0.1slave端  192.168.0.21。MASTER端a.进入mysql，创建一个数据库abc：

create database abc;

b.创建一个用来同步的用户,指定只能在192.168.0.2登录:

grant replication slave on *.* to 'ha'@'192.168.0.2' identified by 'hapwd';c.修改master端的/etc/my.cnf文件     log-bin

server-id = 1

sql-bin-update-same 同步模式 ，在mysql5以上的版本都不需要这句，否则会启动不了mysql服务

binlog-do-db= abc    设置同步数据库，如果有多个数据库，每个数据库一行

binlog-ignore-db = mysql 设置不要同步的数据库，如有多个数据库，每个数据库一行

master-host=192.168.0.2

master-user=ha

master-password='hapwd'

master-port=3306

master-connect-retry=10

replicate-do-db=abc   设置要接收的数据库，如有多个数据库，每个数据库一行

replicate-ignore-db= mysql   设置不要接收的数据库，每个数据库一行   (一般这条可以不写)

log-slave-updates    在mysql5以上的版本都不需要这句

d.重启mysqle.进入mysql，执行：slave start;2。SLAVE端a.进入mysql，创建一个数据库abc：

create database abc;

b.创建一个用来同步的用户,指定只能在192.168.0.1登录:

grant replication slave on *.* to 'ha'@'192.168.0.1' identified by 'hapwd';c.修改slave端的/etc/my.cnf文件     log-bin     server-id       = 2

binlog-do-db= abc    设置同步数据库，如果有多个数据库，每个数据库一行

binlog-ignore-db = mysql 设置不要同步的数据库，如有多个数据库，每个数据库一行

sql-bin-update-same   同步模式 ，在mysql5以上的版本都不需要这句，否则会启动不了mysql服务

master-host=192.168.0.1

master-user= ha

master-password='hapwd'

master-port=3306

master-connect-retry=10

replicate-do-db=abc   设置要接收的数据库，如有多个数据库，每个数据库一行

replicate-ignore-db= mysql   设置不要接收的数据库，每个数据库一行   (一般这条可以不写)

log-slave-updates   在mysql5以上的版本都不需要这句

d.重启mysqle.进入mysql，执行：slave start;注意：1。如果有一端修改了数据后，另一端接收不到，手工执行命令load data from master;就可以了2。如果因为误删了二进制日志文件导致无法同步，则执行：(这几步很有用，你甚至可以把slave上的abc库删除掉，他会自动从master上复制过来重建)

slave stop;

reset slave;slave start;3。我们经常会在reset slave后发现重新同步时会出现重复数据无法倒入的情况，

比如：

Last_Errno: 1062

Last_Error: Error 'Duplicate entry '1472533' for key 1'

on query. Default database: 'epg'. Query: 'insert into

boot_info(stbid,begintime)values('zk124070c819','1259128442')'

这时可以这样，跳过冲突的这一行：

slave stop;

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;slave start;