Database Maintenance

Maintenance There are two functions of MySQL used regularry for maintenance on MySQL tables and databases. These functions can be found via phpMyAdmin in your hosting account, however on large databases doing so from a web interface may prove frustrating. You can perform the following commands via a cron job or by executing a file via SSH shell access. You will need to change the variables to your information. &lt;username&gt; = Your MySQL user name which has access to the database you which to perform maintenance on. &lt;password&gt; = The password to said MySQL username. &lt;hostname&gt; = The hostname for your MySQL server. (Usually something like databasename.yourdomain.com or whatever you set it up as) The first command runs the REPAIR function on every table in your database. This will ensure there are no errors in your tables. The second command runs the OPTIMIZE function on every table in your database. This essentialyl degrags the tables in your database and reduces overhead in the tables. This is also a command you can run via cron job or SSH shell access. Database Backup Another useful maintenance command run regularry by site administrators is a command to backup your MySQL database to a folder, then gzip the backup to save space and remove the original unzipped backup. The backup file will be the database name followed by the date in YYYY.MM.DD format. For this function, you must edit the variables the same as the above example. You must also have a directory made for your backups to go into. Preferabbly this should be a non-web accessable. Change /path/to/non/web/folder/ to one of your choosing.