AI 助理
备案控制台
开发者社区 数据库 文章 正文

mysql数据备份与恢复基础

2015-11-19 1539
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： mysqldump备份 备份所有库[root@miles ~]# mysqldump -uroot -pbeijing --socket=/data/mysql/mysql.

mysqldump备份
备份所有库

[root@miles ~]#  mysqldump -uroot -pbeijing --socket=/data/mysql/mysql.sock --all-databases>/home/mysql/backup/all_db.sql
查看备份
[root@miles backup]# pwd
/home/mysql/backup
[root@miles backup]# ls -lth
total 920M
-rw-r--r--. 1 root root 277M Nov 23 11:26 all_db.sql

备份db1数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1               
...
[root@miles ~]# mysqldump -uroot -pbeijing --socket=/data/mysql/mysql.sock --databases db1>/home/mysql/backup/db1_db.sql

备份单个表

[root@miles ~]# mysqldump -uroot -pbeijing --socket=/data/mysql/mysql.sock db1 test>/home/mysql/backup/db1_test.sql

一致性备份
如果不是使用一致性备份的话，mysql会锁表。如果使用会单独开启一个事务，不会锁表，锁库

[root@miles ~]# mysqldump --single-transaction -uroot -pbeijing --socket=/data/mysql/mysql.sock --all-databases>/home/mysql/backup/all_db_trans.sql

远程备份

[root@m1 ~]# mysqldump -uroot -pbeijing -h192.168.137.20 -P3306 --all-databases > ./backup/remote_backupall.sql 
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'192.168.137.10' for table 'cond_instances' when using LOCK TABLES
报错，添加参数--skip-lock-tables或--single-transaction即可
[root@m1 backup]# mysqldump -uroot -pbeijing -h192.168.137.20 -P3306 --skip-lock-tables --all-databases > /root/backup/remote_backupall_skip.sql 
或
[root@m1 ~]# mysqldump -uroot -pbeijing -h192.168.137.20 -P3306 --single-transaction  --all-databases > ./backup/remote_backupall.sql

导出数据为csv格式

[root@miles ~]# mysqldump --single-transaction -uroot -pbeijing --socket=/data/mysql/mysql.sock db1 test -T /tmp
查看导出数据，默认以制表符分隔
[root@miles tmp]# more test.sql 
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(10) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[root@miles tmp]# more test.txt 
1       zhangsan1
2       zhangsan2
3       zhangsan3

[root@miles ~]# mysqldump --single-transaction -uroot -pbeijing --socket=/data/mysql/mysql.sock --fields-terminated-by=, db1 test -T /tmp
查看导出数据，参数--fields-terminated-by指定分隔符为","
[root@miles tmp]# more test.txt 
1,zhangsan1
2,zhangsan2
3,zhangsan3

xtrabackup备份
全量备份

[root@miles ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=beijing --socket=/data/mysql/mysql.sock /home/mysql/backup
...
151119 11:52:35 Executing UNLOCK TABLES
151119 11:52:35 All tables unlocked
151119 11:52:35 Backup created in directory '/home/mysql/backup/2015-11-19_11-52-27'
151119 11:52:35 [00] Writing backup-my.cnf
151119 11:52:35 [00]        ...done
151119 11:52:35 [00] Writing xtrabackup_info
151119 11:52:35 [00]        ...done
xtrabackup: Transaction log of lsn (832649524) to (832649524) was copied.
151119 11:52:35 completed OK!
查看备份信息
[root@miles 2015-11-20_11-34-42]# pwd
/home/mysql/backup/2015-11-20_11-34-42
[root@miles 2015-11-20_11-34-42]# du -sh *
4.0K    backup-my.cnf
73M     db1
18M     ibdata1
4.0K    log
1.1M    mysql
212K    performance_schema
116K    test
4.0K    xtrabackup_checkpoints
4.0K    xtrabackup_info
4.0K    xtrabackup_logfile

增量备份

首先新增一些数据
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table t(id int(10),name varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(2,'zhangsan2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(3,'zhangsan3');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(4,'zhangsan4');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(5,'zhangsan5');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

开始增量备份
--incremental 表明当前备份为增量备份
--incremental-dir 指定上次备份的路径
[root@miles 2015-11-19_11-52-27]#innobackupex --defaults-file=/etc/my.cnf --user=root --password=beijing --incremental /home/mysql/backup  --incremental-basedir=/home/mysql/backup/2015-11-20_11-34-42 
...
151120 11:57:28 Executing UNLOCK TABLES
151120 11:57:28 All tables unlocked
151120 11:57:28 Backup created in directory '/home/mysql/backup/2015-11-20_11-57-24'
151120 11:57:28 [00] Writing backup-my.cnf
151120 11:57:28 [00]        ...done
151120 11:57:28 [00] Writing xtrabackup_info
151120 11:57:28 [00]        ...done
xtrabackup: Transaction log of lsn (71789633) to (71789633) was copied.
151120 11:57:28 completed OK!
查看备份信息，可见增量备份
[root@miles backup]# du -sh *
92M     2015-11-20_11-34-42
2.0M    2015-11-20_11-57-24

流式备份

[root@miles mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=beijing --stream=xbstream /home/mysql/backup/ > /home/mysql/backup/stream.bak
查看备份信息
[root@miles backup]# ls -lh 
...
-rw-r--r--. 1 root root  92M Nov 20 14:26 stream.bak

并行备份

[root@miles mysql]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=beijing --parallel=4 /home/mysql/backup/
...
151120 15:44:20 Executing UNLOCK TABLES
151120 15:44:20 All tables unlocked
151120 15:44:20 Backup created in directory '/home/mysql/backup//2015-11-20_15-44-18'
151120 15:44:20 [00] Writing backup-my.cnf
151120 15:44:20 [00]        ...done
151120 15:44:20 [00] Writing xtrabackup_info
151120 15:44:20 [00]        ...done
xtrabackup: Transaction log of lsn (71789633) to (71789633) was copied.

限速备份
–throttle=10 表示10M/s的限速

[root@miles tpcc]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=beijing --throttle=10 /home/mysql/backup/
通过iosstat命令查看io状态
[root@miles backup]# iostat -dmx 1

压缩备份

[root@miles tpcc]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=beijing --compress --compress-threads 4 /home/mysql/backup/
效果对比
[root@miles backup]# du -sh *
448M    2015-11-20_16-25-42
278M    2015-11-20_17-14-10

mysqlbinlog

[mysql@miles mysql]$ mysqlbinlog -vv binlog.000002
...
BEGIN
/*!*/;
# at 174
#151124 17:11:48 server id 1  end_log_pos 274   Query   thread_id=1     exec_time=0     error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1448356308/*!*/;
insert into test values(4,'zhangsan4')
/*!*/;
# at 274
#151124 17:11:48 server id 1  end_log_pos 301   Xid = 11
COMMIT/*!*/;
# at 301
#151124 17:11:58 server id 1  end_log_pos 368   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1448356318/*!*/;
BEGIN
/*!*/;
# at 368
#151124 17:11:58 server id 1  end_log_pos 468   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1448356318/*!*/;
insert into test values(5,'zhangsan5')
/*!*/;
# at 468
#151124 17:11:58 server id 1  end_log_pos 495   Xid = 12
COMMIT/*!*/;
# at 495
#151124 17:12:10 server id 1  end_log_pos 562   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1448356330/*!*/;
BEGIN
/*!*/;
# at 562
#151124 17:12:10 server id 1  end_log_pos 651   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1448356330/*!*/;
delete from test where id=7
/*!*/;
# at 651
#151124 17:12:10 server id 1  end_log_pos 678   Xid = 13
COMMIT/*!*/;
...

binlog恢复实验

查看当前binlog状态
mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 |      868 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

插入并提交实验数据
mysql> select * from test;
Empty set (0.00 sec)

mysql> insert into test values (1,'M1'),(2,'M2'),(3,'M3'),(4,'M4'),(5,'M5'),(6,'M6'),(7,'M7'),(8,'M8'),(9,'M9'),(10,'M10');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | M1   |
|    2 | M2   |
|    3 | M3   |
|    4 | M4   |
|    5 | M5   |
|    6 | M6   |
|    7 | M7   |
|    8 | M8   |
|    9 | M9   |
|   10 | M10  |
+------+------+
10 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 |     1139 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

删除实验数据
mysql> delete from test;
Query OK, 10 rows affected (0.07 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

恢复删除数据
[root@miles ~]# mysqlbinlog --start-position=868 --stop-position=1139 /data/mysql/binlog.000003 | mysql -uroot -pbeijing

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | M1   |
|    2 | M2   |
|    3 | M3   |
|    4 | M4   |
|    5 | M5   |
|    6 | M6   |
|    7 | M7   |
|    8 | M8   |
|    9 | M9   |
|   10 | M10  |
+------+------+
10 rows in set (0.00 sec)
文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
SQL
Shell
关键词：
云数据库 RDS MySQL 版数据备份
云数据库 RDS MySQL 版基础
云数据库 RDS MySQL 版恢复
云数据库 RDS MySQL 版数据备份恢复
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
miles_wong
目录
相关文章
洛神灬殇
|
7月前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
洛神灬殇
231 0
洛神灬殇
|
7月前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（备份+恢复篇）（二）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（备份+恢复篇）
洛神灬殇
108 1
游客mldfis24krfue
|
4月前
|
SQL 关系型数据库 MySQL
在Linux中，mysql 数据备份工具有哪些？
在Linux中，mysql 数据备份工具有哪些？
游客mldfis24krfue
67 2
jcLee95
|
4月前
|
SQL 存储 关系型数据库
运维笔记.MySQL.基于mysqldump数据备份与恢复
运维笔记.MySQL.基于mysqldump数据备份与恢复
jcLee95
61 0
jianz123
|
7月前
|
SQL 关系型数据库 MySQL
mysql数据库备份 与恢复 win下的mysql数据备份
mysql数据库备份 与恢复 win下的mysql数据备份
jianz123
54 1
默不会开发
|
7月前
|
关系型数据库 MySQL Shell
在Centos7中利用Shell脚本：实现MySQL的数据备份
在Centos7中利用Shell脚本：实现MySQL的数据备份
默不会开发
420 0
洛神灬殇
|
7月前
|
存储 固态存储 关系型数据库
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（系统底层优化篇）（一）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（系统底层优化篇）
洛神灬殇
81 1
洛神灬殇
|
7月前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）（一）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）
洛神灬殇
70 0
洛神灬殇
|
7月前
|
缓存 网络协议 关系型数据库
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（系统底层优化篇）（二）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（系统底层优化篇）
洛神灬殇
74 0
洛神灬殇
|
7月前
|
关系型数据库 MySQL Shell
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）（二）
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（数据恢复补充篇）
洛神灬殇
40 0

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    PostgreSQL SQL HINT的使用
  • 3
    mysql：Windows修改MySQL数据库密码（修改或忘记密码）
  • 4
    mysql互为主从的环境，更新一条语句同时提交，为什么会出现数据不一致？
  • 5
    更改mysql表引擎(shell)
  • 6
    【MySQL数据库开发之三】MySQL 获得数据库和表操作！
  • 7
    mysql 协议的server状态标识
  • 8
    Mysql数据库初始化后更改数据存储目录
  • 9
    破解并重置mysql密码
  • 10
    MySQL中函数CONCAT及GROUP_CONCAT
  • 1
    【MySQL系列笔记】分库分表
    196
  • 2
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    157
  • 3
    服务器Linux系统配置mysql数据库主从自动备份
    212
  • 4
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    418
  • 5
    golang使用gorm操作mysql3，数据查询
    96
  • 6
    golang使用gorm操作mysql2
    57
  • 7
    golang使用gorm操作mysql1
    106
  • 8
    Hbase与MySQL对比，区别是什么？
    291
  • 9
    解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝
    2309
  • 10
    navicat过期了，直接用idea连接mysql
    64

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月