How to delete the database without running away

Time:2020-11-27

We all know the basic characteristics of transactions, acid. Transactions are persistent. Once a transaction is committed, the changes to the data in the database are permanent. Even in the case of database system failure, the transaction commit operation will not be lost.

I often hear which programmer has run away from deleting the database. If the database is really deleted, what can be done to save these operations or prevent the occurrence of this event.

First, we need to understand binary logs

reference resourcesSummary of MySQL binary log

The binary log of MySQL is a binary file, which is mainly used to record MySQL statements that modify data or may cause data changes. Binary log records all operations to change MySQL database, and records statement occurrence time, execution time, operation data and other additional information. However, it does not record SQL statements that do not modify data such as select and show. Binary log is mainly used for database recovery, master-slave replication, and audit operations.

Binary logs are recorded in the form of events, not transaction logs (but may be based on transactions). This does not mean that it only records InnoDB logs. MyISAM table also has binary logs.

For the operation of transaction table, binary log is only written at one time when the transaction is committed (InnoDB binary log based on Transaction). Each binary log record before commit is cached and written at commit time.

Therefore, for a transaction table, a transaction may contain multiple binary log events that are written once at commit time. For non transaction table operations, the statement is written directly every time the statement is executed.

By commandshow variables like '%log_bin%';Check whether the binary log is enabled. You can see that my binary log is enabled

How to delete the database without running away

MariaDB / MySQL does not start binary log by default. You can also set log bin in the [mysqld] section of the configuration file. Note: for MySQL 5.7, starting binlog directly may cause the MySQL service to fail to start. In this case, you need to assign a server to the MySQL instance in mysqld in the configuration file_ id。

Add the following parameters

server-id=1
log-bin=[on|filename]
log_bin_index=filename.index 
#The configuration information is the file name of log bin plus. Index. For example, if log bin is configured as MySQL bin, the configuration here is mysql- bin.index
binlog_format=[mixed|row|statement]
#State mode (SBR)
#Each SQL statement that will modify the data will be recorded in binlog. The advantage is that it does not need to record the data changes of each SQL statement and each row, reducing the amount of binlog log log, saving IO and improving performance. The disadvantage is that in some cases, the data in master slave will be inconsistent (such as sleep() function, last_ insert_ ID (), user defined functions (UDF) and so on

#Row mode (RBR)
#The context information of each SQL statement is not recorded, only which data has been modified and what has been modified. In addition, there will be no problem that the calls and triggers of stored procedures, functions, or triggers under certain circumstances cannot be copied correctly. The disadvantage is that a large number of logs will be generated, especially when the alter table is used.

#Mixed mode (MBR)
#For the mixed use of the above two modes, the general replication uses the state mode to save the binlog. For the operations that cannot be copied in the state mode, the row mode is used to save the binlog. MySQL will select the log saving method according to the SQL statements executed.

View binary log

By commandshow binary logs;Binary log files can be viewed

How to delete the database without running away

Before we look at the binary log, let’s use the commandflush logs;Clear the binary log

Reuseshow master statusView the currently running binary log file. You can see that the currently running binary log file is mysql-bin.000056;

How to delete the database without running away

Pass the command againshow binlog events in 'mysql-bin.000056';View the binary file event content, as shown in the following figure

How to delete the database without running away

Where log_ Name is the current binary file name, POS is the current event start offset, event_ Type is the current event type, server_ ID is the server ID value configured in the configuration file, end_ log_ POS is the end offset of the current event, and info is the related description of the event (such as transaction start and end, SQL statement execution, etc.)

Of course, we can also use the mysqlbinlog tool

To view the specific contents of the binary file, execute themysqlbinlog [option] log-file1 log-file2...Read and parse binary file

Here are some common options:

-d. -- database = name: only view the log operations of the specified database

-o. -- offset = ා: ignore the first N operation commands in the log

-r. -- result file = name: output the output log information to the specified file. Redirection is also available.

-s. -- short form: displays the log in a simple format, only records some common statements, and omits some additional information, such as location information, time information and line based log. Can be used for debugging, production environment must not be used

--set-charset=char_ Name: when outputting log information to a file, add '` set' ` names char to the first line of the file_ name`

--Start datetime, - stop datetime: Specifies the output of all log information within the start time and end time

--Start position = ා, - stop position = ා: specifies to output all log information in the start and end positions

