MYNITOR.COM

My previous article, Quick MySQL on-liners provided many neat tricks to interact with MySQL directly from the command line. In this article, I’ve added some more things you can do from shell. Running MySQL queries 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 any MySQL query or command in your shell script to get things done faster.

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:

$ mysql -e "\s" -u -p
$ echo "\s" | mysql -u -p

- 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 " - 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.

$ mysql -e "select * from user" -u -p mysql

- Display all users and their privilege details from mysql.user table. You exchange anything between the quotes (“”) to any type of query.

$ mysql -e "show create table users" -u -p sales

- Display the create statement used to create table “users” in “sales” database.

$ tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306

- Monitor mysql port, 3306.

$ my_print_defaults client mysql

- Print defaults for the current client connection. You can put settings /etc/my.cnf for client connections.

$ mysqladmin shutdown

- Restart the mysql server

$ mysqlshow

- A command run from the command prompt that will quickly display database, table and column information.

$ cd /var/lib/mysql/salesdb
$ myisamchk *

- myisamchk helps with damaged files. It’s best to stop database before running it.

$ mysql -e "GRANT ALL PRIVILEGES ON *.salesdb TO salesguy1@localhost IDENTIFIED
BY 'today'" -u -p

- 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’.

$ mysql -X -e "select * from users" salesdb -u -p

- The above will give you XML output with the “-X” option. For html output use the “-H” option.

$ mysql --skip-column-names -e "select * from users" salesdb -u -p

- Display result with no heading.

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=test12

$ ssh remotehost 'mysql -u -p 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.

$ 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.

$ 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.