MySQL data backup and recovery commands

Set the mysql installation directory to system environment variables to facilitate direct execution on the command line terminal.

After mysql is installed in linux, the default root password is blank. You can directly log on to mysql.

Normal logon command mysql-h host address-u username-p Password

Note that no space is required. If you log on to mysql on the local machine directly, you do not need to add the-h parameter.

Database backup and recovery:

1. Back up the database structure only:

Mysqldump-h127.0.0.1-uroot-p654321 -- default-character-set = utf8 -- no-data -- databases -- add-drop-table-R -- result-file = "c:/bak. SQL "test

Note:-R indicates the backup stored procedure or function, but the view is not controlled here.

-Result-file: Specifies the backup file path. in windows, a drive letter is required. In linux, enter the full directory name.

Test indicates the name of the database to be backed up.

2. Backup structure and data (full backup ):

Mysqldump-h127.0.0.1-uroot-p654321 -- default-character-set = utf8 -- databases -- add-drop-table-R -- result-file = "c:/bak. SQL" test

Note: To prevent data loss during backup, add the -- lock-all-tables = true parameter to lock the entire table.

3. Only Backup part of data (differential backup)

Mysqldump-h127.0.0.1-uroot-p654321 -- default-character-set = utf8 test t_table1 t_table2 t_table3 -- no-create-info -- result-file = "c:/bak. SQL"

Note: -- no-create-info does not add table creation statements, which can prevent loss of original data and append backup data to the database.

If you want to overwrite the original data, add the -- replace Parameter

4. database recovery

Mysql-127.0.0.1-uroot-p654321 -- comment <c:/bak. SQL

Generally, the -- comment parameter is not added for restoration, which will cause loss of comments in the stored procedure. We recommend that you add the comments.

Mysql-127.0.0.1-uroot-p654321 -- comment test <c:/bak. SQL

For differential Backup recovery, you must specify the database name.