mysql项目

升级web服务器

1.清除原有配置

将Tomcat服务停止，删除配置文件，web33主机上

[root@web33 ~]# /usr/local/tomcat/bin/shutdown.sh   #停止Tomcat服务
[root@web33 ~]# vim /etc/rc.d/rc.local 
# /usr/local/tomcat/bin/startup.sh   # 注释此行
​
[root@web33 ~]# umount /usr/local/tomcat/webapps/ROOT/
[root@web33 ~]# vim /etc/fstab 
# 192.168.1.30:/sitedir /usr/local/tomcat/webapps/ROOT/ nfs  defaults        0 0     # 注释

2.部署LNMP环境

安装依赖

yum install -y gcc pcre-devel zlib-devel

将Nginx包拷贝到虚拟机

tar -xf nginx-1.12.2.tar.gz #解压 cd nginx-1.12.2/

./configure #编译安装 make && make install

yum install -y php-fpm

systemctl enable php-fpm systemctl start php-fpm vim +65 /usr/local/nginx/conf/nginx.conf

location ~ .php$ { root html; fastcgi_pass 127.0.0.1:9000; fastcgi_index index.php;

#fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;

include fastcgi_params; }

/usr/local/nginx/sbin/nginx -t /usr/local/nginx/sbin/nginx [root@web33 ~]# yum install -y php-mysql

[root@web33 ~]# systemctl restart php-fpm [root@web33 ~]# ss -tlnp | grep :80 LISTEN 0 128 *:80 : users:(("nginx",pid=4943,fd=6),("nginx",pid=4942,fd=6)) [root@web33 ~]# kill 4942 [root@web33 ~]# ss -tlnp | grep :80 [root@web33 ~]# vim /etc/fstab

192.168.4.30:/sitedir   /usr/local/nginx/html   nfs     defaults        0 0

[root@web33 ~]# mount -a #特别注意NFS需要提前配置

[root@web33 ~]#

附NFS配置过程

1，准备磁盘 创建一个GRT分区的磁盘结构

