The online copy of a database into slave.

For some time I have been thinking that the export of a large database from master into slave w/o a long breake in the production is not possible. In case you profit from LVM, it seems quite simple…
I tested the b/m solution with my database which is 233G in size.

A simple description of the idea:
Stop slave as well as master. Create snapshot for the volume where database files are stored. Start master. Run the second instance of mysql on master server which has datadir=snapshot. Copy structure and data between the “master-snapshot” and the slave. Remove snapshot. Start slave, voila…

The receipe (database name is postfix) [neither simple nor fast]
1. Prepare replication
– Master: binlog-do-db=postfix (my.cnf) (restart is required)
– Slave: replicate-do-db=postfix, replicate-wild-do-table=postfix.% (restart is required)
– Slave: skip-slave-start (my.cnf)

2. Prepare second | snapshot instance of mysql database
– Master: configure new instance
Copy all files to new localization:

cp -rpv ./mysql ./mysql_snapshot

Change some variables for snapshot instance (./mysql_snapshot/my.cnf)

# set alternative path | value
datadir=/db_storage_ssd_snapshot/data
relay-log-index=/db_binlog/slave2/mysql-relay-bin.index
relay-log=/db_binlog/slave2/mysql-relay-bin-db01
socket		= /tmp/mysql-3309.sock
port		= 3309
pid-file=/usr/local/services/mysql_snapshot/var/mysql5.1.pid
log-bin=/alternative_path

# turn off
slow_query_log=0
skip-slave-start

Copy start script and make changes to the new start script (cp -pv /etc/init.d/mysql.server /etc/init.d/mysql.server.3309 ):

# change path
lock_file_path="$lockdir/mysql-3309"
basedir=/usr/local/services/mysql_snapshot
bindir=/usr/local/services/mysql_snapshot/bin
sbindir=/usr/local/services/mysql_snapshot/bin
libexecdir=/usr/local/services/mysql_snapshot/bin

3. Replication consistency
You should ensure that replication is up to now. To do this I limit master to the special “backup” network.
You can do this with some special IP, which is known only to your slave and run the master for a while to be sure
that everything was replicated.

bind-address    = SOME.SPECIAL.IP.ADDRESS

Now you can restart master instance, ensure that slave is working.
When the position of master logs in replication does not change you can perform “stop slave;”.
Because of the fact you set skip-slave-start you do not have to worry about that the replication
is active after slave restart. You can remove the bind-address and stop master instance.
Then you are ready to make LVM snapshot.

4. Additional settings

If you copy a large database I suggest set the following parameter:

# master
mysql -S /tmp/mysql-3309.sock -uroot --password=xxx -e "SET GLOBAL net_write_timeout=31536000;"

Otherwise you can excpect the fallowing errors:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table…
[However, I suggest to copy the largest tables as a separate processes. Not actual :)]

If your mysql server >= 5.6.7 set the following variable:

innodb_read_only

In contrary (mysql < 5.6.7) I suggest to copy innodb files and logs to another localizations:

cp -rpv /db_storage_ssd_snapshot/innodb /db_storage/
innodb_data_home_dir=/db_storage/innodb
cp -rpv /db_storage_ssd_snapshot/innodb_log /db_storage/
innodb_log_group_home_dir=/db_storage/innodb_log

5. Prepare snapshot
– Master:

mkdir /db_storage_ssd_snapshot/ 
chown mysql:mysql /db_storage_ssd_snapshot/ 
lvcreate -L6G -s -n datasnapshot /dev/vg_s7v0_ssd/data
mount /dev/vg_s7v0_ssd/datasnapshot /db_storage_ssd_snapshot/ -onouuid,rw

6. Perform export
Start the snapshot master instance.

– Copy the table structure (master->slave):

 
mysqldump \
-S /tmp/mysql-3309.sock -uroot --password=xxx \
--add-drop-table=0 --skip-triggers -d -i -Q -q postfix  \
| mysql -uroot --password=xxx -h 10.0.15.206 -D postfix

– Copy the data:

mysqldump -S /tmp/mysql-3309.sock -uroot --password=xxx \
-K --skip-tz-utc --triggers=0 -t -q postfix \
| mysql -uroot --password=xxx -h 10.0.15.206 -D postfix -f 

– Complete the rest of the structure (triggers/events/procedures/functions):

mysqldump -S /tmp/mysql-3309.sock -uroot --password=xxx \
--add-drop-table=0 --triggers --events --routines -d -i -Q -q postfix  \
| mysql -uroot --password=xxx -h 10.0.15.206 -D postfix -f

– Slave: my.cnf: replicate-do-db=postfix
– Slave: restart

Now, your copy is ready. But you have to remember that after such massive operation performed on the slave, the statistics have to be rebuilt.
For MyISAM engine you can use myisamchk with analyze option, i.e.:

myisamchk -F --analyze /db_storage/data/DATABASE/*.MYI

For InnoDb engine you can use ALTER TABLE syntax (http://www.mysqlperformanceblog.com), i.e.:

ALTER TABLE `TABLE_NAME` TYPE=INNODB;

You might also need to change the status of the events into DISABLE ON SLAVE state.

  1. No comments yet.

  1. No trackbacks yet.