Shulou(Shulou.com)06/01 Report--

Mysqldump is the utility that mysql uses to transfer the database. It mainly generates a SQL script that contains commands such as CREATE TABLE INSERT that are necessary to recreate the database from scratch.

Mysqldump syntax:

Default configuration read path: / etc/mysql/my.cnf / etc/my.cnf ~ / .my.cnf

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS]-- databases [OPTIONS] DB1 [DB2 DB3...]

OR mysqldump [OPTIONS]-- all-databases [OPTIONS]

Common parameters:-u,-- user=name specify user-h,-- host=name specify host-p,-- password [= name] specify password-A,-- all-databases specify all libraries,-An and-- all-databases do not use-B at the same time,-- databases only specify-- databases indicates backup of a single database Multiple databases are separated by spaces; if you do not specify-- databases means to back up the entire database; only specify dbname to back up a single table Separate multiple tables with spaces-master-data [= #] 0 means off, default value 0 means to print CHANGE MASTER command information at the beginning, and 2 means to add comment information in the replication scene to record the current binary log and location-single-transaction if all are InnoDB engine tables Then you don't need to use-- lock-all-tables Hot standby InnoDB table can be realized based on this option. A single large transaction will be started, and the impact on the global service may be lasting-- lock-all-tables requests to lock tables before performing backups, generally locking only the databases and tables currently to be backed up-- add-drop-database deletes existing databases before recovery-- add-drop-table deletes existing corresponding tables before recovery-C -- compress compresses the data and then transmits it over the network-E,-- events backs up the data at the same time Backup event scheduler code-R,-- routines backup data at the same time, backup stored procedures and storage functions-d,-- no-data means that only backup table structure does not back up data, copying table structure, empty library to other servers is useful. Not available at any other time-opt starts various advanced options at the same time

Backup method:

Full + incremental + binary log

The prerequisite is to add parameters to the configuration:

Default-storage-engine = InnoDB sets the default storage engine to InnoDB

Innodb_file_per_table=1 sets a single tablespace per table

Log-bin=mysql-bin enables binary function

Backup process: full backup: using InnoDB engine, backup uses-- single-transaction option to achieve hot backup without locking tables; with non-InnoDB engine, backup needs to use-- lock-all-tables option to request to lock all tables and start backup mysqldump-uroot-pmypass-- single-transaction-- all-databases-- master-data=2 > / backup/mysql_fullbak.sql incremental backup: mysql > SHOW MASTER STATUS Check which log file and location you are currently in less / backup/mysql_fullbak.sql check where to start mysqlbinlog-- start-position=4579-- stop-position=4868 mysql-bin.000015 > / backup/increment.sql specify the start and end locations to export as incremental backup binary log backup: mysql > INSERT INTO test (Name,Age) VALUES ('tom',18) After incremental backup, insert a piece of data mysql > DROP DATABASE hello; and delete the database mysql > SHOW MASTER STATUS at this time Check which log file and location you are currently in mysqlbinlog-- start-position=4868 mysql-bin.000015 view the starting location of the binary log before the database crash The end location must be the location before the database deletion mysqlbinlog-- start-position=4868-- stop-position=5062 mysql-bin.000015 > / tmp/hello.sql data export recovery process from binary log startup location to pre-crash location: premise: empty all files in the data directory and reinitialize database mysql > SET GLOBAL sql_log_bin=0 The binary log needs to be temporarily closed during recovery. If it is not closed, the recovery data will be recorded in the binary log to increase the difficulty of log management mysql > FLUSH LOGS; to manually scroll the binary log once mysql

SET GLOBAL sql_log_bin=1; recovery completes opening binary log

After recovery, you need to use mysqlcheck-uroot-pmypass-c-all-databases to check whether there are problems in all tables. Please remember to make a full backup of the database after all recovery is completed.

At this point, the use of mysqldump backup recovery is complete, if there are any mistakes, please advise!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.