Upgrade guide to MySQL 4.1.x

1.8 -2006-09-04 +2006-09-08 + + +Straight upgrade, suggested for 4.1 => 5.0 migration +

+ + +

+The myisam storage engine in 4.1 version was already mature enough to allow a +direct upgrade to the next major version of MySQL. +

+ +

+# quickpkg dev-db/mysql
+# alias MYSQL="mysql --user=root --password='your_password'"
+# DATADIR=$(MYSQL --batch --raw --silent --skip-column-names \
+     --execute='SHOW variables LIKE "datadir";' \
+     | sed -e 's|datadir[ \t]||')
+# MYSQL --execute="FLUSH TABLES WITH READ LOCK;"
+# tar -cjpvf ~/mysql.$(date +%F"T"%H-%M).tar.bz2 \
+     /etc/mysql/conf.d/mysql /etc/mysql/my.cnf "${DATADIR}"
+# MYSQL --execute="UNLOCK TABLES;"
+# tar -tjvf ~/mysql.*.tar.bz2
+# emerge -av ">mysql-5.0"
+# dispatch-conf
+# revdep-rebuild
+# /etc/init.d/mysql restart
+# mysql_upgrade_shell --user=root --password='your_password' \
+     --protocol=tcp --datadir="${DATADIR}"
+# /etc/init.d/mysql restart
+# unset DATADIR
+# unalias MYSQL
+

+ + +

+ Upgrading from old versions of MySQL @@ -32,8 +68,8 @@

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 next one.

@@ -44,14 +80,14 @@
 
 
 
-
+
 Creating a backup of your current data
 
 
 
 
-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:
 
 
 @@ -71,9 +107,9 @@
 
 
 
-Now a file named BACKUP_MYSQL_4.0.SQL 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 BACKUP_MYSQL_4.0.SQL 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.
 
 
 
@@ -105,15 +141,15 @@
 
 # /etc/init.d/mysql stop
 # emerge -C mysql
-# tar cjpvf ~/mysql.$(date +%F_%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/
+# tar cjpvf ~/mysql.$(date +%F"T"%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/
 # ls -l ~/mysql.*
 # rm -rf /var/lib/mysql/ /var/log/mysql
 
 
 
-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.
 
 
 
@@ -124,7 +160,8 @@
 
 
 # emerge -av ">mysql-4.1"
-# dispatch-conf
+(Update your config files, you may also use dispatch-conf)
+# etc-update
 # revdep-rebuild
 
 
@@ -133,7 +170,7 @@
 
 
 -# emerge --config =mysql-4.1.<micro_version>
+# emerge --config =mysql-4.1.<micro_version>
 # /etc/init.d/mysql start
 
 
@@ -142,11 +179,25 @@
 
 
 
-The default /etc/mysql/my.cnf file sets binary logging
+The default /etc/mysql/my.cnf file sets binary logging on
 (log-bin) 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.
+
+
+
+The default character set in gentoo mysql 4.1 and above is utf8. If the
+data contain non-ASCII characters, you may want to preserve the default
+character set of the database replacing all occurrences of utf8 with
+latin1 into the /etc/mysql/my.cnf config file. More
+information can be found Charset
+conversion charapter.
+
+
+
+The administrative mysql database that containins user names, passwords
+amongst other things is and must be in encoded in utf8.
 
 
 @@ -164,8 +215,8 @@
 
 
 
-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.
 
 
 @@ -174,7 +225,7 @@
 
 
 If you encountered any problems during the upgrade process, please report them
-at our Bugzilla.
+on Bugzilla.
 
 
 
@@ -190,54 +241,178 @@
 If you are not happy with MySQL 4.1, it's possible to go back to MySQL 4.0.
 
 
-+ # /etc/init.d/mysql stop
 # emerge -C mysql
 # rm -rf /var/lib/mysql/ /var/log/mysql
 # emerge --usepkgonly "<mysql-4.1"
-# tar -xjpvf mysql.[tag] -C /
+(Replace <timestamp> with the one used when creating the backup.)
+# tar -xjpvf mysql.<timestamp>.tar.bz2 -C /
 # /etc/init.d/mysql start
 
 
-
-After downgrading, you may need to run revdep-rebuild.
-
+
+If packages other than dev-db/mysql have been emerged following this
+guide, you need to run revdep-rebuild to ensure that every client is
+using the correct mysqlclient shared object.
+
 
 
 
 
 
