Backup/Repair Huge Database's VPS/DED

[Gavo]

Backup/optimize/repair/recover large Databases on VPS/Ded server


A lot of people start a forum and dont know how to manage database maintaince, since there are a lot of autoposters your database soon grows in size and people cant 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 databse with ease, waz-warez database is 15GB and takes 4-5 minutes.
   
   
   • 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 (snow hehe) eg.warezscene and this may have fixed there database.
   I recoverd warezusa database just over a week ago
   
   • 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

Gavo Reviewed by Gavo on 10th Feb 2011. Backup/Repair Huge Database's VPS/DED Backup/optimize/repair/recover large Databases on VPS/Ded server A lot of people start a forum and dont know how to manage database maintaince, since there are a lot of autoposters your database soon grows in size and people cant 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 databse with ease, waz-warez database is 15GB and takes 4-5 minutes. Rating: 5

[snowmanrene]

Thanks for singling me out
Anyway I knew about the backup method, but the repairing all the crashed tables just proved too much. Guess I can try this next time

Thanks G.

[ab1ko]

Hello Gav,


Nice post, might I propose few more steps ?

The MySQL optimize/repair command would be

/usr/bin/mysqlcheck --analyze --optimize --auto-repair _DATABASE_NAME_

If you have crashed tables best practice:

myisamchk --force --fast --update-state --key_buffer_size=64M --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M /var/lib/mysql/_DATABASE_NAME_/*.MYI

If you have huge databases 1GB+ in size, either shutdown the webserver so no queries are going to the MySQL server what so ever, or edit the MySQL connection script if you have one site only on the server.

Also, best practice would be to stop the MySQL server when copying/moving/tarring raw MySQL database files, as no more data is written/read to the database.


Let's make it interesting now:
Preparation steps:

Let's make a backup location for your backups to be sorted:

mkdir -p /backup/mysql/

* Stop the webserver to stop the queries from all the server. Execute :

# RHEL systems:
service httpd stop 

# Debian/Ubuntu
/etc/init.d/apache2 stop

* Stop the MySQL server so no more I/O will be done to the raw MySQL files

# RHEL systems
service mysqld stop

# cPanel server
service mysql stop

# Debian/Ubuntu/other init.d systems
/etc/init.d/mysqld stop

* Go to /var/lib/mysql/, create archive of the files and moce them to a backup location

cd /var/lib/mysql/
tar czfv  _DATABASE_NAME_$(date +"%m-%d-%y").tar.gz  _DATABASE_NAME/
mv _DATABASE_NAME_02-10-11.tar.gz /backup/mysql/

* Start all system services that we've stopped

A backup for the database will executed with data suffix, so you know when the backup is dated, it is located in /backup/mysql/ directory, so you can keep a retention of few database backups.

[Gavo]

I kept it as simple as possable just showing how to recover a table, a lot of people here have trouble even using SSH

of course you can use

#myisamchk -r *.MYI >> /tmp/log.txt

to check/repair all tables with log but i thought if someone is on a small overloaded VPS it may cause problems & i not sure what would happen if the repair was interrupted

[vgnheart]

good job Gav0

[onel0ve]

Backup/optimize/repair/recover large Databases on VPS/Ded server


A lot of people start a forum and dont know how to manage database maintaince, since there are a lot of autoposters your database soon grows in size and people cant manage it.

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


[list=1][*]I will show you how to backup a big databse with ease, waz-warez database is 15GB and takes 4-5 minutes.

• 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

how to restore ?? into new server same way ?? just extract into the same location ?

[Gavo]

I would extract to a tmp folder then mv *.* to the mysql folder, then check the files are chowned correctly (Mysql owner)

[onel0ve]

[*]Run a repair on this file #myisamchk -r PostTable.MYI[*]When it completes you should be good to go.[*]Backup the folder [/list][/list]

PostTable.MYI ? you mean all table which have MYI extension ?
how to repair all MYI extension table .in one command line

myisamchk -r *.MYI ?like

[Gavo]

Yes, cd to your database folder and run

myisamchk -r *.MYI 

To enable logging

myisamchk -r *.MYI >> /tmp/log.txt 

[onel0ve]

Hello Gav,


Nice post, might I propose few more steps ?

The MySQL optimize/repair command would be

/usr/bin/mysqlcheck --analyze --optimize --auto-repair _DATABASE_NAME_

[root@server ~]# /usr/bin/mysqlcheck --analyze --optimize --auto-repair your db
Error: /usr/bin/mysqlcheck doesn't support multiple contradicting commands.

not working for me