在一家小公司做Java程序员,除了编代码,做的事情也很杂,这次有机会接触到MySQL数据的主从备份,之前部门老大有做过一次主从,也算是有一份check list照做,即使这样,中间还是出了很多问题,经过几天的折腾,今天算是把问题解决了,因此决定写一篇博客记录一下,方便以后遇上类似的问题的时候查看,也希望能给遇到相同问题的人带来帮助!
前置条件:两台DB服务器,MySQL版本都是5.5.29,一个数据库在用,一个用来备份。
目标:将两个db做成主从备份,实现自动备份
步骤:1,停掉使用数据库的程序,以sql的形式备份数据库并将数据同步到备份数据库。
2,编辑主数据库的/etc/my.cnf文件,在mysqld部分添加如下代码
server-id = 1 #用来标识主数据库,如果有多个数据库做主从,这个数字要唯一
log_bin = /var/log/mysql/mysql-bin.log #确保此文件可写
read-only =0 #主机,读写都可以
binlog-do-db =test #需要备份数据,多个写多行
binlog-ignore-db=mysql #不需要备份的数据库,多个写多行
3,修改了my.cnf文件,重启主数据库,service mysql restart
4,创建备份用的数据库用户,并给从机授权,这里直接授权并刷新数据库权限,MySQL会自动先创建 用户
GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’192.168.1.101‘ IDENTIFIED BY ‘123456’;
FLUSH PRIVILEGES;
5,修改从机my.cnf文件,,在mysqld部分添加如下代码并重启MySQL
server-id = 2 log_bin = /var/log/mysql/mysql-bin.log replicate-do-db =test #只复制某个库 replicate-ignore-db=mysql #不复制某个库
6,在从机上设置主机的信息,登录mysql,执行下面的sql语句
- mysql>stop slave;
- mysql>CHANGE MASTER TO MASTER_HOST=’192.168.1.100’, MASTER_USER=’slave’, MASTER_PASSWORD=’123456‘,
- MASTER_LOG_FILE=’mysql-bin.0xx‘, MASTER_LOG_POS=33421;
- mysql>start slave;
7,上面代码中的Master_host为主机的地址,master_user和pwd分别是主机上创建的用于备份的slave用户和对应的密码,master_log_file和log_pos非常重要,设置错了就很容易出现1236的错误
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
8,因此为了避免出错,在执行步骤6之前,先登录主机MySQL,执行下面的sql语句查看主机的log file和log pos,然后在步骤6执行sql语句的时候将对应的信息写对
show master status;
9,上面的步骤完成之后,主从就算是配置好了,在从机上可以使用show slave status sql命令查看slave的状态,如果slave_io_running和slave_sql_running都显示yes表示成功了!可以在主数据库上执行更新和插入的操作,然后到从机数据库中验证!
ps:最初是5.5.29版本,使用了上面的方法配置,没能成功,网上也找了很多原因,都以失败告终,今天将数据库版本替换成5.6.10之后使用上面的方法才配置成功的。先将数据备份好 ,rpm -qa|grep -i mysql查看已经安装的mysql,然后使用rpm -e ***mysql的方法卸载mysql,清空/var/lib/mysql下的binlog和错误日志文件,清空后注意要编辑mysql-bin.index文件,将里面对应的信息删除,然后保存,安装时可以将原来配置好的my.cnf文件移走,等安装成功后再覆盖回来。