MySQL Master-Slave replication

1. Prepare the Master server:
• Log in to the Master server and open the MySQL configuration file.
• Add the following lines to the file under the [mysqld] section:
Copy

server-id = 1
log-bin = mysql-bin

• Restart the MySQL service
2. Verify that binary logging is enabled by running the following command in the MySQL client:
Copy

mysql> SHOW MASTER STATUS;

This command should display information about the binary log file that is currently being written. It will be used while connecting the slave server to the master. Sample output,


3. Prepare the Slave server:
• Log in to the Slave server and open the MySQL configuration file.
• Add the following lines to the file under the [mysqld] section:
Copy

server-id = 2
relay-log = mysql-relay-bin

• Restart the MySQL service
4. Create a Replication User on the Master:
• Log in to the Master server and run the following commands:
Copy

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'host' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;

5. Backup Master Data:
• Run the following command to backup the Master's data:
Copy

mysqldump -u [username] -p --all-databases --master-data > backup.sql

1. --all-databases: Dump all tables in all databases
2. --master-data: Write the binary log file name and position to the output
• Checkout other options for taking backup
6. Restore the Backup on the Slave:
• Copy the backup file to the Slave server and run the following command to restore the data:
Copy

mysql -u [username] -p < backup.sql

7. Configure the Slave:
• Log in to the Slave server and run the following commands:
Copy

# Remember to change the values. Do not copy-paste the same. It contains
# sample data.
mysql> CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=157, MASTER_PORT=3333;
mysql> START SLAVE;

Note: Replace master_host_name with the hostname or IP address of the Master server and repl_user and password with the credentials for the replication user.

8. Verify Replication:
• Log in to the Slave server and run the following command to check the replication status:
Copy

mysql> SHOW SLAVE STATUS;

Additional links

1. Official documentation ↗︎
2. Troubleshooting Replication ↗︎
3. MySQL Master-Slave Replication Architecture ↗︎
4. Restart the MySQL service
• To restart the MySQL service on Windows, you can use the following steps:
1. Open the Windows Services app. You can do this by pressing the Windows Key + R and typing services.msc in the Run dialogue box.
2. Scroll down the list of services and find the MySQL service. The name of the service may vary depending on the version of MySQL you have installed, but it should include the word MySQL example, MySQL56 where 56 denotes version 5.6. Once you've found the service, select it.
3. Right-click on the MySQL service and select Restart from the context menu. This will stop and start the service again.
• Alternatively, you can use the following command in the Command Prompt or PowerShell to restart the MySQL service:
Copy

net stop MySQL56 && net start MySQL56

This command will stop the MySQL service and then start it again. Make sure to run this command with administrator privileges.

5. Different options for taking backup
1. If you want to take a full backup i.e., all databases, procedures, routines, and events without interrupting any connections:
Copy

mysqldump -u [username] -p -A -R -E --triggers --single-transaction > full_backup.sql 

1. A For all databases (you can also use -all-databases)
2. R For all routines (stored procedures & triggers)
3. E For all events
4. -single-transaction Without locking the tables i.e., without interrupting any connection (R/W).
2. If you want to take a backup of only specified database(s):
Copy

mysqldump -u [username] -p [database_name] [other_database_name] -R -E --triggers --single-transaction > database_backup.sql

3. If you want to take a backup of only a specific table in a database:
Copy

mysqldump -u [username] -p [database_name] [table_name] > table_backup.sql

4. If you want to take a backup of the database structure only just add --no-data  to the previous commands:
Copy

mysqldump -u [username] –p –-no-data [database_name] > dump_file.sql

PS: Check out the list of all available options in the documentation ↗︎ or run man mysqldump at the command line.