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…
\nI tested the b\/m solution with my database which is 233G in size.<\/p>\n
A simple description of the idea:<\/strong> The receipe (database name is postfix) [neither simple nor fast]<\/strong> 2. Prepare second | snapshot instance of mysql database<\/em> Change some variables for snapshot instance (.\/mysql_snapshot\/my.cnf)<\/p>\n Copy start script and make changes to the new start script (cp -pv \/etc\/init.d\/mysql.server \/etc\/init.d\/mysql.server.3309 ):<\/p>\n 3. Replication consistency<\/em> Now you can restart master instance, ensure that slave is working. 4. Additional settings<\/em><\/p>\n If you copy a large database I suggest set the following parameter:<\/p>\n Otherwise you can excpect the fallowing errors: If your mysql server >= 5.6.7 set the following variable:<\/p>\n In contrary (mysql < 5.6.7) I suggest to copy innodb files and logs to another localizations:\n\n\n 5. Prepare snapshot<\/em> 6. Perform export<\/em> – Copy the table structure (master->slave):<\/p>\n – Copy the data: <\/p>\n – Complete the rest of the structure (triggers\/events\/procedures\/functions):<\/p>\n – Slave: my.cnf: replicate-do-db=postfix 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.
\nStop 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…<\/p>\n
\n1. Prepare replication<\/em>
\n– Master: binlog-do-db=postfix (my.cnf) (restart is required)
\n– Slave: replicate-do-db=postfix, replicate-wild-do-table=postfix.% (restart is required)
\n– Slave: skip-slave-start (my.cnf)<\/p>\n
\n– Master: configure new instance
\nCopy all files to new localization:<\/p>\n\r\ncp -rpv .\/mysql .\/mysql_snapshot\r\n<\/pre>\n
\r\n# set alternative path | value\r\ndatadir=\/db_storage_ssd_snapshot\/data\r\nrelay-log-index=\/db_binlog\/slave2\/mysql-relay-bin.index\r\nrelay-log=\/db_binlog\/slave2\/mysql-relay-bin-db01\r\nsocket\t\t= \/tmp\/mysql-3309.sock\r\nport\t\t= 3309\r\npid-file=\/usr\/local\/services\/mysql_snapshot\/var\/mysql5.1.pid\r\nlog-bin=\/alternative_path\r\n\r\n# turn off\r\nslow_query_log=0\r\nskip-slave-start\r\n<\/pre>\n
\r\n# change path\r\nlock_file_path=\"$lockdir\/mysql-3309\"\r\nbasedir=\/usr\/local\/services\/mysql_snapshot\r\nbindir=\/usr\/local\/services\/mysql_snapshot\/bin\r\nsbindir=\/usr\/local\/services\/mysql_snapshot\/bin\r\nlibexecdir=\/usr\/local\/services\/mysql_snapshot\/bin\r\n<\/pre>\n
\nYou should ensure that replication is up to now. To do this I limit master to the special “backup” network.
\nYou can do this with some special IP, which is known only to your slave and run the master for a while to be sure
\nthat everything was replicated.<\/p>\n\r\nbind-address = SOME.SPECIAL.IP.ADDRESS\r\n<\/pre>\n
\nWhen the position of master logs in replication does not change you can perform “stop slave;”.
\nBecause of the fact you set skip-slave-start you do not have to worry about that the replication
\nis active after slave restart. You can remove the bind-address and stop master instance.
\nThen you are ready to make LVM snapshot.<\/p>\n\r\n# master\r\nmysql -S \/tmp\/mysql-3309.sock -uroot --password=xxx -e \"SET GLOBAL net_write_timeout=31536000;\"\r\n<\/pre>\n
\nmysqldump: Error 2013: Lost connection to MySQL server during query when dumping table…
\n[However, I suggest to copy the largest tables as a separate processes. Not actual :)] <\/p>\n\r\ninnodb_read_only\r\n<\/pre>\n
\r\ncp -rpv \/db_storage_ssd_snapshot\/innodb \/db_storage\/\r\ninnodb_data_home_dir=\/db_storage\/innodb\r\ncp -rpv \/db_storage_ssd_snapshot\/innodb_log \/db_storage\/\r\ninnodb_log_group_home_dir=\/db_storage\/innodb_log\r\n<\/pre>\n
\n– Master:<\/p>\n\r\nmkdir \/db_storage_ssd_snapshot\/ \r\nchown mysql:mysql \/db_storage_ssd_snapshot\/ \r\nlvcreate -L6G -s -n datasnapshot \/dev\/vg_s7v0_ssd\/data\r\nmount \/dev\/vg_s7v0_ssd\/datasnapshot \/db_storage_ssd_snapshot\/ -onouuid,rw\r\n<\/pre>\n
\nStart the snapshot master instance.<\/p>\n \r\nmysqldump \\\r\n-S \/tmp\/mysql-3309.sock -uroot --password=xxx \\\r\n--add-drop-table=0 --skip-triggers -d -i -Q -q postfix \\\r\n| mysql -uroot --password=xxx -h 10.0.15.206 -D postfix\r\n<\/pre>\n
\r\nmysqldump -S \/tmp\/mysql-3309.sock -uroot --password=xxx \\\r\n-K --skip-tz-utc --triggers=0 -t -q postfix \\\r\n| mysql -uroot --password=xxx -h 10.0.15.206 -D postfix -f \r\n<\/pre>\n
\r\nmysqldump -S \/tmp\/mysql-3309.sock -uroot --password=xxx \\\r\n--add-drop-table=0 --triggers --events --routines -d -i -Q -q postfix \\\r\n| mysql -uroot --password=xxx -h 10.0.15.206 -D postfix -f\r\n<\/pre>\n
\n– Slave: restart<\/p>\n
\nFor MyISAM engine you can use myisamchk with analyze option, i.e.:<\/p>\n\r\nmyisamchk -F --analyze \/db_storage\/data\/DATABASE\/*.MYI\r\n<\/pre>\n