【转载】MySQL主从服务器配置
摘要: 1、配置主服务器 2、配置从服务器 3、主从服务器状态查看及问题处理
MySQL的主从同步架构是当前比较流行的一种数据库架构,利用MySQL的主从配置,可以实现读写分离,减轻主数据库的访问压力,提升网站性能。MySQL的主从服务器的基本原理如下:
大致描述一下过程:从服务器的IO线程从主服务器获取二进制日志,并在本地保存为中继日志,然后通过SQL线程来在从上执行中继日志中的内容,从而使从库和主库保持一致。主从同步的详细过程如下:
1. 主服务器验证连接。
2. 主服务器为从服务器开启一个线程。
3. 从服务器将主服务器日志的偏移位告诉主服务器。
4. 主服务器检查该值是否小于当前二进制日志偏移位。
5. 如果小于,则通知从服务器来取数据。
6. 从服务器持续从主服务器取数据,直至取完,这时,从服务器线程进入睡眠,主服务器线程同时进入睡眠。
7. 当主服务器有更新时,主服务器线程被激活,并将二进制日志推送给从服务器,并通知从服务器线程进入工作状态。
8. 从服务器SQL线程执行二进制日志,随后进入睡眠状态。
更多资料参考:http://blog.chinaunix.net/uid-20639775-id-3254611.html
例如现在我们有两台服务器,172.17.22.187和172.17.22.188, 现在把188服务器的MySQL数据库配为主服务器,187数据库配为从服务器(Slave),当然,Slave可以有多个。MySQL主从服务器一般使用方法为主服务器(Master)负责网站的NonQuery操作,从服务器(Slave)负责Query操作,我们可以根据网站功能模块的特性来指定访问的服务器,比如前台项目列表,投融资记录查询等模块可以指定访问Slave,投标,项目审核等等交易性的操作访问Mater,也可以写个池或队列,自由为请求分配从服务器连接。
1、配置主服务器
上面主从同步的原理大致可以理解为主从服务器通过IO线程上的Binary Log复制,和SQL语句的重放实现数据的同步,当然复制时主服务器返回信息中除了日志所包含的信息之外,还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置,这个过程的更多资料可以搜“MySQL Replication 线程”,如:
http://blog.csdn.net/bengda/article/details/7852889
在上面权限管理的小结中也提到了,有一个叫”REPLICATION SLAVE”的权限,它是服务器管理中的复制权限,有这个权限slave才能从master服务器上复制binlog日志。所以我们需要创建一个数据库用户,并给他赋予replication权限,创建并赋权的sql如下:
GRANT REPLICATION SLAVE,FILE ON *.* TO 'repl'@'172.17.%.%' IDENTIFIED BY '123456';
角色创建好了以后,要在主服务器的配置文件上加上log-bin配置,开启Binary Log功能,否则无法实现replication复制,配置文件的路径为/usr/my.cnf,不同机子或系统可能路径不一样,可以用find / -name my.cnf查找一下,找到后修改加入如下内容:
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
Server-id主服务器配为1,从服务器配置时要注意不能与主服务器冲突了,不然到时候会出现莫民其妙的问题,因为同步的时候会会根据server-id做判断,如果server-id一样就不进行同步了,my.cnf的其它各项配置的介绍可以参考:http://database.51cto.com/art/201108/285365.htm
需要注意的是:每次修改配置文件需要重启mysql服务才能生效:
service mysql restart;//重启mysql服务
同步之前我们可以先将原主库里面的数据备份到从库上,可以使用mysqldump,语法如下:
mysqldump -uroot -p --lock-tables --events --triggers --routines --flush-logs --master-data=2 --databases authority_test rep_test_db replication > db.sql
--databases后面跟的authority_test等为数据库,将它们的快照保存到db.sql文件中。
将db.sql文件移到从服务器上后,可以用:mysql -uroot -p < db.sql 将主库的数据内容备份到从库。
2、配置从服务器
1) 找到从服务器的配置文件my.cnf,加入如下配置:
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
2) 重启从服务器mysql服务:service mysql restart。
3) 登录从服务器数据库,要连接上Master,使用如下sql:
change master to master_host='172.17.22.188', master_port=3306, master_user='repl',master_password='123456', master_log_file='master-bin.000001', master_log_pos=0;
4) 在mysq里使用sql语法:start slave。启动slave
3、主从服务器状态查看及问题处理
可以使用show slave status\G,查看slave的运行状态:
在 slave status 的状态中,有两项:Slave_IO_Running 和 Slave_SQL_Running, 必须都为 Yes 才说明正常运行了。
如果是slave_io_running no了,那么就我个人看有三种情况,一个是网络有问题,连接不上,第二个是有可能my.cnf有问题.
一旦io为no了先看err日志,看看有什么错,很可能是网络,也有可能是包太大收不了,这个时候从机上改max_allowed_packet这个参数。
Slave_SQL_Running 为 No 时:
解决办法一
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的.
一般是事务回滚造成的:
解决办法:
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
推荐使用该办法.
set GLOBAL SQL_SLAVE_SKIP_COUNTER=N,用来跳过备机的一条或N条出错的复制语句。
在show slave status 中会有出错的语句。
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1 一次后会发现跳过了一条语句。
如果设置完后发现还有错误语句。要多执行几次。
解决办法二
首先停掉Slave服务:stop slave。
到主服务器上查看主机状态:
记下File和Position对应的值
登录master
使用sql语法:show master status查看master运行状态:
然后到slave服务器上执行手动同步:
change master to master_host = '172.17.22.188',master_port = 3306,master_user = 'repl',master_password='123456',master_log_file='master-bin.000007',master_log_pos=120;
重新定位日志文件和日志位置。
然后: start slave,启动slave。
问题都处理完了以后可以尝试在master上进行各种增删改操作,可以发现,slave数据库上会同步更新,与master保持一致。