MySQL-server_id怎么设置

建议配置成主机IP的10进制格式作为MySQL的server_id，这样就可以在MySQL内部get到所在主机的IP了
MySQL的两个函数：inet_aton() 和 inet_ntoa()

[root@localhost (none)]> SELECT inet_aton('192.168.0.2') as ip10进制;
+------------+
| ip10进制   |
+------------+
| 3232235522 |   /*这个数字作为server_id*/
+------------+

[root@localhost (none)]> SELECT inet_ntoa(@@server_id) as 真实ip;
+-------------+
| 真实ip      |
+-------------+
| 192.168.0.2 |
+-------------+

举例：

#统计所有MySQL机器上的数据量大小，单台机器可以这么写

[root@wp-centos /root]# `which mysql` -udev_root -p123456 -h10.60.96.170 -e \
"select 
    inet_ntoa(@@server_id) ip,
    @@hostname hostname,
    version() version,
    table_schema db_name,
    TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ) db_size_MB 
FRom 
    information_schema.tables  
where 
    table_schema not in ('performance_schema','information_schema','mysql','sys') 
group by 
    table_schema;"

+-------------+-----------+---------+------------+------------+
| ip          | hostname  | version | db_name    | db_size_MB |
+-------------+-----------+---------+------------+------------+
| 192.168.0.2 | wp-centos | 8.0.20  | atguigudb1 |       0.06 |
| 192.168.0.2 | wp-centos | 8.0.20  | lagou      |       0.06 |
| 192.168.0.2 | wp-centos | 8.0.20  | world      |       0.04 |
| 192.168.0.2 | wp-centos | 8.0.20  | haha       |      43.57 |
+-------------+-----------+---------+------------+------------+

#上百台机器可以这么写：
#!/bin/bash
while read line
do
  `which mysql` -uselect_admin -p123456 -h$line -e "select inet_ntoa(@@server_id) ip,@@hostname hostname,version() version,table_schema db_name,TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ) db_size_MB FRom information_schema.tables  where table_schema not in ('performance_schema','information_schema','mysql','sys') group by table_schema;"
done < /root/ip_list.txt
#这些机器上必须有相同的账号密码
#这样可以很清晰的看到每台IP上的MySQL对应的数据大小，该清理就清理，该加容量就加容量

shell可以这么计算IP的10进制

ip_addr=`ifconfig |grep 'broadcast' | awk '{print $2}'`
a=`echo ${ip_addr} | cut -d '.' -f1`
b=`echo ${ip_addr} | cut -d '.' -f2`
c=`echo ${ip_addr} | cut -d '.' -f3`
d=`echo ${ip_addr} | cut -d '.' -f4`
ip_addr_num=`expr $a \* 256 \* 256 \* 256 + $b \* 256 \* 256 + $c \* 256 + $d`
echo ${ip_addr_num}