Use MySQL as the Import and Export commands for Backup recovery,

Use MySQL as the Import and Export commands for Backup recovery,

Mysqldump
Export the database using the command line:
1. Go to the bin folder under the MySQL Directory: directory from cd MySQL to the bin folder
For example, if the command line I entered is cd C: \ Program Files \ MySQL Server 4.1 \ bin
2. Export Database: mysqldump-u username-p Database Name> exported file name
For example, the command line I entered is mysqldump-u root-p news> news. SQL (after entering the command, you will be asked to enter the MySQL password)
(If you export a single table, enter the table name after the database name)
3. The file news. SQL is automatically generated under the binfile.
Command Line import database:
1. The. SQL file to be imported is moved to the binfile, which is convenient.
2. Step 1 of the above-mentioned export
3. Enter MySQL: mysql-u username-p
For example, the command line I entered is mysql-u root-p (after entering the same command, you will be asked to enter the ySQL password)
4. create the database you want to create in MySQL-Front. An empty database is used, for example, create a target database named news (mysql> create database news ;)
5. Enter the mysql> use target database name.
For example, the command line I entered is mysql> use news;
6. Import file: the file name imported from mysql> source;
For example, the command line I entered is mysql> source news. SQL;
 
Instance:
Export

#> cd /usr/local/src#> /usr/local/mysql/bin/mysqldump -uroot -p123456 cacti > cacti.sql;

So that cacti. SQL is exported to the/usr/local/src/directory.
1. Export the entire database

Mysqldump-u user name-p Database Name> exported file name mysqldump-u root-p abc> abc. SQL

2. Export a table

Mysqldump-u user name-p database name Table Name> exported file name mysqldump-u root-p abc users> abc_users. SQL

3. Export a database structure

Mysqldump-u root-p-d -- add-drop-table abc>/usr/local/abc_db. SQL-d no data -- add-drop-table add a drop before each create statement table

 
Import

#>/Usr/local/mysql/bin/mysql-uroot-pmysql> create database cacti; create a database named cactimysql> use cacti; select the target database mysql> source/usr/local/src/cacti. SQL import database

In this way, the cacti. SQL file is imported.

Load data
(Windows system .. When the table structure is consistent)
Run the following command in the DOS mysql/bin directory:
Export:

select * from aaa into outfile "e:/a.txt";

Import:

load data local infile "e:/a.txt" into table aaa;

Backup: SELECT INTO OUTFILE

SELECT * FROM hx_9enjoy INTO OUTFILE '/tmp/9enjoy.txt'

By default, fields are separated by tab, and records are separated by/n. You can customize three parameters.

SELECT * FROM hx_9enjoy INTO OUTFILE '/tmp/9enjoy_hx.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '/n';

That is, use. separate fields. Double quotation marks are used to limit the content of fields./n is used to separate records.
You can also specify the record output for certain conditions:

SELECT * FROM hx_9enjoy WHERE id < 100 INTO OUTFILE '/tmp/9enjoy_100.txt'

Restore: LOAD DATA INFILE
Default:

LOAD DATA INFILE '/tmp/9enjoy.txt' INTO TABLE hx_9enjoy

If a Delimiter is specified, the Delimiter is added:

LOAD DATA INFILE '/tmp/9enjoy.txt' INTO TABLE hx_9enjoy FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '/n'

If the database is GBK or UTF-8 encoded, you must specify the character set parameter during restoration. Otherwise, the restored data is garbled.
For example:

load data infile '/tmp/9enjoy.txt' INTO TABLE hx_9enjoy CHARACTER SET gbk;

Notes
1. mysql must have the permission to access the directory of the generated file
2. For security reasons, the existing file cannot be overwritten, no matter how permissions are assigned to the file.
3. You cannot export a compressed file directly.

Articles you may be interested in:

• Summary of Solutions to Chinese garbled characters in mysql import and export data
• Mysql import and export tool Mysqldump and Source command usage details
• MYSQL Database Import and Export commands