Mac OS X Server version 10.5 Leopard preinstalls MySQL 5.0.45. Users upgrading from Mac OS X Server 10.3 or 10.4 to version 10.5 will need to migrate their MySQL data to ensure compatibility with the new version of MySQL.
This article outlines the steps needed to perform migration of standard MySQL databases. Users with more complex database designs may wish to refer to the documentation provided at www.mysql.org for additional documentation to help with migrating their database to MySQL 5.0, or refer to the links listed at the end of this article.
Prior to upgrading your Mac OS X Server system, administrators should create a backup of their existing MySQL database.
This article describes how to migrate the default versions of MySQL included in Mac OS X Server 10.3.9 and 10.4.10 or 10.4.11. Mac OS X Server version 10.3.9 preinstalls version 4.0.18 of MySQL. Server version 10.4.10 preinstalls MySQL version 4.1.22. If you are unsure of the version of MySQL installed on your system, enter the following command in Terminal:
mysql --version
If you are using a version of MySQL other than those listed above, you may require additional steps not outlined below. For additional information about special steps needed by your version MySQL, refer to the links listed at the end of this article.
To migrate your MySQL databases from one computer to another, you can use the mysqldump command from a Terminal window. The mysqldump command has several forms depending on the scope of data to be backed up: Individual tables, single databases or the entire set of databases on the server.
To back up individual tables, type:
mysqldump database tb1 [tb2 tb3...] > backup-file.sql
(where database is the name of the database containing the listed tables and tb1, tb2, and tb3 represent table names)
To back up one or more databases, type:
mysqldump --databases db1 [db2 db3...] > backup-file.sql
To back up all databases on the system, type:
mysqldump --all-databases > backup-file.sql
To back up tables or databases that require root access (for example, grant tables or other restricted data), run mysqldump with the --user=root & -p options:
mysqldump --user=root -p --all-databases > backup-file.sql
The -p option will cause mysqldump to prompt for the MySQL "root" password before proceeding.
The resulting backup files from this command should be stored in a safe location that will not be modified during the Mac OS X Server upgrade process.
Additional instructions for database backup and restoration can be found in the MySQL documentation at www.mysql.org or the links listed at the end of this article.
Once the Mac OS X Server upgrade (or installation) is complete, make sure that the MySQL service is active before attempting to import backup data. The MySQL service can be activated using Server Admin or the serveradmin command.
serveradmin start mysql
Importing MySQL backup data into MySQL 5.0
To import database backups, use the mysql command line tool in Terminal. To perform the import, type:
mysql < backup-file.sql
To import into databases that require privileged access, run mysql with the --user=root & -p options:
mysql --user=root -p < backup-file.sql
The -p option will cause mysql to prompt for the MySQL "root" password before proceeding.
Additional instructions for database backup and restore can be found in the MySQL documentation at www.mysql.org or the links listed below.
For users that may have performed a system upgrade to Mac OS X Server 10.5 without previously saving their MySQL database, it is possible, in some circumstances, to perform a direct upgrade of the database version to MySQL 5.0 using the mysql_upgrade utility.
Before attempting to perform a direct upgrade, you should create a mysqldump backup of your database prior to the using mysql_upgrade to ensure that the original database is not affected by conversion failure or other interruption. It is also recommended that you consult the documentation provided at www.mysql.org about issues that may impact database upgrades.
To perform a direct upgrade of the MySQL database to version 5.0, type the following in Terminal:
/usr/bin/mysql_upgrade
Note: Due to an issue in version 5.0.45 of the mysql_upgrade tool, the full path to the tool must be used when executing mysql_upgrade.
To perform the upgrade for databases that may require privileged access, type:
/usr/bin/mysql_upgrade --user=root --password
Once the conversion is finished, the MySQL data will be compatible with MySQL 5.0.
For comprehensive information regarding backup and restore of MySQL data, visit the www.mysql.org web site. The following articles will also provide information about optional steps that may be needed to address your specific database configuration:
Additional information about MySQL and Mac OS X Server can be found in the Mac OS X Server Upgrading and Migrating for Version 10.5 guide.
Important: Mention of third-party websites and products is for informational purposes only and constitutes neither an endorsement nor a recommendation. Apple assumes no responsibility with regard to the selection, performance or use of information or products found at third-party websites. Apple provides this only as a convenience to our users. Apple has not tested the information found on these sites and makes no representations regarding its accuracy or reliability. There are risks inherent in the use of any information or products found on the Internet, and Apple assumes no responsibility in this regard. Please understand that a third-party site is independent from Apple and that Apple has no control over the content on that website. Please contact the vendor for additional information.