Bash & Mysql Transactions Example
Post date: Aug 15, 2015 2:23:13 AM
Here is a simple example, no loops just some straight statements outputted via echo and piped all at "once" through a single mysql
Code - Simple
#!/bin/bash
(
echo "BEGIN;"
echo "USE database;"
echo "INSERT INTO table (column1,column2) VALUES ('1','bob');"
echo "INSERT INTO table (column1,column2) VALUES ('2','tom');"
echo "INSERT INTO table (column1,column2) VALUES ('3','bill');"
echo "DELETE FROM table WHERE column2='bob' LIMIT 2;"
echo "COMMIT;"
) | mysql
All sql statements are loaded VIA a temporary files
File - loadme.sql
BEGIN;
USE database;
INSERT INTO table (column1,column2) VALUES ('1','bob');
INSERT INTO table (column1,column2) VALUES ('2','tom');
INSERT INTO table (column1,column2) VALUES ('3','bill');
DELETE FROM table WHERE column2='bob' LIMIT 2;
COMMIT;
Code
#!/bin/bash
cat loadme.sql | mysql
Same as above, but with out the translation statements in the sql
File - loadme.sql
INSERT INTO table (column1,column2) VALUES ('1','bob');
INSERT INTO table (column1,column2) VALUES ('2','tom');
INSERT INTO table (column1,column2) VALUES ('3','bill');
DELETE FROM table WHERE column2='bob' LIMIT 2;
Code
#!/bin/bash
(
echo "BEGIN"
cat loadme.sql
echo "COMMIT"
) | mysql
Load some columns from a CSV file
File - loadme.csv
id,first_name,last_name,email,country,ip_address
1,Michelle,George,mgeorge0@ebay.com,China,123.202.75.179
2,Sara,Evans,sevans1@hc360.com,Finland,134.70.196.70
3,Lawrence,Chavez,lchavez2@about.com,China,18.196.142.206
4,Roy,Carpenter,rcarpenter3@walmart.com,Albania,82.181.123.213
5,Shirley,Green,sgreen4@ucoz.ru,Libya,64.253.188.146
6,Adam,Wood,awood5@hhs.gov,Russia,221.26.34.134
7,Wayne,Stephens,wstephens6@nature.com,Russia,56.65.157.158
8,Theresa,Little,tlittle7@dmoz.org,Afghanistan,69.137.112.37
9,Angela,Sanchez,asanchez8@mayoclinic.com,Indonesia,105.61.90.239
10,Michael,Watson,mwatson9@nih.gov,China,252.233.46.123
11,Frank,Gonzalez,fgonzaleza@rambler.ru,Indonesia,93.237.180.19
12,Juan,Allen,jallenb@lycos.com,United States,218.237.224.77
13,Jack,Hall,jhallc@buzzfeed.com,Russia,4.72.91.28
14,Julie,Berry,jberryd@businesswire.com,China,217.110.87.152
15,Joshua,Simpson,jsimpsone@tinyurl.com,China,12.56.150.26
16,Mary,Hudson,mhudsonf@blogtalkradio.com,Tanzania,134.103.90.216
17,Johnny,Cunningham,jcunninghamg@ustream.tv,Malawi,240.198.146.11
18,Keith,Reynolds,kreynoldsh@huffingtonpost.com,Brazil,132.158.192.170
19,Tina,Bryant,tbryanti@so-net.ne.jp,Ukraine,241.208.105.145
20,David,Lawson,dlawsonj@vkontakte.ru,Finland,168.173.240.164
21,Mark,Nelson,mnelsonk@slate.com,China,184.186.226.254
22,Paul,Welch,pwelchl@businesswire.com,Sweden,13.152.202.44
23,Joyce,Jenkins,jjenkinsm@geocities.jp,Indonesia,8.103.77.179
24,Ernest,Morrison,emorrisonn@sakura.ne.jp,China,11.58.115.47
25,Brian,Bryant,bbryanto@imageshack.us,Poland,198.246.131.204
26,Carl,Fox,cfoxp@upenn.edu,Indonesia,1.89.229.49
27,Doris,Murray,dmurrayq@taobao.com,China,239.230.60.255
28,Doris,Bowman,dbowmanr@jiathis.com,Kazakhstan,71.87.138.11
29,Alan,Young,ayoungs@google.co.uk,Portugal,32.163.140.204
30,Evelyn,Montgomery,emontgomeryt@163.com,France,144.2.32.81
31,Alice,Daniels,adanielsu@quantcast.com,Ireland,247.130.69.39
32,Robert,Marshall,rmarshallv@fotki.com,Canada,116.30.129.9
33,Lois,Green,lgreenw@drupal.org,Indonesia,42.74.77.233
34,Arthur,Day,adayx@ucla.edu,North Korea,89.154.95.21
35,Patricia,Cox,pcoxy@ustream.tv,Turkmenistan,105.242.56.235
36,Antonio,Rice,aricez@jigsy.com,France,199.55.140.129
37,Billy,Simpson,bsimpson10@opensource.org,Mexico,209.115.2.51
38,Dorothy,Welch,dwelch11@list-manage.com,Ethiopia,198.175.147.200
39,Paul,Daniels,pdaniels12@ca.gov,Indonesia,227.236.178.238
40,Larry,Peters,lpeters13@vimeo.com,Jordan,226.138.52.216
41,Brandon,Williams,bwilliams14@skype.com,Russia,105.27.15.197
42,Elizabeth,Day,eday15@weebly.com,China,229.25.28.126
43,Ann,Henry,ahenry16@reference.com,China,42.97.38.8
44,Jimmy,Porter,jporter17@icq.com,France,223.26.225.195
45,Victor,Bowman,vbowman18@merriam-webster.com,Philippines,16.246.77.183
46,Brenda,Jackson,bjackson19@blog.com,Indonesia,128.43.154.62
47,Louis,Bryant,lbryant1a@mit.edu,Indonesia,95.22.26.179
48,Janet,Stephens,jstephens1b@usa.gov,Kazakhstan,231.160.86.102
49,Russell,Little,rlittle1c@joomla.org,Sweden,211.97.195.35
50,Keith,Ruiz,kruiz1d@sciencedaily.com,Israel,179.80.105.231
Code
#!/bin/bash
(
echo "BEGIN;"
echo "USE database;"
while IFS=',' read id first_name last_name email country ip_address; do
echo "UPDATE table SET colum2='${email}' WHERE colum1='${id}';"
done < loadme.csv
echo "COMMIT;"
) | mysql
Roll Back if ALL the rows aren't inserted
Code
#!/bin/bash
# using the < stops wc from printing the file name
totalrows=$(wc -l < loadme.csv)
(
echo "BEGIN;"
echo "USE database;"
echo "DELETE FROM table;"
while IFS=',' read id first_name last_name email country ip_address; do
echo "INSERT INTO table (colum1,colum2) VALUES ('${id}','${email}');"
done < loadme.csv
echo "IF ((SELECT COUNT(*) FROM table) = ${totalrows}) THEN"
echo "COMMIT;"
echo "ELSE"
echo "ROLLBACK;"
echo "END IF;"
) | mysql