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

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