Prod and Dev: Sync two MySQL databases – easy!

No matter what IT project you create, you always need a sandbox to conduct testing and development. If your product has a MySQL database, then you will need to create a database in development environment.

You could manually create such a database and fill it with test data. But if your product is in development, your database schema will change. For example, new tables will appear, which will also need to be filled with test data. As a result, process of maintaining a development database can become time-consuming.

The easiest way to create a development database is to sync it regularly with production database. You will need to configure such a synchronization process once and the data in your development database will be as close to reality as possible.

In the following section, we will review production and development database synchronization methods.

  1. Creating backup on production database and performing restoration in development environment:
  2. Setting up replication

Creating backup on production database and performing restoration in development environment

Creating a production database dump and restoring it on development mysql server is a great solution. You can run this process at night, and in the morning your test database will be just a day behind the production database.

For simplicity, in examples below, password is passed in command parameters, but this is not safe. It is safer to move passwords to files and restrict read permissions for these files. You can do it for MySQL, mysqldump, and NcFTP.

Production database backup, FTP transfer, restoration on development server

The easiest way to create MySQL backup is to use mysqldump utility. This utility is included in MySQL Server. It is very easy to create a backup with it. To do this, run the following command on production server:

mysqldump -u {user} -p{password} {database name} > backup.sql

Then transfer dump to development server. The easiest way to do this is via FTP.

ncftpput -u {ftp-user} -p {ftp-password} {development-ftp-end-point} / backup.sql

After you transfer backup file it needs to be restored on test server

mysql -u {user} -p{ftp-user} < /var/ftp/backup.sql

Now we need to automate the process. This can be done using cron tasks. First of all, you need to create a task on production server. To create a cron task enter

crontab -e

As an example, we create a backup every midnight. The FTP client of NcFTP supports piping, so you can combine creation of a backup and uploading it to FTP

0 0 * * *  mysqldump -u {user} -p{password} {database name} | ncftpput -c -u {ftp-user}-p {ftp-password} --passive {ftp-end-point} backup.sql

Backup will be created every night and sent to the test server. To restore this backup, we need a cron task on development server. It should lag behind the task that we created on the production server, allowing enough time to create and upload a backup. The restore command will look like this

0 5 * * *  mysql -u {user} -p{password} < /var/ftp/backup.sql

Great, now every morning our database in test environment will sync with production database.

The good thing about creating a backup and restoring is that most likely you are already doing backups regularly, which means half of the work is done. You just need to pick up and restore the backup of your production base on the test server.

Database backup without temporary files

The mysqldump and MySQL commands support ––host option, which allows you to connect to a remote server. And since both mysqldump and MySQL support piping, you can dump the production database and restore it instantly in development environment. Example:

mysqldump -h {production-host} -u {production-user} -p{production-password} -d {db-name} | sudo mysql -u {dev-user} -p{dev-password}

This is very convenient, but there is one significant drawback: if production server and development server are not on the same subnet, then this method may not be applicable for security reasons. It is not recommended to keep MySQL server port (3306) open for connection from the Internet.

Third-party backup and restore services

We have reviewed simplified examples of how you can copy a MySQL database. But in practice, simple solutions are poorly applicable. The solution via cron and mysqldump lacks notifications in case of failure and minimal execution log. Bringing up FTP just for the sake of transferring the file may be uncalled for, and making it available over the Internet is not safe. Of course, you can write a more complex script and configure access through certificates, but this will require additional development and support. An alternative is third-party services that implement the entire process for you.

One of such services is SqlBak. SqlBak is a service for creating backups and sending them to a cloud storage. It also has the ability to restore database on another server after backup.

To synchronize production and development databases using SqlBak, you need to register on the website, and then install SqlBak service on both servers and connect them to sqlbak.com. This can be done with one command

curl -sSL https://sqlbak.com/download/linux/latest | sudo bash -s <secret-key>

You also need to establish a connection to MySQL database on each server

sudo sqlbak -ac -dt mysql -u root -p {mysql-password}

After that, both servers will appear on the Dashboard

Now click the add job button and add a backup job. You will need to select databases which you need backup for and where to send it. The final step is to launch a restore after backup on another server.

Then you can save and start the job. You will see a job log at the end of which there will be records of successful recovery on development server.

In addition to synchronizing production and development databases, SqlBak does an excellent job of doing regular backups. It supports various cloud storages and has many useful features, such as sending e-mail notifications, encryption, automatic deletion of outdated backups.

Replication

Another approach is to set up master-slave replication. Replication process will duplicate all transactions from production server (master) on your development server (slave). The main disadvantage of replication is that development server will be writable, which can lead to collisions.

Setting up replication for the entire server in MySQL is very straightforward. We provide a simple instruction on how to do it below.

Master setup

First, you need to turn on bin logs on master server. To do that, open file my.cnf

vi /etc/mysql/my.cnf

If there is no my.cnf file in the given path, use sudo find / -iname my.cnf to find it.

Add or uncomment the following lines in the section [mysqld]

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

We recommend creating a separate user on master when setting up replication. To do this, execute SQL statement

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'{slave-ip-address}' IDENTIFIED BY '{password}';

Then restart MySQL for changes to take effect:

sudo service mysql restart

For the following steps, it is advisable to put database in read-only mode. To do this, run

FLUSH TABLES WITH READ LOCK

Now run SQL command, which will display binlog file status.

SHOW MASTER STATUS

You will see:

File: mysql-bin.001182
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:

Remember the highlighted values, they will come in handy when setting up a slave server.

Now you need to copy the data to slave server. For this, we will create a dump of all databases

mysqldump -u root -p --all-databases > ~/dump.sql:

This completes your work on master server. All that’s left is to unlock the tables

UNLOCK TABLES;
Slave setup

Now we go to the dev server taking the dump.sql file with us. First of all, you also need to edit mysql.cnf on it.

Add or replace the following parameters in section [mysqld]

server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_space_limit = 2000M

Save and restart MySQL instance for changes to take effect:

sudo service mysql restart

Now we need to import dump.sql created on master server:

mysql -u root -p mysql < dump.sql

The last step is to specify a connection to master server and start replication. For this run the following SQL commands.

STOP SLAVE;

CHANGE MASTER TO master_host='{master-ip}', master_port=3306, master_user='replication', master_password='{password}, master_log_file='{bin-log-file-name}', master_log_pos={bin-log-position};

START SLAVE;

Instead of orange values, substitute the values that were received on master server as a result of command SHOW MASTER STATUS

This completes master-slave replication setup. Your development server will now be constantly in sync with production server. However, due to the fact that slave server’s is not in read-only mode, collisions may occur. For example, you can get unexpected errors while editing an entry that is being deleted at this moment on production server. Also, in order to configure replication, you need a direct TCP\IP connection, which means that production base must be available for external connections, which is bad from security point of view.

Conclusion

You can synchronize development and production databases in different ways. When choosing solutions, pay attention to your security requirements, time you are willing to spend on support and your database size.

Leave a Reply

Your email address will not be published. Required fields are marked *