[root@nfs30 ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 20G 0 disk ├─sda1 8:1 0 1G 0 part /boot

└─sda2 8:2 0 19G 0 part

 ├─centos-root 253:0 0 17G 0 lvm /

 └─centos-swap 253:1 0 2G 0 lvm [SWAP] sdb 8:16 0 20G 0 disk sr0 11:0 1 8.8G 0 rom /mydvd [root@nfs30 ~]# fdisk /dev/sdb 欢迎使用 fdisk (util-linux 2.23.2)。

更改将停留在内存中，直到您决定将更改写入磁盘。 使用写入命令前请三思。

Device does not contain a recognized partition table 使用磁盘标识符 0x1714f4d5 创建新的 DOS 磁盘标签。

命令(输入 m 获取帮助)：g Building a new GPT disklabel (GUID: 83AA6972-76DE-49DF-8EA8-51F064CB18A9)

命令(输入 m 获取帮助)：n 分区号 (1-128，默认 1)： #回车 第一个扇区 (2048-41943006，默认 2048)： #回车 Last sector, +sectors or +size{K,M,G,T,P} (2048-41943006，默认 41943006)： #回车 已创建分区 1

命令(输入 m 获取帮助)：p

磁盘 /dev/sdb：21.5 GB, 21474836480 字节，41943040 个扇区 Units = 扇区 of 1 * 512 = 512 bytes 扇区大小(逻辑/物理)：512 字节 / 512 字节 I/O 大小(最小/最佳)：512 字节 / 512 字节 磁盘标签类型：gpt Disk identifier: 83AA6972-76DE-49DF-8EA8-51F064CB18A9

Start End Size Type Name

1 2048 41943006 20G Linux filesyste

命令(输入 m 获取帮助)：w The partition table has been altered!

Calling ioctl() to re-read partition table. 正在同步磁盘。

格式化刚刚分区的磁盘

[root@nfs30 ~]# mkfs.xfs /dev/sdb1 meta-data=/dev/sdb1 isize=512 agcount=4, agsize=1310655 blks = sectsz=512 attr=2, projid32bit=1 = crc=1 finobt=0, sparse=0 data = bsize=4096 blocks=5242619, 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@nfs30 ~]# vim /etc/fstab [root@nfs30 ~]# mkdir /sitedir [root@nfs30 ~]# mount -a [root@nfs30 ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 20G 0 disk ├─sda1 8:1 0 1G 0 part /boot

└─sda2 8:2 0 19G 0 part

├─centos-root 253:0 0 17G 0 lvm /

└─centos-swap 253:1 0 2G 0 lvm [SWAP] sdb 8:16 0 20G 0 disk

└─sdb1 8:17 0 20G 0 part /sitedir sr0 11:0 1 8.8G 0 rom /mydvd

配置NFS服务

[root@nfs30 ~]# yum install -y nfs-utils rpcbind #nfs依赖RPC服务，所以需要先启动rpcbind [root@nfs30 ~]# systemctl enable rpcbind [root@nfs30 ~]# systemctl start rpcbind [root@nfs30 ~]# vim /etc/exports

/sitedir        *(rw)

[root@nfs30 ~]# systemctl start nfs [root@nfs30 ~]# showmount -e Export list for nfs30: /sitedir *

会看到挂载成功

[root@web33 ~]# /usr/local/nginx/sbin/nginx        #启动Nginx服务

配置Redis

将Redis包拷贝到Redis1的家目录下

yum install -y gcc tar -xf redis-4.0.8.tar.gz cd redis-4.0.8 vim +27 src/Makefile

PREFIX?=/usr/local/redis

make && make install ssh-keygen #生成密钥对 for i in {51..56}; do ssh-copy-id 192.168.4.$i; done #将密钥拷贝到其他虚拟机 for i in {52..56}; do scp -r /usr/local/redis/ 192.168.4.$i:/usr/local/; done #将Redis目录拷贝到其他虚拟机 for i in {51..56};do ssh 192.168.4.$i "echo 'export PATH=$PATH:/usr/local/redis/bin' >> /etc/bashrc";done #添加环境变量 source /etc/bashrc #让文件生效 for i in {52..56};do scp -r ~/redis-4.0.8 192.168.4.$i:/root/;done #将Redis包拷贝到其他虚拟机

自动回答初始化问题

for i in {51..56};do ssh 192.168.4.$i yum install -y expect;done #安装expect包 vim answer.sh #编写脚本

#!/bin/bash
​
expect <<EOF
    spawn /root/redis-4.0.8/utils/install_server.sh
    expect "6379]"
    send "\r"
    expect "/etc/redis/6379.conf]"
    send "\r"
    expect "/var/log/redis_6379.log]"
    send "\r"
    expect "/var/lib/redis/6379]"
    send "\r"
    expect "/usr/local/redis/bin/redis-server]"
    send "\r"
    expect "Ctrl-C to abort."
    send "\r"
expect eof
EOF

chmod +x answer.sh #添加X执行权限 for i in {52..56};do scp answer.sh 192.168.4.$i:/root/;done #拷贝给其他主机 for i in {51..56};do ssh 192.168.4.$i"bash /root/answer.sh";done #执行脚本 for i in {51..56};do ssh 192.168.4.$i service redis_6379 stop;done #停止服务 for i in {51..56};do ssh 192.168.4.$i rm -rf /var/lib/redis/6379/*;done #删除Redis目录下所有 vim /etc/redis/6379.conf

 70 # bind 127.0.0.1
 89 protected-mode no
 815 cluster-enabled yes
 823 cluster-config-file nodes-6379.conf
 829 cluster-node-timeout 5000

for i in {53..56};do scp /etc/redis/6379.conf 192.168.4.$i:/etc/redis/;done #将配置文件拷贝到其他主机 for i in {51..56};do ssh 192.168.4.$i service redis_6379 start;done #

搭建Redis集群

将redis-3.2.1.gem拷贝到4.57家目录下

[root@manager1 ~]# yum -y install rubygems Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile nsd | 3.6 kB 00:00:00 Package rubygems-2.0.14.1-33.el7_4.noarch already installed and latest version Nothing to do [root@manager1 ~]# gem install redis-3.2.1.gem Successfully installed redis-3.2.1 Parsing documentation for redis-3.2.1 1 gem installed

[root@redis1 ~]# scp redis-4.0.8/src/redis-trib.rb 192.168.4.57:/usr/local/bin/ #将文件拷贝到4.57上 The authenticity of host '192.168.4.57 (192.168.4.57)' can't be established. ECDSA key fingerprint is SHA256:s9sLxoXnikQEaF2aRfHzTJDrtAi0663iKS8D9n/tAZo. ECDSA key fingerprint is MD5:e4:57:eb:be:d1:08:83:91:05:43:89:2b:df:16:10:ad. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.4.57' (ECDSA) to the list of known hosts. root@192.168.4.57's password: redis-trib.rb 100% 64KB 1.4MB/s 00:00

[root@manager1 ~]# redis-trib.rb create --replicas 1 192.168.4.{51..56}:6379

>>>Creating cluster

[root@manager1 ~]#redis-trib.rb info 192.168.4.51:6379

配置web服务器支持Redis

 **在web服务器上操作**

   yum install -y php-devel automake autoconf             #安装依赖

​    将redis-cluster-4.3.0.tgz拷贝到web33主机上

​     ls                   #查看

​             redis-cluster-4.3.0.tgz 

  tar -xf redis-cluster-4.3.0.tgz           #解压
  cd redis-4.3.0/
  phpize
   ./configure --with-php-config=/usr/bin/php-config            #编译安装
  make && make install
  vim +730 /etc/php.ini         #在730行下面添加以下两行

```
extension_dir = "/usr/lib64/php/modules"
extension = "redis.so"
```

  systemctl restart php-fpm            #重启服务

## 测试

[root@nfs30 ~]# vim /sitedir/set_data.php
<?php
$redis_list = ['192.168.4.51:6379','192.168.4.52:6379','192.168.4.53:6379','192.168.4.54:6379','192.168.4.55:6379','192.168.4.56:6379'];
$client = new RedisCluster(NUll,$redis_list);
$client->set("i","tarenaA ");
$client->set("j","tarenaB ");
$client->set("k","tarenaC ");
?>
[root@nfs30 ~]# vim /sitedir/get_data.php
<?php
$redis_list = ['192.168.4.51:6379','192.168.4.52:6379','192.168.4.53:6379','192.168.4.54:6379','192.168.4.55:6379','192.168.4.56:6379'];
$client = new RedisCluster(NUll,$redis_list);
echo $client->get("i");
echo $client->get("j");
echo $client->get("k");
?>
访问http://192.168.4.33/set_data.php和http://192.168.4.33/get_data.php
[root@redis1 ~]# redis-cli -h 192.168.1.51 -c
192.168.4.51:6379> keys *
1) "j"
192.168.4.51:6379> get i

