| Posted by Jcink, 07-12-2007, 01:17 PM | | Hi,
I have two hard disks, and I would like to move all of my MySQL data onto the second drive. My dilema is this; what is the best way to go about doing this? The tutorial I followed last night had me use cp to copy the /var/lib/mysql folder over to the second drive. However, it took ages to move one of my databases. It's a forum hosting database which has tons and tons of tables. It ran for about 5 hours and still didn't finish because I decided to stop it right there. I know it was working; but still, it took forever and I had to put it back up.
What would you do? Do you recommend say, copying just the 'mysql' database that has the users, and then taking a dump of all databases with mysqldump and moving it that way?
Also, is there any way using the cp command to continue what I started last night, but not write a database file if it hasn't chnaged at all?
Thanks for your time,
Dan
|
| Posted by Patrick, 07-12-2007, 01:20 PM | | How big is the MySQL directory?
du -sh /var/lib/mysql
|
| Posted by Jcink, 07-12-2007, 01:30 PM | | I'll check later when the system isn't so busy, but it's got to be around 10GB or more as an estimate.
|
| Posted by ISPserver, 07-12-2007, 04:01 PM | | It's strange, beckouse cp ~10gb from one hdd to other it's operation long for 10-25 min. may be hard very busy. Or you have many small tables.
I do not think what mysqldump will be faster.
See to -u, --update parameter of cp:
" Do not copy a nondirectory that has an existing destination with the same or newer modification time."
|
| Posted by Jcink, 07-12-2007, 04:20 PM | | That's pretty much the case, yeah. There's thousands of small tables. Copying a 5gb file i had itself was not slow at all, but moving all these little files is a killer.
There was definitely no way the hard drive could be busy because I turned off the entire server processes and whatnot, so nothing else could go on.
Thanks for the cp tip. so I can try starting this again and pick up where I left off I suppose later.
|
| Posted by Patrick, 07-12-2007, 09:42 PM | | A few months ago, I moved roughly ~400 databases around 7-8 GB in size, a few with a thousand+ tables or so, and the whole process took a little under an hour.
What type of hard drives are you using?
|
| Posted by MaximSupport, 07-12-2007, 10:45 PM | | Dear Dan8080,
I prefer you to Dump Databases as if you have InnoDB Tables then copying from one location to another location may crash your tables.
This will work if you have MyISAM Tables.
Best Regards.
|
| Posted by Lightwave, 07-12-2007, 11:38 PM | | There was another poor guy recently trying to move a 10GB database from one machine to another who was completely frustrated because his attempts were running into similar problems.
My long term suggestion was of course to not use a standard ext2/3 filesystem. Use something better designed for thousands/hundreds of thousands of files in a single directory like xfs (and maybe reiserfs or jsf but i've not much experience with them.)
"Large Directories – Most [older] file systems use linear searches, going through a directory entry by entry, to locate a certain file. This becomes very inefficient when the number of files in a directory exceeds a few thousand."
|
| Posted by SparkSupport, 07-13-2007, 05:11 AM | | You can make use of rsync to copy the database and during off peak hours you have to stop MySQL server and rsync once again.
|
|
 Add to Favourites
 Print this Article
|