-v. - VV: display more detailed information. The log based on row will not be displayed by default. You can view it by using - V or - vv

I do it through executionmysqlbinlog /www/server/data/mysql-bin.000056 > /root/binlog.txtRedirect output to binlog.txt In the file

The following is about the output of binary files

# at 52746
#201019 14:33:53 server id 1  end_log_pos 52843 CRC32 0xfa71c68d     Query    thread_id=41332    exec_time=0    error_code=0
SET TIMESTAMP=1603089233/*!*/;
BEGIN
/*!*/;
# at 52843
#201019 14:33:53 server id 1  end_log_pos 53228 CRC32 0xac4385c3     Query    thread_id=41332    exec_time=0    error_code=0
SET TIMESTAMP=1603089233/*!*/;
update `goods` set `min_price_attr_id` = 66938, `max_price_attr_id` = 66938, `goods`.`updated_at` = '2020-10-18 23:33:53' where `id` = 666 and `goods`.`deleted_at` is null and (`store_id` = 3) and (`is_store` = 1 and `max_price` is not null and `min_price` is not null) order by `position` asc
/*!*/;
# at 53228
#201019 14:33:53 server id 1  end_log_pos 53259 CRC32 0x75346610     Xid = 1227571
COMMIT/*!*/;
# at 53259
#201019 14:36:25 server id 1  end_log_pos 53324 CRC32 0x19d0106c     Anonymous_GTID    last_committed=17    sequence_number=18    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 53324
#201019 14:36:25 server id 1  end_log_pos 53421 CRC32 0xb8303b88     Query    thread_id=41341    exec_time=0    error_code=0
SET TIMESTAMP=1603089385/*!*/;
BEGIN
/*!*/;
# at 53421
#201019 14:36:25 server id 1  end_log_pos 53806 CRC32 0xe0a65551     Query    thread_id=41341    exec_time=0    error_code=0
SET TIMESTAMP=1603089385/*!*/;
update `goods` set `min_price_attr_id` = 66938, `max_price_attr_id` = 66938, `goods`.`updated_at` = '2020-10-18 23:36:25' where `id` = 666 and `goods`.`deleted_at` is null and (`store_id` = 3) and (`is_store` = 1 and `max_price` is not null and `min_price` is not null) order by `position` asc
/*!*/;
# at 53806
#201019 14:36:25 server id 1  end_log_pos 53837 CRC32 0x79445d95     Xid = 1227807
COMMIT/*!*/;

Content description referenceDescription of binlog output content of MySQL binlog

  • There are many lines beginning with at in the above information, which are used to indicate the offset of each event in the binlog. For example, ා at 52746 indicates that the position of the event in binlog is 52746 bytes. An event in MySQL is marked between two ා at’s, and end is also used when the event starts_ log_ POS indicates where the event ends

  • Server ID 1 indicates that the transaction server ID number is 1 my.cnf The ID defined in the file cannot be duplicated in the cluster. One is to identify the server generated by the transaction; the other is to interrupt the replication and redo of binlog in a cluster environment similar to the dual master mode

  • CRC32 is used to check the integrity of binlog file

  • Gtid indicates that the gtid property is enabled

  • last_ Committed = 17 transaction group commit ID, transactions submitted by the same group have the same last_ Committed value, which can be replayed in parallel in the slave library to reduce synchronization latency.

  • sequence_ Number = 18, the sequence number corresponding to the transaction. This value increases monotonically. At the same time, it also identifies the order in which the same group of transactions are committed. Set slave in the slave database_ preserve_ commit_ When order = 1, this value is used to simulate the submission order of the master database, and then the slave database is submitted. In order to achieve the data drop process is completely consistent

  • SET @@ SESSION.GTID_ Next =’anonymous’, identifies the gtid sequence number used by the transaction

  • SET TIMESTAMP=1603089385/!/It identifies the time of the transaction and ensures data consistency in some special cases. For example, the SQL with the now function may be delayed for a long time before it is transferred to the standby database. At this time, the now function takes the value and inserts it to ensure that the execution time is consistent with that of the main database.

  • Begin starts executing the transaction

  • Table_map: test.testxxxxMapped to number 185 to identify which table in which library to operate

  • update goods set min_price_attr_id = 66938, max_price_attr_id = 66938, goods.updated_at = ‘2020-10-18 23:36:25’ where id = 666 and goods.deleted_at is null and (store_id = 3) and (is_store = 1 and max_price is not null and min_price is not null) order by positionASC is the specific executed SQL statement

  • XID = 1227807 is an identifier to ensure data integrity. Every DML transaction committed and fully written to the binlog must have an XID at the end (DDL does not exist). The XID value is also recorded in redolog. In the 2pc commit mechanism, this identifier is used to compare redolog and binlog. When a crash occurs, if redolog does not commit, if redolog and binlog have the value, the transaction will be redone. If binlog does not find the value, the transaction will be rolled back. If redolog does not have this value, it is not necessary to find binlog and the transaction will be rolled back directly

  • Finally, the commit ID commits the transaction

