1. Tango

    Tango Super Moderator Staff Member

    Jul 9, 2009
    3,236
    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
     
  2. snowmanrene

    snowmanrene Well-Known Member

    Dec 17, 2008
    1,590
    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.
     
  3. ab1ko

    ab1ko Active Member

    Feb 9, 2011
    33
    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.
     
  4. Tango

    Tango Super Moderator Staff Member

    Jul 9, 2009
    3,236
    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: Feb 10, 2011
  5. Cheetah

    Cheetah Well-Known Member

    Jun 19, 2008
    1,387
  6. onel0ve

    onel0ve Well-Known Member Web Host

    Aug 19, 2010
    1,275


    1. how to restore ?? into new server same way ?? just extract into the same location ?
     
  7. Tango

    Tango Super Moderator Staff Member

    Jul 9, 2009
    3,236
    I would extract to a tmp folder then mv *.* to the mysql folder, then check the files are chowned correctly (Mysql owner)
     
  8. onel0ve

    onel0ve Well-Known Member Web Host

    Aug 19, 2010
    1,275

    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
     
  9. Tango

    Tango Super Moderator Staff Member

    Jul 9, 2009
    3,236
    Yes, cd to your database folder and run

    PHP:
    myisamchk -*.MYI
    To enable logging
    PHP:
    myisamchk -*.MYI >> /tmp/log.txt
     
  10. onel0ve

    onel0ve Well-Known Member Web Host

    Aug 19, 2010
    1,275
    [[email protected] ~]# /usr/bin/mysqlcheck --analyze --optimize --auto-repair your db
    Error: /usr/bin/mysqlcheck doesn't support multiple contradicting commands.

    not working for me
     
  11. onel0ve

    onel0ve Well-Known Member Web Host

    Aug 19, 2010
    1,275
    thanks working smoothly
     
  12. Tango

    Tango Super Moderator Staff Member

    Jul 9, 2009
    3,236
    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 >>
     
  13. ThumperTM

    ThumperTM Well-Known Member

    Jan 4, 2011
    1,823
    Thanks Gavo!
     
  14. Tango

    Tango Super Moderator Staff Member

    Jul 9, 2009
    3,236
    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.
     

Share This Page