mysql数据备份和恢复

mysql数据备份和恢复

今天记录一个知识点，关于mysql数据库、数据库表的一个备份和恢复操作

有时候在工作中会遇到网站迁移、或者服务器更换，需要对数据进行备份还有后续的恢复。这时候就需要用到mysql的备份恢复命令

mysqldump

❗️此命令既可以备份数据库、也可以备份表❗️

话不多说，我们开始演示

任务一 : 备份class数据库，将备份文件保存在 /tmp/mysqlbak/目录下，并起名为 class_db.sql

准备一个简单的class数据库以及一张student表

CREATE DATABASE `class` CHARACTER SET utf8mb4;
USE class;

CREATE TABLE student(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(15) NOT NULL,
   age INT NOT NULL,
   email VARCHAR(20) NOT NULL
);

备份 class 数据库命令如下

这里的备份建议使用非root用户备份，这里为了方便，用root进行演示。

实际工作中，数据库应该有非root用户，给该用户赋予某个数据库或者数据库表的权限。而并非给予root权限。

具有安全风险❗️❗️❗️

[root@centos7 ~]# mysqldump -uroot -p123456 class > /tmp/mysqlbak/class_db.sql
# 查看备份情况，可以看到，备份成功。
[root@centos7 ~]# ls /tmp/mysqlbak
class_db.sql

进行数据库恢复操作，将该数据库恢复为class数据库，或者其他数据库均可。

命令如下

# 将class_db.sql 数据库恢复成class数据库
[root@centos7 ~]# mysql -uroot -p123456 class < /tmp/mysqlbak/class_db.sql
[root@centos7 ~]# mysql -uroot -p
mysql> show databases;
class

# 将class_db.sql数据库恢复成 classTest数据库
[root@centos7 ~]# mysql -uroot -p123456 classTest < /tmp/mysqlbak/class_db.sql
[root@centos7 ~]# mysql -uroot -p
mysql> show databases;
classTest

任务二 : 备份mysql数据库下的user表，将备份文件保存在 /tmp/mysqlbak/目录下，并起名为 user_tb.sql

备份mysql 数据库下的user表的 命令如下

[root@centos7 ~]# mysqldump -uroot -p123456 mysql user > /tmp/mysqlbak/user_tb.sql

将user表恢复到 class数据库中

[root@centos7 ~]# mysql -uroot -p123456 class < /tmp/mysqlbak/user_tb.sql
[root@centos7 ~]# mysql -uroot -p
mysql> use class;
show tables;

当MySQL中有多个库需要备份，一个一个去备份很麻烦，使用 -A选项可以全部备份数据库中的所有库

命令如下 -A

[root@centos7 ~]# mysqldump -uroot -p -A > /tmp/mysqlbak/all_db.sql
Enter password:

有时候 只想备份数据库中的表结构，不需要表数据，那么可以使用-d 选项实现

命令如下：

[root@centos7 ~]# mysqldump -uroot -p123456 -d class > /tmp/mysqlbak/tb_desc.sql 

这里补充一个知识点，如何在mysql中创建用户，并赋予权限

mysql -uroot -p  # 登录mysql

# 创建用户 admin ，密码Admin
# 'admin'@'%' 是表示允许用户从哪个主机连线到mysql
% 表示任何主机
IP 地址指定IP
主机名:指定主机名也可以

mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'Admin';

# 授予admin用户对class数据库有==所有访问执行==权限。

mysql> GRANT ALL PRIVILEGES ON class.* TO 'admin'@'%';
mysql> FLUSH PRIVILEGES;

# 授予admin用户对class数据库只有查询、新增权限
mysql> GRANT SELECT,INSERT ON class.* TO 'admin'@'%';
mysql> FLUSH PRIVILEGES;