数据库项目最终要实现的功能 
通过多种集群方案 解决网站存储数据的如下问题：
		数据库服务的可靠性
        服务的负载均衡
		数据库的自动备份
		数据的永久存储
		网站的访问加速
        
		
在MySQL11主机创建逻辑卷 lv0	 并格式化 （文件系统类型统一使用xfs）
	
[root@mysql11 ~]# which  pvcreate || yum  -y install lvm2
/usr/sbin/pvcreate
[root@mysql11 ~]# lsblk 
NAME   MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
vda    253:0    0  30G  0 disk 
\u2514\u2500vda1 253:1    0  30G  0 part /
vdb    253:16   0   3G  0 disk 
vdc    253:32   0   3G  0 disk 
[root@mysql11 ~]# pvcreate /dev/vdb /dev/vdc
  Physical volume "/dev/vdb" successfully created.
  Physical volume "/dev/vdc" successfully created.
[root@mysql11 ~]# 
[root@mysql11 ~]# 

[root@mysql11 ~]# pvscan 
  PV /dev/vdb                      lvm2 [3.00 GiB]
  PV /dev/vdc                      lvm2 [3.00 GiB]
  Total: 2 [6.00 GiB] / in use: 0 [0   ] / in no VG: 2 [6.00 GiB]
[root@mysql11 ~]# 
[root@mysql11 ~]# vgcreate vg0 /dev/vdb  /dev/vdc
  Volume group "vg0" successfully created
[root@mysql11 ~]# 
[root@mysql11 ~]# vgscan 
  Reading volume groups from cache.
  Found volume group "vg0" using metadata type lvm2
[root@mysql11 ~]# 
[root@mysql11 ~]# vgdisplay  vg0 | grep -i  "vg size"
  VG Size               5.99 GiB
[root@mysql11 ~]# 
[root@mysql11 ~]# lvcreate  -L 5.99G -n lv0  vg0
  Rounding up size to full physical extent 5.99 GiB
  Logical volume "lv0" created.
[root@mysql11 ~]# 
[root@mysql11 ~]# lvscan 
  ACTIVE            '/dev/vg0/lv0' [5.99 GiB] inherit
[root@mysql11 ~]# 

[root@mysql11 ~]# mkfs.xfs /dev/vg0/lv0
meta-data=/dev/vg0/lv0           isize=512    agcount=4, agsize=392704 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=1570816, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
[root@mysql11 ~]# 
[root@mysql11 ~]# blkid /dev/vg0/lv0
/dev/vg0/lv0: UUID="3e7a11f3-5630-45bd-97d0-32ed55d2d71c" TYPE="xfs" 
[root@mysql11 ~]#
					
					 练习时间到 10:00 			 
在MySQL22主机创建逻辑卷 lv0	 并格式化 （文件系统类型统一使用xfs）
[root@mysql22 ~]# yum  -y install lvm2 
[root@mysql22 ~]# lsblk  查看物理磁盘名
[root@mysql22 ~]# pvcreate  /dev/vdb /dev/vdc  创建物理卷
[root@mysql22 ~]# vgcreate  vg0 /dev/vdb /dev/vdc  创建卷组
[root@mysql22 ~]# vgdisplay  vg0 | grep -i  "vg size"  查看卷组大小
[root@mysql22 ~]# lvcreate  -n lv0 -L 5.99G vg0  创建逻辑卷
[root@mysql22 ~]# lvscan  查看逻辑卷设备名
[root@mysql22 ~]# mkfs.xfs /dev/vg0/lv0  格式化lv0
[root@mysql22 ~]# blkid /dev/vg0/lv0  查看格式化信息


总结：分别在做数据服务器的mysql11 和 MySQL22 主机分别添加了 2块 3G 盘 
	  然后分别在2台 主机 MySQL11 和 MySQL22  把添加的2块做成了逻辑卷设备名称叫/dev/vg0/lv0
	  并分别在2台 主机 MySQL11 和 MySQL22把 逻辑卷设备/dev/vg0/lv0格式化 文件系统类型xfs
	   
分别在 mysql11 和 MySQL22 主机安装MySQL服务软件 mysql-5.7.17
[root@mysql11 ~]# tar -xf mysql-5.7.17.tar
[root@mysql11 ~]# yum -y install  mysql-community-*.rpm

[root@mysql22 ~]# tar -xf mysql-5.7.17.tar
[root@mysql22 ~]# yum -y install  mysql-community-*.rpm


