MySQL 主从复制做读写分离

规划：

ip	节点
192.168.1.205	master
192.168.1.206	slave

主服务器操作：

1）修改 my.cnf 参数，并重启mysql服务：

[mysqld]

server-id=1 # 服务器ID， 必须是唯一的
log-bin=mysql-bin # 启动二进制文件

2）登录 mysql，在 mysql 中添加一个backup账号，并授权给从服务器

mysql> grant replication slave on *.* to 'backup'@'192.168.1.206' identified by 'backup';

3）查看主数据库状态，并记下FILE及Position的值，这个在后面配置从服务器的时候要用到

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      263 |              |                  |
+------------------+----------+--------------+------------------+

从服务器操作：

1）修改 my.cnf 参数，并重启mysql服务：

[mysqld]

server-id=10 # 服务器ID， 必须是唯一的

2）登录到mysql，指定master_host, master_user, master_password, master_log_file, master_log_pos :

change master to master_host='192.168.1.205',master_user='backup',master_password='backup',master_log_file='mysql-bin.000007',master_log_pos=263;

3）启动 slave 同步：

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

4）检查主从同步，如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes，则主从复制连接正常。

mysql> show slave status\G

验证配置是否正常，mysql主从能否正常复制

登录master，写入数据：

mysql> create database master_slave_test character set 'utf8' collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use master_slave_test
Database changed
mysql> create table user(id int(5),name char(10));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into user values (00001,'zhangsan');
Query OK, 1 row affected (0.03 sec)

登录slave，查看数据是否同步：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master_slave_test  |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use master_slave_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------------+
| Tables_in_master_slave_test |
+-----------------------------+
| user                        |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select * from user;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.03 sec)