Status
Not open for further replies.

Tango

Moderator
Staff member
4,040
2009
1,595
14,725
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 :P
 
13 comments
Thanks for singling me out xD
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.
 
Hello Gav,


Nice post, might I propose few more steps ?

The MySQL optimize/repair command would be
Code:
/usr/bin/mysqlcheck --analyze --optimize --auto-repair _DATABASE_NAME_

If you have crashed tables best practice:
Code:
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:
Code:
mkdir -p /backup/mysql/

* Stop the webserver to stop the queries from all the server. Execute :
Code:
# 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
Code:
# 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
Code:
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.
 
I kept it as simple as possable just showing how to recover a table, a lot of people here have trouble even using SSH :D

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
 
Last edited:
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 :)


  1. how to restore ?? into new server same way ?? just extract into the same location ?
 
I would extract to a tmp folder then mv *.* to the mysql folder, then check the files are chowned correctly (Mysql owner)
 
[*]Run a repair on this file #myisamchk -r PostTable.MYI
[*]When it completes you should be good to go.
[*]Backup the folder :P[/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
 
Yes, cd to your database folder and run

PHP:
myisamchk -r *.MYI

To enable logging
PHP:
myisamchk -r *.MYI >> /tmp/log.txt
 
Hello Gav,


Nice post, might I propose few more steps ?

The MySQL optimize/repair command would be
Code:
/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
 
That isnt the command i showed you :P


If it doesnt work you can just repair a single table, you would only usually have problems with post & topic tables, so its not a big deal doing them singularly



<<EDIT

you replied while i was posting >>
 
This is a little outdated now, InnoDB is widely used now, this only applies to Myslam Tables.

EG. •Make a backup of the database folder, section will only backup Myslam tables.
 
Status
Not open for further replies.
Back
Top