MySQL Master Slave Replication in Windows using XAMPP MySQL

[e-mail] Chirag's MySQL Tutorial https://www.chirags.in ************************************************************************

MySQL Master Slave Replication in Windows *************************************************************************

YouTube Video:
https://www.youtube.com/watch?v=kwBZldY-mdg

Database replication is the frequent electronic copying of data from a database in one computer or server to a database in another.

In this example, 02 XAMPP servers are being used.

Master – Server IP (Ex. 192.168.157.128) for demo I have localhost.
Slave – Server IP (Ex. 192.168.157.129)
    You need to install XAMPP with MySQL server on both master and slave machine or Install Two XAMPP in Same Machine. 

PART 1 - In master server

---

Step 1. Login to Master Server.

Edit & Modify the Configuration file of MySQL.

log-bin Configuration in my.ini

log-bin="C:/mysql_master_logs/log-bin.log"
server-id = 1

bind-address = 127.0.0.1 #comment this line if you want to remotely access your server

Step 2. Restart MySQL Server. Step 3. Login to MySQL Server.

# mysql -u root -p
Step 4. Create a new user for Replication and specify the Password to that user.

MariaDB [(none)]> CREATE USER 'mysqlrepli'@'localhost' IDENTIFIED BY 'admin@123';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON . TO 'mysqlrepli'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
Example :

MariaDB [(none)]> CREATE USER 'replication_user'@'192.168.157.128' IDENTIFIED BY 'replica_password';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON . TO 'replication_user '@'192.168.157.128';
Step 5. binary logging # check binary logging

MariaDB [(none)]> show global variables like 'log_bin';

View the binary log location

MariaDB [(none)]> show global variables like '%log_bin%';

Show binary logs

MariaDB [(none)]> show binary logs;
Step 6. Execute below command to Lock Tables & take backup and view the File & Position of Master Server.

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

C:\path >mysqldump -u root -p –all-databases –master-data > data.sql
Note: Path will be mysqldump.exe path.. It will be inside the bin folder.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 764 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
PART - 2 - In Slave Server

---

Step 7. Login to Slave Server. Edit & Modify the Configuration file of MySql Server.

     # Find the following line:
    bind-address = 127.0.0.1
    # Replace it with the following line:
    bind-address = Slave-Server-IP
    #log-bin Configuration in my.ini
    log-bin="C:/mysql_slave_logs/log-bin.log"
    server-id = 2

Restart MySQL Server Step

1. Login to MySQL in Slave Server.

   # mysql -u root -p
   

   Step 9. Import Data Dump

   mysql -u root -p < data.sql
   

   Step 10. Specify the following details as given below & make sure to Replace the following settings with your settings.

   MASTER_HOST     :   IP Address of Master server
   MASTER_USER     :   Replication User of Master server that we had created in previous steps.
   MASTER_PASSWORD :   Replication User Password of Master server that we had created in previous steps.
   MASTER_LOG_FILE :   Your Value of Master Log File of Master server.
   MASTER_LOG_POS  :   Your Value of Master Log Position of Master server.
   
   MariaDB [(none)]> STOP SLAVE;
   MariaDB [(none)]>  CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'mysqlrepli', MASTER_PASSWORD = 'admin@123', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 420;
   MariaDB [(none)]>  START SLAVE;
   MariaDB [(none)]>  show databases;
   MariaDB [(none)]>  SHOW SLAVE STATUS;
   ### Don’t forget to unlock the tables.
   

MariaDB [(none)]> UNLOCK TABLES;
PART - 3 - Testing for replication work

---

Step 10. Login to Master Server. Login to MySQL Server

    # mysql -u root -p

Step 11. For testing a Replication we need to create a new database, it will automatically replicate on Slave Server.

    MariaDB [(none)]>   create database chiragdb;

Step 12. Login to Slave Server. Login to MySQL Server

   # mysql -u root -p

Step 13. View your Replicated Database by using below command.

    MariaDB [(none)]>   show databases;

Let me know if you'd like further assistance!

For any doubts and query, please write on YouTube video comments section.

Note : Flow the Process shown in video.

😉Subscribe and like for more videos:

https://www.youtube.com/@chiragstutorial

💛Don't forget to, 💘Follow, 💝Like, 💖Share 💙&, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

---

Note: All scripts used in this demo will be available in our website.

Link will be available in description.

MySQLTutorial, #ChiragsMySQLTutorial, #CreateDatabaseinMySQL, #CreateTablesinMySQL, #MasterSlaveReplicationinMySQL, #MySQLReplication, #MySQLMasterSlave, #InstallMySQLinUbuntu, #InstallMySQLinLinux, #InstallMySQLinWindows