【mysqldump逻辑备份】【备份单表 多表 全部表】【备份单库 多库 全部库】【备份表结构 表数据】【恢复库】【恢复表】【恢复表结构 表数据】【通过binlog恢复数据】

mysqldump逻辑备份 ---- 推荐优先使用

mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。

如何保证数据一致?在备份的时候进行锁表会自动锁表。锁住之后在备份。
本身为客户端工具:
远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
1.常用备份选项
-A, --all-databases #备份所有库

-B, --databases #备份多个数据库

-F, --flush-logs #备份之前刷新binlog日志

–default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

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

–lock-tables #备份前，锁定所有数据库表

–single-transaction #保证数据的一致性和服务的可用性

-f, --force #即使在一个表导出期间得到一个SQL错误，继续。## 标题
注意
使用 mysqldump 备份数据库时避免锁表:
对一个正在运行的数据库进行备份请慎重！！ 如果一定要 在服务运行期间备份，可以选择添加 --single-transaction选项，
类似执行： mysqldump --single-transaction -u root -p123456 dbname > mysql.sql

备份表

语法: # mysqldump -u root -p1 db1 t1 > /db1.t1.bak
步骤1：[root~]# mkdir /home/back #创建备份目录 自定义目录
测试：mysqldump -uroot -p’qf123’ company employee5 > /home/back/company.employee5.bak
备份 指令 用户 密码 备份库名 表名 >到那 自定义目录 文件名自定义

备份多个表：

语法: mysqldump -u root -p1 db1 t1 t2 > /db1.t1_t2.bak
测试；mysqldump -u root -p’QianFeng@123’ zuoye t1 chayi > /home/back/zuoye.t1_chayi.sql
备份 指令 用户 密码 备份库名 表名 表名 >到那 自定义目录 文件名自定义

备份库

备份一个库：相当于将这个库里面的所有表全部备份。
语法；语法: # mysqldump -u root -p1 库名 > /db1.bak
测试；mysqldump -u root -p’QianFeng@123’ gg > /home/back/gg.sql
备份 指令 用户 密码 备份库名 >到那 自定义目录 文件名自定义

备份多个库： -B参数

语法；语法：mysqldump -u root -p1 -B db1 db2 db3 > /db123.bak
测试；mysqldump -u root -p’QianFeng@123’ -B qf qf2 > /home/back/qf_qf2.sql
备份 指令 用户 密码 参数 备份库名 库名 >到那 自定义目录 文件名自定义

备份所有的库： -A参数

语法：# mysqldump -u root -p1 -A > /alldb.bak
测试；mysqldump -u root -p’QianFeng@123’ -A > /home/back/all.sql
备份 指令 用户 密码 参数 备份所有库 >到那 自定义目录 文件名自定义

恢复数据库和表

为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
测试效果；会把之前的库和表都删除

恢复库

方法1；mysql> source /home/back/company.employee5.bak;
命令 -------加路径和备份的文件
因为上面测试 把所有库都删除了，先创建一个库，回复到库里
步骤1；create databaes zuoye； 先创建一个库
恢复： mysql -u root -p’QianFeng@123’ gg < /home/back/gg.sql
用户密码 库名 恢复 那个目录那个文件

恢复表

步骤1；mysql -u root -p’QianFeng@123’ zuoye < /home/back/zuoye_t1.sql

备份及恢复表结构

表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。
备份表结构：先查看表结构支持导入的文件

备份表结构 -d 参数

语法；语法：mysqldump -uroot -p123456 -d database table > dump.sql
mysqldump -uroot -p’qf123’ -d company employee5 > /home/back/emp.bak
备份 指令 用户 密码 参数 备份所有库 表 到 自定义目录 文件名自定义

恢复表结构：-D参数

语法：# mysql -u root -p1 -D db1 < db1.t1.bak
测试； mysql -uroot -p’qf123’ -D t1 < /home/back/emp.bak
备份 指令 用户 密码 参数 恢复库 回复那个 自定义目录 文件名自定义

数据的导入导出,没有表结构

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

步骤1；mysql> show variables like “secure_file_priv”; ----查询导入导出的目录。

修改安全文件目录：
1.创建一个目录：mkdir 路径目录
[root@mysql-server ~]# mkdir /sql
2.修改权限
[root@mysql-server ~]# chown mysql.mysql /sql
3.编辑配置文件：
vim /etc/my.cnf
在[mysqld]里追加
secure_file_priv=/sql
4.重新启动mysql.

导出数据

mysql> show databases; #找到test库
mysql> use test #进入test库
mysql> select * from t3;
±-----+
| id |
±-----+
| 1 |
| 3 |
±-----+
2 rows in set (0.00 sec)
语法；mysql> select * from t3 into outfile ‘/sql/test.t3.bak’;
查看表内容 导出 到哪里

2.数据的导入

先将原来表里面的数据清除掉，只保留表结构
mysql> delete from t3; 删除这个表里的内容
语法；mysql> load data infile ‘/sql/test.t3.bak’ into table t3;
导入指令 这个文件 导出到这个表
mysql> select * from t3;
±-----+
| id |
±-----+
| 1 |
| 3 |
±-----+
2 rows in set (0.00 sec)
如果将数据导入别的表，需要创建这个表并创建相应的表结构。

通过binlog恢复数据

开启binlog日志：
步骤1； vim /etc/my.cnf 编写配置文件 添加一下内容
log-bin=/var/log/mysql-bin/mylog #如果不指定路径默认在/var/lib/mysql
server-id=1 #AB复制的时候使用，为了防止相互复制，会设置一个ID，来标识谁产生的日志
binlog_format=statement #解决binlog日志不记录insert语句

步骤2；创建目录并修改权限 重启MySQL
[root@mysql-server ~]# mkdir var/log/mysql-bin/mylog
[root@mysql-server ~]# chown mysql.mysql var/log/mysql-bin/ -R
[root@mysql-server ~]# systemctl restart mysqld 重启

步骤3；测试 刷新 截断日志 新建一个表
mysql> flush logs; #刷新binlog日志会截断产生新的日志文件
mysql> create table testdb.t3(id int); #创建一个表

步骤4；先找 sql语句位置 进行标记位置
根据位置恢复
找到要恢复的sql语句的起始位置、结束位置
语法；# mysqlbinlog mylog.000002 查看 日志指令

步骤5；测试 进行回复
方法；drop table testdb.t3; #将这个表删除
恢复：
[root]# mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 | mysql -uroot -p’qf123’
工具 起始位置 结束位置 文件 用户
mysql: [Warning] Using a password on the command line interface can be insecure.
查看：