# MySQL/Administration

## Installation

### Debian packages

The package name is usually *mysql-server*, either directly or as a
transitional package for the latest version.

#### Stable

There are two Debian packages in the current *stable* release:

-   mysql-server:
    depends on latest MySQL version
-   mysql-server-5.0:
    MySQL 5.0

You can install it using this command:

`apt-get install mysql-server`

or by installing the package you want using the Synaptic GUI.

#### Backports

Backports.org may also offer more recent versions.

To install it, you need to add the backports source in your
`/etc/apt/sources.list`:

`deb ``http://www.backports.org/debian`` lenny-backports main`

and then use aptitude:

`apt-get install -t lenny-backports mysql-server-5.1`

#### Uninstall

To simply remove the program:

`apt-get remove mysql-server`

To remove the configuration files as well, resulting in a clean
environment:

`apt-get remove --purge mysql-server`

Debconf will ask you if you want to remove the existing databases as
well. Answer wisely!

### Fedora Core 5

The package name is
mysql-server.

You can install it using this command:

`yum install mysql-server`

which will take care of installing the needed dependencies.

Using *pirut* (Applications-\>Add/Remove Software), you can also server
*MySQL Database* in the *Servers* category:

![](MySQL-pirut.png "MySQL-pirut.png")

### Gentoo

MySQL is available in the main Portage tree as \"dev-db/mysql\". You
must use the fully qualified ebuild name as \"mysql\" is made ambiguous
by \"virtual/mysql\"

Command:

`emerge dev-db/mysql`

### FreeBSD

The stable FreeBSD port is version
5.0, and beta
version 5.1 is also available.

You can install it using this command:

`cd /usr/ports/databases/mysql50-server/ && make install clean`

This command will install the MySQL 5.0 server as well as all necessary
dependencies (which includes the MySQL client). t

## Start the service

### Debian

In Debian, you use the `mysql` init script.

`/etc/init.d/mysql start`\
`/etc/init.d/mysql stop`\
`/etc/init.d/mysql restart`

If you need to do so in scripts, prefer the `invoke-rc.d` command, which
only restarts the service if it is launched on system startup. That way,
you do not launch a service if it wasn\'t meant to be run:

`invoke-rc.d mysql start|stop|restart`

If you want to control whether to launch MySQL on startup, you can use
the `rcconf` package, or update-rc.d:

`cp /usr/local/mysql/support-files/mysql.server /etc/init.d/anysqlservernamehere`\
`chmod +x /etc/init.d/anysqlservernamehere`\
`update-rc.d anysqlservernamehere defaults`

### Fedora Core

Fedora Core suggests that you use the `service` wrapper, which cleans
the environment before to run the service, so that all services run in
the same standard environment (for example, the current directory is set
to the system root `/`).

`service mysqld start|stop|restart`\
`service mysqld --full-restart # means stop, then start - not a direct restart`

You can also use the `/etc/init.d/mysqld` if needed.

FC5 displays useful hints the first time you launch the MySQL server
(i.e. when launching /usr/bin/mysql_install_db):

`$ service mysqld start`\
`[...]`\
`PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !`\
`To do so, start the server, then issue the following commands:`\
`/usr/bin/mysqladmin -u root password 'new-password'`\
`/usr/bin/mysqladmin -u root -h localhost password 'new-password'`\
`[...] `

See the next section about changing passwords.

To control whether to launch MySQL on startup, you can use the `ntsysv`
tool: ![](MySQL-ntsysv.png "MySQL-ntsysv.png")

## Client connection

There are two ways to connect to a MySQL server, using Unix sockets and
TCP/IP.

The default TCP/IP port is 3306:

`# grep mysql /etc/services`\
`mysql           3306/tcp                        # MySQL`\
`mysql           3306/udp                        # MySQL`\
`mysql-cluster   1186/tcp                        # MySQL Cluster Manager`\
`mysql-cluster   1186/udp                        # MySQL Cluster Manager`\
`mysql-im        2273/tcp                        # MySQL Instance Manager`\
`mysql-im        2273/udp                        # MySQL Instance Manager`

