Several ways to execute MySQL in shell scripts

Shell executed in script MySQL several ways of (turn)

For automated operations, such as backup recovery, DBAs often need to encapsulate SQL statements into shell scripts. This paper describes several methods of SQL statements for shell scripts in MySQL database under Linux environment, for your reference. For the output of the script to beautify the results, need to further refine and adjust. The following are specific examples and their methods.

1 , the SQL statement is embedded directly into the Shell in the script file

Copy the Code code as follows:

--Demo environment
[Email protected] ~]# more/etc/issue
CentOS release 5.9 (Final)
Kernel \ r on an \m

[Email protected] [(None)]> show variables like ' version ';
+---------------+------------+
| variable_name | Value |
+---------------+------------+
| Version | 5.6.12-log |
+---------------+------------+

[Email protected] ~]# 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

[Email protected] ~]#./shell_call_sql1.sh
Logging to file '/tmp/temp.log '
+------+-------+
| ID | Val |
+------+-------+
| 1 | Jack |
| 2 | Robin |
| 3 | Mark |
+------+-------+
Outfile disabled.

2 , the command line invokes a separate SQL file

Copy the Code code as follows:

[Email protected] ~]# 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

[Email protected] ~]# 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 , using pipe breaks to invoke SQL file

Copy the Code code as follows:

[Email protected] ~]# mysql-uroot-p123456 </root/temp.sql
Logging to file '/tmp/temp.log '
ID val
1 Jack
2 Robin
3 mark
Outfile disabled.

#使用管道符调用SQL文件以及输出日志
[Email protected] ~]# mysql-uroot-p123456 </root/temp.sql>/tmp/temp.log
[Email protected] ~]# More/tmp/temp.log
Logging to file '/tmp/temp.log '
ID val
1 Jack
2 Robin
3 mark
Outfile disabled.

4 , Shell in the script MySQL The prompt is lowered with SQL

Copy the Code code as follows:

[Email protected] ~]# more shell_call_sql2.sh
#!/bin/bash
mysql-uroot-p123456 <<eof
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
[Email protected] ~]#./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 , Shell variable input and output in scripts

Copy the Code code as follows:

[Email protected] ~]# 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
[Email protected] ~]#./shell_call_sql3.sh
Warning:using a password on the command line interface can beinsecure.
Current Count Is:3

[[email protected] ~]# echo "SELECT COUNT (*) from tempdb.tb_tmp" |mysql-uroot-p123456-s
3

[Email protected] ~]# 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

[Email protected] ~]#./shell_call_sql4.sh
Current Count Is:1

Transferred from: http://blog.sina.com.cn/s/blog_4a80a5730101mbeg.html

The following is a non-interactive way to manipulate the database through the shell command line:

MYSQL-HHOSTNAME-PPORT-UUSERNAME-PPASSWORD-E mysql-related SQL statements without running MySQL at the prompt of MySQL, which means you can manipulate MySQL in the shell.

#!/bin/bash

Hostname= "192.168.111.84" #数据库信息

Port= "3306"

Username= "Root"

Password= ""

Dbname= "Test_db_test" #数据库名称

Tablename= "Test_table_test" #数据库中表的名称

#创建数据库

Create_db_sql= "CREATE database IF not EXISTS ${dbname}"

Mysql-h${hostname}-p${port}-u${username}-p${password}-E "${create_db_sql}"

#创建表

Create_table_sql= "CREATE table IF not EXISTS ${tablename} (Namevarchar (), id int (one) default 0)"

Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${create_table_sql}"

#插入数据

Insert_sql= "INSERT INTO ${tablename} values (' Billchen ', 2)"

Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${insert_sql}"

#查询

Select_sql= "SELECT * FROM ${tablename}"

Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${select_sql}"

#更新数据

update_sql= "Update ${tablename} set id=3"

Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${update_sql}"

Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${select_sql}"

#删除数据

Delete_sql= "Delete from ${tablename}"

Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${delete_sql}"

Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${select_sql}"

(EXT) http://www.cnblogs.com/zhuawang/p/5179838.html

Several ways to execute MySQL in a shell script (go)