MySQL 5 administration

Some random information about MySQL administration.

Setup

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.

Configuration

• Configuring MySQL 5 general options

JDBC Connector

• The current stable Gentoo JDBC connector (dev-java/jdbc-mysql-5.0.8) has a problem when localhost is (first) linked to ::1 (an IPv6 address) in /etc/hosts. It will result in a fatal exception and won't be able to connect to the DB (when using localhost in the connection string, of course). Thus it is preferable to not have localhost associated with ::1 in /etc/hosts (if IPv6 is not used, it is useless anyway).

Backup & Restore

The backup can be done as:

mysqldump -u root -p --all-databases > backup.sql
mysqldump -u root --all-databases | bzip2 > brithombar-mysql-backup-2024-01-08.sql.bz2 # directly with bz2 compression
mysqldump -u root -p net_elvanor_example --tables content > content.sql

The restore as:

mysql -u root -p < backup.sql # you can add --database=name to only execute the SQL statements for this DB

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

Users & Grants

• To create an user:

CREATE USER 'documentation'@'localhost' IDENTIFIED BY 'ghsui53';

• To see all users in the database:

SELECT user FROM mysql.user;

• To check grants (permissions) for a given user:

SHOW GRANTS FOR 'documentation'@'localhost';

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.

• Usually the names of the indexes and the names of the constraints will be equal because Hibernate creates them with the same name by default. But the previous remark is still true; don't panick if they are not equal, it's not important.

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

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

• Sometimes it does not work with DROP FOREIGN KEY and you need to use DROP KEY instead.

• To remove all the foreign keys from a table:

select concat('alter table ', table_schema, '.', table_name,' DROP FOREIGN KEY ', constraint_name, ';') from information_schema.table_constraints where constraint_type='FOREIGN KEY' AND table_schema rlike 'database_name' AND table_name like 'table_name';

• 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). These names depend on the names of the class (including the package of the class) and the properties it refers to. PhpMyAdmin, on the contrary, gives more meaningful names to constraints it creates.

• Usually MySQL will refuse to drop a column if a key exists on that column. To remove it, first drop the foreign key constraint, then drop the index, and finally the column.

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.

Other

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

Using INTO OUTFILE

• The MySQL user must have the FILE privilege.

Maintenance

• The mysqld-bin.00000x files (in the MySQL directory) are binary logs. To purge them issue the following SQL command:

RESET MASTER;

• Warning: if you use replication this may apparently cause problems, another command should be used.

Upgrading major versions

• You should run the tool mysql_upgrade:

mysql_upgrade -u root -p

• You should also dump all databases with mysqldump before upgrading:

mysqldump --all-databases -u root -p > /tmp/mysql.sql

• I've had troubles importing the resulting file (ERROR 2006 (HY000) at line 26698: MySQL server has gone away). However, it was probably due to the max packet size, so increase this in the new my.cnf configuration file before booting the new MySQL server.

Hints

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

ALTER TABLE TableName AUTO_INCREMENT=1;

• To display a boolean (BIT(1) type) in a more friendly way in the command line mysql client, use +0 after the field:

SELECT custom+0 FROM color_palette WHERE name = "tech light";

• To run a command directly from the command line use:

mysql -u root -p "database_name" -e "COMMAND HERE;" -N -B

The -N and -B option can be useful to get raw output.

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.