分类： 系统运维
2011-05-23 14:57:06
原理：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)中。第三个是SQL线程，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.sql#把数据传输到从节点上
[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
的实时同步-双机互备
设置方法：
步一 设
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;
第一部分：主从关系的同步
做的时候要做的同步，两台安装一样的系统，都是FreeBSD5.4，安装了Apache 2.0.55和 4.4.0，MySQL的版本是4.1.15，都是目前最新的版本。
1. 安装配置
两台， 分别安装好MySQL，都安装在 /usr/local/MySQL 目录下（安装步骤省略，请参考相关文档），两台服务器的IP分别是192.168.0.1和192.168.0.2，我们把192.168.0.1作为 master数据库，把192.168.0.2作为slave服务器，我们采用单向同步的方式，就是master的数据是主的数据，然后slave主动去 master哪儿同步数据回来。
两台服务器的配置一样，我们把关键的配置文件拷贝 一下，默认的配置文件是在 /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 |
添加两行：
| -bin-update-same //同步形式 binlog-do-db = abc //要同步的数据库 |
重启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.2
1。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;