-
-Straight upgrade, not supported, dangerous
+
+On charset conversion:
 
+Introduction
 
 
 
-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.
 
 
--# quickpkg dev-db/mysql
-# /etc/init.d/mysql stop
-# tar -cjpvf ~/mysql.$(date +%F_%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/
-# ls -l ~/mysql.*
-# export MYSQL_STRAIGHT_UPGRADE=1
-# emerge -av ">mysql-4.1"
-# unset MYSQL_STRAIGHT_UPGRADE
-# dispatch-conf
-# revdep-rebuild
-# /etc/init.d/mysql start
-# mysql_fix_privilege_tables --defaults-file=/etc/mysql/my.cnf \
-     -uroot --password='your_password'
-# mysql --database=mysql -uroot --password='your_password' < /tmp/new_pieces.sql
-# /etc/init.d/mysql restart # just to be sure
+
+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.
+
+
+
+
+
+Indexes
+
+
+
+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.
+
+
+
+MySQL can also create indexes on parts of a column, this can be of some help.
+Below are some examples:
+
+
++$ mysql -uroot -p'your_password' test
+
+mysql> SHOW variables LIKE "version" \G
+*************************** 1. row ***************************
+Variable_name: version
+    Value: 5.0.24-log
+1 row in set (0.00 sec)
+
+mysql> CREATE TABLE t1 (
+  ->   c1 varchar(255) NOT NULL default '',
+  ->   c2 varchar(255) NOT NULL default ''
+  ->   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+Query OK, 0 rows affected (0.01 sec)
+
+mysql> ALTER TABLE t1
+  ->   ADD INDEX idx1 ( c1 , c2 );
+ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
+
+mysql> ALTER TABLE t1
+  ->   ADD INDEX idx1 ( c1(165) , c2(165) );
+Query OK, 0 rows affected (0.01 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+
+mysql> CREATE TABLE t2 (
+  ->   c1 varchar(255) NOT NULL default '',
+  ->   c2 varchar(255) NOT NULL default ''
+  ->   ) ENGINE=MyISAM DEFAULT CHARSET=sjis;
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> ALTER TABLE t2
+  ->   ADD INDEX idx1 ( c1(250) , c2(250) );
+Query OK, 0 rows affected (0.03 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+
+mysql> CREATE TABLE t3 (
+  ->   c1 varchar(255) NOT NULL default '',
+  ->   c2 varchar(255) NOT NULL default ''
+  ->   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> ALTER TABLE t3
+  ->   ADD INDEX idx1 ( c1 , c2 );
+Query OK, 0 rows affected (0.03 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+
+
+
+
+
+Environment
+
+
+
+Working in a utf-8 environment, with utf-8 editors and tools help too:
+
+
++LC_ALL=en_US.UTF-8
+LANG=en_US.UTF-8
+export LC_ALL LANG
+
+
+
+The system must be configured to support the chosen UTF-8 locale. You will find
+more information in our Using UTF-8 with
+Gentoo and Localization
+Guide documents.
+
+
+
+
+
+iconv
+
+
+
+iconv, provided by sys-libs/glibc, is used to convert text files
+from one charset to another. The app-text/recode package can be used as
+well.
+
+
++(From latin1 to utf8)
+$ iconv -f ISO-8859-15 -t UTF-8 file1.sql > file2.sql
+
+(From Japanese to utf8)
+$ iconv -f ISO2022JP -t UTF-8 file1.sql > file2.sql
 
 
 
-Good luck and if something fails, don't say we didn't warn you! ;-)
+iconv can be used to recode a sql dump even if the environment is not
+set to utf8.
+
+
+
+
+
+SQL Mangling
+
+
+
+It's possible to use the CONVERT() and CAST() MySQL functions to
+convert data in your SQL scripts.
+
+
+
+
+
+Apache (webserver)
+
+
+
+To use utf-8 with apache, you need to adjust the folowing variables in
+httpd.conf: AddDefaultCharset, CharsetDefault, CharsetSourceEnc.
+If your source html files aren't encoded in utf-8, they must be
+converted with iconv or recode.
 
 
 



-- 
gentoo-doc-cvs@gentoo.org mailing list