mysql 表数据备份和恢复_mysql 数据备份与恢复

MySQL数据备份与恢复

#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。

#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

#3. 导出表： 使用客户端工具将表导入到文本文件中。

一、使用mysqldump实现逻辑备份

#语法：

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例：

[root@mysql mysqlbakcup]# pwd/root/mysqlbakcup

授权

mysql> grant all on *.* to 'root'@'localhost' identified by '123';

Query OK,0 rows affected (0.15 sec)

#单库备份备份数据库导出一个数据库的结构以及数据

mysqldump -uroot -p123 db1 > db1.sql

mysqldump -uroot -p123 "db1" > /root/mysqlbakcup/db1.sql

[root@mysql mysqlbakcup]# ls

db1.sql

2. 备份数据表

导出一个数据库和数据表的结构以及数据

mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

[root@mysql mysqlbakcup]# mysqldump -uroot -p123 db1 auth_group auth_user > /root/mysqlbakcup/db1-auth_group-auth_user.sql

[root@mysql mysqlbakcup]# ls

db1-auth_group-auth_user.sql db1.sql

#多库备份

mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

[root@mysql mysqlbakcup]# mysqldump -uroot -p123 --databases db1 db2 db3 >/root/mysqlbakcup/db1_db2_db3.sql

[root@mysql mysqlbakcup]# lsdb1-auth_group-auth_user.sql db1_db2_db3.sql db1.sql

#备份所有库

mysqldump -uroot -p123 --all-databases > all.sql

[root@mysql mysqlbakcup]# mysqldump -uroot -p123 --all-databases > /root/mysqlbakcup/all.sql

[root@mysql mysqlbakcup]# lsall.sql

二、恢复逻辑备份

#方法一：

[root@mysql mysqlbakcup]# mysql -uroot -p123 < /backup/all.sql

mysql> drop databasedb3;

Query OK,1 row affected (0.56sec)

# 先创建数据库db3

mysql> create database db3 charset=utf8;

Query OK,1 row affected (0.00sec)[root@mysql mysqlbakcup]# mysql -uroot -p123 db3 < /root/mysqlbakcup/db3.sql

mysql> usedb3;

Readingtable information for completion of table and columnnames

You can turnoff this feature to get a quicker startup with -ADatabasechanged

mysql>show tables;+---------------+

| Tables_in_db3 |

+---------------+

| employee |

+---------------+

1 row in set (0.00 sec)

使用数据表来恢复

[root@mysql mysqlbakcup]# mysqldump -uroot -p123 db3 employee> /root/mysqlbakcup/employee.sql[root@mysql mysqlbakcup]# lsall.sql db3.sql employee.sql

准备

[root@mysql mysqlbakcup]# mysql -uroot -p123

mysql> usedb3;

Readingtable information for completion of table and columnnames

You can turnoff this feature to get a quicker startup with -ADatabasechangedmysql>mysql>show tables;+---------------+

| Tables_in_db3 |

+---------------+

| employee |

+---------------+

1 row in set (0.01sec)

mysql> drop tableemployee ;

Query OK,0 rows affected (0.07sec)

mysql>show tables;

Emptyset (0.00 sec)

恢复

[root@mysql mysqlbakcup]# mysql -uroot -p123 db3 < /root/mysqlbakcup/employee.sql

mysql> usedb3;

Readingtable information for completion of table and columnnames

You can turnoff this feature to get a quicker startup with -ADatabasechanged

mysql>show tables;+---------------+

| Tables_in_db3 |

+---------------+

| employee |

+---------------+

1 row in set (0.03 sec)

-P 指定端口

-h 指定服务器ip

--no-data, -d不导出任何数据，只导出数据库表结构。

mysqldump -uroot -p --host=localhost --all-databases --no-data

1.①导出一个库结构

mysqldump -d dbname -u root -p > xxx.sql

②导出多个库结构

mysqldump -d -B dbname1 dbname2 -u root -p > xxx.sql

-B参数备份和恢复(建议使用)

(1)备份操作

a、备份

mysqldump-uroot -p'123456' -B mytest > /mnt/mytest_bak_B.sql

说明：加了-B参数后，备份文件中多的Create database和use mytest的命令

加-B参数的好处：

加上-B参数后，导出的数据文件中已存在创建库和使用库的语句，不需要手动在原库是创建库的操作，在恢复过程中不需要手动建库，可以直接还原恢复。

(2)恢复操作

a、删除mytest库

mysql-uroot -p'123456' -e "drop database mytest;"b、恢复数据

(1)使用不带参数的导出文件导入(导入时不指定要恢复的数据库)，报错

