How do I replicate a MySql server's data without using mysqldump?

Question

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

Answer 1

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.

Answer 2

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.