• Home
  • Archive
  • Tools
  • Contact Us

The Customize Windows

Technology Journal

  • Cloud Computing
  • Computer
  • Digital Photography
  • Windows 7
  • Archive
  • Cloud Computing
  • Virtualization
  • Computer and Internet
  • Digital Photography
  • Android
  • Sysadmin
  • Electronics
  • Big Data
  • Virtualization
  • Downloads
  • Web Development
  • Apple
  • Android
Advertisement
You are here: Home » MySQL Configuration to Set Up Master-Slave Replication

By Abhishek Ghosh October 6, 2019 10:14 pm Updated on October 6, 2019

MySQL Configuration to Set Up Master-Slave Replication

Advertisement

Our Use-Case of Master-Slave Replication is For WordPress. Master-Slave Replication For WordPress Makes it Fail-Proof to WordPress Database Errors Making the Website Fully Unreadable (which we have discussed in our article on Difference Between Standalone Database and Distributed Database). The main reason for so much discussion is for one big reason – the master MySQL database will read and write like a normal MySQL database while the slave MySQL database only is readable to WordPress. It does mean – easy switch to one-server setup. So, this kind of setup needs work on WordPress settings file too. One can use the same methodology to setup MySQL replication for other purposes other than for using WordPress. A WordPress setup with MySQL master-slave replication has three parts :

  1. WordPress configuration part
  2. Server configuration part
  3. MySQL configuration part

 

Among the server configuration, there are optional matters like using Percona XtraBackup to initially copy the backup. As the initial step, you should have at least two servers running MySQL with identical optimization except the configaration part we are going to discuss. One of the servers probably your live WordPress website which will act as master. Please note that we are using InnoDB engine. Make sure that you have allowed TCP port 3306 on Iptables.

We will suggest using two fresh cloud server instances to learn than directly using a live website’s MySQL database as a master. You can keep the instances running for 1-2 days to get the grasp. We have guide to install Percona MySQL on Ubuntu. You must resolve debian-sys-maint user matters related to Debian, Ubuntu and MySQL.

Advertisement

---

MySQL Configuration to Set Up Master-Slave Replication

 

Required MySQL Configuration

 

A default my.cnf is provided with any flavour of MySQL but we will use the master.cnf and slave.cnf configuration files when starting up the MySQL servers at least for testing. We are mentioning only the minimum required parameters for [mysqld] sections.

master.cnf
Vim
1
2
3
4
5
6
7
8
[mysqld]
server-id=1
log-bin=master-slave.log
datadir=/path/to/mysql/master/data
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-ignore-db = "mysql"
bind-address = 0.0.0.0

and this is of slave :

slave.cnf
Vim
1
2
3
4
5
6
7
8
9
10
[mysqld]
server-id=2
master-host =  [private-IP-of-master]
master-user = [replication-username]
master-password = [replication-password]
master-connect-retry = 60
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=/path/to/mysql/slave/data
bind-address = 0.0.0.0

Edit the /path/to/mysql/.. in both.

innodb_flush_log_at_trx_commit=1 and sync_binlog=1 options are used for the greater durability and consistency in a replication setup using InnoDB with transactions. Now, we can start the MySQL servers :

Vim
1
2
3
4
5
6
7
8
#
 
mysqld --defaults-file=/path/to/mysql/master/master.cnf &
 
# slave server
 
mysqld --defaults-file=/path/to/mysql/slave/slave.cnf &
#

We need to create replication user on the master server that the slave server can use to connect :

Vim
1
2
3
mysql -u root --prompt='master> '
master> CREATE USER repl_user@slave-ip-address;
master> GRANT REPLICATION SLAVE ON *.* TO repl_user@slave-ip-address IDENTIFIED BY 'your-password';

OR, depending on the flavour you may do in this way :

Vim
1
2
3
4
# mysql -u root -p
mysql> grant replication slave on *.* TO [replication_username]@'[IP of slave]' identified by '[some password]';
mysql> flush privileges;
mysql> quit

We can now start replication on the slave :

Vim
1
2
3
4
5
slave> CHANGE MASTER TO MASTER_HOST='master-ip-address',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='your-password',
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=4;

You can start replication on the slave by issuing this command :

Vim
1
mysql> start slave;

Restart both the MySQL servers. You can test the system by creating database on the master :

Vim
1
2
3
4
5
6
7
# mysql -u root -p
mysql> create database testing;
mysql> use testing
mysql> create table users(id int not null auto_increment, primary key(id), username varchar(30) not null);
mysql> insert into users (username) values ('foo');
mysql> insert into users (username) values ('bar');
mysql> exit

In case, the master is a MySQL database of a live website, use the following command to ensure that nothing can write to the master database during a database dump:

