MySQL 5 administration

Some random information about MySQL administration.

Installation

• When you finished installation and compilation, on UNIX you must run some setup scripts. In particular, mysql_install_db. This script must be ran as the mysql user, else if you run it as root you will get failures later. Eg do something as:

sudo -u mysql mysql_install_db

• On Gentoo just run emerge --config mysql. Be careful however with hostname problems; if you just changed your hostname, it is advised to reboot before configuring MySQL.

Backup & Restore

The backup can be done as:

mysqldump -u root -p --all-databases > backup.sql

The restore as:

mysql -u root -p < backup.sql

Warning: if you do that, the MySQL user table will change and you will need to restart the server to see the changes (although of course, there is probably a better way than simply restarting).

== Indexes and Foreign Keys (constraints)

• There is no relation between the names of the indexes and of the constraints on a table. The only thing that's needed for a constraint is that an index exists on the same column (maybe also the referenced column). Note that the names of indexes / constraints are actually only useful when dropping these constraints or indexes.

• You can remove a foreign key constraint with the command:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

• The SHOW CREATE TABLE command is extremely useful to list all the indexes and constraints (along with their names) on a given table. It will show duplicated constraints that phpMyAdmin does not list, for example.

• Hibernate creates the constraints / indexes with totally random names like FKCFADB8B0C22A5F64 (it may be actually MySQL who generates such names). PhpMyAdmin, on the contrary, gives more meaningful names to constraints it creates.

The INFORMATION_SCHEMA database

• This database contains a lot of *very useful* information about the structures of the database. Querying this database allows you for example to build a simple script to compare the structure of two databases. Just run the following query:

SELECT information_schema.columns.table_name, information_schema.columns.column_name, is_nullable,
column_key, column_type, referenced_table_name, referenced_column_name
FROM information_schema.columns
LEFT OUTER JOIN key_column_usage ON ( key_column_usage.table_name = information_schema.columns.table_name
AND information_schema.columns.column_name = key_column_usage.column_name AND key_column_usage.table_schema = "shoopz")
WHERE information_schema.columns.table_schema = "shoopz"
ORDER BY table_name, column_name

The results of the query (exported to CVS for example) can be compared with diff.

Using LOAD DATA INFILE

If you don't compile MySQL with the --enable-local-infile option (note: this seems to be the case by default, unless for the binaries provided from MySQL directly), then if you want to use this command you have to explicitly request it at connection time.

• When using the command-line client, add --local-infile=1 to your arguments.

• When using PHP, this can be set with

$mysqli->options(MYSQLI_OPT_LOCAL_INFILE, "1");

and then using real_connect instead of just connect.

• Do not put this option (local-infile=1) in your my.cnf file (in the [client] section)! If you do, the mysql client will start fine, but mysqldump won't be able to start! This is maybe a bug; or maybe this option should be set on a different section than [client].

Hints

• To reset the AUTO_INCREMENT value on a table (generally the ID), use the following SQL command:

ALTER TABLE TableName AUTO_INCREMENT=1;

Configuration

• Configuring MySQL 5 general options

Bugs

• When you have a very big float number stored in MySQL, exporting it via mysqldump, and subsequently rereading it can lead to parsing errors. The bug has been fixed in MySQL 6.0.5. A workaround is to manually edit the SQL file with the big float numbers and replace all occurences with lower values.