MYSQL Database Import Export command _mysql

MySQL command line export database

1, into the MySQL directory under the Bin folder: 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 directly to the environment variable path of Windows)

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 allow you to enter the password into MySQL)

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

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

command-line Import Database

1, to move the imported. sql file to the bin file, this is a convenient path
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 (Enter the same after 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, enter: Mysql>use Target database name

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

6, import file: Mysql>source import of the file name;

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

MySQL backups and restores are done using the mysqldump, MySQL, and source commands.

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

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

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

exporting events in a database
Input: mysqldump-u [database user name]-P-E [Database username]>[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<[save path for backup files] Question

Restore Backup files:
Enter MySQL Command line Client
To create a database first: Creating database Test Note: test is the name
And then switch to the current database: Use test
then enter: \. D:/test.sql or Souce D:/test.sql

1. Overview
There are two ways to import a MySQL database:
1 lead out the database SQL script, and then import;
2 directly copy the database directory and files.

In the case of different operating systems or versions of MySQL, the method of directly copying files may be incompatible.
Therefore, it is generally recommended to import in SQL script form. Here are two ways to describe each.

2. Method one SQL script form
Action steps are as follows:
2.1. Export SQL Script
On the original database server, you can export the SQL script using the phpMyAdmin tool, or the mysqldump command line (mysqldump command in the mysql/bin/directory).
2.1.1 with phpMyAdmin Tools
Export options, select export structure and data, and do not add drop DATABASE and drop TABLE options.
Select the Save As File option to select the gzipped option if the data is more numerous.
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 Master interface/Control Panel. Suppose the database is named ABC and the database is Abc_f.

2.3. Import SQL script into execution
There are also two methods, one using the phpMyAdmin (MySQL database management) tool, or the MySQL command line.
2.3.1 with phpMyAdmin Tools
From the Control Panel, choose to create an empty database, click "Manage", and go to the admin tools page.
On the SQL menu, browse to select the SQL file you just exported and click "Execute" to download and execute.

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

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 large, you can consider using a direct copy method, but the different versions and operating systems may be incompatible between the use of caution.
3.1 Preparation of original documents
Packaged in tar as a file

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 copies
Copy the extracted database files to the related directory
CD mydb/
CP */var/lib/mysql/mydb/

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

3.5 Permission settings
Change the owner of the document copying the past to Mysql:mysql, with the permission changed to 660
Chown mysql:mysql/var/lib/mysql/mydb/*
chmod 660/var/lib/mysql/mydb/*