How to execute SQL file with MySQL
Answer:
If you already inside the mysql client, type
mysql> source /tmp/data.sql
Or you can pipe the file to mysql client
cat /tmp/data.sql | mysql -u root
Jan 162010
Jan 122010
Enable slow query log in MySQL
Answer:
Turn on the Slow Query log in MySQL allow you to debug the sources of slow and problematic SQL.
To activate it, edit the MySQL configurations, e.g. /etc/my.cnf
[mysqld]
...
...
log-slow-queries = /var/log/mysqld-slow.log
long_query_time = 5
Restart MySQL and you will notice the file mysqld-slow.log will be created.
Any queries take more than 5 seconds to execute will be logged in this file.
Jan 082010
How to combine insert & update in a single SQL statement
Answer:
It is quite common to check if a given record already exist in database (check by primary key), insert it if not found, or update it if found.
It can be easily done using the MySQL's ON DUPLICATE KEY UPDATE statement
INSERT INTO `foo` (id, num) values (1, 0)
ON DUPLICATE KEY UPDATE num = num + 1;
Reference: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
Jan 022010
How to check and kill long running MySQL query
Answer:
1. Get into the MySQL shell, type 'SHOW PROCESSLIST' to see which query is blocking
mysql> SHOW PROCESSLIST;
2. It will print out all the current executing queries, and you will find the thread Id.
+-----+-------------+--------------------+-------+---------+-------+
| Id | User | Host | db | Command | Time| State | Info
+-----+-------------+--------------------+-------+---------+-------+-
| 3928 |root |192.168.1.5:59202 |db1 |Query | 84 | Locked |
select * from table1 where name like '%test%'
3. Remember this Id, to kill it, use the "KILL" command
mysql> KILL 3928;
Query OK, 0 rows affected (0.05 sec)
Jan 022010
How to delete MySQL user?
Answer:
1. Enter the MySQL shell using root account
mysql -u root -p mysql
2. Execute the SQL like the followings
DROP USER user1;
FLUSH PRIVILEGES;
3. Now try to login using user1 account to check if account is removed
mysql -u user1 -p db1
