Maria DB Replication - Master Slave.

Maria DB Replication.

Configuring the master

1. Enable binary logging if it's not already enabled.
2. Give the master a unique server_id. All slaves will also be given a server_id. This can be a number from 1 to 2³²-1, and must be unique for each server in the replicating group.
3.  Slaves will need permission to connect and start replicating from a server. Usually this is done by creating a dedicated slave user, and granting that user permission only to replicate (REPLICATION SLAVE permission).

Example

In your my.cnf file.

log-basename=master

log-bin=/var/log/mariadb/mariadb-bin

binlog-format=row

server_id=1

 Execute in the SQL prompt:

GRANT REPLICATION SLAVE ON *.* TO replication_user;

Variables to check                                           

There are a number of options that may impact or break replication. Check the following variable settings to avoid problems.

·         Skip-networking. If skip-networking=1, the server will limit connections to localhost only, and prevent all remote slaves from connecting.

·         bind_address. Similarly, if the address the server listens for TCP/IP connections is 127.0.0.1 (localhost), remote slaves connections will fail.

Configuring the slave                             

        Give the slave a unique server_id. All servers, whether masters or slaves, are given a server_id. This can be a number from 1 to 2³²-1, and must be unique for each server in the replicating group. The server will need to be restarted in order for a change in this option to take effect.

Getting the master's binary log co-ordinates

1. Now you need prevent any changes to the data while you view the binary log position. You'll use this to tell the slave at exactly which point it should start replicating from.

On the master, flush and lock all tables by running.           

FLUSH TABLES WITH READ LOCK;

Keeps this session running - exiting it will release the lock.

      2.  Get the current position in the binary log by running SHOW MASTER STATUS; 

SHOW MASTER STATUS;

+--------------------+----------+--------------+--------------------------+

| File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+--------------------+----------+--------------+--------------------------+

| mariadb-bin.000096 |      568 |                       |                           |

+--------------------+----------+--------------+--------------------------+         

Record the File and Position details. If binary logging has just been enabled, these will be blank.

·         Now, with the lock still in place, copy the data from the master to the slave.

·         Once the data has been copied, you can release the lock on the master by running UNLOCK TABLES.

                                                  UNLOCK TABLES;

 Start the Slave

      Once the data has been imported, you are ready to start replicating. Begin by running a CHANGE MASTER TO, making sure that MASTER_LOG_FILE matches the file and MASTER_LOG_POS the position returned by the earlier SHOW MASTER STATUS.

Ex:

CHANGE MASTER TO

  MASTER_HOST='192.168.1.110',

  MASTER_USER='replication_user',

  MASTER_PASSWORD='Coffee$123#',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='mariadb-bin.020496',

  MASTER_LOG_POS=4987,

  MASTER_CONNECT_RETRY=10;

        Now start the slave with the START SLAVE command.

   START SLAVE;