[ Pobierz całość w formacie PDF ]
.If you get weird errors when repairing (such as out ofmemory errors), or if myisamchk crashes, go to Stage 3.Stage 3: Difficult repairYou should only reach this stage if the first 16K block in the index file isdestroyed or contains incorrect information, or if the index file ismissing.In this case, it's necessary to create a new index file.Do so asfollows:Move the data file to some safe place.Use the table description file to create new (empty) data and index files:shell> mysql db_namemysql> SET AUTOCOMMIT=1;mysql> TRUNCATE TABLE table_name;mysql> quitIf your SQL version doesn't have TRUNCATE TABLE, use DELETE FROMtable_name instead.Copy the old data file back onto the newly created data file.(Don't just move the old file back onto the new file; you want to retaina copy in case something goes wrong.)Go back to Stage 2.myisamchk -r -q should work now.(This shouldn'tbe an endless loop.)Stage 4: Very difficult repairYou should reach this stage only if the description file has alsocrashed.That should never happen, because the description file isn't changedafter the table is created:Restore the description file from a backup and go back to Stage 3.You canalso restore the index file and go back to Stage 2.In the latter case, youshould start with myisamchk -r.If you don't have a backup but know exactly how the table was created, createa copy of the table in another database.Remove the new data file, then movethe description and index files from the other database to your crasheddatabase.This gives you new description and index files, but leavesthe data file alone.Go back to Stage 2 and attempt to reconstructthe index file.16.5.3 Table OptimizationTo coalesce fragmented records and eliminate wasted space resulting fromdeleting or updating records, run myisamchk in recovery mode:shell> myisamchk -r tbl_nameYou can optimize a table in the same way using the SQL OPTIMIZE TABLEstatement.OPTIMIZE TABLE does a repair of the table, a keyanalyzes and also sorts the index tree to give faster key lookups.There is also no possibility of unwanted interaction between a utilityand the server, because the server does all the work when you useOPTIMIZE TABLE.See section 7.11 OPTIMIZE TABLE Syntax.myisamchk also has a number of other options you can use to improvethe performance of a table:-S, --sort-index-R index_num, --sort-records=index_num-a, --analyzeFor a full description of the option.See section 16.1.1 myisamchk Invocation Syntax.16.6 Log file MaintenanceMySQL has a lot of log files which make it easy to see what isgoing.See section 23 The MySQL log files.One must however from time to time clean upafter MysQL to ensure that the logs don't take up too much diskspace.When using MySQL with log files, you will, from time to time,want to remove/backup old log files and tell MySQL to startlogging on new files.See section 22.2 Database Backups.On a Linux (Redhat) installation, you can use themysql-log-rotate script for this.If you installed MySQLfrom an RPM distribution, the script should have been installedautomatically.Note that you should be careful with this if you are usingthe log for replication!On other systems you must install a short script yourself that youstart from cron to handle log files.You can force MySQL to start using new log files by usingmysqladmin flush-logs or by using the SQL command FLUSH LOGS.If you are using MySQL Version 3.21 you must use mysqladmin refresh.The above command does the following:If standard logging (--log) or slow query logging(--log-slow-queries) is used, closes and reopens the log file.(`mysql.log' and ``hostname`-slow.log' as default).If update logging (--log-update) is used, closes the update log andopens a new log file with a higher sequence number.If you are using only an update log, you only have to flush the logs and thenmove away the old update log files to a backup.If you are using the normal logging, you can do something like:shell> cd mysql-data-directoryshell> mv mysql.log mysql.oldshell> mysqladmin flush-logsand then take a backup and remove `mysql.old'.Go to the first, previous, next, last section, table of contents
[ Pobierz całość w formacie PDF ]