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

Background: due to the adjustment of the network of the company's computer room, the server IP of a number of mysql databases needs to be adjusted. A new architecture (keepalive+lvs) has been built in the new environment, and developers are required to cooperate to modify the program configuration. There are 2 businesses, 9 servers and more than 50 instances.

1. Grab the connection script-free from the heavy repetitive work

In order to make the switching process more efficient and free your hands, write a simple shell script that grabs the connection regularly and stores it to the core database, a simple example:

#! / bin/bash StatFile= "/ var/log/status/processlist.txt" # get IP information IP= `/ sbin/ifconfig | egrep-A1 "eth [0-4]" | egrep "inet" | egrep-v "192.168 |: 10." | awk-F' [ :] +'{print $4}'| sed-n "1p" `if [[$IP='']] then IP= `/ sbin/ifconfig | egrep-A1 "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 / 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# the show processlist information is stored in the file here, or you can loop directly # save the information in the file to the core database Ignore table structure cat $StatFile | while read ip port username host do / bin/mysql-h database IP-u username-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.

The ① crawl connection script is added to the scheduled task and is executed every minute

At this time, ② can look up the connection information from the core database and judge the connection status at the latest time according to the update_time field in the table.

③ can use SQL statements to query, but it is still tedious, and many servers still need to execute sql repeatedly, which is boring.

④ therefore uses the recently learned python to build a simple web page with django to display. The specific structure is very simple, that is, query the data from the database and send it to the template for rendering.

The last minute data is displayed by default, and a search box is added, which can be filtered according to IP and port number.

In this way, ⑤ is clearer when communicating with developers, and it is no longer a ● pages? black-and-white ●, although it is very simple

3. Abnormal connection encountered

The whole work went smoothly, but at the end of the switch, the page saw that the write library of a business had been connected by a web, and asked the developer if there was any configuration that was not synchronized.

He searched for a long time and told me that all the places used had been modified and could not be found.

The connection caught shows that it is his server, so we need to help him locate the problem point.

① enabled mysql general-log to crawl connection details (because the main database is no longer writable and the information cannot be found in binlog). According to the page information, the data was crawled for five minutes.

Find the sql that is executed and provide it to the developer for confirmation

② was developed to know the specific program, but check that the configuration has been modified, and it is suspected that other separately arranged scripts do not use vip, so grab the network information and see what ip connection database is used

Execute the command on the database server:

Netstat-lna | grep 'Application Server IP'

You can see what the database IP used by the program is, and it is found that it is indeed VIP. At this time, we can no longer solve the problem and need to give it to the developer to look it up carefully.

Finally, ③ told me that there was a process that had been implemented since yesterday, and the new configuration did not take effect.

④ records this problem, which may be encountered in the future. After modifying the configuration, it is best to check all the program processes.

4. Thinking

After making database changes in DBA, developers need to make changes every time, which should have architectural problems, and it is also a nuisance that developers have to wait until midnight every time.

How to make changes in the schema to make the database configuration transparent and basically insensitive to the application should be the next thing to consider. In the dead of night, good night

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.