MySQL/Administration

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 : 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

Debian
In Debian, you use the  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  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  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  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  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  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: 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
 * 1) grep mysql /etc/services

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 : $ 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 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  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  (Debian) or   (FC5). You can specify where the socket is located using the  parameter in the   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  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    and  )

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.

If you are looking for a way to generate passwords, either secure or easy to remember, try the  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  syntax.

Alternatively, you can use the  configuration option in your server's. 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

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  file.

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. means  - 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  for password-less database access
 * Place the logrotate configuration file above in the  directory
 * Bootstrap the first dump:
 * Check the dump using.
 * Check the dump using.
 * Check the dump using.
 * Check the dump using.
 * Check the dump using.

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

In production, you'll get something like this:

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 ...
 * 1) ls -lt /dumps

Beware that the date in the filename is the date of the rotation, not the date of the dump. Using  helps with remote backups, because filenames don't change daily, not you avoid re-downloading all of   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: 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
 * 1) !/bin/sh

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, write a   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.

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.

To view the SQL commands in a binary log, you use the  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  on the client machine - which is likely not to be online every day.