MYSQL database Import and Export command

MySQL command line export database

1. Go to the Bin folder in the MySQL directory: cd MySQL to the Bin folder directory

As I entered the command line: CD C:\Program files\mysql\mysql Server 4.1\bin

(or add the directory to the Windows environment variable path directly)

2, Export database: Mysqldump-u user name-p database name > exported file name

As I entered the command line: Mysqldump-u root-p News > News.sql (input will let you enter the password into MySQL)

(If you export a single table, enter the table name after the database name)

3, you will see the file News.sql automatically generated into the bin file

Command line Import Database

1, move the. sql file to be imported into the bin file, such a path is more convenient
2, the 1th step with the above export
3, enter mysql:mysql-u user name-P

As I entered the command line: mysql-u root-p (Input the same will let you enter the MySQL password)

4, create the database you want to build in Mysql-front, this is an empty database, such as a new target database named News
5, Input: Mysql>use target database name

As I entered the command line: Mysql>use news;

6, import file: mysql>source import filename;

As I entered the command line: Mysql>source news.sql;

MySQL backup and restore is done using the mysqldump, MySQL, and source commands.

To back up the database:
Go to cmd
Export all databases: input: mysqldump-u [database user name]-p-a>[save path for backup file]

Export data and structure: input: mysqldump-u [Database user name]-p [database name to be backed up]>[backup file save path]
Example: Mysqldump-u root-p test>d:\test.sql
Note: This backup backs up only data and structure, no backup stored procedures and triggers

Export-only data structure not exported: input: mysqldump-u [database user name]-p-t [the name of the database to be backed up]>[the save path of the backup file]

export events in the database
Input: mysqldump-u [database user name]-p-e [Database user name]>[save path to backup file]

Exporting stored procedures and functions in a database
mysqldump-u [Database user name]-p-r [Database user name]>[save path to backup file]

Import Database
Mysql-u root-p<[backup File save path] Question

To restore a backup file:
Enter MySQL Command line Client
First CREATE DATABASE: CREATE DATABASE Test Note: test is the name
Then switch to the current database: Use test
Re-enter: \. D:/test.sql or Souce D:/test.sql

1. Overview
MySQL database import, there are two ways:
1) pilot out the database SQL script, and then import;
2) Copy the database directory and files directly.

In the case of different operating systems or versions of MySQL, the method of copying files directly may occur in an incompatible situation.
Therefore, it is generally recommended to import in SQL script form. Two methods are described below.

2. Method one SQL script form
The procedure is as follows:
2.1. Exporting SQL Scripts
On the original database server, you can export the SQL script using the phpMyAdmin tool, or the mysqldump (mysqldump command is located in the mysql/bin/directory) command line.
2.1.1 with phpMyAdmin Tools
Export options, select export structure and data, and do not add the drop DATABASE and drop TABLE options.
Select the Save As File option, and if you have more data, you can select the gzipped option.
Save the exported SQL file.

2.1.2 with mysqldump command line
Command format
Mysqldump-u user name-p database name > database name. sql
Example:
MYSQLDUMP-UROOT-P ABC > Abc.sql
(Export database ABC to abc.sql file)

When prompted for a password, enter the password for the database user name.

2.2. Create an empty database
Create a database from the main control interface/Control Panel. Assuming that the database name is ABC, the database is full-user abc_f.

2.3. Import SQL scripts into the execution
The same is true of two methods, a phpmyadmin (MySQL database management) tool, or a MySQL command line.
2.3.1 with phpMyAdmin Tools
From the Control Panel, select Create an empty database, click "Manage" and go to the Administration Tools page.
From the SQL menu, browse to select the SQL file you just exported, click "Execute" to download and execute.

Note: phpMyAdmin has a limit on the size of uploaded files, and PHP itself limits the size of uploaded files if the original SQL file
Relatively large, you can first compress it with gzip, for a text file such as SQL file, you can get 1:5 or higher compression rate.
How to use gzip:
# gzip Xxxxx.sql
Get
xxxxx.sql.gz file.

2.3.2 with mysql command line
Command format
Mysql-u user name-p database name < database name. sql
Example:
MYSQL-UABC_F-P ABC < Abc.sql
(Import database ABC from Abc.sql file)

When prompted for a password, enter the password for the database user name.

3 Method Two direct copies
If the database is larger, you can consider the method of direct copy, but different versions and operating systems may be incompatible between the use of caution.
3.1 Preparing the original file
Package as a file with tar

3.2 Creating an empty database

3.3 Decompression
Unzip in the temp directory, such as:
Cd/tmp
Tar zxf mydb.tar.gz

3.4 Copy
Copy the extracted database files to the relevant directory
CD mydb/
CP */var/lib/mysql/mydb/

For FreeBSD:
CP */var/db/mysql/mydb/

3.5 Permission settings
Change the owner of the copy of the previous file to Mysql:mysql and change the permission to 660
Chown mysql:mysql/var/lib/mysql/mydb/*
chmod 660/var/lib/mysql/mydb/*

MYSQL database Import and Export command