jkt 06/01/30 15:56:12 Added: xml/htdocs/doc/en mysql-upgrade-slotted.xml Log: #120210, new howto about upgrading to the slotted MySQL
Revision Changes Path 1.1 xml/htdocs/doc/en/mysql-upgrade-slotted.xml file : http://www.gentoo.org/cgi-bin/viewcvs.cgi/xml/htdocs/doc/en/mysql-upgrade-slotted.xml?rev=1.1&content-type=text/x-cvsweb-markup&cvsroot=gentoo plain: http://www.gentoo.org/cgi-bin/viewcvs.cgi/xml/htdocs/doc/en/mysql-upgrade-slotted.xml?rev=1.1&content-type=text/plain&cvsroot=gentoo Index: mysql-upgrade-slotted.xml =================================================================== <?xml version='1.0' encoding="UTF-8"?> <!DOCTYPE guide SYSTEM "/dtd/guide.dtd"> <!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/mysql-upgrade-slotted.xml,v 1.1 2006/01/30 15:56:12 jkt Exp $ --> <guide link="/doc/en/mysql-upgrade-slotted.xml"> <title>Slotted MySQL Upgrade and Migration Guide</title> <author title="Author"> <mail link="[EMAIL PROTECTED]">Francesco Riosa</mail> </author> <author title="Editor"> <mail link="[EMAIL PROTECTED]">Chris White</mail> </author> <author title="Editor"> <mail link="[EMAIL PROTECTED]">Jan Kundrát</mail> </author> <author title="Editor"> <mail link="[EMAIL PROTECTED]">Joshua Saddler</mail> </author> <abstract> This document describes the upgrade path to the new slotted MySQL. It strives to make the upgrade as painless and friendly as possible. </abstract> <!-- The content of this document is licensed under the CC-BY-SA license --> <!-- See http://creativecommons.org/licenses/by-sa/2.5 --> <license/> <version>1.0</version> <date>2006-01-29</date> <chapter> <title>Upgrading Older MySQL Versions</title> <section> <body> <p> This document covers how to upgrade to the latest available MySQL version. There are currently three versions of MySQL supported in Portage: </p> <ul> <li> 5.0 follows upstream releases in the stable tree and actively maintained </li> <li> 4.1 follows upstream releases by implementing major bugfixes and security fixes, but no new features are added </li> <li>4.0 contains security fixes only</li> </ul> <p> There are two additional versions present, but are currently unsupported. This means that they may lack certain functionalities, and bug reports have very low priority. This document is not guaranteed to work with the following versions: </p> <ul> <li>3.23 has been deprecated</li> <li>5.1 is currently under heavy development</li> </ul> <note> <c>mysql-4.0.26</c> will be used as the starting point, and <c>mysql-5.0.18-r30</c> as the target version. Replace any of these versions with your own. </note> </body> </section> </chapter> <chapter id="install_the_new_version"> <title>Installing the New Version</title> <section> <body> <p> This step will require (re)moving some files from the running environment, so the first thing to do is backup the running database <e>server</e>, not the data. Once this is done, it's possible to remove the conflicting files with the slotted MySQL, and install the new version side by side with the current one. </p> <pre caption="Backing up the older version and preparing the installation"> # <i>quickpkg dev-db/mysql</i> # <i>rm -rf /usr/include/mysql /usr/bin/mysql_config</i> # <i>for tmpfile in /usr/lib/*mysql* do mv "${tmpfile}" "${tmpfile}.TMP" ln -s "${tmpfile}.TMP" "${tmpfile}" done</i> # <i>emerge -av =dev-db/mysql-5.0.18-r30</i> </pre> <p> Be sure to run <c>etc-update</c> or <c>dispatch-conf</c> in order to update the <path>/etc/init.d/mysql</path> startup script. </p> </body> </section> </chapter> <chapter> <title>Copying Data to the New Server</title> <section> <body> <p> Now we'll go ahead and dump the data. This will be imported into the new version of MySQL. <c>mysqldump</c> will be used from the <b>slotted</b> MySQL. Notice the <c>-500</c> suffix to the <c>mysqldump</c> program name. This indicates it's from the <b>5.0</b>.x version. </p> <impo> If you are upgrading between version revisions (ie. <c>dev-db/mysql-5.0.18</c> to <c>dev-db/mysql-5.0.18-r30</c>), you can simply stop the database, move from one data directry to the other, and restart the server. This allows you to skip this section entirely. </impo> <pre caption="Dump of all databases"> # <i>mysqldump-500 \ --defaults-file=/etc/mysql/my.cnf \ --user=root \</i> <comment>(Replace 'your_password' with your MySQL root password)</comment> <i>--password='your_password' \ --host=localhost \ --all-databases \ --opt \ --allow-keywords \ --flush-logs \ --hex-blob \ --master-data \ --max_allowed_packet=16M \ --quote-names \ --result-file=BACKUP_MYSQL_4.0.SQL</i> </pre> <p> A file named <path>BACKUP_MYSQL_4.0.SQL</path> is created, which can be used to recreate your data. The data is described in the MySQL dialect of SQL, the Structured Query Language. </p> <p> Start the server without networking and user management and run the SQL script: </p> <pre caption="Loading the data"> # <i>mv /etc/conf.d/mysql /etc/conf.d/mysql.orig</i> # <i>cat <<- EOF > /etc/conf.d/mysql NOCHECK=1 DEBUG=3 mysql_slot_500=( "skip-networking" "skip-grant-tables" ) EOF</i> # <i>/etc/init.d/mysql-500 start</i> # <i>mysql-500 --defaults-file=/etc/mysql-500/my.cnf < BACKUP_MYSQL_4.0.SQL</i> # <i>/etc/init.d/mysql-500 stop</i> # <i>mv /etc/conf.d/mysql.orig /etc/conf.d/mysql</i> </pre> <note> To convert the data to UTF-8 during this step, you <e>must</e> remove <c>--hex-blob</c> from the <c>mysqldump</c> option, then filter the data through a converter like <c>iconv</c>. In most cases this is done by simply "piping" it like so: <c>iconv -f ISO_8859-1 -t UTF8 BACKUP_MYSQL_4.0.SQL | mysql-500 --defaults-file=/etc/mysql-500/my.cnf</c>. Manual adjustments of the SQL file could be required, depending on the structure and the data contained within. </note> <p> If there are applications still <e>writing</e> to the previous database, it's possible to setup a "Replication" relationship between the two databases. However, this document does not cover that procedure. </p> </body> </section> </chapter> <chapter> <title>Migrating to the New Server</title> <section> <body> <p> Please test applications against the newly installed server to make sure they work with it. Quite often every application has configuration settings to choose which port or socket to use for connecting to the database server. Simply start the server on an alternate port (for example 3307) and tell your application (or a test copy of it) to connect with those parameters. Please note that most applications will try to use the parameters found in the <c>[client]</c> section of <path>/etc/mysql/my.cnf</path> config file. </p> <p> When you're satisfied with the results, remove every test setting, stop the old server and start the new one. Also, be sure to add <path>/etc/init.d/mysql</path> to the default runlevel, ensuring it starts at the next reboot. </p> <pre caption="Using the new server"> # <i>rc-update del mysql default</i> # <i>rc-update add mysql-500 default</i> # <i>/etc/init.d/mysql stop</i> # <i>/etc/init.d/mysql-500 start</i> </pre> <p> Next, unmerge the old version and make the new one the default. The unmerge command will be unable to remove some files, such as the ones moved in <uri link="#install_the_new_version">Code Listing 2.1</uri>. This is an intended behavior, and is meant to avoid breaking applications linked to the old MySQL version. </p> <pre caption="Cleanup the old version"> # <i>emerge --unmerge --pretend mysql</i> # <i>emerge --unmerge =dev-db/mysql-4.0.26</i> # <i>cd /etc</i> # <i>mv mysql mysql.$(date +%F_%H-%M)</i> # <i>rm -rf /usr/lib/*.TMP</i> # <i>for i in /usr/lib/*mysql* do [[ -z "$( readlink -f $i )" ]] && [[ -L $i ]] && rm $i done</i> <comment>(emerge app-admin/eselect-mysql if you haven't already)</comment> # <i>eselect mysql list</i> # <i>eselect mysql set 1</i> # <i>eselect mysql show</i> </pre> </body> </section> </chapter> <chapter> <title>Rebuilding Applications</title> <section> <body> <p> After you remove your old MySQL installation, you can install the new version. Note that <c>revdep-rebuild</c> from <c>app-portage/gentoolkit</c> is necessary for rebuilding packages linked against MySQL. </p> <pre caption="Rebuilding reverse dependancies"> # <i>revdep-rebuild --soname libmysqlclient.so.12 -- -p -v</i> # <i>revdep-rebuild --soname libmysqlclient.so.12</i> </pre> <note> Depending on the older MySQL version, the <path>libmysqlclient.so</path> version may be <c>10</c>, <c>12</c>, <c>14</c> or <c>15</c>. Please choose the correct version of the <e>old</e> package. </note> </body> </section> </chapter> <chapter> <title>Final Touches</title> <section> <body> <pre caption="Upgrading the user database"> <comment>(Replace all instances of 'your_password' with your MySQL root password)</comment> # <i>mysql_fix_privilege_tables-500 \ --defaults-file=/etc/mysql-500/my.cnf \ --user=root \ --password='your_password'</i> # <i>mysql -uroot -p'your_password' mysql -e "FLUSH PRIVILEGES;"</i> # <i>for tbl in $( mysql --silent -uroot -p'your_password' -e 'USE mysql ; SHOW TABLES LIKE "help%";' ) do mysql -uroot -p'your_password' -e "use mysql ; TRUNCATE TABLE ${tbl};" done</i> # <i>mysql -uroot -p'your_password' mysql < /usr/share/mysql/fill_help_tables.sql</i> </pre> <p> If you encounter any problems during the upgrade process, please file a <uri link="https://bugs.gentoo.org";>bug report</uri>. </p> </body> </section> </chapter> </guide> -- [email protected] mailing list
