MySQL数据库（七）数据读写分离，MySQL多实例

一、数据读写分离

    主机名       角色                       数据库         IP地址
    mysql57    代理服务器                 无       192.168.4.57/24
    mysql51    数据库服务器（主）   有       192.168.4.57/24
    mysql52    数据库服务器（从）   有       192.168.4.57/24

1.安装软件maxscale
[root@mysql57 ~]# systemctl stop mysqld
[root@mysql57 ~]# systemctl disable mysqld
    [root@mysql57 ~]# systemctl stop firewalld
    [root@mysql57 ~]# systemctl disable firewalld
[root@mysql57 ~]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm 

2.修改配置文件
[root@mysql57 ~]# cp /etc/maxscale.cnf /etc/maxscale.cnf.bak
[root@mysql57 ~]# vim /etc/maxscale.cnf
  9 [maxscale]
 10 threads=auto

 18 [server1]
 19 type=server
 20 address=192.168.4.51
 21 port=3306
 22 protocol=MySQLBackend
 23 
 24 [server2]
 25 type=server
 26 address=192.168.4.52
 27 port=3306
 28 protocol=MySQLBackend

 35 [MySQL Monitor]
 36 type=monitor
 37 module=mysqlmon
 38 servers=server1,server2
 39 user=mon
 40 passwd=123qqq...A
 41 monitor_interval=10000

 52 #[Read-Only Service]
 53 #type=service
 54 #router=readconnroute
 55 #servers=server1
 56 #user=myuser
 57 #passwd=mypwd
 58 #router_options=slave

 63 [Read-Write Service]
 64 type=service
 65 router=readwritesplit
 66 servers=server1,server2
 67 user=mon2
 68 passwd=123qqq...A
 69 max_slave_connections=100%

 75 [MaxAdmin Service]
 76 type=service
 77 router=cli

 85 #[Read-Only Listener]
 86 #type=listener
 87 #service=Read-Only Service
 88 #protocol=MySQLClient
 89 #port=4008

 91 [Read-Write Listener]
 92 type=listener
 93 service=Read-Write Service
 94 protocol=MySQLClient
 95 port=4006
 96 
 97 [MaxAdmin Listener]
 98 type=listener
 99 service=MaxAdmin Service
100 protocol=maxscaled
101 socket=default
102 port=4016

3.配置数据库服务
    授权监控用户和路由用户
[root@mysql51 ~]# mysql -uroot -ptarena
mysql> grant replication slave,replication client on *.* to mon@"%" identified by "123qqq...A";
mysql>  grant select on mysql.* to mon2@"%" identified by "123qqq...A";
[root@mysql52 ~]# mysql -uroot -ptarena
mysql> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | mon           |
| %         | mon2          |
| %         | yaya99        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

4.启动代理服务
[root@mysql57 ~]# maxscale -f /etc/maxscale.cnf -U maxscale
[root@mysql57 ~]# ls /var/log/maxscale
maxscale.log

5.查看服务状态
[root@mysql57 ~]# ps -C maxscale
   PID TTY          TIME CMD
  9683 ?        00:00:00 maxscale
[root@mysql57 ~]# netstat -utlnp|grep maxscale
tcp6       0      0 :::4006                 :::*                    LISTEN      9683/maxscale       
tcp6       0      0 :::4016                 :::*                    LISTEN      9683/maxscale   

6.客户端测试配置
[root@mysql57 ~]# maxadmin -uadmin -pmariadb -P4016
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.4.51    |  3306 |           0 | Master, Running
server2            | 192.168.4.52    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

二、MySQL多实例
    多实例，在一台物理机上运行多个数据库服务
1.配置多实例
    数据库目录
    服务使用的端口号
    错误日志文件
    进程pid号文件
    sock文件
[root@mysql58 ~]# yum -y install libaio
[root@mysql58 ~]# useradd -s /sbin/nologin mysql
[root@mysql58 ~]# tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz 
[root@mysql58 ~]# mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql
[root@mysql58 ~]# echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/bashrc 
[root@mysql58 ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root

[mysqld1]
datadir=/dir1
port=3307
log-error=/dir1/mysqld1.err
pid-file=/dir1/mysqld1.pid
socket=/dir1/mysqld1.sock

[mysqld2]
datadir=/dir2
port=3308
log-error=/dir2/mysqld2.err
pid-file=/dir2/mysqld2.pid
socket=/dir2/mysqld2.sock

[root@mysql58 ~]# mkdir /dir1 /dir2

2.管理多实例
启动服务
    mysqld_multi start 实例编号
[root@mysql58 ~]# mysqld_multi start 1
......
2020-07-10T05:33:31.430242Z 1 [Note] A temporary password is generated for root@localhost: c-MQlA9lj2j4
    第一次启动服务强制修改密码
    [root@mysql58 ~]# mysql -uroot -p'c-MQlA9lj2j4' -S /dir1/mysqld1.sock
    mysql> alter user root@"localhost" identified by "123456";
    
查看服务
[root@mysql58 ~]# netstat -ntlup|grep :3307
tcp6       0      0 :::3307                 :::*                    LISTEN      27813/mysqld   
连接服务
    mysql -uroot -p'123456' -S sock文件
[root@mysql58 ~]# mysql -uroot -p'123456' -S /dir1/mysqld1.sock

停止服务
    mysqld_multi --user=root --password=密码 stop 实例编号
[root@mysql58 ~]# mysqld_multi --user=root --password=123456 stop 1

3.使用多实例
    在主机mysql58上运行编号为3的多实例服务
    启用实例3的binlog日志
    允许mysql50主机使用admin用户连接密码tarena对gamedb库有完全权限
    在mysql50主机连接58主机的实例3
[root@mysql58 ~]# vim /etc/my.cnf
[mysqld3]
datadir=/dir3
port=3309
log_bin=binlog3
server_id=3
log-error=/dir3/mysqld3.err
pid-file=/dir3/mysqld3.pid
socket=/dir3/mysqld3.sock
[root@mysql58 ~]# mysql -uroot -p'123456' -S /dir3/mysqld3.sock
mysql> grant all on gamedb.* to admin@"192.168.4.50" identified by "tarena";
[root@mysql50 ~]# mysql -h192.168.4.58 -P3309 -uadmin -ptarena