Based on:

 

Requirements:

  • New replica must have exactly the same version of MySQL as the one you will use for “cloning”
  • It must have the same setting regarding replication and logs
  • You must shutdown both databases for the time of copying – so the “master” replica should not be used for connections or you must be able to temporarily redirect them to some other replica
  • you must be able to do “sudo su mysql” on both replicas which usually mean:
    • set proper shell in /etc/passwd file for user “mysql” – most likely /bin/bash
    • set correct home directory in /etc/passwd file for user “mysql” (most likely mysql “datadir” – /var/lib/mysql – this is already set for mysql user when you install MySQL 5.7)
  • you must be able to connect via ssh from “master” replica to the new replica under user mysql to remote user mysql – it usually means:
    • create ssh keys for mysql user on both machines using ssh-keygen
    • add public ssh key of mysql user from “master” replica to ~/.ssh/authorized_keys file on the new replica
  • install “parallel” on “master” replica:
    • sudo apt-get install parallel

 

Steps:

  • connect to the new replica
  • shutdown mysql on the new replica (runs by default after installation) using:
    • “sudo service mysql stop”
    • or “mysqladmin -u root -p shutdown”
  • check settings in /etc/mysql – new replica MUST have UNIQUE server-id !!!!
  • on the new replica only !!!
    • delete all files from “datadir” directory
      • most likely /var/lib/mysql – including directories
      • using something like “rm /var/lib/mysql/* -rt” (command is safer with full path)
    • delete all files from log directory – check “relay-log” and “log_bin” variables
      • most likely /var/log/mysql – “rm /var/log/mysql/*
  • connect to the “master” replica
  • shutdown MySQL on “master” replica (see above)
  • IMPORTANT: start "tmux" or some other tool which will preserve session in case you loose connection !!!!!!!
  • open second window in tmux and start following commands (add IP of the new replica):
    • sync of data files in the first window:
      • find /var/lib/mysql -not -path '/var/lib/mysql/.ssh/*' -print | parallel -v -j8 rsync -dvpogXEtS -e ssh --progress {} mysql@xxx.xxx.xxx.xxx:{}
    • sync of log files in the second window:
      • find /var/log/mysql -not -type d -print | parallel -v -j8 rsync -dvpogXEtS -e ssh --progress {} mysql@xxx.xxx.xxx.xxx:{}
    • These commands will copy files in parallel – using 8 jobs for each task. “rsync” will compress files for copying and will preserve all attributes etc.
    • Total time of copying depends on biggest files like /var/lib/mysql/ib_logfile* or ibd files of the biggest tables.
  • when copying is done start MySQL on both replicas and check consistency of replication on both machines:
    • for f in $(find /var/lib/mysql -type f|sort); do sha256sum $f; done > /tmp/var-lib-mysql-checksums-instancename.txt
    • for f in $(find /var/log/mysql -type f|sort); do sha256sum $f; done > /tmp/var-log-mysql-checksums-instancename.txt
    • Download all files and compare them – cloning replica against new replica
    • If some files are missing (it can happen due to errors during copying) simply repeat commands shown above. Already copied files will be ignored.
  • IMPORTANT:
    • server_id of the new replica must be unique !!!!
    • After copying of file is done check size of files in directories /var/lib/mysql and /var/log/mysql on target replica and compare it with source replica!!!!! It happened to me that in several cases some files were not copied. Although slight differences in numbers are possible between machines…
    • if you use MySQL 5.6 or higher – after copying is done delete file /var/lib/mysql/auto.cnf on target machine before starting MySQL !!!! MySQL will generate new unique server UUID (see here)
    • If you after all forget to detele auto.cnf file and you start cloned replica with UUID copied from source replica, then simply delete auto.cnf on source replica before you start it.