mysql 主从备份

概述:在两台虚拟机master和slave中安装相同版本的 mysql,在master中的mysql做为主库,slave中的mysql做为从库,对主库和从库配置,从库可以从主库的日志文件中读取SQL语句并执行,从库中的数据就和主库中一致,到达备份的目的;主从库除了保障了数据的安全性,还可以做读写分离,从库负责读请求,主库负责写请求

主库配置

修改主库配置文件

编辑/etc/my.cnf,加入以下配置:

1
2
server-id = 1
log_bin = master_log

server-id 是mysql服务的标识,master的标识必须小于slave的标识

log_bin 是日志文件名,记录主库执行的除了查询以外的SQL指令,slave要读取的文件,mysql会自动增加文件后缀名和文件类型

重启mysql服务

service mysqld restart

为从库创建访问用户

进入mysql DBMS: mysql -u username -p password,执行:

1
2
grant all privileges on *.* to 'username'@'ip' identified by 'password' with grant option;
flush privileges;

其中,ip 是从库所在服务器的ip地址(注:经测试,不能是域名),执行完,就向 mysql 库的 user 表中添加了一个用户,对应从库的 ip 和使用的用户

从库配置

修改 slave 配置文件

vi /etc/my.cnf,添加server-id = 2,这里要大于主库的server-id即可,不要和其他从库重复

同上,重启 mysql 服务

查看主库日志文件

在主库DBMS中执行:show master status;,查看日志文件名

master status

配置主库信息

进入DBMS:mysql -uroot -proot ,先停止slave功能:stop slave;,将主库的ip或域名,主库提供的对应从库的用户名密码,日志文件(上图中的文件名)输入到主库信息中:

1
2
change master to master_host='master', master_user='slave',
master_passowrd='slave', master_log_file='master_log.000002';

执行完,开启slave:start slave;

查看slave情况

执行show slave status \G; 查看slave连接情况:

slave status

slave status

图二中没有错误就是连接成功,且没有执行错误的sql语句

验证

在主库中创建数据库,数据表,插入记录,在从库中查询结果

遇到的问题

从库执行日志记录出错

在从库读取主库日志文件时,我遇到了 Last_IO_Error: Got fatal error 1236 from master 在执行主库日志记录过程中出现了执行错误,应该是主从库初始状态不一致导致的,我的主库中原来是有自己创建的库的,如果日志中有对这个库的操作,那么从库执行这些代码都会报错,虽然我删除了自己创建的库让主从库的库一样,并在my.cnf中修改日志文件名,企图在新的日志文件中重新记录,但是从库执行依旧报错

解决方法

首先在从库执行stop slave;命令,将同步停止,

然后在主库中执行flush logs;命令,刷新日志,

在主库中执行show master status;来查看当前日志的位置

在从库中执行change master to master_log_file='master_log.000002', master_log_pos=964;

在从库中开启slave: start slave 启动同步,查看 slave 状态

此外,还有主从库版本不同带来的问题,我换成相同版本问题消失,但是不同版本的解决方案我没有测试

您的支持鼓励我继续创作!