MySQL

Debian packages

I use MySQL to provide database services on my machine. The following Debian packages need to be installed:

mysql-server
mysql-client
phpmyadmin

Maintenance DB user

Installing the MySQL packages creates a maintenance database user

debian-sys-maint@localhost

An automatically generated password is placed in plaintext (!) in

/etc/mysql/debian.cnf

The user is used by

Maintenance scripts run by cron (e.g. log rotation)
Init script to stop the server

Configuration

Configuration files

The MySQL main configuration file is located here:

/etc/mysql/my.cnf

The file is split into sections. Configuration options in any given section are used only by program that match that section. Examples:

Options in the section "[mysqld_safe]" are used only by mysqld_safe
Options in the section "[mysqldump]" are used by mysqldump
Options in the section "[mysqld]" are used by mysqld itself
Options in the section "[client]" are used by all clients of MySQL. TODO: What does this mean exactly? Web applicatins are clients of MySQL, through PHP, so does this mean that PHP is using the options in this section?

As is common in Debian, you don't edit the main configuration file itself, instead you place your local configuration into one or more files in this folder:

/etc/mysql/conf.d

The files in the conf.d folder must end with .cnf, otherwise they are ignored. A file can have entirely new configuration options, or it can override the values of options that are already present in the main configuration file.

Here are my local configuration options:

root@pelargir:~# cat /etc/mysql/conf.d/pelargir.cnf 
[mysqld]

# Before MySQL 5.6.6 the default for this option is off (disabled).
# We are not yet running MySQL 5.6.6, so we must explicitly enable
# this option.
innodb_file_per_table = 1

User-specific configuration

User-specific settings that add to the default configuration, or override default settings, can be placed in a file in the user's home directory:

~/.my.cnf

As long as the file is named like this, all MySQL commands will automatically read settings from this file. If the file were named differently, it would be necessary to specify the command-line option --defaults-extra-file.

I am using this file to store the database username and password that should be used by automated database maintenance jobs run by cron. One such job is the backup job executed by Backupninja. This is the content of the file:

root@pelargir:~# cat .my.cnf 
# backupninja needs this to determine the list of databases
# to backup
[mysql] 
user = root
password = secret

# backupninja needs this to perform a sanity check whether the daemon is running
[mysqladmin] 
user = root
password = secret

# backupninja needs this to actually create the backups
[mysqldump] 
user = root
password = secret

Obviously the file permissions must be set so that only root can access the file:

root@pelargir:~# ls -l .my.cnf 
-r-------- 1 root root 229 Jul 10 16:49 .my.cnf

A welcome side effect of the presence of this file is that I no longer need to type in the database password when I am logged in as root and run the mysql or mysqldump commands.

System variables

The information in this section is taken from the "Using system variables" section of the MySQL documentation.

A running server maintains a set of system variables whose values govern the server's operation.

There are global system variables and session system variables
When the server starts it uses values from the configuration files, or from specified command line options, or hard-coded default values to initialize its global system variables
When a client opens a connection, a session is created for that connection. The session has its own set of session system variables. The server initializes the session system variables with current values from the global system variables
A client can modify the values for its own session system variables
With the appropriate privileges, a client can also modify the values of global system variables

The following SQL query displays the value of a session system variable:

SELECT @@sort_buffer_size

Prefix the variable name with global. or session. to explicitly distinguish between global and session system variables:

SELECT @@global.sort_buffer_size, @@session.sort_buffer_size, @@sort_buffer_size

Note: Using the session. prefix results in an error for system variables that don't allow session-specific values. For instance, the innodb_file_per_table variable can only have a global value. This reveals that the explanations at the beginning of this section do not exactly match how MySQL implements system variables, but the MySQL documentation is not very precise in its specification so my explanations are a suitable approximation.

The following queries show all system variables:

SHOW VARIABLES;
SHOW GLOBAL VARIABLES
SHOW SESSION VARIABLES

Changing the value of a dynamic system variable is done like this:

