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 the shell script in the MySQL database in Linux.

For automated O & M, such as backup and recovery, DBA often needs to encapsulate SQL statements into shell scripts. This article describes the shell script in the MySQL database in Linux.

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/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/temp. log
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/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:

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

[Root @ SZDB ~] # More temp. SQL
Tee/tmp/temp. log
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/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-p123456Logging to file '/tmp/temp. Log'
Id val
1 jack
2 robin
3 mark
Outfile disabled.

# Using MPs queues to call SQL files and output logs
[Root @ SZDB ~] # Mysql-uroot-p123456/Tmp/temp. log
[Root @ SZDB ~] # More/tmp/temp. log
Logging to file '/tmp/temp. Log'
Id val
1 jack
2 robin
3 mark
Outfile disabled.

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

[Root @ SZDB ~] # More shell_call_sql2.sh
#! /Bin/bash
Mysql-uroot-p123456 < Source/root/temp. SQL;
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/temp. Log'
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

[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 as an example. The output results are not very regular and friendly, and need to be further improved and improved.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------