MySQL--数据备份还原以及主从复制

时间:2022-05-03 10:23:15

作为数据库,为了数据安全,mysql当然提供了数据备份和数据还原的功能,而且还提供了主从复制的功能,也就是说能搭建数据库集群,以下记录下常用的操作步骤流程;

一. MYSQL的备份

备份相当简单,使用mysqldump工具就可以实现:

//将指定的数据库备份
mysqldump -uroot -h host -p123456 库名 > test.sql
//将指定的数据表备份
mysqldump -uroot -h host -p123456 库名 表名> test.sql
//将多个数据库
mysqldump -uroot -h host -p123456 -datebases 库名1 库名2> test.sql
//备份时锁定数据库并且自动flush binlog
mysqldump -uroot -h host -p123456 库名 -l -F> test.sql

当然,除了将数据还原为mysql脚本外,还可以直接备份MySQL存储在磁盘上的文件,但是通过这种方式转移数据时,可能会遇到因为MYSQL版本不同或存储殷勤不同,导致出现还原失败的现象,所以这种方式备份不推荐;


二. MYSQL的SQL文件还原

得到sql脚本后,就可以用于数据的恢复还原了:

//登录mysql-client后,使用source指令:
mysql> source sql文件路径
//或者使用mysql工具:
#mysql -uroot -p123456 库名 -v -f < sql文件路径 //v是显示信息 f遇到错误继续

三. MYSQL的BINLOG(二进制日志)数据恢复

使用上述的方式进行数据备份还原可以进行阶段性的数据恢复,但是如果遇到突发情况(如不小心删除表数据),在上一次备份和当前时间点的数据就会丢失。

因此解决的方法也很简单,就是将mysql数据修改操作(增删改)的每个步骤用日志记录下来,以便以后有迹可循;

mysql常用的日志方式:二进制日志(binlog)

1.开启binlog,在my.ini中,这样设置:

// log file storage path
log-bin [=path/[filename]]
// period of binlog(before the period the log will be deleted),disabled if 0
expire_logs_days = 10
// the max size of every file of binlog, cant set 4096K<x<1G(default)
max_binlog_size = 100M

2.重启MySQL使得配置生效,生效后每次进行增删查改就会写入binlog

3.查看binlog的内容

#mysqlbinlog --no-defaults --start-position="597" --stop-positon="746" --start-date="2012-01-04 10:00:25" --stop-date="2012-01-04 19:00:25" log文件 | more

4.运行指定binlog以还原数据

mysqlbinlog --no-defaults --stop-position="1017" mysql_bin.000001 | mysql -uroot -p 数据库名字

四.主从机复制

MySQL提供了主从机机制,允许主机进行读写操作,而从机负责读操作,这样子就能同时加强了数据的安全性以及访问的均衡性。

1.主机设置server-id

my.cnf 中[mysqld]写上:

server-id=1 //注意server-id是mysql集群中的唯一id

2.主机创建从机访问账户

GRANT REPLICATION SLAVE ON *.* to 'slave_mac'@'%' identified by '123456'

3.主机导出mysql脚本(见“一. MYSQL的备份”)
注意:导出时对mysql进行锁操作,然后再进行导出,同时查看bin-log的文件和position

4.从机导入主机的mysql脚本(见“二. MYSQL的SQL文件还原”)

5.从机设置server-id

server-id=2 //注意server-id是mysql集群中的唯一id

6.从机设置主机信息

#mysql
mysql>change master to
master_host='192.168.145.222', //主句地址
master_user='slave_mac',
master_password='123456',
master_log_file='mysql-bin.000004', //主机当前bin-log的文件
master_log_pos=308; //主机当前bin-log的文件position

5.从机开启slave模式

mysql>start slave;

6.从机查看slave状态

mysql>show slave status;

好了,已经开启了mysql的主从模式,当操作mysql的主数据库数据时,从数据库的数据也会跟着变化。
在实际应用中,从数据只允许读访问,这起到了备份数据的同时减轻了主服务器的读数据压力,也就起到了均衡访问和集群冗余的作用了。