MySQL Master Master Configuration

Hello Folks

Creating a Master – Master replication for MySQL has never been so simple. We shall be using terminal and phpmyadmin, both, to create the Master-Master Replication. Will be using 2 numbers of Ubuntu 16.04 LTS, 64 bit edition server for this activity.

Server 1- 192.168.65.11 & hostname: mysqldb1

Server 2- 192.168.65.12 & hostname: mysqldb2

Configuration files will be edited through terminal, while adding the replication user, assigning rights and configuring replication server will be done through phpmyadmin. With Ubuntu 16.04 LTS servers, mysql server 5.7 will be available by default through the repos and php will be updated to newer and latest version – 7.0. There will be a slight change in the way phjpmyadmin gets installed. To know how to install phpmyadmin on Ubuntu 16.04 and php7.0 click here

Software Installation: Installing required software using apt. Do the following steps on both servers

sudo apt update && sudo apt upgrade
sudo apt install apache2 mysql-server-5.7 phpmyadmin php7.0 php-mbstring php7.0-mbstring php-mcrypt php7.0-mcrypt php7.0-mysql php-gettext php7.0-curl php7.0-json php7.0-cgi libapache2-mod-php7.0 phpmyadmin

Once the above has been run both the servers, run the following to ensure the mysql installation is secure and phpmyadmin works.

sudo mysql_secure_installation
sudo phpenmod mcrypt 
sudo phpenmod mbstring 
sudo systemctl restart mysql
sudo systemctl restart apache2

This completes the installation of mysql database server and phpmyadmin on the servers. Run all possible hardening steps to ensure server security is maintained.

Replication Configuration: Activities to be carried out through the terminal

Server 1: edit the mysqld.cnf file and copy paste the below configuration file – 192.168.65.11

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
general_log_file = /var/log/mysql/mysql.log
general_log = 1
log_error = /var/log/mysql/error.log
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

Save the file and exit (^O followed by ^x)

Server 2: edit the mysqld.cnf file and copy paste the below configuration file – 192.168.65.12

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
general_log_file = /var/log/mysql/mysql.log
general_log = 1
log_error = /var/log/mysql/error.log
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

Save the file and exit (^O followed by ^x)

NOTE: THE ONLY DIFFERENCE IN THE CONFIGURATION FILES ABOVE IS THE SERVER-ID PARAMETER. SERVER 1 HAS server-id=1 WHILE SERVER 2 HAS server-id=2

Replication Configuration: Activities to be carried out through web-interface (phpmyadmin)

We shall first configure Master – Slave configuration, where Server 1 will be the master & Server 2 will be slave. Once that is successful, we will add the configuration to make both the servers master servers

  1. Navigate to http://192.168.65.11/phpmyadmin -use root and password you kept while installing mysql and log-in the server
  2. Create a new user for replication called as  repl & password as 123
  3. Grant ‘Replication Slave’ and ‘Replication Master’ access rights for the user for Global so that all databases will be replicated. If you want only specific databases to be replicated, you have add those databases in the .cnf file and then grant rights to repl user basis specific databases
  4. Now navigate to http://192.168.65.12/phpmyadmin – use root and password you kept while installing mysql
  5. Click on replication and on slave configurationreplication1
  6. Click on go. Logout of the browser and log back in and click on replication. It should look like below screen shotreplication2
  7. Now click on ‘Control Slave’ option and click on  ‘Full Start’
  8. Click on replication Tab again in the top menu bar to see if replication is configured
  9. Click on ‘See slave status table’. You should see the below screen. Text highlighted in Green below should be Yes for both parameters. if it is not, there is something wrong. You can click a ‘Full reset’ option and then ‘Full Start’ option and check if you continue to have problemsreplication3
  10. That’s it. You have configured server 2 as slave server and server 1 as Master.

With this all databases in Server 1 will replicate with server 2 and so will the users with their passwords including the ‘repl’ user we created for replication.

Now we configure the slave server as Master Server so that both servers will replicate with each other. 

  1. There are no changes in config files and you do not need to use the terminal for this.
  2. Navigate to http://192.168.65.11/phpmyadmin and log in the server
  3. Click on replication and click on Slave configuration
  4. Follow exactly the above steps this time replacing host as 192.168.65.12. Everything else remains same
  5. Click on go. Log out and log back in
  6. Click on replication. You will either see the screen similar to point # 6 above or similar to point # 9 above
  7. Click on Full start and check if you see Slave_IO & Slave_SQL – running then you have completed the Master Master configuration else follow steps from 7 downward

Thats it folks, you have successfully configures mysql-server 5.7 in Master – Master configuration.

If you find any error feel free to leave a comment or share it if you like it.

Cheers

Kedar

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s