devbox
码创造者
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

搭建基于keepalived+GTID的半同步主从复制MySQL集群_半同步复制搭建

作者：码创造者 | 2024-08-16 17:28:44

赞

踩

半同步复制搭建

目录

项目所需的服务器

项目所需服务器的ip地址

项目架构图

项目步骤：

一、搭建好4台MySQL数据库服务器，安装好MySQL系统，并且安装好半同步的插件

linux系统中一键安装mysql脚本：

在服务器上开启二进制日志功能

在master上配置半同步

在slave服务器上配置半同步

二、搭建好ansible服务器

安装ansible

编辑配置，建立主机清单：

让ansible和所有的MySQL节点服务器之间建立免密通道

将master上的二进制日志复制到本地，拿到基础数据后，传输至每台slave上

三、开启GTID功能，启动主从复制

在slave与master的配置文件中添加配置：

在master上新建一个授权用户，用于给slave来复制二进制日志

在slave上配置master info信息

四、配置一台延迟备份服务器，从MySQL-slave1上读取二进制文件

五、使用rsync+sersync实现文件实时同步，将master上的数据远程同步到ansible服务器（相当于一台异地备份服务器）上

在备份服务器--ansible服务器上配置rsync，备份服务器上的rsync是一个守护进程监听873端口

在数据源服务器上配置rsync

安装sersync工具，实时触发rsync进行同步--安装在数据源服务器上

在master上编写备份脚本，并设置计划任务定时备份

六、安装部署mysqlrouter中间件软件，实现读写分离

1.下载mysqlrouter

2.将下载好的rpm文件远程传输到虚拟机上，然后直接安装

3.修改配置文件/etc/mysqlrouter.conf

七、安装keepalived实现高可用，配置2个vrrp实例实现双vip的高可用功能

1.修改mysqlrouter机器配置文件

2.安装keepalived

3.修改配置文件

4.查看vip是否出现

八、使用sysbench压力测试工具测试整个MySQL集群的性能

 1.在客户机上安装sysbench

2.在master上库供测试使用

3.构造测试表和测试数据 

4.测试数据库读写性能

5.进行完压力测试之后，清除数据

九、搭建基于prometheus+grafana的监控系统，对数据库集群进行监控

1.安装部署mysqld_exporter组件

下载

上传mysqld_exporter软件包至各mysql节点

在各mysql服务器上同步解压，并移动至/usr/local/mysqld_exporter目录下

编辑配置文件

将mysqld_exporter命令加入环境变量，并设置后台启动

2.安装部署prometheus

上传安装包、解压并移动至/prometheus

将prometheus配置成service服务

访问prometheus服务页面

添加prometheus节点node，刷新服务

访问prometheus服务页面查看节点是否添加成功

3.安装grafana

上传grafana的安装包，并安装

启动grafana

访问grafana

在grafana中添加prometheus数据源

添加Dashboards模板（使用14057--出图效果好）

grafana效果展示

项目所需的服务器

4台MySQL服务器：搭建主从复制的集群，一个master服务器（MySQL-master），两个slave服务器（MySQL-slave1、MySQL-slave2），一个延迟备份服务器（delay-backup）--将备份操作在一定延迟时间内执行，以解决备份对源数据库服务器性能的影响

2台mysqlrouter服务器：安装keepalived软件，实现高可用的读写分离服务

1台ansible中控服务器：实现对MySQL整个集群里的服务器进行批量管理

项目所需服务器的ip地址

mysql-master        192.168.15.159

mysql-slave-1        192.168.15.157

mysql-slave-2        192.168.15.160

delay-backup         192.168.15.161

ansible中控机        192.168.15.162

mysql-router-1       192.168.15.169 vip：192.168.15.200

mysql-router-2       192.168.15.170 vip：192.168.15.210

项目架构图

项目步骤：

一、搭建好4台MySQL数据库服务器，安装好MySQL系统，并且安装好半同步的插件

从mysql官方下载好mysql二进制安装包：mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz

linux系统中一键安装mysql脚本：

  1. #解决软件的依赖关系并且安装需要的工具
  2. yum  install cmake ncurses-devel gcc  gcc-c++  vim  lsof bzip2 openssl-devel ncurses-compat-libs net-tools -y
  3.  
  4. #解压mysql二进制安装包
  5. tar xf mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz
  6.  
  7. #移动mysql解压后的文件到/usr/local下改名叫mysql
  8. #/usr/local/mysql是mysql的安装目录
  9. mv mysql-5.7.41-linux-glibc2.12-x86_64 /usr/local/mysql
  10.  
  11. #新建组和用户 mysql
  12. groupadd mysql
  13. #mysql这个用户的shell是/bin/false 属于mysql组，-r表示为系统用户
  14. useradd -r -g mysql -s /bin/false mysql
  15.  
  16. #关闭firewalld防火墙服务，并且设置开机不要启动
  17. service firewalld stop
  18. systemctl  disable  firewalld
  19.  
  20. #临时关闭selinux
  21. setenforce 0
  22. #永久关闭selinux
  23. sed -i '/^SELINUX=/ s/enforcing/disabled/'  /etc/selinux/config
  24.  
  25. #新建存放数据的目录
  26. mkdir /data/mysql -p
  27.  
  28. #修改/data/mysql目录的权限归mysql用户和mysql组所有，这样mysql用户启动的mysql进程可以对这个文件夹进行读写了
  29. chown mysql:mysql /data/mysql/
  30. #只是允许mysql这个用户和mysql组可以访问，其他人都不能访问
  31. chmod 750 /data/mysql/
  32.  
  33. #进入/usr/local/mysql/bin目录
  34. cd /usr/local/mysql/bin/
  35.  
  36. #初始化mysql
  37. ./mysqld  --initialize --user=mysql --basedir=/usr/local/mysql/  --datadir=/data/mysql 
  38.  
  39. #让mysql支持ssl方式登录的设置
  40. ./mysql_ssl_rsa_setup --datadir=/data/mysql/
  41.  
  42. #修改PATH变量，加入mysql bin目录的路径
  43. #临时修改PATH变量的值
  44. export PATH=/usr/local/mysql/bin/:$PATH
  45. #重新启动linux系统后也生效，永久修改
  46. echo  'PATH=/usr/local/mysql/bin:$PATH' >>/root/.bashrc
  47.  
  48. #复制support-files里的mysql.server文件到/etc/init.d/目录下叫mysqld
  49. cp  ../support-files/mysql.server   /etc/init.d/mysqld
  50.  
  51. #修改/etc/init.d/mysqld脚本文件里的datadir目录的值
  52. sed  -i '70c  datadir=/data/mysql'  /etc/init.d/mysqld
  53.  
  54. #生成/etc/my.cnf配置文件
  55. cat  >/etc/my.cnf  <<EOF
  56. [mysqld_safe]
  58. [client]
  59. socket=/data/mysql/mysql.sock
  61. [mysqld]
  62. socket=/data/mysql/mysql.sock
  63. port = 3306
  64. open_files_limit = 8192
  65. innodb_buffer_pool_size = 512M
  66. character-set-server=utf8
  68. [mysql]
  69. auto-rehash
  70. prompt=\\u@\\d \\R:\\m  mysql>
  71. EOF
  72.  
  73. #临时修改：修改内核的open file(允许1个进程打开的文件)的数量 默认情况下是1024
  74. ulimit -n 1000000
  75. #永久修改：设置开机启动的时候也配置生效
  76. echo "ulimit -n 1000000" >>/etc/rc.local
  77. chmod +x /etc/rc.d/rc.local
  78.  
  79. #老式的设置mysql服务开机启动
  80. #将mysqld添加到linux系统里服务管理名单里
  81. /sbin/chkconfig --add mysqld
  82. #设置mysqld服务开机启动
  83. /sbin/chkconfig mysqld on
  84.  
  85. #启动mysqld进程
  86. service mysqld start
  87.  
  88. #初次修改密码-p后面接的是系统自动生成的临时密码
  89. #初次修改密码需要使用--connect-expired-password 选项
  90. #-e 后面接的表示是在mysql里需要执行命令  execute 执行
  91. #set password='Sanchuang123#';  修改root用户的密码为Sanchuang123#
  92. mysql -uroot -p'FJjh7(li2sdu#' --connect-expired-password   -e  "set password='Sanchuang123#';"
  93.  
  94. #检验上一步修改密码是否成功，如果有输出能看到mysql里的数据库，说明成功。
  95. mysql -uroot -p'Sanchuang123#'  -e "show databases;"

在服务器上开启二进制日志功能

在配置文件中加入一下配置：

  1. log_bin
  2. server_id = 1 	#服务器的编号

在master上配置半同步

  1. root@(none) 20:04  mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';
  2. root@(none) 20:07  mysql>set global rpl_semi_sync_master_timeout = 1; 	#临时修改变量
  3. root@(none) 20:10  mysql>set global rpl_semi_sync_master_enabled = 1;

修改配置文件

  1. [root@sumeng-master ~]# vim /etc/my.cnf
  2. [mysqld]
  3. ...
  4. rpl_semi_sync_master_enabled=1
  5. rpl_semi_sync_master_timeout=1000  #1 second 超时时间设置为1s

修改配置文件后刷新master的mysqld服务

  1. [root@sumeng-master ~]# service mysqld restart
  2. Shutting down MySQL............. SUCCESS! 
  3. Starting MySQL.. SUCCESS!

在slave服务器上配置半同步

  1. root@(none) 20:06  mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
  2. root@(none) 20:16  mysql> set global rpl_semi_sync_slave_enabled=1;

修改从服务器上的配置文件

  1. [root@sumeng-slave ~]# vim /etc/my.cnf
  2. [mysqld]
  3. ...
  4. rpl_semi_sync_slave_enabled=1

刷新slave上的mysqld服务

  1. [root@sumeng-slave ~]# service mysqld restart
  2. Shutting down MySQL.. SUCCESS! 
  3. Starting MySQL.. SUCCESS!

二、搭建好ansible服务器

安装ansible

  1. [root@localhost ~]# yum install epel-release -y
  2. [root@localhost ~]# yum install ansible -y

编辑配置，建立主机清单：

master服务器ip：192.168.15.159

slave服务器ip：192.168.15.157、192.168.15.160、192.168.15.161

  1. [root@localhost ~]# vim /etc/ansible/hosts
  2. [db]
  3. 192.168.15.159
  4. 192.168.15.157
  5. 192.168.15.160
  6. 192.168.15.161
  7. [dbslaves]
  8. 192.168.15.161
  9. 192.168.15.157
  10. 192.168.15.160

让ansible和所有的MySQL节点服务器之间建立免密通道

  1. [root@localhost ~]# ssh-keygen -t rsa 	#生成秘钥
  2. [root@localhost ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.15.159 	#将本地生成的ssh公钥信息复制到远程主机上
  3. [root@localhost ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.15.157
  4. [root@localhost ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.15.160
  5. [root@localhost ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.15.161

依次测试免密通道是否建立成功

  1. [root@localhost ~]# ssh 'root@192.168.15.157'
  2. [root@localhost ~]# ssh 'root@192.168.15.159'
  3. [root@localhost ~]# ssh 'root@192.168.15.160'
  4. [root@localhost ~]# ssh 'root@192.168.15.161'

将master上的二进制日志复制到本地，拿到基础数据后，传输至每台slave上

  1. [root@localhost ~]# scp root@192.168.15.159:/root/all_db.SQL .
  2. [root@localhost ~]# ansible -m copy -a "src=/root/all_db.SQL dest=/root" dbslaves

在所有的slave机器上导入基础数据

mysql -uroot -p'Sanchuang123#' <all_db.SQL

三、开启GTID功能，启动主从复制

在slave与master的配置文件中添加配置：

  1. gtid-mode=ON 	#开启GTID功能
  2. enforce-gtid-consistency=ON

在master上新建一个授权用户，用于给slave来复制二进制日志

grant replication slave on *.* to 'zhouzhou'@'192.168.15.%' identified by 'Sanchuang123#';

在slave上配置master info信息

为了防止环境不纯洁，在开启slave前进行以下操作：

  1. root@(none) 16:32  mysql>reset master;
  2. Query OK, 0 rows affected (0.01 sec)
  3.  
  4. root@(none) 16:39  mysql>show master status;
  5. +--------------------------+----------+--------------+------------------+-------------------+
  6. | File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  7. +--------------------------+----------+--------------+------------------+-------------------+
  8. | sumeng-master-bin.000001 |      154 |              |                  |                   |
  9. +--------------------------+----------+--------------+------------------+-------------------+
  10. 1 row in set (0.00 sec)
  11.  
  12. root@(none) 16:39  mysql>stop slave;
  13. Query OK, 0 rows affected, 1 warning (0.00 sec)
  14.  
  15. root@(none) 16:40  mysql>reset slave all;
  16. Query OK, 0 rows affected (0.01 sec)

在slave上均配置master信息

  1. root@(none) 16:47  mysql>CHANGE MASTER TO MASTER_HOST='192.168.15.159',
  2.     -> MASTER_USER='zhouzhou',
  3.     -> MASTER_PASSWORD='Sanchuang123#',
  4.     -> MASTER_PORT=3306,
  5.     -> MASTER_AUTO_POSITION=1;
  6. Query OK, 0 rows affected, 2 warnings (0.01 sec)
  7.  
  8. root@(none) 16:47  mysql>start slave;
  9. Query OK, 0 rows affected (0.01 sec)

查看slave是否配置成功，Slave_IO_Running和Slave_SQL_Running都是yes说明IO线程和SQL线程都启动了

  1. root@(none) 16:57  mysql>show slave status\G;
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.15.159
  5.                   Master_User: zhouzhou
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: sumeng-master-bin.000002
  9.           Read_Master_Log_Pos: 154
  10.                Relay_Log_File: localhost-relay-bin.000002
  11.                 Relay_Log_Pos: 383
  12.         Relay_Master_Log_File: sumeng-master-bin.000002
  13.              Slave_IO_Running: Yes
  14.             Slave_SQL_Running: Yes
  15.               Replicate_Do_DB: 
  16.           Replicate_Ignore_DB: 
  17.            Replicate_Do_Table: 
  18.        Replicate_Ignore_Table: 
  19.       Replicate_Wild_Do_Table: 
  20.   Replicate_Wild_Ignore_Table: 
  21.                    Last_Errno: 0
  22.                    Last_Error: 
  23.                  Skip_Counter: 0
  24.           Exec_Master_Log_Pos: 154
  25.               Relay_Log_Space: 594
  26.               Until_Condition: None
  27.                Until_Log_File: 
  28.                 Until_Log_Pos: 0
  29.            Master_SSL_Allowed: No
  30.            Master_SSL_CA_File: 
  31.            Master_SSL_CA_Path: 
  32.               Master_SSL_Cert: 
  33.             Master_SSL_Cipher: 
  34.                Master_SSL_Key: 
  35.         Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37.                 Last_IO_Errno: 0
  38.                 Last_IO_Error: 
  39.                Last_SQL_Errno: 0
  40.                Last_SQL_Error: 
  41.   Replicate_Ignore_Server_Ids: 
  42.              Master_Server_Id: 1
  43.                   Master_UUID: b53f58ce-2a25-11ee-b708-000c29cf54b0
  44.              Master_Info_File: /data/mysql/master.info
  45.                     SQL_Delay: 0
  46.           SQL_Remaining_Delay: NULL
  47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  48.            Master_Retry_Count: 86400
  49.                   Master_Bind: 
  50.       Last_IO_Error_Timestamp: 
  51.      Last_SQL_Error_Timestamp: 
  52.                Master_SSL_Crl: 
  53.            Master_SSL_Crlpath: 
  54.            Retrieved_Gtid_Set: 
  55.             Executed_Gtid_Set: 
  56.                 Auto_Position: 1
  57.          Replicate_Rewrite_DB: 
  58.                  Channel_Name: 
  59.            Master_TLS_Version: 
  60. 1 row in set (0.00 sec)

配置时可能会出现的问题：slave的io线程起不来，原因是slave上的GTID编号比master上的还大，这就意味着slave上的数据比master上的还新，导致io线程启动不成功，解决的办法是在所有slave上清除master信息和salve信息，然后重新配置启动。

四、配置一台延迟备份服务器，从MySQL-slave1上读取二进制文件

  1. root@(none) 16:54  mysql>stop slave;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. root@(none) 17:10  mysql>reset master;
  5. Query OK, 0 rows affected (0.01 sec)
  6.  
  7. root@(none) 17:10  mysql>reset slave all;
  8. Query OK, 0 rows affected (0.01 sec)
  9.  
  10. root@(none) 17:10  mysql>CHANGE MASTER TO MASTER_HOST='192.168.15.157',
  11.     -> MASTER_USER='zhouzhou',
  12.     -> MASTER_PASSWORD='Sanchuang123#',
  13.     -> MASTER_PORT=3306,
  14.     -> MASTER_AUTO_POSITION=1;
  15. Query OK, 0 rows affected, 2 warnings (0.01 sec)
  16.  
  17. root@(none) 17:10  mysql>change master to master_delay = 10;
  18. Query OK, 0 rows affected (0.01 sec)
  19.  
  20. root@(none) 17:11  mysql>start slave;
  21. Query OK, 0 rows affected (0.00 sec)

其中设置延迟备份时，默认情况下的单位是秒

五、使用rsync+sersync实现文件实时同步，将master上的数据远程同步到ansible服务器（相当于一台异地备份服务器）上

在备份服务器--ansible服务器上配置rsync，备份服务器上的rsync是一个守护进程监听873端口

1.永久关闭selinux和防火墙

  1. [root@localhost ~]# service firewalld stop
  2. Redirecting to /bin/systemctl stop firewalld.service
  3. [root@localhost ~]# setenforce 0     #临时关闭
  4. [root@localhost ~]# vim /etc/selinux/config    #永久关闭，修改SELINUX=disabled
  5. # This file controls the state of SELinux on the system.
  6. # SELINUX= can take one of these three values:
  7. #     enforcing - SELinux security policy is enforced.
  8. #     permissive - SELinux prints warnings instead of enforcing.
  9. #     disabled - No SELinux policy is loaded.
  10. SELINUX=disabled
  11. # SELINUXTYPE= can take one of three values:
  12. #     targeted - Targeted processes are protected,
  13. #     minimum - Modification of targeted policy. Only selected processes are protected. 
  14. #     mls - Multi Level Security protection.
  15. SELINUXTYPE=targeted

2.安装rsync服务端软件

[root@localhost ~]# yum install rsync xinetd -y

3.设置rsync开机启动，并启动xinetd进程--提供保姆服务的进程，rsync就是它照顾的进程

  1. [root@localhost ~]# vim /etc/rc.d/rc.local
  2. ...
  3. /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
  4. [root@localhost ~]# chmod +x /etc/rc.d/rc.local
  5. [root@localhost ~]# systemctl start xinetd

4.创建rsync.conf配置文件

  1. [root@localhost ~]# vim /etc/rsyncd.conf
  2. uid = root
  3. gid = root
  4. use chroot = yes
  5. max connections = 0
  6. log file = /var/log/rsyncd.log
  7. pid file = /var/run/rsyncd.pid 
  8. lock file = /var/run/rsync.lock 
  9. secrets file = /etc/rsync.pass  
  10. motd file = /etc/rsyncd.Motd
  11. [back_data]
  12.     path = /backup
  13.     comment = A directory in which data is stored
  14.     ignore errors = yes
  15.     read only = no
  16.     hosts allow = 192.168.15.159

5.创建用户认证的文件

添加允许传输的用户和密码，格式：用户名:密码，可以设置多个

  1. [root@localhost ~]# vim /etc/rsync.pass
  2. sumeng:sumeng123456

6.设置文件权限--设置文件所有者读取、写入权限

  1. [root@localhost ~]# chmod 600 /etc/rsyncd.conf 
  2. [root@localhost ~]# chmod 600 /etc/rsync.pass

7.启动rsync和xinetd

  1. [root@localhost ~]# /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
  2. [root@localhost ~]# systemctl start xinetd

8.查看rsync监听的端口号

  1. [root@localhost ~]# netstat -anplut|grep rsync
  2. tcp        0      0 0.0.0.0:873             0.0.0.0:*               LISTEN      20313/rsync         
  3. tcp6       0      0 :::873                  :::*                    LISTEN      20313/rsync

在数据源服务器上配置rsync

1.永久关闭selinux以及防火墙

  1. [root@sumeng-master ~]# service firewalld stop
  2. Redirecting to /bin/systemctl stop firewalld.service
  3. [root@sumeng-master ~]# setenforce 0
  4. [root@sumeng-master ~]# vim /etc/selinux/config
  5. ...
  6. SELINUX=disabled
  7. ...

2.安装rsync服务端软件

  1. [root@sumeng-master ~]# yum install rsync xinetd -y
  2. [root@sumeng-master ~]# vim /etc/rc.d/rc.local
  3. ...
  4. /usr/bin/rsync --daemon     #添加该行
  5. [root@sumeng-master ~]# chmod +x /etc/rc.d/rc.local
  6. [root@sumeng-master ~]# vim /etc/rsyncd.conf
  7. log file = /var/log/rsyncd.log
  8. pid file = /var/run/rsyncd.pid
  9. lock file = /var/run/rsync.lock
  10. motd file = /etc/rsyncd.Motd
  11. [Sync]
  12. comment = Sync
  13. uid = root
  14. gid = root
  15. port= 873
  16. [root@sumeng-master ~]# systemctl start xinetd

3.创建认证密码文件

  1. [root@sumeng-master ~]# vim /etc/passwd.txt
  2. [root@sumeng-master ~]# cat /etc/passwd.txt 
  3. sumeng123456
  4. [root@sumeng-master ~]# chmod 600 /etc/passwd.txt     #设置文件权限

4.测试数据同步

数据源服务器192.168.15.159到备份服务器192.168.15.162之间的数据同步

  1. [root@sumeng-master ~]# rsync -avH --port=873 --progress --delete /backup root@192.168.15.162::back_data --password-file=/etc/passwd.txt
  2.  
  3. sending incremental file list
  4. backup/
  5.  
  6. sent 60 bytes  received 16 bytes  152.00 bytes/sec
  7. total size is 0  speedup is 0.00

注意：两边的服务器没有新建/backup目录会报错，导致不能备份过去

安装sersync工具，实时触发rsync进行同步--安装在数据源服务器上

1.修改inotify默认参数--默认内核参数太小

  1. [root@sumeng-master ~]# sysctl -w fs.inotify.max_queued_events="99999999"
  2. fs.inotify.max_queued_events = 99999999
  3. [root@sumeng-master ~]# sysctl -w fs.inotify.max_user_watches="99999999"
  4. fs.inotify.max_user_watches = 99999999
  5. [root@sumeng-master ~]# sysctl -w fs.inotify.max_user_instances="65535"
  6. fs.inotify.max_user_instances = 65535
  7. [root@sumeng-master ~]# vim /etc/sysctl.conf    #永久修改参数
  8. fs.inotify.max_queued_events=99999999
  9. fs.inotify.max_user_watches=99999999
  10. fs.inotify.max_user_instances=65535

2.安装sersync

  1. #wget用于从指定网址下载网络文件
  2. [root@sumeng-master ~]# wget http://down.whsir.com/downloads/sersync2.5.4_64bit_binary_stable_final.tar.gz
  3. [root@sumeng-master ~]# tar xf sersync2.5.4_64bit_binary_stable_final.tar.gz
  4. [root@sumeng-master ~]# mv GNU-Linux-x86/ /usr/local/sersync

3.创建rsync

  1. [root@sumeng-master ~]# cd /usr/local/sersync/
  2. [root@sumeng-master sersync]# cp confxml.xml confxml.xml-bak    #备份配置文件，防止修改出错，便于还原
  3. [root@sumeng-master sersync]# cp confxml.xml data_configxml.xml     #复制用于同步data目录的文件，data_configxml.xml是后面需要使用的配置文件

4.修改配置文件data_configxml.xml

  1. [root@sumeng-master sersync]# vim data_configxml.xml
  2. <sersync>
  3.         <localpath watch="/backup">
  4.             <remote ip="192.168.15.162" name="back_data"/>
  5.             <!--<remote ip="192.168.8.39" name="tongbu"/>-->
  6.             <!--<remote ip="192.168.8.40" name="tongbu"/>-->
  7.         </localpath>
  8.         <rsync>
  9.             <commonParams params="-artuz"/>
  10.             <auth start="false" users="root" passwordfile="/etc/passwd.txt"/>
  11.             <userDefinedPort start="false" port="874"/><!-- port=874 -->
  12.             <timeout start="false" time="100"/><!-- timeout=100 -->
  13.             <ssh start="false"/>
  14.         </rsync>
  15.         <failLog path="/tmp/rsync_fail_log.sh" timeToExecute="60"/><!--default every 60mins execute once-->
  16.         <crontab start="false" schedule="600"><!--600mins-->
  17.             <crontabfilter start="false">
  18.                 <exclude expression="*.php"></exclude>
  19.                 <exclude expression="info/*"></exclude>
  20.             </crontabfilter>
  21.         </crontab>
  22.         <plugin start="false" name="command"/>
  23. </sersync>

5.启动服务

  1. #修改环境变量，可以直接使用文件名执行
  2. [root@sumeng-master sersync]# PATH=/usr/local/sersync:$PATH
  3. [root@sumeng-master sersync]# echo 'PATH=/usr/local/sersync:$PATH' >>/root/.bashrc
  4. [root@sumeng-master sersync]# sersync2 -d -r -o  /usr/local/sersync/data_configxml.xml
  5. set the system param
  6. execute：echo 50000000 > /proc/sys/fs/inotify/max_user_watches
  7. execute：echo 327679 > /proc/sys/fs/inotify/max_queued_events
  8. parse the command param
  9. option: -d 	run as a daemon
  10. option: -r 	rsync all the local files to the remote servers before the sersync work
  11. option: -o 	config xml name：  /usr/local/sersync/data_configxml.xml
  12. daemon thread num: 10
  13. parse xml config file
  14. host ip : localhost	host port: 8008
  15. daemon start，sersync run behind the console 
  16. config xml parse success
  17. please set /etc/rsyncd.conf max connections=0 Manually
  18. sersync working thread 12  = 1(primary thread) + 1(fail retry thread) + 10(daemon sub threads) 
  19. Max threads numbers is: 22 = 12(Thread pool nums) + 10(Sub threads)
  20. please according your cpu ，use -n param to adjust the cpu rate
  21. ------------------------------------------
  22. rsync the directory recursivly to the remote servers once
  23. working please wait...
  24. execute command: cd /backup && rsync -artuz -R --delete ./ 192.168.15.162::back_data >/dev/null 2>&1 
  25. run the sersync: 
  26. watch path is: /backup

6.设置sersync监控开机自动执行

  1. [root@sumeng-master sersync]# vim /etc/rc.d/rc.local
  2. ...
  3. /usr/local/sersync/sersync2 -d -r -o  /usr/local/sersync/data_configxml.xml    #添加此行

验证：去/backup目录下新建一些文件或者文件夹，测试是否在备份服务器上可以看到

在master上编写备份脚本，并设置计划任务定时备份

  1. [root@sumeng-master backup]# vim backup_alldb.sh
  2. [root@sumeng-master backup]# cat backup_alldb.sh 
  3. #!/bin/bash
  4.  
  5. mkdir -p /backup
  6. mysqldump -uroot -p"Sanchuang123#" --all-databases --triggers --routines --events >/backup/$(date +%Y%m%d%H%M%S)_all_db.SQL
  7. [root@sumeng-master backup]# crontab -e
  8. no crontab for root - using an empty one
  9. crontab: installing new crontab
  10. [root@sumeng-master backup]# crontab -l
  11. 30 2 * * * bash /backup/backup_alldb.sh

六、安装部署mysqlrouter中间件软件，实现读写分离

开启两台安装部署了mysqlrouter中间件软件的服务器，可以实现高可用

读写分离：将对数据库的增删改查等操作进行分离，读操作往slave上进行，读写操作都可以在master上进行，本质上起到了负载均衡的作用。

mysqlrouter是MySQL官方给我们提供的一个读写分离的软件，是一个轻量级MySQL中间件，只能通过不同的端口来实现简单的读/写分离，mysqlrouter的前提是后端实现了MySQL的主从复制。

1.下载mysqlrouter

https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-community-8.0.23-1.el7.x86_64.rpm

2.将下载好的rpm文件远程传输到虚拟机上，然后直接安装

  1. [root@mysql-router-1 ~]# rpm -ivh mysql-router-community-8.0.23-1.el7.x86_64.rpm 
  2. 警告：mysql-router-community-8.0.23-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
  3. 准备中...                          ################################# [100%]
  4. 正在升级/安装...
  5.    1:mysql-router-community-8.0.23-1.e################################# [100%]

3.修改配置文件/etc/mysqlrouter.conf

cd /etc/mysqlrouter        #进入存放配置文件的目录

添加以下代码

  1. #read
  2. [routing:slaves]
  3. bind_address = 192.168.15.169:7001
  4. destinations = 192.168.15.157:3306,192.168.15.160:3306
  5. mode = read-only        #只读模式
  6. connect_timeout = 1
  7.  
  8. #write and read
  9. [routing:masters]
  10. bind_address = 192.168.15.169:7002
  11. destinations = 192.168.15.159:3306
  12. mode = read-write         #可读可写
  13. connect_timeout = 1

4.启动mysqlrouter服务

  1. [root@mysql-router-1 mysqlrouter]# service mysqlrouter restart
  2. Redirecting to /bin/systemctl restart mysqlrouter.service
  3. [root@mysql-router-1 mysqlrouter]# netstat -anplut|grep mysqlrouter     #检查端口
  4. tcp        0      0 192.168.15.169:7001     0.0.0.0:*               LISTEN      8808/mysqlrouter    
  5. tcp        0      0 192.168.15.169:7002     0.0.0.0:*               LISTEN      8808/mysqlrouter

5.在master上创建2个测试账号，一个可读，一个可写

  1. root@(none) 09:56  mysql>grant all on *.* to 'scwrite'@'%' identified by 'Sanchuang123#';
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. root@(none) 09:56  mysql>grant select on *.* to 'scread'@'%' identified by 'Sanchuang123#';
  5. Query OK, 0 rows affected, 1 warning (0.01 sec)

6.在客户机上测试读写分离效果

  1. #只读用户无法对数据库进行更改
  2. [root@sc-docker ~]# mysql -h 192.168.15.169 -P 7001 -uscread -p"Sanchuang123#"
  3. mysql: [Warning] Using a password on the command line interface can be insecure.
  4. Welcome to the MySQL monitor.  Commands end with ; or \g.
  5. Your MySQL connection id is 9
  6. Server version: 5.7.41-log MySQL Community Server (GPL)
  7.  
  8. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
  9.  
  10. Oracle is a registered trademark of Oracle Corporation and/or its
  11. affiliates. Other names may be trademarks of their respective
  12. owners.
  13.  
  14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15.  
  16. scread@(none) 10:30  mysql>create database sumeng;
  17. ERROR 1044 (42000): Access denied for user 'scread'@'%' to database 'sumeng'
  18.  
  19. [root@sc-docker ~]# mysql -h 192.168.15.169 -P 7002 -uscwrite -p"Sanchuang123#"
  20. mysql: [Warning] Using a password on the command line interface can be insecure.
  21. Welcome to the MySQL monitor.  Commands end with ; or \g.
  22. Your MySQL connection id is 9
  23. Server version: 5.7.41-log MySQL Community Server (GPL)
  24.  
  25. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
  26.  
  27. Oracle is a registered trademark of Oracle Corporation and/or its
  28. affiliates. Other names may be trademarks of their respective
  29. owners.
  30.  
  31. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  32.  
  33. scwrite@(none) 10:35  mysql>create database Tuesday;
  34. Query OK, 1 row affected (0.00 sec)

读写分离的关键点是用户的权限，让不同的用户连接不同的端口，最后仍然要到后端的mysql服务器里去验证是否有读写的权限，mysqlrouter只是做了读写分流，让应用程序去连接不同的端口

七、安装keepalived实现高可用，配置2个vrrp实例实现双vip的高可用功能

高可用HA：不会出现单点故障，一个坏了，另外的能够顶替，不影响工作，有备份

keepalived是一个用c语言编写的路由软件，主要目标是为linux系统和基于linux的基础设施提供简单而强大的负载均衡和高可用性功能。vip：虚拟IP地址，会绑定在优先级高的机器上

1.修改mysqlrouter机器配置文件

将2台mysqlrouter机器配置文件/etc/mysqlrouter/mysqlrouter.conf中的bind_address配置修改为0.0.0.0:[port]，这样可以使得机器能够绑定vip。改完配置文件后记得刷新mysqlrouter服务

2.安装keepalived

[root@mysql-router-1 ~]# yum install keepalived -y

3.修改配置文件

keepalived配置文件存放路径为：/etc/keepalived/keepalived.conf

实现双vip的思路：创建2个vrrp实例，2个vip，2个实例互为主备

配置文件中每项配置的含义：

mysql-router-1配置：

  1. [root@mysql-router-1 ~]# cat /etc/keepalived/keepalived.conf 
  2. ! Configuration File for keepalived
  3.  
  4. global_defs {
  5.    notification_email {
  6.      acassen@firewall.loc
  7.      failover@firewall.loc
  8.      sysadmin@firewall.loc
  9.    }
  10.    notification_email_from Alexandre.Cassen@firewall.loc
  11.    smtp_server 192.168.200.1
  12.    smtp_connect_timeout 30
  13.    router_id LVS_DEVEL
  14.    vrrp_skip_check_adv_addr
  15.   #vrrp_strict
  16.    vrrp_garp_interval 0
  17.    vrrp_gna_interval 0
  18. }
  19.  
  20. vrrp_instance VI_1 {
  21.     state MASTER
  22.     interface ens33
  23.     virtual_router_id 99
  24.     priority 200
  25.     advert_int 1
  26.     authentication {
  27.         auth_type PASS
  28.         auth_pass 1111
  29.     }
  30.     virtual_ipaddress {
  31.         192.168.15.200
  32.     }
  33. }
  34.  
  35. vrrp_instance VI_2 {
  36.     state backup
  37.     interface ens33
  38.     virtual_router_id 66
  39.     priority 100
  40.     advert_int 1
  41.     authentication {
  42.         auth_type PASS
  43.         auth_pass 1111
  44.     }
  45.     virtual_ipaddress {
  46.         192.168.15.210
  47.     }
  48. }

mysql-router-2配置：

  1. [root@mysql-router-2 ~]# cat /etc/keepalived/keepalived.conf 
  2. ! Configuration File for keepalived
  3.  
  4. global_defs {
  5.    notification_email {
  6.      acassen@firewall.loc
  7.      failover@firewall.loc
  8.      sysadmin@firewall.loc
  9.    }
  10.    notification_email_from Alexandre.Cassen@firewall.loc
  11.    smtp_server 192.168.200.1
  12.    smtp_connect_timeout 30
  13.    router_id LVS_DEVEL
  14.    vrrp_skip_check_adv_addr
  15.   #vrrp_strict
  16.    vrrp_garp_interval 0
  17.    vrrp_gna_interval 0
  18. }
  19.  
  20. vrrp_instance VI_1 {
  21.     state backup
  22.     interface ens33
  23.     virtual_router_id 99
  24.     priority 100
  25.     advert_int 1
  26.     authentication {
  27.         auth_type PASS
  28.         auth_pass 1111
  29.     }
  30.     virtual_ipaddress {
  31.         192.168.15.200
  32.     }
  33. }
  34.  
  35. vrrp_instance VI_2 {
  36.     state MASTER
  37.     interface ens33
  38.     virtual_router_id 66
  39.     priority 200
  40.     advert_int 1
  41.     authentication {
  42.         auth_type PASS
  43.         auth_pass 1111
  44.     }
  45.     virtual_ipaddress {
  46.         192.168.15.210
  47.     }
  48. }

4.查看vip是否出现

mysql-router-1，vip为192.168.15.200：

  1. [root@mysql-router-1 ~]# ip add
  2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
  3.     link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  4.     inet 127.0.0.1/8 scope host lo
  5.        valid_lft forever preferred_lft forever
  6.     inet6 ::1/128 scope host 
  7.        valid_lft forever preferred_lft forever
  8. 2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
  9.     link/ether 00:0c:29:74:8a:2a brd ff:ff:ff:ff:ff:ff
  10.     inet 192.168.15.169/24 brd 192.168.15.255 scope global noprefixroute ens33
  11.        valid_lft forever preferred_lft forever
  12.     inet 192.168.15.200/32 scope global ens33
  13.        valid_lft forever preferred_lft forever
  14.     inet6 fe80::20c:29ff:fe74:8a2a/64 scope link 
  15.        valid_lft forever preferred_lft forever

mysql-router-2，vip为192.168.15.210：

  1. [root@mysql-router-2 ~]# ip add
  2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
  3.     link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  4.     inet 127.0.0.1/8 scope host lo
  5.        valid_lft forever preferred_lft forever
  6.     inet6 ::1/128 scope host 
  7.        valid_lft forever preferred_lft forever
  8. 2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
  9.     link/ether 00:0c:29:f4:23:bc brd ff:ff:ff:ff:ff:ff
  10.     inet 192.168.15.170/24 brd 192.168.15.255 scope global noprefixroute ens33
  11.        valid_lft forever preferred_lft forever
  12.     inet 192.168.15.210/32 scope global ens33
  13.        valid_lft forever preferred_lft forever
  14.     inet6 fe80::20c:29ff:fef4:23bc/64 scope link 
  15.        valid_lft forever preferred_lft forever

在dns域名里添加同一个域名对应2个vip，就可以实现dns的负载均衡

八、使用sysbench压力测试工具测试整个MySQL集群的性能

 1.在客户机上安装sysbench

  1. [root@sc-docker ~]# yum install epel-release -y
  2. [root@sc-docker ~]# yum install sysbench -y

2.在master上库供测试使用

  1. root@mysql 14:56  mysql>create database test_db;
  2. Query OK, 1 row affected (0.01 sec)

3.构造测试表和测试数据 

使用客户机远程构建测试表和测试数据时记得关闭防火墙，以免出现连接不成功的情况

  1. [root@localhost ~]# sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=192.168.15.200 --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123# --mysql-db=test_db --tables=10 --table_size=1000 oltp_read_write prepare
  2. sysbench 1.0.17 (using system LuaJIT 2.0.4)
  3.  
  4. Initializing worker threads...
  5.  
  6. Creating table 'sbtest1'...
  7. Creating table 'sbtest10'...
  8. Creating table 'sbtest7'...
  9. Creating table 'sbtest3'...
  10. Creating table 'sbtest4'...
  11. Creating table 'sbtest6'...
  12. Creating table 'sbtest8'...
  13. Creating table 'sbtest5'...
  14. Creating table 'sbtest9'...
  15. Creating table 'sbtest2'...
  16. Inserting 1000 records into 'sbtest4'
  17. Inserting 1000 records into 'sbtest7'
  18. Inserting 1000 records into 'sbtest5'
  19. Inserting 1000 records into 'sbtest10'
  20. Inserting 1000 records into 'sbtest6'
  21. Inserting 1000 records into 'sbtest9'
  22. Inserting 1000 records into 'sbtest8'
  23. Inserting 1000 records into 'sbtest1'
  24. Inserting 1000 records into 'sbtest2'
  25. Inserting 1000 records into 'sbtest3'
  26. Creating a secondary index on 'sbtest8'...
  27. Creating a secondary index on 'sbtest7'...
  28. Creating a secondary index on 'sbtest5'...
  29. Creating a secondary index on 'sbtest4'...
  30. Creating a secondary index on 'sbtest1'...
  31. Creating a secondary index on 'sbtest10'...
  32. Creating a secondary index on 'sbtest9'...
  33. Creating a secondary index on 'sbtest6'...
  34. Creating a secondary index on 'sbtest2'...
  35. Creating a secondary index on 'sbtest3'...

构建时使用的参数说明：

--db-driver=mysql：数据库驱动

--time=300：连续访问300秒

--threads=10：用10个线程模拟并发访问

--report-interval=1：每隔一秒输出一次测试进度报告，0表示仅输出最终报告结果，默认为0

--mysql-host=192.168.15.200 --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123# --mysql-db=test_db：表示连接数据库的信息，这里连接的是mysqlrouter机器配置的vip和端口

--tables=10 --table_size=1000：在测试库中构造10个测试表，每个测试表中构造1000条测试数据，测试数据太大可能会导致mysql集群的磁盘耗尽，集群崩盘

oltp_read_write：表示进行读写测试

prepare：准备阶段，创建测试用的表，并往其中填写数据

4.测试数据库读写性能

将prepare阶段切换为run阶段，进行压力测试

每秒的测试结果：

  1. [root@localhost ~]# sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=192.168.15.200 --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123# --mysql-db=test_db --tables=10 --table_size=1000 oltp_read_write run
  2. sysbench 1.0.17 (using system LuaJIT 2.0.4)
  3.  
  4. Running the test with following options:
  5. Number of threads: 10
  6. Report intermediate results every 1 second(s)
  7. Initializing random number generator from current time
  8.  
  9.  
  10. Initializing worker threads...
  11.  
  12. Threads started!
  13.  
  14. [ 1s ] thds: 10 tps: 286.27 qps: 5828.84 (r/w/o: 4095.29/1151.06/582.49) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
  15. [ 2s ] thds: 10 tps: 279.17 qps: 5609.51 (r/w/o: 3932.48/1118.69/558.34) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
  16. [ 3s ] thds: 10 tps: 234.86 qps: 4661.30 (r/w/o: 3253.11/939.46/468.73) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
  17. [ 4s ] thds: 10 tps: 178.46 qps: 3574.26 (r/w/o: 2506.47/710.86/356.93) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
  18. [ 5s ] thds: 10 tps: 174.15 qps: 3470.99 (r/w/o: 2425.09/696.60/349.30) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
  19. [ 6s ] thds: 10 tps: 164.90 qps: 3334.96 (r/w/o: 2334.57/671.59/328.80) lat (ms,95%): 81.48 err/s: 0.00 reconn/s: 0.00
  20. [ 7s ] thds: 10 tps: 118.21 qps: 2336.07 (r/w/o: 1637.85/461.80/236.41) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
  21. [ 8s ] thds: 10 tps: 135.99 qps: 2729.87 (r/w/o: 1914.91/541.97/272.99) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
  22. [ 9s ] thds: 10 tps: 156.04 qps: 3147.90 (r/w/o: 2201.63/634.18/312.09) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00
  23. [ 10s ] thds: 10 tps: 179.90 qps: 3597.09 (r/w/o: 2522.66/713.62/360.81) lat (ms,95%): 69.29 err/s: 1.00 reconn/s: 0.00

测试结果参数说明：

thds: 10:表示有10个线程在进行压力测试

tps：表示每秒执行了多少个事务

qps：表示每秒执行了多少个请求

(r/w/o: 4095.29/1151.06/582.49)：每秒有4095.29个读请求，1151.06个写请求，582.49个其他请求，其实本质就是对qps的请求进行了拆解

lat (ms,95%): 43.39：表示95%的请求延迟都在43.39毫秒以下

err/s: 0.00 reconn/s: 0.00：表示每秒有0请求失败，发生0次网络重连

总测试结果：

  1. [root@localhost ~]# sysbench --db-driver=mysql --time=60 --threads=10 --mysql-host=192.168.15.200 --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123# --mysql-db=test_db --tables=10 --table_size=1000 oltp_read_write run
  2. sysbench 1.0.17 (using system LuaJIT 2.0.4)
  3.  
  4. Running the test with following options:
  5. Number of threads: 10
  6. Initializing random number generator from current time
  7.  
  8.  
  9. Initializing worker threads...
  10.  
  11. Threads started!
  12.  
  13. SQL statistics:
  14.     queries performed:
  15.         read:                            126266
  16.         write:                           36073
  17.         other:                           18037
  18.         total:                           180376
  19.     transactions:                        9018   (150.03 per sec.)
  20.     queries:                             180376 (3000.88 per sec.)
  21.     ignored errors:                      1      (0.02 per sec.)
  22.     reconnects:                          0      (0.00 per sec.)
  23.  
  24. General statistics:
  25.     total time:                          60.1034s
  26.     total number of events:              9018
  27.  
  28. Latency (ms):
  29.          min:                                   21.10
  30.          avg:                                   66.58
  31.          max:                                  262.32
  32.          95th percentile:                      108.68
  33.          sum:                               600417.67
  34.  
  35. Threads fairness:
  36.     events (avg/stddev):           901.8000/13.27
  37.     execution time (avg/stddev):   60.0418/0.03

测试结果分析： 

SQL statistics 数据库语句统计

General statistics 综合统计，一共执行了9018次，实际执行时间为60.1034s

Latency (ms) 统计线程真正执行的时间，单位为毫秒，单次最少执行21.10ms，最多262.32ms，平均66.58ms，95%次执行时间在108.68ms，总执行600417.67ms

Threads fairness 总结统计执行事务与执行时间

5.进行完压力测试之后，清除数据

cleanup阶段：测试后删除测试数据

  1. [root@localhost ~]# sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=192.168.15.200 --mysql-port=7002 --mysql-user=scwrite --mysql-password=Sanchuang123# --mysql-db=test_db --tables=10 --table_size=1000 oltp_read_write cleanup
  2. sysbench 1.0.17 (using system LuaJIT 2.0.4)
  3.  
  4. Dropping table 'sbtest1'...
  5. Dropping table 'sbtest2'...
  6. Dropping table 'sbtest3'...
  7. Dropping table 'sbtest4'...
  8. Dropping table 'sbtest5'...
  9. Dropping table 'sbtest6'...
  10. Dropping table 'sbtest7'...
  11. Dropping table 'sbtest8'...
  12. Dropping table 'sbtest9'...
  13. Dropping table 'sbtest10'...

九、搭建基于prometheus+grafana的监控系统，对数据库集群进行监控

在新的一台服务器上安装部署prometheus+grafana，监控master、slave1、slave2、delay_backup服务器的性能

1.安装部署mysqld_exporter组件

在Prometheus架构中，exporter是负责收集数据并将信息汇报给Prometheus Server的组件，mysqld_exporter是常用的数据库监控工具

下载

下载地址：https://prometheus.io/download/

上传mysqld_exporter软件包至各mysql节点

先上传软件包至ansible服务器，然后批量下发至master及各slave服务器

  1. [root@localhost ~]# ls
  2. 2_all_db.SQL  3_all_db.SQL  all_db.SQL  anaconda-ks.cfg  mysqld_exporter-0.15.0.linux-amd64.tar.gz
  3. [root@localhost ~]# ansible -m copy -a "src=/root/mysqld_exporter-0.15.0.linux-amd64.tar.gz dest=/root" db
在各mysql服务器上同步解压，并移动至/usr/local/mysqld_exporter目录下
  1. [root@sumeng-master ~]# tar -zxvf mysqld_exporter-0.15.0.linux-amd64.tar.gz 
  2. mysqld_exporter-0.15.0.linux-amd64/
  3. mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter
  4. mysqld_exporter-0.15.0.linux-amd64/NOTICE
  5. mysqld_exporter-0.15.0.linux-amd64/LICENSE
  6. [root@sumeng-master ~]# mv mysqld_exporter-0.15.0.linux-amd64 /usr/local/mysqld_exporter
  7. [root@sumeng-master ~]# cd /usr/local/mysqld_exporter/
  8. [root@sumeng-master mysqld_exporter]# ls
  9. LICENSE  mysqld_exporter  NOTICE
编辑配置文件

先在数据库中创建好连接本机数据库的用户

  1. root@mysql 10:37  mysql>create user 'sumeng'@'%' identified by '123456';
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. root@mysql 10:38  mysql>grant all on *.* to 'sumeng'@'%';
  5. Query OK, 0 rows affected (0.00 sec)

然后去各服务器上同步编辑配置文件--在/usr/local/mysqld_exporter目录下编辑

  1. [root@sumeng-master mysqld_exporter]# vim my.cnf
  2. [root@sumeng-master mysqld_exporter]# cat my.cnf 
  3. [client]
  4. user=sumeng
  5. password=123456
将mysqld_exporter命令加入环境变量，并设置后台启动

各mysql服务器上同步进行

  1. [root@sumeng-master mysqld_exporter]# PATH=/usr/local/mysqld_exporter/:$PATH
  2. [root@sumeng-master mysqld_exporter]# echo "PATH=/usr/local/mysqld_exporter/:$PATH" >>/root/.bashrc
  3. [root@sumeng-master mysqld_exporter]# nohup mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/my.cnf &
  4. [1] 5877

查看进程和端口，检查是否开启

  1. [root@sumeng-master mysqld_exporter]# ps aux|grep exporter
  2. root       5877  0.0  0.4 719816  7984 pts/0    Sl   10:48   0:00 mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/my.cnf
  3. root       5883  0.0  0.0 112824   984 pts/0    S+   10:50   0:00 grep --color=auto exporter
  4. [root@sumeng-master mysqld_exporter]# netstat -anplut|grep mysqld
  5. tcp6       0      0 :::3306                 :::*                    LISTEN      4501/mysqld         
  6. tcp6       0      0 :::9104                 :::*                    LISTEN      5877/mysqld_exporte 
  7. tcp6       0      0 192.168.15.159:3306     192.168.15.157:44850    ESTABLISHED 4501/mysqld         
  8. tcp6       0      0 192.168.15.159:3306     192.168.15.160:60524    ESTABLISHED 4501/mysqld

网页访问测试：ip + 9140(端口号)

2.安装部署prometheus

新建一台虚拟机，在上面安装部署prometheus+grafana

上传安装包、解压并移动至/prometheus
  1. [root@prometheus_grafana ~]# ls
  2. anaconda-ks.cfg  prometheus-2.43.0.linux-amd64.tar.gz
  3. [root@prometheus_grafana ~]# tar -zvxf prometheus-2.43.0.linux-amd64.tar.gz 
  4. prometheus-2.43.0.linux-amd64/
  5. prometheus-2.43.0.linux-amd64/LICENSE
  6. prometheus-2.43.0.linux-amd64/consoles/
  7. prometheus-2.43.0.linux-amd64/consoles/prometheus.html
  8. prometheus-2.43.0.linux-amd64/consoles/node-disk.html
  9. prometheus-2.43.0.linux-amd64/consoles/node-overview.html
  10. prometheus-2.43.0.linux-amd64/consoles/prometheus-overview.html
  11. prometheus-2.43.0.linux-amd64/consoles/index.html.example
  12. prometheus-2.43.0.linux-amd64/consoles/node-cpu.html
  13. prometheus-2.43.0.linux-amd64/consoles/node.html
  14. prometheus-2.43.0.linux-amd64/prometheus
  15. prometheus-2.43.0.linux-amd64/promtool
  16. prometheus-2.43.0.linux-amd64/NOTICE
  17. prometheus-2.43.0.linux-amd64/console_libraries/
  18. prometheus-2.43.0.linux-amd64/console_libraries/prom.lib
  19. prometheus-2.43.0.linux-amd64/console_libraries/menu.lib
  20. prometheus-2.43.0.linux-amd64/prometheus.yml
  21. [root@prometheus_grafana ~]# mv prometheus-2.43.0.linux-amd64 /prometheus
  22. [root@prometheus_grafana ~]# cd /prometheus/
  23. [root@prometheus_grafana prometheus]# ls
  24. console_libraries  consoles  LICENSE  NOTICE  prometheus  prometheus.yml  promtool
将prometheus配置成service服务
  1. [root@prometheus_grafana prometheus]# vim /usr/lib/systemd/system/prometheus.service
  2. [root@prometheus_grafana prometheus]# cat /usr/lib/systemd/system/prometheus.service 
  3. [Unit]
  4. Description=prometheus
  5.  
  6. [Service]
  7. ExecStart=/prometheus/prometheus --config.file=/prometheus/prometheus.yml
  8. ExecReload=/bin/kill -HUP $MAINPID
  9. KillMode=process
  10. Restart=on-failure
  11.  
  12. [Install]
  13. WantedBy=multi-user.target
  14. [root@prometheus_grafana prometheus]# systemctl daemon-reload     #重新加载systemd相关服务
  15. [root@prometheus_grafana prometheus]# service prometheus restart
  16. Redirecting to /bin/systemctl restart prometheus.service
  17. [root@prometheus_grafana prometheus]# ps aux|grep prometheus     #检查prometheus进程是否存在
  18. root      22447  1.0  2.0 798956 38004 ?        Ssl  11:50   0:00 /prometheus/prometheus --config.file=/prometheus/prometheus.yml
  19. root      22456  0.0  0.0 112824   992 pts/1    S+   11:50   0:00 grep --color=auto prometheus
  20. [root@prometheus_grafana prometheus]# netstat -anplut|grep prometheus     #查看端口
  21. tcp6       0      0 :::9090                 :::*                    LISTEN      22447/prometheus    
  22. tcp6       0      0 ::1:58730               ::1:9090                ESTABLISHED 22447/prometheus    
  23. tcp6       0      0 ::1:9090                ::1:58730               ESTABLISHED 22447/prometheus
访问prometheus服务页面

访问ip+端口号(9090)，可以看到如下页面

添加prometheus节点node，刷新服务
  1. [root@prometheus_grafana ~]# cd /prometheus/
  2. [root@prometheus_grafana prometheus]# ls
  3. console_libraries  consoles  LICENSE  NOTICE  prometheus  prometheus.yml  promtool
  4. [root@prometheus_grafana prometheus]# vim prometheus.yml
  5. [root@prometheus_grafana prometheus]# cat prometheus.yml
  6. # my global config
  7. global:
  8.   scrape_interval: 15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  9.   evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  10.   # scrape_timeout is set to the global default (10s).
  11.  
  12. # Alertmanager configuration
  13. alerting:
  14.   alertmanagers:
  15.     - static_configs:
  16.         - targets:
  17.           # - alertmanager:9093
  18.  
  19. # Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
  20. rule_files:
  21.   # - "first_rules.yml"
  22.   # - "second_rules.yml"
  23.  
  24. # A scrape configuration containing exactly one endpoint to scrape:
  25. # Here it's Prometheus itself.
  26. scrape_configs:
  27.   # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  28.   - job_name: "prometheus"
  29.  
  30.     # metrics_path defaults to '/metrics'
  31.     # scheme defaults to 'http'.
  32.  
  33.     static_configs:
  34.       - targets: ["localhost:9090"]
  35.  
  36.  - job_name: "master"
  37.    static_configs:
  38.       - targets: ["192.168.15.159:9104"]
  39.  - job_name: "slave1"
  40.    static_configs:
  41.       - targets: ["192.168.15.157:9104"]
  42.  - job_name: "slave2"
  43.    static_configs:
  44.       - targets: ["192.168.15.160:9104"]
  45.  - job_name: "backup"
  46.    static_configs:
  47.       - targets: ["192.168.15.161:9104"]
  48. [root@prometheus_grafana prometheus]# service prometheus restart
  49. Redirecting to /bin/systemctl restart prometheus.service
访问prometheus服务页面查看节点是否添加成功

3.安装grafana

上传grafana的安装包，并安装
  1. [root@prometheus_grafana ~]# ls
  2. anaconda-ks.cfg    prometheus-2.43.0.linux-amd64.tar.gz
  3. grafana-enterprise-9.4.7-1.x86_64.rpm
  4. [root@prometheus_grafana ~]# yum install grafana-enterprise-9.4.7-1.x86_64.rpm -y
启动grafana
  1. [root@prometheus_grafana ~]# service grafana-server start
  2. Starting grafana-server (via systemctl):                   [  确定  ]
  3. [root@prometheus_grafana ~]# netstat -anplut|grep grafana     #查看端口
  4. tcp        0      0 192.168.15.178:49718    34.120.177.193:443      ESTABLISHED 23204/grafana       
  5. tcp6       0      0 :::3000                 :::*                    LISTEN      23204/grafana
访问grafana

页面访问的初始账号和密码均为admin

在grafana中添加prometheus数据源

添加Dashboards模板（使用14057--出图效果好）

grafana效果展示

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/码创造者/article/detail/989252
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号