runtime/faq/mysql maintenance
From JumpBox Documentation Wiki
MySQL Maintenance
 |
Caution The instructions or information on this page may damage your JumpBox if performed incorrectly or used carelessly. It is advised that you test these instructions on a test JumpBox to ensure that you understand the process and that these instructions apply to your specific JumpBox release. Proceed with extreme caution. (details) |
Over time, it is possible that your JumpBox might encounter a database problem. These can happen if the JumpBox is powered off unexpectedly, the host is powered off unexpectedly, or there is an application error. Most of these cases can be repaired. So most importantly, don't panic and proceed with caution. When in doubt, ask for help.
Connect to the Database
First off, SSH into your JumpBox as admin and then connect to your MySQL database as the root user using the Local Access method described in "How can I access my MySQL databases?". Once you have successfully connected to MySQL, take a look at the databases present. In this case I use Joomla 1.5 as an example. I show the databases, then I connect to the 'joomla15' database. The actual commands are bold.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | joomla15 | | mysql | +--------------------+ 3 rows in set (0.00 sec) mysql> use joomla15; Database changed
Checking Table Status
Now that you are connected to the database of choice, show the status of all of the tables:
mysql> show table status; +-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | jos_banner | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2009-03-30 14:25:22 | 2009-03-30 14:25:22 | 2009-03-30 14:25:22 | utf8_general_ci | NULL | | | | jos_bannerclient | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2009-03-30 14:25:22 | 2009-03-30 14:25:22 | NULL | utf8_general_ci | NULL | | | | jos_bannertrack | MyISAM | 10 | Fixed | 0 | 0 | 0 | 3377699720527871 | 1024 | 0 | NULL | 2009-03-30 14:25:22 | 2009-03-30 14:25:22 | NULL | utf8_general_ci | NULL | | | | jos_categories | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2009-03-30 14:25:22 | 2009-03-30 14:25:22 | 2009-03-30 14:25:22 | utf8_general_ci | NULL | | |
or to get longer but non-wrapped output
mysql> show table status\G
*************************** 1. row ***************************
Name: jos_banner
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: 1
Create_time: 2009-03-30 14:25:22
Update_time: 2009-03-30 14:25:22
Check_time: 2009-03-30 14:25:22
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: jos_bannerclient
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: 1
Create_time: 2009-03-30 14:25:22
Update_time: 2009-03-30 14:25:22
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
Scan this output for mention of a crashed table or any other sort of error. The following line is an example comment from a crashed table:
Table './wordpress/wp_comments' is marked as crashed and should be repaired
If you find something, this would be a good time to contact JumpBox, the application developer or your contractor for advice. If you want to proceed, do so with caution. Make a JumpBox backup or use mysqldump to make sure your database data is safe and you can restore in the event of any trouble.
Repairing a damaged table
This is where it gets tricky. The various causes of damaged tables mean that there is no single way to repair them. This is where having a backup, reading references and thinking carefully are crucial. In the case of a simple crash, users have been able to repair that table with the following commands:
mysql> use wordpress; mysql> show table status; ... mysql> CHECK TABLE wp_comments FAST QUICK; +-----------------------+-------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------+-------+----------+---------------------------------------------------------+ | wordpress.wp_comments | check | warning | Table is marked as crashed | | wordpress.wp_comments | check | warning | 1 client is using or hasn't closed the table properly | | wordpress.wp_comments | check | error | Size of datafile is: 3179180 Should be: 3179804 | | wordpress.wp_comments | check | error | Corrupt | +-----------------------+-------+----------+---------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> REPAIR TABLE wp_comments QUICK; +-----------------------+--------+----------+------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------+--------+----------+------------------------------------------+ | wordpress.wp_comments | repair | warning | Number of rows changed from 6616 to 6614 | | wordpress.wp_comments | repair | status | OK | +-----------------------+--------+----------+------------------------------------------+ 2 rows in set (0.13 sec) mysql> CHECK TABLE wp_comments FAST QUICK; +-----------------------+-------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------+-------+----------+-----------------------------+ | wordpress.wp_comments | check | status | Table is already up to date | +-----------------------+-------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> \q
Where the REPAIR TABLE wp_comments QUICK; is the actual repair command. We will extend this section as more information is available.
