.::: Script Broadcast Query in MariaDB MySQL to Multple Database in Linux :::.

1. create database sample
create database db_klt_teguh;
create database db_klt_teguh_dmart;
create database db_klt_teguh_archive;

create database db_bgr_tri;
create database db_bgr_tri_dmart;
create database db_bgr_tri_archive;

create database db_lpg_raju;
create database db_lpg_raju_dmart;
create database db_lpg_raju_archive;

2. check all database
 

3. check specify database

SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('performance_schema', 'informance_schema', 'information_schema', 'sys', 'mysql') AND schema_name NOT LIKE '%dmart' AND schema_name NOT LIKE '%archive' AND schema_name LIKE 'db_%\_%' ORDER BY schema_name DESC;

SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('performance_schema', 'informance_schema', 'information_schema', 'sys', 'mysql')
AND schema_name NOT LIKE '%dmart' AND schema_name NOT LIKE '%archive' AND schema_name LIKE 'db_%\_%' ORDER BY schema_name DESC;
 

4. run script if table empty
[root@teguhth broadcast]# sh script_broadcast_maria.sh insert_pembelian.sql
 

5. log script if table not empty
[root@teguhth broadcast]# sh script_broadcast_maria.sh insert_pembelian.sql
 

6. for lab delete table and run again script_broadcast_maria.sh
delete from db_klt_teguh;
delete from db_bgr_tri;
delete from db_lpg_raju;

7. script broadcast

[root@teguhth broadcast]# cat script_broadcast_maria.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

infotgl=$(date)
dbversion=$(mysql -uadmin -pxxxx -sNe "select @@version as VersionDB");

echo ".::: Broardcast Query to All database MariaDB / MySQL in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");

# script_broadcast_maria.sh file_name
echo "sintax >>> sh script_broadcast_maria.sh <sql_file>"
SQL_FILE=${1}
echo "sh script_broadcast_maria.sh '$SQL_FILE'"
echo ""
echo "Script will be apply to Multiple Database"
cat $SQL_FILE

#dbname=$(mysql -uadmin -pxxxx -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");

#dbname=$(mysql -uadmin -pxxxx -sNe "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('performance_schema', 'informance_schema', 'information_schema', 'sys', 'mysql')   AND schema_name NOT LIKE '%dmart' AND schema_name NOT LIKE '%archive'   AND schema_name LIKE 'db\_%\_%' ESCAPE '\\' ORDER BY schema_name DESC;");

dbname=$(mysql -uadmin -pxxxx -sNe "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('performance_schema', 'informance_schema', 'information_schema', 'sys', 'mysql') AND schema_name NOT LIKE '%dmart' AND schema_name NOT LIKE '%archive' AND schema_name LIKE 'db_%\_%' ORDER BY schema_name DESC;")

for listdb in $dbname
do
 datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
 echo ""
    echo "$datex --->>> Broadcast to database '$listdb' using $SQL_FILE Begin"
    mysql -uadmin -pxxxx "$listdb" < "$SQL_FILE"
        datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
    echo "$datey --->>> Finish Broadcast to database '$listdb' using $SQL_FILE"

done;
 echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth broadcast]#