As a client, MySQL interprets \'localhost\' as \'use the Unix socket\'.
This means that MySQL won\'t connect to 127.0.0.1:3306, but will use
`/var/run/mysqld/mysqld.sock`:

`$ mysql -h localhost`\
`mysql> \s`\
`--------------`\
`mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i386) using readline 5.0`\
`[...]`\
`Current user:           sylvain@localhost`\
`[...]`\
`Connection:             Localhost via UNIX socket`\
`[...]`\
`UNIX socket:            /var/lib/mysql/mysql.sock`

If you really need to connect to MySQL via TCP/IP to the local host
without using Unix sockets, then specify \'127.0.0.1\' instead of
\'localhost\':

`$ mysql -h 127.0.0.1`\
`mysql> \s`\
`--------------`\
`mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i386) using readline 5.0`\
`[...]`\
`Current user:           sylvain@localhost`\
`[...]`\
`Connection:             127.0.0.1 via TCP/IP`\
`[...]`\
`TCP port:               3306`

In both cases, MySQL will understand your machine name as \'localhost\'
(this is used in the privileges system).

## Configuration

Configure /etc/mysql/my.cnf - for heavily loaded databases, for fat
databases\...; different kinds of connections (Unix sockets, TCP/IP w/
or w/o SSL, MySQL+SSL licensing issues)

### Change the root password

`$ mysql -u root`\
`mysql> SET PASSWORD = PASSWORD('PassRoot');`

For more information, see the #SET_PASSWORD
section.

### Network configuration

`--bind-address=127.0.0.1 # localhost only`\
`--bind-address=0.0.0.0 # listen on all interfaces`\
`--bind-address=192.168.1.120 # listen on that IP only`

#### skip-networking

When you specify `skip-networking` in the configuration, then MySQL will
not listen on any port, not even on localhost (127.0.0.1). This means
that only programs running on the same machine than the MySQL server
will be able to connect to it. This is a common setup on dedicated
servers.

The only way to contact MySQL will be to use the local *Unix socket*,
such as `/var/run/mysqld/mysqld.sock` (Debian) or
`/var/lib/mysql/mysql.sock` (FC5). You can specify where the socket is
located using the `socket` parameter in the `[mysqld]` section of the
configuration:

`[mysqld]`\
`...`\
`socket=/var/lib/mysql/mysql.sock`

## Privileges

The MySQL privileges system.

### Introduction

MySQL requires you to identify yourself when you connect to the
database. You provide the following credentials:

-   an identity, composed of:
    -   a username
    -   a machine name or IP address (detected automatically by the
        server)
-   a password, to prove your identity

Usually, MySQL-aware applications also ask you for a database name, but
that\'s not part of the credentials, because this does not relate to who
you are.

MySQL then associates privileges to these credentials; for example, the
right to query a given database, add data to another one, create
additional databases or remove existing ones, etc.

### Who am I?

Once connected, it is not necessarily obvious who MySQL thinks you are.
CURRENT_USER() provides this information:

`mysql> SELECT CURRENT_USER();`\
`+----------------+`\
`| CURRENT_USER() |`\
`+----------------+`\
`| root@localhost |`\
`+----------------+`\
`1 row in set (0.00 sec)`

### SHOW GRANTS

Prototype:

`SHOW GRANTS FOR user`\
`SHOW GRANTS --current user`

SHOW GRANTS allow you to check the current privileges for a given user.
For example, here are the default privileges for user root:

`mysql> SHOW GRANTS FOR 'root'@'localhost';`\
`+---------------------------------------------------------------------+`\
`| Grants for root@localhost                                           |`\
`+---------------------------------------------------------------------+`\
`| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |`\
`+---------------------------------------------------------------------+`\
`1 row in set (0.00 sec)`

You also use use `SHOW GRANTS;` to check the privileges for the current
user.

### GRANT

The GRANT command allows you to give (GRANT) privileges to a given user.

`GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, database.* `\
`TO 'user'@'localhost';`

### DROP USER

`DROP USER 'mediawiki';`\
`DROP USER 'mediawiki'@'host';`

