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

mysql-upgrade-slotted.xml

mysql.xml (text/plain), 7.90 KB, created by Jan Kundrát (RETIRED) on 2006-01-24 17:32:22 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jan Kundrát (RETIRED)

Created: 2006-01-24 17:32:22 UTC

Size: 7.90 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>MySQL, upgrade and switch to slotted guide</title>
>
><author title="Author">
> <mail link="vivo@gentoo.org">Francesco Riosa</mail>
></author>
>
><abstract>
>Here described there is an upgrade path for MySQL databases as painless as
>possible.
></abstract>
>
>
>
><license/>
>
><version>1</version>
><date>2006-01-25</date>
>
><chapter>
><title>Upgrading from old versions of MySQL</title>
><section>
><body>
>
>
>This document cover how to upgrade to the latest version available. There are
>currently three version 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 version presently, but are currently unsupported
>meaning that bug reports have very low priority any may lack functionalities.
>The current document is not guaranteed to work with them.
>
>
><ul>
><li>3.23 ancient</li>
><li>5.1 active development</li>
></ul>
>
><note>
>The present document will use <c>mysql-4.0.26</c> as start point and
><c>mysql-5.0.18-r30</c> as the target version. Replace any of these with your
>own.
></note>
>
></body>
></section>
></chapter>
>
>
><chapter id="install_the_new_version">
><title>Install the new version</title>
><section>
><body>
>
>
>This step require to (re)move some files from the running environment, so the
>first thing to do is a backup of the running database server, still not the
>data. Done that, 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="Backup old package and prepare 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 update the <path>/etc/init.d/mysql</path> startup script with
><c>etc-update</c> or <c>dispatch-conf</c>.
>
>
></body>
></section>
></chapter>
>
><chapter>
><title>Copy the data to the newly installed server</title>
><section>
><body>
>
>
>Lets go to to dump the data, this will be imported in the new version of MySQL.
>We are going to use mysqldump from the slotted MySQL. Notice the
><c>-500</c> suffix to the mysqldump program name, it's from the 5.0.x
>version.
>
>
><pre caption="Dump of all databases">
># mysqldump-500 \
> --defaults-file=/etc/mysql/my.cnf
> -uroot \
> --password=<comment>'your_password'</comment> \
> -hlocalhost \
> --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>
>
>
>Now a file named <path>BACKUP_MYSQL_4.0.SQL</path> exist 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="Load data">
># mv /etc/conf.d/mysql /etc/conf.d/mysql.orig
># echo '
> NOCHECK=1
> DEBUG=3
> mysql_slot_500=(
> "skip-networking"
> "skip-grant-tables"
> )
> ' > /etc/conf.d/mysql
># /etc/init.d/mysql-500 start
># mysql-500 --defaults-file=/etc/mysql-500/my.cnf &lt; BACKUP_MYSQL_4.0.SQL
># mv /etc/conf.d/mysql.orig /etc/conf.d/mysql
># /etc/init.d/mysql-500 stop
></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 case this is done simply "piping" it like
><c>iconv -f ISO_8859-1 -t UTF8 BACKUP_MYSQL_4.0.SQL | mysql-500
>--defaults-file=/etc/mysql-500/my.cnf</c>. Also manual tweaking of the SQL file
>could be needed, depending on the structure and the data contained in it.
></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 will not be covered here.
></note>
>
></body>
></section>
></chapter>
>
>
><chapter>
><title>Switch 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 every application has configuration
>settings to chose which port or socket to use to connect 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. Pay
>attention that most of them 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 testing setting, stop the
>old server and start the new one. Also make it the server that will start at
>next reboot.
>
>
><pre caption="Use 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, the ones moved in <uri
>link="#install_the_new_version">Backup old package and prepare
>installation</uri>. This is an intended behavior, and avoid breaking application
>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
># rm -rf /usr/lib/*.TMP
># for i in /usr/lib/*mysql* ; do
> readlink -f $i || [[ -L $i ]] &amp;&amp; rm $i
> done
># eselect mysql list
># eselect mysql set 1
># eselect mysql show
></pre>
>
></body>
></section>
></chapter>
>
>
><chapter>
><title>Rebuild applications</title>
><section>
><body>
>
>
>After you got rid of 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 linking 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 which really is the old version <path>libmysqlclient.so</path>
>number may be 10, 12, 14 or 15, chose the one of the <e>old</e> package.
></note>
>
></body>
></section>
></chapter>
>
>
><chapter>
><title>Final touches</title>
><section>
><body>
>
><pre caption="Upgrading user database">
># mysql_fix_privilege_tables-500 \
> --defaults-file=/etc/mysql-500/my.cnf \
> --user=root \
> --password=<comment>'your_password'</comment>
># mysql -uroot -p<comment>'your_password'</comment> mysql -e "FLUSH PRIVILEGES;"
># for tbl in $( mysql --silent -uroot -p<comment>'your_password'</comment> -e 'USE mysql ; SHOW TABLES LIKE "help%";' )
> do
> mysql -uroot -p<comment>'your_password'</comment> -e "use mysql ; TRUNCATE TABLE ${tbl};"
> done
># mysql -uroot -p<comment>'your_password'</comment> mysql &lt; /usr/share/mysql/fill_help_tables.sql
></pre>
>
>
>If you encountered any problems during the upgrade process, please report them
>at 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