mysql-uroot - p'123456' < /mnt/mytest_bak.sql

ERROR1046 (3D000) at line 22: No database selected

(2)使用带-B参数的导出文件导入(导入时也不指定要恢复的数据库)，成功

mysql-uroot -p'123456' < /mnt/mytest_bak_B.sql

c、查看数据

mysql-uroot -p'123456' -e "select * from mytest.student;"

指定压缩命令来压缩备份文件

(1)备份

mysqldump-uroot -p'123456' -B mytest | gzip > /mnt/mytest_bak_.sql.gz

说明：

mysqldump导出的文件是文本文件，压缩效率很高

备份多个数据库

(1)说明

通过-B参数指定相关数据库，每个数据库名之前用空格分格。当使用-B参数后，将所有数据库全部列全，则此时等同于-A参数。

(2)备份

mysqldump-uroot -p'123456' -B mytest wiki | gzip > /mnt/mytestAndWiki_bak.sql.gz

--quick, -q：在备份数据量比较大的表时有用。会将数据读入内存，在输出完成之前会存在内存缓冲区。，默认为打开状态，使用--skip-quick取消该选项。

mysqldump -uroot -p --host=localhost --all-databases

mysqldump-uroot -p --host=localhost --all-databases --skip-quick

--events，-E：使用事件调度器完成备份，但是需要数据库相关权限。(导出事件)

mysqldump -uroot -p --all-databases --events

--routines, -R

导出存储过程以及自定义函数

mysqldump -uroot -p --host=localhost --all-databases --routines

包括存储程序(过程和函数)的倾倒数据库输出。

这个选项需要mysql.proc表的SELECT权限。使用,例程生成的输出包含创建过程和创建函数语句创建例程。然而,这些语句不包括属性,如例程创建和修改时间戳,所以当例程.重新加载,创建时间戳等于重新加载时间。如果你需要创建例程和原来的时间戳属性,不使用,例程。相反,转储和重载mysql的内容。proc直接表,使用MySQL账户有合适的MySQL数据库特权。

导出包括存储程序(过程和函数)的数据。使用这个选项需要mysql.proc表的SELECT权限。 导出的数据将包含 CREATE PROCEDURE and CREATE FUNCTION 语句，该选项也不会导出相应的时间戳。如果要包含时间戳，使用mysql.proc 表的内容

--triggers：会在导出每个表时都导出触发器，不过需要具有TIGGER的权限，此选项默认启用,用--skip-triggers禁用它。(导出触发器)

mysqldump -uroot -h'192.168.1.55' -P3306 -p123 -d --triggers -q -E -R backstage > backstage.sql

方法二：

进入mysql，进入要恢复的数据库， 对该数据库导入表 source命令

mysql> drop databasedb3;

Query OK,1 row affected (0.56sec)

# 先创建数据库db3

mysql> create database db3 charset=utf8;

Query OK,1 row affected (0.00sec)

#进入数据库，使用source命令导入 恢复usedb3；

mysql> source /root/mysqlbakcup/db3.sql

Query OK,0 rows affected (0.03sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

实现自动化备份

#!/bin/bash

mysql_backup_dir=/root/mysqlbakcup

mysql_backup_file=`date +%Y-%m-%d`_all.sql

user=root

pass=123

if [ ! -d ${mysql_backup_dir} ];then

mkdir -p ${mysql_backup_dir}fi# 备份

mysqldump-u${user} -p${pass} --all-databases > ${mysql_backup_dir}/${mysql_backup_file}

# 只保留最近一周数据备份

cd $mysql_backup_dirfind . -mtime +7 -exec rm -f {} \;

授权

[root@mysql mysqlbakcup]# lsmysql_backup.sh[root@mysql mysqlbakcup]#chmod +x mysql_backup.sh

测试一下脚本

[root@mysql mysqlbakcup]# sh -x mysql_backup.sh

+ mysql_backup_dir=/root/mysqlbakcup++ date +%Y-%m-%d+ mysql_backup_file=2019-03-13_all.sql+ user=root+ pass=123

+ '[' '!' -d /root/mysqlbakcup ']'

+ mysqldump -uroot -p123 --all-databases

Warning: Using a password on the command line interface can be insecure.+ cd /root/mysqlbakcup+ find . -mtime +7 -exec rm -f '{}' ';'[root@mysql mysqlbakcup]#ls

配置crond

[root@mysql mysqlbakcup]# crontab -e

[root@mysql mysqlbakcup]# crontab -l*/2 * * * * sh /root/mysqlbakcup/mysql_backup.sh