3.mysql导入导出

浅浅的花香味﹌ 2022-07-15 08:27 220阅读 0赞

### 文章目录 ###

*  1.导出
 *   *  1.语法
     *   *  options参数含义
     *  2.常用例子
     *   *  1.导出所有数据库
         *  2.导出表结构
         *  3.导出 数据库 存储过程+函数
         *  4.导出整个数据库
         *  5.导出表
         *   *  1.导出一张表
             *  2.导出多张表使用空格分割
         *  6.导出表结构
         *  7.分别导出每一个数据库 shell 脚本
         *  8.指定筛选条件导出
 *  2.导入数据库
 *   *  1.mysql 命令导入
     *   *  1.语法
         *  2.例子
     *  2.source 命令导入
     *  3.使用 LOAD DATA 导入数据

# 1.导出 #

## 1.语法 ##

# 单库多表
    mysqldump[options] database [tables,多个表用空格隔开]
    # 多库
    mysqldump[options] --databases [options] db1 [db2,多个库用空格隔开]
    # 所有库
    mysqldump[options] --all-databases [options]

### options参数含义 ###

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>-q , --quick</td> 
   <td>在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中</td> 
  </tr> 
  <tr> 
   <td>-c</td> 
   <td>是在insert中增加具体的字段名。这样对目的表结构不同原表，情况下更有用</td> 
  </tr> 
  <tr> 
   <td>-e ,–extended-insert</td> 
   <td>用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用–skip-extended-insert取消选项</td> 
  </tr> 
  <tr> 
   <td>-t</td> 
   <td>仅导出表数据，不导出表结构</td> 
  </tr> 
  <tr> 
   <td>–opt –d</td> 
   <td>仅导出表结构</td> 
  </tr> 
  <tr> 
   <td>-R , --routines</td> 
   <td>导出存储过程以及自定义函数</td> 
  </tr> 
  <tr> 
   <td>–triggers</td> 
   <td>导出触发器,<strong>默认启用</strong></td> 
  </tr> 
  <tr> 
   <td>-E , --events</td> 
   <td>导出事件</td> 
  </tr> 
  <tr> 
   <td>–single-transaction</td> 
   <td>该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和–lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用–quick 选项。在本例子中没有起到加快速度的作用<br>mysqldump -uroot -p --host=localhost --all-databases --single-transaction</td> 
  </tr> 
  <tr> 
   <td>–master-data=[1/2]</td> 
   <td>如果值等于1，就会添加一个CHANGE MASTER语句(二进制文件的名称和位置) <br> 如果值等于2，就会在CHANGE MASTER语句前添加注释 <br><br>– CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001’, MASTER_LOG_POS=154;<br><br> 如果加入此配置,mysql说Binlogging on server not active,解决方案如下:<br><br>Linux:需要在/etc/my.cnf里的[mysqlid]下方加入: log-bin=mysql-bin 再重启<br><br>Windows:mysql安装目录下my.ini文件,如上操作<br></td> 
  </tr> 
  <tr> 
   <td><strong>–all-databases , -A</strong></td> 
   <td>导出全部数据库</td> 
  </tr> 
  <tr> 
   <td><strong>–add-drop-database</strong></td> 
   <td>每个数据库创建之前添加drop数据库语句</td> 
  </tr> 
  <tr> 
   <td><strong>–add-drop-table</strong></td> 
   <td>每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用–skip-add-drop-table取消选项)</td> 
  </tr> 
  <tr> 
   <td><strong>-w , --where</strong></td> 
   <td>过滤条件,只支持单表数据条件导出</td> 
  </tr> 
 </tbody> 
</table>

## 2.常用例子 ##

### 1.导出所有数据库 ###

备份数据库
    mysqldump -u用户名 -p密码 --lock-all-tables --all-databases > all.sql
    导入数据库
    mysql -u用户名 -p密码 < all.sql

### 2.导出表结构 ###

mysqldump -uroot -p123456 -h192.168.1.100 -d testdb> D:\test.sql

### 3.导出 数据库 存储过程+函数 ###

mysqldump -uroot -p123456 -h172.16.2.27 -P3306 -n -d -t -R tq_copy> D:\test.sql

### 4.导出整个数据库 ###

mysqldump -uroot -p123456 -h192.168.1.100  testdb> D:\test.sql

### 5.导出表 ###

#### 1.导出一张表 ####

mysqldump -uroot -p123456 -h192.168.1.100 testdb test > D:\test.sql

#### 2.导出多张表使用空格分割 ####

mysqldump -uroot -p123456 -h192.168.1.100 testdb test test1 > D:\test.sql

### 6.导出表结构 ###

mysqldump -uroot -p123456 -h192.168.1.100 -d testdb test > D:\test.sql

### 7.分别导出每一个数据库 shell 脚本 ###

#!/bin/bash
     
    USER="用户名"
    PASSWORD="密码"
    
    databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
     
    for db in $databases; do
        if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
            echo "Dumping database: $db"
            mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
           # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
        fi
    done

### 8.指定筛选条件导出 ###

mysqldump -uroot -proot db1 table1 -w "id in (1,2,3)" > /data/test.sql

# 2.导入数据库 #

## 1.mysql 命令导入 ##

### 1.语法 ###

mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql)

### 2.例子 ###

mysql -uroot -p123456 < runoob.sql

## 2.source 命令导入 ##

source 命令导入数据库需要先登录到数库终端

#1.进入mysql数据库控制台
    mysql -u root -p 
    #2.指定数据库 
    mysql>use testdb
    #3.指定编码
    mysql>set names utf8;  
    #4.使用source命令，后面参数为脚本文件(如这里用到的.sql) 
    mysql>source runoob.sql

## 3.使用 LOAD DATA 导入数据 ##

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

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;