首页 » Linux » CentOS » 在windows和centos做mysql主从搭建方法

在windows和centos做mysql主从搭建方法

 

网上很多主从搭建的方法,但大多都是同平台下搭建,本篇文章是以centos服务器为主windows2008服务器为从,搭建mysql主从。

一些想法:windows和centos平台不同,是否能做mysql主从,后来想了想,mysql也是可以在windows上跑的,同是mysql应该不会有什么问题存在,然后此篇文章产生了……

MySQL主从复制原理:

MySQL的主从复制是一个异步的复制过程(虽然一般情况下感觉是实时的),数据将从一个Mysql数据库(我们称之为Master)复制到另一个Mysql数据库(我们称之为Slave),在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程(SQL线程和IO线程)在Slave端,另一个线程(I/O线程)在Master端。

要实现MySQL的主从复制,首先必须要先打开Master端的binlog记录功能。因为整个复制过程实际上就是slave从master端获取binlog日志,然后再在slave上以相同顺序执行获取的binlog日志中所记录的各种SQL操作。

简单点来说:

就是主服务器将改变的内容记录到二进制日志文件(binlog)中,从服务器将主服务器的二进制文件拷贝到它的中继日志(relay log),并重新开启SQL线程,从中继日志中读取二进制日志,使其数据和主服务器的保持一致,最后slave端的I/O线程和SQL线程将进入睡眠状态,等待下一次被唤醒。

注意:如果你的版本和我所写的版本不同,可能在配置过程中会有问题出现,为了确保成功性,建议现在本地环境测试成功,再对线上服务进行操作,请确保服务器的时间同步一致。

服务器配置:

linux主192.168.0.70
版本Centos6.7 nginx1.10 php5.4.45 mysql5.5.48

windows从192.168.0.71
版本IIS7 mysql5.5.54 php5.6.29

 

master centos

设置master mysql配置文件/etc/my.cnf

#log-bin表示开启mysql的binlog日志功能,指定的mysql-bin表示日志文件的命名格式,会生成文件名为mysql-bin.000001、mysql-bin.000002、等的日志文件。
#server-id是节点标识,主、从的节点必须是全局唯一,不能相同。
#expire_logs_days二进制日志自动删除的天数,0则是不自动删除。
#注意,log-bin和server-id都是在[mysqld]模块内的。
#注意,先在my.cnf中查找相关参数并修改,如果查找不到则手动添加,参数不能重复。
修改配置文件完成后重启mysql

登录mysql查看server-id的值是否为1

m1

查看binglog功能是否开启

m2

建立数据库whsir(我这里作为演示用,见附录3)

查看bin-log的信息

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 107 | whsir | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

#注意:这里记住File的值:mysql-bin.000008和Position的值:107,后面会用到。
#根据主从复制原理,从库想要和主库同步,必须要有一个连接的帐号,这个帐号是要在主库上创建的。

授权可以来读取日志文件的用户

#REPLICATION SLAVE为mysql的必须权限,此处不要ALL。
#*.*表示所有库和表,此处也可以指定具体的库和表。例如aaa库的bbb表:aaa.bbb
#'zhu'@'192.168.0.71',同步的帐号和授权的主机地址,主机地址可以使用%进行通配。
#此处为演示用,密码就设置了123456。

刷新权限,使其生效

查看zhu帐号是否生效

+------+--------------+
| user | host |
+------+--------------+
| root | 127.0.0.1 |
| zhu | 192.168.0.71 |
| root | ::1 |
| root | localhost |
| test | localhost |
+------+--------------+

为了确保主服务器的账户配置正确,我们在从slave服务器上,登录下master mysql(此处如果连不上,查看是不是防火墙问题)
mysql -uzhu -p123456 -h192.168.0.70

 

slave windows

修改my.ini 原来配置文件中有的话就不用在添加了

my.ini配置中一定要配置的就是server-id其他都可以省略掉!!!

配置好my.ini后重启mysql(不知道windows中如何重启mysql的请自行google)

m3

创建whsir库(我这里做演示用,直接就创建了,没有导入数据)

登录mysql查看server-id的值是否为2

m4

先停止slave同步,也可以mysql>STOP SLAVE;

#MASTER_PORT是主服务器端口,默认就是3306。
#MASTER_CONNECT_RETRY是连接失败后等待的秒数。
#注意上面是分开写的,你也可以写在一行里。
#上述操作原理其实是把用户的信息写入到了从库的data/master.info中了

上述配置好后启动slave同步

查看状态\G就是结束,再加一个";" 就多余了,如果加上;会报错

m5

如果看到Slave_IO_Running和Slave_SQL_Running都是Yes,Seconds_Behind_Master:0,表示主从服务器已经配置成功

#Slave_IO_Running是IO的线程状态,IO线程负责从slave库到master库读取binlog日志,并写入到slave的中继日志(relay-log),Yes表示IO线程工作正常。
#Slave_SQL_Running是SQL的线程状态,SQL线程负责读取中继日志(relay-log)中的数据并转换为SQL语句应用到slave库中,Yes表示SQL线程工作正常。
#Seconds_Behind_Master是复制过程中,slave库比master库延迟的秒数。

 

附录:

1、从服务器遇到错误:ERROR 1201 <HY000>: Could not initialize master info structure; more error messages can be found in the mysql error log
错误原因是因为从数据库之前已经做过主从复制了,所以要先停止从库,再进行从库设置。
解决方法:
mysql>stop slave;
mysql>reset slave;
mysql>CHANGE MASTER TO
mysql>MASTER_HOST='192.168.0.70',
mysql>MASTER_USER='zhu',
mysql>MASTER_PASSWORD='123456',
mysql>MASTER_PORT=3306,
mysql>MASTER_LOG_FILE='mysql-bin.000008',
mysql>MASTER_LOG_POS=107,
mysql>MASTER_CONNECT_RETRY=10;

2、从服务器Slave_SQL_Running:No
出现问题原因:
可能slave服务器重启导致
也可能是在slave进行了写操作
解决办法一:
mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;
解决办法二:
查看master服务器File和Position值,需要停止master的写操作。
mysql>SHOW MASTER STATUS;
在从服务器上停掉slave服务
mysql>slave stop;
然后在执行下面,注意更改File和Position值
mysql>CHANGE MASTER TO
mysql>MASTER_HOST='192.168.0.70',
mysql>MASTER_USER='zhu',
mysql>MASTER_PASSWORD='123456',
mysql>MASTER_PORT=3306,
mysql>MASTER_LOG_FILE='mysql-bin.000008',
mysql>MASTER_LOG_POS=107,
mysql>MASTER_CONNECT_RETRY=10;
开启slave服务
mysql>slave start;

3、如果服务器的mysql已经在跑着了,需要先锁定数据库防止写入并导出数据库。
mysql>FLUSH TABLES WITH READ LOCK; #master锁定数据库防止写入,锁表后再开一个SSH进行备份操作(当前窗口不要动)。
mysqldump -u root -p123456 --all-databases --lock-tables=false > /root/all.sql #master导出数据库
mysql -u root -p123456 < /root/all.sql #slave服务器导入数据
mysql>UNLOCK TABLES; #master解锁表

4、Slave_IO_Running: No遇到了几次这个问题,最后发现是主服务器防火墙挡住了,关闭主服务器的防火墙(或者自行添加规则),在从服务器上
mysql>slave stop;
mysql>slave start;

5、查看只读状态:show global variables like "%read_only%";

修改只读状态:set global read_only=off;或set global read_only=on;

6、重启mysql不会影响主从服务,还是尽量避免重启。

7、binglog日志自动清理
在my.ini或my.cnf中设置expire_logs_days = 7
表示二进制日志自动删除的天数,0则是不自动删除。
可以通过show variables like '%log%';查看
其中这一行就是自动删除的天数expire_logs_days | 7

8、master的SHOW MASTER STATUS;没有返回结果,检查下master的binlog配置是否正确。
mysql>SHOW VARIABLES LIKE 'log_bin';

9、relicate-wild-ignore-table是复制过滤选项,可以过滤不需要复制的数据库或表,例如:relicate-wild-ignore-table=mysql.%
replicate-wild-do-table用来指定需要复制的数据库或表,例如:replicate-wild-do-table=test.%
过滤多个,就多写一行。

注意:不要在主库上使用binlog-do-db或binlog-ignore-db选项,也不要在从库上使用relicate-do-db或relicate-ignore-db选项,因为这样可能会产生跨库更新失败的问题,推荐直接在从库上使用replicate-wild-do-table和relicate-wild-ignore-table两个选项来解决复制过滤的问题。

不停止mysql服务配置主从https://blog.whsir.com/post-606.html

windows 2008 安装mysql5.5.54https://blog.whsir.com/post-532.html

linux修改mysql字符集编码https://blog.whsir.com/post-487.html

原文链接:在windows和centos做mysql主从搭建方法,转载请注明来源!

2