Tag Archives: mysql migration

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.

1. mysqldump method

1.1. prepare the MySQL instance on hostname2 and config file to match the hostname1 MySQL instance.

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.

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.

1.5. make the flip between master and slave and promote slave as future master.

This step can be done by several methods and it depends on your network configuration.

2. Percona toolkit method

2.1. prepare the MySQL instance on hostname2 and config file to match the hostname1 MySQL instance.

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.

2.2. Download and install the right version of Percona xtrabackup tool on hostname1 and hostname2
they have release notes for each version and you can check if your MySQL version is supported by one version or another.

About me…

I am from Bucharest, Romania and I am working with SQL Servers since 2006. I have been working closely with all SQL versions starting with SQL Server 2000. I am a senior DBA at OpenSky company and spend my free time with my husband and my son.
See more about me and why I started this blog.