MySql - spack.org/wiki

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:

http://www.bitbybit.dk/mysqlfaq/faq.html RIO to quote from the page itself. It's suffered bitrot since the author got a new job. Follow the link to find the irony in this. -- MichaelRasmussen

Contents

Contents
Basic Commands
Administration
1. General
2. Reset the Root Password
Troubleshooting / Debugging
1. Figuring Out What MySQL Is Doing
2. Resources
Best Practices

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:

# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 73028 to server version: 3.23.36-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql>

List the databases on a server:

mysql> show databases; 
+-----------------------+
| Database              |
+-----------------------+
| Foo                   |
| Bar                   |
| Baz                   |
+-----------------------+
3 rows in set (0.00 sec)

List the tables in a database:

mysql> use testdb; 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_testdb   |
+--------------------+
| foo                |
| bar                |
+--------------------+
2 rows in set (0.00 sec)

To create a user and give them rights over a database, you use the GRANT command which has a format like this:
```
GRANT ALL PRIVILEGES ON "<database>.<table>" TO "<username>"@"<host>" IDENTIFIED BY 'SecretPassword'; 
```
To create the user "foo" and grant them global permissions to all databases from any host:
```
mysql> GRANT ALL PRIVILEGES ON *.* TO "foo"@"%" IDENTIFIED BY "SecretPassword"; 
```
To create the user "admin" and grant them RELOAD and PROCESS permissions to all databases from any host:
```
mysql> GRANT RELOAD , PROCESS ON *.* TO "admin"@"%" IDENTIFIED BY "SecretPassword"; 
```
To create the user "adam" and grant them all permissions to the database "iaddressbook":
```
mysql> GRANT ALL PRIVILEGES ON iaddressbook.* TO adam@localhost IDENTIFIED BY "secretpass"; 
```

Administration

General

Set the root password if it doesn't already have one (Note that "password" is a command to mysqladmin, not the old password):
```
# mysqladmin -u root -p password new-password 
```

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

Making MySQL accessable to remote access

# grant all privileges on dbname.* to username@'%' identified by 'password';

Backup just the table structure of a database:

# mysqldump -d -u <username> -p <password> <database> > /tmp/out.sql

Backup or dump a database:

# mysqldump -u <username> -p <password> <database> > /tmp/out.sql

Backup all databases:

# mysqldump -Aa -u <username> -p<password> > /tmp/out.sql

Restore a database:

# mysql -u <username> -p <password> <database> < /tmp/in.sql

Repair a database:

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:

# /usr/bin/myisamchk /var/lib/mysql/<database>/*.MYI

# /usr/bin/isamchk /var/lib/mysql/<database>/*.ISM

If you get a message like this:

Checking MyISAM file: /var/lib/mysql/<your_db_name>/<your_table_name>.MYI 
Data records:     513   Deleted blocks:       0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links

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:

# /usr/bin/myisamchk -r /var/lib/mysql/<database>/<tablename>.MYI

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

Note: Even if you're running 3.23.x, if you have .ISD or .ISM tables, then use the older isamchk to check and repair.

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.

Stop MySql:
```
# /etc/init.d/mysql stop
```
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
```

Reset the root password using the grant command:

# mysql
mysql> use mysql;
mysql> update user SET Password=password('NewPassword') where user='root';

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;
```
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.

What To Do If MySQL Keeps Crashing
Debugging a MySQL server
Some MySQL "gotchas"

Best Practices

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

O'Reilly's Ten Mysql Best Practices
some comments on above article, and a critique
DevShed article on using MySql transactions

CategorySoftware

Wiki

Page

User