MySql-Replica

Assumptions:

You have MySQL installed on both the master and replica machines.
You have appropriate permissions to perform administrative tasks.

Step 1: Update Package List on Both Master and Slave Node:

To ensure you get the most recent details about the packages that are available, update the package list:

sudo apt update
sudo apt-get update

Step 2: Install MySQL Server on Both Master and Slave Node:

Install the MySQL server package:

sudo apt install mysql-server

You will be required to set a root password for MySQL throughout the installation process. You must select a strong password and keep it in mind in order to access the MySQL server.

Step 3:Configure Master Server:

Add or change the lines below in the MySQL configuration file on the master system, which is often found at "/etc/mysql/mysql.conf.d/mysqld.cnf " or "/etc/my.cnf ":

[mysqld]
bind-address            = 0.0.0.0

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name    #For single Database
replicate-wild-do-table = "%"."%"    #Replicate all databases and tables

Step 4: Restart MySQL on Master:

Restart the MySQL service to apply the changes.

sudo service mysql restart

Step 5: Create a MySQL User for Replication:

Establish a user account on the master with replication rights after connecting to the MySQL server.

mysql -u root -p

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

Replace replication_user with the desired username and set a secure password.

Get the Master Status:

SHOW MASTER STATUS;

Note down the values of File and Position.

Step 6: Allow port on Master MySql-Server :

Check the port used by MySql on your machine and allow this port for communication.

sudo ufw allow 3306
sudo ufw reload

By Default Port 3306 used for MySql-Server..

Step 7: Configure Slave Server:

Edit the MySQL configuration file on the slave server and add or modify the following lines:
```
 [mysqld]
 server-id = 2
 relay-log = /var/log/mysql/mysql-relay-bin.log
 log_bin = /var/log/mysql/mysql-bin.log
```
Adjust the server-id and file paths as needed.
Restart the MySQL service on the slave server:
```
 sudo service mysql restart
```
Connect to the MySQL server on the slave:
```
 mysql -u root -p
```

Configure the slave to replicate from the master:

 CHANGE MASTER TO
     MASTER_HOST = 'master_ip',
     MASTER_PORT = master_port,
     MASTER_USER = 'replication_user',
     MASTER_PASSWORD = 'your_password',
     MASTER_LOG_FILE = 'master_log_file',
     MASTER_LOG_POS = master_log_pos;

Start the replication process on the slave:
```
 START SLAVE;
```

Step 8: Check Slave Status:

To monitor the status of replication on the slave:

SHOW SLAVE STATUS\G;

Check the values of Slave_IO_Running and Slave_SQL_Running. Both should be Yes if replication is working correctly.

Successfully!

The designated database and tables should now be replicated by the slave from the master. Don't forget to modify the instructions and parameters to suit your unique setup and needs.

Muhammad Ahmad's Blog