MySQL

Replication & Other Administration issues

Database Migration

The term "migration" applied to databases denotes the transport of a database from one system to another. There are many reasons that can account for the migratory instinct appearing in a database:

. installation of a new database server
. transfer of a development system (for example, on a local computer) to a production system (on the ISP's computer)
. aMySQL update (for example, from version 3.23 to 4.0)
. a change in database system (for example, from Microsoft SQL Server to MySQL)

Transfer of Databases Between MySQL Systems

Migration between MySQL systems is generally carried out with the backup tools mysqldump and mysql, which we have previously described. If the tables are in MyISAM format and compatible versions of MySQL are running on both computers (say, 3.23n), then the migration can be effected by simply copying the database files. This holds beginning with version 3.23 even if MySQL is running under different operating systems.

The main advantage of direct copying of MyISAM tables as opposed to the use of mysqldump/mysql is, of course, the much greater speed. Note, however, that you must recreate all the indexes with myisamchk if MySQL on the other computer uses a different character set (that is, a different sort order).

MySQL guarantees compatibility of database files only within main versions (such as from 4.0.17 to 4.0.18), and not between major updates (such as from 3.22 to 3.23 or 3.23 to 4.0). In fact, even major updates generally cause few problems

Word of advice

If you carry out the migration with mysqldump/mysql, then you do not necessarily have to create (possibly enormous) files. You can pass the output of mysqldump directly to mysql.

The following command demonstrates the usual way of proceeding. It assumes that the command is executed on the computer with the source database and that the data are transported to a second computer (hostname destinationhost). There the database in question must already exist, and the access privileges must allow access to the source computer.

For space considerations the command is broken over two lines:

root# mysqldump -u root --password=xxx --opt sourcedb | \
mysql -u root --password=yyy -h destinationhost dbname

MySQL Update from Version 3.23 to Version 4.0

Usually, an update to the MySQL server (such as from 4.0.7 to 4.0.8) is done in such a way that the server is stopped, the old version deinstalled, and the new version installed. For the database mysql, before deinstallation a backup with access privileges must be carried out, since this database will be deleted and overwritten.

(Your best course is to rename the mysql database directory mysqlold before the deinstallation.) Like the configuration file for the server, all other database files will be carried over unchanged.

This simple process is possible in updating from 3.23n to 4.0.n. However, there are also some details to keep track of:

. Configuration file:

Some of the configuration options for the MySQL server have new names or were deleted. Make sure that no such options appear in Windows\my.ini or /etc/my.cnf. Otherwise, the server cannot be started. The following options have had their names changed:

Old name	New name
enable-locking	external-locking
myisam_bulk_insert_tree_size	bulk_insert_buffer_size
query_cache_startup_type	query_cache_type
record_buffer	read_buffer_size
record_rnd_buffer	read_rnd_buffer_size
skip-locking	skip-external-locking
sort_buffer	sort_buffer_size
warnings	log-warnings

. mysql database:

In MySQL 4.0, some tables of the mysql database were expanded by additional columns. Under Unix/Linux you can simply update the database mysql restored from an earlier version using the script mysql_fix_privilege_tables. To execute the script, you must give the root password (unless the password is empty). Then you may have to set certain privileges by hand, so that existing programs continue to be executed correctly. (This affects particularly the new privileges Create Temporary Table and Lock Tables, which by default are usually not specified, but which are required by many programs.)

Under Windows, the further use of the mysql database is more complicated: Although the script mysql_fix_privilege_tables is included (directory scripts), its execution is impossible, due to the absence of a suitable shell interpreter. The best approach is to restore the old mysql database under another name (e.g., mysqlold) and then copy the entries into the new mysql tables using INSERT INTO ... SELECT. The following commands show how to proceed for the user table:

USE mysql
DELETE FROM user
INSERT INTO user (Host, User, Password,
    Select_priv, Insert_priv, Update_priv, Delete_priv,
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv,
    Process_priv, File_priv, Grant_priv, References_priv,
    Index_priv, Alter_priv)
SELECT * FROM mysqlold.user

You proceed analogously for the other mysql tables. With these commands, only the old privileges, of course, are copied. The new ones must be granted manually (this is not the case by default). In particular, root should have all privileges! Finally, you must execute FLUSH PRIVILEGES for the changes to take effect.

. If you have been working with ISAM tables, then a transfer to tables of type MyISAM is to be recommended. For conversion, execute ALTER TABLE tblname TYPE=MYISAM or use the Perl script mysql_convert_table_format (only under Unix/Linux). (The script assumes that a connection to the database can be established.)

The ISAM table format (the predecessor to MyISAM) is still supported in MySQL 4.0, but it is considered obsolete (deprecated). By version 5.0 at the latest, ISAM tables will finally cease to be able to be used.

Updating MySQL from Version 4.0 to Version 5.0

In principle, updating is accomplished by deinstalling the old version of the server and installing the new one. Then you must change only the table mysql.user. Version 4.1 provides a new password column in this table, which is supposed to improve security. Under Unix/Linux you simply execute the script mysql_fix_privilege_tables to introduce this column. It is unclear how best to proceed under Windows.

Changing the Database System

For migrating to MySQL from another database system or vice versa there is no universal solution. Almost every database system offers a tool, comparable to mysqldump, that represents the contents of the database as an SQL file. The problem is that the resulting files are seldom precisely compatible (for example, due to different column types or lack of ANSI SQL/92 conformity). With Find and Replace you can solve some of these problems. Under Unix/Linux the tools awk and sed can be helpful.