Restart Mysql before the maximum connections arrive

Question

Restart Mysql before the maximum connections arrive

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I have a MySQL DB running on a Debian machine in GCP, which sometimes reaches the limit of connections, which leaves the application stopped until it is restarted and then the application can be reconnected.

Currently has a maximum number of connections has 1000, my idea was to create a script that would look at the database to get the number of connections and if it exceeded 950 restart the database. the idea is to put it in a Crontab that runs every 5 minutes.

At the moment what I have been up to here and I have not managed to make it work anymore (I'm a blanket :)) The two data connections are for tests, restart when there are more than three connections.

sudo mysql < in.txt > out.txt;

sudo grep -o '[0-9]' <out.txt >connections.txt
#maxconnections=$(cat connections.txt)
maxconnections=$(awk '{ print $1 }' connections.txt)
echo  $maxconnections
sleep 4
if [[$maxconnections -ge "3"]]; then  service mysql restart
else
    echo "por debajo de las conexiones maximas"
    sleep 3
fi ;

The file in.txt has the following command:

show status where 'variable_name' = 'Threads_connected';

The file out has the result of executing the file command in.txt:

Variable_name   Value
Threads_connected       2

The connections file only contains the number of connections, in contract: 2

When executing the script I get the following error:

root@test-mysql:/home/aminiasin/temp# ./script.sh 
2
./script.sh: line 11: [[2: command not found

one or less

root@test-mysql:/home/aminiasin/temp#

Any clue as to why it may be failing?

mysql bash shell

asked by Avc Alberto 12.12.2018 в 15:44

source

2 answers

1

you can do it like this:

Note: I put it only as teaching material not as a possible solution to your problem since it would be best to increase the number of connections temporarily:

SET GLOBAL max_connections = 3000;

or permanently in my.ini and restart the database:

max_user_connections=3000

and this is the requested code:

#!/bin/bash
MAX=5
NUM=$(cat in.txt  | grep "connected" | awk '{print $2}')
if [[ $NUM -ge $MAX ]]; then
 echo "es mayor de $MAX lo que quieras hacer"
fi

answered by 12.12.2018 в 16:37

How to call a method that belongs to another class? JAVA POO Convert Date to UTC in SQL Server 2014

score 2 · Accepted Answer

./script.sh: line 11: [[2: command not found

That error lies mainly in your sentence if

if [[$maxconnections -ge "3"]]; then  service mysql restart

This [[$maxconnections does not make sense for bash. Since, apparently, when the variable $ maxconnections expands as "2", then you have the token [[2 in your script running. But that token has no meaning. What has meaning would be something like

[[ $maxconnections -ge "3" ]]
# |_______________________|______ Nota estos espacios.

Then, if the number 2 were replaced again in $maxconnections , the sentence would be of the form if [[ 2 -ge "3" ]] and that makes sense since if executes commands based on a conditional and that conditional is given when evaluated with the built-in [[ which is a token that uses bash to evaluate. Like any token, it needs a separator that identifies it, in this case it is a space before and after if [[ condicion ]]

Your script should look something like this.

sudo mysql < in.txt > out.txt;

sudo grep -o '[0-9]' <out.txt >connections.txt
maxconnections=$(awk '{ print $1 }' connections.txt)
echo  $maxconnections
if [[ $maxconnections -ge "3" ]]
then
    service mysql restart
else
    echo "por debajo de las conexiones maximas"
fi

I'm not saying that your script does the right thing, I think the @Bryro option is the right one. And referring to your doubt in the script that @Bryro wrote you should put something like that.

if [[ $NUM -ge $MAX ]]
then 
    service mysql restart
fi

But remember not to copy and paste everything that you see immediately, before reviewing the syntax, which was meant by the author of the code.