How-to setup mysql replication in simple steps

MySQL replication is quite straight forward. Here are the setup steps:

1. Create a user for replication at master.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' identified by '';

2. Copy master data directory to slave.

For Innodb, use mysqldump with option –single-transaction to get a consistent dump (you can get the log file and position in the dumpfile), or shutdown the source DB to get a cold consistent copy.

For all other DB engine, following these steps:
1) Lock the tables at master

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| bin-log.000074 | 915 | | |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

–Copy the log file name and position number, it will be used later

2) Copy the data over
$ rsync -avz /mysql/data :/mysql/data

Remove file master.info and relay-log.info if you build the slave from Master.

3) After the copy is done, unlock tables at Master:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

or you can exist the session which will release the lock as well.

3. Setup slave

If slave binary is not setup, you can also copy them from master.

1) Startup mysql
a. Remember change the server_id to a different value in my.cnf
b. Binary logging is required for replication. And relay-log is recommended.
log-bin=mysql-bin
relay-log = relay-log
relay-log-index = relay-log.index

2) Set master

mysql> change master to
    ->                     master_host='alexzeng.wordpress.com',
    ->                     master_user='repluser',
    ->                     master_password='<password>',
    ->                     master_port = 3306,
    ->                     master_log_file='bin-log.000074',
    ->                     master_log_pos=915;
Query OK, 0 rows affected (0.00 sec)
--use the log file name and postion we got previously

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: alexzeng.wordpress.com
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000074
          Read_Master_Log_Pos: 1199
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 533
        Relay_Master_Log_File: bin-log.000074
             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: 1199
              Relay_Log_Space: 682
              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:
1 row in set (0.00 sec)

Now the Master-Slave replication is setup, is it easy 🙂

In addition,

A. If you want to setup Master-Master replication, continue the following steps:
At slave :

mysql>  show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| bin-log.000077 |      106 |              |                  |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

At master:

mysql> change master to
    ->                     master_host='alexzeng2.wordpress.com',
    ->                     master_user='repluser',
    ->                     master_password='<password>',
    ->                     master_port = 3306,
    ->                     master_log_file='bin-log.000077',
    ->                     master_log_pos=106;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

B. If you want to setup a slave from an existing slave, using the following steps:

* Shutdown the existing slave db

* Copy the data to new slave:
$ rsync -avz /mysql/data <target_host>:/mysql/data
--Keep file master.info and relay-log.info if you build the slave from another slave.

* Startup mysql at new slave.
make sure it uses a different server_id in my.cnf
(cd $MYSQL_BASE; ./bin/mysqld_safe &)
start slave;
--Do NOT need to run "change master to ..." statement as it already in master.info and relay-log.info
Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

3 Responses to How-to setup mysql replication in simple steps

  1. Pingback: How-to setup MySQL HA by using keepalived « Standing on the shoulders of Giants

  2. Malai says:

    Thanks its really help to us

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: