5.lamp-----mysql(慢查询、Mysql路由器)

一、慢查询

[root@server1 ~]# /etc/init.d/mysqld start 
[root@server2 ~]# /etc/init.d/mysqld start
[root@server3 ~]# /etc/init.d/mysqld start
 Starting MySQL SUCCESS! 
[root@server1 ~]# mysql -pwestos 
mysql> show variables like 'slow%'; ##查看慢查询相关参数 
mysql> set global slow_query_log=ON; ##开启慢查询
mysql> show variables like "long%"; ##查看慢查询的时间，默认为10s； 
mysql> select sleep(10); 
[root@server1 data]# pwd 
/usr/local/mysql/data 
[root@server1 data]# cat server1-slow.log ##查看慢查询的日志文件，有记录的日志信息

二、mysql路由器

- 安装：
 # rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm mysql
 原装的路由 - 配置：(读写分离) 
 # vim /etc/mysqlrouter/mysqlrouter.conf 
 [routing:ro] ##进行只读时候选择7001端口 
 bind_address = 0.0.0.0 
 bind_port = 7001 
 destinations = 172.25.0.1:3306,172.25.0.2:3306,172.25.0.3:3306 
 routing_strategy = round-robin ##采用的负载均衡的模式 
 [routing:rw] ##进行读写的时候采用7002端口 
 bind_address = 0.0.0.0 
 bind_port = 7002 
 destinations = 172.25.0.1:3306,172.25.0.2:3306,172.25.0.3:3306
  routing_strategy = first-available ##第一可用模式，1不可用了才往2里面写 - 不止可以使用多主模式。也可以使用主从复制

1.安装配置路由

[root@server1 data]# mysql -pwestos
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> STOP GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
server2 :mysql> START GROUP_REPLICATION;
server3: mysql> START GROUP_REPLICATION;
server1:mysql> SELECT * FROM performance_schema.replication_group_members;	

2.路由器server4

[root@server4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm  ##安装路由
[root@server4 ~]# cd /etc/mysqlrouter/ ##进入配置目录，编写配置文件
[root@server4 mysqlrouter]# ls
mysqlrouter.conf
[root@server4 mysqlrouter]# vim mysqlrouter.conf 
[keepalive]
interval = 60  ##高可用，60s切换主机

[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001  ##通过端口访问
destinations = 172.25.15.1:3306,172.25.15.2:3306,172.25.15.3:3306  ##配合后端组复制
routing_strategy = round-robin ##round-robin负载均衡

[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.15.1:3306,172.25.15.2:3306,172.25.15.3:3306
routing_strategy = first-available

[root@server4 mysqlrouter]# systemctl start mysqlrouter.service ##启动服务
[root@server4 mysqlrouter]# netstat -antlp ##查看7001 7002端口
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      3515/mysqlrouter       
tcp        0      0 0.0.0.0:7002            0.0.0.0:*               LISTEN      3515/mysqlrouter  

建立一个用户，作为客户端访问后端数据库的授权用户，多主模式下server2，server3会自动同步建立用户。

server1
mysql> grant all on test.* to wxh@'%' identified by 'westos';##可写

3.真机远程登陆数据库

[root@foundation15 ]# mysql -h 172.25.15.4 -P 7001 -u wxh -p
mysql> show databases;
mysql> use test	#进入数据库test
mysql> select * from test.t1;	#查看表t1
[root@foundation15 ]# mysql -h 172.25.15.4 -P 7002 -u wxh -p
mysql> insert into test.t1 values (5,'wxh'); 	#写入数据
mysql> select * from test.t1;	#查看表t1
写入完成
在server1、server2、server3三台机器上完成同步
[root@server1 data]# yum install -y lsof
[root@server1 data]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  3938 mysql   30u  IPv6  44930      0t0  TCP *:mysql (LISTEN)
mysqld  3938 mysql   80u  IPv6  47390      0t0  TCP server11:mysql->server14:57662 (ESTABLISHED)

[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  8947 mysql   29u  IPv6  43398      0t0  TCP *:mysql (LISTEN)
mysqld  8947 mysql   55u  IPv6  45132      0t0  TCP server12:mysql->server14:47216 (ESTABLISHED)

[root@server3 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  8939 mysql   14u  IPv6  42861      0t0  TCP *:mysql (LISTEN)
mysqld  8939 mysql   56u  IPv6  43644      0t0  TCP server13:mysql->server14:38874 (ESTABLISHED)