MySQL运维之

Wesley13
• 阅读 980

1、mysqldump备份一个数据库

mysqldump命令备份一个数据库的基本语法:

mysqldump  -u user -p pwd  dbname > Backup.sql

我们来讲解一下备份的文件都包含了什么?-- MySQL dump 10.13 Distrib 5.5.20, for Win32 (x86)--

-- Host: 127.0.0.1    Database: school
-- ------------------------------------------------------
-- Server version    5.5.20-log

/*!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 */;
--
-- Table structure for table `book`
--
DROP TABLE IF EXISTS `book`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameIdx` (`bookname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `book`
--
LOCK TABLES `book` WRITE;
/*!40000 ALTER TABLE `book` DISABLE KEYS */;
INSERT INTO `book` VALUES (1,'鍓戝湥','灏忔槑','13','hao',2013);
/*!40000 ALTER TABLE `book` ENABLE KEYS */;
UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 2014-07-23 22:04:16

我们看到文件中以“ - ”开头的都表示注释。以“ /*! ”开头,“ */ ”结尾的语句为MYSQL可以执行的注释。旨在说明mysql具有可移植性。

备份文件的上半部分的一些SET语句,这些语句将一些系统变量值赋给用户定义变量,目的是确保被恢复的数据库的系统变量和原来备份时的变量相同。

备份文件的最后几行MYSQL使用SET语句恢复服务器系统变量原来的值。

还有一点需要注意:备份文件中的MYSQL可执行注释语句,以“40101”数字开头的,这些数字代表了MYSQL版本号,告诉我们,这些语句只有在制定的MYSQL版本或者比该版本高的情况下才能执行。40101代表版本:4.01.01版本。

2、备份一个数据库中的某个表

mysqldump  -u user  -p pwd dbname table1 table2  table3... >  Backup.sql

备份多个数据库

mysqldump  -u root -p pwd  --databases dbname1  dbname2  dbname3  .... > Backup.sql

备份MYSQL中全部的数据库

mysqldump  -u root -p pwd  --all-databases > Backup.sql

3、mysqldump命令的简单使用(这里注重简单快速上手的使用,如果想看详细的使用方法,可以继续往下拉)

--add-drop-database         每个数据库创建语句前面加上DROP DATABASE语句
--add-drop-table            每个表创建语句前面加上DROP TABLE语句

默认情况下,这两个参数命令会自动加上,因此你需要注意的是:导入前一定要确认好旧数据库的确可以删除,因为备份文档里面包含了删除旧的数据库和旧的表的删除语句,因此导入前要仔细检查这一点。

-n,--no-create-db          不包含数据库的创建语句
-t,--no-create-info        不包含表的创建语句
-d,--no-data               不包含数据

这三个语句默认是不加上的,因此备份的时候需要根据自己的需求来进行选择。

4、使用mysqldump命令导出为类似csv文本格式的数据

记住关键参数-T:

~]# mysqldump -u username -T target_dir dbname tablename [option]

-T target_dir                    备份表和数据至目标目录下--fields-terminated-by=name           字段分隔符--fields-enclosed-by=name            字段引用符--fields-optionally-enclosed-by=name      字段引用符(只在CHAR、VARCHAR、TEXT等字符型字段上使用)--fields-escaped-by=name            转移字符--lines-terminated-ny=name           记录结束符

 当你备份至指定目录完成后,你会发现指定目录下面有两个文件,一个是sql文件,一个是txt文件。

案例演示:

[tidb@:vg_adn_tidbCkhsTest /tmp/bak]$mysqldump -uroot -p123456 mob_adn test -T /tmp/ --fields-terminated-by '\t' --fields-optionally-enclosed-by '"'
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[tidb@:vg_adn_tidbCkhsTest /tmp/bak]$ls
test.sql  test.txt[tidb@:vg_adn_tidbCkhsTest /tmp/bak]$cat test.txt
312    "0"    2    0    "com.apps.noise.detector"    "GB"    5    0    0    0    4    401    "{\"value\":\"Noise Detector\"}"    "1160dbc70324001ddf709052280e2de6"    2    11    ""    1    162035357    2047421827    1499358006    1501237112    ""    ""    ""

 sql文件主要是建表语句而已:

-- MySQL dump 10.13  Distrib 5.7.24, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: mob_adn
-- ------------------------------------------------------
-- Server version       5.7.24-log
/*!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 */;--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,..................

 mysqldump实际调用的就是select....into  outfile...的接口而已,并在其上添加了新的功能。

如果我们要导入这样的txt格式的文件,我们可以使用mysqlimport命令或者load data infile的方法。参考下节内容

5、使用SELECT.......INTO  OUTFILE导出文本文件

该文件被创建在服务器主机上,因此必须有文件写入权限(FILE权限),才能使用此语法

语法:

select columnlist  from Table WHERE condition  into outfile 'filename' [OPTIONS]
fields terminated by 'VALUE'
fields [OPTIONALLY]  ENCLOSED BY 'VALUE'
fields ESCAPED BY 'VALUE'
lines STARTING by 'VALUE' lines terminated by 'VALUE'

[OPTIONS]部分为可选参数,[OPTIONS]部分的语法包括FILED和LINES子句,其可能取值为:

● fields子句:在FIELDS子句中有三个子句:TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。

如果指定了FIELDS子句,则这三个子句中至少要指定一个。
(1)TERMINATED BY用来指定字段值之间的符号,例如,“TERMINATED BY ','”指定了逗号作为两个字段值之间的标志,默认为“\t”制表符。
(2)ENCLOSED BY子句用来指定包裹文件中字符值的符号,例如,“ENCLOSED BY ' " '”表示文件中字符值放在双引号之间,

