A failure after upgrading MySQL

I. Background of the problem

　　To meet the requirements of the superior, a production database needs to implement a backup; Just the vulnerability Scan report, MySQL version needs to upgrade to 5.7.20, so prepare for a rainy day, first write the script. The script is fully available under the old MySQL version (MySQL is 5.7.18 before the upgrade). After the upgrade, in the spirit of a rigorous attitude of technical personnel, absolutely to sh-x look at the script in the new environment there is no abnormal ~ ~ ~

second, the phenomenon (there are two main) 　　 1.Mysqldump can only back up tables cannot back up libraries (Z-Libraries and S-Libraries), just test with the S library! (This emphasizes the following is useful, the audience slowly appreciate). Start very puzzled, two library not to 100m,mysqldump long no results, total can not only 1-2 days, production database on the data volume of 2, at the same time, the tester feedback, the project page has been in a circle (just upgraded the database, tested, page open no problem) 　　

Third, troubleshooting and resolution

1, the head said, may be because after the upgrade Tomcat did not restart, resulting in the application of the problem (PS: the database and application on two different machines), so restart Tomcat ~ ~ Watch the real-time log: Tail-f catalina.out

(1) Figure I

(2) Figure II

In conclusion, it can be found that quite a lot of preparing ... At that time actually did not see anything strange (after all, too young, embarrassed)

2, after the developer to get the database account password, said s library of a table, that is, the log table: Tb_ict_sys_log was locked! This is why the mysqldump test backup S library is not backed up, if the test backup Z-Library, the results are estimated to be possible, because the table on the Z-Library is not locked

Login database Check thread running situation, finally have a look!!!

(1) Figure one (mysqldump the thread of the S library)

(2) Figure II (page landing is not stuck on the error thread)

3, Solve: root According to the process number, kill the database lock process

After that, the database is back to normal, the page open no problem

Iv. Conclusion

1, the browser Landing project page, regardless of whether the landing is successful, there is a 3rd 1 (2) Figure two Insert S library log table operation

2, mysqldump Backup database will have a lock table problem, may be in conflict with the insert operation ( Developer: There is no other place in the business to cause the lock table Operation ), So the problem is probably caused by debugging the script. In order to not affect the business, it is recommended to back up at night ( schedule changed to 0 o'clock in the morning backup )

3. Determine whether the Tomcat database connection pool configuration is accurate (.../web-inf/classes/jdbc.properties), although it has nothing to do with this troubleshooting process

This log is Tomcat's earliest information (I don't know if this is a hidden problem)

A failure after upgrading MySQL