威尼斯人网上投注:MySQL数据导出工具mysqldump介绍

by admin on 2019年9月22日

一.概述

    mysqldump客户端工具是用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表或装载表的sql语句。mysqldump目前是mysql中最常用的备份工具。
  三种方式来调用mysqldump,命令如下:
威尼斯人网上投注 1
  上图第一种是备份单个数据库或者库中部分数据表(从备份方式上,比sqlserver要灵活一些,虽然sql
server有文件组备份)。第二种是备份指定的一个或者多个数据库。第三种是备份所有数据库。
  1.连接导出,下面将test数据库导出为test.txt文件,导出位置在data目录下

[root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p test > test.txt

威尼斯人网上投注 2威尼斯人网上投注 3

  上图显示: 导出到test.txt文件里,
数据有几部份sql语句,包括:(1)有判断表存在删除,(2)导出表结构和表数据,(3)导前加table
write锁,导完释放。通过下面帮助命令可以看到默认设置。

 [root@hsr data]# /usr/local/mysql/bin/mysqldump --help

威尼斯人网上投注 4  2.
输出内容选项

-n, –no-create-db

不包含数据库的创建语句

-t, –no-create-info

不包含数据表的创建语句

-d,–no-data

不包含数据

    下面演示导出test库的a表,不包含数据:

[root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p -d  test a > a.txt

威尼斯人网上投注 5

    上图显示,使用more 查看a.txt,内容只有表结构。
  3. 使用
–compact选项使得结果简洁,不包括默认选项中的各种注释,下面还是演示a表:

[root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p --compact  test a > a.txt

威尼斯人网上投注 6

  4. 使用-c –complete-insert 选项,使insert语句包括字段名称

[root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p -c --complete-insert  test b > b.txt

威尼斯人网上投注 7

  5. 使用-T选项将指定数据表中的数据备份为单纯的数据文本和建表sql,
两个文件。

     [root@hsr data]# midir bak
        [root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p   test b -T ./bak
        Enter password: 
        mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute 
                      this statement when executing 'SELECT INTO OUTFILE'

 --上面的语句报错,查找错误信息中的字段设置
SHOW VARIABLES LIKE '%secure%';

威尼斯人网上投注 8

    secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and
LOAD_FILE()传到哪个指定目录的。

                   (1) 当secure_file_priv的值为null ,表示限制mysqld
不允许导入|导出。

                   (2) 当secure_file_priv的值为/tmp/ ,表示限制mysqld
的导入|导出只能发生在/tmp/目录下。

                   (3
)当secure_file_priv的值没有具体值时,表示不对mysqld
的导入|导出做限制。

    下面来设置my.cnf文件,加上导入位置,位置在/tmp 目录下,如下图:
威尼斯人网上投注 9威尼斯人网上投注 10

-- 再次导出,导出路径在/tmp下
[root@hsr data]#  /usr/local/mysql/bin/mysqldump -uroot -p   test b -T /tmp

威尼斯人网上投注 11

    使用more 查看文件,b.sql中包含了表架构, b.txt包含数据。
威尼斯人网上投注 12

威尼斯人网上投注 13

   6.  字符集选项

    –default-character-set=name
选项可以设置导出的客户端字符集。这个选项很重要,如果客户端字符集和数据库字符集不一致,有可能成为乱码,使得备份文件无法恢复。

[root@hsr data]#  /usr/local/mysql/bin/mysqldump -uroot -p  --compact --default-character-set=utf8 test >test.txt

    威尼斯人网上投注 14

  7. 其他常用选项

    (1) -F –flush-logs(备份前刷新日志) 
备份前将关闭旧日志,生成新日志。恢复的时候直接从新日志开始进行重做,方便恢复过程。

    (2) -l –lock-tables(给所有表加读锁)
使得数据无法被更新,从而使备份的数据保持一致性(可以导致大量长时间阻塞)。

一.概述

    mysqladmin是一个执行管理操作的客户端程序。用来检要服务的配置和当前的状态,创建并删除数据库等。功能与mysql客户端类似,主要区别在于它更侧重于一些管理方面的功能。

  1. 查找mysqladmin工具

    –同样还是找到安装基地址bin目录下:

     [root@hsr bin]# pwd
     /usr/local/mysql/bin
    

    下面是可以执行的命令行以及描述

     [root@hsr bin]# ./mysqladmin
     语法如下:mysqladmin [option] command [command option] command ......
    

    威尼斯人网上投注 15

2 查看mysql 活动线程列表

[root@hsr bin]# ./mysqladmin -uroot -p processlist    
    or 
 [root@hsr bin]# ./mysqladmin -uroot -p  proc

  威尼斯人网上投注 16

  1. 查看mysql 活动线程列表 每秒一次。 ctrl+c 停止

    [root@hsr bin]# ./mysqladmin -uroot -p -i 1 processlist

    威尼斯人网上投注 17

  1. 查看服务器的状态, 每两秒查看一次状态,总共重复5次。

         Uptime                    MySQL服务器已经运行的秒数
         Threads                 
 活跃线程(客户)的数量 包括sleep线程
         Questions                从mysqld启动起来自客户问题的数量 
         Slow queries            
已经超过long_query_time秒的查询数量 
         Opens                      mysqld已经打开了多少表 
         Flush tables             flush …, refresh和reload命令数量 
         Open tables              现在被打开的表数量

[root@hsr bin]# ./mysqladmin -uroot -p -i 2 -c 5 status

    威尼斯人网上投注 18

  1. 修改root密码

    [root@hsr bin]# ./mysqladmin -uroot -p password 1234567

    威尼斯人网上投注 19
6.检查mysql服务是否可用

[root@hsr bin]# ./mysqladmin -uroot -p ping

    威尼斯人网上投注 20
7.检查当前服务版本

[root@hsr bin]# ./mysqladmin -uroot -p version

    威尼斯人网上投注 21

8.检查当前服务状态值

[root@hsr bin]# ./mysqladmin -uroot -p extended-status

    威尼斯人网上投注 22

  1. kill掉mysql线程ID

  威尼斯人网上投注 23

  1. 删除数据库 daba-test

         mysqladmin -uroot -p drop daba-test

  1. 重载权限信息

         mysqladmin -uroot -p reload

更多使用方法参看:mysqladmin文档    

mysqldump常用于MySQL数据库逻辑备份,导出数据库表的结构和内容。

一.mysql 连接工具  

  在mysq提供的工具中,DBA使用最频繁的莫过于mysql。这里的mysql是指连接数据库的客户端工具。

  1.1 连接选项

-u, — user=name 指定用户名
-p ,–password[=name] 指定密码
-h ,–host=name 指定服务器IP或者域名
-p, –port=# 指定连接端口

在实际连接中命令如下:mysql -u 用户名 -p 密码
[root@hsr ~]#  mysql -u root -p

-- 完整的连接命令如下
[root@hsr ~]#  mysql -u root -p -h 172.168.18.201 -P 3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

  1.2 执行选项
    通过 “-e,–execute=name 执行sql语句并退出”
此选项不需要连接到mysql数据库后再执行,对于一些批处理脚本很方便,下面是从客户端直接查询mysql数据库中user表的User和Host字段:

[root@hsr ~]#  mysql -u root -p  mysql -e "select User,Host from user"
Enter password: 

    威尼斯人网上投注 24
  连接多个sql 语句用英文分号隔开如   select User,Host from user;
select xxx。

导出要用到MySQL的mysqldump工具,基本用法是:  

常用参数

-A, –all-databases
导出所有数据库
–add-drop-database
在创建数据库之前先DROP数据库
–add-drop-table
在创建表之前先drop表,默认true
–add-locks
在insert语句前加锁,默认true
–compact
导出更少的输出信息(用于调试)。去掉注释和头尾等结构
-c, –complete-insert
使用完整的insert语句(包含列名称)。
-B, –databases
导出几个数据库。参数后面所有名字参量都被看作数据库名。导出文件中会有’USE
db_name;’
–default-character-set=name
设置默认字符集
-e, –extended-insert
使用具有多个VALUES列的INSERT语法。这样使导出文件更小,并加速导入时的速度,默认true
**–ignore-table=name **
忽略指定的表不导出
–insert-ignore
在插入行时使用insert ignore
-l, –lock-tables
开始导出前,锁定所有表
–dump-slave
将主库的binlog位置和文件名追加到导出数据的文件中,该参数在在从服务器上执行,相当于执行show
slave status.当设置为1时,将会以CHANGE
MASTER命令输出到数据文件;设置为2时,会在change前加上注释。
–master-data[=#]
该选项将当前服务器的binlog的位置和文件名追加到输出文件中(show master
status)。如果为1,将会输出CHANGE MASTER 命令;如果为2,输出的CHANGE
MASTER命令前添加注释信息。
-n, –no-create-db
只导出数据,不添加create database语句,默认false
-t, –no-create-info
只导出数据,不添加create table语句,默认false
-d, –no-data
不导出任何数据,只导出数据库表结构
-q, –quick
不缓冲查询,直接导出到标准输出。默认true
**–replace **
插入数据时用replace
–set-gtid-purged[=name]
是否添加SET@@GLOBAL.GTID_PURGED,默认auto
–single-transaction
使用一致性的快照来导出数据,不锁表,默认false
–tables
覆盖–databases (-B)参数,指定需要导出的表名
-w, –where=name
按照条件导出,只能导出单个表
-u -h -P -p
用户名,主机名,端口,密码

二. myisampack(MyISAM表压缩工具)

  myisampack是一个表压缩工具。在压缩之前需要确认mysqld已关闭或者要压缩的表不会有其他的sql操作;而且压缩过程会很占用cpu资源,建议在服务器空闲的状态进行。

  可以将数据文件压缩到40%-70%,当以后使用表时,解压缩列需要的信息被读入内存。当访问具体的记录时性能会更好,因为你只需要解压缩一个记录。压缩后的表也将成为一个只读表,不能进行DML操作。

--压缩前,查看test库中表的数据文件大小,其中有个a表是myisam类型, 将压缩MYD文件

    [root@hsr ~]# cd /usr/local/mysql/data

    [root@hsr data]# cd test
    [root@hsr test]# ls -ltr
    总用量 24
    -rw-r----- 1 mysql mysql   61 8月  24 16:43 db.opt
    -rw-r----- 1 mysql mysql 8566 8月  28 14:29 a.frm
    -rw-r----- 1 mysql mysql 1024 8月  28 14:32 a.MYI
    -rw-r----- 1 mysql mysql  456 8月  28 14:32 a.MYD

-- myisampack先到基地址里面找到该命令(用rpm安装可以在根目录下直接用该命令)
[root@hsr ~]# cd /usr/local/mysql/bin

威尼斯人网上投注 25

-- 压缩a表,提示太小无法压缩
[root@hsr bin]# ./myisampack /usr/local/mysql/data/test/a
/usr/local/mysql/data/test/a is too small to compress
如果压缩成功,该表就只能读,不能写。可以在查看MYD文件大小。

shell> mysqldump [OPTIONS] database [tables]  

常用方法

1、导出所有库
mysqldump -uroot -p123 –single-transaction –all-databases
>all.sql
该命令会导出包括系统数据库在内的所有数据库
2、导出单个库
mysqldump -uroot -p123 –single-transaction test > test.sql
3、导出多个数据库
mysqldump -uroot -p123 –single-transaction –databases db1 db2>
db.sql
导出文件中会有’USE db1;USE db2′
4、导出单表
mysqldump -uroot -p123 –single-transaction test table_1 >
table_1.sql
5、导出多表
mysqldump -uroot -p123 –single-transaction test –tables table_1
table_2> table.sql
6、只导表结构
mysqldump -uroot -p123 –single-transaction test -d >
test_structure.sql
7、只导出数据
mysqldump -uroot -p123 –no-create-info –single-transaction
–skip-add-locks –compact -c –set-gtid-purged=OFF test t2 >t2.sql
8、按照where条件导出
mysqldump -uroot -p123 –single-transaction test table_1 -w
“id<1000000” > table_1.sql

如果你不给定任何表,整个数据库将被导出。  

通过执行mysqldump –help,你能得到你mysqldump的版本支持的选项表。  

注意,如果你运行mysqldump没有–quick或–opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题。  

mysqldump支持下列选项:  

–add-locks  
在每个表导出之前增加LOCK TABLES并且之后UNLOCK
TABLE。(为了使得更快地插入到MySQL)。  
–add-drop-table  
在每个create语句之前增加一个drop table。  
–allow-keywords  
允许创建是关键词的列名字。这由表名前缀于每个列名做到。  
-c, –complete-insert  
使用完整的insert语句(用列名字)。  
-C, –compress  
如果客户和服务器均支持压缩,压缩两者间所有的信息。  
–delayed  
用INSERT DELAYED命令插入行。  
-e, –extended-insert  
使用全新多行INSERT语法。(给出更紧缩并且更快的插入语句)  
-#, –debug[=option_string]  
跟踪程序的使用(为了调试)。  
–help  
显示一条帮助消息并且退出。  
–fields-terminated-by=…  
   
–fields-enclosed-by=…  
   
–fields-optionally-enclosed-by=…  
   
–fields-escaped-by=…  
   
–fields-terminated-by=…  
这些选择与-T选择一起使用,并且有相应的LOAD DATA
INFILE子句相同的含义。  
LOAD DATA INFILE语法。  
-F, –flush-logs  
在开始导出前,洗掉在MySQL服务器中的日志文件。  
-f, –force,  
即使我们在一个表导出期间得到一个SQL错误,继续。  
-h, –host=..  
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。  
-l, –lock-tables.  
为开始导出锁定所有表。  
-t, –no-create-info  
不写入表创建信息(CREATE TABLE语句)  
-d, –no-data  
不写入表的任何行信息。如果你只想得到一个表的结构的导出,这是很有用的!  
–opt  
同–quick –add-drop-table –add-locks –extended-insert
–lock-tables。  
应该给你为读入一个MySQL服务器的尽可能最快的导出。  
-pyour_pass, –password[=your_pass]  
与服务器连接时使用的口令。如果你不指定“=your_pass”部分,mysqldump需要来自终端的口令。  
-P port_num, –port=port_num  
与一台主机连接时使用的TCP/IP端口号。(这用于连接到localhost以外的主机,因为它使用
Unix套接字。)  
-q, –quick  
不缓冲查询,直接导出至stdout;使用mysql_use_result()做它。  
-S /path/to/socket, –socket=/path/to/socket  
与localhost连接时(它是缺省主机)使用的套接字文件。  
-T, –tab=path-to-some-directory  
对于每个给定的表,创建一个table_name.sql文件,它包含SQL CREATE
命令,和一个table_name.txt文件,它包含数据。
注意:这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据–fields-xxx和–lines–xxx选项来定。  
-u user_name, –user=user_name  
与服务器连接时,MySQL使用的用户名。缺省值是你的Unix登录名。  
-O var=option, –set-variable
var=option设置一个变量的值。可能的变量被列在下面。  
-v, –verbose  
冗长模式。打印出程序所做的更多的信息。  
-V, –version  
打印版本信息并且退出。  
-w, –where=’where-condition’  
只导出被选择了的记录;注意引号是强制的!  
“–where=user=’jimf'” “-wuserid>1” “-wuserid<1” 

最常见的mysqldump使用可能制作整个数据库的一个备份: 

mysqldump –opt database > backup-file.sql  

但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的:  

mysqldump –opt database | mysql –host=remote-host -C database  

由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了:  

shell> mysqladmin create target_db_name  
shell> mysql target_db_name < backup-file.sql 
就是 
shell> mysql 库名 < 文件名

================================
几个常用用例:

1.导出整个数据库
 mysqldump -u 用户名 -p 数据库名 > 导出的文件名   
 mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2.导出一个表
 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
 mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
  mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc
>d:\wcnc_db.sql

 -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4.导入数据库
  常用source 命令
  进入mysql数据库控制台,
  如mysql -u root -p
 
  mysql>use 数据库

  然后使用source命令,后面参数为脚本文件(如这里用到的.sql)
  mysql>source d:\wcnc_db.sql

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图