Execute mysql statements in Shell scripts

Source: Internet
Author: User

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/issueCentOS release 5.9 (Final) Kernel \ r on an \ mroot @ localhost [(none)]> show variables like 'version '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + [root @ SZDB ~] # More shell_call_sql1.sh #! /Bin/bash # Define logTIMESTAMP = 'date + % Y % m % d % H % M % s' LOG = call_ SQL _ $ {TIMESTAMP }. logecho "Start execute SQL statement at 'date '. ">>$ {LOG} # execute SQL statmysql-uroot-p123456-e" tee/tmp/temp. logdrop database if exists tempdb; create database tempdb; use tempdbcreate 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_tm P; noteequit "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: http://blog.csdn.net/leshami

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

[root@SZDB ~]# more temp.sql tee /tmp/temp.logdrop database if exists tempdb;create database tempdb;use tempdbcreate 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-p123456 </root/temp. sqlLogging to file '/tmp/temp. log 'id val1 jack2 then 3 markOutfile disabled. # Use the pipeline to call the SQL file and output logs [root @ SZDB ~] # Mysql-uroot-p123456 </root/temp. SQL>/tmp/temp. log [root @ SZDB ~] # More/tmp/temp. logLogging to file '/tmp/temp. log' id val1 jack2 then 3 markOutfile disabled.

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

[root@SZDB ~]# more shell_call_sql2.sh#!/bin/bashmysql -uroot -p123456 <<EOFsource /root/temp.sql;select current_date();delete from tempdb.tb_tmp where id=3;select * from tempdb.tb_tmp where id=2;EOFexit;[root@SZDB ~]# ./shell_call_sql2.shLogging to file '/tmp/temp.log'id      val1       jack2       robin3       markOutfile disabled.current_date()2014-10-14id      val2       robin

5. variable input and output in shell scripts

[Root @ SZDB ~] # More shell_call_sql3.sh #! /Bin/bashcmd = "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-s3 [root @ SZDB ~] # More shell_call_sql4.sh #! /Bin/bashid = 1cmd = "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, we only use it as an example. The output results are not very regular and friendly and need further improvement.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.