Mysqldump Import and Export MySQL database

Mysqldump Import and Export MySQL database

Basic import of database \ Export command is mysqldump and source
In Linux under the direct command line operation can under Windows under normal circumstances there are two ways one is to use the command line another is to use phpMyAdmin

First of all, phpmyadmin. This tool is simple to export and import without having to build a library directly to restore the original database with source can also be restored but when he imports the file, the size limit cannot exceed 2M
In other words, mysqldump and source operate quickly with commands, but when you want to import the exported file again, you must first establish a database (the library can be named) and then enter the database to import the files exported with phpMyAdmin Import mysqldump also need this step

Here is a copy from the predecessor of the command specific use method

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
　　
Memo: Mysqldump under Linux can be used directly under Windows sometimes need to go into the mysql/bin because sometimes it is similar to Appserv-like package software, so the command cannot be used directly because these commands are not in the environment variable directory Also, a backup exported with mysqldump must be established before the database can be imported


4. Import the database
Common source Commands
Go to MySQL Database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source D:wcnc_db.sql


Doubt: phpMyAdmin import has a size limit do not know when the source import is unrestricted and export is not allowed to limit the file size and the export of a number of files

Mysqldump is a good tool for backing up MySQL databases. It is relatively fast with backup tools such as phpMyAdmin, and avoids limitations such as php.ini, and can be scheduled to remotely back up the database in conjunction with scheduled tasks under the Windows system.

The user who uses mysqldump to back up the database requires a small amount of permissions, and only the permissions on select and lock table are sufficient. So you can create a user who backs up the database, specifically for backing up the database, which avoids accidental damage to the database.

Before backing up the database we have to do the preparation, first created the database user--backup to perform the backup operation, the password is: 123456. Next, determine which database we want to back up and where to put it. Then, determine where the backup files for the database are to be placed.

Suppose our task is to back up a database on the 192.168.1.2 server named Backup_test and place the backup file in the D:\bak\ folder. Also assuming that our operating system is Windows XP, we can perform the backup operation in the following ways:

We can use the command prompt that comes with Windows (there are several ways to invoke the command prompt, the most common is to click on the Start menu, select "Run", enter cmd command to start), and at the command prompt, enter the following command:

Mysqldump-ubackup-p123456-h 192.168.1.2 backup_test > D:\bak\bakcup.sql

In this section of the command:

Mysqldump is the program name of Myqldump ;

-U is the user name of the database, followed by the user name backup;

-P is the password of the database, followed by the same password, note that there is no space between-p and password;

-H is the address of the database and, if not, indicates that the database is backed up locally;

Backup_test the name of the database to be backed up;

> indicates that the database is backed up to a later file, followed by the address of the backup file of course, note that to use the absolute file path, the suffix of the file can also be used. txt.

Once the command is executed, Mysqldump immediately performs the backup operation. Remotely backing up a database it would take a while for a typical remote backup to look like a network.

=======================================

Commands to back up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

backing up the MySQL database to a format with a delete table
Backing up the MySQL database is a format with a delete table that allows the backup to overwrite the existing database without having to manually delete the existing database.

Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql

Compress the MySQL database directly backup

Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Back up a MySQL database (some) table

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Back up multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql

Backing up the database structure only

Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server

Mysqldump–all-databases > Allbackupfile.sql

Commands to restore MySQL database

Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restore a compacted MySQL database

Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

To transfer a database to a new server

Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName

======================================================

This more flexible, export SELECT * FROM table_name to outfile ' C:\\path\\filename.txt '
Import: LOAD DATA LOCAL INFILE ' file_name.txt ' into TABLE tbl_name

Mysqldump Import and Export MySQL database