MySQL DBA in my heart

Original URL Link: http://wangwei007.blog.51cto.com/68019/1718311

MySQL is a cross-platform open source relational database management system, currently MySQL is widely used in small and medium-sized websites on the internet. Because of its small size, fast speed, low total cost of ownership, especially the open source, many small and medium-sized web sites in order to reduce the total cost of ownership of the site chose MySQL as the site database. As MySQL is widely used on the Internet, in the field of database explosion, bat and other Internet to the IoE, a large number of MySQL as the core database, MySQL DBA market demand is increasing, the good MySQL DBA is the various internet companies sought-after talent. As more and more IT professionals begin to transform into DBAs, then as a DBA, it is important to understand that the core goal of being a DBA is also the ultimate goal: to ensure the stability, security, integrity, and performance of the database management system.

MySQL DBAs can be divided into two types: one is the development DBA, the other is the operational DBA, and the MySQL DBA referred to here is the operational DBA. In general, DBAs refer to Operations DBAs, who are responsible for the operation and maintenance of the database, including database installation, monitoring, backup, recovery, and other basic tasks, and senior or senior DBA responsibility is much larger than this, need to cover the product from requirements design, testing to delivery of the entire life cycle, In this process, not only is responsible for the database management system construction and operation, but also to participate in the early database design, medium-term database testing and later database capacity management and performance optimization.

For operations DBAs, the main responsibilities are as follows:

1) Monitoring: real-time monitoring of the status of database service operation, including database sessions, database logs, data file fragmentation, table space monitoring, user access monitoring, etc., at any time to find the database service operation anomalies and resource consumption situation Output important daily database service run report to evaluate the overall health of database service, Discover database hidden trouble; monitoring is critical for DBAs, the third eye of the DBA, which facilitates monitoring to automate some of the usual failures, identify and address potential pitfalls, and monitor the skills necessary for operations. If you do not understand the monitoring, then you are not a competent operation and maintenance personnel;

2) Backup: Develop and implement database backup plan, recover database information when disaster occurs, maintain archive on appropriate media or backup data. The backup policy of the database is changed according to the actual requirements, and the daily backup of the data is monitored. There are two types of backups: A timed, scheduled backup with full and incremental backups, commonly used tools such as mysqldump, and third-party backup tools represented by Xtrabackup; a backup that is updated and deleted when DML is executed. This backup typically has the following backup methods: one for the Create TABLEXXX_MMDDHHMM select Backup to the table, one for the mysqldump plus the conditional backup, and the other is a SELECT into Oufile and other ways, Prevents a quick recovery when an error is executed. In the daily operation of the database, especially as a DBA, always give yourself a hand, to ensure that the need for data recovery can be readily picked up.

3) Security Audit: For different database management system users set different access rights to protect the database from unauthorized access and destruction. For example, allowing a class of users to retrieve data only, while another class of users might have permission to update data and delete records.

4) Fault handling: to deal with any anomalies in the database service in a timely manner, avoid the enlargement of the problem or even abort the service. Before this, DBAs need to deal with various types of service anomalies, such as computer room/network failure, program bugs and other issues to develop a processing plan, the problem can be automated or manual execution of the plan to achieve the purpose of stop loss. In troubleshooting, the DBA's response is critical, and it is possible that small failures can lead to large failures and greater impact on the business in minutes, and this is a great test of the DBA's ability to respond to emergencies and resilience. For common failures, such as a sudden increase in the number of connections, database server space cleanup, master-slave synchronization delay, high CPU use, can be developed together with the development of the corresponding plan, and monitoring, when a certain threshold, using the corresponding script automatic processing, to a certain extent, reduce the workload of the DBA, Improve the efficiency of troubleshooting, and more importantly, to the DBA for a certain amount of time to prevent failure to upgrade the business has a greater impact.

5) Capacity management: including resource evaluation, capacity expansion, engine room migration, traffic scheduling and other planning and concrete implementation.

6) Database performance optimization: The most important point of the product service is the user experience, which is very important in the user experience is the product availability and response speed. And how to use the most reasonable resources to support products to provide a high-availability and high-speed user experience, which is the important responsibility of the DBA. In the years of DBA career, the personal summary of the database failure is: 80% or even higher fault from the abnormal SQL statement, about 10% from the database architecture design unreasonable, about 10% from the hardware, OS level. Then the basic optimization is the optimization of SQL, this need to the developers to develop specifications for MySQL training, strict SQL access rules, audit each line of SQL statements, at the source of SQL blocking most, and then the slow SQL monitoring analysis, Regularly sent to research and development to rectify, and urge research and development in a limited time to rectify. This part of the work is more tedious and dull, but the implementation of the good, can eliminate many unnecessary failures, to ensure the efficient and stable operation of the business, this part of the work is very test DBA basic theoretical Knowledge, test DBA responsibility and perseverance.

Personally, the basic skills that a good DBA needs include understanding data backup/recovery and disaster recovery, using the DBA's Common toolset, knowing how to quickly find answers, knowing how to monitor and optimize database performance, automating as much as possible, capacity monitoring and planning, indexing design, database design, Database security, continuous learning and research, and the ability to write standalone or system functional scripts independently. These skills basically cover MySQL basic theory knowledge, OS level knowledge, hardware knowledge, network knowledge, code writing ability, architecture design planning ability, monitoring, operation and maintenance concept, operation and maintenance automation, etc. Therefore, to be a DBA, not purely database-level, the basic knowledge covers many aspects, so it can be said that DBA comprehensive knowledge of highly complex professionals.

The disposition decides the destiny, the attitude decides everything, the DBA is also such, the disposition decides the height, the attitude decides the level. Some DBAs have been doing for many years, is still marginal people, can not become the backbone of technology, in the final analysis may be related to their own character, do not think enterprising, work is not active, say how much to do, do sloppy, do not mind, can make it right, time concept is not strong, some people think the company one months to me 1000 Why do I have to do 1001 bucks? As long as there is no man-made accident on the line, natural and natural disasters, such personality in the personal career will not have outstanding achievements. Have met some DBAs have written books, theory and other skills must be no problem, but things rough, consider the problem is not comprehensive, from time to time a little accident, sometimes do things feel passable on the line, the interview when the impression may be excellent, but work up can see the problem, Every time in a company to do DBA business is not long, after all, the eyes of the masses are sharp, each company, every leader is the need to work, can make the job beautiful people. A good DBA, the DBA in my heart should be a perfectionist person, should have the hard qualities include work carefully, diligent thinking, good communication skills, team spirit, self-driving ability or say work initiative strong, tough willpower and calm mentality, curiosity and strong self-confidence and so on Basic skills are basic, DBAs must be basic, and if you talk about good words, then good personality and attitude determine how far you can go in your career.

We all understand the sermon, really can actually implement to the actual not much, it is these few people, become the backbone of the industry, so-called Daniel! The last thing to say is that there is not the most cattle in various industries, only more cattle, learning, in the study and practice to improve and strengthen their ability to all aspects, and constantly improve themselves. Steadfast work, upright man, can not change others, to do their best, to do their own!!

MySQL DBA in my heart