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. 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 [the name of the database to be backed up]>[the save path to the backup file] export a table in the database: input: mysqldump-u [Database user name]-p [database name to be backed up] [to back up The data sheet]
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: input: mysqldump-u [database user name]-p-t [the name of the database to be backed up]>[the save path to the backup file] 1. Export the entire database
Mysqldump-u user name-p database name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql
2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC users> wcnc_users.sql
3. Export a database structure
Mysqldump-u Wcnc-p-D--add-drop-table SMGP_APPS_WCNC >d:\wcnc_db.sql
-D No data--add-drop-table add a drop table before each CREATE statement
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 (go to MySQL commands interface)
First CREATE DATABASE: CREATE DATABASE Test Note: test is the name
Then switch to the current database: Use test
Re-enter: 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 Data import and export
