开源中文网

您的位置: 首页 > 数据库应用 > MySQL > 正文

MYSQL:主从服务器同步例子(修改版)

来源:  作者:

一般使用MySQL的时候,如果数据量不大,我们都使用一台MySQL服务器,备份的时候使用mysqldump工具就可以了,但是随着业务不断发展,问题出现了:

首先:数据量往往直线上升,单独一台数据库服务器开始出现性能的瓶颈,数据访问越来越慢。

其次:备份也变得困难了,因为mysqldump是导出一份文本文件,而数据量特别大的时候,这样的备份往往需要很长时间,可能有人会说,我们可以直接通过拷贝数据文件来备份数据库,这样很方便,快捷,不错,这样是比mysqldump方便快捷,但是,直接拷贝数据文件备份的方式要求我们必须先关闭 mysql服务,然后再拷贝数据文件,否则,你拷贝的文件很可能是坏的。而实际运行的mysql服务往往要求在任何时候都不可以停止服务,所以这样的备份方式在此情况下不可行。

如果你遇到了类似上面的问题,你就可以使用建立MySQL主从服务器的方式来解决,下面先来看看主从服务器的设置:

前提:MySQL主从服务器最好使用相同的软件版本,以避免不不可预期的故障。

首先设置MySQL主服务器:

在主服务器上为从服务器建立一个用户:
mysql>grant all on *.* to root@'%' identified by '1234';  添加权限
mysql>flush privileges;   刷新权限
mysql> select host,user from user;  查看权限

编辑主服务器的配置文件:/etc/my.cnf
[mysqld]
#sync data
server-id=1
log-bin

sql-bin-update-same
binlog-do-db=DB1
binlog-do-db=DB2     如果备份多个数据库,重复设置这个选项即可


编辑从服务器的配置文件:/etc/my.cnf
   [mysqld]
      #sync data

      server-id=2
      master-host=192.168.14.5

      master-user=root
      master-password=********
      master-port=3306
      master-connect-retry=60
      replicate-do-db=DB1
      replicate-do-db=DB2
      log-slave-updates

记得先手动同步一下主从服务器中要备份的数据库,然后重启主,从服务器。

要验证主从设置是否已经成功,可以登录从服务器输入如下命令:

mysql> show slave status\G

会得到类似下面的列表:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果这两个选项不全是Yes,那就说明你前面某个步骤配置错了。

如果你的设置是正确的,尝试在主服务器上插入若干条记录,然后你再转到从服务器,会发现相应的新记录已经自动同步过来了。

如果你的主从服务器已经配置好了,那么你在应用程序中,只要保证所有的insert/delete/update操作是在主服务器上进行的,那么相应的数据变化会自动同步到从服务器上,这样,我们就可以把select操作分担到多台从数据库上,从而降低服务器的载荷。

如果你想使用复制数据文件的方式来备份数据库,只要在从服务器上的mysql命令行先键入slave stop;然后复制数据库文件,复制好了,再在mysql命令行键入slave start;启动从服务器,这样就即备份了数据有保证了数据完整性,而且整个过程中主服务器的mysql无需停止。
 

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

提示:如果修改了主服务器的配置,记得删除从服务器上数据库目录下的 master.info文件。否则从服务器使用的还是老配置,可能会导致错误。

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

注意:关于要复制多个数据库时,binlog-do-db和replicate- do-db选项的设置,网上很多人说是用半角逗号分隔,经过测试,这样的说法是错误的,MySQL官方文档也明确指出,如果要备份多个数据库,只要重复设置相应选项就可以了。

比如:

binlog-do-db=a
binlog-do-db=b

replicate-do-db=a
replicate-do-db=b

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

补充:
在从服务器上使用show slave status\G
Slave_IO_Running, 为No,
则说明IO_THREAD没有启动,请执行start slave io_thread
Slave_SQL_Running 为No
则复制出错,查看Last_error字段排除错误后执行start slave sql_thread

查看Slave_IO_State字段空 //复制没有启动
Connecting to master//没有连接上master
Waiting for master to send event//已经连上

补充:可以使用LOAD DATA FROM MASTER语句来建立slave。但有约束条件:
数据表要全部是MyISAM表,必须有SUPER权限,master的复制用户必须具备 RELOAD和SUPER权限。
在master端执行RESET MASTER清除已有的日志变更,
此时slave端会因为找不到 master日志无法启动IO_THREAD,请清空data目录下
relay-log.info,hosname-relay-bin*等文件重新启动mysql
中继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay- bin.index。可用从服务器的--
relay-log和--relay-log-index选项修改。在从服务器中还有一个relay- log.info中继信息文件,可用
--relay-log-info-file启动选项修改文件名。
双机互备则是两个mysql同时配置为master及slave

主服务器上的相关命令:
show master status
show slave hosts
show logs
show binlog events
purge logs to 'log_name'
purge logs before 'date'
reset master(老版本flush master)
set sql_log_bin=

从服务器上的相关命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志

Tags:MYSQL: 主从 服务器
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明