星彩彩票app下载:将MySQL数据导入到SQL,MySQL数据

随着时代的进步,社会的发展,各种技术层出不穷五花八门乱七八糟数不胜数(写作文呢!!!)

13.1、数据备份

MySQL命名规则
http://www.jb51.net/article/47617.htm
1) 表名一般使用小写,如果有多个单词,使用下划线作为分割
2) 字段名和表名一样的命名规则

不扯废话,简单而言,很多公司都会同时使用多种数据库,因此数据在不同数据库之间导入导出就成为一个让人蛋疼的问题,对于周期行的需求,可以开发专门的程序处理,但是对于偶尔不确定性的需求,就到了需要DBA献身的时候啦,当需要将MySQL数据导入到SQL Server中时,该怎么搞呢?

数据备份:目的用于恢复;必须对备份数据做恢复测试。

1.DATE 和 DATETIME 区别

当然使用工具最简单,但是我就忽略工具!!!

  备份类型:

DATE: 只有日期 2016-12-12
DATETIME: 日期 具体时间 2016-12-12 23:25:02

在MySQL中创建测试数据:

  热备份:在线备份,读、写不受影响;

  温备份:仅可以执行读操作;

  冷备份:离线备份;读、写操作均中止;

2.修改表名

create table tb001(c1 int auto_increment primary key,c2 varchar(2000));
insert into tb001(c2) select 'abcrn';
insert into tb001(c2) select '你好啊rn';
insert into tb001(c2) select '你好啊n';
insert into tb001(c2) select '双引号"';
insert into tb001(c2) select '全角双引号“';
insert into tb001(c2) select '单引号''';
insert into tb001(c2) select '全角单引号’';

  物理备份和逻辑备份:

ALTER TABLE dinnertable RENAME TO dinnerTable;

##=====================================================================##
使用mysqldump来导出与MS SQL SERVER兼容的INSERT 语句:

  物理备份:复制数据文件;速度快

  逻辑备份:将数据导出至文本文件中;速度慢、丢失浮点数精度,需要重建索引;方便使用文本处理工具直接对其处理、可移植能力强。

3.Can't write; duplicate key in table 错误的解决:

mysqldump --host='192.168.166.169' --port=3358 --user='mysql_admin' --password='mysql@Admin@Pwd' --skip-add-locks --compatible=mssql --complete-insert --compact --extended-insert=false --default-character-set=utf8 -t --databases 'test' --table 'tb001' >/tmp/t4.sql

  完全备份、增量备份和差异备份;

[SQL] ALTER TABLE orders ADD CONSTRAINT fk_orders_table_id FOREIGN KEY(dinnertable_id) REFERENCES dinnerTable(id);
[Err] 1022 - Can't write; duplicate key in table '#sql-72c_3'

上面脚本的一些注释说明:

  完全备份:备份全部数据;

  差异备份:仅备份上次完全备份以来变化的数据;

  增量备份:仅备份上次完全备份或增量备份以后变化的数据;

需要修改外键约束的名字:

--compatible=mssql ##导出的SQL与MS SQL Server兼容
--complete-insert ##导出的INSERT语句包含列名
--compact ##采用精简模式,不输出各种MySQL信息
--extended-insert=false ##采用一行数据一条INSERT的方式
--default-character-set=utf8 ##指定导出的字符集
-t ##-t表示只导出数据,-d表示只导出数据结构
--databases 'test' ##数据库名称
--table 'CityMatchup' ##表名

  备份策略:选择备份方式;选择备份时间;考虑到恢复成本(恢复时长);备份成本(锁时间、备份时长、备份负载)

ALTER TABLE orders ADD CONSTRAINT fk_orders_dinnertable_id FOREIGN KEY(dinnertable_id) REFERENCES dinnerTable(id);

导出结果为:

  备份对象:数据、配置文件代码(存储过程,存储函数,触发器)、OS相关的配置文件,如crontab配置计划及相关的脚本。

之前是fk_orders_table_id

