Bookmark and Share
Implementing MySQL Database Replication (step by step)
(Publish Date: 2009-10-16 3:15pm, Total Visits: 2721, Today: 1, This Week: 1, This Month: 9)

Master:IP1, Slave: IP2

Master site (IP1)

1, Reconfigure a new my.cnf.bak file under /etc

    Using my-large.cnf as a model file, change the following item

1)    key_buffer = 128m
2)    myisam_sort_buffer_size = 32m
3)    query_cache_size= 32M
4)    sort_buffer_size = 128M
5)    server-id = 1  
6)    log-slow-queries = /var/log/mysql/mysqld-slow.log
7)    long_query_time = 2
8)    log-bin = /var/log/mysql/mysql-bin

2, Add ‘repl’ user
    $ mysql -u root -P 3306 -S /var/lib/mysql/mysql.sock -p
     mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
    -> repl@"IP2" IDENTIFIED BY 'Password';
    mysql> show grants for repl@"IP2";

3, Check users in the database
    mysql> select host,user from user;
+------------------------+-----------+
| host                   | user      |
+------------------------+-----------+
| localhost              |           |
| IP2                     | repl      |
+------------------------+-----------+

4, Shutdown mysql
    $ /etc/init.d/mysql stop

5, update /etc/my.cnf with new my.cnf
    $cd /etc
    $mv my.cnf my.cnf.0308
    $mv my.cnf.bak my.cnf

6,Start mysql
    $ /etc/init.d/mysql start

7, Backup master site data directory
1)    become root
2)    block the tables for updates, record bin log position
    mysql –u root –p –S /var/lib/mysql/mysql.sock
    mysql>FLUSH TABLES WITH READ LOCK;
   
    # record bin log position

    mysql> SHOW MASTER STATUS;

+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| wg-webt1-bin.000001 |   382973 |              |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
   
3)    backup whole mysql directory
    cd /var/lib/
    tar cvf /home/gwu/db-backup/mysql-data.tar mysql
    # check if it is good
    tar tvf /home/gwu/db-backup/mysql-data.tar
4)    unlock the tables
    mysql –u root –p –S /var/lib/mysql/mysql.sock
    mysql> UNLOCK TABLES;

5)    transfer mysql-data.tar from master site to /tmp on zhang2(
    cd /home/gwu/db-backup/
    scp mysql-data.tar admin@IP2:/tmp

Slave site (IP2)   
1)    become root

2)    shutdown mysql-group1
        $/etc/init.d/mysql stop

3)    delete the old mysql data files
    $cd /var/lib/
    $rm –rf mysql

4)    untar the mysql-data.tar
        $mv /home/gwu/mysql-data.tar /var/lib
    $cd /var/lib
        $tar xvf mysql-data.tar

5)    remove bin log files and bin log index files
    $cd /var/lib/mysql
     $rm db1-bin.*

6)    configure the option file under /etc, add the following item
    server-id = 2

7)    start slave
    $/etc/init.d/mysql start

8)    based on the commented slave settings in my.cnf, issue ‘change master’ statement:

    mysql> CHANGE MASTER TO
        -> MASTER_HOST='IP1',
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='PASSWORD',
        -> MASTER_LOG_FILE=' wg-webt1-bin.000001 ',
      -> MASTER_LOG_POS=382973;
    mysql> start slave;

•    Check master mysql’s status
    mysql -u root -S /var/lib/mysql/mysql –p
    mysql>SHOW MASTER STATUS \G
    mysql>SHOW MASTER LOGS;

•    Check on slave the mysql’s status
    mysql -u root -S /var/lib/mysql/mysql.sock –p
          mysql> SHOW SLAVE STATUS \G

      If you see “Slave_IO_Running: Yes
                      Slave_SQL_Running: Yes”,
      then slave is ok.

•    Set up “read only” privileges on slave if necessary

•    Test it by insert a record on a table in master database, and see if it shows on slave server.

On master:
    Mysql>use mysql;
    Mysql> create table student (name char(10),age integer(2));
    On slave, check with test, you will find table student, and use ‘select * from student’, you will find no data in it.

    On master,
    Mysql>insert into student(name,age) values('Tommy',20);
    Mysql> delete from student where name='Tommy';

    Check on the slave, using ‘select * from student’, you will find the result already changed.

    +------+------+
    | name | age  |
    +------+------+
    | Tom  |   20 |
    +------+------+
    1 row in set (0.00 sec)

    On master,
    Mysql>drop table student;

Check on slave, you will find that “student” already be removed.