如何搭建MYSQL8和CENTOS7.6

本篇文章为大家展示了如何搭建MYSQL8和CENTOS7.6，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。一、环境准备总共3个IP地址：2个物理机IP，1个VIPMASTER：10.18.96.15SLAVE: 10.18.96.16VIP： 10.18.96.17操作系统：CENTOS7.6数据库：MYSQL8.0.20我的硬件资源是8核16GMYSQL8下载地址：https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-boost-8.0.20.tar.gz依赖下载地址：https://mirrors.ustc.edu.cn/gnu/gmp/gmp-6.1.2.tar.xzhttps://mirrors.ustc.edu.cn/gnu/mpfr/mpfr-4.0.2.tar.gzhttps://mirrors.ustc.edu.cn/gnu/mpc/mpc-1.1.0.tar.gzhttps://mirrors.ustc.edu.cn/gnu/gcc/gcc-9.2.0/gcc-9.2.0.tar.gzhttps://github.com/Kitware/CMake/releases/download/v3.15.2/cmake-3.15.2.tar.gz（CMAKE如果使用YUM源安装了CMAKE3，也可以不下载了）二、依赖安装在安装MYSQL8之前，得先有前置准备，否则就是各种报错，无法安装1、GCC编译安装yum -y install epel-releaseyum -y group install “Development Tools”mysql 依赖yum -y install ncurses-devel openssl openssl-devel bisonyum install cmake32、安装gcc 源码编译依赖## 先编译gmp->mpfr->mpccd /usr/local/src/编译: gmp-6.1.2tar -xvf /opt/gmp-6.1.2.tar.xzcd gmp-6.1.2./configure –prefix=/usr/local/gmp-6.1.2make -j $(nproc)make installcd ../编译：mpfr-4.0.2tar -xvf /opt/mpfr-4.0.2.tar.gzcd mpfr-4.0.2./configure –prefix=/usr/local/mpfr-4.0.2 –with-gmp=/usr/local/gmp-6.1.2make -j $(nproc)make installcd ../编译：mpc-1.1.0tar -xvf /opt/mpc-1.1.0.tar.gzcd mpc-1.1.0./configure –prefix=/usr/local/mpc-1.1.0 –with-mpfr=/usr/local/mpfr-4.0.2 –with-gmp=/usr/local/gmp-6.1.2make -j $(nproc)make install# 把mpfr lib 加入 ld.so.conf 不然gcc 编译报错echo /usr/local/mpfr-4.0.2/lib >> /etc/ld.so.confldconfig编译安装gcccd /usr/local/src/tar -xvf /opt/gcc-9.2.0.tar.gzcd gcc-9.2.0./configure –prefix=/usr/local/gcc-9.2.0 -enable-threads=posix -disable-checking -disable-multilib -enable-languages=c,c++ –with-gmp=/usr/local/gmp-6.1.2 –with-mpfr=/usr/local/mpfr-4.0.2 –with-mpc=/usr/local/mpc-1.1.0 –with-tune=generic –with-arch_32=x86-64make -j $(nproc)make install -j $(nproc)(友情提醒，我再make编译GCC的时候，跑了1个多小时才跑完)##备份旧 gcc 可执行文件mv /usr/bin/gcc /usr/bin/gcc.oldmv /usr/bin/g++ /usr/bin/g++.oldmv /usr/bin/c++ /usr/bin/c++.oldmv /usr/bin/cpp /usr/bin/cpp.oldmv /usr/bin/gcov /usr/bin/gcov.old## 创建最新gcc 执行文件软链ln -sf /usr/local/gcc-9.2.0/bin/* /usr/bin/## 删除lib64 目录下.py 文件不然ldconfig 报错cp /usr/local/gcc-9.2.0/lib64/libstdc++.so.6.0.27-gdb.py /opt/rm -rf /usr/local/gcc-9.2.0/lib64/libstdc++.so.6.0.27-gdb.pyecho /usr/local/gcc-9.2.0/lib64 >> /etc/ld.so.confldconfig## 复制libstdc++.so.6.0.27 /lib64/cp /usr/local/gcc-9.2.0/lib64/libstdc++.so.6.0.27 /lib64/# 创建软链 libstdc++.so.6cd /lib64ln -sf libstdc++.so.6.0.27 libstdc++.so.6## 查看是否最新版本strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX编译安装cmake cd /usr/local/src/tar -xvf /opt/cmake-3.15.2.tar.gzcd /opt/cmake-3.15.2./configure –prefix=/usr/local/cmake-3.15.2gmake -j $(nproc)gmake install -j $(nproc)# 创建cmake 软链ln -sf /usr/local/cmake-3.15.2/bin/cmake /bin/cmake3三、MYSQL8安装cd /opt/tar xvf mysql-boost-8.0.20.tar.gzcd /opt/mysql-8.0.20/1、cmakecmake3 -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ -DEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=ON -DWITH_INNODB_MEMCACHED=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DCOMPILATION_COMMENT=”nancy edition” -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/data/mysql/tmp -DMYSQL_UNIX_ADDR=/data/mysql/run/mysql.sock -DMYSQL_TCP_PORT=3306 -DSYSCONFDIR=/data/mysql -DWITH_READLINE=1 -DFORCE_INSOURCE_BUILD=1 -DWITH_SSL=system -DWITH_ZLIB=system -DCMAKE_CXX_COMPILER=/usr/local/gcc-9.2.0/bin/g++ -DCMAKE_C_COMPILER=/usr/local/gcc-9.2.0/bin/gcc注意：如果你的机器没有联网，需要自己下载一个包boost_1_70_0.tar.gz放到/data/mysql/tmp目录下下载地址：https://dl.bintray.com/boostorg/release/1.70.0/source/boost_1_70_0.tar.gz2、编译：gmake -j $(nproc)3、安装：gmake install -j $(nproc)4、创建MYSQL用户及相关目录、授权groupadd mysqluseradd mysql -g mysql -M -s /sbin/nologinmkdir /data/mysql/data/chown mysql.mysql /data/mysql/data/mkdir /data/mysql/runchown mysql.mysql /data/mysql/runmkdir /data/mysql/binlog/chown mysql.mysql /data/mysql/binlog/chown -R mysql.mysql /data/mysql/5、配置文件（主库）vi /etc/my.cnf[mysqld]port = 3306user = mysqlsocket = /data/mysql/run/mysql.sockpid_file = /data/mysql/mysqld.pidbasedir =免费主机域名 /usr/local/mysqldatadir = /data/mysql/datatmpdir = /data/mysql/tmpopen_files_limit = 65535server_id = 1#默认为0,0代表区分大小写，1代表不区分大小写,以小写存储，开发要求要区分大小写lower_case_table_names = 0character_set_server = utf8mb4# 允许最大连接数max_connections = 6000# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统max_connect_errors = 10# 默认8小时。交互等待时间和非交互等待时间# 建议300~500s,两参数值必须一致，且同时修interactive_timeout = 500wait_timeout = 500#短时间内的多少个请求可以被存在堆栈中back_log=1024default_storage_engine = InnoDB#log_slave_updates = 1#query_cache_size=1M#*********** Logs related settings ***********log_bin = /data/mysql/binlog/mysql-binlog-bin-index=/data/mysql/binlog/mysql-bin.index#binlog日志过期时间设置,单位为秒,604800=7天binlog_expire_logs_seconds=604800log_bin_trust_function_creators=1#最小400，对内存占用影响大 #服务模式：从1400设置为400，内存从324M降到227M#table_definition_cache=400#优化参数,按事务刷盘，刷日志 [0:最快模式,1:安全模式,2:比0安全但比0要慢]innodb_flush_log_at_trx_commit=2# 提交1次事务刷1次，可以为nsync_binlog=1# 默认使用“mysql_native_password”插件认证default_authentication_plugin=mysql_native_password# 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘# 25%~50%innodb_max_dirty_pages_pct=30# 后台进程最大IO性能指标# 默认200，如果SSD，调整为5000~20000innodb_io_capacity=200# 默认10M。防止高并发下，数据库受影响innodb_data_file_path=ibdata1:1024M:autoextend# 默认2，单位s。慢查询时间,建议0.3-0.5long_query_time=0.3# 8.0默认row。记录格式，让数据安全可靠binlog_format=row# 默认8小时。交互等待时间和非交互等待时间# 建议300~500s,两参数值必须一致，且同时修改interactive_timeout=500wait_timeout=500#日志大小innodb_log_file_size=256M#日志缓存大小innodb_log_buffer_size=12M#这里确认是否起用压缩存储功能innodb_file_per_table=1#决定压缩程度的参数，如果你设置比较大，那么压缩比较多，耗费的CPU资源也较多；#相反，如果设置较小的值，那么CPU占用少。默认值6，可以设置0-9#innodb_compression_level=6#指定在每个压缩页面可以作为空闲空间的最大比例，#该参数仅仅应用在设置了innodb_compression_failure_threshold_pct不为零情况下，并且压缩失败率通过了中断点。#默认值50，可以设置范围是0到75innodb_compression_pad_pct_max=50[mysqld_safe]log-error=/data/mysql/data/csp-serverdb-1.err[mysql]# 设置mysql客户端默认字符集default-character-set=utf8mb4[client]# 设置mysql客户端连接服务端时默认使用的端口port=3306default-character-set=utf8mb46、配置文件（从库）其实和主库一样的，就server_id改一下7、 复制启动文件cp /opt/mysql-8.0.20/support-files/mysql.server /etc/init.d/mysqldchmod 700 /etc/init.d/mysqld8、 添加mysql 到环境变量echo export PATH=$PATH:/usr/local/mysql/bin >>/etc/profilessource /etc/profileecho /usr/local/mysql/lib >> /etc/ld.so.confldconfigecho PATH=$PATH:/usr/local/mysql/bin ~/.bash_profilesource ~/.bash_profile9、初始化/usr/local/mysql/bin/mysqld –defaults-file=/etc/my.cnf –initialize –user=mysql初始化后屏幕上会打印出来root用户登录临时随机密码10、启动service mysqld start或者/etc/init.d/mysqld start或者/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –user=mysql &不记得初始化密码，可以在日志中查找/data/mysql/data/serverdb-1.err11、修改密码：alter user ‘root’@’localhost’ identified by ”;flush privileges; (使用mysql -uroot -hlocalhost可本地免密登录，用于写keepalived脚本)create user ‘root’@’%’ identified by ‘123456’
withGRANTOPTION;
grant allprivileges on *.* to ‘root’@’%’;（建其他普通用户也是差不多的套路，详细增减权限也可以在navicat操作）ALTER user ‘root’@’%’ IDENTIFIED BY ‘123456’; （这个是修改）flush privileges;四、MYSQL主从配置1、主库清除一下防火墙iptables -Fsetenforce 0create user ‘repl_user’@’%’ identified by ‘123456’;flush privileges;grant REPLICATION SLAVE on *.* to ‘repl_user’@’%’;flush privileges;master_log_file和master_log_pos通过在主库上使用命令获得：show master status G;2、从库清除一下防火墙iptables -Fsetenf免费主机域名orce 0在从库上执行：change master tomaster_host=’10.18.96.15′,master_port=3306,master_user=’repl_user’,master_password=’123456′,master_log_file=’mysql-bin.000001′,master_log_pos=156;start slave;show slave statusG;五、keepalived搭建1、安装keepalivedcd /opt/tar zxvf /opt/keepalived-1.4.2.tar.gzcd /opt/keepalived-1.4.2./configure如果遇到以下报错!!! OpenSSL is not properly installed on your system. !!! !!! Can not include OpenSSL headers files.解决方法:yum -y install openssl-devel2、编译：makemake install3、拷贝配置文件mkdir /etc/keepalivedcp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/cp /usr/local/sbin/keepalived /usr/sbin/4、配置文件vi /etc/keepalived/keepalived.confglobal_defs { router_id MySQL-HA2}vrrp_script check_run {script “/etc/keepalived/mysql_check.sh”interval 60}vrrp_sync_group VG2 {group {VI_1}}vrrp_instance VI_2 { state BACKUP interface eth0 virtual_router_id 52 priority 100 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1234 } track_script { check_run } notify_master /etc/keepalived/master.sh notify_stop /etc/keepalived/stop.sh virtual_ipaddress { 10.18.96.17 }}从库一样的，就是priority 90 这里改成905、自动切换脚本，2个节点一样的配置(1)vi /etc/keepalived/mysql_check.sh#!/bin/bash. ~/.bash_profilecount=1while truedomysql -uroot -hlocalhost -e “show status;” > /dev/null 2>&1i=$?ps aux | grep mysqld | grep -v grep > /dev/null 2>&1j=$?if [ $i = 0 ] && [ $j = 0 ]then exit 0else if [ $i = 1 ] && [ $j = 0 ] then exit 0 else if [ $count -gt 5 ] then break fi let count++ continue fifidone/usr/bin/systemctl stop keepalived(2)vi /etc/keepalived/master.sh#!/bin/bash. /home/mysql/.bashrcMaster_Log_File=$(mysql -uroot -hlocalhost -e “show slave statusG” | grep -w Master_Log_File | awk -F”: ” ‘{print $2}’)Relay_Master_Log_File=$(mysql -uroot -hlocalhost -e “show slave statusG” | grep -w Relay_Master_Log_File | awk -F”: ” ‘{print $2}’)Read_Master_Log_Pos=$(mysql -uroot -hlocalhost -e “show slave statusG” | grep -w Read_Master_Log_Pos | awk -F”: ” ‘{print $2}’)Exec_Master_Log_Pos=$(mysql -uroot -hlocalhost -e “show slave statusG” | grep -w Exec_Master_Log_Pos | awk -F”: ” ‘{print $2}’)i=1while truedoif [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ]then echo “ok” breakelse sleep 1 if [ $i -gt 60 ] then break fi continue let i++fidonemysql -uroot -hlocalhost -e “stop slave;”mysql -uroot -hlocalhost -e “reset slave all;”mysql -uroot -hlocalhost -e “reset master;”mysql -uroot -hlocalhost -e “show master status;” > /tmp/master_status_$(date “+%y%m%d-%H%M”).txt(3)vi /etc/keepalived/stop.sh#!/bin/bash. ~/.bash_profileM_File1=$(mysql -uroot -hlocalhost -e “show master statusG” | awk -F’: ‘ ‘/File/{print $2}’)M_Position1=$(mysql -uroot -hlocalhost -e “show master statusG” | awk -F’: ‘ ‘/Position/{print $2}’)sleep 1M_File2=$(mysql -uroot -hlocalhost -e “show master statusG” | awk -F’: ‘ ‘/File/{print $2}’)M_Position2=$(mysql -uroot -hlocalhost -e “show master statusG” | awk -F’: ‘ ‘/Position/{print $2}’)i=1while truedoif [ $M_File1 = $M_File2 ] && [ $M_Position1 -eq $M_Position2 ]then echo “ok” breakelse sleep 1 if [ $i -gt 60 ] then break fi continue let i++fidone然后确认MYSQL主从都已配置完成并启动启动keepalivedsystemctl start keepalived下面这个是停止命令systemctl stop keepalived这样就部署完成了，其他的测试啥的（什么自动主备切换啊，VIP漂浮啊），自己搞就好了。注意，如果是生产库中，有多套keepalived的，virtual_router_id，group,instance之类的编组更换一下就好。如果是生产库，注意一下root用户授权
create user ‘root’@’%’ identified by ‘123456’ with
GRANTOPTION;grant allprivileges on *.* to ‘root’@’%’;flush privileges;还有一个问题就是，我安装完MYSQL，发现内存16G全被吃完了，手动刷新下缓存echo 1 > /proc/sys/vm/drop_caches然后观察一段时间，空闲内存没有再被吃完。上述内容就是如何搭建MYSQL8和CENTOS7.6，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注云技术行业资讯频道。

相关推荐: 如何把ACCESS导入SQL数据库

小编给大家分享一下如何把ACCESS导入SQL数据库，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！一，首先，我说的是在ACCESS2000，SQL2000之间转换，其他的我也还没有尝…