For automated operations, such as backup and recovery, DBAs often need to encapsulate SQL statements into shell scripts. This article describes several methods of calling SQL statements under shell scripts in mysql database in Linux environment for your reference. For the beautification of the results of script output, further improvement and adjustment are needed. The following are specific examples and methods.
1. Embed SQL statements directly into shell script file
Copy the codeThe code is as follows:
--Demo environment
[root@SZDB ~]# more /etc/issue
CentOS release 5.9 (Final)
Kernel \r on an \m
root@localhost[(none)]> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.6.12-log |
+---------------+------------+
[root@SZDB ~]# more shell_call_sql1.sh
#!/bin/bash
# Define log
TIMESTAMP=`date +%Y%m%d%H%M%S`
LOG=call_sql_${TIMESTAMP}.log
echo "Start execute sql statement at `date`." >>${LOG}
# execute sql stat
mysql -uroot -p123456 -e "
tee /tmp/
drop database if exists tempdb;
create database tempdb;
use tempdb
create 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
quit"
echo -e "\n">>${LOG}
echo "below is output result.">>${LOG}
cat /tmp/>>${LOG}
echo "script executed successful.">>${LOG}
exit;
[root@SZDB ~]# ./shell_call_sql1.sh
Logging to file '/tmp/'
+------+-------+
| id | val |
+------+-------+
| 1 | jack |
| 2 | robin |
| 3 | mark |
+------+-------+
Outfile disabled.
2. The command line calls a separate SQL file
Copy the codeThe code is as follows:
[root@SZDB ~]# more
tee /tmp/
drop database if exists tempdb;
create database tempdb;
use tempdb
create 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/"
Logging to file '/tmp/'
+------+-------+
| id | val |
+------+-------+
| 1 | jack |
| 2 | robin |
| 3 | mark |
+------+-------+
Outfile disabled.
3. Use pipe characters to call SQL files
Copy the codeThe code is as follows:
[root@SZDB ~]# mysql -uroot -p123456 </root/
Logging to file '/tmp/'
id val
1 jack
2 robin
3 mark
Outfile disabled.
#Use pipe characters to call SQL files and output logs
[root@SZDB ~]# mysql -uroot -p123456 </root/ >/tmp/
[root@SZDB ~]# more /tmp/
Logging to file '/tmp/'
id val
1 jack
2 robin
3 mark
Outfile disabled.
4. Call SQL at the MySQL prompt in the shell script
Copy the codeThe code is as follows:
[root@SZDB ~]# more shell_call_sql2.sh
#!/bin/bash
mysql -uroot -p123456 <<EOF
source /root/;
select current_date();
delete from tempdb.tb_tmp where id=3;
select * from tempdb.tb_tmp where id=2;
EOF
exit;
[root@SZDB ~]# ./shell_call_sql2.sh
Logging to file '/tmp/'
id val
1 jack
2 robin
3 mark
Outfile disabled.
current_date()
2014-10-14
id val
2 robin
5. Variable input and output in shell scripts
Copy the codeThe code is as follows:
[root@SZDB ~]# more shell_call_sql3.sh
#!/bin/bash
cmd="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 -s
3
[root@SZDB ~]# more shell_call_sql4.sh
#!/bin/bash
id=1
cmd="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, it is only used to throw bricks and attract jade. The output results are not very regular and friendly, and further improvements are needed.