Simple bash script's for managing mysql

1. Monitoring replication health

#!/bin/bash

sql_thread_running=$(mysql -e "show slave status\G" | awk -F":" '/Slave_SQL_Running/ { print $2 }' | tr -d ' ')
io_thread_running=$(mysql -e "show slave status\G" | awk -F":" '/Slave_IO_Running/ { print $2 }' | tr -d ' ')
seconds_late=$(mysql -e "show slave status\G" | awk -F":" '/Seconds_Behind_Master/ { print $2 }' | tr -d ' ')
seconds_late=$(($seconds_late+0))

if [ "$sql_thread_running" = "No" ] || [ "$io_thread_running" = "No" ] || [ $seconds_late -gt 3600 ]||[ "$io_thread_running" = "Connecting" ]; then

mysql -e "show slave status\G"|mail -s "Slave Database Issue" xxx.gmail.com

fi

2.Monitoring mysql status

 #!/bin/bash

 /usr/bin/mysqladmin  ping| grep 'mysqld is alive' > /dev/null 2>&1

 if [ $? != 0 ]

 then

     echo 'Mysql Service is Stop'|mail -s "Slave Mysql service down" xxx@gmail.com 

  else

     echo 'Mysql Service Running'

 fi

3.Backup mysql data

#!/bin/bash

databases=`mysql -e "SHOW DATABASES;" | grep -E "(test1|test2)"`

for db in $databases; do

  mysqldump  --databases $db | gzip > /home/mysql/data/$db`date +%Y%m%d`.sql.gz;

  mysqldump  --no-data --databases $db | gzip > /home/mysql/schema/$db`date +%Y%m%d`.sql.gz;

  done

find /home/mysql/data  -iname "*.gz" -mtime +7 -exec rm {} \;

find /home/mysql/schema  -iname "*.gz" -mtime +7 -exec rm {} \;

echo 'Dump completed Successfully'|mail -s "Dump completed" xxx@gmail.com

This script will hold last 7 days backup.Change it according to your need.

Hope this article helps !

Read More

How to Export and Import files in Mysql

 Import of csv file into mysql(Linux)

LOAD DATA INFILE '/path to csv file/' INTO TABLE 'tbl_name'

  FIELDS TERMINATED BY ',' ENCLOSED BY '"'

  LINES TERMINATED BY '\r\n'

  IGNORE 1 LINES

 (@col1,@col2) SET fileld1=@col1,field2=@col2;

 Import of csv file into mysql(Windows)

LOAD DATA LOCAL INFILE '//path to csv file//'

INTO TABLE `tbl_name`

FIELDS TERMINATED BY ',' ENCLOSED BY '"'

LINES TERMINATED BY '\r\n'

(`field1, `field2`, `field3`, `field4`)

Export of mysql data into csv file(Linux)

SELECT col1,col2

union

SELECT col1,col2  INTO OUTFILE '/path to csv/'

from 'tbl_name'

FIELDS TERMINATED BY ','ENCLOSED BY '"'

LINES TERMINATED BY '\n' 

Export of mysql data into csv file(Windows)

SELECT col1,col2

union

SELECT col1,col2 INTO OUTFILE '\\path to csv\\'

FROM tbl_name

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n';

Find columns names from a table in case of large columns

(helpful while importing large data with column name)

select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))

from INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'tbl_name'

AND TABLE_SCHEMA = 'db_name'

order BY ORDINAL_POSITION

Read More

Store Procedure & Views Backups

How to Backup Store Procedures ?


mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt  mydb > mydb.sql

mysqldump  --compact --no-create-info \
    --where="db='db_name' AND type='PROCEDURE' AND name IN ('')" \
    --databases mysql --tables proc > outputfile.sql

mysql --user=root mydb -e "SHOW CREATE PROCEDURE myprocedure;" > myprocedure.sql

mysql db_name -e "show create procedure proc_name\G;">/directory/

How to Backup Views ?

mysql db_name -e "show create view view_culture\G;">/directory/

Mysqldump  db_name view_name>/directory/

Simple Bash script for taking views dump

#!/bin/bash -e

mysql --skip-column-names --batch -e \

"select table_name from information_schema.views \

 where table_schema = database()" $* |

xargs --max-args 1 mysqldump $*

save it as mysql-dump-views.sh

$ mysql-dump-views.sh -u user -ppassword databasename >dumpfile.sql

That's It.

Read More