Shulou(Shulou.com)06/01 Report--

There are also a lot of nnd problems in the Spring Festival. Today, we encounter two, one is that the mysql replication process is moving forward but the data is out of sync, and the other is that performance problems lead to all write and read blocking. [@ more@]

My staff do mysql replication, and when they are done, they find that read master position is constantly moving, but the data is out of sync. In fact, a little understanding of the function of server-id in mysql replication will know what's going on. When you open my.cnf immediately, you can see that there are 2 server-id. If you remove one, you will naturally ok. The judgment of this problem still depends on experience, but if you do things carefully, you will not have this malfunction.

When I went home in the evening, I received a phone call from the staff on duty in the subway, and there were a lot of sql jams in the billing system, and the synchronization of the database was slow.

At home, vpn analyzed the logs of the surveillance system. Look at the current situation of the system, locate the problem sql, the developer added an unnecessary sort caused sql to go the index full scan, 100w table concurrency of course died, but also a sentence update, directly leading to serial work of the replication process on the reading machine is slow, this situation is that oracle is also dead, or the development of cattle ah.

Immediately let the application stop the application related to the table, and add skip-replicate-table on the slave side to skip the relevant sql of the table, so that slave can synchronize other table data as soon as possible, otherwise there will be complaints that the excessive punch does not reach the account. After processing, we also found a bigger problem, because the database was forcibly closed after the problem was found, and mysql used myisam, coupled with skip to replicate the table, so the data of the table in master and slave were out of sync, so we had to build a new library, copy the table on the write library to the new database, then copy the table to the server, and finally use insert...select to synchronize the data. Why create a new library? because mysql replication continues sql, it is impossible to insert the same data on write and read simply using insert...select. So we have to do this, and the same thing is to comment out the log-bin and copy the file, but we have to stop the library.

Finally, we should pay attention to the development and change the procedure and send out the accident report. It seems that the quantity and quality of dba under his staff still need to be improved. I always rely on myself to die of exhaustion.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.