MySQL Replication 環境搭建小記

MySQL Replication是比較簡單且常見的一種資料庫負載平衡方法。

這裡記錄一下環境搭建的具體過程：

環境：兩台ubuntu，一台做Master，一台Slave。

Master: ubuntub(192.168.1.101)

Slave: ubuntuc(192.168.1.104)

 

Master上:

ubuntub@ubuntub:/etc/mysql$ sudo vi my.cnf

 #bind-address = 127.0.0.1  //注釋掉

server-id               = 1  // 給master設定為1，給slave設定為2
log_bin                 = /var/log/mysql/mysql-bin.log

ubuntub@ubuntub:/etc/mysql$ sudo /etc/init.d/mysql restart
ubuntub@ubuntub:/etc/mysql$ mysql -uroot -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'%' IDENTIFIED BY 'rep_password';
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 |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      261 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)

mysql> use rep;

mysql> select * from user;
+-------+----+
| name  | id |
+-------+----+
| B     |  1 |
| user3 |  2 |
+-------+----+
2 rows in set (0.00 sec)

 

ubuntub@ubuntub:/data/mysql_bak$ mysqldump -uroot -p rep > dbdump.sql

ubuntub@ubuntub:/data/mysql_bak$ mysql -u root -p

mysql> UNLOCK TABLES;

 

ubuntub@ubuntub:/data/mysql_bak$ scp /data/mysql_bak/dbdump.sql ubuntuc@192.168.1.104:/data/      //把備份資料倒倒slave上

 

在Slave上:

 

ubuntuc@ubuntuc:/data$ sudo vi /etc/mysql/my.cnf

 

#bind-address           = 127.0.0.1

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log

 

ubuntuc@ubuntuc:/etc/mysql$ sudo /etc/init.d/mysql restart

ubuntuc@ubuntuc:/data$ mysql -u root -p

mysql> use rep;

mysql> source /data/dbdump.sql   //把資料匯入

mysql> CHANGE MASTER TO                            //Change Master
    -> MASTER_HOST='192.168.1.101',
    -> MASTER_USER='rep_user',
    -> MASTER_PASSWORD='rep_password',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=261;

mysql> START SLAVE;

 

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      746 |              |                  |
+------------------+----------+--------------+------------------+

 

mysql> show slave status;  //查看一下沒有錯誤，比如server-id有衝突的話，可能會導致一些錯誤。

 

測試：

1）在Master上建立一個資料庫，然後在Slave上查看，show databases; 應該是可以看到；

2） Master上對rep資料庫的user表進行操作，結果立即複製倒slave上；

參考資料：

http://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html