Mysqldump Backup command

Mysqldump Backup Instructions:

#mysqldump-uroot-p123456 Test > Test.sql #mysqldump-uroot-p123456-b Test > Test.sql #这两个的差别:-B database to be created Name will also be backed up #mysqldump-uroot-p123456-b test | gzip > test.sql.gz Backup Library and compress

mysqldump Principle of Backup:

is actually the data from the MySQL library in the form of logical SQL statements directly output or generate a backup of the file process. belongs to a logical backup.

Physical Backup: Package files directly.

#mysqldump-uroot-p123456-b Test Xpg | gzip > Test.sql.gz #同时备份多个库

What is the meaning of the sub-library backup?

If there is a problem with the data of a library, just import the data into it, the significance of the library

To back up a single table:

#mysqldump-uroot-p123456 dbname tablename > Test.sql #备份一个库里面的一个表 #mysqldump-uroot-p123456 dbname tablename1 Tablename2 > Test.sql #备份一个库里面的多个表 #mysqldump-uroot-p123456-d dbname tablename > Test.sql #只是备份一个表的结构, no data backup #my Sqldump-uroot-p123456-t dbname tablename > Test.sql #只是备份一个表的数据

Back up all data inside the database:-A

#mysqldump-uroot-p123456-a-B--events | gzip > test.sql.gz

--master-data=1 This marks the location of the current Binlog log record, recovering the data from the master_log_pos behind the log.

#mysqldump-uroot-p123456--master-data=1 Test > Test.sql

InnoDB: Storage Engine (--single-transaction: Lock table)

#mysqldump-uroot-p ""-a-b--master-data=1--single-transaction | Gzip >/opt/all.sql.gz

08-use Source to recover MySQL data to explain and combat

Source needs to log in to the database.

Mysql>drop database dbname; Delete a library.

Mysql>system ls/opt #查看opt下面有什么文件

Mysql>source/opt/...sql

09 Library Backup after MySQL how to practice of library recovery

You did not use-B when you backed up. Recovery is required to specify the library

#mysql-uroot-p ' 123456 ' dbname </opt/mysql_bak.sql

If it is a compressed backup file.

You can use gzip-d to compress files first. And then in the import.

10-mysql process-Status-online modification of parameters important instructions explained

-E: Interactive display

Set global variables to use set Globals key_buffer_size=:, restart after failure

Summary: Production of common commands

Show Global Status: View entire database health information, important analysis and monitoring

Show full processlist: View the complete SQL statement being executed

Set global key_buffer_size=32777218 does not restart database tuning database parameters, directly effective, restart after failure

Show variables: View parameter information for a database

11-mysqlbinlog Command Introduction and practical explanation

mysqlbinlog--"parsing MySQL binlog log

What is a binglog log?

Under Data. Mysql-bin.index is the index of the Binlog log file

The function is: never record MySQL internal additions and deletions and other updates to the MySQL database records. Select is not logged.

Binglog logs all tables in all libraries. Binlog logs for related libraries cannot be recorded separately

Typically, all library logs are in a binlog.

So we're going to do the library: import a library log into a new file

#mysqlbinlog-D Test mysql-bin.000001 > Test.sql #将test库的binlog日志导入到test. sql

Mysqldump Backup command