Attachment 78470 Details for Bug 120210 – mysql-slotted-edited.xml

mysql-slotted-edited.xml

mysql-slotted-edited.xml (text/plain), 8.68 KB, created by Chris White (RETIRED) on 2006-01-29 13:05:38 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Chris White (RETIRED)

Created: 2006-01-29 13:05:38 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"></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-25</date>
>
><chapter>
><title>Upgrading Older MySQL Versions</title>
><section>
><body>
>
>
>This document covers how to upgrade to the latest avaliable MySQL version.
>There are currently three versions of MySQL supported in portage:
>
>
><ul>
> <li>5.0 follow upstream schedule (stable tree, active bugfixing)</li>
> <li>4.1 follow upstream schedule (only changes to a large userbase)</li>
> <li>4.0 security fix only</li>
></ul>
>
>
>There are two additional versions present, but are currently unsupported.
>This means that bug reports have very low priority any may lack functionalities.
>This document is not guaranteed to work with them.
>
>
><ul>
><li>3.23 deprecated</li>
><li>5.1 active development</li>
></ul>
>
><note>
><c>mysql-4.0.26</c> will be used as start 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 server, 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 The 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 datadir 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>
>
><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 will not be cover that procedure.
></note>
>
></body>
></section>
></chapter>
>
>
><chapter>
><title>Migrating To The New Server</title>
><section>
><body>
>
>
>If you need to be sure that applications are working with the newly installed
>server, please test them against it. Quite often every application has 
>configuration settings to chose 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>
>
>
>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">Backing up the older version and preparing the
>installation</uri>. This is an intended behavior, and 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 now install the new
>version. Note that <c>revdep-rebuild</c> from the <c>app-portage/gentoolkit</c>
>is necessary for rebuilding packages linked against MySQL.
>
>
><pre caption="Reverse dependancies rebuild">
># 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 version of the <e>old</e>
>package.
></note>
>
></body>
></section>
></chapter>
>
>
><chapter>
><title>Final Touches</title>
><section>
><body>
>
><pre caption="Upgrading 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 report them
>to our <uri link="https://bugs.gentoo.org">Bugzilla</uri>.
>
>
></body>
></section>
></chapter>
>
></guide>

Actions: View

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