INSERT INTO "tb001" ("c1", "c2") VALUES (1,'abcrn');
INSERT INTO "tb001" ("c1", "c2") VALUES (2,'你好啊rn');
INSERT INTO "tb001" ("c1", "c2") VALUES (3,'你好啊n');
INSERT INTO "tb001" ("c1", "c2") VALUES (4,'双引号"');
INSERT INTO "tb001" ("c1", "c2") VALUES (5,'全角双引号“');
INSERT INTO "tb001" ("c1", "c2") VALUES (6,'单引号'');
INSERT INTO "tb001" ("c1", "c2") VALUES (7,'全角单引号’');

  mysql备份工具:

4.MySQL where 子句

对于列名用双引号的问题,可以使用SET QUOTED_IDENTIFIER ON 来处理,也可以使用SQLCMD加-I参数来处理
但是对文本中的单引号就无解了,MySQL中使用""来作为转义符,而SQL Server中使用两个单引号来表示一个单引号。

  mysqldump: 逻辑备份工具、MyISAM(温)、InnoDB(热备份)

  mysqldumper: 多线程的mysqldump,很难实现差异或增量备份;

  mysqlhotcopy:物理备份工具、几乎冷备;仅适用于MyISAM存储引擎

  lvm-snapshot: 接近于热备的工具:因为要先请求全局锁,而后创建快照,并在创建快照完成后释放全局锁;备份和恢复速度较快;很难实现增量备份,并且请求全局需要等待一段时间。数据文件和事务日志要在同一个逻辑卷上。

我们知道从MySQL表中使用SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中。

MySQLdump可以将数据导成INSERT语句,并提供配置兼容其他数据库的参数,但由于不同数据库转义字符不同,因此即使使用compatible=mssql也不能保证导出的脚本能在SQL Server中正常执行。

使用mysqldump命令备份

查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
你可以在WHERE子句中指定任何条件。
你可以使用AND或者OR指定一个或多个条件。
WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的if条件,根据 MySQL 表中的字段值来读取指定的数据。

##===========================================================================##

  MySQLdump是MySQL提供的一个非常有用的数据备份工具。MySQLdump命令执行时,可以将数据库备份成一个文本文件,该文件中实际上包含了多个CREATE和INSERT语句,使用这些语句可以重新创建表和插入数据。

星彩彩票app下载 1

使用SELECT INTO OUTFILE来导出数据

  MySQLdump备份数据库语句的基本语法格式为:

图片.png

SELECT * INTO OUTFILE '/tmp/tb001.txt' 
FIELDS TERMINATED BY '||--||' 
LINES TERMINATED BY '||==||' FROM test.tb001;
mysqldump -u user -h host -p password dbname [tbname,[ tbname...]] >filename.sql

如果我们想再MySQL数据表中读取指定的数据,WHERE 子句是非常有用的。
使用主键来作为 WHERE 子句的条件查询是非常快速的。
如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。

在Linux下看到的是这样:

  • 备份单个数据库中的所有表

5.使用 LIKE 子句

星彩彩票app下载 2

使用mysqldump命令备份数据库所有表

WHERE runoob_author LIKE '%jay';

虽然有点乱,但是忍啦!

#创建样例数据库
mysql> CREATE DATABASE booksDB;
Query OK, 1 row affected (0.00 sec)

mysql> USE booksDB
Database changed
mysql> CREATE TABLE books
    -> (
    ->   bk_id  INT NOT NULL PRIMARY KEY,
    ->   bk_title VARCHAR(50) NOT NULL,
    ->   copyright YEAR NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO books
    -> VALUES (11078, 'Learning MySQL', 2010),
    -> (11033, 'Study Html', 2011),
    -> (11035, 'How to use php', 2003),
    -> (11072, 'Teach youself javascript', 2005),
    -> (11028, 'Learing C  ', 2005),
    -> (11069, 'MySQL professional', 2009),
    -> (11026, 'Guide to MySQL 5.5', 2008),
    -> (11041, 'Inside VC  ', 2011);
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE authors
    -> (
    ->   auth_id     INT NOT NULL PRIMARY KEY,
    ->   auth_name  VARCHAR(20),
    ->  auth_gender CHAR(1)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO authors  
    -> VALUES (1001, 'WriterX' ,'f'),
    -> (1002, 'WriterA' ,'f'),
    -> (1003, 'WriterB' ,'m'),
    -> (1004, 'WriterC' ,'f'),
    -> (1011, 'WriterD' ,'f'),
    -> (1012, 'WriterE' ,'m'),
    -> (1013, 'WriterF' ,'m'),
    -> (1014, 'WriterG' ,'f'),
    -> (1015, 'WriterH' ,'f');
Query OK, 9 rows affected (0.04 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE authorbook
    -> (
    ->   auth_id  INT NOT NULL,
    ->   bk_id   INT NOT NULL,
    ->   PRIMARY KEY (auth_id, bk_id),
    ->   FOREIGN KEY (auth_id) REFERENCES authors (auth_id),
    ->   FOREIGN KEY (bk_id) REFERENCES books (bk_id)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO authorbook
    -> VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
    -> (1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

6.MySQL UNION 操作符
MySQL UNION 操作符的语法和实例。

然后下载文件,使用notepad 打开,选择“格式”>> "转为ANSI编码格式" ,然后另存为新文件,在SQL Server中使用BULK INSERT来导入:

完成数据插入后,输入备份命令:

MySQL UNION操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT语句会删除重复的数据。

CREATE TABLE tmp_tb001(id NVARCHAR(2000),c1 NVARCHAR(2000))
GO
BULK INSERT tmp_tb001
FROM 'D:tb002.txt'
WITH(FIELDTERMINATOR='||--||',
ROWTERMINATOR='||==||'
)
GO
SELECT * FROM tmp_tb001
[root@mylinux ~]# mysqldump -u root -p booksDB > books.sql
Enter password:
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

也可以使用SQL Server的导入导出工具来处理,主要修改分隔符。

备份完成后的文件大致内容为:

星彩彩票app下载 3

注意使用SELECT INTO OUTFILE导出文件时,NULL值被表示为N,而N在导入SQL Server时会被当初字符串“N”来处理,因此建议先建立一个完全由NVARCHAR类型列组成的表来“暂存”导入的时候,然后经过数据清理后再导入正式表中,对于懒与一列一列折腾的人来说,可以拼下SQL来获取表的所有列转换:

[root@mylinux ~]# cat books.sql 
-- MySQL dump 10.13  Distrib 5.5.56, for Linux (x86_64)
--
-- Host: localhost    Database: booksDB
-- ------------------------------------------------------
-- Server version       5.5.56-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 `authorbook`
--

DROP TABLE IF EXISTS `authorbook`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `authorbook` (
  `auth_id` int(11) NOT NULL,
  `bk_id` int(11) NOT NULL,
  PRIMARY KEY (`auth_id`,`bk_id`),
  KEY `bk_id` (`bk_id`),
  CONSTRAINT `authorbook_ibfk_1` FOREIGN KEY (`auth_id`) REFERENCES `authors` (`auth_id`),
  CONSTRAINT `authorbook_ibfk_2` FOREIGN KEY (`bk_id`) REFERENCES `books` (`bk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
...

图片.png

SELECT 
'CASE WHEN [' T1.name ']=''N'' THEN NULL ELSE [' T1.name '] END AS [' T1.name '],'
FROM sys.all_columns T1
WHERE T1.object_id=OBJECT_ID('tmp_tb001')

  可以发现,备份文件包含一些信息,文件开头首先声明了备份文件使用的mysqldump版本号;然后是备份账号的名称和主机信息,以及备份的数据库的名称,最后是MySQL服务器的版本号。

注释:UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家,每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值!

由于我们强行将N当成NULL来转换,难免会造成误伤,将真实数据就为’N‘的值变为NULL,因此导完数据后检查是必须的。

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

SQL UNION ALL 实例

最后语句为:

  备份文件中的'--'开头的是注释语句,以'/*!'开头,以'*/'结尾的语句为可执行的mysql注释,这些语句可以被MySQL执行。

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
SELECT 
CASE WHEN [id]='N' THEN NULL ELSE [id] END AS [id],
CASE WHEN [c1]='N' THEN NULL ELSE [c1] END AS [c1]
FROM tmp_tb001
  • 备份数据库中的某个表

星彩彩票app下载 4

执行结果为:

备份booksDB数据库中的books表

图片.png

星彩彩票app下载 5

[root@mylinux ~]# mysqldump -u root -p booksDB books > books_20170810.sql          
Enter password: 
[root@mylinux ~]# cat books_20170810.sql 
-- MySQL dump 10.13  Distrib 5.5.56, for Linux (x86_64)
--
-- Host: localhost    Database: booksDB
-- ------------------------------------------------------
-- Server version       5.5.56-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 `books`
--

带有 WHERE 的 SQL UNION ALL

##=======================================================================##

  • 备份多个数据库
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

导出INSERT脚本存在转义字符单引号的问题,同时导出数据不包含GO,在需要大量数据导入到SQL SERVER时存在严重的性能问题,可以尝试参考本人的《Powershell--批量拆分SQL语句为事务并批处理》来处理,但也是问题多多。

使用mysqldump备份booksDB和test数据库

7.MySQL GROUP BY 语句
分组

而导出文件然后导入的方式,需要对文件进行一次转换,文件较大时notepad 可能无法打卡,UE能稍微给力点,但面对好几个G的文本文件也是无力回天,同时NULL值处理也需要慎重对待。

[root@mylinux ~]# mysqldump -u root -p --databases  booksDB test> books_testDB_20170710.sq
l
Enter password:
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

##========================================================================##

使用mysqldump备份服务器中的所有数据库

8.Mysql 连接的使用

好啦,是时候上妹子啦。

[root@mylinux ~]# mysqldump  -u root -p --all-databases > alldbinMySQL.sql          
Enter password:

INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

星彩彩票app下载 6

  mysqldump还有一些其他选型可以用来指定备份过程,常用选项有:

星彩彩票app下载 7


–all-databases , -A
导出全部数据库
mysqldump -uroot -p –all-databases

–all-tablespaces , -Y
导出全部表空间
mysqldump -uroot -p –all-databases –all-tablespaces

–no-tablespaces , -y
不导出任何表空间信息
mysqldump -uroot -p –all-databases –no-tablespaces

–add-drop-database
每个数据库创建之前添加drop数据库语句
mysqldump -uroot -p –all-databases –add-drop-database

–add-drop-table
每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用–skip-add-drop-table取消选项)
mysqldump -uroot -p –all-databases (默认添加drop语句)
mysqldump -uroot -p –all-databases –skip-add-drop-table (取消drop语句)

–add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态,使用–skip-add-locks取消选项)
mysqldump -uroot -p –all-databases (默认添加LOCK语句)
mysqldump -uroot -p –all-databases –skip-add-locks (取消LOCK语句)

–comments
附加注释信息。
默认为打开,可以用–skip-comments取消mysqldump -uroot -p –all-databases (默认记录注释)mysqldump -uroot -p –all-databases –skip-comments (取消注释)

–compact
导出更少的输出信息(用于调试),去掉注释和头尾等结构,可以使用选项:–skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keys
mysqldump -uroot -p –all-databases –compact

–complete-insert, -c
使用完整的insert语句(包含列名称)。这么做能提高插入效率,但是可能会受到max_allowed_packet参数的影响而导致插入失败。
mysqldump -uroot -p –all-databases –complete-insert

–compress, -C在客户端和服务器之间启用压缩传递所有信息mysqldump -uroot -p –all-databases –compress

–databases, -B
导出几个数据库。参数后面所有名字参量都被看作数据库名。
mysqldump -uroot -p –databases test mysql

–debug
输出debug信息,用于调试。
默认值为:d:t:o,/tmp/mysqldump.trace
mysqldump -uroot -p –all-databases –debug
mysqldump -uroot -p –all-databases –debug="d:t:o,/tmp/debug.trace"

–debug-info
输出调试信息并退出
mysqldump -uroot -p –all-databases –debug-info

–default-character-set
设置默认字符集,默认值为utf8
mysqldump -uroot -p –all-databases –default-character-set=latin1

–delayed-insert
采用延时插入方式(INSERT DELAYED)导出数据
mysqldump -uroot -p –all-databases –delayed-insert

–events, -E
导出事件
mysqldump -uroot -p –all-databases –events

–flush-logs
开始导出之前刷新日志
请注意:假如一次导出多个数据库(使用选项–databases或者–all-databases),将会逐个数据库刷新日志。除使用–lock-all-tables或者–master-data外。在这种情况下,日志将会被刷新一次,相应的所以表同时被锁定。因此,如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs。
mysqldump -uroot -p –all-databases –flush-logs

–flush-privileges
在导出mysql数据库之后,发出一条FLUSH PRIVILEGES 语句。为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
mysqldump -uroot -p –all-databases –flush-privileges

–force
在导出过程中忽略出现的SQL错误
mysqldump -uroot -p –all-databases –force

–host, -h
需要导出的主机信息
mysqldump -uroot -p –host=localhost –all-databases

–ignore-table
不导出指定表。
指定忽略多个表时,需要重复多次,每次一个表。每个表必须同时指定数据库和表名。
例如:–ignore-table=database.table1 –ignore-table=database.table2 ……
mysqldump -uroot -p –host=localhost –all-databases –ignore-table=mysql.user

–lock-all-tables, -x
提交请求锁定所有数据库中的所有表,以保证数据的一致性。
这是一个全局读锁,并且自动关闭–single-transaction 和–lock-tables 选项。
mysqldump -uroot -p –host=localhost –all-databases –lock-all-tables

–lock-tables, -l
开始导出前,锁定所有表。
用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB,–single-transaction是一个更好的选择,因为它根本不需要锁定表。
请注意当导出多个数据库时,–lock-tables分别为每个数据库锁定表。因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
mysqldump -uroot -p –host=localhost –all-databases –lock-tables

–no-create-db, -n
只导出数据,而不添加CREATE DATABASE 语句。
mysqldump -uroot -p –host=localhost –all-databases –no-create-db

–no-create-info, -t只导出数据,而不添加CREATE TABLE 语句
mysqldump -uroot -p –host=localhost –all-databases –no-create-info

–no-data, -d
不导出任何数据,只导出数据库表结构
mysqldump -uroot -p –host=localhost –all-databases –no-data

–password, -p
连接数据库密码

–port, -P
连接数据库端口号

–user, -u
指定连接的用户名。



作者:HoLau
链接:http://www.jianshu.com/p/18db0fbb02b8

图片.png


使用mysqlhotcopy工具快速备份

星彩彩票app下载 8

  MySQLhotcopy是一个Perl脚本。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库后单个表的最快的途径,但是只能运行在数据库目录所在的机器上,并且只能备份MyISAM类型的表,其语法格式为:

图片.png

mysqlhotcopy db_name_1,...db_name_n /[ath/to new_directory

星彩彩票app下载 9

使用mysqlhotcopy备份test数据库到/usr/backup目录下

图片.png

mysqlhotcopy  -u root -p test /usr/backup

= 和 != 运算符对NULL是不起作用的:

判断值是否为 NULL,必须使用IS NULL和IS NOT NULL

13.2、数据恢复

MySQL正则表达式

使用MySQL命令恢复

MySQL 事务

 对于已经备份的包含CREATE 、INSERT语句的文本,可以使用MySQL命令导入到数据库中。MySQL命令直接执行文件汇总的这些语句,其语法为:

MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!

mysql -u user -p [dbname] <filename.sql
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
事务用来管理insert,update,delete语句

将books.sql文件中的备份导入到数据库中

一般来说,事务是必须满足4个条件(ACID):Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

mysql -u root-p booksDB < books.sql

在Mysql控制台使用事务来操作
1,开始一个事务

 执行该语句前,必须先在MySQL服务器中创建booksDB数据库,如果不存在恢复过程将会出错。如果已经登录MySQL服务器,可以使用source命令导入SQL文件,语法格式为:

start transaction
source filename

2, 做保存点

使用root用户登录到服务器,然后使用souce导入本地的备份文件books.sql

savepoint 保存点名称
use booksdb;
source books.sql;

3, 操作
4,可以回滚,可以提交,没有问题,就提交,有问题就回滚。

MySQL 复制表

使用mysqlhotcopy快速恢复

尝试以下实例来复制表 runoob_tbl 。
步骤一:
获取数据表的完整结构。

 MySQLhotcopy备份后的文件可以用来恢复数据库,在MySQL服务器停止运行时,将备份的数据库文件复制到MySQL存放数据的位置,重启MySQL服务器即可。如果以根用户执行该操作,必须指定数据库文件的所有者,输入语句为:

步骤二:
修改SQL语句的数据表名,并执行SQL语句。

chown -R mysql.mysql /var/lib/mysql/dbname

步骤三:
执行完第二步骤后,你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。

从mysqlhotcopy拷贝的备份恢复数据库

MySQL 元数据

cp –R  /usr/backup/test usr/local/mysql/data

查询结果信息: SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息: 包含了数据库及数据表的结构信息。
MySQL服务器信息: 包含了数据库服务器的当前状态,版本号等。

执行该语句,重启服务器,MySQL将恢复到备份状态。

获取服务器元数据

13.3、表的导入和导出

SELECT VERSION( )   服务器版本信息
SELECT DATABASE( )  当前数据库名 (或者返回空)
SELECT USER( )  当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES  服务器配置变量

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

MySQL 序列使用
MySQL序列是一组整数:1, 2, 3, ...,由于一张数据表只能有一个字段自增主键, 如果你想实现其他字段也实现自动增加,就可以使用MySQL序列来实现。
使用AUTO_INCREMENT

 MySQL数据库导出数据时,允许使用包含导出定义的SELECT语句进行数据的到处操作。该文件被创建到服务器主机上,因此必须拥有文件写入权限,才能使用此语法。SELECT..INTO OUTFILE语句基本格式为:

MySQL中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。

SELECT columnlist FROM table WHERE condition INTO OUTFILE 'file_name' [OPTIONS]
 --OPTIONS 选项
  FIELDS TERMINATED BY ‘value’
  FIELDS [OPTIONALLY] ENCLOSED BY 'value'
  FIELDS ESCAPED BY 'value'
  LINES STARTING BY 'value'
  LINES TERMINATED BY 'value'

MySQL 及 SQL 注入

FIELDS TERMINATED BY ‘value’:设置字段间的分隔符

FIELDS [OPTIONALLY] ENCLOSED BY 'value':设置字段的包围符,只能为单个字符

FIELDS ESCAPED BY 'value':设置如何写入或读取特殊字符,只能为单个字符

LINES STARTING BY 'value':设置每行数据开头的字符

LINES TERMINATED BY 'value':设置每行数据结尾的字符

如果通过网页获取用户输入的数据并将其插入一个MySQL数据库,那么就有可能发生SQL注入安全的问题。
防止SQL注入,并通过脚本来过滤SQL中注入的字符。
所谓SQL注入,就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。
我们永远不要信任用户的输入,我们必须认定用户输入的数据都是不安全的,我们都需要对用户输入的数据进行过滤处理。

 FIELDS和LINES两个子句自选,如果都被指定,FIELDS必须位于LINES的前面。

// 设定$name 中插入了我们不需要的SQL语句
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");

  SELECT..INTO OUTFILE语句可以快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件。不能使用SELECT..INTO OUTFILE。应该在主机上使用比如'mysql -e 'SELECT...' > fine_name'的命,来生成文件。

防止SQL注入

使用SELECT...INTO OUTFILE将test数据库中的books表中的记录导出到文本文件

Like语句中的注入

mysql> SELECT *  FROM booksDB.books INTO OUTFILE "/tmp/book0.txt";  
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> 
mysql>  show global variables like '%secure%';  
 ------------------ ------- 
| Variable_name    | Value |
 ------------------ ------- 
| secure_auth      | OFF   |
| secure_file_priv | NULL  |
 ------------------ ------- 
2 rows in set (0.00 sec)

[root@mylinux ~]# vim /etc/my.cnf
   添加 secure_file_priv=/tmp/
[root@mylinux ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@mylinux ~]# 
[root@mylinux ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.56-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>  show global variables like '%secure%';                     
 ------------------ ------- 
| Variable_name    | Value |
 ------------------ ------- 
| secure_auth      | OFF   |
| secure_file_priv | /tmp/ |
 ------------------ ------- 
2 rows in set (0.00 sec)

mysql> SELECT *  FROM booksDB.books INTO OUTFILE "/tmp/book0.txt";            
Query OK, 8 rows affected (0.00 sec)

[root@mylinux tmp]# cat book0.txt 
11026   Guide to MySQL 5.5      2008
11028   Learing C       2005
11033   Study Html      2011
11035   How to use php  2003
11041   Inside VC       2011
11069   MySQL professional      2009
11072   Teach youself javascript        2005
11078   Learning MySQL  2010

like查询时,如果用户输入的值有""和"%",则会出现这种情况:用户本来只是想查询"abcd",查询结果中却有"abcd_"、"abcde"、"abcdf"等等;用户要查询"30%"(注:百分之三十)时也会出现问题。

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == %something_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

使用SELECT...INTO OUTFILE将booksDB数据库中的books表中的记录导出到文本文件,使用FIELDS选项和LINES选项,要求字段之间使用逗号‘,’间隔,所有字段值用双引号括起来,定义转义字符定义为单引号‘’’

MySQL 导出数据

mysql> SELECT * FROM booksDB.books INTO OUTFILE "/tmp/book1.txt"
    ->   FIELDS 
    ->     TERMINATED BY ','
    ->     ENCLOSED BY '"'
    ->     ESCAPED BY '''
    ->   LINES 
    ->     TERMINATED BY 'rn';
Query OK, 8 rows affected (0.00 sec)

[root@mylinux tmp]# cat book1.txt 
"11026","Guide to MySQL 5.5","2008"
"11028","Learing C  ","2005"
"11033","Study Html","2011"
"11035","How to use php","2003"
"11041","Inside VC  ","2011"
"11069","MySQL professional","2009"
"11072","Teach youself javascript","2005"
"11078","Learning MySQL","2010"

MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。
使用 SELECT ... INTO OUTFILE 语句导出数据
以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/tutorials.txt 文件中:

使用SELECT...INTO OUTFILE将booksDB数据库中的books表中的记录导出到文本文件,使用LINES选项,要求每行记录以字符串“> ”开始,以“<end>”字符串结尾

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/tutorials.txt';
mysql> SELECT * FROM booksDB.books INTO OUTFILE "/tmp/book2.txt"
    ->   LINES 
    ->     STARTING BY '> '
    ->     TERMINATED BY '<end>rn';
Query OK, 8 rows affected (0.00 sec)

[root@mylinux tmp]# cat book2.txt  
> 11026 Guide to MySQL 5.5      2008<end>
> 11028 Learing C       2005<end>
> 11033 Study Html      2011<end>
> 11035 How to use php  2003<end>
> 11041 Inside VC       2011<end>
> 11069 MySQL professional      2009<end>
> 11072 Teach youself javascript        2005<end>
> 11078 Learning MySQL  2010<end>

你可以通过命令选项来设置数据输出的指定格式,以下实例为导出 CSV 格式:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY 'rn';

使用mysqldump导出文本文件

SELECT ... INTO OUTFILE 语句有以下属性:

 mysqldump创建一个包含创建表的CREATE TABLE语句的tablename.sql文件和一个包含其数据的tablename.txt文件,其导出文本文件的基本语法为:

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作,SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT ... INTO OUTFILE,为了将文件读回数据库,使用LOAD DATA INFILE。
SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
在UNIX中,该文件被创建后是可读的,权限由MySQL服务器所拥有。这意味着,虽然你就可以读取该文件,但可能无法将其删除。

mysqldump -T path -u root -p dbname [tables] [options] #指定T才可以导出纯文本文件
--options 选项
--fields-terninated-by=value
--fields-enclosed-by=value
--fields-optionally-by=value
--fields-escaped-by=value
--lines-terninated-by=value

导出SQL格式的数据
导出SQL格式的数据到指定文件,如下所示:

使用mysqldump将booksDB数据库中的books表中的记录导出到文本文件

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******
[root@mylinux ~]# mysqldump -T /tmp -u root -p booksDB books
Enter password: 
[root@mylinux ~]# cd /tmp
[root@mylinux tmp]# ls
agent_cmd.sock  book1.txt  books.sql  dir    person0.txt
book0.txt       book2.txt  books.txt  fstab
[root@mylinux tmp]# cat books.sql 
-- MySQL dump 10.13  Distrib 5.5.56, for Linux (x86_64)
--
-- Host: localhost    Database: booksDB
-- ------------------------------------------------------
-- Server version       5.5.56-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' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `books`
--

DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `books` (
  `bk_id` int(11) NOT NULL,
  `bk_title` varchar(50) NOT NULL,
  `copyright` year(4) NOT NULL,
  PRIMARY KEY (`bk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!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 2017-08-11 17:51:06
[root@mylinux tmp]# cat books.txt 
11026   Guide to MySQL 5.5      2008
11028   Learing C       2005
11033   Study Html      2011
11035   How to use php  2003
11041   Inside VC       2011
11069   MySQL professional      2009
11072   Teach youself javascript        2005
11078   Learning MySQL  2010

将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。
在源主机上执行以下命令,将数据备份至 dump.txt 文件中:

使用mysqldump命令将test数据库中的person表中的记录导出到文本文件,使用FIELDS选项,要求字段之间使用逗号‘,’间隔,所有字符类型字段值用双引号括起来,定义转义字符定义为问号‘?’,每行记录以回车换行符“rn”结尾

$ mysqldump -u root -p database_name table_name > dump.txt
password *****
[root@mylinux tmp]# mysqldump -T /tmp  -u root -p booksDB books --fields-terminated-by=, -
-fields-optionally-enclosed-by=" --fields-escaped-by=? --lines-terminated-by=rn
Enter password: 
[root@mylinux tmp]# ls
agent_cmd.sock  book1.txt  books.sql  dir    person0.txt
book0.txt       book2.txt  books.txt  fstab
[root@mylinux tmp]# cat book.txt
cat: book.txt: 没有那个文件或目录
[root@mylinux tmp]# cat books.txt
11026,"Guide to MySQL 5.5",2008
11028,"Lea?ring C  ",2005
11033,"Study Html",2011
11035,"How to use php",2003
11041,"Inside VC  ",2011
11069,"MySQL p?rofessional",2009
11072,"Teach youself javasc?ript",2005
11078,"Lea?rning MySQL",2010
[root@mylinux tmp]#

如果完整备份数据库,则无需使用特定的表名称。
如果你需要将备份的数据库导入到MySQL服务器中,可以使用以下命令,使用以下命令你需要确认数据库已经创建:

使用mysql命令导出文本文件

$ mysql -u root -p database_name < dump.txt
password *****

  如果MySQL服务器是单独的机器,用户是在一个client上进行操作,用户要把数据结果导入到client机器上,可以使用mysql -e语句,其语法格式为:

你也可以使用以下命令将导出的数据直接导入到远程的服务器上,但请确保两台服务器是相通的,是可以相互访问的:</p>

mysql -u root -p --execute="SELECT 语句" dbname >filename.txt
$ mysqldump -u root -p database_name 
       | mysql -h other-host.com database_name

使用mysql语句导出booksDB数据库中books表中的记录到文本文件

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

[root@mylinux tmp]# mysql -u root -p --execute="SELECT * FROM books;" booksDB > /tmp/books
4.txt
Enter password: 
[root@mylinux tmp]# cat books4.txt
bk_id   bk_title        copyright
11026   Guide to MySQL 5.5      2008
11028   Learing C       2005
11033   Study Html      2011
11035   How to use php  2003
11041   Inside VC       2011
11069   MySQL professional      2009
11072   Teach youself javascript        2005
11078   Learning MySQL  2010

MySQL 导入数据

MySQL中可以使用两种简单的方式来导入MySQL导出的数据。

使用mysql命令导出booksDB数据库中books表中的记录到文本文件,使用--vertical参数显示结果

使用 LOAD DATA 导入数据
MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ,将该文件中的数据插入到当前数据库的 mytbl 表中。

[root@mylinux tmp]# mysql -u root -p --vertical --execute="SELECT * FROM books;" booksDB >
 /tmp/books5.txt 
Enter password: 
[root@mylinux tmp]# cat books5.txt 
*************************** 1. row ***************************
    bk_id: 11026
 bk_title: Guide to MySQL 5.5
copyright: 2008
*************************** 2. row ***************************
    bk_id: 11028
 bk_title: Learing C  
copyright: 2005
*************************** 3. row ***************************
    bk_id: 11033
 bk_title: Study Html
copyright: 2011
*************************** 4. row ***************************
    bk_id: 11035
 bk_title: How to use php
copyright: 2003
*************************** 5. row ***************************
    bk_id: 11041
 bk_title: Inside VC  
copyright: 2011
*************************** 6. row ***************************
    bk_id: 11069
 bk_title: MySQL professional
copyright: 2009
*************************** 7. row ***************************
    bk_id: 11072
 bk_title: Teach youself javascript
copyright: 2005
*************************** 8. row ***************************
    bk_id: 11078
 bk_title: Learning MySQL
copyright: 2010
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

使用LOAD DATA INFILE导入文本文件

使用 mysqlimport 导入数据
mysqlimport客户端提供了LOAD DATA INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。
从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令:

LOAD DATA语句基本格式为:

$ mysqlimport -u root -p --local database_name dump.txt
password *****
LOAD DATA INFILE 'filename.txt' INTO TABLE tablename  [OPTIONS] [IGNORE number LINES]
 --OPTIONS 选项
  FIELDS TERMINATED BY ‘value’
  FIELDS [OPTIONALLY] ENCLOSED BY 'value'
  FIELDS ESCAPED BY 'value'
  LINES STARTING BY 'value'
  LINES TERMINATED BY 'value'

使用LOAD DATA命令将/tmp/books0.txt文件中的数据导入到booksDB数据库中的books表

mysql> USE booksDB;
Database changed

mysql> DELETE FROM books;
Query OK, 8 rows affected (0.02 sec)

mysql> LOAD DATA  INFILE '/tmp/book0.txt' INTO TABLE booksDB.books; 
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT *FROM books;
 ------- -------------------------- ----------- 
| bk_id | bk_title                 | copyright |
 ------- -------------------------- ----------- 
| 11026 | Guide to MySQL 5.5       |      2008 |
| 11028 | Learing C                |      2005 |
| 11033 | Study Html               |      2011 |
| 11035 | How to use php           |      2003 |
| 11041 | Inside VC                |      2011 |
| 11069 | MySQL professional       |      2009 |
| 11072 | Teach youself javascript |      2005 |
| 11078 | Learning MySQL           |      2010 |
 ------- -------------------------- ----------- 
8 rows in set (0.00 sec)

使用mysqlimport导入文本文件

  使用MySQLimport可以导入文本文件,并不需要登录MySQL客户端,其命令的基本语法格式为:

mysqlimport -u root -p dbname filename.txt [options]
--options 选项
--fields-terninated-by=value
--fields-enclosed-by=value
--fields-optionally-by=value
--fields-escaped-by=value
--lines-terninated-by=value

使用mysqlimport命令将/tmp目录下的books.txt文件内容导入到booksDB数据库中,字段之间使用逗号‘,’间隔,字符类型字段值用双引号括起来,定义转义字符定义为问号‘?’,每行记录以回车换行符“rn”结尾

mysqlimport -u root -p booksDB /tmp/books.txt --fields-terminated-by=, --fields-optionally-enclosed-by=" --fields-escaped-by=? --lines-terminated-by=rn

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:星彩彩票app下载:将MySQL数据导入到SQL,MySQL数据

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。