美女精品美女视频,美女视频在线网址

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

#恢復(fù)

create database db3;surce XXX.sql;

#使用事務(wù)備份全庫

5)mysqldump --single-transaction -uroot -p123456 --all-databases > XXX.sql

#遠(yuǎn)程備份

6)mysqldump -utest -ptest -hXXX -P3306 --all-databases > XXX.sql

#備份.sql,和.csv格式；

7)mysqldump --single-transaction -uroot -p123456 db1 -T XXX

#指定分隔符為","默認(rèn)分隔符為制表符

8)mysqldump -uroot -p000000 --single-transaction --fields-terminated-by=, shuxue -T /tmp

主要示例

演示使用mysqldump備份表，庫，實(shí)例
演示使用mysqldump制作一致性備份
演示使用mysqldump導(dǎo)出數(shù)據(jù)為csv格式

演示使用mysqldump備份表，庫，實(shí)例：

備份全庫：

aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 --socket=/var/run/mysqld/mysqld.sock --all-databases > ./all_db.sqlaiapple@itcast01:~/mysql_backup$ lsall_db.sqlaiapple@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，實(shí)際上就是SQL語句，會(huì)將表，插入數(shù)據(jù)等等；

備份指定庫：

aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 --socket=/var/run/mysqld/mysqld.sock --databases shuxue > ./shuxue.sqlaiapple@itcast01:~/mysql_backup$ lsall_db.sql  shuxue.sqlaiapple@itcast01:~/mysql_backup$ sed -n '22,45p' shuxue.sqlCREATE 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;

備份單個(gè)表：

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.sqlaiapple@itcast01:~/mysql_backup$ lsall_db.sql  shuxue.sql  week_day.sqlaiapple@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 dbDatabase changed mysql> show tables;Empty set (0.00 sec)  mysql> source /home/aiapple/mysql_backup/week_day.sqlQuery OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec) mysql> show tables;+--------------+| Tables_in_db |+--------------+| week_day     |+--------------+使用source命令還原數(shù)據(jù)；

mysqldump一致性備份

定義：

因?yàn)樵趥浞葸^程中，數(shù)據(jù)庫的數(shù)據(jù)是不斷變動(dòng)的，而一致性備份是指?jìng)浞菽硶r(shí)刻點(diǎn)的數(shù)據(jù)；

因?yàn)閕nnodb提供了可重復(fù)讀，這個(gè)隔離級(jí)別，即在同一個(gè)事務(wù)內(nèi)看到的數(shù)據(jù)不變，所以備份工作中，

如果開啟一個(gè)事務(wù)再進(jìn)行備份，那么這個(gè)備份就是事務(wù)開啟的時(shí)間點(diǎn)的備份，也叫一致性備份；

參數(shù)：--single-transaction

默認(rèn)備份會(huì)鎖表，鎖庫，來備份，因?yàn)闆]有開啟事務(wù)，而使用這個(gè)參數(shù)之后，會(huì)開啟一個(gè)事務(wù)來備份，不會(huì)鎖表鎖庫。

一致性備份全庫

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$ lsall_db.sqlaiapple@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;

遠(yuǎn)程備份

aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 -h127.0.0.1 -P3306 --database shuxue > ./shuxue.sql aiapple@itcast01:~/mysql_backup$ lsshuxue.sql

演示使用mysqldump導(dǎo)出數(shù)據(jù)為csv格式

參數(shù)：-T

aiapple@itcast01:~/mysql_backup$ mysqldump -uroot -p000000 shuxue -T /tmp aiapple@itcast01:~/mysql_backup$ cd /tmp/aiapple@itcast01:/tmp$ lsres_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.sqlDROP 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.txt2015-05-02    50    52015-05-03    62    62015-05-09    79    52015-05-10    31    62015-05-16    75    52015-05-17    64    62015-05-23    101    52015-05-24    55    62015-05-30    87    52015-05-31    66    62015-06-06    76    52015-06-07    66    62015-06-13    72    52015-06-14    69    62015-06-20    108    52015-06-21    64    62015-06-27    62    52015-06-28    71    62015-07-04    68    52015-07-05    69    62015-07-11    104    52015-07-12    105    62015-07-18    76    52015-07-19    86    62015-07-25    90    52015-07-26    94    6

#默認(rèn)分隔符為制表符

#可以指定分隔符

#指定分隔符為","

aiapple@itcast01:/tmp$ mysqldump -uroot -p000000 --single-transaction --fields-terminated-by=, shuxue -T /tmp aiapple@itcast01:~/mysql_backup$ cd /tmp/aiapple@itcast01:/tmp$ lsres_new.sql         res_new.txtweek_day.txt        week_day.sqlres_sum_num.sql     res_sum_num.txt aiapple@itcast01:/tmp$ sed -n '20,45p' week_day.sqlDROP 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.txt2015-05-02,50,52015-05-03,62,62015-05-09,79,52015-05-10,31,62015-05-16,75,52015-05-17,64,62015-05-23,101,52015-05-24,55,62015-05-30,87,52015-05-31,66,62015-06-06,76,52015-06-07,66,62015-06-13,72,52015-06-14,69,62015-06-20,108,52015-06-21,64,62015-06-27,62,52015-06-28,71,62015-07-04,68,52015-07-05,69,62015-07-11,104,52015-07-12,105,62015-07-18,76,52015-07-19,86,62015-07-25,90,52015-07-26,94,6

本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊舉報(bào)。

国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看