Execute mysql statements in Shell scripts

Execute mysql statements in Shell scripts

For automated O & M, such as backup and recovery, DBA often needs to encapsulate SQL statements into shell scripts. This article describes several methods for calling SQL statements in a mysql database in a Linux environment using shell scripts. The results of Script output must be further improved and adjusted. The following are examples and methods.

1. embed SQL statements directly into shell script files

-- Demo Environment [root @ SZDB ~] # More/etc/issueCentOS release 5.9 (Final) Kernel \ r on an \ mroot @ localhost [(none)]> show variables like 'version '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + [root @ SZDB ~] # More shell_call_sql1.sh #! /Bin/bash # Define logTIMESTAMP = 'date + % Y % m % d % H % M % s' LOG = call_ SQL _ $ {TIMESTAMP }. logecho "Start execute SQL statement at 'date '. ">>$ {LOG} # execute SQL statmysql-uroot-p123456-e" tee/tmp/temp. logdrop database if exists tempdb; create database tempdb; use tempdbcreate table if not exists tb_tmp (id smallint, val varchar (20); insert into tb_tmp values (1, 'jack '), (2, 'Robin '), (3, 'mark'); select * from tb_tm P; noteequit "echo-e" \ n ">$ {LOG} echo" below is output result. ">>$ {LOG} cat/tmp/temp. log >>$ {LOG} echo "script executed successful. ">>$ {LOG} exit; [root @ SZDB ~] #. /Shell_call_sql1.sh Logging to file '/tmp/temp. log' + ------ + ------- + | id | val | + ------ + ------- + | 1 | jack | 2 | robin | 3 | mark | + ------ + ------- + Outfile disabled. -- Author: Leshami -- Blog: http://blog.csdn.net/leshami

2. Use a command line to call a separate SQL File

[root@SZDB ~]# more temp.sql tee /tmp/temp.logdrop database if exists tempdb;create database tempdb;use tempdbcreate table if not exists tb_tmp(id smallint,val varchar(20));insert into tb_tmp values (1,'jack'),(2,'robin'),(3,'mark');select * from tb_tmp;notee[root@SZDB ~]# mysql -uroot -p123456 -e "source /root/temp.sql"Logging to file '/tmp/temp.log'+------+-------+| id   | val   |+------+-------+|    1 | jack  ||    2 | robin ||    3 | mark  |+------+-------+Outfile disabled.

3. Use the MPs queue to call the SQL File

[Root @ SZDB ~] # Mysql-uroot-p123456 </root/temp. sqlLogging to file '/tmp/temp. log 'id val1 jack2 then 3 markOutfile disabled. # Use the pipeline to call the SQL file and output logs [root @ SZDB ~] # Mysql-uroot-p123456 </root/temp. SQL>/tmp/temp. log [root @ SZDB ~] # More/tmp/temp. logLogging to file '/tmp/temp. log' id val1 jack2 then 3 markOutfile disabled.

4. Call the SQL statement at the MySQL prompt in the shell script

[root@SZDB ~]# more shell_call_sql2.sh#!/bin/bashmysql -uroot -p123456 <<EOFsource /root/temp.sql;select current_date();delete from tempdb.tb_tmp where id=3;select * from tempdb.tb_tmp where id=2;EOFexit;[root@SZDB ~]# ./shell_call_sql2.shLogging to file '/tmp/temp.log'id      val1       jack2       robin3       markOutfile disabled.current_date()2014-10-14id      val2       robin

5. variable input and output in shell scripts

[Root @ SZDB ~] # More shell_call_sql3.sh #! /Bin/bashcmd = "select count (*) from tempdb. tb_tmp "cnt = $ (mysql-uroot-p123456-s-e" $ {cmd} ") echo" Current count is :$ {cnt} "exit [root @ SZDB ~] #./Shell_call_sql3.sh Warning: Using a password on the command line interface can be insecure. Current count is: 3 [root @ SZDB ~] # Echo "select count (*) from tempdb. tb_tmp" | mysql-uroot-p123456-s3 [root @ SZDB ~] # More shell_call_sql4.sh #! /Bin/bashid = 1cmd = "select count (*) from tempdb. tb_tmp where id =$ {id} "cnt =$ (mysql-uroot-p123456-s-e" $ {cmd} ") echo" Current count is: $ {cnt} "exit [root @ SZDB ~] #./Shell_call_sql4.sh Current count is: 1 # in the above script demonstration, we only use it as an example. The output results are not very regular and friendly and need further improvement.