Access mysql using shell script

Question

I want to login to mysql console and set the max packet variable and then exit from the mysql console. I have written the following:

mysql -u root -proot
SET GLOBAL max_allowed_packet=509715201
exit

it only login to mysql and doesn't do anything else.

This might help: [How to change max_allowed_packet size](http://stackoverflow.com/questions/8062496/how-to-change-max-allowed-packet-size) — Cyrus, Jan 29 '17 at 09:00

jacob · Answer 1 · 2017-01-29T09:28:32.023

1

#!/bin/bash

mysql << EOF
SET GLOBAL max_allowed_packet=509715201;
quit
EOF

You should use shell's here documents to execute sql statement.

Quote from Advanced Bash-Scripting Guide:

A here document is a special-purpose code block. It uses a form of I/O redirection to feed a command list to an interactive program or a command, such as ftp, cat, or the ex text editor.

edited Jan 29 '17 at 09:28

answered Jan 29 '17 at 09:22

jacob

671
6
15

Still it doesn't work. Can you please elaborate EOF. – Vivek Sadh Jan 29 '17 at 11:44
EOF is just a tag for shell's here documents. Execute the above shell script, what's your output?@Vivek Sadh – jacob Jan 29 '17 at 11:50
I think I should also specify username and password to log in. Right ? I do mysql -u root -proot << EOF and get nothing as output. – Vivek Sadh Jan 29 '17 at 12:06
If everything was okay with your SET command there‘s no output. – Cyrus Jan 29 '17 at 12:21
I checked value of max packet. It didn't set the desired value. Its showing old value. – Vivek Sadh Jan 29 '17 at 12:23
Show output of: `echo 'select @@global.max_allowed_packet;' | mysql -u root -p` – Cyrus Jan 29 '17 at 12:45
@@global.max_allowed_packet 509714432 – Vivek Sadh Jan 29 '17 at 12:47
Everything works fine. Use a number that is a multiple of 1024: 509715456 – Cyrus Jan 29 '17 at 12:50
1

Thanks. Multiple of 1024 works. See my answer. I tried using a different command. – Vivek Sadh Jan 29 '17 at 12:54

score 0 · Answer 2 · answered Jan 29 '17 at 12:53

0

Thanks. I figured it out:

mysql -u root -proot -Bse "SET GLOBAL max_allowed_packet=509715456"

answered Jan 29 '17 at 12:53

Vivek Sadh

4,230
3
32
49

Access mysql using shell script

2 Answers2