分别在 mysql11 和 MySQL22 主机 把本机创建的逻辑卷/dev/vg0/lv0 挂载到数据库目录下/var/lib/mysql  
		        ！！！注意 ！！！如果 mysql11 和 MySQL22 主机启动了MySQL服务 需要做如下操作
				]# systemctl  stop mysqld
				]# rm -rf /var/lib/mysql/*

				设置开机挂载/dev/vg0/lv0
[root@mysql11 ~]# vim  /etc/fstab 
/dev/vg0/lv0	/var/lib/mysql	xfs	defaults	0 0
:wq
[root@mysql11 ~]# mount -a
[root@mysql11 ~]# mount | grep "/var/lib/mysql"
/dev/mapper/vg0-lv0 on /var/lib/mysql type xfs (rw,relatime,attr2,inode64,noquota)
[root@mysql11 ~]# 


[root@mysql22 ~]# vim  /etc/fstab 
/dev/vg0/lv0	/var/lib/mysql	xfs	defaults	0 0
:wq
[root@mysql22 ~]# mount -a
[root@mysql22 ~]# mount | grep "/var/lib/mysql"
																	

分别在 mysql11 和 MySQL22 主机 启动mysqld服务，使用初始密码登录并把数据库管理员root的密码修改为123qqq...A  
并使用修改后的密码123qqq...A登录

[root@mysql11 ~]# grep -i "password" /var/log/mysqld.log  | tail -1
2021-11-27T02:16:33.987486Z 1 [Note] A temporary password is generated for root@localhost: q9=wvl.Th1b9
[root@mysql11 ~]# 
[root@mysql11 ~]# mysqladmin -uroot -p'q9=wvl.Th1b9' password "123qqq...A"
[root@mysql11 ~]# mysql -uroot -p123qqq...A
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

[root@mysql22 ~]# grep -i  "password" /var/log/mysqld.log | tail -1 
2021-11-27T02:16:50.255701Z 1 [Note] A temporary password is generated for root@localhost: +pK/aeyuM6yq

[root@mysql22 ~]# mysqladmin  -uroot -p'+pK/aeyuM6yq' password '123qqq...A'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

[root@mysql22 ~]# mysql -uroot -p123qqq...A
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 


		总结：到现在为止 把2台MySQL数据库服务器 准备好了 ，
				数据库管员root 本机登录密码都是123qqq...A
				


~~~~~~~把2台数据库服务器配置为MySQL一主一从同步结构
		配置master 服务器 mysql11
				1)启用binlog日志
				2） 用户授权
				3） 查看日志信息
[root@mysql11 ~] vim /etc/my.cnf
[mysqld]
server_id=11
log_bin=master11                                                        休息到11:00 
:wq
[root@mysql11 ~]# systemctl  restart mysqld		
[root@mysql11 ~]# mysql  -uroot -p123qqq...A		
mysql> grant replication slave on  *.*  to repluser@"%" identified by "123qqq...A";
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master11.000001 |      441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
mysql>	
		配置master 服务器 mysql22
			    1） 指定server_id 并重启mysqld服务
				2） 指定主服务器信息
				3） 启动slave进程
				4） 查看状态 
[root@mysql22~]# vim  /etc/my.cnf
[mysqld]
server_id=22
:wq
[root@mysql22~]# systemctl  restart mysqld
[root@mysql22~]# mysql  -uroot -p123qqq...A
mysql> change master to master_host="192.168.4.11",master_user="repluser",
        master_password="123qqq...A",master_log_file="master11.000001",master_log_pos=441;
mysql> start slave;
mysql> exit
[root@mysql22 ~]# mysql -uroot -p123qqq...A -e 'show slave status \G' | grep  -i "yes"
             Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

总结：刚刚完成了 mysql 一主一从的配置 查看的结果 在MySQL22 如下
[root@mysql22 ~]# mysql -uroot -p123qqq...A -e 'show slave status \G' | grep  -i "master_host"
mysql: [Warning] Using a password on the command line interface can be insecure.
                  Master_Host: 192.168.4.11
[root@mysql22 ~]#
[root@mysql22 ~]# mysql -uroot -p123qqq...A -e 'show slave status \G' | grep  -i "yes"
mysql: [Warning] Using a password on the command line interface can be insecure.
             Slave_IO_Running: Yes
             Slave_SQL_Running: Yes


~~~~~配置读写分离服务器 maxscale77
			功能：maxscale77 主机接收网站服务web33  访问数据库服务的连接请求
				  把查询请求分发给 主从结构中的slave服务器 mysql22 
                  把存储数据的请求  分发给 主从结构中的master服务器 mysql11
				  
			在maxscale77主机做如下配置：
				  1) 安装软件
				  2） 修改配置文件
				  3） 配置数据库服务器
				  4） 启动读写分离服务
				  5） 查看服务状态
				  6） 访问本机管理服务查看监视信息
]# yum -y install maxscale-2.1.2-1.rhel.7.x86_64.rpm 				  
[root@maxscale77 ~]# vim /etc/maxscale.cnf

[maxscale]
threads=auto


[server1]
type=server
address=192.168.4.11
port=3306
protocol=MySQLBackend


[server2]
type=server
address=192.168.4.22
port=3306
protocol=MySQLBackend


[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2
user=mysqla
passwd=123qqq...A
monitor_interval=10000

[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2
user=mysqlb
passwd=123qqq...A
max_slave_connections=100%


[MaxAdmin Service]
type=service
router=cli



[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=4016
:wq

[root@mysql11 ~]# mysql -uroot -p123qqq...A -e 'grant replication client , replication slave on *.* to mysqla@"%" identified by "123qqq...A"'

[root@mysql11 ~]# mysql -uroot -p123qqq...A -e 'grant select on mysql.* to mysqlb@"%" identified by "123qqq...A"'
	
[root@mysql22 ~]# mysql -uroot -p123qqq...A -e 'select user from mysql.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| user      |
+-----------+
| mysqla    |
| mysqlb    |
| mysql.sys |
| root      |
+-----------+
[root@mysql22 ~]# 

[root@maxscale77 ~]# maxscale /etc/maxscale.cnf

[root@maxscale77 ~]# netstat  -utnalp  | grep  4006
tcp6       0      0 :::4006                 :::*                    LISTEN      1182/maxscale       
[root@maxscale77 ~]# netstat  -utnalp  | grep  4016
tcp6       0      0 :::4016                 :::*                    LISTEN      1182/maxscale       
[root@maxscale77 ~]# 
[root@maxscale77 ~]# maxadmin -uadmin -pmariadb -P4016
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.4.11    |  3306 |           0 | Master, Running
server2            | 192.168.4.22    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> exit
[root@maxscale77 ~]#

总结：到此处就把存储数据网站数据的读写分离结构配好了

~~~~~~~配置NFS30主机
		功能：存储网站集群所有主机的网页文件
		把本机的物理磁盘/dev/vdb 做成逻辑卷设备lv0  然后配置nfs服务共享逻辑卷设备lv0
  296  yum -y install rpcbind nfs-utils
  303  yum -y install lvm2
  305  pvcreate  /dev/vdb
  306  vgcreate  vg0 /dev/vdb
  311  vgdisplay vg0  | grep -i "Total PE"
  312  lvcreate  -n lv0 -l 767 vg0
  313  mkfs.xfs /dev/vg0/lv0
  314  blkid  /dev/vda1 vg0/lv0
  315  mkdir /sitedir
  316  chmod o+w /sitedir/
 
  318  vim /etc/fstab 
       /dev/vg0/lv0   /sitedir  xfs   defaults  0 0
       :wq   
  319  mount -a
  322  vim /etc/exports
       /sitedir   *(rw)
	   :wq
  323  systemctl  restart rpcbind
  324  systemctl  restart nfs
[root@nfs30 ~]# showmount  -e localhost
Export list for localhost:
/sitedir *
[root@nfs30 ~]# 

~~~~~~~~~~~~~配置网站服务 
			诉求：在ip地址192.168.4.33 的主机运行 tomcat 服务
			具体操作如下：
					1）安装软件
					2) 挂载nfs30主机共享目录
					3）启动tomcat服务
					4）查看服务状态
					
[root@web33 ~]# 				
yum -y install  java-1.8.0-openjdk.x86_64

tar -xf apache-tomcat-8.0.30.tar.gz 

mv apache-tomcat-8.0.30 /usr/local/tomcat

yum -y install  mysql-connector-java

cp /usr/share/java/mysql-connector-java.jar /usr/local/tomcat/lib/

rm -rf  /usr/local/tomcat/webapps/ROOT/*

which showmount || yum -y install  nfs-utils

showmount  -e 192.168.4.30

vim /etc/fstab 
192.168.4.30:/sitedir   /usr/local/tomcat/webapps/ROOT  nfs  defaults  0 0
:wq
	   
mount -a

mount | grep  "192.168.4.30"

df -h /usr/local/tomcat/webapps/ROOT/
					
				
[root@web33 ~]# /usr/local/tomcat/bin/startup.sh 
Using CATALINA_BASE:   /usr/local/tomcat
Using CATALINA_HOME:   /usr/local/tomcat
Using CATALINA_TMPDIR: /usr/local/tomcat/temp
Using JRE_HOME:        /usr
Using CLASSPATH:       /usr/local/tomcat/bin/bootstrap.jar:/usr/local/tomcat/bin/tomcat-juli.jar
Tomcat started.
[root@web33 ~]# 
[root@web33 ~]# netstat -utnalp | grep :8080
tcp6       0      0 :::8080                 :::*                    LISTEN      1489/java           
[root@web33 ~]# 


总结 ： 此处把网站服务器配置好了

~~~~~测试配置
		1） 在nfs30主机的共享目录下编写网页文件
[root@nfs30 ~]# echo "web page" > /sitedir/test.html
[root@nfs30 ~]# ls /sitedir/
test.html
[root@nfs30 ~]# 
		
        2） 在web33 查看本机tomcat 服务的网页目录 并访问本机的tomcat网站服务
[root@web33 ~]# ls /usr/local/tomcat/webapps/ROOT/
test.html
[root@web33 ~]# 
		
[root@web33 ~]# curl  http://localhost:8080/test.html
web page
[root@web33 ~]# 

3） 在mysql11 主服务器添加用户 （客户端访问数据库服务使用的用户）
			具体操作如下：
[root@mysql11 ~]# mysql -uroot -p123qqq...A
create database  gamedb;
create  table  gamedb.user(name char(10));
grant  select,insert on gamedb.* to yaya99@"%" identified by "123qqq...A";
			
        4) 在mysql22 从数据库服务器 查看同步数据
[root@mysql22 ~]# mysql -uroot -p123qqq...A -e 'desc gamedb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
[root@mysql22 ~]# 
[root@mysql22 ~]# mysql -uroot -p123qqq...A -e 'show grants for yaya99@"%"'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------------------------+
| Grants for yaya99@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'yaya99'@'%'                 |
| GRANT SELECT, INSERT ON `gamedb`.* TO 'yaya99'@'%' |
+----------------------------------------------------+
[root@mysql22 ~]#

		5）在web33 主机的命令行下连接读写分离服务器maxscale77 存储数据和查询数据
			#安装提供mysql命令的软件包
[root@web33 ~]# which  mysql || yum -y install mariadb			
[root@web33 ~]# mysql -h192.168.4.77 -P4006 -uyaya99 -p123qqq...A
mysql> insert into gamedb.user values("xdd");
mysql> select * from gamedb.user;
+------+
| name |
+------+
| xdd  |
+------+
1 row in set (0.00 sec)

mysql> 
		6）在nfs30主机的共享目录/sitedir 里编写java脚本 连接数据库服务 存储数据
			说明：在工作中java脚本是程序员负责编写
	
[root@nfs30 ~]# vim  /sitedir/linkdb.jsp
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<%@ page import="java.naming.*" %>
<%@ page import="java.sql.*" %>

<%
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://192.168.4.77:4006","yaya99","123qqq...A");
Statement state=con.createStatement();
String sql="insert into gamedb.user values('pmm')";
state.executeUpdate(sql);
%>
data save ok 
[root@nfs30 ~]# 

	  7) 在网站服务器本机访问连接数据库服务的java脚本
[root@web33 ~]# ls /usr/local/tomcat/webapps/ROOT/
linkdb.jsp  test.html
[root@web33 ~]# 
[root@web33 ~]# curl  http://localhost:8080/linkdb.jsp

data save ok 
[root@web33 ~]# 	  
	  
	  
	  
	  8）在数据库服务器本机查看表里的记录（有脚本中添加的记录pmm）
[root@mysql11 ~]# mysql -uroot -p123qqq...A -e 'select * from gamedb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| name |
+------+
| xdd  |
| pmm  |
+------+
[root@mysql11 ~]#	  
[root@mysql22 ~]# mysql -uroot -p123qqq...A -e 'select * from gamedb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| name |
+------+
| xdd  |
| pmm  |
+------+
[root@mysql22 ~]#





















~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[root@host51 ~]# redis-cli  -h 192.168.4.51 -p 6379  shutdown
 
[root@host51 ~]# vim /etc/redis/6379.conf 
70：bind 192.168.4.51
815：cluster-enabled yes
823:cluster-config-file nodes-6379.conf
829:cluster-node-timeout 5000
:wq 

[root@host51 ~]# /etc/init.d/redis_6379 start
Starting Redis server...
[root@host51 ~]# netstat -utnalp  | grep redis-server
tcp        0      0 192.168.4.51:6379       0.0.0.0:*               LISTEN      10580/redis-server  
tcp        0      0 192.168.4.51:16379      0.0.0.0:*               LISTEN      10580/redis-server  
[root@host51 ~]#