Vim
1
2
3
# mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Also, you need to note the filename and position of the binary log to complete the replication configuration on the slave server. After creating a database dump, lift the read lock from the master:

Vim
1
2
# mysql -u root -p
mysql> UNLOCK TABLES;

Complete the slave replication steps:

Vim
1
2
3
4
5
6
7
8
9
# mysql -u root -p
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[IP-of-master]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master]',
MASTER_LOG_POS=[log-position-listed-on-master];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

 

Conclusion

 

After the test setup works, move the perfect settings to production. Setup of MySQL master-slave replication is just a matter of getting used. Also, you may read different guides on the topic for extra tips, like that from Percona :

Vim
1
https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html

Tagged With setting up mysql in 5 7 21 slave on windows , master cnf , master slave configuration in database , master slave replication , mysql master -slave configuration , mysql master slave in same computer windows 7 , mysql set up a master slave on windows 7 , mysqld cnf replication-same-server-id , use case mysql master slave

This Article Has Been Shared 850 Times!

Facebook Twitter Pinterest

Abhishek Ghosh

About Abhishek Ghosh

Abhishek Ghosh is a Businessman, Surgeon, Author and Blogger. You can keep touch with him on Twitter - @AbhishekCTRL.

Here’s what we’ve got for you which might like :

Articles Related to MySQL Configuration to Set Up Master-Slave Replication

  • How to Upload Backup to Dropbox from Cloud Server

    Here is How to Upload Backup to Dropbox from Cloud Server in Case You Want To Keep Your Backup of Files and Database on a Free Cloud Storage.

  • Top Reasons Why Cloud Server Sucks Your Bucks

    Public Cloud is Deceptive From Sysadmin’s Point of View When Usage of RAM is More. Here Are Top Reasons Why Cloud Server Sucks Your Bucks.

  • How To Perform Security Audits On Ubuntu 16.04 (With Lynis)

    Here Is How To Perform Security Audits On Ubuntu 16.04 With Lynis And Other Tools Which Are Appropriate On Cloud Server’s Shared Environment.

  • Apache2 IPv6 Virtual Host Configuration (Ubuntu Server)

    Most Important Part of Apache Web Server is Virtual Host. Here is Apache2 IPv6 Virtual Host Configuration For Ubuntu Server.

  • EV SSL Certificate for Sole Proprietorship Business

    Is EV SSL Certificate for Sole Proprietorship Business Required At All? If Yes, Then What Are the Advantages it Will Offer to Business Value?

Additionally, performing a search on this website can help you. Also, we have YouTube Videos.

Take The Conversation Further ...

We'd love to know your thoughts on this article.
Meet the Author over on Twitter to join the conversation right now!

If you want to Advertise on our Article or want a Sponsored Article, you are invited to Contact us.

Contact Us

Subscribe To Our Free Newsletter

Get new posts by email:

Please Confirm the Subscription When Approval Email Will Arrive in Your Email Inbox as Second Step.

Search this website…

 

Popular Articles

Our Homepage is best place to find popular articles!

Here Are Some Good to Read Articles :

  • Cloud Computing Service Models
  • What is Cloud Computing?
  • Cloud Computing and Social Networks in Mobile Space
  • ARM Processor Architecture
  • What Camera Mode to Choose
  • Indispensable MySQL queries for custom fields in WordPress
  • Windows 7 Speech Recognition Scripting Related Tutorials

Social Networks

  • Pinterest (24.3K Followers)
  • Twitter (5.8k Followers)
  • Facebook (5.7k Followers)
  • LinkedIn (3.7k Followers)
  • YouTube (1.3k Followers)
  • GitHub (Repository)
  • GitHub (Gists)
Looking to publish sponsored article on our website?

Contact us

Recent Posts

  • Cyberpunk Aesthetics: What’s in it Special January 27, 2023
  • How to Do Electrical Layout Plan for Adding Smart Switches January 26, 2023
  • What is a Data Mesh? January 25, 2023
  • What is Vehicular Ad-Hoc Network? January 24, 2023
  • Difference Between Panel Light, COB Light, Track Light January 21, 2023

About This Article

Cite this article as: Abhishek Ghosh, "MySQL Configuration to Set Up Master-Slave Replication," in The Customize Windows, October 6, 2019, January 29, 2023, https://thecustomizewindows.com/2019/10/mysql-configuration-to-set-up-master-slave-replication/.

Source:The Customize Windows, JiMA.in

PC users can consult Corrine Chorney for Security.

Want to know more about us? Read Notability and Mentions & Our Setup.

Copyright © 2023 - The Customize Windows | dESIGNed by The Customize Windows

Copyright  · Privacy Policy  · Advertising Policy  · Terms of Service  · Refund Policy

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie SettingsAccept
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT