Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a working server with a lot of data on it. I've built a new server that will be lots faster. Both are running x686 code on Debian Squeeze.

A quick test using one of the tables shows that it takes about ten days to load up the output of a simple mysqldump dbname tablename > table.sql mysql dbname < table.sql

Since I have lots of tables with lots of data, this isn't going to work. It would take until Christmas to load them all up.

Is there any way to just copy the binary files that mysqld is using on the first server to the disks on the second and use them without the dump/reload?

If not, are the dump or load options that will get me a two order of magnitude speed improvement?

A typical table has 10 million to 100 million records. Records lengths vary from 100 bytes to 10K bytes per record.

It would be nice to have the old slow server be a replication client of the new fast one, but I think I have to get the data up first.

Thanks

share|improve this question
1  
I wrote an answer based on doing an rsync of an entire /var/lib/mysql folder. I just noticed that you are only copying a table. Is the table InnoDB or MyISAM ? I hope it's MyISAM because my answer cannot be applied to InnoDB. –  RolandoMySQLDBA Aug 19 '12 at 1:15
    
Never mind, just read it again. I think my answer should work on /var/lib/mysql regardless which storage engine is used. –  RolandoMySQLDBA Aug 19 '12 at 1:16
1  
This question makes me wonder: have you ever tested your backup strategy? What will you do if your original server crashes beyond repair today? –  Jeff Aug 19 '12 at 9:05
    
@jeff, ouch. The answer is no. The longer answer is that we are just getting ready to go from development to production, and this is one of the key questions. –  Pat Farrell Aug 19 '12 at 19:14
add comment

2 Answers 2

The question essentially equals this one: "what is a fast backup + recovery method for MySQL". This is because setting up MySQL replication is essentially taking a backup of some sorts, and opening it on another machine (and then syncing the slave with the master, which is a simple operation).

So it boils down to the fact mysqldump is too slow for you (makes sense). I've just answered the same here, but here are the highlights:

  • Percona Xtrabackup is a common and tested (and free) backup tool. It is of interest to you if you're using InnoDB since it makes a hot backup of the InnoDB tablespace, with small impact on the running server. The resulting backup reflects the time at which the backup process ended, which means shorter initial lag for your slave.

  • File system snapshots, via filesystems such as ZFS, or via LVM, or otherwise disk based snapshots, are all good solutions. Some (LVM in particular) making for more load than others.

What's common to the above solutions is that they make for a binary backup, not a logical one.

For your next question - yes. You can use MyDumper, which makes for a multithreaded backup (complemented by the multithreaded MyLoader tool). This backup is logical, and uses mysqldump internally, concurrently. It is typically a few times faster than standard mysqldump.

share|improve this answer
    
Thanks. A "few times faster" is not going to be enough, I need 100 times or more. I'll look at Xtrabackup. Gotta say that the website is not terribly clear. –  Pat Farrell Aug 18 '12 at 19:45
    
I've not found that the speed of mysqldump is a big problem. I can dump all the tables in a few hours. Speeding that up would be cool and nice, so I'll look at MyDumper. But the real problem is that loading the data back is 1000 or more times slower than dumping. –  Pat Farrell Aug 18 '12 at 19:53
    
You're most probably better off with Xtrabackup or file system snapshot, then. –  Shlomi Noach Aug 19 '12 at 3:24
add comment

Under normal circumstances I would recommend setting up parallel mysqldumps ( Mysql backup strategies? ). However, it will be ineffective an against gigantic tables. Why ?

The recreation of a table in a mysqldump basically does this

  • CREATE TABLE
  • LOCK TABLE ... WRITE
  • ALTER TABLE ... DISABLE KEYS (Applies to MyISAM only)
  • multiple extended INSERT INTO
  • ALTER TABLE ... ENABLE KEYS (Applies to MyISAM only)
  • UNLOCK TABLES

A giant MyISAM table with either a lot of indexes or indexes with large columns becomes the instant bottleneck of the entire reload, even if you dump each individual table.

What can you do. Well, there is only one thing I would recommend. RSYNC !!! What possible advantage would rsync have?

ADVANTAGE #1 : There would be no processing of SQL. Your strict focus would be just copying (Drawback : Increased Disk I/O on Master during any given rsync)

ADVANTAGE #2 : There would be minimal downtime. You will need to shutdown mysql on the source server, but you can minimize how long that downtime can be.

Here is the basic idea: You could run rsync against /var/lib/mysql on a master and copy it to /var/lib/mysql on a slave. Of course, you need to run rsync several times. Before copying make sure you hose all binary logs and start from scratch.

NOTE : In case you don't have binary logging enabled : Before running anything, please make sure binary logs are written in /var/lib/mysql on both master and slave by having something like the following in /etc/my.cnf:

[mysqld]
log-bin=mysql-bin

The following script will shutdown mysql for the final rsync:

SOURCE_DATADIR=`mysql -u... -p... -ANe"SHOW VARIABLES LIKE 'datadir'" | awk '{print $2}'`
TARGET_DATADIR=${SOURCE_DATADIR}
mysql -u... -p... -e"RESET MASTER;"
RSYNCSTOTRY=7
cd ${SOURCE_DATADIR}
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:${TARGET_DATADIR}/.
    sleep 60
done
service mysql stop
rsync -r * slaveserver:${TARGET_DATADIR}/.
service mysql start

That's all for the rsync portion from the master. What about the Slave ???

Before you start mysql on the slave, you need to have the log file and log position from the master. The binary logs you copied have you need, particularly the last binary log. Here is how you get it on the slave:

cd /var/lib/mysql (or the datadir for your OS)
for X=`ls -l mysql-bin.0* | awk '{print $5}'`
do
    LOGFIL=${X}
done
LOGPOS=`ls -l ${LOGFIL} | awk '{print $9}'`
echo "Master Log File ${LOGFIL} Position ${LOGPOS}"

You can trust these numbers because you copied them personally from the master. Now that you have the master log and position, you can start up mysql on the slave and setup replication using the log file and log position that was just reported.

CAVEAT #1 : I cannot make any promises on the the performance of this method. This is an option nonetheless.

CAVEAT #2 : Make sure you are copying to another server running the same version of MySQL (5.0 - > 5.0,5.1 - > 5.1,5.5 - > 5.5)

CAVEAT #3 : Make sure you are copying to the same DATADIR on Slave Server. Otherwise, set TARGET_DATADIR to the folder of your choice.

share|improve this answer
    
I did the copy using rsync, but mysqld would not properly start up using the copied data. I'll read carefully through your instructions and see what I missed. –  Pat Farrell Aug 19 '12 at 1:20
    
I have been doing this copying for one of my clients for many years. Please make sure you have every option in my.cnf from the Master server enabled in the Slave server. –  RolandoMySQLDBA Aug 19 '12 at 1:23
    
Does your "cd /var/lib/mysql" assume the default location? I trust it should be updated to suit the real location of all the binaries. –  Pat Farrell Aug 19 '12 at 1:35
    
Good point. /var/lib/mysql is Linux. MySQL for other operating systems may be different folders. I updated my answer to reflect this. –  RolandoMySQLDBA Aug 19 '12 at 1:44
1  
@Rolando, no, this cannot work with InnoDB unless you're using Google Patches with InnoDB write disabled. See, InnoDB keeps writing even with FLUSH TABLES WITH READ LOCK, so youre rsync can be inconsistent. See also this. –  Shlomi Noach Aug 19 '12 at 3:50
show 8 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.