Preparation before deleting database

Turn on binary log

Start the corresponding binary log in the configuration file

Regularly back up the database

The data backup script can be written, and the scheduled task can be executed regularly to save the backup

#! /bin/bash

user="root"
passwd="123456"
database="test"

BCK_ Dir = / root / MySQL backups "ා backup file directory
DATE=`date +%F`

#Creating files
mkdir -p $BCK_DIR/$DATE

mysqldump -u$user -p$passwd --databases $database > $BCK_DIR/$DATE/$database.sql
#crontab -e
#Set up backup at 1:00 a.m. every day 
0 1 * * * /home/sh/dump.sh

Practice database deletion

Create a database named binlog in the database

How to delete the database without running away

There is a table named test in the database

How to delete the database without running away

Before deleting the database, we should first make a full backup of the whole databasemysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

Because before performing binary log file recall, the database needs to have relevant data, such as the corresponding table structure data. So that we can restore the data before the last backup, and then through the incremental log, we can restore to the environment before the deletion

And then we execute ordersdrop database binlogCommand to delete the database

First of all, we use the backup data, back to the last backup data, and use the commandcat /backup/mysqldump/all.db > mysql -uroot -p

Execute the ordermysqlbinlog /www/server/data/mysql-bin.000058 > /root/mysql-bin.000058.txtOutput the binary log file contents to a TXT file

Find the contents of the binary and navigate todrop database binlogStatement

# at 331017
# at 331049
#201020 16:58:58 server id 1  end_log_pos 331049 CRC32 0x639270f4     Intvar
SET INSERT_ID=1603/*!*/;
#201020 16:58:58 server id 1  end_log_pos 331552 CRC32 0xf87e2911     Query    thread_id=51712    exec_time=0    error_code=0
SET TIMESTAMP=1603184338/*!*/;
insert into `trackers` (`type`, `type_name`, `value`, `currency`, `content`, `uuid`, `store_id`, `time`, `from_host`, `from_path`, `url_host`, `url_path`, `updated_at`, `created_at`) values (102, 'VIEW_GOODS_CONTENT', '{{$selectAttr[\'price\']}}', 'USD', NULL, '034DF7A7E1E3E1590C5FCF28BBB0A975', 3, 1603184338, 'test.belment.cn', '/', 'test.belment.cn', '/product/ODQ2', '2020-10-20 01:58:58', '2020-10-20 01:58:58')
/*!*/;
# at 331552
#201020 16:58:58 server id 1  end_log_pos 331583 CRC32 0xca2c5c6a     Xid = 1495527
COMMIT/*!*/;
# at 331583
#201020 16:59:35 server id 1  end_log_pos 331648 CRC32 0x57c85e3d     Anonymous_GTID    last_committed=319    sequence_number=320    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 331648
#201020 16:59:35 server id 1  end_log_pos 331746 CRC32 0xd22d3ecb     Query    thread_id=51675    exec_time=0    error_code=0
SET TIMESTAMP=1603184375/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
drop database binlog
/*!*/;
# at 331746
#201020 16:59:48 server id 1  end_log_pos 331811 CRC32 0xf21596d0     Anonymous_GTID    last_committed=320    sequence_number=321    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 331811
#201020 16:59:48 server id 1  end_log_pos 331908 CRC32 0x8acc8fd4     Query    thread_id=51719    exec_time=0    error_code=0
SET TIMESTAMP=1603184388/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;

Thus, we should reset the binary offset to 331583

mysqlbinlog /www/server/data/mysql-bin.000058 --stop-position=331583 | mysql -u root -p

This work adoptsCC agreementThe author and the link to this article must be indicated in the reprint