| 1 |
neysx 06/09/08 10:39:04 |
| 2 |
|
| 3 |
Modified: mysql-upgrading.xml |
| 4 |
Log: |
| 5 |
#143834 Update from Francesco |
| 6 |
|
| 7 |
Revision Changes Path |
| 8 |
1.10 xml/htdocs/doc/en/mysql-upgrading.xml |
| 9 |
|
| 10 |
file : http://sources.gentoo.org/viewcvs.py/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml?rev=1.10&view=markup |
| 11 |
plain: http://sources.gentoo.org/viewcvs.py/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml?rev=1.10&content-type=text/plain |
| 12 |
diff : http://sources.gentoo.org/viewcvs.py/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml?r1=1.9&r2=1.10 |
| 13 |
|
| 14 |
Index: mysql-upgrading.xml |
| 15 |
=================================================================== |
| 16 |
RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml,v |
| 17 |
retrieving revision 1.9 |
| 18 |
retrieving revision 1.10 |
| 19 |
diff -u -r1.9 -r1.10 |
| 20 |
--- mysql-upgrading.xml 4 Sep 2006 09:38:53 -0000 1.9 |
| 21 |
+++ mysql-upgrading.xml 8 Sep 2006 10:39:04 -0000 1.10 |
| 22 |
@@ -1,6 +1,6 @@ |
| 23 |
<?xml version='1.0' encoding="UTF-8"?> |
| 24 |
<!DOCTYPE guide SYSTEM "/dtd/guide.dtd"> |
| 25 |
-<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml,v 1.9 2006/09/04 09:38:53 nightmorph Exp $ --> |
| 26 |
+<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/mysql-upgrading.xml,v 1.10 2006/09/08 10:39:04 neysx Exp $ --> |
| 27 |
|
| 28 |
<guide link="/doc/en/mysql-upgrading.xml"> |
| 29 |
<title>Upgrade guide to MySQL 4.1.x</title> |
| 30 |
@@ -13,9 +13,9 @@ |
| 31 |
</author> |
| 32 |
|
| 33 |
<abstract> |
| 34 |
-The MySQL herd is proud to announce that MySQL 4.1 can now be found in Gentoo's |
| 35 |
-unstable tree (~arch). As the team hopes that it soon will be able to stabilize |
| 36 |
-this ebuild, here's an upgrade path for all willing testers. |
| 37 |
+The MySQL herd is proud to announce that MySQL 5.0 will soon be found in |
| 38 |
+Gentoo's stable tree. This document describes how to upgrade from MySQL 4.* to |
| 39 |
+r.0.* |
| 40 |
</abstract> |
| 41 |
|
| 42 |
<!-- The content of this document is licensed under the CC-BY-SA license --> |
| 43 |
@@ -23,7 +23,43 @@ |
| 44 |
<license/> |
| 45 |
|
| 46 |
<version>1.8</version> |
| 47 |
-<date>2006-09-04</date> |
| 48 |
+<date>2006-09-08</date> |
| 49 |
+ |
| 50 |
+<chapter> |
| 51 |
+<title>Straight upgrade, suggested for 4.1 => 5.0 migration</title> |
| 52 |
+<section> |
| 53 |
+<body> |
| 54 |
+ |
| 55 |
+<p> |
| 56 |
+The myisam storage engine in 4.1 version was already mature enough to allow a |
| 57 |
+direct upgrade to the next major version of MySQL. |
| 58 |
+</p> |
| 59 |
+ |
| 60 |
+<pre caption="Straight upgrade"> |
| 61 |
+# <i>quickpkg dev-db/mysql</i> |
| 62 |
+# <i>alias MYSQL="mysql --user=root --password=</i><comment>'your_password'</comment><i>"</i> |
| 63 |
+# <i>DATADIR=$(MYSQL --batch --raw --silent --skip-column-names \</i> |
| 64 |
+ <i>--execute='SHOW variables LIKE "datadir";' \</i> |
| 65 |
+ <i>| sed -e 's|datadir[ \t]||')</i> |
| 66 |
+# <i>MYSQL --execute="FLUSH TABLES WITH READ LOCK;"</i> |
| 67 |
+# <i>tar -cjpvf ~/mysql.$(date +%F"T"%H-%M).tar.bz2 \</i> |
| 68 |
+ <i>/etc/mysql/conf.d/mysql /etc/mysql/my.cnf "${DATADIR}"</i> |
| 69 |
+# <i>MYSQL --execute="UNLOCK TABLES;"</i> |
| 70 |
+# <i>tar -tjvf ~/mysql.*.tar.bz2</i> |
| 71 |
+# <i>emerge -av ">mysql-5.0"</i> |
| 72 |
+# <i>dispatch-conf</i> |
| 73 |
+# <i>revdep-rebuild</i> |
| 74 |
+# <i>/etc/init.d/mysql restart</i> |
| 75 |
+# <i>mysql_upgrade_shell --user=root --password=</i><comment>'your_password'</comment><i> \</i> |
| 76 |
+ <i>--protocol=tcp --datadir="${DATADIR}"</i> |
| 77 |
+# <i>/etc/init.d/mysql restart</i> |
| 78 |
+# <i>unset DATADIR</i> |
| 79 |
+# <i>unalias MYSQL</i> |
| 80 |
+</pre> |
| 81 |
+ |
| 82 |
+</body> |
| 83 |
+</section> |
| 84 |
+</chapter> |
| 85 |
|
| 86 |
<chapter> |
| 87 |
<title>Upgrading from old versions of MySQL</title> |
| 88 |
@@ -32,8 +68,8 @@ |
| 89 |
|
| 90 |
<p> |
| 91 |
Users upgrading from an old version (<4.0.24) of MySQL will first have to |
| 92 |
-install MySQL 4.0.25. If you are already running a current version, you can skip |
| 93 |
-this section and continue with the next one. |
| 94 |
+install MySQL 4.0.25. If you are already running a more recent version, you can |
| 95 |
+skip this section and continue with the <uri link="#backup">next one</uri>. |
| 96 |
</p> |
| 97 |
|
| 98 |
<pre caption="Simple upgrade"> |
| 99 |
@@ -44,14 +80,14 @@ |
| 100 |
</section> |
| 101 |
</chapter> |
| 102 |
|
| 103 |
-<chapter> |
| 104 |
+<chapter id="backup"> |
| 105 |
<title>Creating a backup of your current data</title> |
| 106 |
<section> |
| 107 |
<body> |
| 108 |
|
| 109 |
<p> |
| 110 |
-One of the most important tasks that every database administrator has to perfom |
| 111 |
-is backing up data. Here we go: |
| 112 |
+One of the most important tasks that every database administrator has to |
| 113 |
+perform is backing up data. Here we go: |
| 114 |
</p> |
| 115 |
|
| 116 |
<pre caption="Dump of all databases"> |
| 117 |
@@ -71,9 +107,9 @@ |
| 118 |
</pre> |
| 119 |
|
| 120 |
<p> |
| 121 |
-Now a file named <path>BACKUP_MYSQL_4.0.SQL</path> should exist, which later can |
| 122 |
-be used to recreate your data. The data is described in the MySQL dialect of SQL, |
| 123 |
-the Structured Query Language. |
| 124 |
+Now a file named <path>BACKUP_MYSQL_4.0.SQL</path> should exist, which can be |
| 125 |
+used later to recreate your data. The data is described in the MySQL dialect of |
| 126 |
+SQL, the Structured Query Language. |
| 127 |
</p> |
| 128 |
|
| 129 |
<p> |
| 130 |
@@ -105,15 +141,15 @@ |
| 131 |
<pre caption="Uninstall MySQL"> |
| 132 |
# <i>/etc/init.d/mysql stop</i> |
| 133 |
# <i>emerge -C mysql</i> |
| 134 |
-# <i>tar cjpvf ~/mysql.$(date +%F_%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/</i> |
| 135 |
+# <i>tar cjpvf ~/mysql.$(date +%F"T"%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/</i> |
| 136 |
# <i>ls -l ~/mysql.*</i> |
| 137 |
# <i>rm -rf /var/lib/mysql/ /var/log/mysql</i> |
| 138 |
</pre> |
| 139 |
|
| 140 |
<note> |
| 141 |
-Now two different backups should exist: The SQL one, which is portable between |
| 142 |
-various versions of MySQL, and the other one that will allow you to quickly |
| 143 |
-restore your database. This will be covered later in this doc in more detail. |
| 144 |
+Now two different backups should exist: the SQL one, which is portable between |
| 145 |
+various versions of MySQL, and the other one that will allow you to quickly |
| 146 |
+restore your database. This is covered later in this doc in more detail. |
| 147 |
</note> |
| 148 |
|
| 149 |
<p> |
| 150 |
@@ -124,7 +160,8 @@ |
| 151 |
|
| 152 |
<pre caption="Upgrading the binaries"> |
| 153 |
# <i>emerge -av ">mysql-4.1"</i> |
| 154 |
-# <i>dispatch-conf</i> |
| 155 |
+<comment>(Update your config files, you may also use dispatch-conf)</comment> |
| 156 |
+# <i>etc-update</i> |
| 157 |
# <i>revdep-rebuild</i> |
| 158 |
</pre> |
| 159 |
|
| 160 |
@@ -133,7 +170,7 @@ |
| 161 |
</p> |
| 162 |
|
| 163 |
<pre caption="Configure MySQL 4.1 base setup"> |
| 164 |
-# <i>emerge --config =mysql-4.1.<micro_version></i> |
| 165 |
+# <i>emerge --config =mysql-4.1.<comment><micro_version></comment></i> |
| 166 |
# <i>/etc/init.d/mysql start</i> |
| 167 |
</pre> |
| 168 |
|
| 169 |
@@ -142,11 +179,25 @@ |
| 170 |
</p> |
| 171 |
|
| 172 |
<impo> |
| 173 |
-The default <path>/etc/mysql/my.cnf</path> file sets binary logging |
| 174 |
+The default <path>/etc/mysql/my.cnf</path> file sets binary logging on |
| 175 |
(<c>log-bin</c>) by default. This will log every single transaction that |
| 176 |
-modifies data. If run on a very large database (1GB for example), this could |
| 177 |
-create extremely large files that take up disk space rather quickly. If you |
| 178 |
-are low on space, disabling binary logging might be a good idea. |
| 179 |
+modifies data. If run on a very large database (1GB or more), this could create |
| 180 |
+extremely large files that take up disk space rather quickly. If you are low on |
| 181 |
+space, disabling binary logging might be a good idea. |
| 182 |
+</impo> |
| 183 |
+ |
| 184 |
+<impo> |
| 185 |
+The default character set in gentoo mysql 4.1 and above is <c>utf8</c>. If the |
| 186 |
+data contain <e>non</e>-ASCII characters, you may want to preserve the default |
| 187 |
+character set of the database replacing all occurrences of <c>utf8</c> with |
| 188 |
+<c>latin1</c> into the <path>/etc/mysql/my.cnf</path> config file. More |
| 189 |
+information can be found <uri link="#On_charset_conversion">Charset |
| 190 |
+conversion</uri> charapter. |
| 191 |
+</impo> |
| 192 |
+ |
| 193 |
+<impo> |
| 194 |
+The administrative <c>mysql</c> database that containins user names, passwords |
| 195 |
+amongst other things is and <b>must</b> be in encoded in utf8. |
| 196 |
</impo> |
| 197 |
|
| 198 |
<pre caption="Importing the SQL backup"> |
| 199 |
@@ -164,8 +215,8 @@ |
| 200 |
</pre> |
| 201 |
|
| 202 |
<p> |
| 203 |
-If you now restart your MySQL daemon and everything went as expected, you will |
| 204 |
-have a fully working version of 4.1.x! :-) |
| 205 |
+If you restart your MySQL daemon now and everything goes as expected, you have |
| 206 |
+a fully working version of 4.1.x. |
| 207 |
</p> |
| 208 |
|
| 209 |
<pre caption="Restart the MySQL instance"> |
| 210 |
@@ -174,7 +225,7 @@ |
| 211 |
|
| 212 |
<p> |
| 213 |
If you encountered any problems during the upgrade process, please report them |
| 214 |
-at our <uri link="https://bugs.gentoo.org">Bugzilla</uri>. |
| 215 |
+on <uri link="http://bugs.gentoo.org">Bugzilla</uri>. |
| 216 |
</p> |
| 217 |
|
| 218 |
</body> |
| 219 |
@@ -190,54 +241,178 @@ |
| 220 |
If you are not happy with MySQL 4.1, it's possible to go back to MySQL 4.0. |
| 221 |
</p> |
| 222 |
|
| 223 |
-<pre caption="Back to the past"> |
| 224 |
+<pre caption="Reverting to the previous version"> |
| 225 |
# <i>/etc/init.d/mysql stop</i> |
| 226 |
# <i>emerge -C mysql</i> |
| 227 |
# <i>rm -rf /var/lib/mysql/ /var/log/mysql</i> |
| 228 |
# <i>emerge --usepkgonly "<mysql-4.1"</i> |
| 229 |
-# <i>tar -xjpvf mysql.[tag] -C /</i> |
| 230 |
+<comment>(Replace <timestamp> with the one used when creating the backup.)</comment> |
| 231 |
+# <i>tar -xjpvf mysql.<timestamp>.tar.bz2 -C /</i> |
| 232 |
# <i>/etc/init.d/mysql start</i> |
| 233 |
</pre> |
| 234 |
|
| 235 |
-<note> |
| 236 |
-After downgrading, you <e>may</e> need to run <c>revdep-rebuild</c>. |
| 237 |
-</note> |
| 238 |
+<impo> |
| 239 |
+If packages other than <c>dev-db/mysql</c> have been emerged following this |
| 240 |
+guide, you need to run <c>revdep-rebuild</c> to ensure that every client is |
| 241 |
+using the correct mysqlclient shared object. |
| 242 |
+</impo> |
| 243 |
|
| 244 |
</body> |
| 245 |
</section> |
| 246 |
</chapter> |
| 247 |
|
| 248 |
-<chapter> |
| 249 |
-<title>Straight upgrade, not supported, dangerous</title> |
| 250 |
+<chapter id="On_charset_conversion"> |
| 251 |
+<title>On charset conversion:</title> |
| 252 |
<section> |
| 253 |
+<title>Introduction</title> |
| 254 |
<body> |
| 255 |
|
| 256 |
<p> |
| 257 |
-Under certain conditions it's possible to directly upgrade to the next major |
| 258 |
-version of MySQL. If you know what you're doing and think that applies to your |
| 259 |
-case, here's a little trick that makes it possible to directly upgrade to |
| 260 |
-MySQl 4.1. |
| 261 |
+This charapter is not intended to be an exhaustive guide on how to do such |
| 262 |
+conversions, rather a short list of hints on which the reader can elaborate. |
| 263 |
</p> |
| 264 |
|
| 265 |
-<pre caption="Straight upgrade"> |
| 266 |
-# <i>quickpkg dev-db/mysql</i> |
| 267 |
-# <i>/etc/init.d/mysql stop</i> |
| 268 |
-# <i>tar -cjpvf ~/mysql.$(date +%F_%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/</i> |
| 269 |
-# <i>ls -l ~/mysql.*</i> |
| 270 |
-# <i>export MYSQL_STRAIGHT_UPGRADE=1</i> |
| 271 |
-# <i>emerge -av ">mysql-4.1"</i> |
| 272 |
-# <i>unset MYSQL_STRAIGHT_UPGRADE</i> |
| 273 |
-# <i>dispatch-conf</i> |
| 274 |
-# <i>revdep-rebuild</i> |
| 275 |
-# <i>/etc/init.d/mysql start</i> |
| 276 |
-# <i>mysql_fix_privilege_tables --defaults-file=/etc/mysql/my.cnf \</i> |
| 277 |
- <i>-uroot --password=</i><comment>'your_password'</comment><i></i> |
| 278 |
-# <i>mysql --database=mysql -uroot --password=</i><comment>'your_password'</comment><i> < /tmp/new_pieces.sql</i> |
| 279 |
-# <i>/etc/init.d/mysql restart</i> # just to be sure |
| 280 |
+<p> |
| 281 |
+Converting a database may be a complex task and difficulty increases with data |
| 282 |
+variancy. Things like serialized object and blobs are one example where it's |
| 283 |
+difficult to keeps pieces together. |
| 284 |
+</p> |
| 285 |
+ |
| 286 |
+</body> |
| 287 |
+</section> |
| 288 |
+<section> |
| 289 |
+<title>Indexes</title> |
| 290 |
+<body> |
| 291 |
+ |
| 292 |
+<p> |
| 293 |
+Every utf-8 char is considered 3 bytes long within an index. Indexes in MySQL |
| 294 |
+can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that the |
| 295 |
+limits are measured in bytes, whereas the length of a column is interpreted as |
| 296 |
+number of characters. |
| 297 |
+</p> |
| 298 |
+ |
| 299 |
+<p> |
| 300 |
+MySQL can also create indexes on parts of a column, this can be of some help. |
| 301 |
+Below are some examples: |
| 302 |
+</p> |
| 303 |
+ |
| 304 |
+<pre caption="Indexing using prefixes"> |
| 305 |
+$ <i>mysql -uroot -p'<comment>your_password</comment>' test</i> |
| 306 |
+ |
| 307 |
+mysql> <i>SHOW variables LIKE "version" \G</i> |
| 308 |
+*************************** 1. row *************************** |
| 309 |
+Variable_name: version |
| 310 |
+ Value: <comment>5.0.24-log</comment> |
| 311 |
+1 row in set (0.00 sec) |
| 312 |
+ |
| 313 |
+mysql> <i>CREATE TABLE t1 (</i> |
| 314 |
+ -> <i>c1 varchar(255) NOT NULL default '',</i> |
| 315 |
+ -> <i>c2 varchar(255) NOT NULL default ''</i> |
| 316 |
+ -> <i>) ENGINE=MyISAM DEFAULT CHARSET=utf8;</i> |
| 317 |
+Query OK, 0 rows affected (0.01 sec) |
| 318 |
+ |
| 319 |
+mysql> <i>ALTER TABLE t1</i> |
| 320 |
+ -> <i>ADD INDEX idx1 ( c1 , c2 );</i> |
| 321 |
+<comment>ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes</comment> |
| 322 |
+ |
| 323 |
+mysql> <i>ALTER TABLE t1</i> |
| 324 |
+ -> <i>ADD INDEX idx1 ( c1(165) , c2(165) );</i> |
| 325 |
+Query OK, 0 rows affected (0.01 sec) |
| 326 |
+Records: 0 Duplicates: 0 Warnings: 0 |
| 327 |
+ |
| 328 |
+mysql> <i>CREATE TABLE t2 (</i> |
| 329 |
+ -> <i>c1 varchar(255) NOT NULL default '',</i> |
| 330 |
+ -> <i>c2 varchar(255) NOT NULL default ''</i> |
| 331 |
+ -> <i>) ENGINE=MyISAM DEFAULT CHARSET=sjis;</i> |
| 332 |
+Query OK, 0 rows affected (0.00 sec) |
| 333 |
+ |
| 334 |
+mysql> <i>ALTER TABLE t2</i> |
| 335 |
+ -> <i>ADD INDEX idx1 ( c1(250) , c2(250) );</i> |
| 336 |
+Query OK, 0 rows affected (0.03 sec) |
| 337 |
+Records: 0 Duplicates: 0 Warnings: 0 |
| 338 |
+ |
| 339 |
+mysql> <i>CREATE TABLE t3 (</i> |
| 340 |
+ -> <i>c1 varchar(255) NOT NULL default '',</i> |
| 341 |
+ -> <i>c2 varchar(255) NOT NULL default ''</i> |
| 342 |
+ -> <i>) ENGINE=MyISAM DEFAULT CHARSET=latin1;</i> |
| 343 |
+Query OK, 0 rows affected (0.00 sec) |
| 344 |
+ |
| 345 |
+mysql> <i>ALTER TABLE t3</i> |
| 346 |
+ -> <i>ADD INDEX idx1 ( c1 , c2 );</i> |
| 347 |
+Query OK, 0 rows affected (0.03 sec) |
| 348 |
+Records: 0 Duplicates: 0 Warnings: 0 |
| 349 |
+</pre> |
| 350 |
+ |
| 351 |
+</body> |
| 352 |
+</section> |
| 353 |
+<section> |
| 354 |
+<title>Environment</title> |
| 355 |
+<body> |
| 356 |
+ |
| 357 |
+<p> |
| 358 |
+Working in a utf-8 environment, with utf-8 editors and tools help too: |
| 359 |
+</p> |
| 360 |
+ |
| 361 |
+<pre caption="Shell evironment variables"> |
| 362 |
+LC_ALL=en_US.UTF-8 |
| 363 |
+LANG=en_US.UTF-8 |
| 364 |
+export LC_ALL LANG |
| 365 |
+</pre> |
| 366 |
+ |
| 367 |
+<p> |
| 368 |
+The system must be configured to support the chosen UTF-8 locale. You will find |
| 369 |
+more information in our <uri link="/doc/en/utf-8.xml">Using UTF-8 with |
| 370 |
+Gentoo</uri> and <uri link="/doc/en/guide-localization.xml">Localization |
| 371 |
+Guide</uri> documents. |
| 372 |
+</p> |
| 373 |
+ |
| 374 |
+</body> |
| 375 |
+</section> |
| 376 |
+<section> |
| 377 |
+<title>iconv</title> |
| 378 |
+<body> |
| 379 |
+ |
| 380 |
+<p> |
| 381 |
+<c>iconv</c>, provided by <c>sys-libs/glibc</c>, is used to convert text files |
| 382 |
+from one charset to another. The <c>app-text/recode</c> package can be used as |
| 383 |
+well. |
| 384 |
+</p> |
| 385 |
+ |
| 386 |
+<pre caption="Using iconv"> |
| 387 |
+<comment>(From latin1 to utf8)</comment> |
| 388 |
+$ <i>iconv -f ISO-8859-15 -t UTF-8 file1.sql > file2.sql</i> |
| 389 |
+ |
| 390 |
+<comment>(From Japanese to utf8)</comment> |
| 391 |
+$ <i>iconv -f ISO2022JP -t UTF-8 file1.sql > file2.sql</i> |
| 392 |
</pre> |
| 393 |
|
| 394 |
<p> |
| 395 |
-Good luck and if something fails, don't say we didn't warn you! ;-) |
| 396 |
+<c>iconv</c> can be used to recode a sql dump even if the environment is not |
| 397 |
+set to utf8. |
| 398 |
+</p> |
| 399 |
+ |
| 400 |
+</body> |
| 401 |
+</section> |
| 402 |
+<section> |
| 403 |
+<title>SQL Mangling</title> |
| 404 |
+<body> |
| 405 |
+ |
| 406 |
+<p> |
| 407 |
+It's possible to use the <c>CONVERT()</c> and <c>CAST()</c> MySQL functions to |
| 408 |
+convert data in your SQL scripts. |
| 409 |
+</p> |
| 410 |
+ |
| 411 |
+</body> |
| 412 |
+</section> |
| 413 |
+<section> |
| 414 |
+<title>Apache (webserver)</title> |
| 415 |
+<body> |
| 416 |
+ |
| 417 |
+<p> |
| 418 |
+To use utf-8 with apache, you need to adjust the folowing variables in |
| 419 |
+<path>httpd.conf</path>: AddDefaultCharset, CharsetDefault, CharsetSourceEnc. |
| 420 |
+If your source html files aren't encoded in utf-8, they <b>must</b> be |
| 421 |
+converted with <c>iconv</c> or <c>recode</c>. |
| 422 |
</p> |
| 423 |
|
| 424 |
</body> |
| 425 |
|
| 426 |
|
| 427 |
|
| 428 |
-- |
| 429 |
gentoo-doc-cvs@g.o mailing list |
Archives