MYSQL Database Import and Export command, mysql Import and Export

MYSQL Database Import and Export command, mysql Import and Export

MySQL command line Export Database

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

(Or directly add the directory to the path of the windows environment variable)

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 (enter the same password for MySQL)

4. Create the database you want to create in MySQL-Front. An empty database is used, for example, create a target database named "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;

MySQL backup and restoration are completed using mysqldump, mysql, and source commands.

Back up the database:
Go to cmd
Export all databases: Enter mysqldump-u [Database User Name]-p-A> [backup file storage path]

Export data and data structure: input: mysqldump-u [database username]-p [name of the database to be backed up]> [storage path of the backup file]
Example: mysqldump-u root-p test> d: \ test. SQL
Note: This backup only backs up data and data structures. There is no backup stored procedure or trigger.

Export data only without exporting data structure: input: mysqldump-u [database username]-p-t [name of the database to be backed up]> [storage path of the backup file]

Export the Events in the database
Input: mysqldump-u [Database User Name]-p-E [Database User Name]> [backup file storage path]

Export stored procedures and functions in the database
Mysqldump-u [Database User Name]-p-R [Database User Name]> [backup file storage path]

Import Database
Mysql-u root-p <[backup file storage path]

Restore the backup file:
Go to MYSQL Command Line Client
First create database: create database test Note: test is the name of the created database
Switch to the current database: use test
Enter \. d:/test. SQL or souce d:/test. SQL.

1. Overview
There are two ways to import a MySQL database:
1) Export the database SQL script first and then import it;
2) directly copy database directories and files.

In different operating systems or MySQL versions, the method of directly copying files may be incompatible.
Therefore, we recommend that you use SQL scripts to import data. The following describes two methods.

2. Method 1: SQL script
The procedure is as follows:
2.1. Export an SQL script
On the original database server, you can use the phpMyAdmin tool or the mysqldump command (the mysqldump command is located in the mysql/bin/directory) command line to export the SQL script.
2.1.1 phpMyAdmin
Select "structure" and "data" to export. Do not add the "Drop DATABASE" and "Drop TABLE" options.
Select the "Save As file" option. If there is more data, select the "gzipped" option.
Save the exported SQL file.

2.1.2 use mysqldump command line
Command Format
Mysqldump-u username-p Database Name> database name. SQL
Example:
Mysqldump-uroot-p abc> abc. SQL
(Export the database abc to the abc. SQL file)

Enter the password of the Database User Name When prompted.

2.2. Create an empty Database
Create a database on the master page/control panel. Assume that the database name is abc and the database full user is abc_f.

2.3. Import and execute SQL scripts
You can use either phpMyAdmin or mysql command line.
2.3.1 use phpMyAdmin
From the control panel, select the created empty database and click "manage" to go to the management tools page.
In the "SQL" menu, browse and select the exported SQL file, and click "run" to load and execute the file.

Note: phpMyAdmin has a limit on the size of the uploaded file, and php itself has a limit on the size of the uploaded file. If the original SQL File
Relatively large, you can use gzip to compress it. For text files such as SQL files, you can obtain a compression rate of or higher.
Gzip usage:
# Gzip xxxxx. SQL
Get
Xxxxx. SQL .gz file.

2.3.2 use mysql Command Line
Command Format
Mysql-u username-p database name <database name. SQL
Example:
Mysql-uabc_f-p abc <abc. SQL
(Import database abc from abc. SQL file)

Enter the password of the Database User Name When prompted.

3 method 2 Direct Copy
If the database is large, you can use the direct copy method. However, different versions and operating systems may be incompatible with each other, so use it with caution.
3.1 prepare the original file
Package A file with tar

3.2 Create an empty Database

3.3 decompress
Decompress the package in the temporary directory, for example:
Cd/tmp
Tar zxf mydb.tar.gz

3.4 copy
Copy the extracted database file 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 copied files to mysql: mysql and the permission to 660.
Chown mysql: mysql/var/lib/mysql/mydb /*
Chmod 660/var/lib/mysql/mydb /*