Skip to content

June 30, 2012

Quick Introduction to MySQL (Part 2)

by noise

This is second tutorial from a series of tutorials about MySQL. We will begin with some MySQL general tips.

1. MySQL General Tips

1.1 MySQL Logging

When you develop complex application it is useful to log every query to a file, that way you will know for example if your command from a PHP script will add values to your database. To enable logging it depends on your operating system. For FreeBSD you must edit /usr/local/etc/rc.d/mysql-server script and on the command_args line the following string: –log=/var/log/mysql.log. Then restart mysql-server.

For Linux distribution we will add later the info on how to activate logging of MySQL querries.

1.2 Repair a MySQL Database

If you have errors in your tables from your databases you can check and repair using mysqlcheck utility from your shell (this comes with MySQL package):

mysqlcheck -r myinfo -u root -p

1.3 Show status of MySQL server

To see the status of your MySQL server run:

mysql> show status;

or:

show status\G;

To see status of a MySQL table run (you must first use a database):

mysql> show table status like '%';

1.4 See queries in realtime for every MySQL user

To see queries in realtime for every MySQL user run:

mysql> show processlist;

or:

mysql> show processlist\G;

If you want to see complete queries for show processlist command run:

mysql> show full processlist;

or:

mysql> show full processlist\G;

You can also kill a query by running MySQL kill command (where 100 is the ID of the querry/process):

mysql> kill 100

1.5 See MySQL user name you are connected with to MySQL client

To see MySQL user name for current connection run:

mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

That’s all for today.

Read more from MySQL

Leave a Reply

required
required

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments