Attachment 78019 Details for Bug 120210 – migrate-to-slot.xml

migrate-to-slot.xml

mysql-migrate-to-slot.xml (text/plain), 8.02 KB, created by Francesco R. (RETIRED) on 2006-01-24 11:55:49 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Francesco R. (RETIRED)

Created: 2006-01-24 11:55:49 UTC

Size: 8.02 KB

patch

obsolete

><?xml version='1.0' encoding="UTF-8"?>
><!DOCTYPE guide SYSTEM "/dtd/guide.dtd">
>
>
><guide link="/doc/en/mysql-upgrading.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.6</version>
><date>2006-01-04</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 mysql-4.0.26 as start point
>	and mysql-5.0.18-r30 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 /etc/init.d/mysql startup script with
>etc-update or dispatch-conf.
>
>
></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 "-500" 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 MUST remove "--hex-blob"
>	from the mysqldump option, then filter the data through a converter like
>	"iconv". 
>	In most case this is done simply "piping" it like this: 
>	"iconv -f ISO_8859-1 -t UTF8 BACKUP_MYSQL_4.0.SQL
>	| mysql-500 --defaults-file=/etc/mysql-500/my.cnf" 
>	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 <comment>writing</comment> 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
>"[client]" section of /etc/mysql/my.cnf 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> 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 libmysqlclient.so number may
>be 10, 12, 14 or 15, chose the one of the <comment>old</comment> 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