Starting with v5.0.2, this removes the associated privileges as well.

With earlier versions, you also need to REVOKE its PRIVILEGES manually.

### REVOKE

`REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'host';`\
`REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';`

### SET PASSWORD

Prototype:

`SET PASSWORD [FOR user] = PASSWORD('your_password')`

If *user* is not specified, the current user is used (this is useful
when you connect to mysql using the command line).

Example with an explicit user:

`SET PASSWORD FOR 'mediawiki'@'localhost' = PASSWORD('ifda8GQg');`

There is a command-line synonym:

`mysqladmin password 'your_password'`

(with the usual connection options `-h` `-u` and `-p`)

However, using passwords on the command line presents a security risk.
For example, if root changes his MySQL password:

`root# mysqladmin password 'K2ekiEk3'`

Then another user can spy on him by looking at the process list:

`user$ ps aux | grep mysqladmin`\
`root      7768  0.0  0.1   7044  1516 pts/1    S+   16:57   0:00 mysqladmin password K2ekiEk3`

Conclusion: don\'t user `mysqladmin password`.

If you are looking for a way to generate passwords, either secure or
easy to remember, try the `pwgen` program (there is a Debian package
available):

`$ pwgen`\
`ooGoo7ba ir4Raeje Ya2veigh zaXeero8 Dae8aiqu rai9ooYi phoTi6gu Yeingo9r`\
`tho9aeDa Ohjoh6ai Aem8chee aheich8A Aelaeph3 eu4Owudo koh6Iema oH6ufuya`\
`[...]`\
`$ pwgen -s # secure`\
`zCRhn8LH EJtzzLRE G4Ezb5BX e7hQ88In TB8hE6nn f8IqdMVQ t7BBDWTH ZZMhZyhR`\
`gbsXdIes hCQMbPE6 XD8Owd0b xitloisw XCWKX9B3 MEATkWHH vW2Y7HnA 3V5ubf6B`\
`[...]`

Very handy if you manage a lot of accounts :)

### MySQL 4.1 password issues

As of version 4.1, MySQL introduced a password-related change.

You\'ll experience this via errors such as: *Client does not support
authentication protocol requested by server; consider upgrading MySQL
client*. [^1]

If you wish to support older client programs, you need to define the
MySQL account password this way:

`SET PASSWORD [FOR user] = OLD_PASSWORD('your_pass');`

There is apparently no way to use old passwords with the
`GRANT ... IDENTIFIED BY 'password'` syntax.

Alternatively, you can use the `old_passwords` configuration option in
your server\'s `my.cnf`. This means that new passwords will be encoded
using the old-style, shorter, less secure format. For example, in Debian
Sarge and FC5, the MySQL default configuration enforces old-style
password for backward compatibility with older clients:

`[mysqld]`\
`...`\
`old_passwords=1`

```{=html}
<references />
```
## Processes

MySQL provides a Unix-like way to show the current server threads and
kill them.

### SHOW PROCESSLIST

Here is a peaceful MySQL server:

`mysql> SHOW PROCESSLIST;`\
`+----+-----------+-----------+-----------+---------+------+-------+------------------+`\
`| Id | User      | Host      | db        | Command | Time | State | Info             |`\
`+----+-----------+-----------+-----------+---------+------+-------+------------------+`\
`| 34 | monddprod | localhost | monddprod | Sleep   | 1328 |       | NULL             |`\
`| 43 | root      | localhost | NULL      | Query   |    0 | NULL  | SHOW PROCESSLIST |`\
`+----+-----------+-----------+-----------+---------+------+-------+------------------+`\
`2 rows in set (0.00 sec)`

`mysqladmin` provides a command-line synonym:

`$ mysqladmin processlist`\
`+----+-----------+-----------+-----------+---------+------+-------+------------------+`\
`| Id | User      | Host      | db        | Command | Time | State | Info             |`\
`+----+-----------+-----------+-----------+---------+------+-------+------------------+`\
`| 34 | monddprod | localhost | monddprod | Sleep   | 1368 |       |                  |`\
`| 44 | root      | localhost |           | Query   | 0    |       | show processlist |`\
`+----+-----------+-----------+-----------+---------+------+-------+------------------+`