PXC集群 数据迁移

主从同步

[root@pxcnode66 ~]# yum install -y mysql-community*
[root@pxcnode66 ~]# systemctl start mysqld
[root@pxcnode66 ~]# grep password /var/log/mysqld.log 
[root@pxcnode66 ~]# mysqladmin -uroot -p'=6hU>ykqKjic' password NSD2021@tedu.cn

[root@pxcnode66 ~]# vim /etc/my.cnf
[mysqld]
server-id = 66
[root@pxcnode66 ~]# systemctl restart mysqld

# 主服务器完全备份
[root@mysql11 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7
[root@mysql11 ~]# innobackupex --user root --password NSD2021@tedu.cn --slave-info /root/allbak --no-timestamp
[root@mysql11 ~]# scp -r /root/allbak/ 192.168.1.66:/root

# 从服务器恢复
[root@pxcnode66 ~]# yum -y install percona-xtrabackup-24-2.4.13-1.el7
[root@pxcnode66 ~]# systemctl stop mysqld
[root@pxcnode66 ~]# rm -rf /var/lib/mysql/*
[root@pxcnode66 ~]# innobackupex --apply-log /root/allbak/
[root@pxcnode66 ~]# innobackupex --copy-back /root/allbak/
[root@pxcnode66 ~]# chown -R mysql:mysql /var/lib/mysql
[root@pxcnode66 ~]# systemctl start mysqld

# 配置从服务器
[root@pxcnode66 ~]# grep master11 /root/allbak/xtrabackup_info 
binlog_pos = filename 'master11.000001', position '3070'
[root@pxcnode66 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to
    -> master_host="192.168.1.11",
    -> master_user="repluser",
    -> master_password="NSD2021@tedu.cn",
    -> master_log_file="master11.000001",
    -> master_log_pos=3070;
mysql> start slave;
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

pxcnode66获取数据后，更换为pxc软件

[root@pxcnode66 ~]# systemctl stop mysqld
[root@pxcnode66 ~]# yum remove -y mysql-community*
[root@pxcnode66 ~]# yum -y install Percona-XtraDB-Cluster-*
[root@pxcnode66 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf 
... ...
server-id=66
... ...

[root@pxcnode66 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 
  8 wsrep_cluster_address=gcomm://  # 集群地址未填，表示新建集群
  25 wsrep_node_address=192.168.1.66
  27 wsrep_cluster_name=pxc-cluster
  30 wsrep_node_name=pxcnode66
  39 wsrep_sst_auth="sstuser:NSD2021@tedu.cn"
[root@pxcnode66 ~]# systemctl start mysql
[root@pxcnode66 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> grant all on *.* to sstuser@"localhost" identified by 'NSD2021@tedu.cn';
mysql> show slave status\G
                  Master_Host: 192.168.1.11
                  Master_User: repluser
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

• 配置另外两台pxcnode

[root@pxcnode10 ~]# yum -y install Percona-XtraDB-Cluster-*
[root@pxcnode10 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf 
[mysqld]
server-id=10
[root@pxcnode10 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 
  8 wsrep_cluster_address=gcomm://192.168.1.66,192.168.1.10
 25 wsrep_node_address=192.168.1.10
 27 wsrep_cluster_name=pxc-cluster
 30 wsrep_node_name=pxcnode10
 39 wsrep_sst_auth="sstuser:NSD2021@tedu.cn"
[root@pxcnode10 ~]# systemctl enable mysql
[root@pxcnode10 ~]# systemctl start mysql


[root@pxcnode88 ~]# yum -y install Percona-XtraDB-Cluster-*
[root@pxcnode88 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf 
[mysqld]
server-id=88
[root@pxcnode88 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 
  8 wsrep_cluster_address=gcomm://192.168.1.66,192.168.1.10,192.168.1.88
 25 wsrep_node_address=192.168.1.88
 27 wsrep_cluster_name=pxc-cluster
 30 wsrep_node_name=pxcnode88
 39 wsrep_sst_auth="sstuser:NSD2021@tedu.cn"
[root@pxcnode88 ~]# systemctl enable mysql
[root@pxcnode88 ~]# systemctl start mysql

# 将pxcnode{66,10}的配置文件加入所有节点
[root@pxcnode{10,66} ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 
  8 wsrep_cluster_address=gcomm://192.168.1.66,192.168.1.10,192.168.1.88

为pxc集群配置负载均衡

[root@haprox99 ~]# yum install -y haproxy
[root@haprox99 ~]# vim /etc/haproxy/haproxy.cfg 
# 删除63行到结尾的内容，然后添加以下内容：
 63 listen status
 64     mode http
 65     bind *:80
 66     stats enable
 67     stats uri /admin
 68     stats auth admin:admin
 69     
 70 listen mysql_3306 *:3306
 71     mode tcp
 72     option tcpka
 73     balance roundrobin
 74     server mysql_01 192.168.1.66:3306 check
 75     server mysql_02 192.168.1.10:3306 check
 76     server mysql_03 192.168.1.88:3306 check
[root@haprox99 ~]# systemctl start haproxy.service
[root@haprox99 ~]# systemctl enable haproxy.service
# 访问http://192.168.1.99/admin，查看监控信息

[root@haprox98 ~]# yum install -y haproxy
[root@haprox99 ~]# scp /etc/haproxy/haproxy.cfg 192.168.1.98:/etc/haproxy/
[root@haprox98 ~]# systemctl start haproxy.service
[root@haprox98 ~]# systemctl enable haproxy.service
# 访问http://192.168.1.98/admin，查看监控信息

配置高可用

[root@haprox99 ~]# yum install -y keepalived
[root@haprox99 ~]# vim /etc/keepalived/keepalived.conf 
# 在14行下面添加
 15    vrrp_iptables
... ...
 20 vrrp_instance VI_1 {
 21     state MASTER
 22     interface eth0
 23     virtual_router_id 51
 24     priority 150
 25     advert_int 1
 26     authentication {
 27         auth_type PASS
 28         auth_pass 1111
 29     }
 30     virtual_ipaddress {
 31         192.168.1.100
 32     }
 33 }

[root@haprox98 ~]# yum install -y keepalived
[root@haprox98 ~]# vim /etc/keepalived/keepalived.conf 
# 在14行下面添加
 15    vrrp_iptables
... ...
 20 vrrp_instance VI_1 {
 21     state BACKUP
 22     interface eth0
 23     virtual_router_id 51
 24     priority 100
 25     advert_int 1
 26     authentication {
 27         auth_type PASS
 28         auth_pass 1111
 29     }
 30     virtual_ipaddress {
 31         192.168.1.100
 32     }
 33 }
[root@haprox99 ~]# systemctl enable keepalived.service 
[root@haprox99 ~]# systemctl start keepalived.service 
[root@haprox98 ~]# systemctl start keepalived.service 
[root@haprox98 ~]# systemctl enable keepalived.service 
[root@haprox99 ~]# ip a s eth0   # 查看vip地址
[root@haprox99 ~]# shutdown 
[root@haprox98 ~]# ip a s eth0   # 过一段时间可以看到vip切换过来