Attachment 78478 Details for Bug 120210 – mysql-upgrade-slotted.xml

mysql-upgrade-slotted.xml

mysql-upgrade-slotted.xml (text/plain), 8.68 KB, created by nm (RETIRED) on 2006-01-29 14:47:15 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: nm (RETIRED)

Created: 2006-01-29 14:47:15 UTC

Size: 8.68 KB

patch

obsolete

><?xml version='1.0' encoding="UTF-8"?>
><!DOCTYPE guide SYSTEM "/dtd/guide.dtd">
>
>
><guide link="/doc/en/mysql-upgrade-slotted.xml">
><title>Slotted MySQL Upgrade and Migration Guide</title>
>
><author title="Author">
> <mail link="vivo@gentoo.org">Francesco Riosa</mail>
></author>
><author title="Editor">
> <mail link="chriswhite@gentoo.org">Chris White</mail>
></author>
><author title="Editor">
> <mail link="jkt@gentoo.org">Jan KundrÃ¡t</mail>
></author>
><author title="Editor">
> <mail link="jackdark@gmail.com">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>
>
>
>
><license/>
>
><version>1.0</version>
><date>2006-01-29</date>
>
><chapter>
><title>Upgrading Older MySQL Versions</title>
><section>
><body>
>
>
>This document covers how to upgrade to the latest available MySQL version.
>There are currently three versions of MySQL supported in Portage:
>
>
><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>
>
>
>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:
>
>
><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>
>
>
>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.
>
>
><pre caption="Backing up the older version and preparing the installation">
># quickpkg dev-db/mysql
># rm -rf /usr/include/mysql /usr/bin/mysql_config
># for tmpfile in /usr/lib/*mysql* ; do
> mv "${tmpfile}" "${tmpfile}.TMP" ;
> ln -s "${tmpfile}.TMP" "${tmpfile}" ;
> done
># emerge -av =dev-db/mysql-5.0.18-r30
></pre>
>
>
>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.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>Copying Data to the New Server</title>
><section>
><body>
>
>
>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 slotted
>MySQL. Notice the <c>-500</c> suffix to the <c>mysqldump</c> program name. This
>indicates it's from the 5.0.x version.
>
>
><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">
># mysqldump-500 \
> --defaults-file=/etc/mysql/my.cnf \
> --user=root \
> <comment>(Replace 'your_password' with your MySQL root password)</comment>
> --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
></pre>
>
>
>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.
>
>
>
>Start the server without networking and user management and run the SQL script:
>
>
><pre caption="Loading the data">
># mv /etc/conf.d/mysql /etc/conf.d/mysql.orig
># cat &lt;&lt;- EOF &gt; /etc/conf.d/mysql
> NOCHECK=1
> DEBUG=3
> mysql_slot_500=(
> "skip-networking"
> "skip-grant-tables"
> )
> EOF
># /etc/init.d/mysql-500 start
># mysql-500 --defaults-file=/etc/mysql-500/my.cnf &lt; BACKUP_MYSQL_4.0.SQL
># /etc/init.d/mysql-500 stop
># mv /etc/conf.d/mysql.orig /etc/conf.d/mysql
></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>
>
>
>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.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>Migrating to the New Server</title>
><section>
><body>
>
>
>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.
>
>
>
>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.
>
>
><pre caption="Using the new server">
># rc-update del mysql default
># rc-update add mysql-500 default
># /etc/init.d/mysql stop
># /etc/init.d/mysql-500 start
></pre>
>
>
>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.
>
>
><pre caption="cleanup first step">
># emerge --unmerge --pretend mysql
># emerge --unmerge =dev-db/mysql-4.0.26
># cd /etc
># mv mysql mysql.$(date +%F_%H-%M)
># rm -rf /usr/lib/*.TMP
># for i in /usr/lib/*mysql* ; do
> [[ -z "$( readlink -f $i )" ]] &amp;&amp; [[ -L $i ]] &amp;&amp; rm $i ;
> done
>
><comment>(emerge app-admin/eselect-mysql if you haven't already)</comment>
># eselect mysql list
># eselect mysql set 1
># eselect mysql show
></pre>
>
></body>
></section>
></chapter>
>
><chapter>
><title>Rebuilding Applications</title>
><section>
><body>
>
>
>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.
>
>
><pre caption="Rebuilding reverse dependancies">
># revdep-rebuild --soname libmysqlclient.so.12 -- -p -v
># revdep-rebuild --soname libmysqlclient.so.12
></pre>
>
><note>
>Depending on the older MySQL version, the <path>libmysqlclient.so</path>
>version may be 10, 12, 14 or 15. 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>
># mysql_fix_privilege_tables-500 \
> --defaults-file=/etc/mysql-500/my.cnf \
> --user=root \
> --password='your_password'
># mysql -uroot -p'your_password' mysql -e "FLUSH PRIVILEGES;"
># 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
># mysql -uroot -p'your_password' mysql &lt; /usr/share/mysql/fill_help_tables.sql
></pre>
>
>
>If you encounter any problems during the upgrade process, please file a <uri
>link="https://bugs.gentoo.org">bug report</uri>.
>
>
></body>
></section>
></chapter>
>
></guide>

Actions: View

Attachments on bug 120210: 78019 | 78040 | 78470 | 78471 | 78478 | 78479 | 78523 | 78525