十一、MYSQL数据库备份还原

mysqldump备份和还原数据库

备份

主从数据库备份

参考: https://my.oschina.net/u/1473861/blog/1622953

Mysql 数据库 在主从 数据库进行 导出备份和恢复的时候，需要注意是否启用数据库用GTID模式.如果开启，则在mysqldump数据时，应该mysqldump命令加上参数--set-gtid-purged=OFF。

# 主从数据库备份
mysqldump -h{db_host} -u{db_user} -p{db_passwd} -P{port} {dbname} {tables} --set-gtid-purged=OFF > /tmp/dbname.tables.sql  

备份指定数据库

# 备份指定数据库
mysqldump -u root -p --databases 数据库名>C:\testandmysql.sql

备份多个数据库

# 备份多个数据库
mysqldump -u username -p --databases dbname1 dbname2 ... > filename.sql

备份所有数据库

# 使用“--all-databases”参数时，不需要指定数据库名称。
mysqldump -u username -p --all-databases>filename.sql

#  备份所有数据库
mysqldump -u root -p --all-databases > C:\all.sql

备份数据表

参考: https://blog.51cto.com/fuwenchao/1335473

方法1: mysqldump备份数据表

# 备份数据库中某个表
# # mysqldump -u 用户名 -p 数据库名 表名 >备份的文件名
mysqldump -uroot -p test student > C:\student.sql


# 备份数据库中多个表
mysqldump db1 tb1 tb2 -u root -p123 > biao.sql

方法2: create table备份

-- 备份fund_netvalue表--只支持下划线
-- create table 表名_备份 as (select * from 表名);
create table fund_netvalue_bak_2013_11_08 as (select * from fund_netvalue);

方法3: SQL SELECT INTO 实例 - 制作备份复件

-- 制作 "Persons" 表的备份复件
SELECT
 * 
INTO
 Persons_backup
FROM Persons

导出数据库信息

查看secure-file-priv当前的值是

-- 查看secure-file-priv当前的值
show variables like '%secure%'; 

导出数据库

如果导出失败,则需要添加**secure_file_priv**

-- 导出数据库
SELECT * FROM student INTO OUTFILE 'C://Pcode/person.txt';

还原

登录mysql后还原

# 登录mysql
mysql -p

# 进入要还原的数据库
use edu
# 还原备份的数据库
source /root/edu.sql
## 弹出的提示输入y即可.

还原指定数据库

-- 恢复数据库
mysql -u username -P [dbname] < filename.sql
mysql -u root -p < C:\all.sql

还原所有数据库

# 如果使用--all-databases参数备份了所有的数据库,还原时不需要指定数据库
mysql -u root -p < C:\all.sql

mysqldump备份过程中避免锁表

## 添加 --skip-opt
mysqldump --skip-opt -u root --password=123456 dbname >mySQL.SQL

mysqldump不备份任何数据，只导出数据库表结构

# --no-data, -d
##不导出任何数据，只导出数据库表结构。
mysqldump -d -u root --password=123456 dbname tablename >mySQL.SQL

还原数据库方法2：

-- 先新建一个数据库
create database 数据库名;
-- 或
create schema 数据库名;

-- 指定数据库编码格式
CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 进入空数据库
use 数据库名;

-- 还原数据库
source 备份的数据库.sql;
-- 弹出的提示按y

-- 查看还原的数据表
show tables;