关于Mysql主从数据库的设置[转]

Linux, MySQL, 安装配置 , , , , , , , ,

来源:http://caiying0504.javaeye.com/blog/176432

一般使用MySQL的时候,如果数据量不大,我们都使用一台MySQL服务器,备份的时候使用mysqldump工具就可以了,但是随着业务不断发展,问题出现了:
首先:数据量往往直线上升,单独一台数据库服务器开始出现性能的瓶颈,数据访问越来越慢。
其次:备份也变得困难了,因为mysqldump是导出一份文本文件,而数据量特别大的时候,这样的备份往往需要很长时间,可能有人会说,我们可以直接通过拷贝数据文件来备份数据库,这样很方便,快捷,不错,这样是比mysqldump方便快捷,但是,直接拷贝数据文件备份的方式要求我们必须先关闭mysql服务,然后再拷贝数据文件,否则,你拷贝的文件很可能是坏的。而实际运行的mysql服务往往要求在任何时候都不可以停止服务,所以这样的备份方式在此情况下不可行。
如果你遇到了类似上面的问题,你就可以使用建立MySQL主从服务器的方式来解决,下面先来看看主从服务器的设置:
前提:MySQL主从服务器最好使用相同的软件版本,以避免不不可预期的故障。


首先设置MySQL主服务器:
在主服务器上为从服务器建立一个用户:

grant replication slave on *.* to '用户名'@'主机' identified by '密码';

编辑主服务器的配置文件:/etc/my.cnf的mysqld处

server-id = 1
log-bin
binlog-do-db=需要备份的数据库名,如果备份多个数据库,重复设置这个选项即可
binlog-ignore-db=不需要备份的数据库苦命,如果备份多个数据库,重复设置这个选项即可

编辑从服务器的配置文件:/etc/my.cnf

server-id=2
master-host=主机
master-user=用户名
master-password=密码
master-port=端口
replicate-do-db=需要备份的数据库名,如果备份多个数据库,重复设置这个选项即可

记得先手动同步一下主从服务器中要备份的数据库,然后重启主,从服务器。
要验证主从设置是否已经成功,可以登录从服务器输入如下命令:

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

———————————————————————————–
补充:从服务器上my.cnf中的master-*的设置仅在第一次生效,后保存在master.info文件里。
———————————————————————————–
在从服务器上使用show slave status
Slave_IO_Running,为No,则说明IO_THREAD没有启动,请执行slave start [IO_THREAD]
Slave_SQL_Running为No则复制出错,查看Last_error字段排除错误后执行slave start [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 {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}

———————————————————————————–
从服务器上的相关命令:

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端已同步过的日志

———————————————————————————–
–read-only
该选项让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新。可以确保从服务器不接受来自客户的更新。

附加一些bin-log的方法
With your mysql client :

mysql -u root -p
then
mysql> flush logs;
will create a brand new mysql-bin.0000xy
mysql> purge binary logs to 'mysql-bin.0000xy';
will purge all your logs files before the last.

Leave a Reply