Mywql主从同步+读写分离

Mysql主从同步+读写分离

步骤一：搭建主从

1）搭建一主一从结构，主库上面操作

[root@master10 ~]# vim /etc/my.cnf
[mysqld]
server_id=10    //指定服务器ID号 
log-bin=master10        //启用binlog日志，并指定文件名前缀
...

[root@master10 ~]# systemctl restart mysqld        //重启mysqld

2）从库上面操作

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

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

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

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

[root@slave20 ~]# mysql -u root -p123456
mysql> change master to master_host='192.168.4.10',
    -> master_user='replicater',
    -> master_password='123456',
    -> master_log_file='master10.000002',
    -> master_log_pos=738;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show  slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.10
                  Master_User: replicater
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master10.000002
          Read_Master_Log_Pos: 738
               Relay_Log_File: slave20-relay-bin.000002
                Relay_Log_Pos: 319
        Relay_Master_Log_File: master10.000002
             Slave_IO_Running: Yes              //查看到两个yes即为正确
            Slave_SQL_Running: Yes
            ......
            ......

步骤二：实现mysql读写分离

1）配置数据读写分离服务器192.168.4.100
环境准备关闭防火墙和SElinux，保证yum源可以正常使用

[root@maxscale ~]# cd mysql/
[root@maxscale mysql]# ls
maxscale-2.1.2-1.rhel.7.x86_64.rpm
[root@maxscale mysql]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm       

//安装maxscale

warning: maxscale-2.1.2-1.rhel.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 8167ee24: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:maxscale-2.1.2-1                 ################################# [100%]

2）配置maxscale

    [root@maxscale mysql]# vim /etc/maxscale.cnf.template
    [maxscale]
    threads=auto            //运行的线程的数量
    [server1]            //定义数据库服务器
    type=server
    address=192.168.4.10        //数据库服务器的ip
    port=3306
    protocol=MySQLBackend        //后端数据库
    [server2]
    type=server
    address=192.168.4.20
    port=3306
    protocol=MySQLBackend
    [MySQL Monitor]                //定义监控的数据库服务器
    type=monitor
    module=mysqlmon
    servers=server1, server2        //监控的数据库列表，不能写ip
    user=scalemon                    //监视数据库服务器时连接的用户名scalemon
    passwd=123456                    //密码123456
    monitor_interval=10000        //监视的频率 单位为秒
    #[Read-Only Service]        //不定义只读服务器
    #type=service
    #router=readconnroute
    #servers=server1
    #user=myuser
    #passwd=mypwd
    #router_options=slave
    [Read-Write Service]            //定义读写分离服务
    type=service
    router=readwritesplit
    servers=server1, server2
    user=maxscaled            //用户名 验证连接代理服务时访问数据库服务器的用户是否存在
    passwd=123456                //密码
    max_slave_connections=100%
    [MaxAdmin Service]        //定义管理服务
    type=service
    router=cli
    #[Read-Only Listener]        //不定义只读服务使用的端口号
    #type=listener
    #service=Read-Only Service
    #protocol=MySQLClient
    #port=4008
    [Read-Write Listener]            //定义读写服务使用的端口号
    type=listener
    service=Read-Write Service
    protocol=MySQLClient
    port=4006
    [MaxAdmin Listener]        //管理服务使用的端口号
    type=listener
    service=MaxAdmin Service
    protocol=maxscaled
    socket=default
    port=4099     //手动添加，不指定时使用的是默认端口在启动服务以后可以知道默认端口是多少

3）根据配置文件的设置，在数据库服务器上添加授权用户（主库执行，从库查看）
mysql> grant replication slave,replication client on *.* to scalemon@'%' identified by "123456"; //监控数据库服务器时，连接数据库服务器的用户

Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant select on mysql.* to maxscaled@"%" identified by "123456"; 
//验证 访问数据时，连接数据库服务器使用的用户，是否在数据库服务器上存在的，连接用户
Query OK, 0 rows affected, 1 warning (0.01 sec)

4）查看授权用户
在主库上面查看

mysql> select user,host from mysql.user where user in ("scalemon","maxscaled");
+-----------+------+
| user      | host |
+-----------+------+
| maxscaled | %    |
| scalemon  | %    |
+-----------+------+
2 rows in set (0.00 sec)
在从库上面查看
mysql> select user,host from mysql.user where user in ("scalemon","maxscaled"); 
+-----------+------+
| user      | host |
+-----------+------+
| maxscaled | %    |
| scalemon  | %    |
+-----------+------+
2 rows in set (0.00 sec)
测试授权用户
[root@maxscale mysql]# mysql -h 192.168.4.10 -u scalemon -p123456
[root@maxscale mysql]# mysql -h 192.168.4.20 -u scalemon -p123456
[root@maxscale mysql]# mysql -h 192.168.4.10 -u maxscaled -p123456
[root@maxscale mysql]# mysql -h 192.168.4.20 -u maxscaled -p123456

5）启动服务

[root@maxscale ~]# maxscale -f  /etc/maxscale.cnf   
[root@maxscale ~]# ps -C  maxscale        //查看进程
  PID TTY          TIME CMD
17930 ?        00:00:00 maxscale   
[root@maxscale ~]# netstat  -antup | grep maxscale      //查看端口
tcp6       0      0 :::4099                 :::*                    LISTEN      17930/maxscale      
tcp6       0      0 :::4006                 :::*                    LISTEN      17930/maxscale

6）测试，在本机访问管理端口查看监控状态
maxadmin -P端口 -u用户名 -p密码

[root@maxscale ~]# maxadmin -P4099 -uadmin   -pmariadb
MaxScale> 
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.4.10    |  3306 |           0 | Master, Running
server2            | 192.168.4.20    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

7）在客户端访问读写分离服务器（没有mysql命令可以安装）
mysql -h读写分离服务ip -P4006 -u用户名 -p密码

[root@slave53 ~]# mysql -h192.168.4.100  -P4006 -ureplicater -p123456
mysql> select  @@hostname;            //查看当前主机名
+------------+
| @@hostname |
+------------+
| slave20    |
+------------+
1 row in set (0.00 sec) 
mysql> create table t2(id int(4) );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into aa.t2 values(777);
Query OK, 1 row affected (0.01 sec)

在主库上面查看

mysql> use aa
mysql> select * from t2;
+------+
| id   |
+------+
|  777 |
+------+
1 row in set (0.00 sec)

从库（主库同步到从库）

mysql> use aa
mysql> select * from t2;
+------+
| id   |
+------+
|  777 |
+------+
1 row in set (0.00 sec)