Migration Process

USE rdb;

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- Insert first users
INSERT INTO user (username, password, email) VALUES
('user1', 'password1', '[email protected]'),
('user2', 'password2', '[email protected]'),
('user3', 'password3', '[email protected]');

SELECT * from user;

INSERT INTO user (username, password, email) VALUES
('user4', 'password4', '[email protected]'),
('user5', 'password5', '[email protected]'),
('user6', 'password6', '[email protected]');

Step 1: Configure the Master Server

1. Edit the MySQL Configuration File (my.cnf or my.ini): Add the following lines under the [mysqld] section:

   [mysqld]
   server-id = 1
   log_bin = /var/log/mysql/mysql-bin.log
   binlog_do_db = your_database_name   # Optional: specify the database you want to replicate
   
   

2. Restart the MySQL Service:

   sudo systemctl restart mysql
   

3. Create a Replication User: Log in to the MySQL master server and execute:

   CREATE USER 'replication_user'@'%' IDENTIFIED BY 'replication_password';
   GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
   FLUSH PRIVILEGES;
   

4. Lock the Database and Get the Binary Log Coordinates:

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;
   

   Note down the File and Position from the SHOW MASTER STATUS output. You will need these for the slave configuration.

Step 2: Configure the Slave Server

1. Edit the MySQL Configuration File (my.cnf or my.ini): Add the following lines under the [mysqld] section:

   [mysqld]
   server-id = 2
   

2. Restart the MySQL Service:

   sudo systemctl restart mysql
   

3. Copy the Data from Master to Slave:

   • If the database is large, you might want to use a tool like mysqldump:

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

   • Transfer the master_data.sql file to the slave server.

   • Import the data into the slave server:

     mysql -u root -p < master_data.sql
     

Step 3: Set Up Replication on the Slave

1. Configure the Slave to Use the Master’s Log File and Position: Log in to the MySQL slave server and execute:

   CHANGE MASTER TO
     MASTER_HOST='master_host_ip',
     MASTER_USER='replication_user',
     MASTER_PASSWORD='replication_password',
     MASTER_LOG_FILE='mysql-bin.000001',  -- Use the file from the master status
     MASTER_LOG_POS=12345;                -- Use the position from the master status
   

2. Start the Slave:

   START SLAVE;
   

3. Verify the Replication Status:

   SHOW SLAVE STATUS\\G;
   

   Ensure that Slave_IO_Running and Slave_SQL_Running are both set to Yes.

Unlock the Master Database

If you locked the master database earlier, you can unlock it now:

UNLOCK TABLES;