MySQL复制设置及相关维护操作

主从复制配置步骤

1. 主库服务器配置

主服务器MySQ配置参数,参数主要依据《高性能MySQL 第三版》;从服务器配置与此稍有不同,主要是增加几个参数,如后节。

#replication
server-id=100
log-bin=mysql-bin
expire_logs_days=30
sync_binlog=1
relay_log=mysql-relay-bin
log_slave_updates=1

#replication safe for innodb engine
innodb_flush_logs_at_commit
innodb_support_xa=1

服务器id号;设置二进制日志文件名(隐含启用);binlog文件过期天数(最大允许99,设为0则不过期);每次写入binlog后同步到磁盘。接下几个参数实际是slave上使用的(参看文slave的配置所述),写在这里可以让主从配置参数都一致,方便实际使用时少出错,也方便多级式的复制拓扑。

2. 添加复制用户

只需要赋两个全局的权限: REPLICATION SLAVE, REPLICATION CLIENT

CREATE USER 'repl'@'192.168.10.132' IDENTIFIED BY '***';
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'repl'@'192.168.10.132' IDENTIFIED BY '***';

3. 备份主库,备份中加入binlog及位置

全面备份主库,备份前需要已经启用二进制日志。

mysqldump -hlocalhost --opt --master-data=1 --all-databases --max_allowed_packet=8M --net_buffer_length=128K -uroot -pyour-password >all.sql

为了获得一致性的备份,考虑加入参数--single-transaction 或 --lock-all-tables,分别针对事务型引擎及非事务型引擎,但它们不能同时使用。

注意其中--master-data=1参数,它将binlog及位置信息生成到备份文件中,大概如下一行

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=8747725;

4.iptables放行备库连入

允许从服务器ip到主服务器3306端口的连接

iptables -I INPUT 7 -s 192.168.10.132/32 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

5. 配置从服务器

server-id=201
log-bin=mysql-bin
expire_logs_days=30
sync_binlog=1
relay_log=mysql-relay-bin
log_slave_updates=1     # actually only for B in A->B->C 
read_only=1

前四个与主配置一样;中继日志写入binlog (默认是不写的,这实在有点出乎意料);中继日志文件名;数据只读(root用户不在此限)

6. 还原到从库服务器上

SOURCE /path/to/bakup.sql;

6. 配置从库,连接到主库

事实上下面的log_file,log_pos可以不用再指定,因为当初的备份里已经有该信息。当然这里再指定一次也没负作用。

change master to master_host='192.168.10.100', 
master_user='repl',
master_password='repl-password', 
master_log_file='mysql-bin.000004', 
master_log_pos=8747725;

7. 启动slave

start slave

顺利的话即完成。

如报 Failed to open the relay log 的错误,先停止slave的mysql服务,删除 /var/lib/mysql/ 下的中继日志相关文件,主要是  *-relay-bin.* 及 relay-log.info,再启动重新start slave

配置中的一些注意事项

  • 关于binlog_format 参数。在mysql 5.5(win32)版下,该参数默认值为STATEMENT。于是,至少有以下问题insert into tablename select ... limit n这样的语句,执行时会报warning错误,原因是limit结果在一致性上是非安全的。所以可以加入 binlog_format=MIXED 的配置参数。
  • 至少在 Mariadb 5.5 上,RESET SLAVE 似乎并不起效,而要写成RESET SLAVE ALL; 才行。
  • 如果是同时是主与从的服务器上,必须加入参数 log_slave_updates ,因为,MySQL默认不把中继日志写入二进制日志里。这好像有点出乎意料。

日常管理维护相关

查看二进制日志文件 SHOW BINARY LOGS;SHOW MASTER LOGS; ,二者等价。

刷新二进制日志。刷新并终结当前的二进制日志文件的写入,并开启一个新的二进制日志文件  FLUSH BINARY LOGS;   如果要刷新中继日志,则 FLUSH RELAY LOGS;(中继日志,一般不需要理它,事实上它主要由 chagne master ... 时会自动重设,一般不要手工指定)

自动定时清理。通过参数 expire_logs_days 指定过期天数,过期自动清理;具体天数据按实际情况定(空间与同步延迟等)。该参数可运行时设定。