若加上关键字OPTIONALLY表示所有的值都放在双引号之间,则只有CHAR和VARCHAR等字符数据字段被包括。

(3)ESCAPED BY子句用来指定转义字符,例如,“ESCAPED BY '*'”将“*”指定为转义字符,取代“\”,如空格将表示为“*N”。
● LINES子句:在LINES子句中使用TERMINATED BY指定一行结束的标志,如“LINES TERMINATED BY '?'”表示一行以“?”作为结束标志,默认值为“\n”。

LINES STARTING BY ''表示每行数据开头的字符,可以为单个或多个,默认不是有任何字符

如果在导出的时候没有制定OPTIONS的参数,导入表的时候也不用指明这些参数。

注意:这个SELECT命令导出文本文件有限制,就是想要在服务器主机之外的部分客户主机上创建结果文件,不能使用此参数。应该使用“mysql  -e”命令。(就是我们使用客户端连接远程的服务器端的操作,怪不得TIDB数据库不支持这个命令导出数据)

6、、使用“mysql  -e”命令导出文本文件.

这个就是说我们可以在命令模式下执行SQL指令,将查询结果导入到一个类似与csv格式的文本文件中。

mysql -u root -p --execute="SELECT * from tablename" dbname >filename.txt

上面的select语句必须使用双引号引起来。导出的数据使用制表符分割了。

7、最后细说讲一下mysqldump语法说明:

-h, --host=name
主机名
-P[ port_num], --port=port_num
用于连接MySQL服务器的的TCP/IP端口号

--master-data
这个选项可以把binlog的位置和文件名添加到输出中,如果等于1,将会打印成一个CHANGE MASTER命令;如果等于2,会加上注释前缀。并且这个选项会自动打开--lock-all-tables,除非同时设置了--single-transaction(这种情况下,全局读锁只会在开始dump的时候加上一小段时间,不要忘了阅读--single-transaction的部分)。在任何情况下,所有日志中的操作都会发生在导出的准确时刻。这个选项会自动关闭--lock-tables。

-x, --lock-all-tables
锁定所有库中所有的表。这是通过在整个dump的过程中持有全局读锁来实现的。会自动关闭--single-transaction和--lock-tables。

--single-transaction
通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎(目前只有InnoDB)时才可以工作;其他引擎不能保证导出是一致的。当导出开启了--single-transaction选项时,要确保导出文件有效(正确的表数据和二进制日志位置),就要保证没有其他连接会执行如下语句:ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE,这会导致一致性快照失效。这个选项开启后会自动关闭--lock-tables。

-l, --lock-tables
对所有表加读锁。(默认是打开的,用--skip-lock-tables来关闭,上面的选项会把关闭-l选项)

-F, --flush-logs
在开始导出前刷新服务器的日志文件。注意,如果你一次性导出很多数据库(使用 -databases=或--all-databases选项),导出每个库时都会触发日志刷新。例外是当使用了--lock-all-tables或--master-data时:日志只会被刷新一次,那个时候所有表都会被锁住。所以如果你希望你的导出和日志刷新发生在同一个确定的时刻,你需要使用--lock-all-tables,或者--master-data配合--flush-logs。

--delete-master-logs
备份完成后删除主库上的日志。这个选项会自动打开``--master-data`。

--opt
同-add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys。(默认已开启,--skip-opt关闭表示这些选项保持它的默认值)应该给你为读入一个MySQL服务器的尽可能最快的导出,--compact差不多是禁用上面的选项。

-q, --quick
不缓冲查询,直接导出至stdout。(默认打开,用--skip-quick来关闭)该选项用于转储大的表。
--set-charset
将SET NAMES default_character_set加到输出中。该选项默认启用。要想禁用SET NAMES语句,使用--skip-set-charset。
--add-drop-tables
在每个CREATE TABLE语句前添加DROP TABLE语句。默认开启。
--add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。默认开启。
--create-option
在CREATE TABLE语句中包括所有MySQL表选项。默认开启,使用--skip-create-options来关闭。
-e, --extended-insert
使用全新多行INSERT语法,默认开启(给出更紧缩并且更快的插入语句)

-d, --no-data
不写入表的任何行信息。如果你只想得到一个表的结构的导出,这是很有用的。

--add-drop-database
在create数据库之前先DROP DATABASE,默认关闭,所以一般在导入时需要保证数据库已存在。

--default-character-set=
使用的默认字符集。如果没有指定,mysqldump使用utf8。

-B, --databases
转储几个数据库。通常情况,mysqldump将命令行中的第1个名字参量看作数据库名,后面的名看作表名。使用该选项,它将所有名字参量看作数据库名。CREATE DATABASE IF NOT EXISTS db_name和USE db_name语句包含在每个新数据库前的输出中。

--tables
覆盖--database选项。选项后面的所有参量被看作表名。

-u[ name], --user=
连接服务器时使用的MySQL用户名。

-p[password], --password[=password]
连接服务器时使用的密码。如果你使用短选项形式(-p),不能在选项和密码之间有一个空格。如果在命令行中,忽略了--password或-p选项后面的 密码值,将提示你输入一个。

注意:如果运行mysqldump没有--quick或--opt选项,mysqldump在转储结果前将整个结果集装入内存,如果转储大数据库可能会出现问题,该选项默认启用,但可以使用--skip-opt禁用。如果使用最新版本的mysqldump程序备份数据,并用于恢复到比较旧版本的mysql服务器中,则不要使用--opt选项。

备份的时候-F和 -l (小写字母L)经常配合一起使用,-F表示备份前刷新日志,方便恢复过程。-l选项在备份期间使用,使得数据无法被更新。从而使备份的数据保持一致性。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这