Mysql Setup

Basic commands in MySQL

Create Database

# mysql -u root
mysql> CREATE DATABASE wikidb;
mysql> grant create, references, drop, select, insert, update, delete, alter, lock tables on wikidb.* to 'wiki-user'@'localhost' identified by 'pwd';
mysql> flush privileges;

Reset Password

# /etc/init.d/mysql stop
# /usr/sbin/mysqld --skip-grant-tables
# mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
mysql> FLUSH PRIVILEGES;
# /etc/init.d/mysql restart

Monitor Mysql

:~# mysqladmin processlist

+-------+--------+-----------+--------+---------+------+-------+------------------+
| Id    | User   | Host      | db     | Command | Time | State | Info             |
+-------+--------+-----------+--------+---------+------+-------+------------------+
| 19958 | wikius | localhost | wikidb | Sleep   | 0    |       |                  |
| 19959 | wikius | localhost | wikidb | Sleep   | 0    |       |                  |
| 19960 | root   | localhost |        | Query   | 0    |       | show processlist |
+-------+--------+-----------+--------+---------+------+-------+------------------+

mytop

MySQL on localhost (5.0.51a-24+lenny5)                                                                                                                                               up 102+00:03:12 [13:47:48] Queries: 303.8k  qps:    0 Slow:     0.0         Se/In/Up/De(%):    19/00/00/00              qps now:    0 Slow qps: 0.0  Threads:    1 (   1/   6) 00/00/00/00  Key Efficiency: 99.6%  Bps in/out:   0.0/  0.0   Now in/out:   8.3/ 1.3k

      Id      User         Host/IP         DB      Time    Cmd Query or State                                                                                                                                        --      ----         -------         --      ----    --- ----------                                                                                                                                         19984      root       localhost                    0  Query show full processlist