MySQL 备份与还原及主从复制

我就是我 2022-01-22 06:43 159阅读 0赞

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

## 一. MYSQL的备份 ##

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

1.  //将指定的数据库备份  
    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的主数据库数据时，从数据库的数据也会跟着变化。  
在实际应用中，从数据只允许读访问，这起到了备份数据的同时减轻了主服务器的读数据压力，也就起到了均衡访问和集群冗余的作用了。