mysql数据备份恢复

mysql数据备份恢复

---

1 最常用的是用mysqldump做数据备份

mysqldump备份操作

在数据库机器上操作，将单个数据库完整备份到本地.sql文件

$ mysqldump -u root -p Password database > backup.sql

远程备份数据库到本地.sql文件

$ mysqldump -h 127.0.0.1 -P 3306 -u root -p Password database > backup.sql

只备份表结构--no-data

$ mysqldump -u root -p Password --no-data database > backup.sql

备份多个表--databases

$ mysqldump -u root -p Password --databases database1 database2 database3 > backup.sql

备份所有数据库--all-databases

$ mysqldump -u root -p Password --all-databases > backup.sql

下面介绍备份的部分参数

--default-character-set=utf8 设置默认字符集，默认值为utf8

--tables 指定需要导出的表名，与–databases用法差不多

--all-tablespaces 导出全部表空间

--no-tablespaces 不导出任何表空间信息

--set-gtid-purged=off 不使用GTID(全局事务ID),如果开启在导入其他库的时候可能重复

--where 增加条件，例如 –where=”user=’root’”

mysql数据恢复

利用mysqldump备份的文件来恢复数据库

$ mysql -uroo -p database < backup.sql

2 binlog数据备份和恢复

一般是误删除或者误修改用到的比较多

binlog备份

使用binlog首先要打开这个设置

查看是否开启binlog,ON代表已经开启,OFF代表关闭，默认关闭

mysql> show variables like 'log_bin'; 
+----------------------------------------+---------------------------------------+
| Variable_name                          | Value                                 |
+----------------------------------------+---------------------------------------+
| log_bin                                | OFF                                    |

如果没有开启需要修改my.cnf,然后重启mysql

$ vim /etc/my.cnf
[mysqld]
log_bin = /var/lib/mysql/logs/log-bin
$ systemctl restart mysqld.server

然后操作会记录在log_bin目录下

$ ls /var/lib/mysql/logs
log-bin.000001  log-bin.index

binlog恢复

查看log-bin文件，
$ mysqlbinlog log-bin.000001

按位置截取解析log-bin
$ mysqlbinlog log-bin.000001 --start-position=1 --stop-position=100 -r log-bin.sql

按时间截取解析log-bin
$ mysqlbinlog log-bin.000001 --start-datetime='2018-01-01 00:00:00' --stop-datetime='2018-01-02 00:00:00' -r log-bin.sql

恢复数据库,也可以按位置和时间来恢复
$ mysqlbinlog --database=database1 /usr/lib/mysql/logs/log-bin.000001|mysql -u root -p Password -v database1

$ mysqlbinlog --start-position=20 --stop-position=120 --database=database1 /usr/lib/mysql/logs/log-bin.000001|mysql -u root -p Password -v database1

$ mysqlbinlog --start-datetime='2018-01-01 00:00:00' --stop-datetime='2018-01-02 00:00:00' --database=database1 /usr/lib/mysql/logs/log-bin.000001|mysql -u root -p Password -v database1

3 直接利用data文件夹来迁移

找到my.cnf里面设置的datadir目录

$ cat /etc/my.cnf|grep datadir
datadir = /var/lib/mysql/data

还原的时候就将这个data文件夹拷贝过去

在新的mysql机器上将my.cnf里面的datadir改为data文件夹的地址,然后重启数据库

$ vim /etc/my.cnf
datadir = /var/lib/mysql/new_data
$ systemctl restart mysqld.server