Mysqldump database backup and Restoration Methods

Mysqldump is a database backup and restoration tool that comes with mysql. I don't think many of you have used it, or you have been using phpmyadmin to operate it, or you can directly package the database on the server, next let's take a look at the usage of mysqldump.

Mysql backup:

The Code is as follows:	Copy code
Mysqldump-uroot-proot bookmark> bm. bak Mysqldump-u username-p Password Database Name> absolute path of the exported file

If you run mysqldump without the-quick or-opt option, mysqldump loads the entire result set into the memory before dumping the result. If you dump a large database, problems may occur. This option is enabled by default, but can be disabled with-skip-opt.

Use-skip-opt to export each piece of data separately, without inserting a large string of values

The Code is as follows:	Copy code
Mysqldump-uroot-proot-skip-opt bookmark> bm. bak

Mysql DATA Restoration

The Code is as follows:	Copy code
Mysql-u root-proot tt <C: usersyyj1_topbm. bak Mysql-u username-p Password Database Name <absolute path of the backup file

If ERROR 1214 (HY000) at line 56475: The used table type doesn't support FULLTEXT indexes appears

It indicates that your original table may use full-text indexes, but the current engine does not support

Modify the mysql configuration file

The Code is as follows:	Copy code
# INNODB by default Default-storage-engine = MyISAM

The mysqldump tool has a large number of options. Some options are shown in the following table:

Option/Option Action/Action completed MED

-- Add-drop-table

This option will add the drop table if exists statement before each TABLE. This ensures that errors will not occur when importing data back to the MySQL database, check whether the table exists and delete the table if it exists.

-- Add-locks

This option binds a lock table and an unlock table statement in the INSERT statement. This prevents operations performed on tables by other users when these records are re-imported to the database.
　　
-C or-complete_insert

This option causes the mysqldump command to add the column (field) name to each INSERT statement. This option is useful when exporting data to another database.

-- Delayed-insert: add the DELAY option to the INSERT command.

-F or-flush-logs uses this option to refresh the log of the MySQL server before executing the export.

-F or-force: use this option to continue exporting even if an error occurs.

-- Full: This option adds the additional information to the create table statement.

-L or-lock-tables: When you export a table, the server locks the table.

-T or-no-create-info

This option does not allow the mysqldump command to CREATE a create table statement. This option is convenient when you only need data and do not need DDL statements.
　　
-D or-no-data does not create an INSERT statement for the mysqldump command.

You can use this option when you only need DDL statements.

-- Opt this option will open all options that will increase the File Export speed and create a file that can be imported more quickly.

-Q or-quick does not allow MySQL to read the entire exported content into the memory and then execute the export. Instead, MySQL writes the exported content to the export file when reading the exported content.

-T path or-tab = path will create two files. One file contains DDL statements or table creation statements, and the other file contains data. The name of the ddlfile is table_name. SQL, and the name of the data file is table_name.txt. The path name is the directory where the two files are stored. The directory must already exist, and the user of the command has privileges on the file.
　　
-W "WHERE Clause" or-where = "Where clause"

As mentioned above, you can use this option to filter the data to be placed in the exported file.