Success Stories

Install and Configure Master – Master Replication on Hateregonous MySQL Environments

In a recent Indeed survey of more than 1,000 hiring managers and recruiters, more than half (53 percent) of respondents have hired tech talent despite candidates not meeting the job description requirements. That may be a good thing for businesses in need of IT resources to fill gaps in their talent pool. While that alludes to the fact that businesses are working hard to meet their needs for IT talent in what must be creative ways, here are five of those ways that businesses can employ to fill the gaps in IT resources and talent.

I create installation and configuration steps below;

Master1 – Slave1 Host Information

Host Info:
host : 1.1.1.1
user: dbuser
Hostname: Server1

OS release:

[email protected]:~# lsb_release -a
Description: Debian GNU/Linux 7.5 (wheezy)

MySQL Version:
Server version: 5.6.15-63.0-log Percona Server (GPL), Release 63.0

Master2 – Slave2 Host Information

Host Info:
host : 2.2.2.2
user : dbuser
Hostname: Server2

OS release:

[email protected]:~# lsb_release -a
Description: Ubuntu 16.04.3 LTS

MySQL 5.6 Installation on Server2

MySQL Installation:

$ sudo apt-get install software-properties-common
$ sudo add-apt-repository -y ppa:ondrej/mysql-5.6
$ sudo apt-get update
$ sudo apt-get install mysql-server-5.6

MySQL Version:

Server version: 5.6.35-1+deb.sury.org~xenial+0.1-log (Ubuntu)

You can move databases with using mysqldump, percona xtradbbackup or MySQL enterprise backup when creating the slave environment. I used MySQL enterprise backup.

Install MySQL Enterprise Backup on Server1

Download:

Link: https://edelivery.oracle.com/osdc/faces/SoftwareDelivery
Search: MySQL Enterprise Backup for Server 5.6, 5.5
Choose : MySQL Enterprise Backup 3.12.3 DEB for Debian 7 Linux x86 (64bit), 3.0 MB

Install:

$ sudo dpkg -i meb-3.12.3-debian7-x86-64bit.deb

Install MySQL Enterprise Backup on Server2

Download:

Link: https://edelivery.oracle.com/osdc/faces/SoftwareDelivery
Search: MySQL Enterprise Backup for Server 5.6, 5.5
Choose : MySQL Enterprise Backup 3.12.3 DEB for Ubuntu 14.04 Linux x86 (64bit), 2.9 MB
MySQL Enterprise Backup for Ubuntu 16.04 is not available on edelivery.oracle so you can download MySQL Enterprise Backup for Ubuntu 14.04

Install:

$ sudo dpkg -i meb-3.12.3-ubuntu14.04-x86-64bit.deb

Change Server1 MySQL Configuration file (my.cnf)

Add these parameters to my.cnf file for replication:

We have to enable binlog for creating MySQL replication.

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog-format = mixed

Restart MySQL on Server1:

You have to restart the MySQL Instance for enabling binlog.

$ sudo service mysql restart

Backup and Restore MySQL Database

Backup MySQL Databases on Server1 with MySQL Enterprise Backup (Online):

$ sudo mysqlbackup --user=root --password=root123 --socket=/var/run/mysqld/mysqld.sock --with-timestamp --backup-dir=/backup backup

Apply-log to Backup File:

You have to apply logs for creating a consistent backup

$ sudo mysqlbackup --backup-dir=/backup/2017-12-28_12-17-36 apply-log

Copy Backup Files to Server2:

$ sudo scp -r /backup/2017-12-28_12-17-36 [email protected]:/backup

Stop MySQL service on Server2:

$ sudo service mysql stop

Backup my.cnf file on Server2:

$ sudo mv /etc/mysql/my.cnf /etc/mysql/my.cnf.old

Copy Server1 my.cnf to Server2:

$ sudo scp -r /etc/mysql/my.cnf [email protected]:/etc/mysql

Change Server2 MySQL Configuration file (my.cnf):

server-id = 2

Change server-id for replication settings, the skip-slave-start parameter is used for opening MySQL instance without starting slave process, innodb_force_recovery is used for forcing InnoDB recovery.

Restore Database from backup on Server2:

$ sudo mysqlbackup --defaults-file=/etc/mysql/my.cnf --backup-dir=/backup/2017-12-28_12-17-36 copy-back-and-apply-log --force

Use –force parameter to overwrite existing files.

Add these parameters to Server2 MySQL Configuration file (my.cnf) for recovery:

skip-slave-start
innodb_force_recovery = 1 (for force innodb recovery)

the skip-slave-start parameter is used for opening MySQL instance without starting slave process, innodb_force_recovery parameter is used for forcing InnoDB recovery

Start MySQL Instance on Server2:

$ sudo service mysql start

After starting the MySQL service, connect the database and check the tables and data. Also, look at the MySQL error log. If there are no errors on logs , stop the MySQL instance and remove innodb_force_recovery parameter from my.cnf file.

Remove innodb_force_recovery and skip-slave-start parameter from Server2 my.cnf file and restart MySQL service:

$ sudo service mysql stop
$ sudo service mysql start

Replication Configuration on Server2

Create Replication User on Server1:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'slaveuser123';

Verify Connection From Server2 to Server1:

$ mysql --host=1.1.1.1 --user=slaveuser --password=slaveuser123
mysql> 
mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+-----------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY PASSWORD  |
+-----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

Get Backup Position:

$ cat /backup/2017-12-28_12-17-36/meta/backup_variables.txt | grep binlog_position
binlog_position=mysql-bin.000007:17008887

Slave Configuration on Server2:

mysql> CHANGE MASTER TO
MASTER_HOST='1.1.1.1',
MASTER_USER='slaveuser',
MASTER_PASSWORD='slaveuser123',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=17008887;

Starting Slave Process on Server2:

mysql> START SLAVE;

View Slave Status on Server2:

mysql> SHOW SLAVE STATUS\G;

Slave Configuration on Server1 for Master-Master Replication

Show Master Status on Server2:

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
 File: mysql-bin.000001
 Position: 107
 Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set:

Slave Configuration on Server1:

mysql> CHANGE MASTER TO
MASTER_HOST='2.2.2.2',
MASTER_USER='slaveuser',
MASTER_PASSWORD='slaveuser123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Starting Slave Process on Server1:

mysql> START SLAVE;

View Slave Status on Server1:

mysql> SHOW SLAVE STATUS\G;

Master-Master replication is ready. You can test replication on both Server1 and Server2. We use a different OS and MySQL version for creating master-master replication with minimum downtime (Only restarting the Server1 MySQL Instance)