### KILL

If a heavy, nasty query is consuming too many resources on your server,
you need to shut it down.

`TODO: Add a sample SHOW PROCESSLIST output here`

The brute force way is to restart the server:

`/etc/init.d/mysql restart`

A more subtle way is to use SHOW PROCESSLIST to identify the nasty query
and kill it independently of other server threads.

`mysql> KILL 342;`\
`Query OK, 0 rows affected (0.00 sec)`

There is also a command-line synonym:

`$ mysqladmin kill 342`

## Security

Basic security: firewall (iptables), SELinux? also, some words about: do
not store passwords as cleartext

## Backup

Backup/recovery and import/export techniques.

### mysqldump

`` mysqldump --opt -h 192.168.2.105 -u john -p'****' mybase | gzip > mybase-`date +%Y%m%d`.sql.gz ``

This creates the `mybase-20061027.sql.gz` file.

`--opt` is the magical option that uses all the options that are
generally useful. In recent versions of mysqldump, it is even enabled by
default, so you need not type it. `--opt` means
`--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset` -
so it will lock tables during the backup for consistency, add DROP TABLE
statements so the dump can be applied without cleaning the target
database, will use the most efficient ways to perform the INSERTs and
specify the charset (latin1, Unicode/UTF-8\...) used.

If you don\'t provide a database to mysqldump, you\'ll get a backup
containing all databases - which is less easy to use for restoring a
single database later on.

### Daily rotated mysqldump with logrotate

We\'re using logrotate in a slightly non-standard way to keep a batch of
dumps. Each day, logrotate will cycle the dumps to keep the last N
dumps, removing old backups automatically, and generating the new one
immediately through a postrotate hook.

The following configuration keeps 2 months of daily backups:

`/dumps/mybase.sql.gz {`\
`        rotate 60`\
`        dateext`\
`        dateyesterday`\
`        daily`\
`        nocompress`\
`        nocopytruncate`\
`        postrotate`\
`          HOME=/root mysqldump --opt mybase | gzip > /dumps/mybase.sql.gz`\
`        endscript`\
`}`

Cf. logrotate(8) in the GNU/Linux man pages for more information.

Variant to backup all databases at once:

`/dumps/*/*.sql.gz {`\
`        daily`\
`        rotate 20`\
`        dateext`\
`        dateyesterday`\
`        nocompress`\
`        sharedscripts`\
`        create`\
`        postrotate`\
`                export HOME=/root`\
`                for i in $(mysql --batch --skip-column-names -e 'SHOW DATABASES' | grep -vE '^information_schema|performance_schema$'); do`\
`                        if [ ! -e /dumps/$i ]; then mkdir -m 700 /dumps/$i; fi`\
`                        mysqldump --events $i | gzip -c > /dumps/$i/$i.sql.gz`\
`                done`\
`        endscript`\
`}`

Setup:

-   Create your `~/.my.cnf` for password-less database access
-   Place the logrotate configuration file above in the
    `/etc/logrotate.d/` directory
-   Bootstrap the first dump:
    -   `mkdir -m 700 /dumps`
    -   `mkdir -m 700 /dumps/mybase`
    -   `touch /dumps/mybase/mybase.sql.gz`
    -   `logrotate -f /etc/logrotate.d/mysql-dumps`
-   Check the dump using `zcat /dumps/mybase.sql.gz`.

Comments on the code: `HOME=/root` is needed for systems (such as FC5)
that set `HOME=/` in their cron, which prevents mysqldump from finding
the `.my.cnf` configuration. We also use `| gzip` instead of
logrotate\'s `compress` option for disk I/O efficiency (single-step).

In production, you\'ll get something like this:

