What mysql dba needs to do

Http://www.fastcloud.cn/zhishi/zhishi387.html

I wanted to write an article a long time ago to introduce some of the things that Internet DBAs need to do. However, I am not busy with platform construction. Recently, all modules have begun to take shape, so we have time to calm down and introduce it.

As we all know, Internet DBAs are very different from traditional industry DBAs, that is, there are many machines to manage and new technologies to be updated quickly. They are faced with many developments, complex network environments, and require standby; in this way, the Internet DBA is working on a traditional DBA, increasing complexity. We must consider how to deploy in large quantities, how to centralize monitoring and solve single point of failure (spof) to ensure. To achieve this, we do not rely on human resources. We must have a complete platform to support it, so what will the database platform look like?

1. Powerful Monitoring System (Supervision + control ):

Monitoring is our eyes, and we cannot stare at our DB 7x24 hours, so we need a monitoring system to help us stare. Once an exception occurs, monitoring not only notifies us, control is required. For example, when MySQL goes down from the database, we automatically turn it offline through monitoring; when the slave Database Synchronization status becomes invalid, the synchronization can be automatically repaired; and, with the increase of machines and the increase of instance daemon, we will find that our mobile phone alarms will increase dramatically. How can we reduce our alarms so that we can have a stable night's sleep? For example: which SMS messages and emails are sent. Can disk space alarms of all machines be integrated and reported? This is what we must consider in the monitoring system,

2. Automatic review system:

There are many developers and many projects, but the development habits are inconsistent. This may lead to a large header when we review the table structure. To ensure online unification, in order to prevent the development of magic SQL statements from being hurt or overwhelmed by N-Plus project reviews, we must have an automatic table creation review system that defines some rules, such: the reserved fields and primary keys cannot be INT, BIGINT, and so on. Then, the developer can enter the table structure to be launched, which is automatically reviewed by the system, approved, automatically released, and not approved, provide suggestions;

3. Slow log analysis system:

With the launch of the automatic review system, we may miss some SQL statements that are not very good at indexing. Therefore, we need the slow log subsystem to help us design the system, we need to consider capturing slow logs in real time, whether to regularly push slow logs every day, whether to push slow logs to development immediately after capturing slow logs, or give suggestions to development after automatic analysis, whether to filter slow logs or not, because it may be daily statistics, of course these are all automatic, after the design, no manual intervention is required;

4. Statistical System:

We must have a clear understanding of the overall running status of the online database, the changes in access volume, the changes in write volume, and the figure are dead, so that we will not cheat anyone; through access statistics, we can determine whether malicious access exists, whether optimization is required, and whether it is necessary to increase the number of nodes to withstand greater pressure;

5. Backup System:

Whether you believe it or not, I believe it. Cold backup is always a saving sign. No matter how good we are, there will always be faults, and drop database will also happen. Therefore, a complete backup system, it is imperative that we should consider whether our backup is normal, whether the backup data can be recovered, and how long it takes to restore it;

6. Management System:

We have hundreds of machines and thousands of machines. How can we clearly know how many daemon machines are running and what machines are running under DB Proxy, how to distribute scripts to the master database machine and slave database machine separately must be completed by the management system;

7. Intermediate Layer:

It is a double-edged sword. It can provide us with good extensions, such as dynamic addition of slave databases and master database failure detection; however, it brings DBA management complexity, more fault points, more bugs, and worse if DB Proxy performance is poor, in addition, to ensure client transparency, we must consider a lot of issues, such as connection persistence, such as character set, last_insert_id, and use dbname. If we have human development and maintenance, so I believe that Proxy will bring us joy;

All of the above systems provide support for us to manage databases. Without such system support, we would not be able to talk about database management platforms, nor batch management, nor databases that carry tens of billions of visits and hundreds of TB of data; of course, when such a system is involved, we also need to consider the introduction of new technologies, such as: If we can quickly build a NoSQL platform, and so on; of course, when deploying these modules, we always remember, all modules change. We need to keep learning and improving to create database services with lower downtime.

In the future, we will share with you how these modules are implemented and their progress.