Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
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
Find the current user UID and GID
Answer:
Use the id command
E.g. check the UID and GID of the user - "mysql"
# id mysql
uid=106(mysql) gid=111(mysql) groups=111(mysql)
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)
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
How to create a new MySQL user?
Answer:
1. Enter the MySQL shell using root account
mysql -u root -p mysql
2. Execute the SQL like the followings
GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
3. Now try to login using user1 account
mysql -u user1 -p db1
