本文我们主要介绍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上；
关于MySQL Replication环境搭建的知识就介绍到这里，如果您想了解更多关于MySQL数据库的知识，可以看一下这里的文章：http://database.51cto.com/mysql/，相信一定会带给您收获的！
【编辑推荐】
