Backup/Optimize/Repair/Recover Large Databases on VPS/Dedi server

  • Post author:
  • Post category:Uncategorized
  • Post comments:0 Comments

In this tutorial I will show you how to Backup/Optimize/Repair/Recover Large Databases on VPS/Dedi server.

A lot of people start a forum/site/blog and don’t know how to manage database maintaince, since there are a lot of autoposters your database soon grows in size and people can’t manage it.

After you have >1million posts the database becomes harder to manage especially if you have problems with tables crashing etc.

  1. I will show you how to backup a big database:
    • Close your board.
    • Purge your cache if your cms has one.
    • (Optional) Edit your forums config.php database connection file so no queries can be run while getting a backup.
    • Login to SSH
    • Navigate to your database in mysql folder. #cd /var/lib/mysql
    • Find your forums database folder #ls
    • Make a backup of the database folder #tar cvzf mydatabasebackup.tar.gz YourDatabaseFolder

    Now you have a complete database backup (without using MYSQL server) that can be restored in minutes if needed

  2. Repair huge tables marked as crashed/in use with ease.When you are working with huge tables you may find your server/vps hangs when trying to run a phpmyadmin/ssh table repair so you need some other method.
    Over time I have seen some big sites just start over fresh due to database errors..

    • This presumes your post table is showing ‘in use’ or ‘crashed’ and you cant repair it.
    • Edit your forums database config.php file so your site cant connect to the database.
    • Backup your database See Above
    • Login to SSH
    • restart MYSQL service mysqld restart
    • Browse to your forums MYSQL folder and display files #cd /var/lib/mysql/YourDatabaseFolder;ls
    • Find the filename for your post table with MYI extension.
    • Run a repair on this file #myisamchk -r PostTable.MYI
    • When it completes you should be good to go.
    • Backup the folder

    That is it, if you have any question, just ask! 🙂

Leave a Reply