MySQL is an opensource Database Management System and offers a very flexible platform from management point of view. It has a lot of options when the discussion comes to how to backup and restore a MySQL instance. This can be done in several ways, depending what you are after:
- Consistency before speed – use mysqldump
- Speed before consistency – use LVM backups or Percona toolkit.
I used mysqldump and Percona toolkit and I saw the advantages to each one of these methods.
mysqldump: can guarantee the consistency of the backup and the data behind it. It will take a lot of time because it practically creates the scripts for each Database Object (structure and data).
Percona: integrated solution for backup/recovery that makes a folder backup and can include corruption inside database objects because it is not checking the consistency of the objects. This method is faster and can be a good option if the database has been checked for corruption AND FIXED.
A mysqld (instance) can have one or more databases. It has a config file which can contain the configuration options for more MySQL instances. In this post I will explain how to move a machine with one MySQL instance, using the above mentioned methods.
In my case, I had a server with a MySQL instance (5.6 version) on it and I had to move the servers’s databases to another machine. This was a DEV server and there where 2 reasons why I chose to use MySQL Replication.
I will use hostname1(current) and hostname2 (new)
- I didn’t want to lock the DEV team from doing changes on db side, while the dump is being made and restored
- The specification was to keep the same MySQL server name on hostname2 that was used in connection strings and for my case it easier to down interface from hostname1 and put it up on hostname2 with little disruption of activity.
- install the right version of MySQL 5.6.
- change the config file to match the source MySQL my.cnf file. validate that on the new server you have the directories for data, innodb files, logging and startup variables if specified (ex: socket and pid).
- create MySQL user if not exists
- initialize MySQL database Data Directory through mysql_install_db
It is important to make sure that the database directories and files are owned by the mysql login account so that the server has read and write access to them when you run it later. If you are running the command as root, specify
the –user that will be used to run mysqld. The –datadir option is mandatory. mysql_install_db creates the mysql directory which will contain MySQL database files and should NOT exist.
/usr/local/mysql-VERSION/scripts/mysql_install_db --user=mysql --datadir=/path-to/datadir/
- check datadir permissions. change owner&group if necessary.
ls -la /path-to/datadir/ chown -R mysql:mysql /path-to/datadir/
- start mysql server
when master-data is used with value=2 it causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.
mysqldump --host=hostname1 --port=3306 --all-databases --add-drop-database --add-drop-table --master-data=2 --single-transaction > /drive_location_for_dump/backupfile.sql
use rsync or scp – whatever you prefer.
Run from hostname1:
scp /drive_location_for_dump/backupfile.sql youruser@hostname2:/drive_location_for_dump/
Run on hostname2:
mysql -h hostname2 -P3306 < /drive_location_for_dump/backupfile.sql
Run from shell on hostname2:
head -n2000 /drive_location_for_dump/backupfile.sql | grep "CHANGE MASTER TO"
copy the line and execute in mysql:
CHANGE MASTER TO MASTER_HOST='hostname1', master_port = 3306, MASTER_LOG_FILE='binlog file' MASTER_LOG_POS=position start slave; show master status \G
This step can be done by several methods and it depends on your network configuration.
Repeat the 1.1. steps from above method, EXCEPT the part with initializing MySQL database. Because you are doing a folder to folder copy MySQL database will be copied from hostname1 to hostname2.
they have release notes for each version and you can check if your MySQL version is supported by one version or another.
Next, you can find all the steps to setup a slave to an existing master, explained here (https://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html)