| List: | Commits | « Previous MessageNext Message » | |
| From: | paul | Date: | February 27 2008 6:47pm |
| Subject: | svn commit - mysqldoc@docsrva: r10047 - in trunk: . it/refman-5.1 pt/refman-5.1 refman-4.1 refman-5.0 refman-5.1 refman-6.0 | ||
| View as plain text | |||
Author: paul Date: 2008-02-27 18:47:04 +0100 (Wed, 27 Feb 2008) New Revision: 10047 Log: r29732@arctic: paul | 2008-02-27 11:42:18 -0600 Eliminate needless version differences. Reformat. Modified: trunk/it/refman-5.1/backup.xml trunk/pt/refman-5.1/backup.xml trunk/refman-4.1/backup.xml trunk/refman-5.0/backup.xml trunk/refman-5.1/backup.xml trunk/refman-5.1/dba-core-new-tmp.xml trunk/refman-6.0/backup.xml Property changes on: trunk ___________________________________________________________________ Name: svk:merge - 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:29560 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:29691 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:29560 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:29732 Modified: trunk/it/refman-5.1/backup.xml =================================================================== --- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',» +++ trunk/it/refman-5.1/backup.xml 2008-02-27 17:47:04 UTC (rev 10047) Changed blocks: 8, Lines Added: 32, Lines Deleted: 34; 6046 bytes @@ -162,7 +162,7 @@ <para> The MySQL Enterprise Monitor provides numerous advisors that issue immediate warnings should replication issues arise. For - more information see + more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -411,7 +411,7 @@ <para> For expert advice on backups and replication, subscribe to the - MySQL Enterprise Monitor. For more information see + MySQL Enterprise Monitor. For more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -457,10 +457,10 @@ includes all data, even that part that has not changed since the previous full backup. After we have made the initial full backup, it is more efficient to make incremental backups. They - are smaller and take less time to produce. The trade off is - that, at recovery time, you cannot restore your data just by - reloading the full backup. You must also process the incremental - backups to recover the incremental changes. + are smaller and take less time to produce. The tradeoff is that, + at recovery time, you cannot restore your data just by reloading + the full backup. You must also process the incremental backups + to recover the incremental changes. </para> <para> @@ -476,13 +476,13 @@ </para> <programlisting> --rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 --rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 --rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 --rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 --rw-rw---- 1 guilhem guilhem 2247446 Nov 12 16:47 gbichot2-bin.000005 --rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 --rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index +-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 +-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 +-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 +-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 +-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005 +-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 +-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index </programlisting> <para> @@ -522,8 +522,7 @@ <programlisting> -- Position to start replication or point-in-time recovery from --- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',» - MASTER_LOG_POS=4; +-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4; </programlisting> <para> @@ -724,9 +723,9 @@ <title>MySQL Enterprise</title> <para> - For maximum data recovery, MySQL Enterprise Monitor advises + For maximum data recovery, the MySQL Enterprise Monitor advises subscribers to synchronize to disk at each write. For more - information see + information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -1757,16 +1756,16 @@ Recordlength: 226 table description: -Key Start Len Index Type Rec/key Root Blocksize -1 2 8 unique double 1 15845376 1024 -2 15 10 multip. text packed stripped 2 25062400 1024 -3 219 8 multip. double 73 40907776 1024 -4 63 10 multip. text packed stripped 5 48097280 1024 -5 167 2 multip. unsigned short 4840 55200768 1024 -6 177 4 multip. unsigned long 1346 65145856 1024 -7 155 4 multip. text 4995 75090944 1024 -8 138 4 multip. unsigned long 87 85036032 1024 -9 177 4 multip. unsigned long 178 96481280 1024 +Key Start Len Index Type Rec/key Root Blocksize +1 2 8 unique double 1 15845376 1024 +2 15 10 multip. text packed stripped 2 25062400 1024 +3 219 8 multip. double 73 40907776 1024 +4 63 10 multip. text packed stripped 5 48097280 1024 +5 167 2 multip. unsigned short 4840 55200768 1024 +6 177 4 multip. unsigned long 1346 65145856 1024 +7 155 4 multip. text 4995 75090944 1024 +8 138 4 multip. unsigned long 87 85036032 1024 +9 177 4 multip. unsigned long 178 96481280 1024 193 1 text </programlisting> @@ -1846,18 +1845,17 @@ - check records and index references <replaceable>*** LOTS OF ROW NUMBERS DELETED ***</replaceable> -Records: 1403698 M.recordlength: 226 Packed: 0% -Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 -Record blocks: 1403698 Delete blocks: 0 -Recorddata: 317235748 Deleted data: 0 -Lost space: 0 Linkdata: 0 +Records: 1403698 M.recordlength: 226 Packed: 0% +Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 +Record blocks: 1403698 Delete blocks: 0 +Recorddata: 317235748 Deleted data: 0 +Lost space: 0 Linkdata: 0 User time 1639.63, System time 251.61 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0 Blocks in 4 out 0, Messages in 0 out 0, Signals 0 -Voluntary context switches 10604, » -Involuntary context switches 122798 +Voluntary context switches 10604, Involuntary context switches 122798 </programlisting> <para> Modified: trunk/pt/refman-5.1/backup.xml =================================================================== --- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',» +++ trunk/pt/refman-5.1/backup.xml 2008-02-27 17:47:04 UTC (rev 10047) Changed blocks: 8, Lines Added: 32, Lines Deleted: 34; 6046 bytes @@ -162,7 +162,7 @@ <para> The MySQL Enterprise Monitor provides numerous advisors that issue immediate warnings should replication issues arise. For - more information see + more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -411,7 +411,7 @@ <para> For expert advice on backups and replication, subscribe to the - MySQL Enterprise Monitor. For more information see + MySQL Enterprise Monitor. For more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -457,10 +457,10 @@ includes all data, even that part that has not changed since the previous full backup. After we have made the initial full backup, it is more efficient to make incremental backups. They - are smaller and take less time to produce. The trade off is - that, at recovery time, you cannot restore your data just by - reloading the full backup. You must also process the incremental - backups to recover the incremental changes. + are smaller and take less time to produce. The tradeoff is that, + at recovery time, you cannot restore your data just by reloading + the full backup. You must also process the incremental backups + to recover the incremental changes. </para> <para> @@ -476,13 +476,13 @@ </para> <programlisting> --rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 --rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 --rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 --rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 --rw-rw---- 1 guilhem guilhem 2247446 Nov 12 16:47 gbichot2-bin.000005 --rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 --rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index +-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 +-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 +-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 +-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 +-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005 +-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 +-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index </programlisting> <para> @@ -522,8 +522,7 @@ <programlisting> -- Position to start replication or point-in-time recovery from --- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',» - MASTER_LOG_POS=4; +-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4; </programlisting> <para> @@ -724,9 +723,9 @@ <title>MySQL Enterprise</title> <para> - For maximum data recovery, MySQL Enterprise Monitor advises + For maximum data recovery, the MySQL Enterprise Monitor advises subscribers to synchronize to disk at each write. For more - information see + information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -1757,16 +1756,16 @@ Recordlength: 226 table description: -Key Start Len Index Type Rec/key Root Blocksize -1 2 8 unique double 1 15845376 1024 -2 15 10 multip. text packed stripped 2 25062400 1024 -3 219 8 multip. double 73 40907776 1024 -4 63 10 multip. text packed stripped 5 48097280 1024 -5 167 2 multip. unsigned short 4840 55200768 1024 -6 177 4 multip. unsigned long 1346 65145856 1024 -7 155 4 multip. text 4995 75090944 1024 -8 138 4 multip. unsigned long 87 85036032 1024 -9 177 4 multip. unsigned long 178 96481280 1024 +Key Start Len Index Type Rec/key Root Blocksize +1 2 8 unique double 1 15845376 1024 +2 15 10 multip. text packed stripped 2 25062400 1024 +3 219 8 multip. double 73 40907776 1024 +4 63 10 multip. text packed stripped 5 48097280 1024 +5 167 2 multip. unsigned short 4840 55200768 1024 +6 177 4 multip. unsigned long 1346 65145856 1024 +7 155 4 multip. text 4995 75090944 1024 +8 138 4 multip. unsigned long 87 85036032 1024 +9 177 4 multip. unsigned long 178 96481280 1024 193 1 text </programlisting> @@ -1846,18 +1845,17 @@ - check records and index references <replaceable>*** LOTS OF ROW NUMBERS DELETED ***</replaceable> -Records: 1403698 M.recordlength: 226 Packed: 0% -Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 -Record blocks: 1403698 Delete blocks: 0 -Recorddata: 317235748 Deleted data: 0 -Lost space: 0 Linkdata: 0 +Records: 1403698 M.recordlength: 226 Packed: 0% +Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 +Record blocks: 1403698 Delete blocks: 0 +Recorddata: 317235748 Deleted data: 0 +Lost space: 0 Linkdata: 0 User time 1639.63, System time 251.61 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0 Blocks in 4 out 0, Messages in 0 out 0, Signals 0 -Voluntary context switches 10604, » -Involuntary context switches 122798 +Voluntary context switches 10604, Involuntary context switches 122798 </programlisting> <para> Modified: trunk/refman-4.1/backup.xml =================================================================== --- trunk/refman-4.1/backup.xml 2008-02-27 17:29:20 UTC (rev 10046) +++ trunk/refman-4.1/backup.xml 2008-02-27 17:47:04 UTC (rev 10047) Changed blocks: 3, Lines Added: 4, Lines Deleted: 4; 1279 bytes @@ -242,7 +242,7 @@ If you have performance problems with your server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See - <xref linkend="replication-intro"/>. + <xref linkend="replication"/>. </para> <para> @@ -418,7 +418,7 @@ <para> For expert advice on backups and replication, subscribe to the - MySQL Enterprise Monitor. For more information see, + MySQL Enterprise Monitor. For more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -730,9 +730,9 @@ <title>MySQL Enterprise</title> <para> - For maximum data recovery, MySQL Enterprise Monitor advises + For maximum data recovery, the MySQL Enterprise Monitor advises subscribers to synchronize to disk at each write. For more - information see, + information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> Modified: trunk/refman-5.0/backup.xml =================================================================== --- trunk/refman-5.0/backup.xml 2008-02-27 17:29:20 UTC (rev 10046) +++ trunk/refman-5.0/backup.xml 2008-02-27 17:47:04 UTC (rev 10047) Changed blocks: 3, Lines Added: 3, Lines Deleted: 3; 1145 bytes @@ -162,7 +162,7 @@ <para> The MySQL Enterprise Monitor provides numerous advisors that issue immediate warnings should replication issues arise. For - more information see + more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -411,7 +411,7 @@ <para> For expert advice on backups and replication, subscribe to the - MySQL Enterprise Monitor. For more information see + MySQL Enterprise Monitor. For more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -725,7 +725,7 @@ <para> For maximum data recovery, the MySQL Enterprise Monitor advises subscribers to synchronize to disk at each write. For more - information see + information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> Modified: trunk/refman-5.1/backup.xml =================================================================== --- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',» +++ trunk/refman-5.1/backup.xml 2008-02-27 17:47:04 UTC (rev 10047) Changed blocks: 8, Lines Added: 32, Lines Deleted: 34; 6037 bytes @@ -162,7 +162,7 @@ <para> The MySQL Enterprise Monitor provides numerous advisors that issue immediate warnings should replication issues arise. For - more information see + more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -411,7 +411,7 @@ <para> For expert advice on backups and replication, subscribe to the - MySQL Enterprise Monitor. For more information see + MySQL Enterprise Monitor. For more information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -457,10 +457,10 @@ includes all data, even that part that has not changed since the previous full backup. After we have made the initial full backup, it is more efficient to make incremental backups. They - are smaller and take less time to produce. The trade off is - that, at recovery time, you cannot restore your data just by - reloading the full backup. You must also process the incremental - backups to recover the incremental changes. + are smaller and take less time to produce. The tradeoff is that, + at recovery time, you cannot restore your data just by reloading + the full backup. You must also process the incremental backups + to recover the incremental changes. </para> <para> @@ -476,13 +476,13 @@ </para> <programlisting> --rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 --rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 --rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 --rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 --rw-rw---- 1 guilhem guilhem 2247446 Nov 12 16:47 gbichot2-bin.000005 --rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 --rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index +-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 +-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 +-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 +-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 +-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005 +-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 +-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index </programlisting> <para> @@ -522,8 +522,7 @@ <programlisting> -- Position to start replication or point-in-time recovery from --- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',» - MASTER_LOG_POS=4; +-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4; </programlisting> <para> @@ -724,9 +723,9 @@ <title>MySQL Enterprise</title> <para> - For maximum data recovery, MySQL Enterprise Monitor advises + For maximum data recovery, the MySQL Enterprise Monitor advises subscribers to synchronize to disk at each write. For more - information see + information, see <ulink url="&base-url-enterprise;advisors.html"/>. </para> @@ -1757,16 +1756,16 @@ Recordlength: 226 table description: -Key Start Len Index Type Rec/key Root Blocksize -1 2 8 unique double 1 15845376 1024 -2 15 10 multip. text packed stripped 2 25062400 1024 -3 219 8 multip. double 73 40907776 1024 -4 63 10 multip. text packed stripped 5 48097280 1024 -5 167 2 multip. unsigned short 4840 55200768 1024 -6 177 4 multip. unsigned long 1346 65145856 1024 -7 155 4 multip. text 4995 75090944 1024 -8 138 4 multip. unsigned long 87 85036032 1024 -9 177 4 multip. unsigned long 178 96481280 1024 +Key Start Len Index Type Rec/key Root Blocksize +1 2 8 unique double 1 15845376 1024 +2 15 10 multip. text packed stripped 2 25062400 1024 +3 219 8 multip. double 73 40907776 1024 +4 63 10 multip. text packed stripped 5 48097280 1024 +5 167 2 multip. unsigned short 4840 55200768 1024 +6 177 4 multip. unsigned long 1346 65145856 1024 +7 155 4 multip. text 4995 75090944 1024 +8 138 4 multip. unsigned long 87 85036032 1024 +9 177 4 multip. unsigned long 178 96481280 1024 193 1 text </programlisting> @@ -1846,18 +1845,17 @@ - check records and index references <replaceable>*** LOTS OF ROW NUMBERS DELETED ***</replaceable> -Records: 1403698 M.recordlength: 226 Packed: 0% -Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 -Record blocks: 1403698 Delete blocks: 0 -Recorddata: 317235748 Deleted data: 0 -Lost space: 0 Linkdata: 0 +Records: 1403698 M.recordlength: 226 Packed: 0% +Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 +Record blocks: 1403698 Delete blocks: 0 +Recorddata: 317235748 Deleted data: 0 +Lost space: 0 Linkdata: 0 User time 1639.63, System time 251.61 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0 Blocks in 4 out 0, Messages in 0 out 0, Signals 0 -Voluntary context switches 10604, » -Involuntary context switches 122798 +Voluntary context switches 10604, Involuntary context switches 122798 </programlisting> <para> Modified: trunk/refman-5.1/dba-core-new-tmp.xml =================================================================== --- trunk/refman-5.1/dba-core-new-tmp.xml 2008-02-27 17:29:20 UTC (rev 10046) +++ trunk/refman-5.1/dba-core-new-tmp.xml 2008-02-27 17:47:04 UTC (rev 10047) Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 808 bytes @@ -23215,7 +23215,7 @@ has not changed since the previous full backup. After we have made the initial full backup, it is more efficient to make incremental backups. They are smaller and take less time to - produce. The trade off is that, at recovery time, you cannot + produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes. Modified: trunk/refman-6.0/backup.xml =================================================================== --- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',» +++ trunk/refman-6.0/backup.xml 2008-02-27 17:47:04 UTC (rev 10047) Changed blocks: 10, Lines Added: 1816, Lines Deleted: 1847; 158695 bytes @@ -3,360 +3,357 @@ <!ENTITY % all.entities SYSTEM "all-entities.ent"> %all.entities; ]> +<chapter id="backup-and-recovery"> - <chapter id="backup-and-recovery"> + <title>Backup and Recovery</title> - <title>Backup and Recovery</title> + <remark role="todo"> + a lot of the information here assumes MyISAM implicitly and will not + necessarily work for other storage engines. + </remark> - <remark role="todo"> - a lot of the information here assumes MyISAM implicitly and will - not necessarily work for other storage engines. - </remark> + <para> + This section discusses how to make database backups (full and + incremental) and how to perform table maintenance. The syntax of the + SQL statements described here is given in + <xref linkend="sql-syntax"/>. Much of the information here pertains + primarily to <literal>MyISAM</literal> tables. Additional + information about <literal>InnoDB</literal> backup procedures is + given in <xref linkend="innodb-backup"/>. + </para> - <para> - This section discusses how to make database backups (full and - incremental) and how to perform table maintenance. The syntax of - the SQL statements described here is given in - <xref linkend="sql-syntax"/>. Much of the information here - pertains primarily to <literal>MyISAM</literal> tables. Additional - information about <literal>InnoDB</literal> backup procedures is - given in <xref linkend="innodb-backup"/>. - </para> + <section id="backup"> - <section id="backup"> + <title>Database Backups</title> - <title>Database Backups</title> + <indexterm> + <primary>databases</primary> + <secondary>backups</secondary> + </indexterm> - <indexterm> - <primary>databases</primary> - <secondary>backups</secondary> - </indexterm> + <indexterm> + <primary>backups</primary> + </indexterm> - <indexterm> - <primary>backups</primary> - </indexterm> + <para> + Because MySQL tables are stored as files, it is easy to do a + backup. To get a consistent backup, do a <literal>LOCK + TABLES</literal> on the relevant tables, followed by + <literal>FLUSH TABLES</literal> for the tables. See + <xref linkend="lock-tables"/>, and <xref linkend="flush"/>. You + need only a read lock; this allows other clients to continue to + query the tables while you are making a copy of the files in the + database directory. The <literal>FLUSH TABLES</literal> statement + is needed to ensure that the all active index pages are written to + disk before you start the backup. + </para> - <para> - Because MySQL tables are stored as files, it is easy to do a - backup. To get a consistent backup, do a <literal>LOCK - TABLES</literal> on the relevant tables, followed by - <literal>FLUSH TABLES</literal> for the tables. See - <xref linkend="lock-tables"/>, and <xref linkend="flush"/>. You - need only a read lock; this allows other clients to continue to - query the tables while you are making a copy of the files in the - database directory. The <literal>FLUSH TABLES</literal> - statement is needed to ensure that the all active index pages - are written to disk before you start the backup. - </para> + <para> + To make an SQL-level backup of a table, you can use + <literal>SELECT INTO ... OUTFILE</literal>. For this statement, + the output file cannot already exist because allowing files to be + overwritten would constitute a security risk. See + <xref linkend="select"/>. + </para> - <para> - To make an SQL-level backup of a table, you can use - <literal>SELECT INTO ... OUTFILE</literal>. For this statement, - the output file cannot already exist because allowing files to - be overwritten would constitute a security risk. See - <xref linkend="select"/>. - </para> + <para> + Another technique for backing up a database is to use the + <command>mysqldump</command> program or the <command>mysqlhotcopy + script</command>. See <xref linkend="mysqldump"/>, and + <xref linkend="mysqlhotcopy"/>. + </para> - <para> - Another technique for backing up a database is to use the - <command>mysqldump</command> program or the - <command>mysqlhotcopy script</command>. See - <xref linkend="mysqldump"/>, and <xref linkend="mysqlhotcopy"/>. - </para> + <orderedlist> - <orderedlist> + <listitem> + <para> + Create a full backup of your database: + </para> - <listitem> - <para> - Create a full backup of your database: - </para> - <programlisting> shell> <userinput>mysqldump --tab=<replaceable>/path/to/some/dir</replaceable> --opt <replaceable>db_name</replaceable></userinput> </programlisting> - <para> - Or: - </para> + <para> + Or: + </para> <programlisting> shell> <userinput>mysqlhotcopy <replaceable>db_name</replaceable> <replaceable>/path/to/some/dir</replaceable></userinput> </programlisting> - <para> - You can also create a binary backup simply by copying all - table files (<filename>*.frm</filename>, - <filename>*.MYD</filename>, and <filename>*.MYI</filename> - files), as long as the server isn't updating anything. The - <command>mysqlhotcopy</command> script uses this method. - (But note that these methods do not work if your database - contains <literal>InnoDB</literal> tables. - <literal>InnoDB</literal> does not store table contents in - database directories, and <command>mysqlhotcopy</command> - works only for <literal>MyISAM</literal> tables.) - </para> - </listitem> + <para> + You can also create a binary backup simply by copying all + table files (<filename>*.frm</filename>, + <filename>*.MYD</filename>, and <filename>*.MYI</filename> + files), as long as the server isn't updating anything. The + <command>mysqlhotcopy</command> script uses this method. (But + note that these methods do not work if your database contains + <literal>InnoDB</literal> tables. <literal>InnoDB</literal> + does not store table contents in database directories, and + <command>mysqlhotcopy</command> works only for + <literal>MyISAM</literal> tables.) + </para> + </listitem> - <listitem> - <para> - <indexterm> - <primary>log files</primary> - <secondary>names</secondary> - </indexterm> + <listitem> + <para> + <indexterm> + <primary>log files</primary> + <secondary>names</secondary> + </indexterm> - Stop <command>mysqld</command> if it is running, then start - it with the - <option>--log-bin[=<replaceable>file_name</replaceable>]</option> - option. See <xref linkend="binary-log"/>. The binary log - files provide you with the information you need to replicate - changes to the database that are made subsequent to the - point at which you executed <command>mysqldump</command>. - </para> - </listitem> + Stop <command>mysqld</command> if it is running, then start it + with the + <option>--log-bin[=<replaceable>file_name</replaceable>]</option> + option. See <xref linkend="binary-log"/>. The binary log files + provide you with the information you need to replicate changes + to the database that are made subsequent to the point at which + you executed <command>mysqldump</command>. + </para> + </listitem> - </orderedlist> + </orderedlist> - <para> - For <literal>InnoDB</literal> tables, it is possible to perform - an online backup that takes no locks on tables; see - <xref linkend="mysqldump"/>. - </para> + <para> + For <literal>InnoDB</literal> tables, it is possible to perform an + online backup that takes no locks on tables; see + <xref linkend="mysqldump"/>. + </para> - <para> - MySQL supports incremental backups: You need to start the server - with the <option>--log-bin</option> option to enable binary - logging; see <xref linkend="binary-log"/>. At the moment you - want to make an incremental backup (containing all changes that - happened since the last full or incremental backup), you should - rotate the binary log by using <literal>FLUSH LOGS</literal>. - This done, you need to copy to the backup location all binary - logs which range from the one of the moment of the last full or - incremental backup to the last but one. These binary logs are - the incremental backup; at restore time, you apply them as - explained further below. The next time you do a full backup, you - should also rotate the binary log using <literal>FLUSH - LOGS</literal>, <literal>mysqldump --flush-logs</literal>, or - <literal>mysqlhotcopy --flushlog</literal>. See - <xref linkend="mysqldump"/>, and <xref linkend="mysqlhotcopy"/>. - </para> + <para> + MySQL supports incremental backups: You need to start the server + with the <option>--log-bin</option> option to enable binary + logging; see <xref linkend="binary-log"/>. At the moment you want + to make an incremental backup (containing all changes that + happened since the last full or incremental backup), you should + rotate the binary log by using <literal>FLUSH LOGS</literal>. This + done, you need to copy to the backup location all binary logs + which range from the one of the moment of the last full or + incremental backup to the last but one. These binary logs are the + incremental backup; at restore time, you apply them as explained + further below. The next time you do a full backup, you should also + rotate the binary log using <literal>FLUSH LOGS</literal>, + <literal>mysqldump --flush-logs</literal>, or + <literal>mysqlhotcopy --flushlog</literal>. See + <xref linkend="mysqldump"/>, and <xref linkend="mysqlhotcopy"/>. + </para> - <para> - If your MySQL server is a slave replication server, then - regardless of the backup method you choose, you should also back - up the <filename>master.info</filename> and - <filename>relay-log.info</filename> files when you back up your - slave's data. These files are always needed to resume - replication after you restore the slave's data. If your slave is - subject to replicating <literal>LOAD DATA INFILE</literal> - commands, you should also back up any - <filename>SQL_LOAD-*</filename> files that may exist in the - directory specified by the <option>--slave-load-tmpdir</option> - option. (This location defaults to the value of the - <literal>tmpdir</literal> variable if not specified.) The slave - needs these files to resume replication of any interrupted - <literal>LOAD DATA INFILE</literal> operations. - </para> + <para> + If your MySQL server is a slave replication server, then + regardless of the backup method you choose, you should also back + up the <filename>master.info</filename> and + <filename>relay-log.info</filename> files when you back up your + slave's data. These files are always needed to resume replication + after you restore the slave's data. If your slave is subject to + replicating <literal>LOAD DATA INFILE</literal> commands, you + should also back up any <filename>SQL_LOAD-*</filename> files that + may exist in the directory specified by the + <option>--slave-load-tmpdir</option> option. (This location + defaults to the value of the <literal>tmpdir</literal> variable if + not specified.) The slave needs these files to resume replication + of any interrupted <literal>LOAD DATA INFILE</literal> operations. + </para> - <formalpara role="mnmas"> + <formalpara role="mnmas"> - <title>MySQL Enterprise</title> + <title>MySQL Enterprise</title> - <para> - The MySQL Enterprise Monitor provides numerous advisors that - issue immediate warnings should replication issues arise. For - more information see - <ulink url="&base-url-enterprise;advisors.html"/>. - </para> - - </formalpara> - <para> - If you have to restore <literal>MyISAM</literal> tables, try to - recover them using <literal>REPAIR TABLE</literal> or - <command>myisamchk -r</command> first. That should work in 99.9% - of all cases. If <command>myisamchk</command> fails, try the - following procedure. Note that it works only if you have enabled - binary logging by starting MySQL with the - <option>--log-bin</option> option. + The MySQL Enterprise Monitor provides numerous advisors that + issue immediate warnings should replication issues arise. For + more information, see + <ulink url="&base-url-enterprise;advisors.html"/>. </para> - <orderedlist> + </formalpara> - <listitem> - <para> - Restore the original <command>mysqldump</command> backup, or - binary backup. - </para> - </listitem> + <para> + If you have to restore <literal>MyISAM</literal> tables, try to + recover them using <literal>REPAIR TABLE</literal> or + <command>myisamchk -r</command> first. That should work in 99.9% + of all cases. If <command>myisamchk</command> fails, try the + following procedure. Note that it works only if you have enabled + binary logging by starting MySQL with the + <option>--log-bin</option> option. + </para> - <listitem> - <para> - Execute the following command to re-run the updates in the - binary logs: - </para> + <orderedlist> + <listitem> + <para> + Restore the original <command>mysqldump</command> backup, or + binary backup. + </para> + </listitem> + + <listitem> + <para> + Execute the following command to re-run the updates in the + binary logs: + </para> + <programlisting> shell> <userinput>mysqlbinlog binlog.[0-9]* | mysql</userinput> </programlisting> - <para> - In some cases, you may want to re-run only certain binary - logs, from certain positions (usually you want to re-run all - binary logs from the date of the restored backup, excepting - possibly some incorrect statements). See - <xref linkend="mysqlbinlog"/>, for more information on the - <command>mysqlbinlog</command> utility and how to use it. - </para> - </listitem> + <para> + In some cases, you may want to re-run only certain binary + logs, from certain positions (usually you want to re-run all + binary logs from the date of the restored backup, excepting + possibly some incorrect statements). See + <xref linkend="mysqlbinlog"/>, for more information on the + <command>mysqlbinlog</command> utility and how to use it. + </para> + </listitem> - </orderedlist> + </orderedlist> - <para> - You can also make selective backups of individual files: - </para> + <para> + You can also make selective backups of individual files: + </para> - <itemizedlist> + <itemizedlist> - <listitem> - <para> - To dump the table, use <literal>SELECT * INTO OUTFILE - '<replaceable>file_name</replaceable>' FROM - <replaceable>tbl_name</replaceable></literal>. - </para> - </listitem> + <listitem> + <para> + To dump the table, use <literal>SELECT * INTO OUTFILE + '<replaceable>file_name</replaceable>' FROM + <replaceable>tbl_name</replaceable></literal>. + </para> + </listitem> - <listitem> - <para> - To reload the table, use <literal>LOAD DATA INFILE - '<replaceable>file_name</replaceable>' REPLACE - ...</literal>. To avoid duplicate rows, the table must have - a <literal>PRIMARY KEY</literal> or a - <literal>UNIQUE</literal> index. The - <literal>REPLACE</literal> keyword causes old rows to be - replaced with new ones when a new row duplicates an old row - on a unique key value. - </para> - </listitem> + <listitem> + <para> + To reload the table, use <literal>LOAD DATA INFILE + '<replaceable>file_name</replaceable>' REPLACE ...</literal>. + To avoid duplicate rows, the table must have a + <literal>PRIMARY KEY</literal> or a <literal>UNIQUE</literal> + index. The <literal>REPLACE</literal> keyword causes old rows + to be replaced with new ones when a new row duplicates an old + row on a unique key value. + </para> + </listitem> - </itemizedlist> + </itemizedlist> - <para> - If you have performance problems with your server while making - backups, one strategy that can help is to set up replication and - perform backups on the slave rather than on the master. See - <xref linkend="replication"/>. - </para> + <para> + If you have performance problems with your server while making + backups, one strategy that can help is to set up replication and + perform backups on the slave rather than on the master. See + <xref linkend="replication"/>. + </para> - <para> - If you are using a Veritas filesystem, you can make a backup - like this: - </para> + <para> + If you are using a Veritas filesystem, you can make a backup like + this: + </para> - <orderedlist> + <orderedlist> - <listitem> - <para> - From a client program, execute <literal>FLUSH TABLES WITH - READ LOCK</literal>. - </para> - </listitem> + <listitem> + <para> + From a client program, execute <literal>FLUSH TABLES WITH READ + LOCK</literal>. + </para> + </listitem> - <listitem> - <para> - From another shell, execute <literal>mount vxfs - snapshot</literal>. - </para> - </listitem> + <listitem> + <para> + From another shell, execute <literal>mount vxfs + snapshot</literal>. + </para> + </listitem> - <listitem> - <para> - From the first client, execute <literal>UNLOCK - TABLES</literal>. - </para> - </listitem> + <listitem> + <para> + From the first client, execute <literal>UNLOCK + TABLES</literal>. + </para> + </listitem> - <listitem> - <para> - Copy files from the snapshot. - </para> - </listitem> + <listitem> + <para> + Copy files from the snapshot. + </para> + </listitem> - <listitem> - <para> - Unmount the snapshot. - </para> - </listitem> + <listitem> + <para> + Unmount the snapshot. + </para> + </listitem> - </orderedlist> + </orderedlist> - </section> + </section> - <section id="backup-strategy-example"> + <section id="backup-strategy-example"> - <title>Example Backup and Recovery Strategy</title> + <title>Example Backup and Recovery Strategy</title> - <para> - This section discusses a procedure for performing backups that - allows you to recover data after several types of crashes: - </para> + <para> + This section discusses a procedure for performing backups that + allows you to recover data after several types of crashes: + </para> - <itemizedlist> + <itemizedlist> - <listitem> - <para> - Operating system crash - </para> - </listitem> + <listitem> + <para> + Operating system crash + </para> + </listitem> - <listitem> - <para> - Power failure - </para> - </listitem> + <listitem> + <para> + Power failure + </para> + </listitem> - <listitem> - <para> - Filesystem crash - </para> - </listitem> + <listitem> + <para> + Filesystem crash + </para> + </listitem> - <listitem> - <para> - Hardware problem (hard drive, motherboard, and so forth) - </para> - </listitem> + <listitem> + <para> + Hardware problem (hard drive, motherboard, and so forth) + </para> + </listitem> - </itemizedlist> + </itemizedlist> - <para> - The example commands do not include options such as - <option>--user</option> and <option>--password</option> for the - <command>mysqldump</command> and <command>mysql</command> - programs. You should include such options as necessary so that - the MySQL server allows you to connect to it. - </para> + <para> + The example commands do not include options such as + <option>--user</option> and <option>--password</option> for the + <command>mysqldump</command> and <command>mysql</command> + programs. You should include such options as necessary so that the + MySQL server allows you to connect to it. + </para> - <para> - We assume that data is stored in the <literal>InnoDB</literal> - storage engine, which has support for transactions and automatic - crash recovery. We also assume that the MySQL server is under - load at the time of the crash. If it were not, no recovery would - ever be needed. - </para> + <para> + We assume that data is stored in the <literal>InnoDB</literal> + storage engine, which has support for transactions and automatic + crash recovery. We also assume that the MySQL server is under load + at the time of the crash. If it were not, no recovery would ever + be needed. + </para> - <para> - For cases of operating system crashes or power failures, we can - assume that MySQL's disk data is available after a restart. The - <literal>InnoDB</literal> data files might not contain - consistent data due to the crash, but <literal>InnoDB</literal> - reads its logs and finds in them the list of pending committed - and non-committed transactions that have not been flushed to the - data files. <literal>InnoDB</literal> automatically rolls back - those transactions that were not committed, and flushes to its - data files those that were committed. Information about this - recovery process is conveyed to the user through the MySQL error - log. The following is an example log excerpt: - </para> + <para> + For cases of operating system crashes or power failures, we can + assume that MySQL's disk data is available after a restart. The + <literal>InnoDB</literal> data files might not contain consistent + data due to the crash, but <literal>InnoDB</literal> reads its + logs and finds in them the list of pending committed and + non-committed transactions that have not been flushed to the data + files. <literal>InnoDB</literal> automatically rolls back those + transactions that were not committed, and flushes to its data + files those that were committed. Information about this recovery + process is conveyed to the user through the MySQL error log. The + following is an example log excerpt: + </para> <programlisting> InnoDB: Database was not shut down normally. @@ -382,479 +379,470 @@ mysqld: ready for connections </programlisting> + <para> + For the cases of filesystem crashes or hardware problems, we can + assume that the MySQL disk data is <emphasis>not</emphasis> + available after a restart. This means that MySQL fails to start + successfully because some blocks of disk data are no longer + readable. In this case, it is necessary to reformat the disk, + install a new one, or otherwise correct the underlying problem. + Then it is necessary to recover our MySQL data from backups, which + means that we must already have made backups. To make sure that is + the case, we should design a backup policy. + </para> + + <section id="backup-policy"> + + <title>Backup Policy</title> + <para> - For the cases of filesystem crashes or hardware problems, we can - assume that the MySQL disk data is <emphasis>not</emphasis> - available after a restart. This means that MySQL fails to start - successfully because some blocks of disk data are no longer - readable. In this case, it is necessary to reformat the disk, - install a new one, or otherwise correct the underlying problem. - Then it is necessary to recover our MySQL data from backups, - which means that we must already have made backups. To make sure - that is the case, we should design a backup policy. + We all know that backups must be scheduled periodically. A full + backups (a snapshot of the data at a point in time) can be done + in MySQL with several tools. For example, <literal>InnoDB Hot + Backup</literal> provides online non-blocking physical backup of + the <literal>InnoDB</literal> data files, and + <command>mysqldump</command> provides online logical backup. + This discussion uses <command>mysqldump</command>. </para> - <section id="backup-policy"> + <formalpara role="mnmas"> - <title>Backup Policy</title> + <title>MySQL Enterprise</title> <para> - We all know that backups must be scheduled periodically. A - full backups (a snapshot of the data at a point in time) can - be done in MySQL with several tools. For example, - <literal>InnoDB Hot Backup</literal> provides online - non-blocking physical backup of the <literal>InnoDB</literal> - data files, and <command>mysqldump</command> provides online - logical backup. This discussion uses - <command>mysqldump</command>. + For expert advice on backups and replication, subscribe to the + MySQL Enterprise Monitor. For more information, see + <ulink url="&base-url-enterprise;advisors.html"/>. </para> - <formalpara role="mnmas"> + </formalpara> - <title>MySQL Enterprise</title> + <para> + Assume that we make a backup on Sunday at 1 p.m., when load is + low. The following command makes a full backup of all our + <literal>InnoDB</literal> tables in all databases: + </para> - <para> - For expert advice on backups and replication, subscribe to - the MySQL Enterprise Monitor. For more information see - <ulink url="&base-url-enterprise;advisors.html"/>. - </para> - - </formalpara> - - <para> - Assume that we make a backup on Sunday at 1 p.m., when load is - low. The following command makes a full backup of all our - <literal>InnoDB</literal> tables in all databases: - </para> - <programlisting> shell> <userinput>mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql</userinput> </programlisting> - <para> - This is an online, non-blocking backup that does not disturb - the reads and writes on the tables. We assumed earlier that - our tables are <literal>InnoDB</literal> tables, so - <option>--single-transaction</option> uses a consistent read - and guarantees that data seen by <command>mysqldump</command> - does not change. (Changes made by other clients to - <literal>InnoDB</literal> tables are not seen by the - <command>mysqldump</command> process.) If we do also have - other types of tables, we must assume that they are not - changed during the backup. For example, for the - <literal>MyISAM</literal> tables in the - <literal>mysql</literal> database, we must assume that no - administrative changes are being made to MySQL accounts during - the backup. - </para> + <para> + This is an online, non-blocking backup that does not disturb the + reads and writes on the tables. We assumed earlier that our + tables are <literal>InnoDB</literal> tables, so + <option>--single-transaction</option> uses a consistent read and + guarantees that data seen by <command>mysqldump</command> does + not change. (Changes made by other clients to + <literal>InnoDB</literal> tables are not seen by the + <command>mysqldump</command> process.) If we do also have other + types of tables, we must assume that they are not changed during + the backup. For example, for the <literal>MyISAM</literal> + tables in the <literal>mysql</literal> database, we must assume + that no administrative changes are being made to MySQL accounts + during the backup. + </para> - <para> - The resulting <filename>.sql</filename> file produced by - <command>mysqldump</command> contains a set of SQL - <literal>INSERT</literal> statements that can be used to - reload the dumped tables at a later time. - </para> + <para> + The resulting <filename>.sql</filename> file produced by + <command>mysqldump</command> contains a set of SQL + <literal>INSERT</literal> statements that can be used to reload + the dumped tables at a later time. + </para> - <para> - Full backups are necessary, but they are not always - convenient. They produce large backup files and take time to - generate. They are not optimal in the sense that each - successive full backup includes all data, even that part that - has not changed since the previous full backup. After we have - made the initial full backup, it is more efficient to make - incremental backups. They are smaller and take less time to - produce. The tradeoff is that, at recovery time, you cannot - restore your data just by reloading the full backup. You must - also process the incremental backups to recover the - incremental changes. - </para> + <para> + Full backups are necessary, but they are not always convenient. + They produce large backup files and take time to generate. They + are not optimal in the sense that each successive full backup + includes all data, even that part that has not changed since the + previous full backup. After we have made the initial full + backup, it is more efficient to make incremental backups. They + are smaller and take less time to produce. The tradeoff is that, + at recovery time, you cannot restore your data just by reloading + the full backup. You must also process the incremental backups + to recover the incremental changes. + </para> - <para> - To make incremental backups, we need to save the incremental - changes. The MySQL server should always be started with the - <option>--log-bin</option> option so that it stores these - changes in a file while it updates data. This option enables - binary logging, so that the server writes each SQL statement - that updates data into a file called a MySQL binary log. - Looking at the data directory of a MySQL server that was - started with the <option>--log-bin</option> option and that - has been running for some days, we find these MySQL binary log - files: - </para> + <para> + To make incremental backups, we need to save the incremental + changes. The MySQL server should always be started with the + <option>--log-bin</option> option so that it stores these + changes in a file while it updates data. This option enables + binary logging, so that the server writes each SQL statement + that updates data into a file called a MySQL binary log. Looking + at the data directory of a MySQL server that was started with + the <option>--log-bin</option> option and that has been running + for some days, we find these MySQL binary log files: + </para> <programlisting> --rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 --rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 --rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 --rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 --rw-rw---- 1 guilhem guilhem 2247446 Nov 12 16:47 gbichot2-bin.000005 --rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 --rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index +-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 +-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 +-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003 +-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004 +-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005 +-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006 +-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index </programlisting> - <para> - Each time it restarts, the MySQL server creates a new binary - log file using the next number in the sequence. While the - server is running, you can also tell it to close the current - binary log file and begin a new one manually by issuing a - <literal>FLUSH LOGS</literal> SQL statement or with a - <command>mysqladmin flush-logs</command> command. - <command>mysqldump</command> also has an option to flush the - logs. The <literal>.index</literal> file in the data directory - contains the list of all MySQL binary logs in the directory. - This file is used for replication. - </para> + <para> + Each time it restarts, the MySQL server creates a new binary log + file using the next number in the sequence. While the server is + running, you can also tell it to close the current binary log + file and begin a new one manually by issuing a <literal>FLUSH + LOGS</literal> SQL statement or with a <command>mysqladmin + flush-logs</command> command. <command>mysqldump</command> also + has an option to flush the logs. The <literal>.index</literal> + file in the data directory contains the list of all MySQL binary + logs in the directory. This file is used for replication. + </para> - <para> - The MySQL binary logs are important for recovery because they - form the set of incremental backups. If you make sure to flush - the logs when you make your full backup, then any binary log - files created afterward contain all the data changes made - since the backup. Let's modify the previous - <command>mysqldump</command> command a bit so that it flushes - the MySQL binary logs at the moment of the full backup, and so - that the dump file contains the name of the new current binary - log: - </para> + <para> + The MySQL binary logs are important for recovery because they + form the set of incremental backups. If you make sure to flush + the logs when you make your full backup, then any binary log + files created afterward contain all the data changes made since + the backup. Let's modify the previous + <command>mysqldump</command> command a bit so that it flushes + the MySQL binary logs at the moment of the full backup, and so + that the dump file contains the name of the new current binary + log: + </para> <programlisting> shell> <userinput>mysqldump --single-transaction --flush-logs --master-data=2 \</userinput> <userinput>--all-databases > backup_sunday_1_PM.sql</userinput> </programlisting> - <para> - After executing this command, the data directory contains a - new binary log file, <filename>gbichot2-bin.000007</filename>. - The resulting <filename>.sql</filename> file includes these - lines: - </para> + <para> + After executing this command, the data directory contains a new + binary log file, <filename>gbichot2-bin.000007</filename>. The + resulting <filename>.sql</filename> file includes these lines: + </para> <programlisting> -- Position to start replication or point-in-time recovery from --- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',» - MASTER_LOG_POS=4; +-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4; </programlisting> - <para> - Because the <command>mysqldump</command> command made a full - backup, those lines mean two things: - </para> + <para> + Because the <command>mysqldump</command> command made a full + backup, those lines mean two things: + </para> - <itemizedlist> + <itemizedlist> - <listitem> - <para> - The <filename>.sql</filename> file contains all changes - made before any changes written to the - <filename>gbichot2-bin.000007</filename> binary log file - or newer. - </para> - </listitem> + <listitem> + <para> + The <filename>.sql</filename> file contains all changes made + before any changes written to the + <filename>gbichot2-bin.000007</filename> binary log file or + newer. + </para> + </listitem> - <listitem> - <para> - All data changes logged after the backup are not present - in the <filename>.sql</filename>, but are present in the - <filename>gbichot2-bin.000007</filename> binary log file - or newer. - </para> - </listitem> + <listitem> + <para> + All data changes logged after the backup are not present in + the <filename>.sql</filename>, but are present in the + <filename>gbichot2-bin.000007</filename> binary log file or + newer. + </para> + </listitem> - </itemizedlist> + </itemizedlist> - <para> - On Monday at 1 p.m., we can create an incremental backup by - flushing the logs to begin a new binary log file. For example, - executing a <command>mysqladmin flush-logs</command> command - creates <filename>gbichot2-bin.000008</filename>. All changes - between the Sunday 1 p.m. full backup and Monday 1 p.m. will - be in the <filename>gbichot2-bin.000007</filename> file. This - incremental backup is important, so it is a good idea to copy - it to a safe place. (For example, back it up on tape or DVD, - or copy it to another machine.) On Tuesday at 1 p.m., execute - another <command>mysqladmin flush-logs</command> command. All - changes between Monday 1 p.m. and Tuesday 1 p.m. will be in - the <filename>gbichot2-bin.000008</filename> file (which also - should be copied somewhere safe). - </para> + <para> + On Monday at 1 p.m., we can create an incremental backup by + flushing the logs to begin a new binary log file. For example, + executing a <command>mysqladmin flush-logs</command> command + creates <filename>gbichot2-bin.000008</filename>. All changes + between the Sunday 1 p.m. full backup and Monday 1 p.m. will be + in the <filename>gbichot2-bin.000007</filename> file. This + incremental backup is important, so it is a good idea to copy it + to a safe place. (For example, back it up on tape or DVD, or + copy it to another machine.) On Tuesday at 1 p.m., execute + another <command>mysqladmin flush-logs</command> command. All + changes between Monday 1 p.m. and Tuesday 1 p.m. will be in the + <filename>gbichot2-bin.000008</filename> file (which also should + be copied somewhere safe). + </para> - <para> - The MySQL binary logs take up disk space. To free up space, - purge them from time to time. One way to do this is by - deleting the binary logs that are no longer needed, such as - when we make a full backup: - </para> + <para> + The MySQL binary logs take up disk space. To free up space, + purge them from time to time. One way to do this is by deleting + the binary logs that are no longer needed, such as when we make + a full backup: + </para> <programlisting> shell> <userinput>mysqldump --single-transaction --flush-logs --master-data=2 \</userinput> <userinput>--all-databases --delete-master-logs > backup_sunday_1_PM.sql</userinput> </programlisting> - <note> - <para> - Deleting the MySQL binary logs with <command>mysqldump - --delete-master-logs</command> can be dangerous if your - server is a replication master server, because slave servers - might not yet fully have processed the contents of the - binary log. The description for the <literal>PURGE MASTER - LOGS</literal> statement explains what should be verified - before deleting the MySQL binary logs. See - <xref linkend="purge-master-logs"/>. - </para> - </note> + <note> + <para> + Deleting the MySQL binary logs with <command>mysqldump + --delete-master-logs</command> can be dangerous if your server + is a replication master server, because slave servers might + not yet fully have processed the contents of the binary log. + The description for the <literal>PURGE MASTER LOGS</literal> + statement explains what should be verified before deleting the + MySQL binary logs. See <xref linkend="purge-master-logs"/>. + </para> + </note> - </section> + </section> - <section id="recovery-from-backups"> + <section id="recovery-from-backups"> - <title>Using Backups for Recovery</title> + <title>Using Backups for Recovery</title> - <para> - Now, suppose that we have a catastrophic crash on Wednesday at - 8 a.m. that requires recovery from backups. To recover, first - we restore the last full backup we have (the one from Sunday 1 - p.m.). The full backup file is just a set of SQL statements, - so restoring it is very easy: - </para> + <para> + Now, suppose that we have a catastrophic crash on Wednesday at 8 + a.m. that requires recovery from backups. To recover, first we + restore the last full backup we have (the one from Sunday 1 + p.m.). The full backup file is just a set of SQL statements, so + restoring it is very easy: + </para> <programlisting> shell> <userinput>mysql < backup_sunday_1_PM.sql</userinput> </programlisting> - <para> - At this point, the data is restored to its state as of Sunday - 1 p.m.. To restore the changes made since then, we must use - the incremental backups; that is, the - <filename>gbichot2-bin.000007</filename> and - <filename>gbichot2-bin.000008</filename> binary log files. - Fetch the files if necessary from where they were backed up, - and then process their contents like this: - </para> + <para> + At this point, the data is restored to its state as of Sunday 1 + p.m.. To restore the changes made since then, we must use the + incremental backups; that is, the + <filename>gbichot2-bin.000007</filename> and + <filename>gbichot2-bin.000008</filename> binary log files. Fetch + the files if necessary from where they were backed up, and then + process their contents like this: + </para> <programlisting> shell> <userinput>mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql</userinput> </programlisting> - <para> - We now have recovered the data to its state as of Tuesday 1 - p.m., but still are missing the changes from that date to the - date of the crash. To not lose them, we would have needed to - have the MySQL server store its MySQL binary logs into a safe - location (RAID disks, SAN, ...) different from the place where - it stores its data files, so that these logs were not on the - destroyed disk. (That is, we can start the server with a - <option>--log-bin</option> option that specifies a location on - a different physical device from the one on which the data - directory resides. That way, the logs are safe even if the - device containing the directory is lost.) If we had done this, - we would have the <filename>gbichot2-bin.000009</filename> - file at hand, and we could apply it using - <command>mysqlbinlog</command> and <command>mysql</command> to - restore the most recent data changes with no loss up to the - moment of the crash. - </para> + <para> + We now have recovered the data to its state as of Tuesday 1 + p.m., but still are missing the changes from that date to the + date of the crash. To not lose them, we would have needed to + have the MySQL server store its MySQL binary logs into a safe + location (RAID disks, SAN, ...) different from the place where + it stores its data files, so that these logs were not on the + destroyed disk. (That is, we can start the server with a + <option>--log-bin</option> option that specifies a location on a + different physical device from the one on which the data + directory resides. That way, the logs are safe even if the + device containing the directory is lost.) If we had done this, + we would have the <filename>gbichot2-bin.000009</filename> file + at hand, and we could apply it using + <command>mysqlbinlog</command> and <command>mysql</command> to + restore the most recent data changes with no loss up to the + moment of the crash. + </para> - </section> + </section> - <section id="backup-strategy-summary"> + <section id="backup-strategy-summary"> - <title>Backup Strategy Summary</title> + <title>Backup Strategy Summary</title> - <para> - In case of an operating system crash or power failure, - <literal>InnoDB</literal> itself does all the job of - recovering data. But to make sure that you can sleep well, - observe the following guidelines: - </para> + <para> + In case of an operating system crash or power failure, + <literal>InnoDB</literal> itself does all the job of recovering + data. But to make sure that you can sleep well, observe the + following guidelines: + </para> - <itemizedlist> + <itemizedlist> - <listitem> - <para> - Always run the MySQL server with the - <option>--log-bin</option> option, or even - <option>--log-bin=<replaceable>log_name</replaceable></option>, - where the log file name is located on some safe media - different from the drive on which the data directory is - located. If you have such safe media, this technique can - also be good for disk load balancing (which results in a - performance improvement). - </para> - </listitem> + <listitem> + <para> + Always run the MySQL server with the + <option>--log-bin</option> option, or even + <option>--log-bin=<replaceable>log_name</replaceable></option>, + where the log file name is located on some safe media + different from the drive on which the data directory is + located. If you have such safe media, this technique can + also be good for disk load balancing (which results in a + performance improvement). + </para> + </listitem> - <listitem> - <para> - Make periodic full backups, using the - <command>mysqldump</command> command shown earlier in - <xref linkend="backup-policy"/>, that makes an online, - non-blocking backup. - </para> - </listitem> + <listitem> + <para> + Make periodic full backups, using the + <command>mysqldump</command> command shown earlier in + <xref linkend="backup-policy"/>, that makes an online, + non-blocking backup. + </para> + </listitem> - <listitem> - <para> - Make periodic incremental backups by flushing the logs - with <literal>FLUSH LOGS</literal> or <command>mysqladmin - flush-logs</command>. - </para> - </listitem> + <listitem> + <para> + Make periodic incremental backups by flushing the logs with + <literal>FLUSH LOGS</literal> or <command>mysqladmin + flush-logs</command>. + </para> + </listitem> - </itemizedlist> + </itemizedlist> - </section> - </section> - <section id="point-in-time-recovery"> + </section> - <title>Point-in-Time Recovery</title> + <section id="point-in-time-recovery"> - <indexterm> - <primary>point in time recovery</primary> - </indexterm> + <title>Point-in-Time Recovery</title> - <indexterm> - <primary>recovery</primary> - <secondary>point in time</secondary> - </indexterm> + <indexterm> + <primary>point in time recovery</primary> + </indexterm> - <para> - If a MySQL server was started with the - <option>--log-bin</option> option to enable binary logging, you - can use the <command>mysqlbinlog</command> utility to recover - data from the binary log files, starting from a specified point - in time (for example, since your last backup) until the present - or another specified point in time. For information on enabling - the binary log and using <command>mysqlbinlog</command>, see - <xref linkend="binary-log"/>, and <xref linkend="mysqlbinlog"/>. - </para> + <indexterm> + <primary>recovery</primary> + <secondary>point in time</secondary> + </indexterm> - <formalpara role="mnmas"> + <para> + If a MySQL server was started with the <option>--log-bin</option> + option to enable binary logging, you can use the + <command>mysqlbinlog</command> utility to recover data from the + binary log files, starting from a specified point in time (for + example, since your last backup) until the present or another + specified point in time. For information on enabling the binary + log and using <command>mysqlbinlog</command>, see + <xref linkend="binary-log"/>, and <xref linkend="mysqlbinlog"/>. + </para> - <title>MySQL Enterprise</title> + <formalpara role="mnmas"> - <para> - For maximum data recovery, MySQL Enterprise Monitor Service - advises subscribers to synchronize to disk at each write. For - more information see - <ulink url="&base-url-enterprise;advisors.html"/>. - </para> + <title>MySQL Enterprise</title> - </formalpara> - <para> - To restore data from a binary log, you must know the location - and name of the current binary log file. By default, the server - creates binary log files in the data directory, but a pathname - can be specified with the <option>--log-bin</option> option to - place the files in a different location. Typically the option is - given in an option file (that is, <filename>my.cnf</filename> or - <filename>my.ini</filename>, depending on your system). It can - also be given on the command line when the server is started. To - determine the name of the current binary log file, issue the - following statement: + For maximum data recovery, the MySQL Enterprise Monitor advises + subscribers to synchronize to disk at each write. For more + information, see + <ulink url="&base-url-enterprise;advisors.html"/>. </para> + </formalpara> + + <para> + To restore data from a binary log, you must know the location and + name of the current binary log file. By default, the server + creates binary log files in the data directory, but a pathname can + be specified with the <option>--log-bin</option> option to place + the files in a different location. Typically the option is given + in an option file (that is, <filename>my.cnf</filename> or + <filename>my.ini</filename>, depending on your system). It can + also be given on the command line when the server is started. To + determine the name of the current binary log file, issue the + following statement: + </para> + <programlisting> mysql> <userinput>SHOW MASTER STATUS</userinput> </programlisting> - <para> - If you prefer, you can execute the following command from the - command line instead: - </para> + <para> + If you prefer, you can execute the following command from the + command line instead: + </para> <programlisting> shell> <userinput>mysql -u root -p -E -e "SHOW MASTER STATUS"</userinput> </programlisting> - <para> - Enter the <literal>root</literal> password for your server when - <command>mysql</command> prompts you for it. - </para> + <para> + Enter the <literal>root</literal> password for your server when + <command>mysql</command> prompts you for it. + </para> - <para> - To view the contents of a binary log, use - <literal>mysqlbinlog</literal>. See - <xref linkend="mysqlbinlog"/>. - </para> + <para> + To view the contents of a binary log, use + <literal>mysqlbinlog</literal>. See <xref linkend="mysqlbinlog"/>. + </para> - <section id="point-in-time-recovery-times"> + <section id="point-in-time-recovery-times"> - <title>Specifying Times for Recovery</title> + <title>Specifying Times for Recovery</title> - <para> - To indicate the start and end times for recovery, specify the - <option>--start-date</option> and <option>--stop-date</option> - options for <command>mysqlbinlog</command>, in - <literal>DATETIME</literal> format. As an example, suppose - that exactly at 10:00 a.m. on April 20, 2005 an SQL statement - was executed that deleted a large table. To restore the table - and data, you could restore the previous night's backup, and - then execute the following command: - </para> + <para> + To indicate the start and end times for recovery, specify the + <option>--start-date</option> and <option>--stop-date</option> + options for <command>mysqlbinlog</command>, in + <literal>DATETIME</literal> format. As an example, suppose that + exactly at 10:00 a.m. on April 20, 2005 an SQL statement was + executed that deleted a large table. To restore the table and + data, you could restore the previous night's backup, and then + execute the following command: + </para> <programlisting> shell> <userinput>mysqlbinlog --stop-date="2005-04-20 9:59:59" \</userinput> <userinput>/var/log/mysql/bin.123456 | mysql -u root -p</userinput> </programlisting> - <para> - This command recovers all of the data up until the date and - time given by the <option>--stop-date</option> option. If you - did not detect the erroneous SQL statement that was entered - until hours later, you will probably also want to recover the - activity that occurred afterward. Based on this, you could run - <command>mysqlbinlog</command> again with a start date and - time, like so: - </para> + <para> + This command recovers all of the data up until the date and time + given by the <option>--stop-date</option> option. If you did not + detect the erroneous SQL statement that was entered until hours + later, you will probably also want to recover the activity that + occurred afterward. Based on this, you could run + <command>mysqlbinlog</command> again with a start date and time, + like so: + </para> <programlisting> shell> <userinput>mysqlbinlog --start-date="2005-04-20 10:01:00" \</userinput> <userinput>/var/log/mysql/bin.123456 | mysql -u root -p</userinput> </programlisting> - <para> - In this command, the SQL statements logged from 10:01 a.m. on - will be re-executed. The combination of restoring of the - previous night's dump file and the two - <command>mysqlbinlog</command> commands restores everything up - until one second before 10:00 a.m. and everything from 10:01 - a.m. on. You should examine the log to be sure of the exact - times to specify for the commands. To display the log file - contents without executing them, use this command: - </para> + <para> + In this command, the SQL statements logged from 10:01 a.m. on + will be re-executed. The combination of restoring of the + previous night's dump file and the two + <command>mysqlbinlog</command> commands restores everything up + until one second before 10:00 a.m. and everything from 10:01 + a.m. on. You should examine the log to be sure of the exact + times to specify for the commands. To display the log file + contents without executing them, use this command: + </para> <programlisting> shell> <userinput>mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql</userinput> </programlisting> - <para> - Then open the file with a text editor to examine it. - </para> + <para> + Then open the file with a text editor to examine it. + </para> - </section> + </section> - <section id="point-in-time-recovery-positions"> + <section id="point-in-time-recovery-positions"> - <title>Specifying Positions for Recovery</title> + <title>Specifying Positions for Recovery</title> - <para> - Instead of specifying dates and times, the - <option>--start-position</option> and - <option>--stop-position</option> options for - <command>mysqlbinlog</command> can be used for specifying log - positions. They work the same as the start and stop date - options, except that you specify log position numbers rather - than dates. Using positions may enable you to be more precise - about which part of the log to recover, especially if many - transactions occurred around the same time as a damaging SQL - statement. To determine the position numbers, run - <command>mysqlbinlog</command> for a range of times near the - time when the unwanted transaction was executed, but redirect - the results to a text file for examination. This can be done - like so: - </para> + <para> + Instead of specifying dates and times, the + <option>--start-position</option> and + <option>--stop-position</option> options for + <command>mysqlbinlog</command> can be used for specifying log + positions. They work the same as the start and stop date + options, except that you specify log position numbers rather + than dates. Using positions may enable you to be more precise + about which part of the log to recover, especially if many + transactions occurred around the same time as a damaging SQL + statement. To determine the position numbers, run + <command>mysqlbinlog</command> for a range of times near the + time when the unwanted transaction was executed, but redirect + the results to a text file for examination. This can be done + like so: + </para> <programlisting> shell> <userinput>mysqlbinlog --start-date="2005-04-20 9:55:00" \</userinput> @@ -862,19 +850,19 @@ <userinput>/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql</userinput> </programlisting> - <para> - This command creates a small text file in the - <filename>/tmp</filename> directory that contains the SQL - statements around the time that the deleterious SQL statement - was executed. Open this file with a text editor and look for - the statement that you don't want to repeat. Determine the - positions in the binary log for stopping and resuming the - recovery and make note of them. Positions are labeled as - <literal>log_pos</literal> followed by a number. After - restoring the previous backup file, use the position numbers - to process the binary log file. For example, you would use - commands something like these: - </para> + <para> + This command creates a small text file in the + <filename>/tmp</filename> directory that contains the SQL + statements around the time that the deleterious SQL statement + was executed. Open this file with a text editor and look for the + statement that you don't want to repeat. Determine the positions + in the binary log for stopping and resuming the recovery and + make note of them. Positions are labeled as + <literal>log_pos</literal> followed by a number. After restoring + the previous backup file, use the position numbers to process + the binary log file. For example, you would use commands + something like these: + </para> <programlisting> shell> <userinput>mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \</userinput> @@ -884,426 +872,421 @@ <userinput>| mysql -u root -p</userinput> </programlisting> - <para> - The first command recovers all the transactions up until the - stop position given. The second command recovers all - transactions from the starting position given until the end of - the binary log. Because the output of - <command>mysqlbinlog</command> includes <literal>SET - TIMESTAMP</literal> statements before each SQL statement - recorded, the recovered data and related MySQL logs will - reflect the original times at which the transactions were - executed. - </para> + <para> + The first command recovers all the transactions up until the + stop position given. The second command recovers all + transactions from the starting position given until the end of + the binary log. Because the output of + <command>mysqlbinlog</command> includes <literal>SET + TIMESTAMP</literal> statements before each SQL statement + recorded, the recovered data and related MySQL logs will reflect + the original times at which the transactions were executed. + </para> - </section> - </section> - <section id="table-maintenance"> + </section> - <title>Table Maintenance and Crash Recovery</title> + <section id="table-maintenance"> - <para> - This section discusses how to use <command>myisamchk</command> - to check or repair <literal>MyISAM</literal> tables (tables that - have <filename>.MYD</filename> and <filename>.MYI</filename> - files for storing data and indexes). For general - <command>myisamchk</command> background, see - <xref linkend="myisamchk"/>. - </para> + <title>Table Maintenance and Crash Recovery</title> - <para> - You can use <command>myisamchk</command> to get information - about your database tables or to check, repair, or optimize - them. The following sections describe how to perform these - operations and how to set up a table maintenance schedule. - </para> + <para> + This section discusses how to use <command>myisamchk</command> to + check or repair <literal>MyISAM</literal> tables (tables that have + <filename>.MYD</filename> and <filename>.MYI</filename> files for + storing data and indexes). For general + <command>myisamchk</command> background, see + <xref linkend="myisamchk"/>. + </para> - <para> - Even though table repair with <command>myisamchk</command> is - quite secure, it is always a good idea to make a backup - <emphasis>before</emphasis> doing a repair or any maintenance - operation that could make a lot of changes to a table. - </para> + <para> + You can use <command>myisamchk</command> to get information about + your database tables or to check, repair, or optimize them. The + following sections describe how to perform these operations and + how to set up a table maintenance schedule. + </para> - <para> - <command>myisamchk</command> operations that affect indexes can - cause <literal>FULLTEXT</literal> indexes to be rebuilt with - full-text parameters that are incompatible with the values used - by the MySQL server. To avoid this problem, follow the - guidelines in <xref linkend="myisamchk-general-options"/>. - </para> + <para> + Even though table repair with <command>myisamchk</command> is + quite secure, it is always a good idea to make a backup + <emphasis>before</emphasis> doing a repair or any maintenance + operation that could make a lot of changes to a table. + </para> - <para> - In many cases, you may find it simpler to do - <literal>MyISAM</literal> table maintenance using the SQL - statements that perform operations that - <command>myisamchk</command> can do: - </para> + <para> + <command>myisamchk</command> operations that affect indexes can + cause <literal>FULLTEXT</literal> indexes to be rebuilt with + full-text parameters that are incompatible with the values used by + the MySQL server. To avoid this problem, follow the guidelines in + <xref linkend="myisamchk-general-options"/>. + </para> - <itemizedlist> + <para> + In many cases, you may find it simpler to do + <literal>MyISAM</literal> table maintenance using the SQL + statements that perform operations that + <command>myisamchk</command> can do: + </para> - <listitem> - <para> - To check or repair <literal>MyISAM</literal> tables, use - <literal>CHECK TABLE</literal> or <literal>REPAIR - TABLE</literal>. - </para> - </listitem> + <itemizedlist> - <listitem> - <para> - To optimize <literal>MyISAM</literal> tables, use - <literal>OPTIMIZE TABLE</literal>. - </para> - </listitem> + <listitem> + <para> + To check or repair <literal>MyISAM</literal> tables, use + <literal>CHECK TABLE</literal> or <literal>REPAIR + TABLE</literal>. + </para> + </listitem> - <listitem> - <para> - To analyze <literal>MyISAM</literal> tables, use - <literal>ANALYZE TABLE</literal>. - </para> - </listitem> + <listitem> + <para> + To optimize <literal>MyISAM</literal> tables, use + <literal>OPTIMIZE TABLE</literal>. + </para> + </listitem> - </itemizedlist> + <listitem> + <para> + To analyze <literal>MyISAM</literal> tables, use + <literal>ANALYZE TABLE</literal>. + </para> + </listitem> - <para> - These statements can be used directly or by means of the - <command>mysqlcheck</command> client program. One advantage of - these statements over <command>myisamchk</command> is that the - server does all the work. With <command>myisamchk</command>, you - must make sure that the server does not use the tables at the - same time so that there is no unwanted interaction between - <command>myisamchk</command> and the server. See - <xref linkend="analyze-table"/>, <xref linkend="check-table"/>, - <xref linkend="optimize-table"/>, and - <xref linkend="repair-table"/>. - </para> + </itemizedlist> - <section id="crash-recovery"> + <para> + These statements can be used directly or by means of the + <command>mysqlcheck</command> client program. One advantage of + these statements over <command>myisamchk</command> is that the + server does all the work. With <command>myisamchk</command>, you + must make sure that the server does not use the tables at the same + time so that there is no unwanted interaction between + <command>myisamchk</command> and the server. See + <xref linkend="analyze-table"/>, <xref linkend="check-table"/>, + <xref linkend="optimize-table"/>, and + <xref linkend="repair-table"/>. + </para> - <title>Using <command>myisamchk</command> for Crash Recovery</title> + <section id="crash-recovery"> - <indexterm> - <primary>crash</primary> - <secondary>recovery</secondary> - </indexterm> + <title>Using <command>myisamchk</command> for Crash Recovery</title> - <indexterm> - <primary>recovery</primary> - <secondary>from crash</secondary> - </indexterm> + <indexterm> + <primary>crash</primary> + <secondary>recovery</secondary> + </indexterm> - <indexterm> - <primary>external locking</primary> - </indexterm> + <indexterm> + <primary>recovery</primary> + <secondary>from crash</secondary> + </indexterm> - <indexterm> - <primary>locking</primary> - <secondary>external</secondary> - </indexterm> + <indexterm> + <primary>external locking</primary> + </indexterm> - <remark role="todo"> - [pd] This isn't especially clear regarding what is safe and - what's not. - </remark> + <indexterm> + <primary>locking</primary> + <secondary>external</secondary> + </indexterm> - <para> - This section describes how to check for and deal with data - corruption in MySQL databases. If your tables become corrupted - frequently, you should try to find the reason why. See - <xref linkend="crashing"/>. - </para> + <remark role="todo"> + [pd] This isn't especially clear regarding what is safe and + what's not. + </remark> - <para> - For an explanation of how <literal>MyISAM</literal> tables can - become corrupted, see <xref linkend="myisam-table-problems"/>. - </para> + <para> + This section describes how to check for and deal with data + corruption in MySQL databases. If your tables become corrupted + frequently, you should try to find the reason why. See + <xref linkend="crashing"/>. + </para> - <para> - If you run <command>mysqld</command> with external locking - disabled (which is the default as of MySQL 4.0), you cannot - reliably use <command>myisamchk</command> to check a table - when <command>mysqld</command> is using the same table. If you - can be certain that no one will access the tables through - <command>mysqld</command> while you run - <command>myisamchk</command>, you only have to execute - <command>mysqladmin flush-tables</command> before you start - checking the tables. If you cannot guarantee this, you must - stop <command>mysqld</command> while you check the tables. If - you run <command>myisamchk</command> to check tables that - <command>mysqld</command> is updating at the same time, you - may get a warning that a table is corrupt even when it is not. - </para> + <para> + For an explanation of how <literal>MyISAM</literal> tables can + become corrupted, see <xref linkend="myisam-table-problems"/>. + </para> - <para> - If the server is run with external locking enabled, you can - use <command>myisamchk</command> to check tables at any time. - In this case, if the server tries to update a table that - <command>myisamchk</command> is using, the server will wait - for <command>myisamchk</command> to finish before it - continues. - </para> + <para> + If you run <command>mysqld</command> with external locking + disabled (which is the default as of MySQL 4.0), you cannot + reliably use <command>myisamchk</command> to check a table when + <command>mysqld</command> is using the same table. If you can be + certain that no one will access the tables through + <command>mysqld</command> while you run + <command>myisamchk</command>, you only have to execute + <command>mysqladmin flush-tables</command> before you start + checking the tables. If you cannot guarantee this, you must stop + <command>mysqld</command> while you check the tables. If you run + <command>myisamchk</command> to check tables that + <command>mysqld</command> is updating at the same time, you may + get a warning that a table is corrupt even when it is not. + </para> - <para> - If you use <command>myisamchk</command> to repair or optimize - tables, you <emphasis>must</emphasis> always ensure that the - <command>mysqld</command> server is not using the table (this - also applies if external locking is disabled). If you don't - stop <command>mysqld</command>, you should at least do a - <command>mysqladmin flush-tables</command> before you run - <command>myisamchk</command>. Your tables <emphasis>may become - corrupted</emphasis> if the server and - <command>myisamchk</command> access the tables simultaneously. - </para> + <para> + If the server is run with external locking enabled, you can use + <command>myisamchk</command> to check tables at any time. In + this case, if the server tries to update a table that + <command>myisamchk</command> is using, the server will wait for + <command>myisamchk</command> to finish before it continues. + </para> - <para> - When performing crash recovery, it is important to understand - that each <literal>MyISAM</literal> table - <replaceable>tbl_name</replaceable> in a database corresponds - to three files in the database directory: - </para> + <para> + If you use <command>myisamchk</command> to repair or optimize + tables, you <emphasis>must</emphasis> always ensure that the + <command>mysqld</command> server is not using the table (this + also applies if external locking is disabled). If you don't stop + <command>mysqld</command>, you should at least do a + <command>mysqladmin flush-tables</command> before you run + <command>myisamchk</command>. Your tables <emphasis>may become + corrupted</emphasis> if the server and + <command>myisamchk</command> access the tables simultaneously. + </para> - <informaltable> - <tgroup cols="2"> - <colspec colwidth="20*"/> - <colspec colwidth="40*"/> - <tbody> - <row> - <entry><emphasis role="bold">File</emphasis></entry> - <entry><emphasis role="bold">Purpose</emphasis></entry> - </row> - <row> - <entry><filename><replaceable>tbl_name</replaceable>.frm</filename></entry> - <entry>Definition (format) file</entry> - </row> - <row> - <entry><filename><replaceable>tbl_name</replaceable>.MYD</filename></entry> - <entry>Data file</entry> - </row> - <row> - <entry><filename><replaceable>tbl_name</replaceable>.MYI</filename></entry> - <entry>Index file</entry> - </row> - </tbody> - </tgroup> - </informaltable> + <para> + When performing crash recovery, it is important to understand + that each <literal>MyISAM</literal> table + <replaceable>tbl_name</replaceable> in a database corresponds to + three files in the database directory: + </para> - <para> - Each of these three file types is subject to corruption in - various ways, but problems occur most often in data files and - index files. - </para> + <informaltable> + <tgroup cols="2"> + <colspec colwidth="20*"/> + <colspec colwidth="40*"/> + <tbody> + <row> + <entry><emphasis role="bold">File</emphasis></entry> + <entry><emphasis role="bold">Purpose</emphasis></entry> + </row> + <row> + <entry><filename><replaceable>tbl_name</replaceable>.frm</filename></entry> + <entry>Definition (format) file</entry> + </row> + <row> + <entry><filename><replaceable>tbl_name</replaceable>.MYD</filename></entry> + <entry>Data file</entry> + </row> + <row> + <entry><filename><replaceable>tbl_name</replaceable>.MYI</filename></entry> + <entry>Index file</entry> + </row> + </tbody> + </tgroup> + </informaltable> - <para> - <command>myisamchk</command> works by creating a copy of the - <filename>.MYD</filename> data file row by row. It ends the - repair stage by removing the old <filename>.MYD</filename> - file and renaming the new file to the original file name. If - you use <option>--quick</option>, <command>myisamchk</command> - does not create a temporary <filename>.MYD</filename> file, - but instead assumes that the <filename>.MYD</filename> file is - correct and generates only a new index file without touching - the <filename>.MYD</filename> file. This is safe, because - <command>myisamchk</command> automatically detects whether the - <filename>.MYD</filename> file is corrupt and aborts the - repair if it is. You can also specify the - <option>--quick</option> option twice to - <command>myisamchk</command>. In this case, - <command>myisamchk</command> does not abort on some errors - (such as duplicate-key errors) but instead tries to resolve - them by modifying the <filename>.MYD</filename> file. Normally - the use of two <option>--quick</option> options is useful only - if you have too little free disk space to perform a normal - repair. In this case, you should at least make a backup of the - table before running <command>myisamchk</command>. - </para> + <para> + Each of these three file types is subject to corruption in + various ways, but problems occur most often in data files and + index files. + </para> - </section> + <para> + <command>myisamchk</command> works by creating a copy of the + <filename>.MYD</filename> data file row by row. It ends the + repair stage by removing the old <filename>.MYD</filename> file + and renaming the new file to the original file name. If you use + <option>--quick</option>, <command>myisamchk</command> does not + create a temporary <filename>.MYD</filename> file, but instead + assumes that the <filename>.MYD</filename> file is correct and + generates only a new index file without touching the + <filename>.MYD</filename> file. This is safe, because + <command>myisamchk</command> automatically detects whether the + <filename>.MYD</filename> file is corrupt and aborts the repair + if it is. You can also specify the <option>--quick</option> + option twice to <command>myisamchk</command>. In this case, + <command>myisamchk</command> does not abort on some errors (such + as duplicate-key errors) but instead tries to resolve them by + modifying the <filename>.MYD</filename> file. Normally the use + of two <option>--quick</option> options is useful only if you + have too little free disk space to perform a normal repair. In + this case, you should at least make a backup of the table before + running <command>myisamchk</command>. + </para> - <section id="check"> + </section> - <title>How to Check <literal>MyISAM</literal> Tables for Errors</title> + <section id="check"> - <indexterm> - <primary>checking</primary> - <secondary>tables for errors</secondary> - </indexterm> + <title>How to Check <literal>MyISAM</literal> Tables for Errors</title> - <indexterm> - <primary>tables</primary> - <secondary>error checking</secondary> - </indexterm> + <indexterm> + <primary>checking</primary> + <secondary>tables for errors</secondary> + </indexterm> - <indexterm> - <primary>errors</primary> - <secondary>checking tables for</secondary> - </indexterm> + <indexterm> + <primary>tables</primary> + <secondary>error checking</secondary> + </indexterm> - <para> - To check a <literal>MyISAM</literal> table, use the following - commands: - </para> + <indexterm> + <primary>errors</primary> + <secondary>checking tables for</secondary> + </indexterm> - <itemizedlist> + <para> + To check a <literal>MyISAM</literal> table, use the following + commands: + </para> - <listitem> - <para> - <literal>myisamchk - <replaceable>tbl_name</replaceable></literal> - </para> + <itemizedlist> - <para> - This finds 99.99% of all errors. What it cannot find is - corruption that involves <emphasis>only</emphasis> the - data file (which is very unusual). If you want to check a - table, you should normally run - <command>myisamchk</command> without options or with the - <option>-s</option> (silent) option. - </para> - </listitem> + <listitem> + <para> + <literal>myisamchk + <replaceable>tbl_name</replaceable></literal> + </para> - <listitem> - <para> - <literal>myisamchk -m - <replaceable>tbl_name</replaceable></literal> - </para> + <para> + This finds 99.99% of all errors. What it cannot find is + corruption that involves <emphasis>only</emphasis> the data + file (which is very unusual). If you want to check a table, + you should normally run <command>myisamchk</command> without + options or with the <option>-s</option> (silent) option. + </para> + </listitem> - <para> - This finds 99.999% of all errors. It first checks all - index entries for errors and then reads through all rows. - It calculates a checksum for all key values in the rows - and verifies that the checksum matches the checksum for - the keys in the index tree. - </para> - </listitem> + <listitem> + <para> + <literal>myisamchk -m + <replaceable>tbl_name</replaceable></literal> + </para> - <listitem> - <para> - <literal>myisamchk -e - <replaceable>tbl_name</replaceable></literal> - </para> + <para> + This finds 99.999% of all errors. It first checks all index + entries for errors and then reads through all rows. It + calculates a checksum for all key values in the rows and + verifies that the checksum matches the checksum for the keys + in the index tree. + </para> + </listitem> - <para> - This does a complete and thorough check of all data - (<option>-e</option> means <quote>extended check</quote>). - It does a check-read of every key for each row to verify - that they indeed point to the correct row. This may take a - long time for a large table that has many indexes. - Normally, <command>myisamchk</command> stops after the - first error it finds. If you want to obtain more - information, you can add the <option>-v</option> (verbose) - option. This causes <command>myisamchk</command> to keep - going, up through a maximum of 20 errors. - </para> - </listitem> + <listitem> + <para> + <literal>myisamchk -e + <replaceable>tbl_name</replaceable></literal> + </para> - <listitem> - <para> - <literal>myisamchk -e -i - <replaceable>tbl_name</replaceable></literal> - </para> + <para> + This does a complete and thorough check of all data + (<option>-e</option> means <quote>extended check</quote>). + It does a check-read of every key for each row to verify + that they indeed point to the correct row. This may take a + long time for a large table that has many indexes. Normally, + <command>myisamchk</command> stops after the first error it + finds. If you want to obtain more information, you can add + the <option>-v</option> (verbose) option. This causes + <command>myisamchk</command> to keep going, up through a + maximum of 20 errors. + </para> + </listitem> - <para> - This is like the previous command, but the - <option>-i</option> option tells - <command>myisamchk</command> to print additional - statistical information. - </para> - </listitem> + <listitem> + <para> + <literal>myisamchk -e -i + <replaceable>tbl_name</replaceable></literal> + </para> - </itemizedlist> + <para> + This is like the previous command, but the + <option>-i</option> option tells + <command>myisamchk</command> to print additional statistical + information. + </para> + </listitem> - <para> - In most cases, a simple <command>myisamchk</command> command - with no arguments other than the table name is sufficient to - check a table. - </para> + </itemizedlist> - </section> + <para> + In most cases, a simple <command>myisamchk</command> command + with no arguments other than the table name is sufficient to + check a table. + </para> - <section id="repair"> + </section> - <title>How to Repair Tables</title> + <section id="repair"> - <indexterm> - <primary>tables</primary> - <secondary>repairing</secondary> - </indexterm> + <title>How to Repair Tables</title> - <indexterm> - <primary>repairing</primary> - <secondary>tables</secondary> - </indexterm> + <indexterm> + <primary>tables</primary> + <secondary>repairing</secondary> + </indexterm> - <para> - The discussion in this section describes how to use - <command>myisamchk</command> on <literal>MyISAM</literal> - tables (extensions <filename>.MYI</filename> and - <filename>.MYD</filename>). - </para> + <indexterm> + <primary>repairing</primary> + <secondary>tables</secondary> + </indexterm> - <para> - You can also (and should, if possible) use the <literal>CHECK - TABLE</literal> and <literal>REPAIR TABLE</literal> statements - to check and repair <literal>MyISAM</literal> tables. See - <xref linkend="check-table"/>, and - <xref linkend="repair-table"/>. - </para> + <para> + The discussion in this section describes how to use + <command>myisamchk</command> on <literal>MyISAM</literal> tables + (extensions <filename>.MYI</filename> and + <filename>.MYD</filename>). + </para> - <para> - Symptoms of corrupted tables include queries that abort - unexpectedly and observable errors such as these: - </para> + <para> + You can also (and should, if possible) use the <literal>CHECK + TABLE</literal> and <literal>REPAIR TABLE</literal> statements + to check and repair <literal>MyISAM</literal> tables. See + <xref linkend="check-table"/>, and + <xref linkend="repair-table"/>. + </para> - <itemizedlist> + <para> + Symptoms of corrupted tables include queries that abort + unexpectedly and observable errors such as these: + </para> - <listitem> - <para> - <filename><replaceable>tbl_name</replaceable>.frm</filename> - is locked against change - </para> - </listitem> + <itemizedlist> - <listitem> - <para> - Can't find file - <filename><replaceable>tbl_name</replaceable>.MYI</filename> - (Errcode: <replaceable>nnn</replaceable>) - </para> - </listitem> + <listitem> + <para> + <filename><replaceable>tbl_name</replaceable>.frm</filename> + is locked against change + </para> + </listitem> - <listitem> - <para> - Unexpected end of file - </para> - </listitem> + <listitem> + <para> + Can't find file + <filename><replaceable>tbl_name</replaceable>.MYI</filename> + (Errcode: <replaceable>nnn</replaceable>) + </para> + </listitem> - <listitem> - <para> - Record file is crashed - </para> - </listitem> + <listitem> + <para> + Unexpected end of file + </para> + </listitem> - <listitem> - <para> - Got error <replaceable>nnn</replaceable> from table - handler - </para> - </listitem> + <listitem> + <para> + Record file is crashed + </para> + </listitem> - </itemizedlist> + <listitem> + <para> + Got error <replaceable>nnn</replaceable> from table handler + </para> + </listitem> - <para> - To get more information about the error, run - <command>perror</command> <replaceable>nnn</replaceable>, - where <replaceable>nnn</replaceable> is the error number. The - following example shows how to use <command>perror</command> - to find the meanings for the most common error numbers that - indicate a problem with a table: - </para> + </itemizedlist> + <para> + To get more information about the error, run + <command>perror</command> <replaceable>nnn</replaceable>, where + <replaceable>nnn</replaceable> is the error number. The + following example shows how to use <command>perror</command> to + find the meanings for the most common error numbers that + indicate a problem with a table: + </para> + <programlisting> shell> <userinput>perror 126 127 132 134 135 136 141 144 145</userinput> MySQL error code 126 = Index file is crashed @@ -1317,179 +1300,175 @@ MySQL error code 145 = Table was marked as crashed and should be repaired </programlisting> - <para> - Note that error 135 (no more room in record file) and error - 136 (no more room in index file) are not errors that can be - fixed by a simple repair. In this case, you must use - <literal>ALTER TABLE</literal> to increase the - <literal>MAX_ROWS</literal> and - <literal>AVG_ROW_LENGTH</literal> table option values: - </para> + <para> + Note that error 135 (no more room in record file) and error 136 + (no more room in index file) are not errors that can be fixed by + a simple repair. In this case, you must use <literal>ALTER + TABLE</literal> to increase the <literal>MAX_ROWS</literal> and + <literal>AVG_ROW_LENGTH</literal> table option values: + </para> <programlisting> ALTER TABLE <replaceable>tbl_name</replaceable> MAX_ROWS=<replaceable>xxx</replaceable> AVG_ROW_LENGTH=<replaceable>yyy</replaceable>; </programlisting> - <para> - If you do not know the current table option values, use - <literal>SHOW CREATE TABLE</literal>. - </para> + <para> + If you do not know the current table option values, use + <literal>SHOW CREATE TABLE</literal>. + </para> - <para> - For the other errors, you must repair your tables. - <command>myisamchk</command> can usually detect and fix most - problems that occur. - </para> + <para> + For the other errors, you must repair your tables. + <command>myisamchk</command> can usually detect and fix most + problems that occur. + </para> - <para> - The repair process involves up to four stages, described here. - Before you begin, you should change location to the database - directory and check the permissions of the table files. On - Unix, make sure that they are readable by the user that - <command>mysqld</command> runs as (and to you, because you - need to access the files you are checking). If it turns out - you need to modify files, they must also be writable by you. - </para> + <para> + The repair process involves up to four stages, described here. + Before you begin, you should change location to the database + directory and check the permissions of the table files. On Unix, + make sure that they are readable by the user that + <command>mysqld</command> runs as (and to you, because you need + to access the files you are checking). If it turns out you need + to modify files, they must also be writable by you. + </para> - <para> - This section is for the cases where a table check fails (such - as those described in <xref linkend="check"/>), or you want to - use the extended features that <command>myisamchk</command> - provides. - </para> + <para> + This section is for the cases where a table check fails (such as + those described in <xref linkend="check"/>), or you want to use + the extended features that <command>myisamchk</command> + provides. + </para> - <para> - The options that you can use for table maintenance with - <command>myisamchk</command> are described in - <xref linkend="myisamchk"/>. - </para> + <para> + The options that you can use for table maintenance with + <command>myisamchk</command> are described in + <xref linkend="myisamchk"/>. + </para> - <para> - If you are going to repair a table from the command line, you - must first stop the <command>mysqld</command> server. Note - that when you do <command>mysqladmin shutdown</command> on a - remote server, the <command>mysqld</command> server is still - alive for a while after <command>mysqladmin</command> returns, - until all statement-processing has stopped and all index - changes have been flushed to disk. - </para> + <para> + If you are going to repair a table from the command line, you + must first stop the <command>mysqld</command> server. Note that + when you do <command>mysqladmin shutdown</command> on a remote + server, the <command>mysqld</command> server is still alive for + a while after <command>mysqladmin</command> returns, until all + statement-processing has stopped and all index changes have been + flushed to disk. + </para> - <para> - <emphasis role="bold">Stage 1: Checking your tables</emphasis> - </para> + <para> + <emphasis role="bold">Stage 1: Checking your tables</emphasis> + </para> - <para> - Run <command>myisamchk *.MYI</command> or <command>myisamchk - -e *.MYI</command> if you have more time. Use the - <option>-s</option> (silent) option to suppress unnecessary - information. - </para> + <para> + Run <command>myisamchk *.MYI</command> or <command>myisamchk -e + *.MYI</command> if you have more time. Use the + <option>-s</option> (silent) option to suppress unnecessary + information. + </para> - <para> - If the <command>mysqld</command> server is stopped, you should - use the <option>--update-state</option> option to tell - <command>myisamchk</command> to mark the table as - <quote>checked.</quote> - </para> + <para> + If the <command>mysqld</command> server is stopped, you should + use the <option>--update-state</option> option to tell + <command>myisamchk</command> to mark the table as + <quote>checked.</quote> + </para> - <para> - You have to repair only those tables for which - <command>myisamchk</command> announces an error. For such - tables, proceed to Stage 2. - </para> + <para> + You have to repair only those tables for which + <command>myisamchk</command> announces an error. For such + tables, proceed to Stage 2. + </para> - <para> - If you get unexpected errors when checking (such as - <literal>out of memory</literal> errors), or if - <command>myisamchk</command> crashes, go to Stage 3. - </para> + <para> + If you get unexpected errors when checking (such as <literal>out + of memory</literal> errors), or if <command>myisamchk</command> + crashes, go to Stage 3. + </para> - <para> - <emphasis role="bold">Stage 2: Easy safe repair</emphasis> - </para> + <para> + <emphasis role="bold">Stage 2: Easy safe repair</emphasis> + </para> - <para> - First, try <command>myisamchk -r -q - <replaceable>tbl_name</replaceable></command> (<option>-r - -q</option> means <quote>quick recovery mode</quote>). This - attempts to repair the index file without touching the data - file. If the data file contains everything that it should and - the delete links point at the correct locations within the - data file, this should work, and the table is fixed. Start - repairing the next table. Otherwise, use the following - procedure: - </para> + <para> + First, try <command>myisamchk -r -q + <replaceable>tbl_name</replaceable></command> (<option>-r + -q</option> means <quote>quick recovery mode</quote>). This + attempts to repair the index file without touching the data + file. If the data file contains everything that it should and + the delete links point at the correct locations within the data + file, this should work, and the table is fixed. Start repairing + the next table. Otherwise, use the following procedure: + </para> - <orderedlist> + <orderedlist> - <listitem> - <para> - Make a backup of the data file before continuing. - </para> - </listitem> + <listitem> + <para> + Make a backup of the data file before continuing. + </para> + </listitem> - <listitem> - <para> - Use <command>myisamchk -r - <replaceable>tbl_name</replaceable></command> - (<option>-r</option> means <quote>recovery mode</quote>). - This removes incorrect rows and deleted rows from the data - file and reconstructs the index file. - </para> - </listitem> + <listitem> + <para> + Use <command>myisamchk -r + <replaceable>tbl_name</replaceable></command> + (<option>-r</option> means <quote>recovery mode</quote>). + This removes incorrect rows and deleted rows from the data + file and reconstructs the index file. + </para> + </listitem> - <listitem> - <para> - If the preceding step fails, use <command>myisamchk - --safe-recover - <replaceable>tbl_name</replaceable></command>. Safe - recovery mode uses an old recovery method that handles a - few cases that regular recovery mode does not (but is - slower). - </para> - </listitem> + <listitem> + <para> + If the preceding step fails, use <command>myisamchk + --safe-recover + <replaceable>tbl_name</replaceable></command>. Safe recovery + mode uses an old recovery method that handles a few cases + that regular recovery mode does not (but is slower). + </para> + </listitem> - </orderedlist> + </orderedlist> - <para> - Note: If you want a repair operation to go much faster, you - should set the values of the - <literal>sort_buffer_size</literal> and - <literal>key_buffer_size</literal> variables each to about 25% - of your available memory when running - <command>myisamchk</command>. - </para> + <para> + Note: If you want a repair operation to go much faster, you + should set the values of the <literal>sort_buffer_size</literal> + and <literal>key_buffer_size</literal> variables each to about + 25% of your available memory when running + <command>myisamchk</command>. + </para> - <para> - If you get unexpected errors when repairing (such as - <literal>out of memory</literal> errors), or if - <command>myisamchk</command> crashes, go to Stage 3. - </para> + <para> + If you get unexpected errors when repairing (such as + <literal>out of memory</literal> errors), or if + <command>myisamchk</command> crashes, go to Stage 3. + </para> - <para> - <emphasis role="bold">Stage 3: Difficult repair</emphasis> - </para> + <para> + <emphasis role="bold">Stage 3: Difficult repair</emphasis> + </para> - <para> - You should reach this stage only if the first 16KB block in - the index file is destroyed or contains incorrect information, - or if the index file is missing. In this case, it is necessary - to create a new index file. Do so as follows: - </para> + <para> + You should reach this stage only if the first 16KB block in the + index file is destroyed or contains incorrect information, or if + the index file is missing. In this case, it is necessary to + create a new index file. Do so as follows: + </para> - <orderedlist> + <orderedlist> - <listitem> - <para> - Move the data file to a safe place. - </para> - </listitem> + <listitem> + <para> + Move the data file to a safe place. + </para> + </listitem> - <listitem> - <para> - Use the table description file to create new (empty) data - and index files: - </para> + <listitem> + <para> + Use the table description file to create new (empty) data + and index files: + </para> <programlisting> shell> <userinput>mysql <replaceable>db_name</replaceable></userinput> @@ -1497,250 +1476,247 @@ mysql> <userinput>TRUNCATE TABLE <replaceable>tbl_name</replaceable>;</userinput> mysql> <userinput>quit</userinput> </programlisting> - </listitem> + </listitem> - <listitem> - <para> - Copy the old data file back onto the newly created data - file. (Do not just move the old file back onto the new - file. You want to retain a copy in case something goes - wrong.) - </para> - </listitem> + <listitem> + <para> + Copy the old data file back onto the newly created data + file. (Do not just move the old file back onto the new file. + You want to retain a copy in case something goes wrong.) + </para> + </listitem> - </orderedlist> + </orderedlist> - <para> - Go back to Stage 2. <command>myisamchk -r -q</command> should - work. (This should not be an endless loop.) - </para> + <para> + Go back to Stage 2. <command>myisamchk -r -q</command> should + work. (This should not be an endless loop.) + </para> - <para> - You can also use the <literal>REPAIR TABLE - <replaceable>tbl_name</replaceable> USE_FRM</literal> SQL - statement, which performs the whole procedure automatically. - There is also no possibility of unwanted interaction between a - utility and the server, because the server does all the work - when you use <literal>REPAIR TABLE</literal>. See - <xref linkend="repair-table"/>. - </para> + <para> + You can also use the <literal>REPAIR TABLE + <replaceable>tbl_name</replaceable> USE_FRM</literal> SQL + statement, which performs the whole procedure automatically. + There is also no possibility of unwanted interaction between a + utility and the server, because the server does all the work + when you use <literal>REPAIR TABLE</literal>. See + <xref linkend="repair-table"/>. + </para> - <para> - <emphasis role="bold">Stage 4: Very difficult - repair</emphasis> - </para> + <para> + <emphasis role="bold">Stage 4: Very difficult repair</emphasis> + </para> - <para> - You should reach this stage only if the - <filename>.frm</filename> description file has also crashed. - That should never happen, because the description file is not - changed after the table is created: - </para> + <para> + You should reach this stage only if the + <filename>.frm</filename> description file has also crashed. + That should never happen, because the description file is not + changed after the table is created: + </para> - <orderedlist> + <orderedlist> - <listitem> - <para> - Restore the description file from a backup and go back to - Stage 3. You can also restore the index file and go back - to Stage 2. In the latter case, you should start with - <command>myisamchk -r</command>. - </para> - </listitem> + <listitem> + <para> + Restore the description file from a backup and go back to + Stage 3. You can also restore the index file and go back to + Stage 2. In the latter case, you should start with + <command>myisamchk -r</command>. + </para> + </listitem> - <listitem> - <para> - If you do not have a backup but know exactly how the table - was created, create a copy of the table in another - database. Remove the new data file, and then move the - <filename>.frm</filename> description and - <filename>.MYI</filename> index files from the other - database to your crashed database. This gives you new - description and index files, but leaves the - <filename>.MYD</filename> data file alone. Go back to - Stage 2 and attempt to reconstruct the index file. - </para> - </listitem> + <listitem> + <para> + If you do not have a backup but know exactly how the table + was created, create a copy of the table in another database. + Remove the new data file, and then move the + <filename>.frm</filename> description and + <filename>.MYI</filename> index files from the other + database to your crashed database. This gives you new + description and index files, but leaves the + <filename>.MYD</filename> data file alone. Go back to Stage + 2 and attempt to reconstruct the index file. + </para> + </listitem> - </orderedlist> + </orderedlist> - </section> + </section> - <section id="table-optimization"> + <section id="table-optimization"> - <title>Table Optimization</title> + <title>Table Optimization</title> - <indexterm> - <primary>tables</primary> - <secondary>optimizing</secondary> - </indexterm> + <indexterm> + <primary>tables</primary> + <secondary>optimizing</secondary> + </indexterm> - <indexterm> - <primary>optimizing</primary> - <secondary>tables</secondary> - </indexterm> + <indexterm> + <primary>optimizing</primary> + <secondary>tables</secondary> + </indexterm> - <para> - To coalesce fragmented rows and eliminate wasted space that - results from deleting or updating rows, run - <command>myisamchk</command> in recovery mode: - </para> + <para> + To coalesce fragmented rows and eliminate wasted space that + results from deleting or updating rows, run + <command>myisamchk</command> in recovery mode: + </para> <programlisting> shell> <userinput>myisamchk -r <replaceable>tbl_name</replaceable></userinput> </programlisting> - <para> - You can optimize a table in the same way by using the - <literal>OPTIMIZE TABLE</literal> SQL statement. - <literal>OPTIMIZE TABLE</literal> does a table repair and a - key analysis, and also sorts the index tree so that key - lookups are faster. There is also no possibility of unwanted - interaction between a utility and the server, because the - server does all the work when you use <literal>OPTIMIZE - TABLE</literal>. See <xref linkend="optimize-table"/>. - </para> + <para> + You can optimize a table in the same way by using the + <literal>OPTIMIZE TABLE</literal> SQL statement. + <literal>OPTIMIZE TABLE</literal> does a table repair and a key + analysis, and also sorts the index tree so that key lookups are + faster. There is also no possibility of unwanted interaction + between a utility and the server, because the server does all + the work when you use <literal>OPTIMIZE TABLE</literal>. See + <xref linkend="optimize-table"/>. + </para> - <para> - <command>myisamchk</command> has a number of other options - that you can use to improve the performance of a table: - </para> + <para> + <command>myisamchk</command> has a number of other options that + you can use to improve the performance of a table: + </para> - <itemizedlist> + <itemizedlist> - <listitem> - <para> - <option>--analyze</option>, <option>-a</option> - </para> - </listitem> + <listitem> + <para> + <option>--analyze</option>, <option>-a</option> + </para> + </listitem> - <listitem> - <para> - <option>--sort-index</option>, <option>-S</option> - </para> - </listitem> + <listitem> + <para> + <option>--sort-index</option>, <option>-S</option> + </para> + </listitem> - <listitem> - <para> - <option>--sort-records=<replaceable>index_num</replaceable></option>, - <option>-R <replaceable>index_num</replaceable></option> - </para> - </listitem> + <listitem> + <para> + <option>--sort-records=<replaceable>index_num</replaceable></option>, + <option>-R <replaceable>index_num</replaceable></option> + </para> + </listitem> - </itemizedlist> + </itemizedlist> - <para> - For a full description of all available options, see - <xref linkend="myisamchk"/>. - </para> + <para> + For a full description of all available options, see + <xref linkend="myisamchk"/>. + </para> - </section> + </section> - <section id="table-info"> + <section id="table-info"> - <title>Getting Information About a Table</title> + <title>Getting Information About a Table</title> - <indexterm> - <primary>tables</primary> - <secondary>information</secondary> - </indexterm> + <indexterm> + <primary>tables</primary> + <secondary>information</secondary> + </indexterm> - <para> - To obtain a description of a table or statistics about it, use - the commands shown here. We explain some of the information in - more detail later. - </para> + <para> + To obtain a description of a table or statistics about it, use + the commands shown here. We explain some of the information in + more detail later. + </para> - <itemizedlist> + <itemizedlist> - <listitem> - <para> - <command>myisamchk -d - <replaceable>tbl_name</replaceable></command> - </para> + <listitem> + <para> + <command>myisamchk -d + <replaceable>tbl_name</replaceable></command> + </para> - <para> - Runs <command>myisamchk</command> in <quote>describe - mode</quote> to produce a description of your table. If - you start the MySQL server with external locking disabled, - <command>myisamchk</command> may report an error for a - table that is updated while it runs. However, because - <command>myisamchk</command> does not change the table in - describe mode, there is no risk of destroying data. - </para> - </listitem> + <para> + Runs <command>myisamchk</command> in <quote>describe + mode</quote> to produce a description of your table. If you + start the MySQL server with external locking disabled, + <command>myisamchk</command> may report an error for a table + that is updated while it runs. However, because + <command>myisamchk</command> does not change the table in + describe mode, there is no risk of destroying data. + </para> + </listitem> - <listitem> - <para> - <command>myisamchk -d -v - <replaceable>tbl_name</replaceable></command> - </para> + <listitem> + <para> + <command>myisamchk -d -v + <replaceable>tbl_name</replaceable></command> + </para> - <para> - Adding <option>-v</option> runs - <command>myisamchk</command> in verbose mode so that it - produces more information about what it is doing. - </para> - </listitem> + <para> + Adding <option>-v</option> runs <command>myisamchk</command> + in verbose mode so that it produces more information about + what it is doing. + </para> + </listitem> - <listitem> - <para> - <command>myisamchk -eis - <replaceable>tbl_name</replaceable></command> - </para> + <listitem> + <para> + <command>myisamchk -eis + <replaceable>tbl_name</replaceable></command> + </para> - <para> - Shows only the most important information from a table. - This operation is slow because it must read the entire - table. - </para> - </listitem> + <para> + Shows only the most important information from a table. This + operation is slow because it must read the entire table. + </para> + </listitem> - <listitem> - <para> - <command>myisamchk -eiv - <replaceable>tbl_name</replaceable></command> - </para> + <listitem> + <para> + <command>myisamchk -eiv + <replaceable>tbl_name</replaceable></command> + </para> - <para> - This is like <option>-eis</option>, but tells you what is - being done. - </para> - </listitem> + <para> + This is like <option>-eis</option>, but tells you what is + being done. + </para> + </listitem> - </itemizedlist> + </itemizedlist> - <para> - The <replaceable>tbl_name</replaceable> argument can be either - the name of a <literal>MyISAM</literal> table or the name of - its index file, as described in <xref linkend="myisamchk"/>. - Multiple <replaceable>tbl_name</replaceable> arguments can be - given. - </para> + <para> + The <replaceable>tbl_name</replaceable> argument can be either + the name of a <literal>MyISAM</literal> table or the name of its + index file, as described in <xref linkend="myisamchk"/>. + Multiple <replaceable>tbl_name</replaceable> arguments can be + given. + </para> - <indexterm> - <primary>examples</primary> - <secondary>myisamchk output</secondary> - </indexterm> + <indexterm> + <primary>examples</primary> + <secondary>myisamchk output</secondary> + </indexterm> - <indexterm> - <primary>myisamchk</primary> - <secondary>example output</secondary> - </indexterm> + <indexterm> + <primary>myisamchk</primary> + <secondary>example output</secondary> + </indexterm> - <para> - Sample output for some of these commands follows. They are - based on a table with these data and index file sizes: - </para> + <para> + Sample output for some of these commands follows. They are based + on a table with these data and index file sizes: + </para> <programlisting> -rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:30 company.MYD -rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:35 company.MYI </programlisting> - <para> - Example of <command>myisamchk -d</command> output: - </para> + <para> + Example of <command>myisamchk -d</command> output: + </para> <programlisting> MyISAM file: company.MYI @@ -1762,9 +1738,9 @@ 193 1 text </programlisting> - <para> - Example of <command>myisamchk -d -v</command> output: - </para> + <para> + Example of <command>myisamchk -d -v</command> output: + </para> <programlisting> MyISAM file: company @@ -1780,22 +1756,22 @@ Recordlength: 226 table description: -Key Start Len Index Type Rec/key Root Blocksize -1 2 8 unique double 1 15845376 1024 -2 15 10 multip. text packed stripped 2 25062400 1024 -3 219 8 multip. double 73 40907776 1024 -4 63 10 multip. text packed stripped 5 48097280 1024 -5 167 2 multip. unsigned short 4840 55200768 1024 -6 177 4 multip. unsigned long 1346 65145856 1024 -7 155 4 multip. text 4995 75090944 1024 -8 138 4 multip. unsigned long 87 85036032 1024 -9 177 4 multip. unsigned long 178 96481280 1024 +Key Start Len Index Type Rec/key Root Blocksize +1 2 8 unique double 1 15845376 1024 +2 15 10 multip. text packed stripped 2 25062400 1024 +3 219 8 multip. double 73 40907776 1024 +4 63 10 multip. text packed stripped 5 48097280 1024 +5 167 2 multip. unsigned short 4840 55200768 1024 +6 177 4 multip. unsigned long 1346 65145856 1024 +7 155 4 multip. text 4995 75090944 1024 +8 138 4 multip. unsigned long 87 85036032 1024 +9 177 4 multip. unsigned long 178 96481280 1024 193 1 text </programlisting> - <para> - Example of <command>myisamchk -eis</command> output: - </para> + <para> + Example of <command>myisamchk -eis</command> output: + </para> <programlisting> Checking MyISAM file: company @@ -1825,9 +1801,9 @@ Voluntary context switches 639, Involuntary context switches 28966 </programlisting> - <para> - Example of <command>myisamchk -eiv</command> output: - </para> + <para> + Example of <command>myisamchk -eiv</command> output: + </para> <programlisting> Checking MyISAM file: company @@ -1869,603 +1845,596 @@ - check records and index references <replaceable>*** LOTS OF ROW NUMBERS DELETED ***</replaceable> -Records: 1403698 M.recordlength: 226 Packed: 0% -Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 -Record blocks: 1403698 Delete blocks: 0 -Recorddata: 317235748 Deleted data: 0 -Lost space: 0 Linkdata: 0 +Records: 1403698 M.recordlength: 226 Packed: 0% +Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 +Record blocks: 1403698 Delete blocks: 0 +Recorddata: 317235748 Deleted data: 0 +Lost space: 0 Linkdata: 0 User time 1639.63, System time 251.61 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0 Blocks in 4 out 0, Messages in 0 out 0, Signals 0 -Voluntary context switches 10604, » -Involuntary context switches 122798 +Voluntary context switches 10604, Involuntary context switches 122798 </programlisting> - <para> - Explanations for the types of information - <command>myisamchk</command> produces are given here. - <quote>Keyfile</quote> refers to the index file. - <quote>Record</quote> and <quote>row</quote> are synonymous. - </para> + <para> + Explanations for the types of information + <command>myisamchk</command> produces are given here. + <quote>Keyfile</quote> refers to the index file. + <quote>Record</quote> and <quote>row</quote> are synonymous. + </para> - <itemizedlist> + <itemizedlist> - <listitem> - <para> - <literal>MyISAM file</literal> - </para> + <listitem> + <para> + <literal>MyISAM file</literal> + </para> - <para> - Name of the <literal>MyISAM</literal> (index) file. - </para> - </listitem> + <para> + Name of the <literal>MyISAM</literal> (index) file. + </para> + </listitem> - <listitem> - <para> - <literal>File-version</literal> - </para> + <listitem> + <para> + <literal>File-version</literal> + </para> - <para> - Version of <literal>MyISAM</literal> format. Currently - always 2. - </para> - </listitem> + <para> + Version of <literal>MyISAM</literal> format. Currently + always 2. + </para> + </listitem> - <listitem> - <para> - <literal>Creation time</literal> - </para> + <listitem> + <para> + <literal>Creation time</literal> + </para> - <para> - When the data file was created. - </para> - </listitem> + <para> + When the data file was created. + </para> + </listitem> - <listitem> - <para> - <literal>Recover time</literal> - </para> + <listitem> + <para> + <literal>Recover time</literal> + </para> - <para> - When the index/data file was last reconstructed. - </para> - </listitem> + <para> + When the index/data file was last reconstructed. + </para> + </listitem> - <listitem> - <para> - <literal>Data records</literal> - </para> + <listitem> + <para> + <literal>Data records</literal> + </para> - <para> - How many rows are in the table. - </para> - </listitem> + <para> + How many rows are in the table. + </para> + </listitem> - <listitem> - <para> - <literal>Deleted blocks</literal> - </para> + <listitem> + <para> + <literal>Deleted blocks</literal> + </para> - <para> - How many deleted blocks still have reserved space. You can - optimize your table to minimize this space. See - <xref linkend="table-optimization"/>. - </para> - </listitem> + <para> + How many deleted blocks still have reserved space. You can + optimize your table to minimize this space. See + <xref linkend="table-optimization"/>. + </para> + </listitem> - <listitem> - <para> - <literal>Datafile parts</literal> - </para> + <listitem> + <para> + <literal>Datafile parts</literal> + </para> - <para> - For dynamic-row format, this indicates how many data - blocks there are. For an optimized table without - fragmented rows, this is the same as <literal>Data - records</literal>. - </para> - </listitem> + <para> + For dynamic-row format, this indicates how many data blocks + there are. For an optimized table without fragmented rows, + this is the same as <literal>Data records</literal>. + </para> + </listitem> - <listitem> - <para> - <literal>Deleted data</literal> - </para> + <listitem> + <para> + <literal>Deleted data</literal> + </para> - <para> - How many bytes of unreclaimed deleted data there are. You - can optimize your table to minimize this space. See - <xref linkend="table-optimization"/>. - </para> - </listitem> + <para> + How many bytes of unreclaimed deleted data there are. You + can optimize your table to minimize this space. See + <xref linkend="table-optimization"/>. + </para> + </listitem> - <listitem> - <para> - <literal>Datafile pointer</literal> - </para> + <listitem> + <para> + <literal>Datafile pointer</literal> + </para> - <para> - The size of the data file pointer, in bytes. It is usually - 2, 3, 4, or 5 bytes. Most tables manage with 2 bytes, but - this cannot be controlled from MySQL yet. For fixed - tables, this is a row address. For dynamic tables, this is - a byte address. - </para> - </listitem> + <para> + The size of the data file pointer, in bytes. It is usually + 2, 3, 4, or 5 bytes. Most tables manage with 2 bytes, but + this cannot be controlled from MySQL yet. For fixed tables, + this is a row address. For dynamic tables, this is a byte + address. + </para> + </listitem> - <listitem> - <para> - <literal>Keyfile pointer</literal> - </para> + <listitem> + <para> + <literal>Keyfile pointer</literal> + </para> - <para> - The size of the index file pointer, in bytes. It is - usually 1, 2, or 3 bytes. Most tables manage with 2 bytes, - but this is calculated automatically by MySQL. It is - always a block address. - </para> - </listitem> + <para> + The size of the index file pointer, in bytes. It is usually + 1, 2, or 3 bytes. Most tables manage with 2 bytes, but this + is calculated automatically by MySQL. It is always a block + address. + </para> + </listitem> - <listitem> - <para> - <literal>Max datafile length</literal> - </para> + <listitem> + <para> + <literal>Max datafile length</literal> + </para> - <para> - How long the table data file can become, in bytes. - </para> - </listitem> + <para> + How long the table data file can become, in bytes. + </para> + </listitem> - <listitem> - <para> - <literal>Max keyfile length</literal> - </para> + <listitem> + <para> + <literal>Max keyfile length</literal> + </para> - <para> - How long the table index file can become, in bytes. - </para> - </listitem> + <para> + How long the table index file can become, in bytes. + </para> + </listitem> - <listitem> - <para> - <literal>Recordlength</literal> - </para> + <listitem> + <para> + <literal>Recordlength</literal> + </para> - <para> - How much space each row takes, in bytes. - </para> - </listitem> + <para> + How much space each row takes, in bytes. + </para> + </listitem> - <listitem> - <para> - <literal>Record format</literal> - </para> + <listitem> + <para> + <literal>Record format</literal> + </para> - <para> - The format used to store table rows. The preceding - examples use <literal>Fixed length</literal>. Other - possible values are <literal>Compressed</literal> and - <literal>Packed</literal>. - </para> - </listitem> + <para> + The format used to store table rows. The preceding examples + use <literal>Fixed length</literal>. Other possible values + are <literal>Compressed</literal> and + <literal>Packed</literal>. + </para> + </listitem> - <listitem> - <para> - <literal>table description</literal> - </para> + <listitem> + <para> + <literal>table description</literal> + </para> - <para> - A list of all keys in the table. For each key, - <command>myisamchk</command> displays some low-level - information: - </para> + <para> + A list of all keys in the table. For each key, + <command>myisamchk</command> displays some low-level + information: + </para> - <itemizedlist> + <itemizedlist> - <listitem> - <para> - <literal>Key</literal> - </para> + <listitem> + <para> + <literal>Key</literal> + </para> - <para> - This key's number. - </para> - </listitem> + <para> + This key's number. + </para> + </listitem> - <listitem> - <para> - <literal>Start</literal> - </para> + <listitem> + <para> + <literal>Start</literal> + </para> - <para> - Where in the row this portion of the index starts. - </para> - </listitem> + <para> + Where in the row this portion of the index starts. + </para> + </listitem> - <listitem> - <para> - <literal>Len</literal> - </para> + <listitem> + <para> + <literal>Len</literal> + </para> - <para> - How long this portion of the index is. For packed - numbers, this should always be the full length of the - column. For strings, it may be shorter than the full - length of the indexed column, because you can index a - prefix of a string column. - </para> - </listitem> + <para> + How long this portion of the index is. For packed + numbers, this should always be the full length of the + column. For strings, it may be shorter than the full + length of the indexed column, because you can index a + prefix of a string column. + </para> + </listitem> - <listitem> - <para> - <literal>Index</literal> - </para> + <listitem> + <para> + <literal>Index</literal> + </para> - <para> - Whether a key value can exist multiple times in the - index. Possible values are <literal>unique</literal> - or <literal>multip.</literal> (multiple). - </para> - </listitem> + <para> + Whether a key value can exist multiple times in the + index. Possible values are <literal>unique</literal> or + <literal>multip.</literal> (multiple). + </para> + </listitem> - <listitem> - <para> - <literal>Type</literal> - </para> + <listitem> + <para> + <literal>Type</literal> + </para> - <para> - What data type this portion of the index has. This is - a <literal>MyISAM</literal> data type with the - possible values <literal>packed</literal>, - <literal>stripped</literal>, or - <literal>empty</literal>. - </para> - </listitem> + <para> + What data type this portion of the index has. This is a + <literal>MyISAM</literal> data type with the possible + values <literal>packed</literal>, + <literal>stripped</literal>, or + <literal>empty</literal>. + </para> + </listitem> - <listitem> - <para> - <literal>Root</literal> - </para> + <listitem> + <para> + <literal>Root</literal> + </para> - <para> - Address of the root index block. - </para> - </listitem> + <para> + Address of the root index block. + </para> + </listitem> - <listitem> - <para> - <literal>Blocksize</literal> - </para> + <listitem> + <para> + <literal>Blocksize</literal> + </para> - <para> - The size of each index block. By default this is 1024, - but the value may be changed at compile time when - MySQL is built from source. - </para> - </listitem> + <para> + The size of each index block. By default this is 1024, + but the value may be changed at compile time when MySQL + is built from source. + </para> + </listitem> - <listitem> - <para> - <literal>Rec/key</literal> - </para> + <listitem> + <para> + <literal>Rec/key</literal> + </para> - <para> - This is a statistical value used by the optimizer. It - tells how many rows there are per value for this - index. A unique index always has a value of 1. This - may be updated after a table is loaded (or greatly - changed) with <command>myisamchk -a</command>. If this - is not updated at all, a default value of 30 is given. - </para> - </listitem> + <para> + This is a statistical value used by the optimizer. It + tells how many rows there are per value for this index. + A unique index always has a value of 1. This may be + updated after a table is loaded (or greatly changed) + with <command>myisamchk -a</command>. If this is not + updated at all, a default value of 30 is given. + </para> + </listitem> - </itemizedlist> + </itemizedlist> - <para> - For the table shown in the examples, there are two - <literal>table description</literal> lines for the ninth - index. This indicates that it is a multiple-part index - with two parts. - </para> - </listitem> + <para> + For the table shown in the examples, there are two + <literal>table description</literal> lines for the ninth + index. This indicates that it is a multiple-part index with + two parts. + </para> + </listitem> - <listitem> - <para> - <literal>Keyblocks used</literal> - </para> + <listitem> + <para> + <literal>Keyblocks used</literal> + </para> - <para> - What percentage of the keyblocks are used. When a table - has just been reorganized with - <command>myisamchk</command>, as for the table in the - examples, the values are very high (very near the - theoretical maximum). - </para> - </listitem> + <para> + What percentage of the keyblocks are used. When a table has + just been reorganized with <command>myisamchk</command>, as + for the table in the examples, the values are very high + (very near the theoretical maximum). + </para> + </listitem> - <listitem> - <para> - <literal>Packed</literal> - </para> + <listitem> + <para> + <literal>Packed</literal> + </para> - <para> - MySQL tries to pack key values that have a common suffix. - This can only be used for indexes on - <literal>CHAR</literal> and <literal>VARCHAR</literal> - columns. For long indexed strings that have similar - leftmost parts, this can significantly reduce the space - used. In the third of the preceding examples, the fourth - key is 10 characters long and a 60% reduction in space is - achieved. - </para> - </listitem> + <para> + MySQL tries to pack key values that have a common suffix. + This can only be used for indexes on <literal>CHAR</literal> + and <literal>VARCHAR</literal> columns. For long indexed + strings that have similar leftmost parts, this can + significantly reduce the space used. In the third of the + preceding examples, the fourth key is 10 characters long and + a 60% reduction in space is achieved. + </para> + </listitem> - <listitem> - <para> - <literal>Max levels</literal> - </para> + <listitem> + <para> + <literal>Max levels</literal> + </para> - <para> - How deep the B-tree for this key is. Large tables with - long key values get high values. - </para> - </listitem> + <para> + How deep the B-tree for this key is. Large tables with long + key values get high values. + </para> + </listitem> - <listitem> - <para> - <literal>Records</literal> - </para> + <listitem> + <para> + <literal>Records</literal> + </para> - <para> - How many rows are in the table. - </para> - </listitem> + <para> + How many rows are in the table. + </para> + </listitem> - <listitem> - <para> - <literal>M.recordlength</literal> - </para> + <listitem> + <para> + <literal>M.recordlength</literal> + </para> - <para> - The average row length. This is the exact row length for - tables with fixed-length rows, because all rows have the - same length. - </para> - </listitem> + <para> + The average row length. This is the exact row length for + tables with fixed-length rows, because all rows have the + same length. + </para> + </listitem> - <listitem> - <para> - <literal>Packed</literal> - </para> + <listitem> + <para> + <literal>Packed</literal> + </para> - <remark role="todo"> - Still true in 5.1? - </remark> + <remark role="todo"> + Still true in 5.1? + </remark> - <para> - MySQL strips spaces from the end of strings. The - <literal>Packed</literal> value indicates the percentage - of savings achieved by doing this. - </para> - </listitem> + <para> + MySQL strips spaces from the end of strings. The + <literal>Packed</literal> value indicates the percentage of + savings achieved by doing this. + </para> + </listitem> - <listitem> - <para> - <literal>Recordspace used</literal> - </para> + <listitem> + <para> + <literal>Recordspace used</literal> + </para> - <para> - What percentage of the data file is used. - </para> - </listitem> + <para> + What percentage of the data file is used. + </para> + </listitem> - <listitem> - <para> - <literal>Empty space</literal> - </para> + <listitem> + <para> + <literal>Empty space</literal> + </para> - <para> - What percentage of the data file is unused. - </para> - </listitem> + <para> + What percentage of the data file is unused. + </para> + </listitem> - <listitem> - <para> - <literal>Blocks/Record</literal> - </para> + <listitem> + <para> + <literal>Blocks/Record</literal> + </para> - <para> - Average number of blocks per row (that is, how many links - a fragmented row is composed of). This is always 1.0 for - fixed-format tables. This value should stay as close to - 1.0 as possible. If it gets too large, you can reorganize - the table. See <xref linkend="table-optimization"/>. - </para> - </listitem> + <para> + Average number of blocks per row (that is, how many links a + fragmented row is composed of). This is always 1.0 for + fixed-format tables. This value should stay as close to 1.0 + as possible. If it gets too large, you can reorganize the + table. See <xref linkend="table-optimization"/>. + </para> + </listitem> - <listitem> - <para> - <literal>Recordblocks</literal> - </para> + <listitem> + <para> + <literal>Recordblocks</literal> + </para> - <para> - How many blocks (links) are used. For fixed-format tables, - this is the same as the number of rows. - </para> - </listitem> + <para> + How many blocks (links) are used. For fixed-format tables, + this is the same as the number of rows. + </para> + </listitem> - <listitem> - <para> - <literal>Deleteblocks</literal> - </para> + <listitem> + <para> + <literal>Deleteblocks</literal> + </para> - <para> - How many blocks (links) are deleted. - </para> - </listitem> + <para> + How many blocks (links) are deleted. + </para> + </listitem> - <listitem> - <para> - <literal>Recorddata</literal> - </para> + <listitem> + <para> + <literal>Recorddata</literal> + </para> - <para> - How many bytes in the data file are used. - </para> - </listitem> + <para> + How many bytes in the data file are used. + </para> + </listitem> - <listitem> - <para> - <literal>Deleted data</literal> - </para> + <listitem> + <para> + <literal>Deleted data</literal> + </para> - <para> - How many bytes in the data file are deleted (unused). - </para> - </listitem> + <para> + How many bytes in the data file are deleted (unused). + </para> + </listitem> - <listitem> - <para> - <literal>Lost space</literal> - </para> + <listitem> + <para> + <literal>Lost space</literal> + </para> - <para> - If a row is updated to a shorter length, some space is - lost. This is the sum of all such losses, in bytes. - </para> - </listitem> + <para> + If a row is updated to a shorter length, some space is lost. + This is the sum of all such losses, in bytes. + </para> + </listitem> - <listitem> - <para> - <literal>Linkdata</literal> - </para> + <listitem> + <para> + <literal>Linkdata</literal> + </para> - <para> - When the dynamic table format is used, row fragments are - linked with pointers (4 to 7 bytes each). - <literal>Linkdata</literal> is the sum of the amount of - storage used by all such pointers. - </para> - </listitem> + <para> + When the dynamic table format is used, row fragments are + linked with pointers (4 to 7 bytes each). + <literal>Linkdata</literal> is the sum of the amount of + storage used by all such pointers. + </para> + </listitem> - </itemizedlist> + </itemizedlist> - <para> - If a table has been compressed with - <command>myisampack</command>, <command>myisamchk -d</command> - prints additional information about each table column. See - <xref linkend="myisampack"/>, for an example of this - information and a description of what it means. - </para> + <para> + If a table has been compressed with + <command>myisampack</command>, <command>myisamchk -d</command> + prints additional information about each table column. See + <xref linkend="myisampack"/>, for an example of this information + and a description of what it means. + </para> - </section> + </section> - <section id="maintenance-schedule"> + <section id="maintenance-schedule"> - <title>Setting Up a Table Maintenance Schedule</title> + <title>Setting Up a Table Maintenance Schedule</title> - <indexterm> - <primary>maintaining</primary> - <secondary>tables</secondary> - </indexterm> + <indexterm> + <primary>maintaining</primary> + <secondary>tables</secondary> + </indexterm> - <indexterm> - <primary>tables</primary> - <secondary>maintenance schedule</secondary> - </indexterm> + <indexterm> + <primary>tables</primary> + <secondary>maintenance schedule</secondary> + </indexterm> - <para> - It is a good idea to perform table checks on a regular basis - rather than waiting for problems to occur. One way to check - and repair <literal>MyISAM</literal> tables is with the - <literal>CHECK TABLE</literal> and <literal>REPAIR - TABLE</literal> statements. See <xref linkend="check-table"/>, - and <xref linkend="repair-table"/>. - </para> + <para> + It is a good idea to perform table checks on a regular basis + rather than waiting for problems to occur. One way to check and + repair <literal>MyISAM</literal> tables is with the + <literal>CHECK TABLE</literal> and <literal>REPAIR + TABLE</literal> statements. See <xref linkend="check-table"/>, + and <xref linkend="repair-table"/>. + </para> - <para> - Another way to check tables is to use - <command>myisamchk</command>. For maintenance purposes, you - can use <command>myisamchk -s</command>. The - <option>-s</option> option (short for - <option>--silent</option>) causes <command>myisamchk</command> - to run in silent mode, printing messages only when errors - occur. - </para> + <para> + Another way to check tables is to use + <command>myisamchk</command>. For maintenance purposes, you can + use <command>myisamchk -s</command>. The <option>-s</option> + option (short for <option>--silent</option>) causes + <command>myisamchk</command> to run in silent mode, printing + messages only when errors occur. + </para> - <indexterm> - <primary>.pid (process ID) file</primary> - </indexterm> + <indexterm> + <primary>.pid (process ID) file</primary> + </indexterm> - <para> - It is also a good idea to enable automatic - <literal>MyISAM</literal> table checking. For example, - whenever the machine has done a restart in the middle of an - update, you usually need to check each table that could have - been affected before it is used further. (These are - <quote>expected crashed tables.</quote>) To check - <literal>MyISAM</literal> tables automatically, start the - server with the <option>--myisam-recover</option> option. See - <xref linkend="server-options"/>. - </para> + <para> + It is also a good idea to enable automatic + <literal>MyISAM</literal> table checking. For example, whenever + the machine has done a restart in the middle of an update, you + usually need to check each table that could have been affected + before it is used further. (These are <quote>expected crashed + tables.</quote>) To check <literal>MyISAM</literal> tables + automatically, start the server with the + <option>--myisam-recover</option> option. See + <xref linkend="server-options"/>. + </para> - <para> - You should also check your tables regularly during normal - system operation. At MySQL AB, we run a - <command>cron</command> job to check all our important tables - once a week, using a line like this in a - <filename>crontab</filename> file: - </para> + <para> + You should also check your tables regularly during normal system + operation. At MySQL AB, we run a <command>cron</command> job to + check all our important tables once a week, using a line like + this in a <filename>crontab</filename> file: + </para> <programlisting> 35 0 * * 0 <replaceable>/path/to/myisamchk</replaceable> --fast --silent <replaceable>/path/to/datadir</replaceable>/*/*.MYI </programlisting> - <para> - This prints out information about crashed tables so that we - can examine and repair them when needed. - </para> + <para> + This prints out information about crashed tables so that we can + examine and repair them when needed. + </para> - <para> - Because we have not had any unexpectedly crashed tables - (tables that become corrupted for reasons other than hardware - trouble) for several years, once a week is more than - sufficient for us. - </para> + <para> + Because we have not had any unexpectedly crashed tables (tables + that become corrupted for reasons other than hardware trouble) + for several years, once a week is more than sufficient for us. + </para> - <para> - We recommend that to start with, you execute - <command>myisamchk -s</command> each night on all tables that - have been updated during the last 24 hours, until you come to - trust MySQL as much as we do. - </para> + <para> + We recommend that to start with, you execute <command>myisamchk + -s</command> each night on all tables that have been updated + during the last 24 hours, until you come to trust MySQL as much + as we do. + </para> - <indexterm> - <primary>tables</primary> - <secondary>defragmenting</secondary> - </indexterm> + <indexterm> + <primary>tables</primary> + <secondary>defragmenting</secondary> + </indexterm> - <para> - Normally, MySQL tables need little maintenance. If you are - performing many updates to <literal>MyISAM</literal> tables - with dynamic-sized rows (tables with - <literal>VARCHAR</literal>, <literal>BLOB</literal>, or - <literal>TEXT</literal> columns) or have tables with many - deleted rows you may want to defragment/reclaim space from the - tables from time to time. You can do this by using - <literal>OPTIMIZE TABLE</literal> on the tables in question. - Alternatively, if you can stop the <command>mysqld</command> - server for a while, change location into the data directory - and use this command while the server is stopped: - </para> + <para> + Normally, MySQL tables need little maintenance. If you are + performing many updates to <literal>MyISAM</literal> tables with + dynamic-sized rows (tables with <literal>VARCHAR</literal>, + <literal>BLOB</literal>, or <literal>TEXT</literal> columns) or + have tables with many deleted rows you may want to + defragment/reclaim space from the tables from time to time. You + can do this by using <literal>OPTIMIZE TABLE</literal> on the + tables in question. Alternatively, if you can stop the + <command>mysqld</command> server for a while, change location + into the data directory and use this command while the server is + stopped: + </para> <programlisting> shell> <userinput>myisamchk -r -s --sort-index --sort_buffer_size=16M */*.MYI</userinput> </programlisting> - </section> - </section> - </chapter> + </section> + +</chapter>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r10047 - in trunk: . it/refman-5.1 pt/refman-5.1 refman-4.1 refman-5.0 refman-5.1 refman-6.0 | paul | 27 Feb |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.995 sec. using MySQL 5.1.14-beta-log