MySQL Administration

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'