windows 下mysql 主从库的配置

一般主从库的配置，也分为两种，一种是从库针对主库的所有数据库，也就是 主数据库或者是数据有变动，从也变动，即使是主上多了数据库，从也会跟着增加。还有一种就是从库只是针对主动的一个或者是多个规定死的数据库，那么这种，就是我们本文讨论的。

 

注意事项

a:先将主服务器里面的数据库，拷贝一下，放到从服务器上，这样是先保证主从的数据的一致性

b：在执行上面一部的时候，可以在主上面使用导入导出sql，而且最好在导出前，加上一句  flush tables with read lock; (进行锁表操作，不让数据进行写入动作，这么做事为了防止从数据库的原始数据和主数据库的原始数据不一致)   在导出之后，我们去从数据库上把数据导入，然后再回到主数据库上执行  unlock tables;   进行解锁

c: 如果主里面有语句用到 uuid()之类的，记得修改 SET GLOBAL binlog_format = 'MIXED'; （在下面的文章会讲的）

下面是具体的配置，我有两台windows 服务器 

A 主  192.168.1.28

B 从   192.168.1.2

 

在配置之前，必要的修改

1：对主数据库，修改

 

开始配置  主服务器

1：在mysql下找到my.ini文件，在其[mysqld] 下添加主服务器端配置：

server-id=1    #服务器 id
log-bin=D:\Program Files\MySQL\MySQL Server 5.1\mysql-bin    #二进制文件存放路径
binlog-do-db=testadd    #待同步的数据库  （如果这一行没有，那么就是针对所有的不用忽略的数据库进行同步）
binlog-ignore-db=information_schema   #忽略不同步的数据库，这个可以不用写
binlog-ignore-db=mysql    #忽略不同步的数据库，这个可以不用写
binlog-ignore-db=test    #忽略不同步的数据库，这个可以不用写

 

注意，上面的mysql-bin 并不是指一个文件夹，而是指 存放为二进制数据

2：保存my.ini文件，重启数据库，然后会在D:\Program Files\MySQL\MySQL Server 5.1 下看到mysql-bin.index 文件和mysql-bin.000001文件；

 

3：通过cmd进入mysql：cd D:\Program Files\MySQL\MySQL Server 5.1\bin

输入用户名和密码：mysql -u用户名 -p密码 (    mysql -uroot -proot   )

 

4：给要连接的从服务器设置权限：(注意下面的 单引号  分号 )

grant replication slave,reload,super on *.* to 'backup'@'192.168.1.%' identified by '123456';   

注释： 给主机192.168.1.%添加权限，用户名：backup，密码：123456；（只需输入一次就可以了）

*.* 表示任意数据库中的任意表，'192.168.1.%' 表示只允许192.168.1.··网段的“从”访问“主” 的数据库

如果你把字符，单引号或者是分号漏掉了，那么运行会没有反应的，如果运行成功，会显示 query ok

实际就是在mysql中添加一个backup的奴隶账号，并授权给从服务器。创建backup用户，并授权给192.168.1.··使用。

 

5:进行锁表操作，不让数据进行写入动作，这么做事为了防止从数据库的原始数据和主数据库的原始数据不一致。(执行了之后，整个mysql服务器都被锁住了，只能读取不能写入，记得要在下面进行解锁)
 FLUSH TABLES WITH READ LOCK;

6：输入命令 show master status;       # 找到File 和 Position 的值记录下来； （如果你不小心把主服务器上的你的数据库删除了，然后你重新添加了一个同名的，但是这里的position 是会改变的，一定要注意）

6: 导出主库的数据 (建议是使用SQLyog的导出功能)

7：把导出的sql语句，放到 从数据库里面执行一下sql脚本 （可以使用SQLyog的导入功能，或者直接运行sql脚本）

这样从服务器上也有了 testadd这个数据库了

 

 

下面配置从服务器：

1：在mysql下找到my.ini文件，在其[mysqld] 下添加从服务器端配置：

server-id=2    #服务器 id ，不能和主服务器一致
replicate-do-db=testadd    #同步的数据库(需要备份的数据库名)，不写本行 表示 同步所有数据库
binlog-ignore-db=information_schema   #忽略不同步的数据库，这个可以不用写
binlog-ignore-db=mysql    #忽略不同步的数据库，这个可以不用写
binlog-ignore-db=test    #忽略不同步的数据库，这个可以不用写

 

2：保存my.ini文件，重启从数据库，在mysql5.1以上版本中是不支持1中master设置的，如果添加了master设置，数据库就无法重启了(这个可能是针对以前的低于5.1的数据库的，反正我们没有用到那么低的，所以不用管什么 master设置)；

 

3：通过cmd进入mysql：cd C:\Program Files\MySQL\MySQL Server 5.5\bin

 

4：从服务器上测试：mysql -ubackup -p123456 -h 192.168.1.19，查看能否连接主数据库成功，如果成功，则一定要退出来(我是直接把这个dos界面关掉即可)，之前我就是一直犯了一个错误，按照网上的步骤测试完毕之后就没有退出mysql，结果执行以下步骤时老是报错，其原因是一直设置的是主数据库；

 

5：重新打开一个dos界面，用从数据库的管理员账号进入。mysql –uroot –p123456 如果你的从数据库的root账号和密码和主数据库的不一样，你就能很清楚的明白你这里是在用从数据库登陆

修改对主数据库的连接的参数：mysql>change master to master_host='192.168.1.19',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106;  ps：可能会报一个错误，大概的意思是说slave线程正在运行，不能设置，这样的话，执行mysql>stop slave; 停止slave线程，然后再设置连接的参数；

上面打红色字的，就是之前主服务器要求记录的数据

 

