Action after picking lowest number-CodePudding

I have a proxy server that I use to manage registrations. I would like to add each new user to a server with the lowest count.

So far in my script, I have the following that counts entries for servers 1 and 2 printing the result to file.

rm /var/lib/mysql-files/server1.txt
rm /var/lib/mysql-files/server2.txt
mysql -h "localhost" -u root -p"password" "database" <<EOF
SELECT count(*) INTO OUTFILE "/var/lib/mysql-files/server1.txt" from usr_preferences where value like '1.1.1.1';                                                       
SELECT count(*) INTO OUTFILE "/var/lib/mysql-files/server2.txt" from usr_preferences where value like '2.2.2.2';
EOF

The files generated from the count query contains only numbers.

Server 1 file -

Server 2 file -

I have been adding a list of new subscribers to a file called tempadd.txt. This list contains telephone numbers that are also used as usernames. The script below reads the user file and generates the SQL insert commands for me.

To help automate the process a little further I would like the script to change the server IP address 'value' field depending on what file contains the lowest count from the count action above.

#check if tempadd.txt exists 
if [[ -f "tempadd.txt" ]]; then
file="tempadd.txt"
while IFS= read line
do
echo 'INSERT INTO `database`.`usr_preferences`(`uuid`, `username`, `domain`, `attribute`, `type`, `value`, `last_modified`'') VALUES ('\'$line\'', '\'$line\'', '\''mydomain'\'', '\''fs'\'', 2, '\''1.1.1.1'\'', NOW());' >> addlines.txt
done <"$file"
fi

Say I added 20 users at once I'm not worried about one server being unbalanced as this only performs a single count per execution of the script. The next time I added users it would add to the other server.

I'm not sure how to compare the numbers contained in the count files and action on lowest number.

Any help would be amazing :)

CodePudding user response：

There's no need to write to a file, just assign the output of the queries to variables, then compare the variables.

server1=$(mysql -h "localhost" -u root -p"password" "database" -e "SELECT count(*) FROM usr_preferences where value = '1.1.1.1'")
server2=$(mysql -h "localhost" -u root -p"password" "database" -e "SELECT count(*) FROM usr_preferences where value = '2.2.2.2'")
if (( server1 < server2 ))
then server=server1
else server=server2
fi

CodePudding user response：

Expanding on my previous comment ...

Setup:

create table usr_preferences ( value varchar(30) not null);
insert into usr_preferences values ('1.1.1.1');
insert into usr_preferences values ('2.2.2.2');
insert into usr_preferences values ('2.2.2.2');

Instead of pulling values back to bash and then comparing them we can instead let the database do the comparison for us; in this case a simple order by/limit clause can give us the value (aka ip address) with the lowest count:

select value,count(*)
from   usr_preferences
where  value in ('1.1.1.1','2.2.2.2')
group by value
order by count(*)
limit 1

value       count(*)
1.1.1.1     1

Obtaining just the value (ip address sans count(*)), there are a few options (eg, CTE, derived table/subquery, etc); one derived table/subquery idea:

select dt.value
from
(select value,count(*)
 from   usr_preferences
 where  value in ('1.1.1.1','2.2.2.2')
 group by value
 order by count(*)
 limit 1) dt

value
1.1.1.1

Pulling this into OP's mysql call, adding some flags to strip off the column header, and storing the results in a bash variable:

server=$(mysql -h "localhost" -u root -p"password" "database" -NB -e "SELECT dt.value FROM (SELECT value,count(*) FROM usr_preferences where value in ('1.1.1.1','2.2.2.2') group by value order by count(*) limit 1) dt")

NOTES:

while the proposed SQL was tested via an online DB fiddle, I do not have direct access to an environment to test the actual mysql command line
I'm guessing OP can play with the proposed mysql command line (in particular the flags -B and -N) to insure no extraneous stuff ends up in the bash variable (server), otherwise the mysql output could be piped to a short awk (or sed?) script to strip out just the desired string (1.1.1.1 in this example)