Results 1 to 15 of 15
Thread: Mysql Backup
-
09-01-2009, 03:19 AM #1Junior Guru
- 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..
-
09-01-2009, 03:33 AM #2Web Hosting Master
- 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!
-
09-01-2009, 03:43 AM #3Web Hosting Master
- Join Date
- Nov 2007
- Location
- India
- Posts
- 843
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
-
09-01-2009, 04:21 AM #4Aspiring Evangelist
- Join Date
- Sep 2007
- Posts
- 369
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.
-
09-01-2009, 04:40 AM #5Junior Guru Wannabe
- 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).
-
09-01-2009, 04:47 AM #6Junior Guru
- 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.
-
09-01-2009, 04:47 AM #7Aspiring Evangelist
- Join Date
- Sep 2007
- Posts
- 369
-
09-01-2009, 05:23 AM #8Junior Guru Wannabe
- 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
-
09-01-2009, 05:28 AM #9Web Hosting Master
- Join Date
- Feb 2006
- Location
- Kusadasi, Turkey
- Posts
- 3,379
█ 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!
-
09-01-2009, 05:30 AM #10Junior Guru Wannabe
- Join Date
- Dec 2005
- Posts
- 40
Sure ... i play with innobd with mysql stopped.
-
09-01-2009, 06:17 AM #11WHT Addict
- 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
Code:zcat mysqldump-2009-09-01.sql.gz | mysql -u root -p <ROOT_PWD>
just restart mysqld to apply privileges and that is it.
-
09-01-2009, 06:43 AM #12Aspiring Evangelist
- Join Date
- Sep 2007
- Posts
- 369
-
09-01-2009, 07:04 AM #13Junior Guru
- 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..
-
09-01-2009, 07:20 AM #14Aspiring Evangelist
- Join Date
- Sep 2007
- Posts
- 369
-
09-01-2009, 09:46 AM #15Junior Guru Wannabe
- Join Date
- Jun 2009
- Posts
- 72
% 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
-
Need Help, I moved Mysql from /var to /backup/mysql, mysql is no longer working
By pcnet21 in forum Hosting Security and TechnologyReplies: 4Last Post: 05-09-2009, 01:58 AM -
Backup cpanel have problem with backup mysql
By robocap in forum Hosting Security and TechnologyReplies: 4Last Post: 09-01-2008, 11:30 PM -
mysql issue - making backup and resoting backup from shell
By kimrari in forum Hosting Security and TechnologyReplies: 7Last Post: 06-24-2003, 01:08 AM