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.
- 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
- 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! 🙂