SET sort_buffer_size = 123456;
SET @@session.sort_buffer_size = 123456;
SET @@global.sort_buffer_size = 123456;
-- The next two statements are equivalent
SET @@global.sort_buffer_size = @@global.sort_buffer_size;
SET @@global.sort_buffer_size = DEFAULT;

Notes:

Without prefix the session value is changed
Setting a global system variable requires the SUPER privilege
Setting a global system variables affects only future sessions. It doesn't even affect the current session of the client that performs the change.

Stuff to do before going live with a server

Set the initial password for the super user (using the -p option without actually specifying a password forces you to interactively enter the password)

/usr/bin/mysqladmin -u root -p

Remove the anonymous user ('@'%) and revoke all of its privileges to prevent all anonymous access. I don't have the command for this, I did this via phpMyAdmin

Users and privileges

Log in from the command line

Using the -p option without actually specifying a password forces you to interactively enter the password. This is a good thing because otherwise the password will be visible in the process list.

mysql -u root -p

Reset super user password

If the super user password needs to be reset to a known value because it was forgotten:

/etc/init.d/mysqld stop
safe_mysqld --skip-grant-tables &
mysqladmin -u root flush-privileges password '<secret>'
/etc/init.d/mysqld restart

Allow a host to access all databases

insert into
 host(host,db,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
 values('localhost','%','Y','Y','Y','Y','Y','Y');

(note: instead of '%' it is possible to specify just one single database)

Add a new user

Add a new user that is allowed to log in from a specific host:

create user 'username'@'localhost' identified by 'secret';

Change an existing user's password

This is the preferred form:

alter user 'username'@'localhost' identified by 'secret';

The "alter user" form was not recognized by one of my MySQL 5.7.20 installations, so I had to revert to this older form:

set password for 'username'@'localhost' = 'secret';

Show an existing user's privileges

The following command shows all privileges (aka "grants") that an existing user has. This may be useful to verify the effect of a subsequent grant or revoke command.

show grants for 'username'@'localhost'

Allow existing user to access a new database

Allow an existing user to access a specific database from a specific host

-- This is the preferred form
-- Note: "all privileges" means: all except the "grant" privilege
grant all privileges on <database>.* to 'username'@'localhost';
-- If the database name contains special characters, the db name must appear
-- insided backticks (``); simple quotes or double quotes do not work!
grant all privileges on `drupal614\_herzbube`.* to foo@localhost;
-- An alternative, which I believe also creates the user at the same time
grant all privileges on <database>.* to foo@localhost identified by 'secret';

Remove privileges from an existing user

revoke all privileges on <database>.* from 'username'@'localhost';
revoke all privileges on *.* from 'username'@'localhost';

Delete a user

drop user 'username'@'localhost';

Databases

List all existing databases:

mysql -u <username> -p -e "show databases;"

Create a new database

mysql -u <username> -p -e "create database <database>;"

Drop a database (only if it exists)

mysql -u <username> -p -e "drop database if exists <database>;"

Database backup:

mysqldump --add-drop-table -u <user> -p <database>   >/tmp/<backup-filename>.sql
mysqldump --all-databases -u <user> -p   >/var/backups/mysql/all-databases.sql

(the database "mysql" should always backed up because it contains all the administrative information, such as user privileges)

Restore of a database:

mysql -u <user> -p <database>   </tmp/<backup-filename>.sql

Character sets

Display all the available character sets and collations:

show character set;
show collation;

Display various current character set and collation parameters:

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

Change the default character set (for new tables) and collation of a database:

alter database foobar default character set utf8 collate utf8_general_ci;

Display the character set currently in use on a table (will display many other parameters as well, look for the "CHARSET" parameter):

show create table foobar;

Change the character set and collation of a table:

alter table foobar convert to character set utf8 collate utf8_general_ci;

MyISAM vs. InnoDB

Introduction

MySQL has different storage engines that allow tables to be stored in different formats. The traditional format is MyISAM. A newer format is InnoDB. In newer versions of MySQL the default storage format for tables is InnoDB.

In a long-lived database it is possible to have a mixture of MyISAM and InnoDB tables:

The database is created. An older version of MySQL is installed on the system, which means that all tables are created in MyISAM format.
At some point MySQL is upgraded to a newer version that has InnoDB as the default table format. Existing tables are not automatically converted to InnoDB at this point, they remain in MyISAM format.
The application that uses the database is upgraded and needs to perform changes on the database scheme. New tables are created in InnoDB format.

The information which storage engine is used for a table is preserved when you perform a database backup with mysqldump, so you can't get rid of MyISAM by restoring a database from backup. Note: It may be possible to exclude the storage engine information from backup, or to ignore the information when restoring the database, but I have not researched this.

Pros and Cons

TODO Write something that goes beyond "InnoDB is newer and therefore better"

Conversion from MyISAM to InnoDB

ALTER TABLE is used to convert a MyISAM table to InnoDB, like this:

ALTER TABLE table_name ENGINE=InnoDB;

The following script automates the process of converting all tables of a given database to InnoDB. The script is not terribly sophisticated, so use with caution. Also note that it may not be possible to convert some tables because of limitations of the InnoDB format. One such example used to be the "searchindex" table in a Mediawiki database because that table uses an index of type FULLTEXT, which for a long time was not supported by InnoDB. (InnoDB from MySQL 5.6 onwards now has FULLTEXT indices)

root@pelargir:~# cat myisam2innodb.sh 
#!/bin/bash

MYNAME="$(basename $0)"
TMPFILE_MYSQLOUTPUT=/tmp/$MYNAME.$$.mysqloutput
TMPFILE_AWKOUTPUT=/tmp/$MYNAME.$$.awkoutput
DATABASE_USER=root

if test $# -ne 1; then
  echo "Usage: $MYNAME dbname"
  exit 1
fi

DATABASE_NAME="$1"
if test -z "$DATABASE_NAME"; then
  echo "Database name not specified"
  exit 1
fi

mysql -u $DATABASE_USER -p $DATABASE_NAME -e "show table status where Engine='MyISAM';" >$TMPFILE_MYSQLOUTPUT
if test $? -ne 0; then
  echo "Error while finding MyISAM tables"
  rm $TMPFILE_MYSQLOUTPUT
  exit 1
fi

awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' $TMPFILE_MYSQLOUTPUT >$TMPFILE_AWKOUTPUT
if test $? -ne 0; then
  echo "Error processing MySQL output"
  echo ""
  cat $TMPFILE_MYSQLOUTPUT
  rm $TMPFILE_MYSQLOUTPUT $TMPFILE_AWKOUTPUT
  exit 1
fi

if test ! -s $TMPFILE_AWKOUTPUT; then
  echo "No tables found"
  rm $TMPFILE_MYSQLOUTPUT $TMPFILE_AWKOUTPUT
  exit 0
fi

cat $TMPFILE_AWKOUTPUT
echo ""
read -p "Press enter to continue, press ctrl+c to abort"

mysql -u $DATABASE_USER -p $DATABASE_NAME <$TMPFILE_AWKOUTPUT
EXIT_CODE=$?

rm $TMPFILE_MYSQLOUTPUT
rm $TMPFILE_AWKOUTPUT

exit $EXIT_CODE

Planning conversion from MyISAM to InnoDB

Unless you want to place all table data in the system tablespace (read more about tablespaces in the InnoDB section), make sure that you have enabled the following option in the MySQL configuration. Note that since MySQL 5.6.6 this option is enabled by default.

innodb_file_per_table = 1

Depending on the size of a database, the conversion from MyISAM to InnoDB may take an appreciable amount of time. Example: At the time of conversion the database of this wiki was close to 300 MB in size. The conversion of the entire database took several minutes, but definitely less than 10 minutes.

Obviously, access to the database should be restricted while conversion is going on. In the case of web applications, a simple brute-force method can be to shut down the web server. Some web applications offer a maintenance mode, which would be a better option.

Last but definitely not least: Backup the database before you begin!

InnoDB

Tablespaces

References

innodb_file_per_table system parameter
Pros and cons of file-per-table tablespaces. This answers the question why one would want to store all table data in a single giant file (the system tablespace). Also see this interesting dba.stackexchange.com post.

A table that uses the MyISAM storage engine is stored in a set of table-specific files, which are located in the database folder. For instance, the "objectcache" table in a Mediawiki database is stored like this:

root@pelargir:~# ls -l /var/lib/mysql/mediawiki/objectcache*
-rw-r--r-- 1 root root      8634 Jun 12  2011 /var/lib/mysql/mediawikidb/objectcache.frm
-rw-r--r-- 1 root root 215757448 May 21 04:27 /var/lib/mysql/mediawikidb/objectcache.MYD
-rw-r--r-- 1 root root   5774336 May 21 04:27 /var/lib/mysql/mediawikidb/objectcache.MYI

InnoDB is flexible when it comes to where to store its data. The boolean parameter innodb_file_per_table controls where InnoDB creates new tables.

On (the default since MySQL 5.6.6): The table is stored in a table-specific file in the database folder. It is said that "the table has its own tablespace".
Off (the default before MySQL 5.6.6): The table is stored in the so-called system tablespace. This is a single file that is stored outside of the database folder and that is globally managed by InnoDB within the MySQL instance.

In this example we see how the "objectcache" table in a Mediawiki database is stored like if it has its own tablespace:

root@pelargir:~# l /var/lib/mysql/mediawikidb/objectcache*
-rw-rw---- 1 mysql mysql      8634 Jul  6 15:07 /var/lib/mysql/mediawikidb/objectcache.frm
-rw-rw---- 1 mysql mysql 281018368 Jul  6 15:08 /var/lib/mysql/mediawikidb/objectcache.ibd

In this example we see how the "objectcache" table in a Mediawiki database does not manifest itself in a table-specific file. Instead its data is stored in the "system tablespace", i.e. the ibdata1 file:

root@pelargir:~# l /var/lib/mysql/mediawikidb/objectcache*
-rw-rw---- 1 mysql mysql 8634 Jul  6 11:21 /var/lib/mysql/mediawikidb/objectcache.frm
root@pelargir:~# ls -l /var/lib/mysql/ibdata*
-rw-rw---- 1 mysql mysql 622854144 Jul  6 12:37 /var/lib/mysql/ibdata1

Move table to its own tablespace

References

How to change a table's tablespace

If you have accidentally placed a table in the system tablespace, you can move it to its own tablespace by first enabling innodb_file_per_table (typically in the MySQL configuration) and then running this command:

ALTER TABLE table_name ENGINE=InnoDB;

Note: The system tablespace will not shrink when you do this!

Shrink system tablespace

The procedure is explained in the MySQL documentation.

The procedure boils down to

Backup everything
Tear down the entire server (includes manually removing files within the server data directory!)
Configure a new tablespace
Restart server and re-import backed up data

I have not found the stomach to go through with this, even though I have accidentally bloated the system tablespace to the generous size of almost 600 MB:

root@pelargir:~# ls -l /var/lib/mysql/ibdata*
-rw-rw---- 1 mysql mysql 622854144 Jul  6 15:08 /var/lib/mysql/ibdata1

Run a second MySQL instance on the same machine

Prepare a second set of configuration files

cp /etc/mysql/my.cnf /etc/mysql/my.second.cnf
cp /etc/mysql/debian.cnf /etc/mysql/debian.second.cnf
cp /etc/mysql/debian-start /etc/mysql/debian-start.second

Edit the following lines in my.second.cnf:

[client]
port            = 33306
socket          = /var/run/mysqld.second/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld.second/mysqld.sock

[mysqld]
pid-file        = /var/run/mysqld.second/mysqld.pid
socket          = /var/run/mysqld.second/mysqld.sock
port            = 33306
datadir         = /var/lib/mysql.second

log_error = /var/log/mysql.second/error.log

Edit the following lines in debian-start.second:

MYSQL="/usr/bin/mysql --defaults-file=/etc/mysql/debian.second.cnf"
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.second.cnf"
MYUPGRADE="/usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.second.cnf"
MYCHECK="/usr/bin/mysqlcheck --defaults-file=/etc/mysql/debian.second.cnf"

Edit the following lines in debian.second.cnf:

[client]

port            = 33306
password        = secret
socket          = /var/run/mysqld.second/mysqld.sock

[mysql_upgrade]

port            = 33306
password        = secret
socket          = /var/run/mysqld.second/mysqld.sock

Minimal script to launch the daemon. This is a boiled down version of /etc/init.d/mysql. Stuff that was added was the logging part.

#!/bin/bash

set -e
set -u

cd /
umask 077

LOGFILE="$0.log"
date >>"$LOGFILE"
/usr/bin/mysqld_safe --defaults-file=/etc/mysql/my.second.cnf --skip-kill-mysqld --skip-grant-tables >>$LOGFILE 2>&1 &

# Make sure that the daemon has finished starting up before we proceed
seconds=10
echo -n "sleeping $seconds seconds "
for i in $(seq 1 $seconds); do
    sleep 1
    echo -n "."
done
echo ""

# Run checks to see whether the server crashed. Run repair if necessary.
# This requires that the daemon is running.
/etc/mysql/debian-start.second

Before you can run the daemon, you must provide a set of database files in

/var/lib/mysql.second

I tried to stick in a set of database files from a different machine which gave me some troubles. Here are a few notes how I fixed these:

This error

InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!

can be fixed by adding the following line to my.second.cnf:

innodb_log_file_size    = 48M

This error

160628  2:13:33 [Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin specified. The password will be ignored.

can be fixed by adding the option --skip-grant-tables to the mysqld_safe command line.

Troubleshooting

If you receive the error message "Database failed to execute query <...> 1016: Can't open file: 'sometable.MYI'. (errno: 145)", one of your database tables has become corrupt and needs to be fixed. There are several ways how this can be done:

simply restart the MySQL server (/etc/init.d/mysql restart); when the server starts up it should automatically fix any corrupt tables
manually execute the SQL statement repair table; the server does not need to be restarted, but you have to know which database and table you want to fix
stop the MySQL server, then manually fix the .myi table file in /var/lib/mysql by running the myisamchk command line utility

Important: If you are not sure what you are doing, but especially when you use myisamchk, you should make a backup copy of your database before you attempt to fix the problem!

phpMyAdmin

Deprecation notice for pelargir

This section of this wiki page is currently unmaintained and is likely to become obsolete in the future, because I no longer use phpMyAdmin.

The reason is that I am slowly but steadily migrating from MySQL/MariaDB to PostgreSQL. At the time of writing the only database that is still running on MySQL/MariaDB is Mediawiki. For a long time it has not been possible to migrate Mediawiki to PostgreSQL because Mediawiki did not officially support PostgreSQL. Newer versions of Mediawiki indicate that PostgreSQL is now supported, although test coverage is smaller, but this may be sufficient for me to attempt a migration soon'ish.

Apache configuration

phpMyAdmin brings its own Apache configuration file that defines everything necessary to run phpMyAdmin either under an alias path /phpmyadmin, or under its own virtual host.

I have assigned an Apache vhost to phpMyAdmin that is accessible under https://mysql.herzbube.ch/. These are the configuration details:

# --------------------------------------------------------------------------------
# mysql.herzbube.ch
# --------------------------------------------------------------------------------
<VirtualHost *:80>
  ServerName mysql.herzbube.ch
  Redirect permanent "/" "https://mysql.herzbube.ch/"
</VirtualHost>

# --------------------------------------------------------------------------------
# SSL Host
# --------------------------------------------------------------------------------
<VirtualHost *:443>
  ServerName mysql.herzbube.ch
  ServerAdmin webmaster@herzbube.ch
  ErrorLog ${APACHE_LOG_DIR}/mysql.herzbube.ch/error.log
  CustomLog ${APACHE_LOG_DIR}/mysql.herzbube.ch/access.log combined

  DocumentRoot /usr/share/phpmyadmin
  Alias /robots.txt /var/www/mysql.herzbube.ch/robots.txt

  <Directory /usr/share/phpmyadmin/>
    Require all granted
    php_admin_flag engine on
  </Directory>

  <Directory /var/www/mysql.herzbube.ch/>
    Require all granted
  </Directory>

  Include conf-available/pelargir-herzbube.ch-vhosts-ssl.conf
</VirtualHost>

Web browser configuration

A script named setup.php can be found in

/usr/share/phpmyadmin/scripts

This script allows various configuration options for the Web appearance of phpMyAdmin. The script is protected by basic HTTP authentication configured in

/etc/phpmyadmin/apache.conf

The configuration points to the password file

/etc/phpmyadmin/htpasswd.setup

To change the user database from password file to LDAP, modify /etc/phpmyadmin/apache.conf.

Old configuration:

<IfModule mod_authn_file.c>
  AuthType Basic
  AuthName "phpMyAdmin Setup"
  AuthUserFile /etc/phpmyadmin/htpasswd.setup
</IfModule>
Require valid-user

New configuration:

AuthName "phpMyAdmin Setup"
AuthType Basic
# LDAP connection information is inherited
AuthBasicProvider ldap
Require ldap-user admin

Upgrade from version 4:2.11.8.1-3 to 4:3.3.7-1

The upgrade has some hickups:

phpmyadmin now requires its own database
The Debian package suggests to manage this with dbconfig-common -> that's ok
Package installation requires a "DB upgrade"; the upgrade process then fails because the database does not exist yet -> the fix is to manually "create database phpmyadmin" while logged in as DB admin (root), then to retry the DB upgrade
/etc/phpmyadmin/apache.conf also needs to be manually updated; instead of a single setup script (setup.php), there is now an entire directory (/usr/share/phpmyadmin/setup) that needs protection -> simply copy the LDAP authentication/authorization block that was already in use

MySQL

Contents

Debian packages

Maintenance DB user

Configuration

Configuration files

User-specific configuration

System variables

Stuff to do before going live with a server

Users and privileges

Log in from the command line

Reset super user password

Allow a host to access all databases

Add a new user

Change an existing user's password

Show an existing user's privileges

Allow existing user to access a new database

Remove privileges from an existing user

Delete a user

Databases

Character sets

MyISAM vs. InnoDB

Introduction

Pros and Cons

Conversion from MyISAM to InnoDB

Planning conversion from MyISAM to InnoDB

InnoDB

Tablespaces

Move table to its own tablespace

Shrink system tablespace

Run a second MySQL instance on the same machine

Troubleshooting

phpMyAdmin

Deprecation notice for pelargir

Apache configuration

Web browser configuration

Upgrade from version 4:2.11.8.1-3 to 4:3.3.7-1

Navigation menu

MySQL

Debian packages

Maintenance DB user

Configuration

Configuration files

User-specific configuration

System variables

Stuff to do before going live with a server

Users and privileges

Log in from the command line

Reset super user password

Allow a host to access all databases

Add a new user

Change an existing user's password

Show an existing user's privileges

Allow existing user to access a new database

Remove privileges from an existing user

Delete a user

Databases

Character sets

MyISAM vs. InnoDB

Introduction

Pros and Cons

Conversion from MyISAM to InnoDB

Planning conversion from MyISAM to InnoDB

InnoDB

Tablespaces

Move table to its own tablespace

Shrink system tablespace

Run a second MySQL instance on the same machine

Troubleshooting

phpMyAdmin

Deprecation notice for pelargir

Apache configuration

Web browser configuration

Upgrade from version 4:2.11.8.1-3 to 4:3.3.7-1

Navigation menu

Search