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

The company has lost data in the database these days, so we went to the operation and maintenance department to restore the database, but our engineer, after executing the recovery command, prompted a syntax error and did not recover the database.

Specific reason: due to the syntax error or the specified directory does not exist during the backup, but did not report an error during the backup, so the engineer thought that the backup was successful, which eventually led to a disaster; (become the Black Pot Man)

The company finally showed mercy, deducting a penalty of 1000 yuan per person. Here is a summary and simulation of the normal process.

The blogger's new blog address: https://www.dgstack.cn/ comes with a surprise ~ ~ Welcome to collect

First, error backup demonstration

Syntax error, backup failure looks like a successful backup, but when viewing the contents of the backup, there is a warning of syntax error

[root@db02] # mysqldump-uroot-poldboy123-A-B oldboy > / opt/oldboy_ bak1.sql [root @ db02 ~] # cat / opt/oldboy_bak1.sqlUsage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS]-- databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS]-all-databases [OPTIONS] For more options, use mysqldump-- help# hint: # 1, the error point is that-An and [- B specify library name] cannot be used at the same time Syntax errors will occur. You can see the following explanation. There is a conflict in the specified scope #-A:--all databases all database backups #-B: specify multiple database backups to add database building statements and use statements # mysql cannot use kill-9 or you will bear the consequences

Add: when we post, we usually connect to mysql through the web server, and then store the data in the database through insert statements.

The parameter description of mysqldump, summarizing several commonly used ones:

-A:--all databases all database backups-B: specify multiple database backups to add database building statements and use statements-- compact to reduce useless output-remove annotated SQL statements from backup files, suitable for debugging. It is not recommended to use-F to refresh binlog logs and produce new files in production. The future increment starts from this new file (full recovery = full + critical point binlog)-master-data adds the binlog log file name and the corresponding location point (with locking table function) (that is, CHANGE MASTER statement)-- master-data=1 does not comment executable-- master-data=2 comment information (- x is a locking table, why lock the table? The main thing is to ensure the integrity of the backup data, do not appear "jagged" data, try to back up when the business is at a low ebb or add a read lock to all tables in the lock table-l of the slave database dedicated to backup. (open by default, turn it off with-- skip-lock-tables, the above option will turn off-l option)-d only backup database table structure no data-t only backup data without table structure SQL statement form-T library table, data separation different files, data is in text form,-- single-transaction is suitable for InnoDB data database backup, it has Acid feature, isolation: after performing dump, you can only see the previous data. After the insertion is isolated-Q,-- quick is directly exported and does not write to memory Don't buffer query, dump directly to stdout.## fast (Defaults to on Use-- skip-quick to disable.)-- single-transaction does not lock tables during backup, but also ensures data consistency (based on innodb engine). For example, take a picture of everyone and specify socket for "Snapshot"-S multiple instances.

When events-warning appears, here is how to handle it:

This is because mysqldump does not back up the event table by default and only adds-- events. The solution: add-- events or-- ignore-table=mysql.events parameter; # Export event # ignore the meaning of a table. You can check mysqldump-uroot-poldboy123-S / data/3307/mysql.sock-- events-A > all.sql by mysqldump-- help.

The function of "adding database building statement" of-B

If there is no parameter-B, there will be no following in the backup file, and there will be no action of building a database during recovery.

CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `mysql` / *! 40100 DEFAULT CHARACTER SET utf8 * /; USE `mysql`

How to refuse to become the Black Pot Man: bold and careful, good at summing up, absorbing experience, and getting rid of the blame.

Second, correct backup demonstration

Do backup and recovery drills or restore tests regularly

1, create the student table in the test database

Mysql > use test; # # enter the test library create table student (id int (4) not null,name char (20) not null,age tinyint (2) NOT NULL default '0describe varchar (16) default NULL); # # create the student table with the settings of each field

2. Back up the database test

Mysqldump-uroot-poldboy123-S / data/3307/mysql.sock-B test > / opt/test.sqlcat / opt/test.sql # check whether the backup is valid

3. Delete the test library (simulate real environment)

Mysql > drop database test;Query OK, 0 rows affected (0.00 sec) mysql > show databases; # No test data +-+ | Database | +-+ | information_schema | | mysql | | oldboy | | performance_schema | +-

4. Restore the test library

[root@db02 ~] # mysql-uroot-poldboy123-S / data/3307/mysql.sock show databases; # restores the test library, because it was backed up with-B before, so now you don't need to specify the library, just restore mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | oldboy | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) mysql > desc student +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (4) | NO | | NULL | | name | char (20) | NO | | NULL | | age | tinyint (2) | NO | | 0 | | dept | varchar (16) | YES | NULL | | +-+ | -+ 4 rows in set (0.00 sec)

At this point, the simulation data has been restored successfully.

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.