Wednesday, October 24, 2012

Adding First Slave to Running MySQL

Problem: We had a MySQL server running for sometime now, we wanted to add a slave to it.

Steps: It's really simple.
  1. Configure server-id and binary logging: vi /etc/my.cnf. If there exists none, create one.
    [mysqld]
     
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
     
    # master id
    server-id=1
     
    # binary logging for replication)
    log-bin=mysql-bin
     
     
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
     
    # Disabling symbolic-links is recommended to prevent assorted security risks;
    # to do so, uncomment this line:
    # symbolic-links=0
     
    [mysqld_safe]
    log-error=/var/log/mysql/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    restart MySQL service. service mysql restart
  2. Create a replicator user in master:
    GRANT REPLICATION SLAVE ON *.* TO 'replicator' IDENTIFIED BY 'replicatorPassword';
    FLUSH PRIVILEGES;
  3. Get master binary log position:
    mysql> SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000013 |     107|              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

  4. Take a MySQL dump:
    mysqldump --user USERNAME --password=PASSWORD --add-locks --flush-privileges \ 
    --add-drop-table --complete-insert --extended-insert --single-transaction  --database DBNAME \
    -h MASTER_HOST_IP_DNS > backup.sql
    Stop the master MySQL at this point to avoid data alteration.
  5. Configure slave: Keep the configuration same as master #1, except give it a different server-id. You may not include log-bin in slave configuration. But it is good idea to keep it. It will be useful in case where you want to make this slave master.

    Restart slave and login to it. Drop the database which you have taken dump of, if exists; and recreate it. Load the db dump.
    mysql -u USERNAME -pPASSWORD -h SLAVE_IP_DNS DBNAME < backup.sql
  6. Setup replication: Login to slave MySQL. Configure replication:
    CHANGE MASTER TO
       MASTER_HOST='MASTER_IP_OR_DNS',
       MASTER_USER='replicator',
       MASTER_PASSWORD='replicatorPassword',
       MASTER_LOG_FILE='mysql-bin.000013',
       MASTER_LOG_POS=107; 
        
    START SLAVE;


    And start master.
Done!

No comments:

Post a Comment