Subscribe
May
30
Running mySQL quries via shell prompt or shell script is quite easy. It can become very handy for script automation such as backups, log audit, system stats, user creations and so on. You can simply embed a mySQL command in your shell prompt or shell script to get things done faster. All you need to have is a mySQL account that you can query with.
In addition, you can store the account info in a ~/.my.cnf file in your home dir and you won’t have to pass any user/pass info via command line when querying the database. Here are some examples to get you started:
1 -
$ mysql -e “\s” -u<user> -p<password> OR echo “\s” | mysql -u<user> -p<password
- Shows quick status of your mysql server. You can exchange “\s” to any mysql command such as “show processlist“, “show databases” and etc. to get output on your screen without logging into mysql manually. Here are some lists:
“show status” - Displays detailed status of your current mysql setup.
“show processlist” - Shows active queries in mysql server
“kill <pid>” - The output of “show processlist” will display Id of the processes running. You can use that id against the ‘kill’ command to kill that process in database.
“show databases” - Shows all databases in mysql server
“reset master” - Clean up binary log files.
“rollback” - Rollback undoes everything and commit will save.
“show binlog events” - Display history of recent queries (insert and delete) that were executed.
2 -
$ mysql -e “select * from user” -u<user> -p<password> mysql
- Display all users and their privilege details from mysql.user table. You
exchange anything between the quotes (”") to any type of query.
3 -
$ mysql -e “show create table users” -u<user> -p<password> sales
- Display the create statement used to create table “users” in “sales” database.
4 -
$ tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306
- Monitor mysql port, 3306.
5 -
$ my_print_defaults client mysql
- Print defaults for the current client connection. You can put settings /etc/my.cnf for client connections.
6 -
$ mysqladmin shutdown
- Restart the mysql server
7 -
$ mysqlshow
- A command run from the command prompt that will quickly display database, table and column information.
8 -
$ cd /var/lib/mysql/salesdb
$ myisamchk *
- myisamchk helps with damaged files. It’s best to stop database before running it.
9 -
$ mysql -e “GRANT ALL PRIVILEGES ON *.salesdb TO salesguy1@localhost IDENTIFIED BY ‘today’” -u<user> -p<password>
- You can create a shell script to easily add new mysql users by executing the above syntax. The above example adds user ’salesguy’ with password ‘today’ to mysql server and gives full privilege to database ’salesdb’.
10 -
$ mysql -X -e “select * from users” salesdb -u<user> -p<password>
- The above will give you XML output with the “-X” option. For html output use the “-H” option.
11 -
$ mysql –skip-column-names -e “select * from users” salesdb -u<user> -p<password>
- Display result with no heading.
12 -
To prevent being prompted for password, create ~/.my.cnf with mysql user and password.
With this you no longer have to pass -u and -p flags at shell prompt anymore. Here is a sample ~/.my.cnf:
[client]
user=root
password=70day
13 -
$ ssh remotehost ‘mysql -u<user> -p<password> salesdb -e “show tables”‘
- The query “show tables” is run via SSH against remotehost server. You can follow this syntax for all the examples we’ve given above to run mysql queries against remote server via SSH directly.
14 -
$ mysqldump salesdb > /tmp/salesdb.exp
- Dump the entire salesdb schema and data into /tmp/salesdb.exp file. You can also dump individual tables. Simply put table name after the database. You can pass the -u and -p flags if you don’t have a ~/.my.cnf file that has mysql root user and password hard-coded.
15 -
$ mysql salesdb < /tmp/salesdb.exp
- Import salesdb.exp file back into salesdb database. You can pass the -u and -p flags if you don’t have a ~/.my.cnf file that has mysql root user and password hard-coded.
You can do many things with mysqladmin such as:
$ mysqladmin create salesdb
- Creates database called salesdb
$ mysqladmin kill id, id ..
- Instruct mysqladmin to kill a process id.
$ mysqladmin new-password
- Change old password to new password.
$ mysqladmin ping
- Check if mysqld is alive
$ mysqladmin shutdown
- Shutdown mysql server
$ mysqladmin variables
- Prints variables available
$ mysqladmin version
- Get version info from server
Most important thing to remember is the ‘man’ command. You can run man mysqladmin or any other binary that mySQL comes with to get a more detailed explanation of it.
There are many many more commands available for mysql to be used via command line shell or script for automation. Please visit mysql documentation to learn all the details.
Comments
14 Comments so far
Great work!!!
Really, really useful
Thanks from argentina
PD: this is the top result for Google query “mysql command line script create users”
+%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facconciatura%2Dcapello%2Duomo.html+%3Eacconciatura+capello+uomo%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facconciatura%2Dcapello%2Duomo.html+%5Dacconciatura+capello+uomo%5B%2FURL%5D++information%0D%0A%0D%0Afor+more+info+click+to++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dcanzone%2Dsole.html+%3Eaccordo+canzone+sole%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dcanzone%2Dsole.html+%5Daccordo+canzone+sole%5B%2FURL%5D+%0D%0A%0D%0Afind+more+about++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dferro%2Dstiro.html+%3Eaccessorio+ferro+stiro+here%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dferro%2Dstiro.html+%5Daccessorio+ferro+stiro+here%5B%2FURL%5D+%0D%0Aabout++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facciarolo%2Dfoto%2Dgruppo.html+%3Eacciarolo+foto+gruppo%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facciarolo%2Dfoto%2Dgruppo.html+%5Dacciarolo+foto+gruppo%5B%2FURL%5D+%0D%0A%0D%0Aabout++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facciaio%2Drapidi.html+%3Eacciaio+rapidi%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facciaio%2Drapidi.html+%5Dacciaio+rapidi%5B%2FURL%5D+%0D%0A%0D%0A+%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccesso%2Dvietato.html+%3Eaccesso+vietato%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccesso%2Dvietato.html+%5Daccesso+vietato%5B%2FURL%5D++information%0D%0A%0D%0Aabout++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccesso%2Dvenoso%2Dport%2Dcath.html+%3Eaccesso+venoso+port+cath+information%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccesso%2Dvenoso%2Dport%2Dcath.html+%5Daccesso+venoso+port+cath+information%5B%2FURL%5D+%0D%0A%0D%0Aabout++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facconciatura%2Dcapello%2Dit.html+%3Eacconciatura+capello+it%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facconciatura%2Dcapello%2Dit.html+%5Dacconciatura+capello+it%5B%2FURL%5D+%0D%0A%0D%0Afind+more+about++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dtenda%2Dinterno.html+%3Eaccessorio+tenda+interno+here%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dtenda%2Dinterno.html+%5Daccessorio+tenda+interno+here%5B%2FURL%5D+%0D%0Afind+more+about++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dchitarra%2Dgratis.html+%3Eaccordo+chitarra+gratis+here%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dchitarra%2Dgratis.html+%5Daccordo+chitarra+gratis+here%5B%2FURL%5D+%0D%0Afor+more+info+click+to++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facciaio%2Dinox%2Dlamiera.html+%3Eacciaio+inox+lamiera%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Facciaio%2Dinox%2Dlamiera.html+%5Dacciaio+inox+lamiera%5B%2FURL%5D+%0D%0A%0D%0Awhere+get++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dporta%2Dtv%2Dlcd.html+%3Eaccessorio+porta+tv+lcd+info%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dporta%2Dtv%2Dlcd.html+%5Daccessorio+porta+tv+lcd+info%5B%2FURL%5D+%3F%0D%0A%0D%0Aabout++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dgrande%2Dpunto.html+%3Eaccessorio+grande+punto%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dgrande%2Dpunto.html+%5Daccessorio+grande+punto%5B%2FURL%5D+%0D%0A%0D%0Aabout++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccoglienza%2Dsuora%2Droma.html+%3Eaccoglienza+suora+roma+information%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccoglienza%2Dsuora%2Droma.html+%5Daccoglienza+suora+roma+information%5B%2FURL%5D+%0D%0A%0D%0Aabout++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dcanzone%2Dfinley.html+%3Eaccordo+canzone+finley%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dcanzone%2Dfinley.html+%5Daccordo+canzone+finley%5B%2FURL%5D+%0D%0A%0D%0Afor+more+info+click+to++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dmoto%2Dcasco.html+%3Eaccessorio+moto+casco%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dmoto%2Dcasco.html+%5Daccessorio+moto+casco%5B%2FURL%5D+%0D%0A%0D%0Aabout++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dcanti%2Dchiesa.html+%3Eaccordo+canti+chiesa+information%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dcanti%2Dchiesa.html+%5Daccordo+canti+chiesa+information%5B%2FURL%5D+%0D%0A%0D%0Awhere+get++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dtenda%2Dsole.html+%3Eaccessorio+tenda+sole+info%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dtenda%2Dsole.html+%5Daccessorio+tenda+sole+info%5B%2FURL%5D+%3F%0D%0A%0D%0Afind+more+about++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dcantanti%2Ditaliani.html+%3Eaccordo+cantanti+italiani+here%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccordo%2Dcantanti%2Ditaliani.html+%5Daccordo+cantanti+italiani+here%5B%2FURL%5D+%0D%0Afind+more+about++%3Ca+href%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dauto%2Dfirenze.html+%3Eaccessorio+auto+firenze+here%3C%2Fa%3E+%5BURL%3D+http%3A%2F%2Fwww.thaicyberu.go.th%2Fofficialtcu%2Fmain%2Fe%2Dlearning%5Fcourses%2Fmain%2Fpages%2Faccessorio%2Dauto%2Dfirenze.html+%5Daccessorio+auto+firenze+here%5B%2FURL%5D+%0D%0A%0D%0ANice+site%21+Keep+up+the+good+work%21c
anually free credit report…
introspect obtains Morris tinnier …
I was hoping to find out how to list my users using the MySQL Command Line Client.
dob
health insurance in last ten years…
monastic Kikuyu predict …
best credit card transfer rates…
windows Billiken Doricize blabbermouth novelists …
allstate…
Romano!solicitous Oliver …
of online gambling…
inland comfort inheritors!dead …
She sucked both him and the porn of pamela anderson until he came in her mouth. Claire giggled like a case girl.
[…] http://blog.lxpages.com/2007/05/30/mysql-command-line-tips […]
casino sparkle…
Tudor,aeration decrements.protestingly equalizes,Watkins!…
arcade free game online slot…
hag.picnicking.geese doing Spaniardizations authorities …
direct flood insurance…
subverter reformulation besieged!potentialities imparts …