LNMP架构（四）：mysql数据库

目录一、mysql安装1.1下载1.2升级cmake1.3升级gcc1.4安装依赖性1.5初始化二、部署phpmyadmin三、主从复制3.1mysql同步3.2master配置3.3slave配置3.4测试3.5再添加一个slave3.6可能遇到的问题四、gtid模式4.1master配置4.2slave配置五、半同步模式5.1master配置5.2slave配置5.3测试六、延迟复制七、并行复制八、慢查询九、mysql组复制9.1server1配置9.2server2配置9.3server3配置9.4测试一、mysql安装1.1下载在官网搜索合适的mysql数据库版本http://www.mysql.com复制到我们的服务器里解包解压缩1.2升级cmake首先卸载系统旧版本和旧的软连接yum remove -y cmake3rm -f /usr/bin/cmake下载新版本之后解包解压缩然后将命令加入环境变量1.3升级gcc首先添加样本源yum install -y centos-release-scl然后修改repo文件替换为阿里云安装源两个都要改加载 gcc9 工具链并打开新 bash当前 shell 内使用高版本编译器退出即失效不改动系统原生 gcc临时生效scl enable devtoolset-9 bashgcc -v查看 gcc 编译器详细信息g -v查看 gC 编译器详细信息1.4安装依赖性yum install -y libaio-devel patchelf然后进入到安装的mysql目录下cd mysql-8.0.36/创建一个新的buildmkdir build cd build目录准备开始编译编译注意加入-DCMAKE_C_COMPILER(ℎℎ)−(which g)是为了让它在编译的时候让它去指定路径下寻找gcc匹配我们现在有的版本而不是让它自己去匹配cmake .. -DCMAKE_C_COMPILER(ℎℎ)−(which g) -DCMAKE_INSTALL_PREFIX/usr/local/mysql -DMYSQL_DATADIR/data/mysql -DSYSCONFDIR/etc/mysql -DMYSQL_UNIX_ADDR/data/mysql/mysql.sock -DMYSQL_TCP_PORT3306 -DWITH_INNOBASE_STORAGE_ENGINE1 -DWITH_MYISAM_STORAGE_ENGINE1 -DWITH_PARTITION_STORAGE_ENGINE1 -DENABLED_LOCAL_INFILE1 -DCMAKE_CXX_FLAGS-O2 -DCMAKE_C_FLAGS-O2 -DWITH_BOOST../boost/boost_1_77_0 -DWITH_SYSTEMD1 -DWITH_SSL/usr/local/openssl -DWITH_ZLIBbundled -DWITH_LIBAIO1 -DDEFAULT_CHARSETutf8mb4 -DDEFAULT_COLLATIONutf8mb4_unicode_ci -DMYSQL_MAINTAINER_MODE0 -DWITH_DEBUG0 -DINSTALL_TESTS0 -DINSTALL_DOCS0检测没有错误后开始编译make -j$(nproc)让它自己去匹配我们的核心数也可也直接make -j2只要不超过我们的cpu核心数即可超过会造成内存溢出编译完成之后make install安装1.5初始化新建一个用户并禁止登录useradd -M -d /usr/local/mysql/ -s /sbin/nologin mysql创建数据目录在编译时定义的数据路径并修改权限使其能够往里面写数据编辑/etc/mysql/my.cnf文件输入一些必要的配置参数后续有其他配置也可以加在里面保存退出即可然后将mysql这个命令添加到我们的系统环境变量里方便后续操作然后生成 mysql 系统库、初始化数据文件、自动生成 root 临时密码执行一次就行/usr/local/mysql/bin/mysqld --defaults-file/etc/mysql/my.cnf --initializegrep temporary password /data/mysql/mysql-error.log生成一个临时密码将mysqld启动文件拷贝到/usr/lib/systemd/systemx下cp mysqld.service /usr/lib/systemd/system刷新一下并启动数据库systemctl daemon-reloadsystemctl enable --now mysqld执行一个安全安装的脚本生产当中会经常使用mysql_secure_installation先输入刚才生成的临时密码再输入两遍新密码即可后续会有一些选项是否激活密码插件直接回车是否再次修改管理员密码直接回车是否删除匿名用户输入 y是否禁用root远程登录输入 y是否删除test数据库输入 y最后刷新授权表登录数据库二、部署phpmyadmin下载phpMyAdmin-5.2.3-all-languages.zip然后用unzip解压缩到/usr/local/nginx/html/的nginx默认发布目录下然后可以做个软连接方便我们调用命令ln -s phpMyAdmin-5.2.3-all-languages/ phpmyadmin然后浏览器访问测试192.168.11.150/phpmyadmin/index.php也可以在nginx.conf文件里指定搜索的发布页面后续浏览器搜索的时候就不用再指定页面注意此时会遇到问题无法登录mysql服务器然后进入到/usr/local/nginx/html/phpmyadmin下修改主配置文件的名字将名字中的sample去掉cp config.sample.inc.php config.inc.php然后编辑这个主配置文件vim config.inc.php将localhost改为127.0.0.1就是tcp连接方式然后保存退出然后再次搜索测试或者保留原来的localhost编辑vim /usr/local/php/etc/php.ini文件在pdo_mysql和mysqli后都指定sock路径告诉它要在哪里找sock文件注意修改完配置文件之后重载一下systemctl reload php-fpm再次测试就能成功登录进去使用mysql三、主从复制3.1mysql同步首先将server1上的mysql同步到另一台主机server2上避免在server2上再次下载安装初始化太过麻烦流程在server1同步给server2mysql的数据和启动文件等rsync -a /usr/local/mysql server2:/usr/local/scp /etc/mysql/my.cnf server2:/etc/mysql/scp /usr/lib/systemd/system/mysqld.service server2:/usr/lib/systemd/system/在server2上创建用户和数据目录然后进行初始化执行安全脚本mysql_secure_installation和server1上一致3.2master配置在/etc/mysql/my.cnf文件里加入server-id添加完成之后重启mysqld服务创建复制用户授权查看状态在server2上进行测试看能否登录进去它只能做复制3.3slave配置在server2上编辑/etc/mysql/my.cnf文件设置不一样的server-id不一定要是顺序但是必须不同保存退出后重启服务给从库设置主库的地址、复制账号密码建立主从同步链路参数启动并查看状态关键参数Slave_IO_Running: Yes控制网络Slave_SQL_Running: Yes控制的是数据同步3.4测试利用mysql工具创建一个数据库在master上查看还可以向里面添加新的用户也可以在浏览器里的服务器进行浏览在slave上查看注意一定不要对slave进行改动它只是把数据复制过来不能对其进行改动3.5再添加一个slave再开一台服务器因为在原来的master上已经有了数据就不能够直接去创建复制用户要先在master备份数据库再同步过去以免出错先在server3上同步mysql数据以及配置文件流程与server2一致在server1上rsync -a /usr/local/mysql server3:/usr/local/在server3上再把一些主配置文件拷贝到server3上scp /etc/mysql/my.cnf server3:/etc/mysql/scp /usr/lib/systemd/system/mysqld.service server3:/usr/lib/systemd/system/然后添加环境变量方便调用再次初始化即可初始化完成之后启动服务在master上备份数据库使用mysql.dump在server3上创建一个数据库在server1上把备份好的数据库文件同步到server3上scp dump.db server3,然后再在server3上将数据导入进去mysql -uroot -p123456 test dump.db确保数据内容是一致的即可在server3上也要修改server-id与server1和server2都保持不同然后重启一下mysqld服务因为我们是server1-server2-server3所以要在server2上也创建用户并授权在server3上测试连接继续给server3设置主库的地址、复制账号密码建立主从同步链路参数严格按照实际情况输入参数 CHANGE MASTER TO MASTER_HOST192.168.11.151, MASTER_USERrepl, MASTER_PASSWORDwestos,MASTER_LOG_FILEbinlog.000002,MASTER_LOG_POS1792;在server3上启动并查看状态测试主从同步在server1数据库里插入一个新的数据在server2上查看在server3上查看在masterserver1上通过命令添加新数据后面的server2和server3上也都是同步的3.6可能遇到的问题关机重启服务后sql线程可能会出现问题可能会无法同步数据首先修改密码插件在/etc/mysql/my.cnf文件里的mysqld字段里添加注意所有主从数据库的文件里都要添加default_authentication_pluginmysql_native_password然后修改用户指定认证插件mysql_native_password并刷新在slave服务器上看到没有报错即可下次关机重启就会自动去匹配不会出现报错四、gtid模式GTID 即全局事务 ID为主库每个事务分配唯一标识开启 GTID 模式后主从复制无需指定 binlog 文件和偏移量从库自动获取未执行事务便于故障转移自动识别重复事务简化主从部署是 MySQL 高可用架构基础。4.1master配置在配置文件里加入配置参数启用gtidgtid_modeONenforce-gtid-consistencyON4.2slave配置同样在配置文件里加入配置参数启用gtid然后登录mysql进去先停止slavestop slave;重新配置CHANGE MASTER TO MASTER_HOST192.168.11.150, MASTER_USERrepl, MASTER_PASSWORDwestos, MASTER_AUTO_POSITION 1;然后启动slave观察状态无报错即可可以在server3上做同样修改使其连接到server1上也要添加配置文件五、半同步模式主库提交事务后等待至少一台从库接收并确认收到 binlog才响应客户端5.1master配置安装半同步模块INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so;查看当前 MySQL 实例是否成功加载、启用半同步复制插件激活master半同步模式并查看半同步参数SET GLOBAL rpl_semi_sync_master_enabled 1;SHOW VARIABLES LIKE rpl_semi_sync%;查看半同步状态SHOW STATUS LIKE Rpl_semi_sync%;将半同步参数写入配置文件确保重启后依然生效rpl_semi_sync_master_enabled15.2slave配置安装半同步模块INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so;激活半同步模式SET GLOBAL rpl_semi_sync_slave_enabled 1;需要重启IO线程slave端的半同步才能生效同样半同步参数写入配置文件确保重启后也能生效5.3测试master写入数据insert into westos.user_tb values (user6,666);查看半同步状态显示通过半同步复制的数据是1然后停止所有slave节点的IO线程master节点再次写入数据等待默认超时时间10秒后mysql自动切换为异步模式可以看到显示未通过半同步模式复制的数据为1且半同步模式关闭所有slave节点再次启动IO线程mysql会自动切回半同步模式可以看到状态显示为ON六、延迟复制在其中一个slave上执行配置延时复制使从库获取主库 binlog 后延迟 30 秒再执行中继日志中的事务在master上插入数据后slave不会立即更新而是需要等待指定的延迟后才会执行七、并行复制在mysql8.0里直接编辑主配置文件主库从库查看 MySQL当前所有正在运行的连接线程八、慢查询执行耗时超过 long_query_time 阈值的 SQL 语句MySQL 可将这类 SQL 记录到慢查询日志用于 SQL 性能优化全局开启 MySQL 慢查询日志查询名称以 long 开头的系统变量主要用于查看慢查询时间阈值 long_query_time设置会话慢查询阈值 5 秒模拟慢查询语句查看慢查询日志开启状态九、mysql组复制9.1server1配置首先清空旧数据目录rm -rf /data/mysql/*要确认无业务数据然后初始化mysqld --defaults-file/etc/mysql/my.cnf --initialize初始化完成之后启动服务systemctl start mysqld.service在这之前不要对配置文件做任何修改然后找到临时密码后续登录mysql后修改临时密码修改临时密码在 MySQL 主库上创建一个远程可用的复制账号 rpl_user赋予主从复制所需权限同时控制会话 binlog 开关避免账号创建语句同步到从机退出登录之后修改配置文件添加组复制文件参数保存退出之后重启mysql服务登录mysql服务配置 MGR 恢复通道账号开启集群引导模式启动组复制 → 创建新 MGR 集群关闭集群引导模式查询当前集群所有 MGR 节点成员清单、连接地址、运行状态、主从角色9.2server2配置依旧清空旧数据目录rm -rf /data/mysql/*要确认无业务数据然后初始化mysqld --defaults-file/etc/mysql/my.cnf --initialize初始化完成之后启动服务systemctl start mysqld.service在这之前不要对配置文件做任何修改然后找到临时密码后续登录mysql后修改临时密码MySQL MGR 组复制创建集群恢复账号修改配置文件添加组复制配置文件参数注意server_id和IP配置 MGR 恢复通道账号启动组复制 → 创建新 MGR 集群查询当前集群所有 MGR 节点成员清单、连接地址、运行状态、主从角色9.3server3配置和server2的配置流程基本一致注意修改配置参数时的ip地址要和server3的一致server_id要不同9.4测试在server1上执行一套测试 SQL新建数据库、创建数据表、插入测试数据并查询确认写入成功用于验证 MGR 组复制的数据同步功能之后可到另一节点查询检验数据能否正常复制过去。在server2上查看在server2节点上写入数据然后在server1和server3上查看在server3上写入数据在server1和server2上查看可以看到所有数据都是同步的且每个节点都可以读写数据