`# ls -lt /dumps`\
`total 16520`\
`-rw-r----- 1 root clisscom 2819533 mar  2 06:25 clisscom.sql.gz`\
`-rw-r----- 1 root clisscom 2815193 mar  1 06:25 clisscom.sql.gz-20100302`\
`-rw-r----- 1 root clisscom 2813579 fév 28 06:26 clisscom.sql.gz-20100301`\
`-rw-r----- 1 root clisscom 2812251 fév 27 06:25 clisscom.sql.gz-20100228`\
`-rw-r----- 1 root clisscom 2810803 fév 26 06:25 clisscom.sql.gz-20100227`\
`-rw-r----- 1 root clisscom 2808785 fév 25 06:25 clisscom.sql.gz-20100226`\
`...`

Beware that the date in the filename is the date of the rotation, not
the date of the dump. Using `dateext` helps with remote backups, because
filenames don\'t change daily, not you avoid re-downloading all of
`/dumps` each time.

### Remote mysqldump using CGI

mysqldump can be found sometimes in shared-hosting facilities. You can
use a simple CGI script to get a direct dump:

`#!/bin/sh`\
\
`echo "Content-Type: application/x-tar"`\
`echo "Content-Encoding: x-gzip"`\
`echo ""`\
\
`mysqldump --host=mysql.hosting.com --user=john --password=XXXXX my_base | gzip 2>&1`

You can then get it with your browser or wget:

`$ wget -O- --quiet ``http://localhost/~sylvain/test2.cgi```  > base-`date +%Y%m%d`.sql.gz ``

You can even re-inject it on-the-fly in your local test database:

`$ wget -O- --quiet ``http://localhost/~sylvain/test2.cgi`` | gunzip | mysql test_install -u myself -pXXXX`

Protect the script with a `.htaccess`, write a `.netrc` for wget to use,
and you\'ll have a simple, unattended way to grap a backup even without
command-line access. This allows to gain time when grabing a dump
(compared to using phpMyAdmin) and to setup remote automated backups (no
interaction is needed).

Something similar should be feasible in PHP provided you have access to
exec().

### Exporting a single table

If you need to import/export a table, not a complete database, check
MySQL/Language#Import\_.2F_export.

## Binary logs

Binary logs are a mechanism to keep track of everything that happens on
the MySQL server (forensics), allowing to replay the same sequence of
commands on a different computer (master/slave replication), or at a
later time (crash recovery).

On Debian they are stored in `/var/log/mysql/mysql-bin.0*`.

To view the SQL commands in a binary log, you use the `mysqlbinlog`
command:

`mysqlbinlog /var/log/mysql/mysql-bin.000001`

For the crash recovery to be useful, binary logs are usually stored on a
different computer (via a NFS mount, for example). Note that it is meant
to recover the *full* mysql server, not just one database. You could
attempt to filter the log by database, but this isn\'t straightforward.

So in order use binary logs as a recovery plan, you usually combine them
with a full standard backup:

`mysqldump -A | gzip > all.sql.gz`

To flush/reset the logs at the same time (TODO: test):

`mysqldump -A --master-data --flush-logs | gzip > all.sql.gz`

To recover you\'ll just combine the two sources (preferably, disable
binary logging in the server configuration during the recovery, and
re-enable it right after.):

`(zcat all.sql.gz && mysqlbinlog /var/log/mysql/mysql-bin.0*) | mysql`

## Logs

Where interesting logs are located, common errors to look at. For
example:

`tail -f /var/log/mysql.log`

## Admin Tools

Various third-party graphical interfaces and utilities.

### Web interfaces

-   phpMyAdmin (wikipedia:
    phpMyAdmin)
-   eSKUeL: an alternative to
    phpMyAdmin
-   MySQL on Servers
    Support

### Desktop GUI

-   MySQL
    Administrator: from
    MySQL AB. If you want to create real backups, though, do not use
    this, since it runs backups using `at` on the client machine - which
    is likely not to be online every day.


[^1]: For example, you can get this error on Debian Sarge\'s
    apache+libapache_mod_php4+php4-mysql, the latter depends on
    libmysqlclient12 aka MySQL 4.0
    (`ldd /usr/lib/php4/20020429/mysql.so` gives
    `libmysqlclient.so.12 => /usr/lib/libmysqlclient.so.12`). If you
    rely and libmysqlclient14 or later, then your application supports
    both the old and the new password formats.
