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