Export MySQL database using mysqldump import

2. Basic import of the database \ Export command is mysqldump and source under Linux directly with the command line operation can be under windows under normal circumstances there are two ways one is to use the command line and the other is to use phpMyAdmin
4. 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 there is a size limit of not more than 2M then say mysqldump and source with command operation quickly but want to export Files must be created in the first database (the library can be arbitrarily named) and then enter the database to import mysqldump exported files with phpMyAdmin need this step
6. 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 filename mysqldump-u wcnc-p smgp_apps_wcnc > Wcnc.sql 2. Export a table mysqldump-u user name-P database name table name > exported filename 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 in each CREATE statement Add a drop table before
8. 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
10. 4. Import the database The common source command enters the MySQL database console, such as the Mysql-u root-p mysql>use database and then uses the source command, followed by the script file (for example, the. SQL used here) mysq L>source D:wcnc_db.sql
12. 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
14. 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.
16. 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 many ways to invoke the command prompt, the most common is to click the Start menu, select "Run", The command to enter CMD is ready to start), enter the following command at the command prompt:
18. Mysqldump-ubackup-p123456-h 192.168.1.2 backup_test > D:\bak\bakcup.sql in this order: Mysqldump is the program name of Myqldump;
20. -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, if no such entry indicates that the database is backed up locally; backup_test the name of the database to be backed up ；
22. > 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.
24. ======================================= command to back up the MySQL database mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql
26. Backing up the MySQL database for a format with a delete table the MySQL database is formatted with a delete table, allowing the backup to overwrite the existing database without having to manually delete the existing database. Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql
28. Compress MySQL database directly backup mysqldump-hhostname-uusername-ppassword databasename | gzip > backupfile.sql.gz Backup MySQL database some (some) table Mysqldump-hhostname-uusername-ppassword databasename Specific_table1 Specific_table2 > Backupfile.sql
30. Back up multiple MySQL databases simultaneously mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql
32. Just back up the database structure mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql
34. Back up all databases on the server mysqldump–all-databases > Allbackupfile.sql
36. Commands to restore MySQL database mysql-hhostname-uusername-ppassword databasename < Backupfile.sql
38. Restore compressed MySQL database Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName
40. Transferring a database to a new server Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C databasename====================================================== This more flexible, export select * from table _name into outfile ' c:\\path\\filename.txt ' import: LOAD DATA LOCAL INFILE ' file_name.txt ' into TABLE tbl_name

Execute SQL Script:

The first way: in the case of not connected to the database, enter mysql-h localhost-u root-p 123456 < d:\book.sql carriage return;

The second way: in the case of a connected database, at which point the command prompt is Mysql>, enter source D:\book.sql or \. D:\book.sql Enter.