Mysql Command (database backup and recovery)

Cd directly back to the root directory cd .... return to the parent directory (parent directory) cdA of the parent directory. Change the current directory of disk A to the root directory cdA: xx and change the current directory of disk A to the sub-directory cd under directory xx .. 98 first, return to the parent directory, and then enter the 98 sub-directory cd under the parent directory .. return to the parent directory cd98 and go to the 98 subdirectory helpcd under the root directory to get more

Cd \ directly go back to the root directory cd .. \.. return to the parent directory (parent directory) cd A: \ and change the current directory of disk A to the root directory cd: \ xx: change the current directory of disk A to the sub-directory xx under cd .. \ 98 first return to the parent directory, and then enter the 98 subdirectory cd under the parent directory .. return to the parent directory cd \ 98 and enter the 98 sub-directory under the root directory help cd to get more

Cd \ directly go back to the root directory

Cd ...... return to the parent directory (parent directory) of the parent directory)

Cd A: \ change the current directory of disk A to the root directory.

Cd A: \ xx change the current directory of disk A to the subdirectory xx

Cd .. \ 98 first returns the parent directory, and then enters the 98 subdirectory under the parent directory

Cd .. return to parent directory

Cd \ 98 go to the 98 subdirectory under the root directory

Help cd for more information

Local:

1. Enter the bin folder in the MySQL Directory: e: Press ENTER;

E: \> cd mysql \ binEnter

2. Export Database: mysqldump-u username-p Database Name> exported file name

Example:
Mysqldump-uroot-p abc> abc. SQL
(Export the database abc to the abc. SQL file)
When prompted to enter the password, enter the password of the database user name (if you export a single table, enter the table name after the database name)

If a locktable error is prompted, add a space after mysqldump-uroot-pabc.-- Skip-lock-tables

3. The file news. SQL is automatically generated under the binfile.

MySQL 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. (After entering the same password, you will be asked to enter the MySQL password)
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.
1. MySQL backup and restoration under Win32
1.1 backup
Start Menu | run | cmd | use the "cd \ Program Files \ MySQL Server5.0 \ bin" command to enter the bin folder | use "mysqldump"-U username-pdatabasename> exportfilename ": export the database to a file, for example, mysqldump-u root-pvoice> voice. SQL. Then enter the password to start exporting.

1.2 restore
Enter the MySQL Command Line Client, enter the password, go to "mysql>", enter the Command "showdatabases;", Press enter to see what databases are there, and create the database you want to restore, enter "create databasevoice;", Press ENTER; switch to the database you just created, enter "use voice;", Press ENTER; import data, and enter "sourcevoice. SQL; ", Press enter to start importing, and" mysql> "is displayed again. If no error is returned, the restoration is successful.

2. MySQL backup and restoration in Linux
2.1 backup
[Root @ localhost ~] # Cd/var/lib/mysql (go to the MySQL database directory and adjust the directory based on your MySQL installation)
[Root @ localhost mysql] # mysqldump-u root-pvoice> voice. SQL. Enter the password.
2.2 restore
Method 1:
[Root @ localhost ~] # Mysql-u root-p press enter and enter the password to go To the MySQL console "mysql>", which is restored in the same way as 1.2.
Method 2:
[Root @ localhost ~] # Cd/var/lib/mysql (go to the MySQL database directory and adjust the directory based on your MySQL installation)
[Root @ localhost mysql] # mysql-u root-p voice

Mysqldump-hhostname-uusername-ppassword databasename> backupfile. SQL
Back up the MySQL database in the format of a table with deletion
Backing up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.

Mysqldump -- add-drop-table-uusername-ppassword databasename> backupfile. SQL
Directly compress and back up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename | gzip> backupfile. SQL .gz
Back up a MySQL database table

Mysqldump-hhostname-uusername-ppassword databasenamespecific_table1 specific_table2> backupfile. SQL
Back up multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword-databases databasename1databasename2 databasename3> multibackupfile. SQL
Back up database structures only

Mysqldump-no-data-databases databasename1 databasename2databasename3> structurebackupfile. SQL
Back up all databases on the server

Mysqldump-all-databases> allbackupfile. SQL
Command for restoring MySQL database

Mysql-hhostname-uusername-ppassword databasename Restore a compressed MySQL database

Gunzip <backupfile. SQL .gz | mysql-uusername-ppassworddatabasename
Transfer database to new server

Mysqldump-uusername-ppassword databasename | mysql-host = *. *-C databasename