Mysql導(dǎo)出文件及備份操作筆記

通過 SELECT ... INTO OUTFILE 語句 導(dǎo)出某個(gè)表

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| acs                |

| auth               |

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

6 rows in set (0.01 sec)

MariaDB [(none)]> use test

Database changed

MariaDB [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| sheet1         |

+----------------+

1 row in set (0.01 sec)

MariaDB [test]> select * from sheet1 into outfile '/tmp/sheet1.txt';

Query OK, 239 rows affected (0.02 sec)

MariaDB [test]> select * from sheet1 into outfile '/tmp/sheet2.txt'

    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'

    -> LINES TERMINATED BY '\r\n';

Query OK, 239 rows affected (0.00 sec)

      LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。為了將一個(gè)數(shù)據(jù)庫的數(shù)據(jù)寫入一個(gè)文件，使用SELECT ... INTO OUTFILE，為了將文件讀回?cái)?shù)據(jù)庫，使用LOAD DATA INFILE。

  通過 mysqldump 導(dǎo)出表作為原始數(shù)據(jù)

如下語句將 test 數(shù)據(jù)庫的 sheet1 表導(dǎo)出到 /tmp 目錄下，執(zhí)行后在 /tmp 目錄下回生成 sheet1.txt 文本文件：

[root@bogon ~]# mysqldump -u root -p --no-create-info  --tab=/tmp test sheet1

Enter password:

  通過 mysqldump 導(dǎo)出某個(gè)表定義及數(shù)據(jù)

[root@bogon ~]# mysqldump -u root -p test sheet1 > /tmp/sheet1dump.txt

Enter password:

[root@bogon ~]#

[root@bogon tmp]# more sheet1dump.txt

-- MySQL dump 10.16  Distrib 10.1.25-MariaDB, for Linux (x86_64)

--

-- Host: localhost    Database: test

-- ------------------------------------------------------

-- Server version       10.1.25-MariaDB

--

-- Table structure for table `sheet1`

--

DROP TABLE IF EXISTS `sheet1`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `sheet1` (

  `Code` varchar(255) DEFAULT NULL,

  `Name` varchar(255) DEFAULT NULL,

  `Continent` varchar(255) DEFAULT NULL,

…

  `Code2` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `sheet1`

--

LOCK TABLES `sheet1` WRITE;

/*!40000 ALTER TABLE `sheet1` DISABLE KEYS */;

INSERT INTO `sheet1` VALUES ('ABW','Aruba','North America','Caribbean','193.00','','103000','78.4','828.00','793.00','Aruba','

….

8670.00','Zimbabwe','Republic','Robert G. Mugabe','4068','ZW');

/*!40000 ALTER TABLE `sheet1` ENABLE KEYS */;

UNLOCK TABLES;

-- Dump completed on 2020-03-27 18:04:43

全庫備份：

[root@bogon ~]# mysqldump -u root -p --all-databases > /home/dbbakups/Test_DB_dump20200327.txt

Enter password:

將數(shù)據(jù)表及數(shù)據(jù)庫拷貝至其他主機(jī)

如果你需要將數(shù)據(jù)拷貝至其他的 MySQL 服務(wù)器上, 你可以在 mysqldump 命令中指定數(shù)據(jù)庫名及數(shù)據(jù)表。

在源主機(jī)上執(zhí)行以下命令，將數(shù)據(jù)備份至 dump.txt 文件中:

$ mysqldump -u root -p database_name table_name > dump.txt

password *****

[root@bogon ~]# mysqldump -u root -p test sheet1 > /tmp/sheet1dump.txt

如果完整備份數(shù)據(jù)庫，則無需使用特定的表名稱。

[root@bogon ~]# mysqldump -u root -p acs  > /tmp/acsdump.txt

[root@bogon tmp]# ls -alh

-rw-r--r--.  1 root     root     5.2M Mar 27 18:54 acsdump.txt

如果你需要將備份的數(shù)據(jù)庫導(dǎo)入到MySQL服務(wù)器中，可以使用以下命令，使用以下命令你需要確認(rèn)數(shù)據(jù)庫已經(jīng)創(chuàng)建：

創(chuàng)建數(shù)據(jù)庫 語法 :     

                create   database   db1   DEFAULT   CHARSET   utf8   COLLATE   utf8_general_ci;       # utf8編碼  

               create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk編碼

使用數(shù)據(jù)庫:       use   db1;

在另一個(gè)本地PC上創(chuàng)建名稱為acstest的數(shù)據(jù)庫：

mysql>  create database acstest  DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected, 2 warnings (0.09 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| acstest            |

| information_schema |

| kuqlan             |

| mysql              |

| performance_schema |

| sakila             |

| sys                |

| world              |

+--------------------+

8 rows in set (0.00 sec)

mysql> exit

Bye

C:\Users\yspmm>mysql -u root -p acstest < D:\acsdump.txt

Enter password: ********

導(dǎo)入語法：

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

password *****

你也可以使用以下命令將導(dǎo)出的數(shù)據(jù)直接導(dǎo)入到遠(yuǎn)程的服務(wù)器上，但請確保兩臺服務(wù)器是相通的，是可以相互訪問的：

$ mysqldump -u root -p database_name \

       | mysql -h other-host.com database_name

將指定主機(jī)的數(shù)據(jù)庫拷貝到本地

如果你需要將遠(yuǎn)程服務(wù)器的數(shù)據(jù)拷貝到本地，你也可以在 mysqldump 命令中指定遠(yuǎn)程服務(wù)器的IP、端口及數(shù)據(jù)庫名。

在源主機(jī)上執(zhí)行以下命令，將數(shù)據(jù)備份到 dump.txt 文件中：

請確保兩臺服務(wù)器是相通的：

mysqldump  - h other - host . com  - P port  - u root  - p database_name  >  dump . txt
password  ****

一般在導(dǎo)出文件目錄沒有在 mysql  參數(shù)文件 my .ini 或 my .cnf 里進(jìn)行配置會(huì)出現(xiàn)如下錯(cuò)誤：

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

出現(xiàn)這個(gè)錯(cuò)誤是因?yàn)闆]有給數(shù)據(jù)庫指定寫出文件的路徑或者寫出的路徑有問題。錯(cuò)誤解決方法：

首先使用下面的命令  show variables like '%secure%';  查看數(shù)據(jù)庫的存儲(chǔ)路徑。如果查出的 secure_file_priv 是 null 的時(shí)候就證明在 my.ini 文件里面沒有配置寫出路徑。

這時(shí)候就可以在 mysql.ini 文件的 [mysqld] 代碼下增加 secure_file_priv= D :/ tmp  再重啟 mysql 就可以了。

參考文章：

https://www.runoob.com/mysql/mysql-database-export.html