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/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:
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 --------------------------------------
This article permanently updates the link address: