MySQL Database Migration Work----connection fetching, displaying and abnormal connection

Background: As the company's computer room network adjustment, need to adjust a batch of MySQL database server IP, in the new environment has been set up a new architecture (KEEPALIVE+LVS), and need to develop engineers to modify the program configuration, a total of 2 business, 9 servers, more than 50 instances.

1. Crawl Connection Scripts---free from heavy repetitive work

In order to make the switching process more efficient and free your own hands, a simple shell script was written, timed to fetch the connection and stored to the core database, simple example:

#!/bin/bash                                                                                                        statfile= "/var/log/status/processlist.txt" # Get IP information         ip= '/sbin/ifconfig | egrep -a 1   "eth[0-4] "  | egrep  "inet "  | egrep -v  "192.168|:10." |  awk -f ' [ :]+ '   ' {print $4} '  | sed -n  ' 1p ' if [[  $IP  =  '  ]]  &Nbsp;    then           ip= '/sbin/ ifconfig | egrep -a 1  "eth[0-4] "  | egrep  "inet "  |  egrep   "192.168|:10." |  awk -f ' [ :]+ '   ' {print $4} '  | sed -n  ' 1p '         fi# port.txt  Storage Port Number Cat /var/log/port.txt | while read  port                                                                                       do    &nBsp;             /bin/mysql -h Database IP  -uroot -p ' password '  -P$port information_schema -Bse  ' select  ' $IP ', ' $port ', user, Substring_index (Host, ': ', 1)  as host from  processlist where user not  in  (' root ', ' System user ')  group by user,substring_index (host, ': ', 1); "  >  $StatFile #  here to save show processlist information to a file, or to directly loop execution #  the information in the file into the core database, ignoring the table structure                 cat  $StatFile  |  while read ip  port username host                do                            /bin/ Mysql  -h database ip -u user name   -p  password '  -p port    library name  -Bse  ' Insert into mysql_db_proce (Db_ip,port, Username,app_host)  values  (' $IP ', ' $port ', ' $username ', ' $host '); "                done        done

2. Information display

-----make my results look better.

① Crawl connection scripts are added to the scheduled task, once per minute

② at this point you can view the connection information from the core library and determine the current connection status based on the Update_time field in the table

③ can use SQL statements to query, but still cumbersome, many servers still need to repeatedly execute SQL, annoying

④ therefore used the recently learned Python to build a simple Web page with Django, the structure is very simple, is to query the data from the database, upload to the template to render it OK

650) this.width=650; "title=" image "style=" border-right-width:0px;background-image:none;border-bottom-width:0px; padding-top:0px;padding-left:0px;padding-right:0px;border-top-width:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/96/d3/wkiom1klt5ttynhraabho-zu1pm763.png "width=" 802 "height=" 107 "/>

The most recent minute data is displayed by default, and a search box is added to filter by IP and port number.

⑤ so in communication with the development of the time, more clear, no longer black and white page (ˇ?ˇ), although very simple

3. Connection anomalies encountered

The whole work went well, but at the end of the page, there was a web connection to the write library that had a business in it, and asked if there was a configuration that was not synchronized.

He searched for a long time and told me that all the places I had used had been revised and not found.

Caught connection display is his server, so need to help him to locate the problem point

① Open MySQL General-log crawl connection specific information (because the main library is not writable at this time, binlog information), according to the page information, crawled five minutes of data,

Find specific SQL to execute, provide development confirmation

② developed to know the specific program, but the check configuration has been modified, suspect that the other individually arranged scripts are not using the VIP, so crawl the network information to see what IP connected database is used

To execute a command on the database server:

Netstat–lna | grep ' Application IP '

Can see the program uses the database IP is what, found that it is a VIP, at this time we can not solve the problem, need to hand over to the development of carefully find

③ Finally, after he told me that there was a process that had been executing since yesterday, the new configuration did not take effect

④ record This problem, you may encounter later, after modifying the configuration, it is best to check the program process

4. Thinking

When a DBA makes a database change, it needs to be developed and modified every time, there should be architectural problems, and each time it needs to be developed until midnight, and it's a nasty thing.

How to make changes to the architecture, so that the database configuration is transparent to the application, do not feel basic, should be the next thing to consider. Night, goodnight.

This article is from the "Amnesiasun" blog, make sure to keep this source http://amnesiasun.blog.51cto.com/10965283/1929169

MySQL Database Migration Work----connection fetching, displaying and abnormal connection