MySql5.6在Linux服务器上设置数据库同步备份的方法
项目已经完成,后期就是维护之类的了.当然会包括数据库的维护. MySql有个功能Replication正好可以用于数据库同步备份,于是就准备这样弄了. 花了一点时间,终于弄出来了.记录备用: Linux数据库备份设置部分:(第一次设置) 1、数据库版本 两台服务器数据库版本
项目已经完成,后期就是维护之类的了.当然会包括数据库的维护.
MySql有个功能Replication正好可以用于数据库同步备份,于是就准备这样弄了.
花了一点时间,终于弄出来了.记录备用:
Linux数据库备份设置部分:(第一次设置)
1、数据库版本
两台服务器数据库版本最好都保持一致,避免因版本问题带来的不可预知的错误。
2、设置备份帐户名以及密码
数据库都安装好以后,在两台服务器都设置一个专门用于备份的帐号并授权(最好授予全部权限),两个帐号密码最还也设置为一样的。这里在主从数据库都添加了用于备份的帐户名,帐户名都为 backup,密码123123。
3、同步前数据拷贝
从主数据库把数据备份到备份数据库上,可以用navicat的Data Transfer,需要建立两个连接,分别在主、从数据库上,在备份数据库上建立新数据库,与需要备份的主数据库同名,注意编码规则什么的保持一致,然后, 从主数据连接这里选中主数据库,用数据功能把数据传输到备份数据库上,保持两个数据库的数据一致性。
也可以使用其他方法,保证备份前主从数据库的数据一致性就行。
4、主数据库设置
修改主数据库配置文件,在Linux系统中,一般是在 /etc/my.cnf
在[mysqld]下面添加
server-id=1 #这个ID必须是唯一的,主数据库在前
log-bin=mysql-bin #记录日志
binlog-do-db=productInfo #记录日志的数据库名,就是要备份的数据库
修改完主数据库的配置后,保存,退出,重启主数据库服务。
然后在主数据库执行一句sql命令(数据库里有函数用来完成特定功能)
set globallog_bin_trust_function_creators=1;(MySql函数开启日志的方法,默认是不开启的,主从备份需要开启二进制log,而且mysql官方不建议这个写在配置文件里,所以用sql来执行)
5、从数据库设置
5.1 修改从数据库配置文件
在Linux系统中,一般是在 /etc/my.cnf
在[mysqld]下面添加
server-id=2 #从数据库ID
relay-log=mysqld-relay-bin #从数据库的日志文件
replicate-do-db=productInfo #需要备份的数据库名
修改完配置文件后,保存,退出,重启从数据库服务。
5.2 从数据库sql设置命令
接下来的操作需要用sql命令来完成。
用MySql的root用户进入,执行sql命令:
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.1.200',MASTER_USER='backup', MASTER_PASSWORD='123123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=98;
start slave;
退出mysql状态,在linux操作状态下去查看刚才的slave启动是否成功。
在目录 /usr/local/mysql/data 下的*.err,用cat命令查看这个错误日志文件,根据刚才启动slave的时间,在最后几行,看是否有错误提示。
因为MASTER_LOG_POS=98 这个属性表示的是从数据库开始备份的起始位置(从主数据库日志读取)一般不会第一次就很准确,需要查看错误提示后修改。
错误日志类似下面这样:(供参考)
080922 10:29:01 [Note] Slave SQL threadinitialized, starting replication in log 'mysql-bin.000778' at position2874108, relay log './relay-bin.003287' position: 2874245
080922 10:29:01 [Note] Slave I/O thread:connected to master 'replication@192.168.0.10:3306', replication started in log'mysql-bin.000778' at position 2874108
080922 10:29:01 [ERROR] Error reading packetfrom server: Client requested master to start replication from impossibleposition ( server_errno=1236)
080922 10:29:01 [ERROR] Got fatal error1236: 'Client requested master to start replication from impossible position'from master when reading data from binary log
我们上面的起始位置是98,如果不对,按照错误提示的修改(最好主数据库没有人操作,这样主数据库日志记录位置不会变动)
修改方法:
mysql用root用户进入,执行sql命令:
stop slave;
CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=从错误日志上提示的pos;
start slave;
这样的步骤可能需要重复不止一次。
当查看*.err,最后记录的日志提示都是note以及wrning没有error时,表示从数据库与主数据库同步成功。
6、设置完成后检查
在主数据库上做一些 insert update delete 这样的操作,然后检查从数据库数据是否发生改变。
Linux数据库重启时的设置:
当主数据库需要重启时,需要在重启后做如下操作。
1、主数据库设置.
主数据库数据用navicat的data transfer功能备份到从数据库。
查看最新的日志记录文件,第一次是mysql-bin.000001,第二次是mysql-bin.000002,以此类推。
查看目录 /usr/local/mysql/data 下面,主数据库ip地址 192.168.1.200
了解了最新的日志记录文件后,记下来(修改从数据库slave备份设置需要)。
然后在主数据库执行一句sql命令
set global log_bin_trust_function_creators=1;(MySql函数开启日志的方法,默认是不开启的,主从备份需要开启二进制log,而且mysql官方不建议这个写在配置文件里,所以用sql来执行)
2、从数据库设置
mysql用root用户进入,执行sql命令:(假定主数据库最新的日志文件是mysql-bin.000005)
stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=98;
start slave;
然后参考初次设置从数据库slave的方法,查看 *.err,看是否启动同步成功,如果没有同步成功需要多重复几次,主要是根据错误日志的提示,修改MASTER_LOG_POS的值。
3、备注:
主从同步错误调整可参考http://www.linuxidc.com/Linux/2012-02/54729.htm