Versions Compared

Old Version 25

changes.mady.by.user Former user

Saved on

compared with

New Version 26

changes.mady.by.user Former user

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Create a replicator user and grant it replication permissions by executing the query below on DB prompt. Replace REPLICA_USER, REPLICA_PASS and SLAVE_SERVER as you wish:

Code Block
GRANTgrant REPLICATIONreplication SLAVEslave ONon *.* TOto 'REPLICA_USER'@'SLAVE_SERVER' IDENTIFIEDidentified BYby 'REPLICA_PASS';
FLUSHflush PRIVILEGESprivileges;

Configure DB replication on slave DB server

...

Open a new session on master DB server and execute the commands below in mysql prompt.

Code Block
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

This will show master current status. We will need this later when replicating the DB in the slave. Leave this session as it is until replication is completed:

Code Block
flush tables with read lock;
show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhostmaster-bin.000002000001    |  3712878 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

...

Once imported, configure slave DB server replication. Replace MASTER_HOSTSERVER, MASTERREPLICA_USER, MASTERREPLICA_PASSWORDPASS, MASTER_LOG_FILE and MASTER_LOG_POS accordingly to previous steps in command below and execute it in slave server mysql prompt:

Code Block
CHANGEchange MASTERmaster TOto
  MASTER_HOST='MASTER_IPSERVER',
  MASTER_USER='replicatorREPLICA_USER',
  MASTER_PASSWORD='PASSWORDREPLICA_PASS',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='localhostmaster-bin.000002000001',
  MASTER_LOG_POS=3712878,
  MASTER_CONNECT_RETRY=10;

Start slave DB replication from slave mysql prompt:

Code Block
STARTstart SLAVEslave;

You can check replication status by executing on the slave:

Code Block
SHOWshow SLAVEslave STATUSstatus\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.60.13.25
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: localhost-bin.000002
          Read_Master_Log_Pos: 3713036
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 691
        Relay_Master_Log_File: localhost-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)

...