Mysqldump database backup and restore method

MySQL Backup:

The code is as follows	Copy Code
Mysqldump-uroot-proot Bookmark > Bm.bak Mysqldump-u User name-p password database name > absolute path of exported file

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

Use –skip-opt to export each piece of data separately, not after a single insert values a large string

The code is as follows	Copy Code
Mysqldump-uroot-proot–skip-opt Bookmark > Bm.bak

MySQL data restore

The code is as follows	Copy Code
Mysql-u root-proot TT <c:usersyyjdesktopbm.bak Mysql-u User name-p password database name < absolute path of backup file

If the error 1214 (HY000) at line 56475:the used table type doesn ' t support fulltext

It means that your original table might use Full-text indexing, and the current engine does not support

Modify MySQL configuration file

The code is as follows	Copy Code
#默认为INNODB Default-storage-engine=myisam

The Mysqldump tool has a number of options, some of which are listed below:

Option/option action/action performed

--add-drop-table

This option will precede each table with the drop table if exists statement, which guarantees that the MySQL database will be returned without error, because each time it is returned, the table is first checked for existence and deleted.

--add-locks

This option bundles the previous lock table and unlock table statement in the INSERT statement. This prevents other users from working on the table when these records are imported again into the database
　　
-C Or-complete_insert

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

--delayed-insert Add delay option to insert command

-F or-flush-logs Use this option to flush the MySQL server log before performing the export.

-F Or-force Use this option to continue exporting even if errors occur

--full This option adds additional information to the statement in the CREATE table

-L or-lock-tables using this option, the server will lock the table when the table is exported.

-T Or-no-create-info

This option causes the mysqldump command to not create a creating table statement, which is handy when you need only data without the need for DDL (database definition statements).
　　
-D or-no-data This option causes the mysqldump command to not create INSERT statements.

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

--opt This option opens all options that will increase the speed of file exports and create a file that can be imported faster.

-Q or-quick This option allows MySQL to not read the entire exported content into memory and then perform the export, but write to the guide file when read.

-T Path or-tab = Path This option will create two files, one file contains DDL statements or table creation statements, and another file contains data. The DDL file is named Table_name.sql, and the data file is named 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 to the file.
　　
-W "where Clause" Or-where = "where Clause"

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