6：设置完之后，执行 mysql> start slave; 开启slave线程；执行mysql> show slave status\G   (没有分号)，查看下面2个是不是等于 yes

Slave_IO_Running: Yes               这个表示：连接到主库，并读取主库的日志到本地，生成本地日志文件. yes表示  网络正常

Slave_SQL_Running: Yes             这个表示：读取本地日志文件，并执行日志里的SQL命令。  yes表示  表结构正常

 

现在 ，大功告成。我们去主服务器对mysql数据库进行解锁   unlock tables;  这个解锁的操作，有的人是放在 从数据库配置之前就开始解锁，这个可以个人来判断和使用

 

然后我们在主句库  添加一点数据，试试，从数据库有没有拉过来。

先去主数据库

然后我们去看看从数据库,也OK了

 

可能会发生问题的地方：

1：一定要先把A的需要同步的数据库，放到B的里面来导入一下，这样才能保证AB的数据一致性

2：如果Slave_IO_Running: NO 那么要看看是不是  (1):A有账户在用dos界面在登陆数据库而没有退出 (2) A数据库的postion和file有改变，而B里面change的时候，还是用的以前的数据？

3: 主库不小心死机，重启之后发现  主库数据更新，但是从库不执行，造成了主从的数据不同步。但是在从上面查看 Slave_IO_Running 和  从库的状态，都是正常的。这个时候要注意在主库看看 show master status  看看postion和file是不是和从库一致的，我就发现我的主库死机之后再次开机就不一样了。这个时候我们对从库进行  CHANGE MASTER TO MASTER_HOST='192.168.1.19',MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=1598

但是这里还是有个问题啊，虽然这个时候，主从可以开始同步了，但是由于这中间，如果不是及时发现，那么主从的数据还是会有差异的，那这个怎么解决呢？  答案是，只能从新做主从··悲催··

4：如果我在主里面  delete  from  xx表  那么在从里面，到底是读出主里面删除了哪些行，进而对从进行删除？还是直接也是和主一样，运行了 delete from xx呢？答案是后者，从会像主一样，直接运行 delete from  xx 。所以如果你是有上面3这个问题发生（主从数据不同步了），后面再进行了其他操作的话，会越来越不同步的，所以赶紧重新做主从

5: 在主库插入语句里面使用 uuid() 用来自动生成的数值，在主库和从库，不一样，例如一个语句

INSERT INTO `1` (`name`,`addresss`,`uid`) VALUES(1,2,UUID())  但是主库和从库显示出来的 UUID值不一样

这个时候就要修改主库的mysql binlog格式 SET GLOBAL binlog_format = 'MIXED';

查看一下 SHOW GLOBAL VARIABLES LIKE 'binlog_format';

6:.当MySQL主从复制在 show slave status\G 时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时，需要首先通过 stop slave 来停止从服务器，然后再执行一次本文的主从配置步骤（主要是看主的file和positioon，然后从上change一下）即可恢复，但如果想尽可能的同步更多的数据，可以在Slave上将master_log_pos节点的值在之前同步失效的值的基础上增大一些，然后反复测试，直到同步OK。因为MySQL主从复制的原理其实就是从服务器读取主服务器的binlog，然后根据binlog的记录来更新数据库。

 

常用的几个操作命令

主服务器master 

"show master status;"查看下主库的状态，主要是日志的文件和position

"flush logs;"来清空日志

 

从服务器slave

SHOW SLAVE STATUS  查看复制的状态

"slave stop;"来停止从库同步；

执行:"change master to master_host='192.168.48.128',master_user='backup',master_password='backup',master_log_file='mysql-bin.000003',master_log_pos=1650;"，文件和位置对应master中的file和position;

"slave start;"来启动同步。

 

参考资料

1：主从配置（包括全部数据库同步，和，个别数据库同步两种同步） http://gaosc900926.blog.51cto.com/4449050/1046383

2：MySQL数据库主从同步安装与配置总结 http://blog.sina.com.cn/s/blog_49fd52cf0100pog2.html

3： 重新做主从的方法 http://www.jb51.net/article/33052.htm

4: 读写分离 MySQL-Proxy 的运用 http://www.cnblogs.com/luckcs/articles/2543607.html

 

最后讲一下   Mysql复制的几种模式   也是和binlog的格式相关的资料

.从 MySQL 5.1.12 开始，可以用以下三种模式来实现：

– 基于SQL语句的复制(statement-based replication, SBR)，

– 基于行的复制(row-based replication, RBR)，

– 混合模式复制(mixed-based replication, MBR)。

相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是默认的。

在运行时可以动态改动 binlog的格式，除了以下几种情况：

1.存储流程或者触发器中间

2.启用了NDB

3.当前会话试用 RBR 模式，并且已打开了临时表

如果binlog采用了 MIXED 模式，那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式：

1.当DML语句更新一个NDB表时

2.当函数中包含 UUID() 时 (我们上面就是有遇到这样的情况，如果是 Statement的话，就会导致主从生成的UUID不一致了)

3.2个及以上包含 AUTO_INCREMENT 字段的表被更新时

4.行任何 INSERT DELAYED 语句时

5.用 UDF 时

6.视图中必须要求运用 RBR 时，例如建立视图是运用了 UUID() 函数

3.2.设定主从复制模式：

log-bin=mysql-bin

#binlog_format="STATEMENT"

#binlog_format="ROW"

binlog_format="MIXED"

也可以在运行时动态修改binlog的格式。例如

mysql> SET SESSION binlog_format = 'STATEMENT';

mysql> SET SESSION binlog_format = 'ROW';

mysql> SET SESSION binlog_format = 'MIXED';

mysql> SET GLOBAL binlog_format = 'STATEMENT';

mysql> SET GLOBAL binlog_format = 'ROW';

mysql> SET GLOBAL binlog_format = 'MIXED';