mysqldump逻辑备份与数据恢复
mysqldump介绍
- mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
如何保证数据一致?
- 在备份的时候进行锁表会自动锁表。锁住之后在备份。
远程备份语句
# mysqldump -h IP/域名解析 -u用户名 -p密码 数据库名 > 备份文件.sql
本地备份语法
# mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
常用备份数据选项
-A, --all-databases
\\备份所有库
-B, --databases
\\备份多个数据库
-F, --flush-logs
\\备份之前刷新binlog日志
--default-character-set
\\指定导出数据时采用何种字符集,如果数据表不是采用默认的latin1字符集的话,那么导出时必须指定该选项,否则再次导入数据后将产生乱码问题。
-D,--no-data
\\不导出任何数据,只导出数据库表结构。
--lock-tables
\\备份前,锁定所有数据库表
--single-transaction
\\保证数据的一致性和服务的可用性
-f, --force
\\即使在一个表导出期间得到一个SQL错误,继续。
备份注意避免锁库
- 对一个正在运行的数据库进行备份请慎重!! 如果一定要 在服务运行期间备份,可以选择添加 --single-transaction选项,
类似执行:
mysqldump --single-transaction -u root -p123456 dbname > mysql.sql
逻辑备份数据
1,备份表
备份单个表
语法
# mysqldump -u 用户名 -p密码 库名 表名 > 存放备份的目录路径/备份文件名
# mysqldump -uroot -p'xin@123' company employee5 > /home/back/company.employee5.bak
备份多个表
语法
# mysqldump -u 用户名 -p密码 库名 表名 表名 表名 > 存放备份的目录路径/备份文件名
# mysqldump -uroot -p'xin@123' company new_t1 new_t2 > /home/back/company.new_t1_t2.bak
2,备份库
备份一个库:相当于将这个库里面的所有表全部备份。
备份单个库
语法
# mysqldump -u 用户名 -p密码 库名 > 存放备份的目录路径/备份文件名
# mysqldump -uroot -p'xin@123' company > /home/back/company.bak
备份多个库
语法
# mysqldump -u 用户名 -p密码 库名 库名 库名 > 存放备份的目录路径/备份文件名
# mysqldump -uroot -p'xin@123' -B company testdb > /home/back/company_testdb.bak
备份所有的库
语法
#mysqldump -u 用户名 -p密码 -A > 存放备份的目录路径/备份文件名
#mysqldump -uroot -p'xin@123' -A > /home/back/allbase.bak
3,查看存放备份的目录
# cd /home/back/
# ls
恢复数据库和表
为保证数据一致性,
- 应在恢复数据之前停止数据库对外的服务,停止binlog日志
- 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
1,恢复库
删除数据库里备份过的数据
# mysql -uroot -p'xin@123'
mysql> show databases;
mysql> drop database company;
mysql> \q
创建一个库
mysql> create database company;
恢复数据
# mysql -uroot -p'xin@123' company < /home/back/company.bak
2,恢复表
删除数据库里备份过的表数据
mysql> show databases;
mysql> use company
mysql> show tables;
mysql> drop tables employee5;
恢复表数据
停止bin log 日志
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
恢复表数据方式一
mysql> source /home/back/company.employee5.bak;
-------加路径和备份的文件
恢复数据方式二
# mysql -u 用户名 -p密码 库名 < 存放备份的目录路径/备份文件名
# mysql -u root -p1 db1 < db1.t1.bak
备份及恢复表结构
1,备份表结构
语法
# mysqldump -u用户 -p密码 -d 库名 表名 > 存放备份的目录 / 备份的文件名
# mysqldump -uroot -p'xin@123' -d company employee5 > /home/back/emp.bak
2,恢复表结构数据
语法
# mysql -u 用户 -p密码 -D 库名 < 存放备份的目录 / 备份的文件名
# mysql -uroot -p'xin@123' -D t1 < /home/back/emp.bak
3,登陆数据库查看表结构
mysql> desc employee5
4,数据的导入导出,没有表结构
- 表的导出和导入只备份表内记录,不会备份表结构,
需要通过mysqldump备份表结构,恢复时先恢复表结构,再导入数据。
5,查询导入导出的目录
mysql> show variables like "secure_file_priv";
6,修改安全文件目录
创建一个目录,路径目录,并修改权限
# mkdir /sql
# chown mysql.mysql /sql
编译配置文件
vim /etc/my.cnf
在[mysqld]里追加
secure_file_priv=/sql
重新重启MySQL
6,导出表数据
mysql> show databases;
mysql> use test
mysql> show tables;
mysql> select * from t3 into outfile '/sql/test.t3.bak';
7,数据的导入
- 先将原来表里面的数据清除掉,只保留表结构
- 如果将数据导入别的表,需要创建这个表并创建相应的表结构。
mysql> delete from t3;
mysql> load data infile '/sql/test.t3.bak' into table t3;
通过bin log 日志恢复数据
1,开启bin log 日志
[root@mysql-server ~]# vim /etc/my.cnf
log-bin=/var/log/sql-bin/mylog
server-id=1
\\ ID号做主从时用到,这里必须写
创建目录、修改权限并重启服务器
# mkdir /var/log/sql-bin
# chown mysql.mysql /var/log/sql-bin
# systemctl restart mysqld
刷新 bin log 日志,截断产生新的日志
重启会截断bin log日志,也可以手动截断
mysql> flush logs;
2,插入数据,并查看bin log日志
创建一个表
mysql> create table testdb.t3(id int);
找到要恢复的sql语句的起始位置、结束位置
# cd /var/log/sql-bin/
# ls
mysql.000001 mysql.ooooo2 mysql.index
# mysqlbinlog mylog.000002
3,通过bin log 恢复数据
将刚刚插入的数据清除
# mysql -uroot -p'xin@123'
mysql> drop table testdb.t3;
Query OK, 0 rows affected (0.01 sec)
根据想要恢复的sql语句的起始位置、结束位置来恢复数据
# cd /var/log/sql-bin/
# mysqlbinlog mylog.000002
# mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 | mysql -uroot -p'xin@123'
mysql> show databases;
\\查看数据是否恢复