Quick Introduction to MySQL (Part 2)
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;
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;
mysql> show processlist\G;
If you want to see complete queries for show processlist command run:
mysql> show full processlist;
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.