Results 1 to 15 of 15

Thread: Mysql Backup

  1. #1
    Join Date
    Aug 2007
    Posts
    193

    Mysql Backup

    My mysql db is quite big..

    I have some prob with my OS so it needs reinstall..
    The prob is i want to backup the DB..

    All mysql db fles are stores in /var/lib/mysql/

    i wanted to know if i can copy those files and after reinstallation of OS (and with it cpanel) can i move back the files to that location so that my database is ready..

  2. #2
    Join Date
    Feb 2006
    Location
    Kusadasi, Turkey
    Posts
    3,379
    In my experience, that works. The database named "mysql" holds the username and password information of other databases, so as long as you copy all databases, restoration should be easy.
    Fraud Record - Stop Fraud Clients, Report Abusive Customers.
    █ Combine your efforts to fight misbehaving clients.

    HarzemDesign - Highest quality, well designed and carefully coded hosting designs. Not cheap though.
    █ Large and awesome portfolio, just visit and see!

  3. #3
    Join Date
    Nov 2007
    Location
    India
    Posts
    843
    Quote Originally Posted by howsthat View Post
    My mysql db is quite big..

    I have some prob with my OS so it needs reinstall..
    The prob is i want to backup the DB..

    All mysql db fles are stores in /var/lib/mysql/

    i wanted to know if i can copy those files and after reinstallation of OS (and with it cpanel) can i move back the files to that location so that my database is ready..
    better try this method mysqldump -u username -p database_name > file.sql

    this command to take bakup so mv the backup sql file to other mount point after os reload

    restore the mysql via this command

    mysql -u username -p database_name < file.sql

    it will be easy to work
    HostNotch Hosting Services 99.9% uptime Shared Hosting, Reseller Hosting
    yajur | Sales Team
    CPanel Hosting • R1 Soft • Offsite-Backup • Great Uptime
    http://hostnotch.com sales @ hostnotch.com

  4. #4
    Join Date
    Sep 2007
    Posts
    369

    *

    Quote Originally Posted by howsthat View Post
    My mysql db is quite big..

    I have some prob with my OS so it needs reinstall..
    The prob is i want to backup the DB..

    All mysql db fles are stores in /var/lib/mysql/

    i wanted to know if i can copy those files and after reinstallation of OS (and with it cpanel) can i move back the files to that location so that my database is ready..
    Yes, its possible, but

    1. stop mysql daemon
    2. tar -jcvf mysql-backup.tar.bz2 /var/lib/mysql
    3. take backup of /etc/my.cnf
    4. transfer mysql-backup.tar.bz2 and my.cnf to new server via rsync save your time.
    5. trun up new server and stop mysql daemon, make sure it will not be running.
    6. restore your backup under /var/lib/mysql
    7. copy my.cnf /etc
    8. restart mysql daemon.

    I hope it helps.

    If you want to verify above steps you can test on one database small.
    Thanks,
    Noman
    noman@linuxonsupport.com
    O Canada, we stand on guard for thee

  5. #5
    Join Date
    Dec 2005
    Posts
    40
    Thats not true what u said to copy and paste the /var/lib/mysql cause it depends of the type of Database.

    If you have MyIsam you can do that but if you have InoDB you cant do that cause u need transaction logs.

    I prefere the way that some user post here, try to make "dumps" with mysql dump.

    There are a tons of script that can do automatically. So maybe u should look into internet for a script.(it depends in how many database u got)

    Best redard.

    (Sorry for my english).

  6. #6
    Join Date
    Jul 2009
    Posts
    212
    As cPanel,there is a feature to backup the whole DB to the server or a remote server.You can check out there.
    Also,ask your host company about the backups.They always provide a daily backup for their customers,except dedicated server users.
    Hope is helps.

  7. #7
    Join Date
    Sep 2007
    Posts
    369

    *

    Quote Originally Posted by Bokudan View Post
    Thats not true what u said to copy and paste the /var/lib/mysql cause it depends of the type of Database.

    If you have MyIsam you can do that but if you have InoDB you cant do that cause u need transaction logs.

    I prefere the way that some user post here, try to make "dumps" with mysql dump.

    There are a tons of script that can do automatically. So maybe u should look into internet for a script.(it depends in how many database u got)

    Best redard.

    (Sorry for my english).
    No that is not correct i believe, i did that number of times i did not face any problem.
    Thanks,
    Noman
    noman@linuxonsupport.com
    O Canada, we stand on guard for thee

  8. #8
    Join Date
    Dec 2005
    Posts
    40
    But did u try with InnoDB and MyIsam ?

    Cause i test what i said and really there was some problems doing that

  9. #9
    Join Date
    Feb 2006
    Location
    Kusadasi, Turkey
    Posts
    3,379
    Quote Originally Posted by Bokudan View Post
    But did u try with InnoDB and MyIsam ?

    Cause i test what i said and really there was some problems doing that
    Have you stopped mysql before attempting to copy innodb tables?
    Fraud Record - Stop Fraud Clients, Report Abusive Customers.
    █ Combine your efforts to fight misbehaving clients.

    HarzemDesign - Highest quality, well designed and carefully coded hosting designs. Not cheap though.
    █ Large and awesome portfolio, just visit and see!

  10. #10
    Join Date
    Dec 2005
    Posts
    40
    Sure ... i play with innobd with mysql stopped.

  11. #11
    Join Date
    May 2007
    Location
    Ukraine
    Posts
    161
    If mysqld has been stopped, it is definitely safe to backup mysql by packing /var/lib/mysql (witn myisam and innodb files).
    You can even unpack it on another server and it will work ( ofcourse, there must be the same mysql version installed and the same server architecture)
    But this is not effective way for backup, and it is not recommended.

    Among others reasons, if innodb datafile is not optimized, it can be of very large size,

    Why not to use the suggested methods and go with dump-restore approach?

    Do this on source server to backup all databases at once into single backup file:
    Code:
    mysqldump --all-databases --opt | gzip > mysqldump-`date +%F`.sql.gz
    Then copy this archive to target server with mysql installed, and restore from dump:

    Code:
    zcat mysqldump-2009-09-01.sql.gz | mysql -u root -p <ROOT_PWD>
    this will restore your databases and user privileges.
    just restart mysqld to apply privileges and that is it.

  12. #12
    Join Date
    Sep 2007
    Posts
    369

    *

    Quote Originally Posted by Bokudan View Post
    But did u try with InnoDB and MyIsam ?

    Cause i test what i said and really there was some problems doing that

    Yes, many times thats why wrote steps, if you face some problem, please post the error number or some wordings of errors.
    Thanks,
    Noman
    noman@linuxonsupport.com
    O Canada, we stand on guard for thee

  13. #13
    Join Date
    Aug 2007
    Posts
    193
    currently have done both taken a dump and folder backup too..
    The prob is that prev when i had taken a dump there was some prob in restoring the DB so didnt want to risk it this time..

  14. #14
    Join Date
    Sep 2007
    Posts
    369

    *

    Quote Originally Posted by howsthat View Post
    currently have done both taken a dump and folder backup too..
    The prob is that prev when i had taken a dump there was some prob in restoring the DB so didnt want to risk it this time..
    is mysql version is same for new and old host?? check the steps of restoring that i told you.
    Thanks,
    Noman
    noman@linuxonsupport.com
    O Canada, we stand on guard for thee

  15. #15
    % mysqldump --all-databases -u root -p > ~/Desktop/databases.sql

    This will create a text file called "databases.sql" on your desktop, and the file will contain all of the commands you need to re-create your databases on your new server.

    Then, to load the new databas, just copy the *.sql files over, set up mysql, and use this command in terminal:

    % mysql -u root -p < /path/to/mysql_databases.sql

    That will get your data back into database. You can then use your mysql terminal to set up the definitions that are required.
    Windows VPS | Linux Hybrid Server | 99.9% Uptime
    http://www.odishahosting.com ( USA DC)
    http://www.odishahosting.in ( INDIA DC)

Similar Threads

  1. Need Help, I moved Mysql from /var to /backup/mysql, mysql is no longer working
    By pcnet21 in forum Hosting Security and Technology
    Replies: 4
    Last Post: 05-09-2009, 01:58 AM
  2. Backup cpanel have problem with backup mysql
    By robocap in forum Hosting Security and Technology
    Replies: 4
    Last Post: 09-01-2008, 11:30 PM
  3. mysql issue - making backup and resoting backup from shell
    By kimrari in forum Hosting Security and Technology
    Replies: 7
    Last Post: 06-24-2003, 01:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •