Setting up MySQL replication

In this article the Master is 200.126.187.013 and the Slave is 200.126.187.066

On the Master, edit /etc/mysql/my.cnf:

server-id        = 126187013  # choose a unique integer
log_bin          = /var/log/mysql/mysql-bin.log
bind-address   = 0.0.0.0  # listen on all interfaces (by default 127.0.0.1 is used)
binlog_do_db   = mydb    # replicate only the 'mydb' database

Test your new configuration for syntax error:

bash# mysqld --help

Make sure the skip-networking option is not enabled.
Make sure server-id is unique. You can for instance use the last 3 octets of the IP address of the server.

Create a replication user on the Master using the IP address of the Slave that will access it (200.126.187.66):

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.37-1ubuntu5.1 |
+-------------------+
1 row in set (0.00 sec)
mysql> CREATE USER 'replication'@'200.126.187.66' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON mydb.users TO 'replication'@'200.126.187.66';
mysql> GRANT SELECT ON mydb.users TO replication;

Restart MySQL for the changes to take effect:

bash# /etc/init.d/mysql restart

On the Slave, edit /etc/mysql/my.cnf to set the server-id:

server-id  = 126187066    # choose a unique integer

Copy the current database on the Master with mysqldump and load it on the Slave.

Check the current status of the Master by retrieving its log file name and position:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |    23577 | mydb         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>

Start the replication on the Slave.

mysql> CHANGE MASTER TO MASTER_HOST='200.126.187.013', MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=0;
mysql> START SLAVE;

We did take some shortcuts here: the proper way would have been to:
1/ prevent the Master from committing anything
2/ backup the DB on the Master
3/ check the log file name and log position on the Master (‘SHOW MASTER STATUS’)
4/ load the backup on the Slave
5/ start replication using the log file name and position on the Master
6/ unlock the Master so the commits can happen again

A nightmare if the Master is already a live production server because it means from step 1/ to 6/ the Master is dead not committing anything.

As we did a ‘dirty’ backup, the replication will bump into errors that will stop the Slave (mainly because of entries already on the Slave, so we can safely skip them).
List them on the Slave with ’show slave status \G’ (look at the ‘Last_Error’) and if you can skip it, do it this way:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

Continue skipping the failed insert (if they can be skipped of course) until the slave completely catches up with the Master.

How do I know when the Slave is done catching up with the Master?

Issue a ‘SHOW SLAVE STATUS’ on the Slave:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 200.126.187.013
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
        Read_Master_Log_Pos: 339528
             Relay_Log_File: prod1-relay-bin.000004
              Relay_Log_Pos: 339665
      Relay_Master_Log_File: mysql-bin.000003
           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: 339528
            Relay_Log_Space: 339665
            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
1 row in set (0.00 sec)

mysql>

You can see everything is fine because:
- ‘Seconds Behind Master’ reports 0, so the Slave is done catching up (it’s in sync with the Master)
- Both Slave IO and Slave SQL are running
- The Slave is just waiting for new data to come in: ‘Waiting for master to send event’

Source

Tags: ,

5 Responses to “Setting up MySQL replication”

  1. Devon says:

    Great guide! Thank you for piecing this together.

    I do have one minor suggestion, which is the removal of the binlog-do-db line in the master my.cnf file.

    Depending on how the applications were written, It can be pretty easy for someone to encounter unexpected issues when using master-side filtering of the binary log (since the filtering is usually done based on the default database of the connection running the query, rather then the database the query actually affects).
    A secondary reason for trying to minimize the use of binlog-do-db and binlog-ignore-db is so that the master’s binary log serves as a complete incremental backup.

    Showing an example with no filtering or slave-side filtering would be great (and hopefully lead to less new users having strange problems).

    Example issue when using binlog-do-db:
    binlog-do-db=system
    ——————————
    mysql> use radius;
    mysql> update system.users set disabled = 1 where user = “root”;
    Since the connection’s default database is currently set to radius, the second statement is NOT replicated, even though it affects the system database. This type of failure is also 100% silent (the slave never even sees the query), so one does not even know immediately that the databases are out of sync.

    http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_binlog-do-db

  2. Lenhix says:

    Hi, the tutorial is nice but, as you mention, there are some steps missing. I’d appreciate if you could edit the post and add the instructions to stop the master while setting up the slave to sync for the first time.

    But my main concern is this error I’m getting when GRANTing replication:
    mysql> GRANT REPLICATION SLAVE ON database.* TO ‘replicauser’@'ip.of.the.slave’;
    ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
    The user was created without problem, and GRANTing SELECT was ok too.
    I’m running MySQL 5.0.77 on CentOS 5.4 (installed via yum).
    From mysql> status:
    mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1
    Server version: 5.0.77-log Source distribution

    Any ideas? Thanks in advance

  3. PlF says:

    @Devon
    Thanks

    @Lenhix
    Try applying it to the whole DB (*.* instead of database.*) like this:

    GRANT REPLICATION SLAVE ON *.* TO ‘replicauser’@’ip.of.the.slave’;

  4. kalyan says:

    Replication is failing.Could you please help me.

    I have followed the below steps:

    1.I have taken 2 windows server 2008 r2 machines and joined into domain(consider master as C1 and slave as C2)
    2.Installed the “mysql-installer-community-5.6.12.0″ in both the servers
    3.In Master(C1) machine:
    1.go to(C:\Programfiles\MYsqL\mYsql server 5.6) and edited my_default file

    added the below lines into the file
    server-id = 1
    log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days = 10
    max_binlog_size = 100M
    binlog_do_db = exampledb
    (I have restarted the machine)
    4.Open the command prompt in master(C1) and navigate to mysql bin path and connected to the mysql with the credentials
    5.create a user using the below command

    create user ‘username’@’slave machine ipaddress’ identified by ‘user password’;

    6.Grent permissions to the user using the below command

    GRANT REPLICATION SLAVE ON *.* TO ‘username’@’slave ipaddress’ IDENTIFIED BY ‘user password’;
    7.typed the command ‘Show master status;’

    it has shown as “empty set(0.00 sec)”

    7.Go to slave machine and edited my_default file

    added the below lines into the file
    erver-id=2
    master-connect-retry=60
    replicate-do-db=exampledb
    (I have restarted the machine)
    8.Open the cmd in slave,navigate to mysql bin path and connected to the mysql with the credentials, typed the below command:

    CHANGE MASTER TO
    MASTER_HOST=’10.0.0.18′,
    MASTER_USER=’k',
    MASTER_PASSWORD=’slave’,
    MASTER_LOG_FILE=’mysql-bin.000001′,
    MASTER_LOG_POS=3344;

  5. Tech Dude says:

    If you are replicating specific databases then you need the binlog-do-db line for each db.
    Warning

    To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

Leave a Reply