Everyone writes their damn web applications to use a SQL server these days. I think this is stupid for 80% of the appliations out there however it is now so prevelant that I'm forced to learn SQL. MySQL seems to be the most widely deployed database in the OpenSource world so that's what I'm learning first.

For those that know how MySQL works expect this to be a painful process to watch :-)

There is a great FAQ for MySQL to be found here:

Contents

Basic Commands

Note, this is all much easier and generally better if you install PhpMyAdmin ... so just do that. However if you can't here's how you do things with the CommandLine tools:

Administration

General

And add it to /root/.my.cnf (read /usr/share/doc/mysql-server/README.Debian).

MySql tables are seldom corrupted/damaged beyond hope. First, make sure the database is taken down (check for processes after ./mysql stop) and use myisamchk (or isamchk for pre 3.23.x) to check for damage:

If you get a message like this:

Then you're cool. If you get messages that indicate corrupted data, or long records, then repeat with a -r (on just the table, no wildcards) to repair:

Check the tables again to make sure they don't throw any errors this time, and restart the server.

Here's a good article that provides a quick intro. The mysql docs should be your reference for the details. (That link is to the searchable docs with user comments... the user comments are very very helpful)

Reset the Root Password

If you forget the root password then you need to reset it to gain administrative access to your database.

  1. Stop MySql:

    # /etc/init.d/mysql stop
    
  2. Stop MySql and restart it with the option to not check it's grant tables. Remember that while MySql is running this way your entire database is wide open and could be changed by anyone, so you should also disable MySql's network support so only people on the local machine can make changes to your databases.

    # /usr/bin/safe_mysqld --skip-grant-tables --skip-networking
    
  3. Reset the root password using the grant command:
    # mysql
    mysql> use mysql;
    mysql> update user SET Password=password('NewPassword') where user='root';
    
  4. Optionally you may want to make sure that the root user can connect from any host (be aware that this does allow brute force attacks against your database). NOTE: You must restart MySql normally (ie. without the --skip-grant-tables command) to be able to use the GRANT command.

    # mysql 
    mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'Password' WITH GRANT OPTION;
    
  5. Stop and start MySql normally.

    # /etc/init.d/mysql restart
    

Troubleshooting / Debugging

Figuring Out What MySQL Is Doing

Sometimes all you can see is that MySql is really busy and you want to know what it is that's doing all the queries so you can fux it. By logging into MySql you can run these commands manually which allow you to see what it's doing internally:

mysql> show status;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 5821       |
| Aborted_connects         | 622        |
| Bytes_received           | 2442017042 |
... <snip> ...

mysql> show processlist;
+----------+------+------------------------+------------+---------+-------+-------+------------------+
| Id       | User | Host                   | db         | Command | Time  | State | Info             |
+----------+------+------------------------+------------+---------+-------+-------+------------------+
| 18922975 | root | ronin.spack.org        | drupal     | Sleep   | 18783 |       | NULL             |
| 18922978 | root | ronin.spack.org        | drupal     | Sleep   | 18782 |       | NULL             | 
... <snip> ...

However there is much better way then running these commands manually, download MyTop which gives you an interface similar to that of Unix "top" only showing MySql threads and processes.

Resources

Mysql can report a variety of errors, and sometimes just hang. Here's how to figure out whats going wrong.

Best Practices

Info on how to setup your mysql environment so you don't have to be mystified by errors and hangs.


CategorySoftware

MySql (last edited 2006-08-28 19:28:51 by AdamShand)