Xxmysql database operation and Maintenance change process

MySQL database operation and maintenance change process

Drafting Time: 2015.11.13
Set Time:
Revision time:

0x00 Purpose

Standardize the operation and maintenance personnel change process of MySQL database, reduce the security hidden trouble caused by operating process and reduce the risk of human error.

0x01. Scene

1. The business personnel according to the business needs of the data Revision, library table field structure changes related requirements.
2. Operation and maintenance personnel according to the daily operation of the database, fault handling and performance optimization of the change requirements.

0x02. Type

1. Data correction (Insert,update,delete related operations)

　　Major changes

The data revised table has more than 100W of total data, and the table belongs to the core business.
Data revisions involve more than 50W of rows, and this table data is read-write frequent business tables.
Import single-sheet data file size exceeds 1G, online server pressure is large, import core business table or modify the table with frequent operation

　　General changes

If the amount of data to be revised is less than 1% of the total data of the table, and the condition of the data correction has a reasonable index.
If the data file size of the one-time import sheet is not more than 1G, then the choice is general change, but it is recommended to be completed after 18:00 or when the server pressure is relatively small.

　　

2. Database structure changes

major changes
        
      < Span style= "Font-weight:bolder;" >  Modify Table Structure : Table data size exceeds 1G, and do the following: Modify field names, Add/Remove fields, modify field properties
        Add/Modify index Properties : Table data size exceeds 1G, and changes are frequent or apply to database modification operations require very short response times
        Other changes that affect server performance: For example, mysqldump backing up a database, modifying the character set of a table or field, modifying the logic of a table partition

      General changes

New Table Object : For a table that does not exist on the database server, the newly created table needs to be selected as a general change for the project to go online or for other reasons.
changes with less data volume: table records are less than 100w or changes involving change records less than 50w, or data size smaller than 1G.
Other changes , such as modifying a table name, adding a comment to a table or field, creating and deleting a partition, deleting a table, modifying the value of the start position of the increment sequence, and so on.

3. Change of permissions

Major Changes

The database manages or clears the Library table permission authorizations, such as Grant, all, super, process, create User,truncate,drop.

General changes

Business Access (SELECT) Permission authorization
Data Change (insert,update,delete) Permission authorization

4. Server-side parameter changes

　　Major changes

Modification of server global variable values

5.MySQL Server Migration and upgrade

Core changes

involves core DB related MySQL server migrations and upgrades, master-slave rebuild, and other changes

Major changes

Related MySQL server migration and upgrade changes involving CDB database

core Changes , the change promoter must inform the operation and maintenance Leader,leader confirmation, by the operation and maintenance change personnel before the change to release the research and development and product personnel notification mail, determine the change time, change the object, change steps and fallback steps.
major Changes , the promoter of the change must inform the business leader in advance,leader confirmed by the change initiator and OPS personnel to determine the change time after the release of relevant personnel email notification and the RTX group notification.
General Changes , by the change initiator RTX group notify the relevant personnel, operation and maintenance personnel changes in the RTX group to synchronize information.

0x03. Change process

  Core Changes, the change promoter must inform the operation and maintenance Leader,leader confirmation, by the operation and maintenance change personnel before the change releaseResearch and development and product personnelnotification message, determine change time, change object, change step and fallback step. The core change requirement needs to be changed plan, the urgent requirement needs the Director level approval confirmation.
  Major Changes, the change promoter needs to inform the business leader in advance,after the leader is confirmed,the change initiator and OPS personnel determine the change time after the release of the relevant personnel email notification and the RTX group notification.
 General Changes, the change initiator of the RTX group notify the relevant personnel, operation and maintenance personnel changes in the RTX group to synchronize information.

Process content
request -to- audit --- process -- End

1. Apply
process responsible person : Change initiator
For planned change requirements, at least 12 hours prior to the request, for emergency needs to be confirmed after the request, and recorded in the problem management system.

The database operations change process complies with regular business change specifications, and non-regular working days (for example, within 2nd of the Friday or major holidays) change requests need to be emailed to the director for approval.

requirements of personnel through the RTX group and operators to communicate the requirements, after the demand confirmation in the demand work order system for the bill of lading, the choice of work order category "platform-side basic operations"-"database on-line Operations", the work Order description fill in the database changes in the specific object and content, if in doubt can be confirmed in the RTX communication



2. Audit
Process Responsible person : Operations personnel
requirements of personnel to request a database change request, operations and maintenance personnel to review the requirements, Audit by entering the next link, the audit does not pass the communication to confirm the cancellation of requirements or modify requirements.

3. Handling
: OPS personnel

response:
The Authorization class needs to be communicated with the demand party or processed within the validity period of the ticket.
Urgent and significant change requirements, and increase priority to respond immediately.

notice:
after the operator receives the relevant requirements, confirms that the requirements are regular or emergency, general or significant change requirements, Send a change plan notification email or pull the relevant person to the RTX group for notification.
The change starts and changes the key node, and the change end requires a reply in Rtx or in the mail.

backup:
Before changes need to back up the relevant server configuration, library table data, easy to restore data and configuration. The
relates to the structure of the library table, updating the overall data, which needs to be backed up before the change.

change:
Before the change takes effect, you need to reconfirm the change and avoid dealing with other change requirements as much as possible during the database change.

4. Verification
Process Responsible Personnel : operation and maintenance personnel and all notified personnel

After the operation and maintenance personnel changes, confirm the change effect, you need to promptly notify the needs of the initiator and the relevant RTX group or mail, by the need to initiate the final change effect confirmation.
After the requirements initiator verification pass, 30 minutes to 2 hours observation period, confirm the database changes after the relevant system is operating normally.

5. End

Process Responsible Person : Demand Initiator
Notify the relevant personnel, the change is over.

0x05. Handling of violation of process behavior
1. Violation of process behavior:

1. Changes not followed by the process
2. Change of non-authorized behavior

2. Violation of process behavior penalties:

Critical education is not affected by the violation of the database change process behavior.
In violation of the database change process behavior and impact, in accordance with the company and the OMG related punishment provisions.

0x06. Revision and release

1. Revision

Draft and revise the operations change process through communication with business parties and operations change personnel.

2. Release

After revising the content of the process, post the latest content and change the content email to inform all the developers.

0x07. Appendix

1. Self-built MySQL
MySQL database built and filed by OPS personnel in the context of business requirements.
2. Database Hosting
In the product technology Department of the MySQL database, operations through the relevant web pages to apply, database server by other technical department related personnel operations.
3. Core DB
Self-built MySQL cluster to place core data

requirements Application, reference format :

数据变更需提供：数据库IP：数据库端口：数据库库名，表名：业务名称：其他说明：授权需要额外提供：需要授权的主机IP：需要授权的权限：例如，select，insert，update,delete需要使用的账户名：账户是否已存在：

This article from "Ops said: from rookie to veteran" blog, please be sure to keep this source http://liuqunying.blog.51cto.com/3984207/1734383

Xxmysql database operation and Maintenance change process