MySQL DBA in my heart, MySQLDBA in my mind

MySQL DBA in my heart, MySQLDBA in my mind

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 websites on the Internet. Because of its small size, fast speed, and low total cost of ownership, especially the open source code, many small and medium websites have chosen MySQL as their website database to reduce their total cost of ownership. As MySQL is widely used on the Internet, its position in the database field has exploded, and the de-IOE of various Internet such as BAT has been widely used as the core database, the market demand for MySQL DBAs has increased dramatically. Good MySQL DBAs are the most popular talents of Internet companies. More and more IT talents begin to transform into DBAs. As a DBA, we must first understand that the core goal of DBAs is also the ultimate goal: to ensure the stability, security, integrity and high performance of the database management system.

MySQL DBA can be divided into two types: Development DBA and O & m dba. Here MySQL DBA refers to O & m dba. Generally, DBA refers to the O & m dba, which is only responsible for database operation and maintenance, including database installation, monitoring, backup, recovery, and other basic work, senior or senior DBAs have much responsibilities than this. They need to cover the entire lifecycle of products from requirement design, testing to delivery and launch, in this process, we should not only be responsible for the establishment and O & M of the database management system, but also participate in the preliminary database design, mid-term database testing, and later database capacity management and performance optimization.

O & m dba has the following responsibilities:

1) Monitoring: monitors the running status of database services in real time, including database sessions, database logs, data file fragments, table space monitoring, and user access monitoring, detects Database Service Operation exceptions and resource consumption at any time; outputs important daily database service operation reports to evaluate the overall running status of database services and discover database risks; monitoring is vital for DBAs and the third eye of DBAs. It helps monitoring automatically handle some common faults and discover and solve potential risks in advance. monitoring is essential for O & M, if you do not understand monitoring, you are not a competent O & M personnel;

2) backup: Develop and implement database backup plans, recover database information when a disaster occurs, and maintain the archive or backup data on the appropriate media. The database backup policy should be changed according to actual requirements, and the daily data backup status should be monitored. Backup can be divided into two types: Regular and scheduled backup, including full backup and Incremental backup. Commonly used tools are third-party backup tools represented by mysqldump and xtrabackup; one is the backup that is updated and deleted when DML is executed. This backup generally has the following backup methods: one is to create tablexxx_mmddhhmm select to back up to the table, one is mysqldump and conditional backup, and the other is select into oufile and other methods to prevent quick recovery when errors are executed. In daily database operations, especially as a DBA, you always have to leave a hand for yourself to ensure that you can easily roll back the data when necessary.

3) Security Audit: different database management system users are assigned different access permissions to protect the database from unauthorized access and destruction. For example, one type of user can only retrieve data, and the other type of user may have the permission to update data and delete records.

4) troubleshooting: handle any exceptions in the Database Service in a timely manner, and avoid resizing or even suspending the service as much as possible. Prior to this, DBAs had to develop plans for handling various service exceptions, such as machine room/network faults and program bugs. When a problem occurs, the DBA can automatically or manually execute the plan to stop the loss. In troubleshooting, DBA's response is crucial. A small fault may cause a major fault within minutes, which has a greater impact on the business, this tests DBA's emergency response and response capabilities. For common faults, such as sudden increase in the number of connections, cleaning up the database server space, master-slave synchronization latency, and high CPU usage, you can work out relevant plans with R & D and combine them with monitoring, when a threshold value is reached, the corresponding script is used for automatic processing, which reduces DBA workload to a certain extent and improves fault handling efficiency, more importantly, you can win a certain amount of time for the DBA to prevent the service from being greatly affected by the fault escalation.

5) capacity management: plan and implement resource evaluation, expansion, data center migration, and traffic scheduling after database scale expansion.

6) database performance optimization: the most important thing for a product to provide external services is the user experience. What is important in the user experience is the availability and response speed of the product. DBA is also responsible for how to use the most reasonable resources to support products to provide high-availability and high-speed user experience. After many years of DBA career, my personal summary of database faults is: 80% of or higher faults come from abnormal SQL statements, and about 10% of faults come from unreasonable database architecture design, about 10% comes from the hardware and OS layers. The basic optimization focus is on SQL optimization. This requires training developers on MySQL development specifications, developing strict SQL access rules, and reviewing each SQL statement launched, block most of the SQL source, and then conduct monitoring and analysis on slow SQL, regularly send it to R & D for rectification, and urge R & D to make rectification within a limited period of time. This part of work is tedious and dull, but it can be executed well to prevent many unnecessary faults and ensure the efficient and stable operation of the business. This part of work tests the DBA's basic theoretical knowledge, test DBA's responsibility and perseverance.

I personally think that the basic skills required by a good DBA include: understanding data backup/recovery and disaster recovery, use of common DBA tool sets, how to quickly find answers, how to monitor and optimize data database performance, automation, Capacity monitoring and planning as much as possible, index design, database design, database security, continuous learning and research of new versions, you can write independent or systematic functional scripts separately. These skills cover basic theoretical knowledge of MySQL, OS, hardware, network, code writing, architecture design and planning, monitoring, O & M concepts, and O & M automation. Therefore, as a DBA, the basic knowledge covers many layers rather than the database layer. Therefore, it can be said that DBA has a high comprehensive knowledge of compound professionals.

Personality determines fate and Attitude determines everything. The same is true for DBAs. Personality determines height and Attitude determines level. Some DBAs have been working for many years and are still marginal people. They cannot become technical backbone. In the final analysis, they may be related to their own personalities. If they are not enterprising, they cannot take the initiative in their work, they can say how much they do, and they are not doing things, if you don't want to work together, you can work together. The concept of time is not strong. Some people think that the company will pay 1000 yuan a month. Why should I do 1001 yuan of work? As long as there is no human accident, the natural disasters and man-made disasters will be deemed unlucky. Such a character will not have outstanding achievements in my career. Some DBAs have published their books, and the theory and other skills are certainly correct. However, they are rough in their work and are not comprehensive in consideration of the problems. From time to time, there may be minor accidents, sometimes I always feel that I can do things right away. The interview may give an excellent impression, but I can see problems at work, every DBA business in a company does not last long. After all, the eyes of the masses are bright. Every company and every leader must be able to work and be able to work well. A good DBA should be a perfectionist in my mind. It should have hard and hard qualities including meticulous work, diligent thinking, good communication skills, and team spirit, self-driving ability or strong work initiative, strong willpower and calm mentality, curiosity and self-confidence. Basic skills are the foundation and necessary for DBAs. If you talk about excellence, so good personality and attitude determine how far you can go in your career.

Everyone understands the truth, and there are not many people who can actually implement it. These few people have become the backbone of the industry! The last thing I want to talk about is that there are not the best in every industry. There is no end to learning. In terms of learning and practice, we can improve and strengthen our capabilities in all aspects, and constantly improve ourselves. Do things in a down-to-earth manner, be honest and honest, and never change others. Do your best and be the best of yourself !!