EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Replication
Secondary Sidebar
MySQL Tutorial
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL?Table?Size
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Interview Questions
    • MySQL Interview Questions

MySQL Replication

By Payal UdhaniPayal Udhani

MySQL Replication

Introduction to MySQL Replication

The following article provides an outline for MySQL Replication. For maintaining high availability of database and load balancing when a large number of users are accessing the MySQL database, various strategies need to be implemented and multiple database servers can be used that work together with the server and the users. In this case, it is very easy to provide this facility if all the operations are read-only which means no operation is performed on the data present in the database servers only the available data is retrieved.

But in the case of reading or write servers that involve all kinds of manipulations on the data of the database becomes difficult. This is because a single change in one database should be reflected in all the database servers that are serving the users. This is called replication of the data. Where a single change in the database server needs to be done in all the database servers with which it is working to provide high availability to the end-users.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Working of MySQL Replication

Given below is the working of MySQL Replication:

  • In MySQL, there is usually one database server on which all the modification relate operations and requests are served and those changes are in turn applied to other database servers called the slave servers or standby database servers.
  • That means the master nodes are always the senders of the replicated data while slave nodes receive the replicated data and apply the same changes over there.

Setup of Replication

It is required that the two instances of MySQL are running to demonstrate the master-slave configuration of replication. However, you can have any number of slaves where the replication should be maintained. For the current demonstration, we will consider two nodes over the private network. One of which will be master and the other will be a slave.

  • master IP: 172.16.2.4
  • slave IP: 172.16.2.15

Firstly, we will set up the master. For this, you will have to follow the following steps:

Given below are the steps:

1. Set the configuration file

The first thing to do will be to add the following lines in your configuration file mysqld.cnf. The bind-address statement will make sure that your master is listening at that address the server-id will be set to 1. This id needs to remain unique within the network of master-slaves in which your replication will work. This is used to identify the node. Further, the log_bin is specified what will specify the path where the logs will be stored.

  • bind-address: 172.16.2.4
  • server-id: 1
  • log_bin: /var/log/mysql/mysql-bin.log

We have changed configurations in the configuration file located in /etc/mysql/mysql.conf.d/mysqld.cnf folder.

MySQL Replication 1

MySQL Replication 2

2. Reflect changes made in configuration file, restart MySQL service

Now, to make sure that all the changes made in the configuration file are applied properly while setup, we will need to restart the MySQL server using the following statement.

Code:

sudo systemctl restart mysql

Output:

Reflect changes

3. Create a user which can have replication privileges

We will create a new user named newUserForRep that will be used for communication between slaves and masters. For that, the created user must have the privilege of replication on slave.

Code:

sudo mysql -u root -p

Output:

MySQL Replication 4

Code:

CREATE USER 'newUserForRep'@'172.16.2.15' IDENTIFIED BY 'passwordForSlave';

Output:

MySQL Replication 6

Code:

GRANT REPLICATION SLAVE ON *.* TO 'newUserForRep'@'172.16.2.15';

Output:

MySQL Replication 7

4. Lock the master database

Now, we will have to lock the database for write operations so that only read operations will be performed. This is necessary to lock master data to get the snapshot of slave and is required until be dump the database that is our further step. To lock masters we will use the following command.

Code:

FLUSH TABLES WITH READ LOCK;

Output:

Lock the master database

5. Store log position of master replication for further reference

Now, we will need to note down the location of the logs where they will be stored. This will be used further while setting up the slave node.

Code:

SHOW MASTER STATUS;

6. Dumping of the master database

Now, we will dump our master database using the following command.

Code:

sudo mysqldump -u root -p --all-databases --master-data > dbdump.sql

Output:

Dumping of the master database

7. Unlock the master database

As we have finished with dumping our master database, now we can unlock the tables using the following command.

Code:

UNLOCK TABLES;

Output:

Unlock the master database

8. Copy dumped file to slave node

Now, we will copy the master data that we had dumped earlier to the slave node by copying the dumped file to the slave node.

For example, using scp and following command, we can copy master data to the slave.

Code:

scp dbdump.sql 172.16.2.15:/tmp

Output:

Copy dumped file to slave node

Configuration of the Slave

Given below are the configuration of the slave:

1. Set the configuration file

Similar to the master process, we will make changes to the configuration file for slave node setup by making the following changes.

  • bind-address: 172.16.2.15
  • server-id: 2
  • log_bin: /var/log/mysql/mysql-bin.log

MySQL Replication 12JPG

MySQL Replication 13JPG

2. Reflect the changes made in configuration file, restart MySQL service

Next step is the same for the slave to restart MySQL service using the following command.

Code:

sudo systemctl restart mysql

Output:

MySQL Replication 14JPG

3. Import the dumped file for the database

Next step is to import the master data dumped at the master node to the slave node by using the following command.

Code:

mysql -u root -p < /tmp/dbdump.sql

4. Setup the slave node to communicate with master

Now, we will make sure that slave and master are communicating properly by stopping it and setting the master to our master by mentioning the things we had noted earlier and using the user we had created for replication and restarting the slave node.

Code:

sudo mysql -u root -p

Output:

Setup the slave node to communicate with master

Code:

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='172.16.2.4',
MASTER_USER='newUserForRep',
MASTER_PASSWORD='passwordForSlave',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=613;
START SLAVE;

5. Check the working of replication

This is it. You have completed your setup for replication. Now, all you have to do is to check whether replication is working fine by creating a new table or inserting the values in the table on the master node and then check on the slave node whether the updated data is written over there too by retrieving the records and viewing the schema.

Conclusion – MySQL Replication

We can provide high availability and balance the load on the database server by maintaining multiple database servers that can work together and implement replication to serve a large number of users without affecting the performance much in the MySQL database. However, you need to be careful while performing replication and make sure that all the settings are set correctly for the respective database servers depending on the role which they are going to perform in replication and request handling.

Recommended Articles

This is a guide to MySQL Replication. Here we discuss the working of MySQL replication, setup of replication and configuration of the slave. You may also have a look at the following articles to learn more –

  1. MySQL Database Repair
  2. MySQL Timestamp
  3. MySQL Outer Join
  4. ORDER BY in MySQL
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (17 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more