mysqldump
- mysqldump---逻辑备份,热备
-
- 单线程,适合数据量小的库
- 单线程,适合数据量小的库
mysql官方自带的命令行工具
#全库
1)mysqldump -uroot -p123456 --socket=XXX --all-databases > XXX.sql
#指定库
2)mysqldump -uroot -p123456 --socket=XXX --databases db2 > XXX.sql
#指定表
3)mysqldump -uroot -p123456 --socket=XXX db2 t1 > XXX.sql
#恢复
4)
create database db3;
surce XXX.sql;
#使用事务备份全库
5)mysqldump --single-transaction -uroot -p123456 --all-databases > XXX.sql
#远程备份
6)mysqldump -utest -ptest -hXXX -P3306 --all-databases > XXX.sql
#备份.sql,和.csv格式;
7)mysqldump --single-transaction -uroot -p123456 db1 -T XXX
#指定分隔符为","默认分隔符为制表符
8)mysqldump -uroot -p000000 --single-transaction --fields-terminated-by=, shuxue -T /tmp
主要示例
- 演示使用mysqldump备份表,库,实例
- 演示使用mysqldump制作一致性备份
- 演示使用mysqldump导出数据为csv格式
演示使用mysqldump备份表,库,实例:
备份全库:
aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 --socket=/var/run/mysqld/mysqld.sock --all-databases > ./all_db.sql
aiapple@itcast01:~/mysql_backup$ ls
all_db.sql
aiapple@itcast01:~/mysql_backup$ head -n 30 all_db.sql
-- MySQL dump 10.13 Distrib 5.5.47, for debian-linux-gnu (i686)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.47-0ubuntu0.12.04.1
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `TEST`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `TEST` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `TEST`;
--
-- Current Database: `WY_yun`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `WY_yun` /*!40100 DEFAULT CHARACTER SET latin1 */;
#可以看到all_db.sql,实际上就是SQL语句,会将表,插入数据等等;
备份指定库:
aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 --socket=/var/run/mysqld/mysqld.sock --databases shuxue > ./shuxue.sql
aiapple@itcast01:~/mysql_backup$ ls
all_db.sql shuxue.sql
aiapple@itcast01:~/mysql_backup$ sed -n '22,45p' shuxue.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `shuxue` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `shuxue`;
--
-- Table structure for table `res_new`
--
DROP TABLE IF EXISTS `res_new`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `res_new` (
`res` date DEFAULT NULL,
`sum_num` decimal(32,0) DEFAULT NULL,
`week_day` int(1) DEFAULT NULL,
`que` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `res_new`
--
LOCK TABLES `res_new` WRITE;
#指定文件显示第几行到第几行sed -n '22,45p' filename;
备份单个表:
mysql> show tables;
+------------------+
| Tables_in_shuxue |
+------------------+
| res_new |
| res_sum_num |
| week_day |
+------------------+
aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 --socket=/var/run/mysqld/mysqld.sock shuxue week_day > ./week_day.sql
aiapple@itcast01:~/mysql_backup$ ls
all_db.sql shuxue.sql week_day.sql
aiapple@itcast01:~/mysql_backup$ sed -n '10,37p' week_day.sql
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `week_day`
--
DROP TABLE IF EXISTS `week_day`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `week_day` (
`res` date DEFAULT NULL,
`sum_num` decimal(32,0) DEFAULT NULL,
`week_day` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `week_day`
--
LOCK TABLES `week_day` WRITE;
/*!40000 ALTER TABLE `week_day` DISABLE KEYS */;
还原week_day表
mysql> create database db;
Query OK, 1 row affected (0.01 sec)
mysql> use db
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> source /home/aiapple/mysql_backup/week_day.sql
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| week_day |
+--------------+
使用source命令还原数据;
mysqldump一致性备份
定义:
因为在备份过程中,数据库的数据是不断变动的,而一致性备份是指备份某时刻点的数据;
因为innodb提供了可重复读,这个隔离级别,即在同一个事务内看到的数据不变,所以 备份工作中,
如果开启一个事务再进行备份,那么这个备份就是事务开启的时间点的备份,也叫一致性备份;
参数:--single-transaction
默认备份会锁表,锁库,来备份,因为没有开启事务,而使用这个参数之后,会开启一个事务来备份,不会锁表锁库。
一致性备份全库
aiapple@itcast01:~/mysql_backup$ mysqldump --single-transaction -uroot -p000000 --socket=/var/run/mysqld/mysqld.sock --all-databases > ./all_db.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
aiapple@itcast01:~/mysql_backup$ ls
all_db.sql
aiapple@itcast01:~/mysql_backup$ sed -n '20,45p' all_db.sql
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `TEST` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `TEST`;
--
-- Current Database: `WY_yun`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `WY_yun` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `WY_yun`;
--
-- Table structure for table `a`
--
DROP TABLE IF EXISTS `a`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a` (
`name` varchar(20) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`sal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
远程备份
aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 -h127.0.0.1 -P3306 --database shuxue > ./shuxue.sql
aiapple@itcast01:~/mysql_backup$ ls
shuxue.sql
演示使用mysqldump导出数据为csv格式
参数:-T
aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 shuxue -T /tmp
aiapple@itcast01:~/mysql_backup$ cd /tmp/
aiapple@itcast01:/tmp$ ls
res_new.sql res_new.txt
week_day.txt week_day.sql
res_sum_num.sql res_sum_num.txt
aiapple@itcast01:/tmp$ sed -n '20,45p' week_day.sql
DROP TABLE IF EXISTS `week_day`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `week_day` (
`res` date DEFAULT NULL,
`sum_num` decimal(32,0) DEFAULT NULL,
`week_day` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-06-25 9:32:25
aiapple@itcast01:/tmp$ sed -n '20,45p' week_day.txt
2015-05-02 50 5
2015-05-03 62 6
2015-05-09 79 5
2015-05-10 31 6
2015-05-16 75 5
2015-05-17 64 6
2015-05-23 101 5
2015-05-24 55 6
2015-05-30 87 5
2015-05-31 66 6
2015-06-06 76 5
2015-06-07 66 6
2015-06-13 72 5
2015-06-14 69 6
2015-06-20 108 5
2015-06-21 64 6
2015-06-27 62 5
2015-06-28 71 6
2015-07-04 68 5
2015-07-05 69 6
2015-07-11 104 5
2015-07-12 105 6
2015-07-18 76 5
2015-07-19 86 6
2015-07-25 90 5
2015-07-26 94 6
#默认分隔符为制表符
#可以指定分隔符
#指定分隔符为","
aiapple@itcast01:/tmp$ mysqldump -uroot -p000000 --single-transaction --fields-terminated-by=, shuxue -T /tmp
aiapple@itcast01:~/mysql_backup$ cd /tmp/
aiapple@itcast01:/tmp$ ls
res_new.sql res_new.txt
week_day.txt week_day.sql
res_sum_num.sql res_sum_num.txt
aiapple@itcast01:/tmp$ sed -n '20,45p' week_day.sql
DROP TABLE IF EXISTS `week_day`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `week_day` (
`res` date DEFAULT NULL,
`sum_num` decimal(32,0) DEFAULT NULL,
`week_day` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-06-25 9:32:25
aiapple@itcast01:/tmp$ sed -n '20,45p' week_day.txt
2015-05-02,50,5
2015-05-03,62,6
2015-05-09,79,5
2015-05-10,31,6
2015-05-16,75,5
2015-05-17,64,6
2015-05-23,101,5
2015-05-24,55,6
2015-05-30,87,5
2015-05-31,66,6
2015-06-06,76,5
2015-06-07,66,6
2015-06-13,72,5
2015-06-14,69,6
2015-06-20,108,5
2015-06-21,64,6
2015-06-27,62,5
2015-06-28,71,6
2015-07-04,68,5
2015-07-05,69,6
2015-07-11,104,5
2015-07-12,105,6
2015-07-18,76,5
2015-07-19,86,6
2015-07-25,90,5
2015-07-26,94,6