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:

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:

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.

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

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.