2020-09-24
逻辑备份
备份的是建库、建表、插入等操作所执行的SQL语句（DML，DDL，DCL），适用于中小型数据库，效率相对较低
保证数据一致性：在备份的时候会自动进行锁表
工具安装
①mysqldump在安装MySQL数据库时自带，在/安装路径/bin/mysqldump
②若不想安装mysql数据库，只单独安装mysqldump，使用命令下载：yum -y install holland-mysqldump.noarch
#远程备份语法(后缀名随意，能区分开就行)
mysqldump -u 用户名 -p 密码 -h 服务器 -P 端口 需要备份的数据库 > 备份文件名.sql
#本地备份语法
mysqldump -u 用户名 -p 密码 数据库名 > 备份文件.sql
#常用备份选项
-A （或 --all-databases） #备份所有库
-B （或 --databases web mysql db2） #备份多个库
-d （或 --no-data） #不导出任何数据，指导出数据库表结构
备份表
[root@localhost ~]# mkdir /home/bakups
[root@localhost ~]# mysqldump -uroot -p'123' db2 table2 > /home/backups/db2-table2-`date +%F`.sql #指定备份某库中的某个表
[root@localhost ~]# mysqldump -uroot -p'123' db2 table2 table3> /home/backups/db2-table2-table3-`date #备份某库的多个表
备份库
[root@localhost ~]# mysqldump -uroot -p'123' db1 > /home/backups/db1-`date +%F`.sql #备份单个库
[root@localhost ~]# mysqldump -uroot -p'123' -B db1 db2 > /home/backups/db1-db2-`date +%F`.sql #备份多个库
[root@localhost ~]# mysqldump -uroot -p'123' -A > /home/backups/allbase-`date +%F`.sql #备份所有的库
恢复数据库和表
为保证数据一致性，应在数据恢复前停止数据库的对外服务，停止binlog日志，因为当使用binlog日志恢复数据时也会产生binlog日志
(1)恢复库
mysql> create database company;
[root@localhost ~]# mysql -uroot -p'123' company < /home/backups/company.sql
(2)恢复表
方式一：
mysql> set sql_log_bin=0; #停止binlog日志（可选）
mysql > use company;
mysql> source /home/back/company.employee.sql;
方式二：
[root@localhost ~]# mysql -uroot -p'123' company < /home/back/company.employee.sql
备份和恢复表结构
#备份表结构
[root@localhost ~]# mysqldump -uroot -p'123' -d db1 table1 > /home/backups/table1.sql
#恢复表结构
mysql > create database db3; #创建新库（或者直接使用已存在的库）
导入导出数据
直接在库里导入导出数据（没有表结构，只有数据）
在恢复数据的时候先恢复表结构，再导入数据
备份表结构用mysqldump，导出数据SQL语句(select * from table2 into outfile '/home/sql/table2.bak';)
mysql> show variables like "secure_file_priv"; #查询导入导出数据的目录（保证数据安全做共享）
#修改安全文件目录（secure_file_priv）
[root@localhost home]# cd /home/
[root@localhost home]# mkdir sql
[root@localhost home]# chown mysql.mysql sql/
[root@localhost home]# vim /etc/my.cnf
#在[mysqld]里追加
secure_file_priv=/home/sql
[root@localhost home]# systemctl restart mysqld
#导出数据
mysql > show databases;
mysql > use db2;
mysql > show tables;
mysql > select * from t2 into outfile '/home/sql/db2-t2.bak';
mysql > select * from t2 into outfile '/home/sql/db2-t2.bak' fields terminated by ',' lines terminated by '\n'; #(fields terminated by ',' ：字段以逗号分割) (lines terminated by '\n'：结尾换行)
#注意：添加了修饰导出的数据不是表格形式，导入的时候会报错
#导入数据
mysql > delete * from t2; #删除旧数据
mysql > load data infile '/home/sql/db2-t2.bak' into table t2; #如果表结构改变则数据导入失败
使用binlog日志恢复数据
前提：必须开启有binlog日志功能
#开启binlog
[root@localhost ~]# vim /etc/my.cnf
##确保以下两条配置存在##
#指定binlog的存放位置和名称（自定义）
log-bin = /usr/local/mysqld/mysql/mysql-bin.log
#主从复制需要server_id，开启binlog也需要
server_id = 11
[root@localhost ~]# systemctl restart mysqld #重启使配置生效
[root@localhost ~]# cd /usr/local/mysqld/mysql/ && ls #重启之后就能在指定目录下看到binlog日志文件
[root@localhost ~]# mysql -uroot -p'123'
mysql > flush logs; #刷新日志，使后面的SQL操作都记录再另一个binlog日志中
mysql > create table db2.t3(id int);
[root@localhost ~]# mysqlbinlog /usr/local/mysqld/mysql/mysql-bin.000002 #查看binlog日志内容
ff1.png
#恢复测试
mysql>drop table testdb.t2(id int); #删除刚创建的表格 注意：增删改都会记录到binlog日志中
[root@localhost ~]# mysqlbinlog --start-position 219 --stop-position 321 mysql-bin.000002 |mysql -uroot -p123 #恢复语句，根据起始位置和结束为止进行恢复（binlog文件不能带路径，会报错）
