Several methods of executing sql statement in Shell script to operate MySQL Database
The experiments are as follows:
[root@idb1 ~] # more / etc/issue
CentOS release 6.5 (Final)
Kernel\ r on an\ m
Mysql > show variables like 'version'
+-+ +
| | Variable_name | Value |
+-+ +
| | version | 5.6.27-log |
+-+ +
1 row in set (0.00 sec)
1. Embed the SQL statement directly into the shell script file
[root@idb1 ~] # cat shell_example01.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-umdba-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), (2)), (3) (3)
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@idb1 ~] # chmod + x shell_example01.sh
[root@idb1 ~] # sh shell_example01.sh
Warning: Using a password on the command line interface can be insecure.
Logging to file'/ tmp/temp.log'
+-+ +
| | id | val |
+-+ +
| | 1 | jack |
| | 2 | robin |
| | 3 | mark |
+-+ +
Outfile disabled.
2. The command line calls a separate SQL file as an example:
[root@idb1 ~] # cat 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), (2)), (3) (3)
Select * from tb_tmp
Notee
Quit
[root@idb1] # mysql-umdba-p-e "source / root/temp.sql"
Enter password:
Logging to file'/ tmp/temp.log'
+-+ +
| | id | val |
+-+ +
| | 1 | duansf |
| | 2 | liuyb |
| | 3 | jack |
+-+ +
Outfile disabled.
# use pipe characters to call SQL files and output logs
[root@idb1] # mysql-umdba-p
