MySQL 主从复制与 Nginx 升级实践记录

项目三企业数据库用户权限管理企业背景公司数据库管理员需要给不同岗位分配权限。需求1创建数据库账号创建用户数据库管理员dba密码123456要求允许任意IP连接。需求2创建开发人员账号用户developer要求只能操作company_hr数据库。权限允许SELECT、INSERT、UPDATE禁止DROP、DELETE需求3创建只读账号用户readonly要求只能查询shop数据库。需求4权限检查完成1. 查看用户权限2. 查看授权结果3. 回收用户权限项目四企业多表连接查询实战企业背景公司员工信息分开存储员工表employee部门表department需求1查询员工和部门信息要求显示员工姓名、部门名称、办公地点需求2查询所有员工包括没有部门信息的员工。要求使用左连接需求3查询所有部门包括没有员工的部门。要求使用右连接需求4统计部门人数输出| 部门 | 人数 || --- | -- || 技术部 | 3 || 运维部 | 5 |项目五企业数据库备份练习企业需求每天凌晨需要备份员工数据库。需要完成需求1复制员工表结构。需求2复制员工表结构和数据。需求3创建员工历史备份表employee_backupMySQL 一主两从复制搭建实施文档说明本文记录 CentOS 7 环境下 MySQL 源码编译安装、时间同步配置、一主两从复制搭建以及 Nginx 传统升级和平滑升级的操作流程。文中的 IP 按本地实验环境 192.168.64.11、192.168.64.12、192.168.64.13 编写。一主两从版本1三个mysql编译安装同版本的这里把编译安装归为一个完整步骤说明。yum install -y gcc gcc-c make cmake ncurses-devel bison openssl-devel安装编译 MySQL 需要的基础工具和库useradd -r -s /sbin/nologin mysql创建 mysql 系统用户不允许登录系统只给 MySQL 服务运行用cd /root进入源码包所在目录tar -zxvf mysql-5.7.17.tar.gz如果是.zipunzip 文件.zip如果是.tar.bz2就tar -jxvf 文件.tar.bz2解压 MySQL 源码包cd mysql-5.7.17MySQL 5.7 编译需要 boost。你如果是 mysql-boost-5.7.20.tar.gz 那种包里面可能自带boost如果是普通 mysql-5.7.17.tar.gz需要提前有 boost。mkdir -p /usr/local/boost如果有boost 包tar -zxvf /root/boost_1_59_0.tar.gz -C /usr/local/boost --strip-components1cmake 配置编译参数这是最关键的一步cmake . \-DCMAKE_INSTALL_PREFIX/usr/local/mysql \-DMYSQL_DATADIR/usr/local/mysql/data \-DSYSCONFDIR/etc \-DWITH_BOOST/usr/local/boost \-DWITH_INNOBASE_STORAGE_ENGINE1 \-DWITH_PARTITION_STORAGE_ENGINE1 \-DWITH_FEDERATED_STORAGE_ENGINE1 \-DWITH_BLACKHOLE_STORAGE_ENGINE1 \-DWITH_MYISAM_STORAGE_ENGINE1 \-DENABLED_LOCAL_INFILE1 \-DDEFAULT_CHARSETutf8mb4 \-DDEFAULT_COLLATIONutf8mb4_general_ci \-DEXTRA_CHARSETSall \-DWITH_SSLsystem \-DWITH_ZLIBsystem \-DWITH_SYSTEMD1生成 Makefile指定安装目录、数据目录、配置文件目录、字符集、存储引擎、systemd 支持如果 cmake 报错先清缓存再重新 cmakerm -f CMakeCache.txt删除上一次失败的 cmake 缓存rm -rf CMakeFiles删除上一次失败的 cmake 临时目录make -j$(nproc)开始编译-j$(nproc) 表示按 CPU 核心数并行编译make install把编译好的 MySQL 安装到 /usr/local/mysql到这一步只是“程序文件安装完成”。还不能算 MySQL 服务完整可用因为还没配置、初始化数据目录、注册服务。chown -R mysql:mysql /usr/local/mysql把 MySQL 安装目录交给 mysql 用户管理echo export PATH/usr/local/mysql/bin:$PATH /etc/profile把 MySQL 命令加入系统 PATHsource /etc/profile让 PATH 立即生效mysql --version创建 /etc/my.cnfvim /etc/my.cnf[mysqld]basedir/usr/local/mysqldatadir/usr/local/mysql/datasocket/usr/local/mysql/mysql.sockpid-file/usr/local/mysql/mysqld.pidport3306usermysqlcharacter-set-serverutf8mb4collation-serverutf8mb4_general_ciskip-name-resolve[client]socket/usr/local/mysql/mysql.sockdefault-character-setutf8mb4然后保存wq退出chown mysql:mysql /etc/my.cnf让 mysql 用户能读取配置文件数据库初始化cd /usr/local/mysql/bin/./mysqld \--initialize-insecure \--usermysql \--basedir/usr/local/mysql \--datadir/usr/local/mysql/data服务管理cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/systemctl daemon-reloadsystemctl start mysqld.servicesystemctl enable mysqldnetstat -anpt | grep 3306账号密码管理设置 root 密码mysqladmin -uroot password 123456登录mysql -uroot -p1234562.配置时间同步主[rootmaster ~]# yum -y install ntpdate ntp #安装ntp软件[rootmaster ~]# ntpdate ntp.aliyun.com #时间同步[rootmaster ~]# vim /etc/ntp.conf #编辑配置文件fudge 127.127.1.0 stratum 10#设置本机的时间层级为10级0级表示时间层级为0级是向其他服务器提供时间同步源的意思不要设置为0级server 127.127.1.0 #设置本机为时间同步源三台机器同样关闭防火墙和增强功能[rootmaster ~]# systemctl start ntpd[rootmaster ~]# systemctl stop firewalld.service[rootmaster ~]# setenforce 0从 配置时间systemctl stop ntpd[rootlocalhost ~]# yum install ntp ntpdate -y[rootlocalhost ~]# ntpdate 192.168.64.11两台从服务器配置相同#master服务器同步阿里云时钟服务器ntpdate ntp.aliyun.comntpdate 192.168.64.11crontab -e*/10 * * * * /usr/sbin/ntpdate 192.168.64.11主服务器修改配置文件192.168.64.11[rootmaster ~]# vim /etc/my.cnf#在mysqld模块下修改一下内容#开启二进制日志文件之后生成的日志名为master-binlog_binmaster-bin#开启从服务器日志同步log_slave-updatestrue#主服务器id为1不可重复server_id 1保存退出wq重启服务[rootmaster ~]# systemctl restart mysqld[rootmaster ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.mysql GRANT REPLICATION SLAVE ON *.* TO myslave192.168.64.% IDENTIFIED BY 123456;Query OK, 0 rows affected (0.00 sec)#刷新权限表mysql flush privileges;Query OK, 0 rows affected (0.00 sec)查看master数据库状态show master status;从服务器配置192.168.64.12 和 192.168.64.13 基本一样只是 server_id 不能重复[rootslave1 ~]# vim /etc/my.cnf#开启二进制日志文件log-binmaster-bin#设置server_id 为 22第二台从库为 23server_id 22#从主服务器上同步日志文件记录到本地relay-logrelay-log-bin#定义relay-log的位置和名称index索引relay-log-indexslave-relay-bin.index保存退出wq然后记得重启systemctl restart mysqld开启从服务器功能[rootslave1 ~]# mysql -uroot -p然后CHANGE MASTER TOMASTER_HOST192.168.64.11,MASTER_USERmyslave,MASTER_PASSWORD123456,MASTER_LOG_FILEmaster-bin.000003,MASTER_LOG_POS603;关闭io进程STOP SLAVE;如果之前已经启动过复制先停掉再重新配置START SLAVE; 启动复制SHOW SLAVE STATUS\G; 查看从服务器状态Nginx 1.20 升级到 1.22停机升级和平滑升级流程一、升级说明目标将 Nginx 1.20 升级为 1.22。以下示例假设 Nginx 安装在/usr/local/nginx配置文件为/usr/local/nginx/conf/nginx.conf主进程 PID 文件为/usr/local/nginx/logs/nginx.pid。先确认当前版本/usr/local/nginx/sbin/nginx -v二、升级前准备1. 备份配置和程序mkdir -p /data/nginx-backupcp -a /usr/local/nginx/conf /data/nginx-backup/conf-$(date %F)cp -a /usr/local/nginx/sbin/nginx /data/nginx-backup/nginx-1.202. 检查当前配置/usr/local/nginx/sbin/nginx -t必须看到syntax is oktest is successful3. 下载并编译 Nginx 1.22cd /usr/local/srcwget http://nginx.org/download/nginx-1.22.1.tar.gztar -zxf nginx-1.22.1.tar.gzcd nginx-1.22.1获取旧版本编译参数避免丢失模块/usr/local/nginx/sbin/nginx -V 21根据旧版本输出执行配置。例如./configure \--prefix/usr/local/nginx \--usernginx \--groupnginx \--with-compat \--with-file-aio \--with-http_stub_status_module \--with-http_gzip_static_module \--with-http_flv_module \--with-http_ssl_module \--with-http_v2_module \--with-pcre \--with-stream \--with-stream_ssl_module \--http-log-path/var/log/nginx/access.log \--error-log-path/var/log/nginx/error.log \--pid-path/var/run/nginx/nginx.pid \--lock-path/var/lock/nginx.lock \--add-dynamic-module/opt/nginx-module-vtsmake -j$(nproc)编译完成后先确认新程序版本./objs/nginx -v应显示nginx version: nginx/1.22.1三、传统升级停机升级1. 停止旧 Nginx如果使用 systemdsystemctl stop nginx如果没有 systemd/usr/local/nginx/sbin/nginx -s stop确认进程已经停止ps -ef | grep nginx2. 替换 Nginx 程序先保存旧程序cp -a /usr/local/nginx/sbin/nginx /data/nginx-backup/nginx-1.20-$(date %F-%H%M%S)再替换为新程序cp ./objs/nginx /usr/local/nginx/sbin/nginxchmod 755 /usr/local/nginx/sbin/nginx3. 检查配置并启动/usr/local/nginx/sbin/nginx -tsystemctl start nginx如果没有 systemd/usr/local/nginx/sbin/nginx4. 验证升级结果/usr/local/nginx/sbin/nginx -vsystemctl status nginxcurl -I http://127.0.0.1传统升级期间客户端会出现短暂连接失败适合维护窗口执行。四、平滑升级在线升级平滑升级必须使用与旧版本相同的安装路径、配置路径和 PID 路径。1. 编译新版本按照前面的步骤完成 Nginx 1.22 编译但暂时不要覆盖正在运行的旧程序。确认新程序./objs/nginx -v./objs/nginx -t -c /usr/local/nginx/conf/nginx.conf2. 备份旧程序并替换二进制文件cp -a /usr/local/nginx/sbin/nginx /data/nginx-backup/nginx-1.20-$(date %F-%H%M%S)cp ./objs/nginx /usr/local/nginx/sbin/nginxchmod 755 /usr/local/nginx/sbin/nginx3. 向旧主进程发送 USR2 信号kill -USR2 $(cat /usr/local/nginx/logs/nginx.pid)此时旧主进程会保留为 nginx.pid.oldbin同时启动新主进程。查看进程ps -ef | grep nginx检查新旧主进程是否同时存在cat /usr/local/nginx/logs/nginx.pidcat /usr/local/nginx/logs/nginx.pid.oldbin4. 停止旧工作进程确认新主进程和新工作进程正常后执行kill -WINCH $(cat /usr/local/nginx/logs/nginx.pid.oldbin)WINCH 会让旧工作进程平滑退出已经建立的连接会继续处理。5. 退出旧主进程确认业务访问正常后kill -QUIT $(cat /usr/local/nginx/logs/nginx.pid.oldbin)检查旧 PID 文件是否消失ls /usr/local/nginx/logs/nginx.pid.oldbin如果文件不存在说明旧主进程已经退出。6. 验证在线升级/usr/local/nginx/sbin/nginx -vps -ef | grep nginxcurl -I http://127.0.0.1应确认五、回滚方案停机升级回滚systemctl stop nginxcp -a /data/nginx-backup/nginx-1.20 /usr/local/nginx/sbin/nginxcp -a /data/nginx-backup/conf-升级日期/* /usr/local/nginx/conf//usr/local/nginx/sbin/nginx -tsystemctl start nginx平滑升级回滚如果新版本异常在旧主进程仍存在时执行kill -QUIT $(cat /usr/local/nginx/logs/nginx.pid)mv /usr/local/nginx/logs/nginx.pid.oldbin /usr/local/nginx/logs/nginx.pid恢复旧程序cp -a /data/nginx-backup/nginx-1.20 /usr/local/nginx/sbin/nginx/usr/local/nginx/sbin/nginx -t然后启动或恢复旧服务/usr/local/nginx/sbin/nginx