Shulou(Shulou.com)06/01 Report--

Through SELECT... INTO OUTFILE statement exports a table

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 is SELECT. Reverse operation of INTO OUTFILE, SELECT syntax. To write data from a database to a file, use SELECT... INTO OUTFILE, to read the file back to the database, use LOAD DATA INFILE.

Export tables as raw data through mysqldump

The following statement exports the sheet1 table of the test database to the / tmp directory, and generates the sheet1.txt text file in the / tmp directory after execution:

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

Enter password:

Export a table definition and data through mysqldump

[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

...

`Code 2` 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.00The Zimbabweiler, the republicist, the Robert G. Mugabe','4068','ZW')

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

UNLOCK TABLES

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

Full library backup:

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

Enter password:

Copy data tables and databases to other hosts

If you need to copy the data to another MySQL server, you can specify the database name and table in the mysqldump command.

Backup the data to the dump.txt file by executing the following command on the source host:

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

Password *

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

If you fully back up the database, you do not need to use a specific table name.

[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

If you need to import the backed-up database into the MySQL server, you can use the following command to verify that the database has been created:

Create database syntax:

Create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8 coding

Create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk coding

Use database: use db1

Create a database named acstest on another local PC:

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: ******** 导入语法： $ mysql -u root -p database_name < dump.txt password ***** 你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的： $ mysqldump -u root -p database_name \ | mysql -h other-host.com database_name 将指定主机的数据库拷贝到本地 如果你需要将远程服务器的数据拷贝到本地，你也可以在 mysqldump 命令中指定远程服务器的IP、端口及数据库名。 在源主机上执行以下命令，将数据备份到 dump.txt 文件中： 请确保两台服务器是相通的： mysqldump - h other - host . com - P port - u root - p database_name >

Dump. Txt

Password *

Generally speaking, the following error occurs when the export file directory is not configured in the mysql parameter file my .ini or my .cnf:

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

This error occurs because there is no path to write the file to the database or there is a problem with the path written out. Error resolution:

First use the following command show variables like'% secure%'; to view the storage path of the database. If the secure_file_priv found out is null, it proves that the write-out path is not configured in the my.ini file.

At this point, you can add secure_file_priv= D: / tmp to the [mysqld] code of the mysql.ini file and restart mysql.

Reference article:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.