Replication in MySQL

sachin

Configuration for Master Server (10.0.0.100)

In your `/etc/my.cnf` add the following:

server-id=1
log-bin=mysql-bin
port=3306
max_allowed_packet=256M
binlog_do_db=exampledb
expire_logs_days=20
max_binlog_size=500M
auto_increment_increment=2
auto_increment_offset=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

And run the following SQL command as the root user:

grant all on *.* to 'msandbox'@'10.0.0.101' identified by 'msandbox';
flush privileges;

Restart the MySQL server.

service mysqld restart

mysql -u root -p
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000017 |    66402 | billing_new  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Open a second terminal for Master Server, create the dump and transfer it to Slave Server.

Don’t leave the MySQL-shell at this point – otherwise you’ll loose the read-lock (If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.)

cd /tmp/
mysqldump -u root -p exampledb > sqldump.sql
scp sqldump.sql root@10.0.0.101:/tmp/

Afterwards close the second terminal and switch back to the first. Remove the read-lock and leave the MySQL-shell.

UNLOCK TABLES;
quit;

Configuration for Slave Server (10.0.0.101)

import the dump to slave server database.

mysql -u root -p
CREATE DATABASE exampledb;
exit;
mysql -umsandbox -p exampledb < /tmp/sqldump.sql

In your `/etc/my.cnf` add the following:

server-id = 2
log-bin=mysql-bin
binlog-do-db=exampledb
lower_case_table_names=1

Restart the MySQL server.

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='10.0.0.100',MASTER_USER='sandbox', MASTER_PASSWORD='msandbox', MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=66402;
START SLAVE;
SHOW SLAVE STATUS \G

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.100
                  Master_User: sandbox
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 577942
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 578226
        Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 577942
              Relay_Log_Space: 578522
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

and take a look at the slave status. It’s very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they’re not, something went wrong and you should take a look at the logs.