MySQL/Administration
Installation
editDebian packages
editThe package name is usually mysql-server, either directly or as a transitional package for the latest version.
Stable
editThere 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
editBackports.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
editTo 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
editThe 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
editMySQL 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
editThe 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
editDebian
editIn 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
editFedora 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
editThere 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
editConfigure /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
edit$ mysql -u root mysql> SET PASSWORD = PASSWORD('PassRoot');
For more information, see the #SET_PASSWORD section.
Network configuration
edit--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
editWhen 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
editThe MySQL privileges system.
Introduction
editMySQL 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?
editOnce 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
editPrototype:
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
editThe 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
editDROP 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
editREVOKE ALL PRIVILEGES ON database.* FROM 'user'@'host'; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';
SET PASSWORD
editPrototype:
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
editAs 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
- ↑ 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
giveslibmysqlclient.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
editMySQL provides a Unix-like way to show the current server threads and kill them.
SHOW PROCESSLIST
editHere 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
editIf 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
editBasic security: firewall (iptables), SELinux? also, some words about: do not store passwords as cleartext
Backup
editBackup/recovery and import/export techniques.
mysqldump
editmysqldump --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
editWe'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
editmysqldump 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
editIf you need to import/export a table, not a complete database, check MySQL/Language#Import_.2F_export.
Binary logs
editBinary 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
editWhere interesting logs are located, common errors to look at. For example:
tail -f /var/log/mysql.log
Admin Tools
editVarious third-party graphical interfaces and utilities.
Web interfaces
edit- phpMyAdmin (wikipedia: phpMyAdmin)
- eSKUeL: an alternative to phpMyAdmin
- MySQL on Servers Support
Desktop GUI
edit- 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.