24 views

1 Answer

answered by

inchirags@gmail.com          Chirag's MySQL Tutorial                       https://www.chirags.in
************************************************************************

MySQL Master Slave Replication in Windows
*************************************************************************
YouTube Video:

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, two 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 8. 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;

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

Most popular tags

laravel postgresql laravel-10 replication ha postgresql mongodb laravel-11 mongodb database mongodb tutorial ubuntu 24.04 lts streaming-replication mysql database laravel postgresql backup laravel login register logout database mysql php laravel 11 - login with otp valid for 10 minutes. user and admin registration user and admin login multiauth technlogy asp.net asp.net c# mysql master slave replication centos linux laravel sql server schedule backup autobackup postgresql django python haproxy load balancer install self sign ssl laravel 11 gaurds zabbix 7 how to install graylog on ubuntu 24.04 lts | step-by-step asp.net core mvc .net mvc network upload c# ssl integration sql server on ubuntu 22.04 lts mssql server ms sql server sql server user access in postgres mysql password change cent os linux configure replica laravel 11 socialite login with google account google login kubernetes (k8s) install nginx load balancer install install and configure .net 8.0 in ubuntu 24.04 lts php in iis php with iis php tutorial chirags php tutorials chirags php tutorial chirags tutorial laravel 11 guards mongodb sharding metabase business analytics metabase postgresql 16 to postgresql 17 postgresql migration letsencrypt mongodb crud rocky linux laravel custom captcha laravel 11 captcha laravel captcha mongo dll php.ini debian 12 nginx apache nextcloud gitea in ubuntu git gitea npm error node js mysql ndb cluster mysql cluster ssl oracle login register logout in python debian windows shell batch file bat file time stamp date time shopping cart in laravel centos rhel swap memeory rhel 5.5
...