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 into outfile ' C:\\path\\filename.txt ' Import: LOAD DATA LOCAL INFILE ' file_name.txt ' into TABLE tbl_name |