neysx 06/09/08 10:39:04 Modified: mysql-upgrading.xml Log: #143834 Update from Francesco
Revision Changes Path 1.10 xml/htdocs/doc/en/mysql-upgrading.xml file : http://sources.gentoo.org/viewcvs.py/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml?rev=1.10&view=markup plain: http://sources.gentoo.org/viewcvs.py/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml?rev=1.10&content-type=text/plain diff : http://sources.gentoo.org/viewcvs.py/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml?r1=1.9&r2=1.10 Index: mysql-upgrading.xml =================================================================== RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml,v retrieving revision 1.9 retrieving revision 1.10 diff -u -r1.9 -r1.10 --- mysql-upgrading.xml 4 Sep 2006 09:38:53 -0000 1.9 +++ mysql-upgrading.xml 8 Sep 2006 10:39:04 -0000 1.10 @@ -1,6 +1,6 @@ <?xml version='1.0' encoding="UTF-8"?> <!DOCTYPE guide SYSTEM "/dtd/guide.dtd"> -<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml,v 1.9 2006/09/04 09:38:53 nightmorph Exp $ --> +<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml,v 1.10 2006/09/08 10:39:04 neysx Exp $ --> <guide link="/doc/en/mysql-upgrading.xml"> <title>Upgrade guide to MySQL 4.1.x</title> @@ -13,9 +13,9 @@ </author> <abstract> -The MySQL herd is proud to announce that MySQL 4.1 can now be found in Gentoo's -unstable tree (~arch). As the team hopes that it soon will be able to stabilize -this ebuild, here's an upgrade path for all willing testers. +The MySQL herd is proud to announce that MySQL 5.0 will soon be found in +Gentoo's stable tree. This document describes how to upgrade from MySQL 4.* to +r.0.* </abstract> <!-- The content of this document is licensed under the CC-BY-SA license --> @@ -23,7 +23,43 @@ <license/> <version>1.8</version> -<date>2006-09-04</date> +<date>2006-09-08</date> + +<chapter> +<title>Straight upgrade, suggested for 4.1 => 5.0 migration</title> +<section> +<body> + +<p> +The myisam storage engine in 4.1 version was already mature enough to allow a +direct upgrade to the next major version of MySQL. +</p> + +<pre caption="Straight upgrade"> +# <i>quickpkg dev-db/mysql</i> +# <i>alias MYSQL="mysql --user=root --password=</i><comment>'your_password'</comment><i>"</i> +# <i>DATADIR=$(MYSQL --batch --raw --silent --skip-column-names \</i> + <i>--execute='SHOW variables LIKE "datadir";' \</i> + <i>| sed -e 's|datadir[ \t]||')</i> +# <i>MYSQL --execute="FLUSH TABLES WITH READ LOCK;"</i> +# <i>tar -cjpvf ~/mysql.$(date +%F"T"%H-%M).tar.bz2 \</i> + <i>/etc/mysql/conf.d/mysql /etc/mysql/my.cnf "${DATADIR}"</i> +# <i>MYSQL --execute="UNLOCK TABLES;"</i> +# <i>tar -tjvf ~/mysql.*.tar.bz2</i> +# <i>emerge -av ">mysql-5.0"</i> +# <i>dispatch-conf</i> +# <i>revdep-rebuild</i> +# <i>/etc/init.d/mysql restart</i> +# <i>mysql_upgrade_shell --user=root --password=</i><comment>'your_password'</comment><i> \</i> + <i>--protocol=tcp --datadir="${DATADIR}"</i> +# <i>/etc/init.d/mysql restart</i> +# <i>unset DATADIR</i> +# <i>unalias MYSQL</i> +</pre> + +</body> +</section> +</chapter> <chapter> <title>Upgrading from old versions of MySQL</title> @@ -32,8 +68,8 @@ <p> Users upgrading from an old version (<4.0.24) of MySQL will first have to -install MySQL 4.0.25. If you are already running a current version, you can skip -this section and continue with the next one. +install MySQL 4.0.25. If you are already running a more recent version, you can +skip this section and continue with the <uri link="#backup">next one</uri>. </p> <pre caption="Simple upgrade"> @@ -44,14 +80,14 @@ </section> </chapter> -<chapter> +<chapter id="backup"> <title>Creating a backup of your current data</title> <section> <body> <p> -One of the most important tasks that every database administrator has to perfom -is backing up data. Here we go: +One of the most important tasks that every database administrator has to +perform is backing up data. Here we go: </p> <pre caption="Dump of all databases"> @@ -71,9 +107,9 @@ </pre> <p> -Now a file named <path>BACKUP_MYSQL_4.0.SQL</path> should exist, which later can -be used to recreate your data. The data is described in the MySQL dialect of SQL, -the Structured Query Language. +Now a file named <path>BACKUP_MYSQL_4.0.SQL</path> should exist, which can be +used later to recreate your data. The data is described in the MySQL dialect of +SQL, the Structured Query Language. </p> <p> @@ -105,15 +141,15 @@ <pre caption="Uninstall MySQL"> # <i>/etc/init.d/mysql stop</i> # <i>emerge -C mysql</i> -# <i>tar cjpvf ~/mysql.$(date +%F_%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/</i> +# <i>tar cjpvf ~/mysql.$(date +%F"T"%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/</i> # <i>ls -l ~/mysql.*</i> # <i>rm -rf /var/lib/mysql/ /var/log/mysql</i> </pre> <note> -Now two different backups should exist: The SQL one, which is portable between -various versions of MySQL, and the other one that will allow you to quickly -restore your database. This will be covered later in this doc in more detail. +Now two different backups should exist: the SQL one, which is portable between +various versions of MySQL, and the other one that will allow you to quickly +restore your database. This is covered later in this doc in more detail. </note> <p> @@ -124,7 +160,8 @@ <pre caption="Upgrading the binaries"> # <i>emerge -av ">mysql-4.1"</i> -# <i>dispatch-conf</i> +<comment>(Update your config files, you may also use dispatch-conf)</comment> +# <i>etc-update</i> # <i>revdep-rebuild</i> </pre> @@ -133,7 +170,7 @@ </p> <pre caption="Configure MySQL 4.1 base setup"> -# <i>emerge --config =mysql-4.1.<micro_version></i> +# <i>emerge --config =mysql-4.1.<comment><micro_version></comment></i> # <i>/etc/init.d/mysql start</i> </pre> @@ -142,11 +179,25 @@ </p> <impo> -The default <path>/etc/mysql/my.cnf</path> file sets binary logging +The default <path>/etc/mysql/my.cnf</path> file sets binary logging on (<c>log-bin</c>) by default. This will log every single transaction that -modifies data. If run on a very large database (1GB for example), this could -create extremely large files that take up disk space rather quickly. If you -are low on space, disabling binary logging might be a good idea. +modifies data. If run on a very large database (1GB or more), this could create +extremely large files that take up disk space rather quickly. If you are low on +space, disabling binary logging might be a good idea. +</impo> + +<impo> +The default character set in gentoo mysql 4.1 and above is <c>utf8</c>. If the +data contain <e>non</e>-ASCII characters, you may want to preserve the default +character set of the database replacing all occurrences of <c>utf8</c> with +<c>latin1</c> into the <path>/etc/mysql/my.cnf</path> config file. More +information can be found <uri link="#On_charset_conversion">Charset +conversion</uri> charapter. +</impo> + +<impo> +The administrative <c>mysql</c> database that containins user names, passwords +amongst other things is and <b>must</b> be in encoded in utf8. </impo> <pre caption="Importing the SQL backup"> @@ -164,8 +215,8 @@ </pre> <p> -If you now restart your MySQL daemon and everything went as expected, you will -have a fully working version of 4.1.x! :-) +If you restart your MySQL daemon now and everything goes as expected, you have +a fully working version of 4.1.x. </p> <pre caption="Restart the MySQL instance"> @@ -174,7 +225,7 @@ <p> If you encountered any problems during the upgrade process, please report them -at our <uri link="https://bugs.gentoo.org";>Bugzilla</uri>. +on <uri link="http://bugs.gentoo.org";>Bugzilla</uri>. </p> </body> @@ -190,54 +241,178 @@ If you are not happy with MySQL 4.1, it's possible to go back to MySQL 4.0. </p> -<pre caption="Back to the past"> +<pre caption="Reverting to the previous version"> # <i>/etc/init.d/mysql stop</i> # <i>emerge -C mysql</i> # <i>rm -rf /var/lib/mysql/ /var/log/mysql</i> # <i>emerge --usepkgonly "<mysql-4.1"</i> -# <i>tar -xjpvf mysql.[tag] -C /</i> +<comment>(Replace <timestamp> with the one used when creating the backup.)</comment> +# <i>tar -xjpvf mysql.<timestamp>.tar.bz2 -C /</i> # <i>/etc/init.d/mysql start</i> </pre> -<note> -After downgrading, you <e>may</e> need to run <c>revdep-rebuild</c>. -</note> +<impo> +If packages other than <c>dev-db/mysql</c> have been emerged following this +guide, you need to run <c>revdep-rebuild</c> to ensure that every client is +using the correct mysqlclient shared object. +</impo> </body> </section> </chapter> -<chapter> -<title>Straight upgrade, not supported, dangerous</title> +<chapter id="On_charset_conversion"> +<title>On charset conversion:</title> <section> +<title>Introduction</title> <body> <p> -Under certain conditions it's possible to directly upgrade to the next major -version of MySQL. If you know what you're doing and think that applies to your -case, here's a little trick that makes it possible to directly upgrade to -MySQl 4.1. +This charapter is not intended to be an exhaustive guide on how to do such +conversions, rather a short list of hints on which the reader can elaborate. </p> -<pre caption="Straight upgrade"> -# <i>quickpkg dev-db/mysql</i> -# <i>/etc/init.d/mysql stop</i> -# <i>tar -cjpvf ~/mysql.$(date +%F_%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/</i> -# <i>ls -l ~/mysql.*</i> -# <i>export MYSQL_STRAIGHT_UPGRADE=1</i> -# <i>emerge -av ">mysql-4.1"</i> -# <i>unset MYSQL_STRAIGHT_UPGRADE</i> -# <i>dispatch-conf</i> -# <i>revdep-rebuild</i> -# <i>/etc/init.d/mysql start</i> -# <i>mysql_fix_privilege_tables --defaults-file=/etc/mysql/my.cnf \</i> - <i>-uroot --password=</i><comment>'your_password'</comment><i></i> -# <i>mysql --database=mysql -uroot --password=</i><comment>'your_password'</comment><i> < /tmp/new_pieces.sql</i> -# <i>/etc/init.d/mysql restart</i> # just to be sure +<p> +Converting a database may be a complex task and difficulty increases with data +variancy. Things like serialized object and blobs are one example where it's +difficult to keeps pieces together. +</p> + +</body> +</section> +<section> +<title>Indexes</title> +<body> + +<p> +Every utf-8 char is considered 3 bytes long within an index. Indexes in MySQL +can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that the +limits are measured in bytes, whereas the length of a column is interpreted as +number of characters. +</p> + +<p> +MySQL can also create indexes on parts of a column, this can be of some help. +Below are some examples: +</p> + +<pre caption="Indexing using prefixes"> +$ <i>mysql -uroot -p'<comment>your_password</comment>' test</i> + +mysql> <i>SHOW variables LIKE "version" \G</i> +*************************** 1. row *************************** +Variable_name: version + Value: <comment>5.0.24-log</comment> +1 row in set (0.00 sec) + +mysql> <i>CREATE TABLE t1 (</i> + -> <i>c1 varchar(255) NOT NULL default '',</i> + -> <i>c2 varchar(255) NOT NULL default ''</i> + -> <i>) ENGINE=MyISAM DEFAULT CHARSET=utf8;</i> +Query OK, 0 rows affected (0.01 sec) + +mysql> <i>ALTER TABLE t1</i> + -> <i>ADD INDEX idx1 ( c1 , c2 );</i> +<comment>ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes</comment> + +mysql> <i>ALTER TABLE t1</i> + -> <i>ADD INDEX idx1 ( c1(165) , c2(165) );</i> +Query OK, 0 rows affected (0.01 sec) +Records: 0 Duplicates: 0 Warnings: 0 + +mysql> <i>CREATE TABLE t2 (</i> + -> <i>c1 varchar(255) NOT NULL default '',</i> + -> <i>c2 varchar(255) NOT NULL default ''</i> + -> <i>) ENGINE=MyISAM DEFAULT CHARSET=sjis;</i> +Query OK, 0 rows affected (0.00 sec) + +mysql> <i>ALTER TABLE t2</i> + -> <i>ADD INDEX idx1 ( c1(250) , c2(250) );</i> +Query OK, 0 rows affected (0.03 sec) +Records: 0 Duplicates: 0 Warnings: 0 + +mysql> <i>CREATE TABLE t3 (</i> + -> <i>c1 varchar(255) NOT NULL default '',</i> + -> <i>c2 varchar(255) NOT NULL default ''</i> + -> <i>) ENGINE=MyISAM DEFAULT CHARSET=latin1;</i> +Query OK, 0 rows affected (0.00 sec) + +mysql> <i>ALTER TABLE t3</i> + -> <i>ADD INDEX idx1 ( c1 , c2 );</i> +Query OK, 0 rows affected (0.03 sec) +Records: 0 Duplicates: 0 Warnings: 0 +</pre> + +</body> +</section> +<section> +<title>Environment</title> +<body> + +<p> +Working in a utf-8 environment, with utf-8 editors and tools help too: +</p> + +<pre caption="Shell evironment variables"> +LC_ALL=en_US.UTF-8 +LANG=en_US.UTF-8 +export LC_ALL LANG +</pre> + +<p> +The system must be configured to support the chosen UTF-8 locale. You will find +more information in our <uri link="/doc/en/utf-8.xml">Using UTF-8 with +Gentoo</uri> and <uri link="/doc/en/guide-localization.xml">Localization +Guide</uri> documents. +</p> + +</body> +</section> +<section> +<title>iconv</title> +<body> + +<p> +<c>iconv</c>, provided by <c>sys-libs/glibc</c>, is used to convert text files +from one charset to another. The <c>app-text/recode</c> package can be used as +well. +</p> + +<pre caption="Using iconv"> +<comment>(From latin1 to utf8)</comment> +$ <i>iconv -f ISO-8859-15 -t UTF-8 file1.sql > file2.sql</i> + +<comment>(From Japanese to utf8)</comment> +$ <i>iconv -f ISO2022JP -t UTF-8 file1.sql > file2.sql</i> </pre> <p> -Good luck and if something fails, don't say we didn't warn you! ;-) +<c>iconv</c> can be used to recode a sql dump even if the environment is not +set to utf8. +</p> + +</body> +</section> +<section> +<title>SQL Mangling</title> +<body> + +<p> +It's possible to use the <c>CONVERT()</c> and <c>CAST()</c> MySQL functions to +convert data in your SQL scripts. +</p> + +</body> +</section> +<section> +<title>Apache (webserver)</title> +<body> + +<p> +To use utf-8 with apache, you need to adjust the folowing variables in +<path>httpd.conf</path>: AddDefaultCharset, CharsetDefault, CharsetSourceEnc. +If your source html files aren't encoded in utf-8, they <b>must</b> be +converted with <c>iconv</c> or <c>recode</c>. </p> </body> -- [email protected] mailing list
