Guide for Configuring MariaDB replication on aws ec2

In this post, I will introduce that how to configure the MariaDB replication on AWS EC2 instances.

What is the Database replication

Database replication is the frequent electronic copying data from a database in one computer or server to a database in another so that all users share the same level of information ([Techtarget.com] (http://searchsqlserver.techtarget.com/definition/database-replication)). Replication for Database allows the contents of one or more servers (called masters) to be mirrored on one or more servers (called slaves) in real time. You may also choose all databases, one or more databases, tables in one database to be replicated.

Getting replication working involves some steps on both the master server/s and steps on the slave server/s. In this guide, I will use one master and one slave to show how to configure the mariadb replications between two EC2 instances.

Launch one EC2 instances for master

Sign in AWS management console and go to EC2 tab. Click on Launch instance button and Create two instance [AMI Type: Amazon Linux AMI 2016.03.0 (HVM)] for master and slave server separately.

Please do not forget to create security group for Master Instance.

		Protocol  Port Range Source
		TCP       3306        172.31.0.0/16

In my case, the private IP is 172.31.xxx.xxx.

If you meet any problems to launch the new EC2 instance, please refer the details from AWS mannual.

Install the MariaDB on Master & Slave server

You may use SSH to login the system when the instance status on AWS management console is displayed with running.

$ ssh -i your_key.pem -p22 [e-mail]

1. Adding the MariaDB YUM Repository
   MariaDB community has created many repositories for CentOS 5, CentOS 6, CentOS 7, RHEL 5, RHEL 6, RHEL 7, and Fedora 21 Repositories list. You may create one repository for MariaDB with file /etc/yum.repos.d/MariaDB.repo .

		[mariadb]
		name = MariaDB
		baseurl = http://yum.mariadb.org/10.1/centos6-amd64/
		gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
		gpgcheck=1

1. Install MariaDB
   With the repo file in place you can now install MariaDB like so:

    $ sudo yum install MariaDB-server MariaDB-client
   

2. Start the MariaDB

    $ sudo service mysql start
    $ sudo mysql -u root
    MariaDB [(none)]> show databases;
   

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

Configuring the Master

1. Set up one unique server_id (a number from 1 to 2^32-1) in your server.cnf file

    $ sudo mkdir /var/log/mariadb
    $ sudo chown -R mysql. /var/log/mariadb
    $ sudo cp /etc/my.cnf.d/server.cnf  /etc/my.cnf.d/server.cnf.orig
    $ sudo vi /etc/my.cnf.d/server.cnf
   
            [mysqld]
            server-id=1
            log_bin=/var/log/mariadb/mariadb-bin.log
   
    $ sudo service mysql restart
   

2. Grant the permission, which slave can connect and start replicating from a server.

    $ sudo mysql -u root
    MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO replication_user IDENTIFIED BY 'password';
    MariaDB [(none)]> FLUSH PRIVILEGES;
    MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
    MariaDB [(none)]> SHOW MASTER STATUS;
   

+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      315 |              |                  |
+--------------------+----------+--------------+------------------+

	MariaDB [(none)]> UNLOCK TABLES;

1. Restart the MariaDB

    $ sudo service mysql restart
   

Note:
- Make sure skip-networking is commented.
- Make sure bind_address is commented.

Configuring the Slave

1. Set up one unique server_id (a number from 1 to 2^32-1) in your server.cnf file

    $ sudo mkdir /var/log/mariadb
    $ sudo chown -R mysql. /var/log/mariadb
    $ sudo cp /etc/my.cnf.d/server.cnf  /etc/my.cnf.d/server.cnf.orig
    $ sudo vi /etc/my.cnf.d/server.cnf
   
            [mysqld]
            server-id=2
            log_bin=/var/log/mariadb/mariadb-bin.log
   
    $ sudo service mysql restart
   

2. Set the Master Information

    $ sudo mysql -u root
    MariaDB [(none)]> 
   
    CHANGE MASTER TO
    MASTER_HOST='172.31.xxx.xxx',   
    MASTER_USER='replication_user',
    MASTER_PASSWORD='password',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mariadb-bin.000001',
    MASTER_LOG_POS=315,
    MASTER_CONNECT_RETRY=10;
   
    MariaDB [(none)]> START SLAVE;
   

172.31.xxx.xxxis your private IP and you should change your own private IP.

Test the replication

1. Show Databases on Slave

    $ sudo mysql -u root
    MariaDB [(none)]> show databases;
   

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

1. Create one test database on Master

    $ sudo mysql -u root
    MariaDB [(none)]> CREATE DATABASE replication_test;
    MariaDB [(none)]> show databases;
   

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replication_test   |
| test               |
+--------------------+
5 rows in set (0.00 sec)

1. Show Databases on Slave

    $ sudo mysql -u root
    MariaDB [(none)]> show databases;
   

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replication_test   |
| test               |
+--------------------+
5 rows in set (0.00 sec)

END