# 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:
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:
and then use aptitude:

#### Uninstall

To simply remove the program:
To remove the configuration files as well, resulting in a clean environment:
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:
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:

### FreeBSD

The stable FreeBSD port is version 5.0, and beta version 5.1 is also available.
You can install it using this command:
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.
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:
If you want to control whether to launch MySQL on startup, you can use the rcconf package, or update-rc.d:

### 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 /).
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):
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:
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:
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':
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

For more information, see the #SET_PASSWORD section.

### Network configuration
#### 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:

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

### SHOW GRANTS

Prototype:
SHOW GRANTS allow you to check the current privileges for a given user. For example, here are the default privileges for user root:
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.

### DROP USER

Starting with v5.0.2, this removes the associated privileges as well.
With earlier versions, you also need to REVOKE its PRIVILEGES manually.

### SET PASSWORD

Prototype:
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:
There is a command-line synonym:
(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:
Then another user can spy on him by looking at the process list:
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):
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:
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:
* ↑ 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.

## Processes

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

### SHOW PROCESSLIST

Here is a peaceful MySQL server:
mysqladmin provides a command-line synonym:

### KILL

If a heavy, nasty query is consuming too many resources on your server, you need to shut it down.
The brute force way is to restart the server:
A more subtle way is to use SHOW PROCESSLIST to identify the nasty query and kill it independently of other server threads.
There is also a command-line synonym:

## Security

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

## Backup

Backup/recovery and import/export techniques.

### mysqldump

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:
Cf. logrotate(8) in the GNU/Linux man pages for more information.
Variant to backup all databases at once:
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:
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:
You can then get it with your browser or wget:
You can even re-inject it on-the-fly in your local test database:
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:
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:
To flush/reset the logs at the same time (TODO: test):
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.):

## Logs

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

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