MySQL数据库的主从配置(二)

1 案例1：实现MySQL读写分离

1.1 问题
• 搭建一主一从结构
• 配置maxscale代理服务器
• 测试分离配置

1.2 方案
使用4台RHEL 7虚拟机，如图-1所示。其中192.168.4.10和192.168.4.20，分别提供读、写服务，均衡流量，通过主从复制保持数据一致性，由MySQL代理192.168.4.100面向客户端，收到SQL写请求时，交给服务器A处理，收到SQL读请求时，交给服务器B处理。linux客户机用于测试配置，可以使用真机代替

图－1
1.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：搭建主从
1）搭建一主一从结构，主库192.168.4.10上面操作

1.	[root@zlz ~]# vim /etc/my.cnf
2.	[mysqld]
3.	server_id=10    //指定服务器ID号 
4.	log-bin=master10        //启用binlog日志，并指定文件名前缀
5.	...
6.	[root@zlz ~]# systemctl restart mysqld        //重启mysqld

2）从库192.168.4.20上面操作

1.	[mysqld]
2.	server_id=20            //指定服务器ID号，不要与Master的相同
3.	log-bin=slave20        //启动SQL日志，并指定文件名前缀
4.	read_only=1            //只读模式
5.	...
6.	[root@zlz ~]# systemctl restart  mysqld

3）主库授权一个用户并查看master的状态

1.	[root@zlz ~]# mysql -u root -p123456
2.	mysql> grant all on *.* to 'replicater'@'%' identified by '123456';
3.	Query OK, 0 rows affected, 1 warning (0.00 sec)
4.	mysql> show master status;
5.	+-----------------+----------+--------------+------------------+-------------------+
6.	| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
7.	+-----------------+----------+--------------+------------------+-------------------+
8.	| master10.000002 |      449 |              |                  |                   |
9.	+-----------------+----------+--------------+------------------+-------------------+
10.	1 row in set (0.00 sec)

4）从库通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置（参考MASTER上的状态输出）

1.	[root@zlz ~]# mysql -u root -p123456
2.	mysql> change master to master_host='192.168.4.10',
3.	    -> master_user='replicater',
4.	    -> master_password='123456',
5.	    -> master_log_file='master10.000002',
6.	    -> master_log_pos=738;
7.	Query OK, 0 rows affected, 2 warnings (0.01 sec)
8.	
9.	mysql> start slave;
10.	Query OK, 0 rows affected (0.01 sec)
11.	
12.	mysql> show  slave status\G;
13.	*************************** 1. row ***************************
14.	               Slave_IO_State: Waiting for master to send event
15.	                  Master_Host: 192.168.4.10
16.	                  Master_User: replicater
17.	                  Master_Port: 3306
18.	                Connect_Retry: 60
19.	              Master_Log_File: master10.000002
20.	          Read_Master_Log_Pos: 738
21.	               Relay_Log_File: slave20-relay-bin.000002
22.	                Relay_Log_Pos: 319
23.	        Relay_Master_Log_File: master10.000002
24.	             Slave_IO_Running: Yes
25.	            Slave_SQL_Running: Yes
26.	              Replicate_Do_DB: 
27.	          Replicate_Ignore_DB: 
28.	           Replicate_Do_Table: 
29.	       Replicate_Ignore_Table: 
30.	      Replicate_Wild_Do_Table: 
31.	  Replicate_Wild_Ignore_Table: 
32.	                   Last_Errno: 0
33.	                   Last_Error: 
34.	                 Skip_Counter: 0
35.	          Exec_Master_Log_Pos: 738
36.	              Relay_Log_Space: 528
37.	              Until_Condition: None
38.	               Until_Log_File: 
39.	                Until_Log_Pos: 0
40.	           Master_SSL_Allowed: No
41.	           Master_SSL_CA_File: 
42.	           Master_SSL_CA_Path: 
43.	              Master_SSL_Cert: 
44.	            Master_SSL_Cipher: 
45.	               Master_SSL_Key: 
46.	        Seconds_Behind_Master: 0
47.	Master_SSL_Verify_Server_Cert: No
48.	                Last_IO_Errno: 0
49.	                Last_IO_Error: 
50.	               Last_SQL_Errno: 0
51.	               Last_SQL_Error: 
52.	  Replicate_Ignore_Server_Ids: 
53.	             Master_Server_Id: 10
54.	                  Master_UUID: 95ada2c2-bb24-11e8-abdb-525400131c0f
55.	             Master_Info_File: /var/lib/mysql/master.info
56.	                    SQL_Delay: 0
57.	          SQL_Remaining_Delay: NULL
58.	      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
59.	           Master_Retry_Count: 86400
60.	                  Master_Bind: 
61.	      Last_IO_Error_Timestamp: 
62.	     Last_SQL_Error_Timestamp: 
63.	               Master_SSL_Crl: 
64.	           Master_SSL_Crlpath: 
65.	           Retrieved_Gtid_Set: 
66.	            Executed_Gtid_Set: 
67.	                Auto_Position: 0
68.	         Replicate_Rewrite_DB: 
69.	                 Channel_Name: 
70.	           Master_TLS_Version: 
71.	1 row in set (0.00 sec)

5）测试，主库创建aa库

1.	mysql> create database aa;
2.	Query OK, 1 row affected (0.00 sec)
3.	
4.	mysql> show databases;
5.	+--------------------+
6.	| Database           |
7.	+--------------------+
8.	| informatio