The experience of the maintenance work summary of MySQL database production environment

From the previous MySQL database production environment maintenance work, summary of some of the small experience and knowledge, may not be more esoteric, but for us to eliminate hidden dangers, to ensure that the MySQL database production environment Four 9 of the role is very effective one of the means, operators should pay attention to the details, minimize the probability of failure to occur.

　　(i) Recommendations for the writing of DML statements

　(1). DML statements do not allow the @number method to replace the field name

An unreasonable formulation:

UPDATE table_name SET @1=now () WHERE @2=1;

The correct wording:

UPDATE table_name SET column_name1=now () WHERE column_name2=1;

(2). UPDATE OR DELETE disables the limit clause

An unreasonable formulation:

UPDATE table_name SET column_name1=now () WHERE column_name2=1 LIMIT 1;

The correct wording:

UPDATE table_name SET column_name1=now () WHERE column_name2=1;

　　(3). Insert statement needs to write clear values and field correspondence

An unreasonable formulation:

INSERT into table_name VALUES (now (), Date_add (now (), INTERVAL +1 Day);

The correct wording:

INSERT into table_name (gmt_create,gmt_modify) VALUES (now (), Date_add (now (), INTERVAL +1 Day);

(4). DML statements with less uncertainty function

Common use of the uncertainty function: UUID (), RAND (), Sysdate () and other functions, if no special use, please replace it with a deterministic function.

Recommended Reading technical Articles: PPT content used for in-house training: MySQL development specification and practical technology exchange

(ii) Large data volume delete OR UPDATE

For a number of reasons and operational purposes, you need to clean up or change the value of a field in a database for two examples:

① Network Special rectification period, need to delete a large number of content containing certain keywords;

② give 100~1000 unequal amount of game money to a game player that meets a certain condition (for example: grade, online length);

2 Examples of data modification requirements, if directly according to the relevant requirements to do, one is to use the fuzzy query, another data update condition is not reasonable index available, this may cause Table object Table-level lock is locked for a long time, and blocking other types of change data operation services, so we have to use a more reasonable way, It is recommended that the following steps be implemented:

① design and create a table Tmp_pk_data for recording the primary key of the record to be modified, and the relevant information needed;

② gives priority to running a SQL command or stored procedure on the standby, and writes the primary key and related data to the table tmp_pk_data;

③ writes a stored procedure, uses the cursor loop control to obtain the Tmp_pd_data information, updates or deletes the data of the target table based on the primary key, and recommends that this action be done on the standby (note: it must be a dual-master replication mode before it can be performed on the repository);

　　(iii) Delete of regular regularity cleaning data

Regular regularity of data clean-up, priority on the target table data manipulation methods to classify:

① if the log type of data, it can be changed to the use of partitioned table, such as the conditions for data deletion by date, you can use the date as a data partitioning condition, and then delete the partition of the way to achieve data clean-up work;

② if the update/delete/select manipulation conditions of the data are consistent with or contained in the regular cleaning of the data, the partition table can be considered and the target of data cleanup is achieved by means of deleting the partition;

③ if the partition table can not be used to solve, you may consider referring to the "large data volume of the delete OR UPDATE" content described in the previous section;

　(iv) Large data-volume DML techniques for M-M architectures

Regular regularity of data clean-up, priority on the target table data manipulation methods to classify:

① if the log type of data, it can be changed to the use of partitioned table, such as the conditions for data deletion by date, you can use the date as a data partitioning condition, and then delete the partition of the way to achieve data clean-up work;

② if the update/delete/select manipulation conditions of the data are consistent with or contained in the regular cleaning of the data, the partition table can be considered and the target of data cleanup is achieved by means of deleting the partition;

③ if the partition table can not be used to solve, you may consider referring to the "large data volume of the delete OR UPDATE" content described in the previous section;