expire_logs_days = 10 

手工清理。root登录mysql客户端,执行purge命令。

> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'

其中 log_name是show master logs; 显示的日志文件名,如 mysql-bin.000001. 应用举例:

> PURGE MASTER LOGS TO 'mysql-bin.000003';  //清除mysql-bin.000003(含)之前的日志
> PURGE MASTER LOGS BEFORE '2016-11-05 10:00:00';   //清除2016-11-05 10:00:00前的日志
> PURGE MASTER LOGS BEFORE DATE_SUB(NOW(),INTERVAL 3 DAY);
  //清除3天前日志,使用BEFORE函数计算日期,变量的date自变量还可以为'YYYY-MM-DD hh:mm:ss'格式。详查手册

清理二进制日志的影响。如果当前服务器有一个活跃的从属服务器,该从服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是会失败,并伴随一个错误。不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。(参考

正常运行若干时间后的复制失败后的维护

如果在运行中,出现了异常,复制失败了(通常时进程异常结束、断电等,slave状态里有报执行错误的语句)。这时或许需要重配置slave才可以解决问题

stop slave;
SOURCE /path/to/bakup.sql;
RESET SLAVE ALL;
change master to ...
show slave status; #检查状态,确认主库及连接账号等
START SLAVE;

end

linux下日期时间自动同步设置(rdate,ntpdate两种方法)

linux下同步时间,至少有两种方法:

rdate,ntpdate两种。centos最小化安装默认不安装,先确认已经安装过,否则先安装。

其中rdate本身是用来获取远程时间服务器上时间用的,带上 -s 参数,就可以将获取到的时间应用到本地系统。

NAME
       rdate - get the time via the network

SYNOPSIS
       rdate [-p] [-s] [-u] [-l] [-t sec] [host...]

DESCRIPTION
       rdate  connects  to  an  RFC  868 time server over a TCP/IP network, printing the returned time
       and/or setting the system clock.

   OPTIONS
       -p     Print the time returned by the remote machine.

       -s     Set the system time to the returned time.

       -u     Use UDP instead of TCP as the transport.

       -l     Use syslog to output errors (cron.warning) and output (cron.info).

       -t     Set timeout in seconds for every attempt to retreive date.

rdate -s time.nist.gov

ntpdate 本身就是用来同步时间的工具。

示例 :ntpdate ntp.sjtu.edu.cn

NAME
       ntpdate - set the date and time via NTP

       Disclaimer:  The functionality of this program is now available in the ntpd program. See the -q com-
       mand line option in the ntpd - Network Time Protocol (NTP) daemon page. After a suitable  period  of
       mourning, the ntpdate program is to be retired from this distribution

SYNOPSIS
       ntpdate [ -46bBdqsuv ] [ -a key ] [ -e authdelay ] [ -k keyfile ] [ -o version ] [ -p samples ] [ -t
       timeout ] [ -U user_name ] server [ ... ]

DESCRIPTION

[略,具体参看linux man手册]

日期时间服务器

同步时间,就必须有时间服务器。通常情况下,我们完全没必要自己搭建,使用公共的时间服务就可以。

参看 国内常用NTP服务器地址及IP  http://www.douban.com/note/171309770/

其中国家授时中心的似乎不能用,推荐使用上海交大ntp服务 ntp.sjtu.edu.cn 或美国授时中心 time.nist.gov

上代码,拷了就可以用

rdate -s time.nist.gov

ntpdate ntp.sjtu.edu.cn

可以加到crontab里定时执行。

通过网络ntp自动同步linux主机的日期时间

如果只需要同步一次,那么执行如下命令即可

rdate -s time.nist.gov

其中time.nist.gov 是一个时间服务器,当然你也可以使用另外的时间服务器。

如果是多台服务器的时间需要保持完全同步,那么最好还是给这些服务器配置统一的自动同步任务,以免因为各种原因,造成它们的时间有所偏差。方法如下

使用 crontab 让linux自动同步时间
vi /etc/crontab
加上一句:

00 0 1 * * root rdate -s time.nist.gov

time.nist.gov 是一个时间服务器.