Mysql数据库备份(逻辑备份)

准备三个测试表

表company.employee

create database company;

use company;

create table employee( 
emp_id int auto_increment primary key not null, 
emp_name varchar(50), 
age int, 
dept_id int);

insert into employee(emp_name,age,dept_id) values
('tianyun',19,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('natasha',28,204);

select * from employee;

表company.department

create table department(
dept_id int,
dept_name varchar(100)
);

insert into department values
(200,'hr'),
(201,'it'),
(202,'sale'),
(203,'fd');

select * from department;

表school.student

create database school;

use school;

create table student(
id int,
name varchar(30),
date varchar(10)
);

insert into student values
(1,'jack','Monday'),
(2,'tom','Tuesday'),
(3,'alice','Wednesday');

select * from student;

逻辑备份

备份的是建表、建库、插入等操作所执行SQL语句

不论是什么存储引擎，都可以用mysqldump备成SQL语句

速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份

1.1 常用备份选项

-A, --all-databases                                备份所有库

-B, --databases bbs test mysql           备份多个数据库

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

1.2 备份表

注：在Linux命令行操作

创建备份目录

mkdir /opt/back

备份单个表

mysqldump -uroot -p'Linyunxi@123' company employee > /opt/back/company.employee.back

备份多个表

mysqldump -uroot -p'Linyunxi@123' company employee department > /opt/back/company.emp_dep.back

1.3 备份库

备份单个库

mysqldump -uroot -p'Linyunxi@123' company > /opt/back/company.back

备份多个库

mysqldump -uroot -p'Linyunxi@123' -B company school > /opt/back/company_school.back

备份所有库

mysqldump -uroot -p'Linyunxi@123' -A > /opt/back/allbase.back

1.4 删除数据库和表

为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志

为实验效果删除company和school两个数据库

show databases;

drop database company;

drop database school;

show databases;

1.5 恢复库

登陆mysql创建一个库

create database company;

在Linux命令行恢复

mysql -uroot -p'Linyunxi@123' company < /opt/back/company.back

登陆mysql查看

1.6 恢复表

登陆到刚才恢复的库中将其中的一个表删除掉

drop table employee;

show tables;

开始恢复

方式一（sql语句恢复）

source /opt/back/company.employee.back

show tables;

方式二（Linux命令行恢复）

mysql -uroot -p'Linyunxi@123' company < company.employee.back

1.7 备份及恢复表结构

备份表结构

mysqldump  -uroot -p'Linyunxi@123' -d company employee > /opt/back/emp.back

登陆数据库创建一个库

create database t1;

恢复表结构

mysql -uroot -p'Linyunxi@123' -D t1 < /opt/back/emp.back

登录数据库查看

1.8 数据的导入导出（没有表结构）

表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据

查询导入导出的目录

show variables like "secure_file_priv";

修改安全文件目录

        ①创建一个目录

mkdir /sql

        ②修改权限

chown mysql.mysql /sql

        ③编辑配置文件

vim /etc/my.cnf

        在[mysqld]里追加如下内容：

secure_file_priv=/sql

        ④重启mysql

systemctl restart mysqld

导出数据

select * from employee;

select * from employee into outfile '/sql/test.emp.back' fields terminated by ',' lines terminated by '\n';

解释：

fields terminated by ','         字段以逗号分割

lines terminated by '\n'        结尾换行

导入数据

delete from employee;

load data local infile "/sql/test.emp.back" into table employee fields terminated by ',' lines terminated by '\n';

注：如果将数据导入别的表，需要创建这个表并创建相应的表结构