How to check show-processlist-every-second ?
mysqladmin -u root -p -i 1 processlist -u username -p you will be promted for a password -i 1 interval one second
Monitor MySQL SQLs
single command:
mysql -u root -proot -e"show full processlist;"
loop command:
perl -e 'while(1) { print "---------------------------------------------------------------------------------n"; print `mysql -uneoxadmin -pneoxadmin -e"show full processlist;"`; sleep(1);};'
How to examine and kill MySQL client processes
mysql> SHOW PROCESSLIST; Listing A +----+-------+----------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+----------------+------+---------+------+-------+------------------+ | 27 | root | 127.0.0.1:1704 | NULL | Query | 0 | NULL | show processlist | | 40 | admin | 127.0.0.1:1725 | db1 | Sleep | 0 | | NULL | | 41 | root | 127.0.0.1:1726 | db1 | Sleep | 1 | | NULL | +----+-------+----------------+------+---------+------+-------+------------------+ 3 rows in set (0.11 sec) mysql> KILL 27; Query OK, 0 rows affected (0.05 sec)
To kill the query being executed by a thread but leave the connection active (yes, MySQL even allows such fine-grained control),
use the KILL QUERY command instead, followed by the appropriate thread ID.
http://www.techrepublic.com/article/how-to-examine-and-kill-mysql-client-processes/#
How to Restore MySQL’s root password ?
Stop mysql service
# /etc/init.d/mysql stop
Start in safe mode w/o password
# mysqld_safe --skip-grant-tables &
Connect to mysql server using mysql client:
# mysql -u root mysql>
Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
Stop MySQL Server:
# /etc/init.d/mysqld stop OR # service mysqld stop
Start MySQL server and test it
# /etc/init.d/mysql start # mysql -u root -p
Ref: http://www.cyberciti.biz/tips/recover-mysql-root-password.html
Ref: http://www.cyberciti.biz/faq/mysql-change-root-password/
How to repair the table ?
Mysql Error : ERROR 145 (HY000): Table ‘./databasename/tablename’ is marked as crashed and should be repaired
Solution : Run Following Query (Depending on how big the table is, this may take a long time.)
repair table tablename; Output of Query: -------------------------------------------------------------- | Table | Op | Msg_type | Msg_text | -------------------------------------------------------------- | databasename.tablename | repair | status | OK | --------------------------------------------------------------
Command for Mysql Backup
mysqldump -u username -p databasename > backup_filename.sql
Query To Know DataBase Engine
show table status from ;
The engine column in the result will give the database engine.
Configure Read Only User in MySQL
Connect to your database as root, then add your new user like so:
CREATE USER 'tester'@'%' IDENTIFIED BY 'password';
The % here means the user ‘tester’ connecting from any host, you can place a network hostname here instead if you want to restrict access further. Naturally you will also want to substitute password with something a little stronger 😉
Now run the following to grant the SELECT privilege to the new user on all databases:
GRANT SELECT ON *.* TO 'tester'@'%';
Or if you want to restrict access to only one database:
GRANT SELECT ON database.* TO 'tester'@'%';
Connect as the new user:
mysql -u tester -p
And now see if you can update a record:
mysql> use alpha; mysql> update Person set version_num = 5 where OID = 1; ERROR 1142 (42000): UPDATE command denied to user 'tester'@'localhost' for table 'Person'
