Replication between master and slave MySQL databases

Replication should be considered when there’s a primary database which is a production environment while a database where the primary one is being replicated is used for testing purposes and where we test SELECT statements for example. Another case when replication might come handy is backup - we won’t be performing backup with locked tables in a production environment, but in a replicated environment instead; Once backup finishes, binary logs are applied so both environments are equal again. Third case when we should consider replication is a testing environment; We just duplicate the primary database and at some point we stop replication in order to test further upgrades of application or a database itself.

Prerequisites

As usual there should be a MySQL database installed and running on both of your machines. If you’re missing it, get one here. Default installation is enough, however we have to perform some modifications of the configuration file in order to make the replication work. Replication needs at least two database servers - primary is called master while database where we replicate data from master is called slave.

Setting up replication

#Open a terminal and check port number in your my.cnf file cat /etc/my.cnf | grep port #If you see at least one line with port number being not commented out, then it’s OK #Otherwise edit the my.cnf file and enable port by removing the # symbol #In case any changes in my.cnf have to be done, restart mysql by executing: service mysql restart #By default MySQL uses port 3306 - try telnet whether it works: telnet 192.168.0.106 3306 #you have to replace the IP with yours #Here is examle output Trying 192.168.0.106... Connected to 192.168.0.106. Escape character is ’^]’. #The output above shows everything is OK #If port 3306 was disabled, then you have to check firewall settings and allow it there #now ensure binary logs are enabled on master by reviewing my.cnf file: nano /etc/my.cnf #now search for log-bin text within [mysqld] #also ensure server-id is set #Example is below: [mysqld] server-id = 1 log-bin = mysql-bin #now login to the slave host and check my.cnf file there: ssh 192.168.0.109 -l user1 #replace IP with yours as well as username login as root and edit my.cnf: su nano /etc/my.cnf #search for server-id parameter within [mysqld] #if it’s not set or if it’s equal to 1, modify it to 10 for example: [mysqld] server-id = 10 #now restart the database server on slave: service mysql restart #Values can be verified in mysql console by executing: SELECT @@server_id; #Replication will work only if there is a user in master database which means #following SQL’s have to be issued on master: CREATE USER 'repl_user'@'%' IDENTIFIED BY 'some_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'; --Since we’re going to setup replication, lock all tables: use mysql; flush tables with read lock; --it’s now necessary to gather information about current log position by executing: SHOW MASTER STATUS; --output will be similar to the following: +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000083 | 15887 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) --Now switch back to the OS terminal where master database resides and perform full dump: mysqldump -u root -p --all-databases > dump.sql #Get current directory and copy the dump file to host where slave resides: pwd #this will return something like /home/user1 #now connect to the host where replicated database is about to be setup: ssh username@target_host #where username is existing account and target_host can be IP for example #once connected to the host, go to the /tmp directory ideally and upload the file from source host: cd /tmp scp user1@192.168.0.106:/home/user1/dump.sql ./ #Bear in mind that above you have to use directory that is returned after executing the pwd command #If you don’t have private and public key setup between source and target host you will be asked for password ##Once scp finishes, you can unlock tables on the master with this command: unlock tables; #It’s now necessary to perform import to the slave by issuing: mysql -u root -p < dump.sql #It might take a while especially when master database is larger than just a few megabytes #Once the operation finishes, issue following on slave: mysql -u root -p #you will be asked for root password; Type it and issue this: CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='repl_user', MASTER_PASSWORD='some_password', MASTER_LOG_FILE='mysql-bin.000083', MASTER_LOG_POS=15887; --this will setup replication parameters, now just start the slave: START SLAVE; --Once this command is issued, replication should start if there is no connection issue --Slave can be stopped by : STOP SLAVE; --processes will exist after successful connection, simply issue: show processlist; --on MASTER the output will look like: | 513 | repl_user | 192.168.0.106:54380 | NULL | Binlog Dump | 149 | Has sent all binlog to slave; ... --on SLAVE the output will look like: | 62 | system user | | NULL | Connect | 235 | Waiting for master to send event | 63 | system user | | NULL | Connect | 170 | Slave has read all relay log; waiting for the ...

Cleanup

If everything is clear, disable replication by executing following on the host with slave databases:

--this should be executed in a MySQL console where you’re logged as root STOP SLAVE; SHOW DATABASES; --in case you’d like to remove replicated databases, continue with the below --now drop all databases shown after issuing the command above: DROP database1; DROP database2; ... DROP database3; --Also remove the user on master after logging to a MySQL console there: DROP user repl_user;

Conclusion & Benefits

As it’s already mentioned at the top of this page, replication brings several benefits such as:

  • Possibility of executing backups outside of a primary database server thus having no performance impact, no locking issues, etc.
  • Setting up duplicate environments easily without running into issues with using dumps from the primary database.
  • Having a testing environment with possibility of stopping slaves at any time.

Further reading & help

If you need professional help with your MySQL database, drop me a line. You’re also welcome to visit other articles devoted to MySQL - they’re listed below.

↑