MySQL 8.0数据库安装实践1 MySQL的安装方法介绍 当今的互联网企业中，MySQL数据库大多运行在Linux系列操作系统。若应用场景不同、版本不同，MySQL数据库的安装方法也会有区别，下面把常见的几种方法介绍给朋友们 1.1 yum、rpm方式安装 MySQL官网提供了不同版本的rpm安装包，且针对不同操作系统平台的安装包也会有区别。 1.1.1 rpm包安装MySQLrpm包的安装方式相对简单，首先下载安装包--- rpm包listmysql-community-server-8.0.16-2.el7.x86_64.rpmmysql-community-common-8.0.16-2.el7.x86_64.rpmmysql-community-devel-8.0.16-2.el7.x86_64.rpmmysql-community-libs-8.0.16-2.el7.x86_64.rpmmysql-community-libs-compat-8.0.16-2.el7.x86_64.rpmmysql-community-client-8.0.16-2.el7.x86_64.rpm --- 查看系统中的rpm包rpm -qa | grep mysql --- 删除系统中的rpm包rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps --- 安装rpm包rpm -ivh mysql-*.rpm --force 1.1.2 yum安装MySQL yum方式安装MySQL最便捷，只需执行一条命令'yum -y install mysql-server '即可。 --安装repository-rpmsudo yum -y localinstall platform-and-version-specific-package-name.rpm -- EL6sudo yum -y localinstall mysql80-community-release-el6-{version-number}.noarch.rpm -- EL7sudo yum -y localinstall mysql80-community-release-el7-{version-number}.noarch.rpm -- 核验mysql的repository list，新mysql的repository的安装情况yum repolist enabled | grep "mysql.*-community.*" -- 查看mysql的repository listyum repolist all | grep mysql -- 禁用repository中5.7版本，启用8.0sudo yum-config-manager --disable mysql57-communitysudo yum-config-manager --disable mysql80-community -- 配置repo文件vim /etc/yum.repos.d/mysql-community.repo# Enable to use MySQL 8.0[mysql80-community]name=MySQL 8.0 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/enabled=1gpgcheck=1gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql -- 查看repositoryyum repolist enabled | grep mysql -- 安装mysqlsudo yum -y install mysql-community-server 1.2 cmake编译安装MySQL MySQL 5.4.xx及后续版本的特殊性，采用cmake或gmake编译安装。 --- 安装环境依赖包yum -y install bc binutils compat-libcap1 compat-libstdc++-33 glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender-devel libXrender libgcc libstdc++ libstdc++-devel libxcb make nfs-utils net-tools python python-configshell8 python-rtslib python-six smartmontools sysstat targetcli libgcc_s.so.1 libc.so.6 --- 解压软件包tar -zxvf mysql-8.0.17.tar.gz --- 进软件目录cd mysql8.0 --- 配置&编译&安装cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql8.0 \-DMYSQL_DATADIR=/app/mysql8.0/data \-DMYSQL_UNIX_ADDR=/app/mysql8.0/tmp/mysql.sock \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \-DWITH_ZLIB=bundled \-DWITH_SSL=bundled \-DENABLED_LOCAL_INFILE=1 \-DWITH_EMBEDDED_SERVER=1 \-DENABLE_DOWNLOADS=1 \-DWITH_DEBUG=0makemake installln -s /app/mysql8.0 /app/mysql注：需安装cmake等编译工具 1.4 二进制免编译安装MySQL 二进制方式免编译安装的方法和yum安装类似，适合各类MySQL产品，无需复杂编译配置及编译时间的等待，解压软件包后初始化数据库即可完成并启动。 2 安装配置MySQL数据库2.1 安装Mysql软件 2.1.1 依赖包和编译软件Linux环境情况如下--- 查看linux版本cat /etc/redhat-release --- 查看内核版本uname -r --- 查看操作系统位数uname -m 2.1.1.1 安装依赖包 MySQL需要的依赖包，需提前安装rpm -qa | grep ncurses libaioyum -y install ncurses-devel libaio-devel 2.1.1.2 安装编译软件 MySQL 5.5 及以上版本采用特殊的编译方法，需要安装编译工具cmakerpm -qa | grep cmakeyum -y install cmakeyum -y install bc binutils compat-libcap1 compat-libstdc++-33 glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender-devel libXrender libgcc libstdc++ libstdc++-devel libxcb make nfs-utils net-tools python python-configshell8 python-rtslib python-six smartmontools sysstat targetcli libgcc_s.so.1 libc.so.6 gcc gcc-c++ 2.1.2 安装MySQL软件 此次实例采用相对复杂的源代码安装方式 2.1.2.1 创建MySQL账号useradd -s /sbin/nologin -M mysqlid mysql 2.1.2.2 获取MySQL软件包 MySQL下载地址：https://downloads.mysql.com/archives/community/ MySQL 源码包：mysql-5.7.24.tar.gz 2.1.2.3 编译&安装MySQL 配置、编译安装步骤如下： 1. 下载mysql软件包 mkdir -p /opt/mediacd /opt/mediawget -q https://downloads.mysql.com/archives/get/file/mysql-8.0.17.tar.gzls -rtlh 2. 解压、配置mysqltar -zxvf mysql-8.0.17.tar.gz -C /opt/mediacd /opt/media/mysql-8.0.17 cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql8.0 \-DMYSQL_DATADIR=/app/mysql8.0/data \-DMYSQL_UNIX_ADDR=/app/mysql8.0/tmp/mysql.sock \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \-DWITH_BOOST=/usr/local/boost \-DWITH_ZLIB=bundled \-DWITH_SSL=bundled \-DENABLED_LOCAL_INFILE=1 \-DWITH_EMBEDDED_SERVER=1 \-DENABLE_DOWNLOADS=1 \-DWITH_DEBUG=0 \-DFORCE_INSOURCE_BUILD=1 3. 编译、安装mysql--- 编译，启用4核并行编译make -j 4make install 4. 配置安装目录的软连接ln -s /app/mysql8.0 /app/mysqlls -lrth /appls /app/mysql完成上述步骤，mysql软件安装完成 2.2 创建MySQL数据库配置文件、目录授权 MySQL 5.5 及以前版本默认提供多个配置文件模板，MySQL 5.6的support-files目录中没有提供模板 --- 查看模板信息ls -rtlh /app/mysql8.0/support-files/*.cnfls -rtlh /app/mysql/support-files/* --- 备份配置文件mv /etc/my.cnf /etc/cnf.bak --- 生成新配置文件(5.7版本没有my-default.cnf)cp /app/mysql8.0/support-files/my-default.cnf /etc/my.cnf --- 配置my.cnfvim /etc/my.cnf[mysqld]basedir=/app/mysql8.0datadir=/app/mysql8.0/datasocket=/app/mysql8.0/tmp/mysql.sockport=3306plugin_dir=/app/mysql8.0/lib/pluginlog-error=/app/mysql8.0/logs/error.log skip-name-resolveserver-id = 10default-storage-engine = innodbinnodb_buffer_pool_size = 512Mcharacter_set_server = utf8relay-log-space-limit = 2048M max-connections = 320query_cache_limit = 4Mquery_cache_size = 64Mquery_cache_type = 1wait_timeout = 9000connect_timeout = 20thread_cache_size = 256key_buffer_size = 16M join_buffer_size = 2Mmax_heap_table_size = 16Mlow_priority_updates = 1max_seeks_for_key = 100sort_buffer_size = 16Mread_buffer_size = 16Mmax_connect_errors = 10myisam_sort_buffer_size = 64Mtmp_table_size = 64Mread_rnd_buffer_size = 1M --- 软件目录授权chown -R mysql.mysql /app/mysql 2.3 初始化MySQL数据库文件2.3.1 初始化--- MySQL 5.6及以前版本/app/mysql/bin/mysql_install_db --basedir=/app/mysql --datadir=/app/mysql/data --user=mysql --- MySQL 5.7版本/app/mysql/bin/mysqld --no-defaults --initialize --basedir=/app/mysql --datadir=/app/mysql/data --user=mysql --- MySQL 8.0版本/app/mysql8/bin/mysqld --defaults-file=/app/mysql8/my.cnf --initialize --basedir=/app/mysql8 --datadir=/app/mysql8/data --lower-case-table-names=1 --user=mysql /app/mysql8/bin/mysqld --defaults-file=/app/mysql8/my.cnf --initialize --basedir=/app/mysql8 --datadir=/app/mysql8/data --user=mysql 2.3.2 初始化原理、结果说明 初始化是创建基础的库文件，例如，生成mysql库表等。初始化后查看数据目录ls -rlth /app/mysql/datayum -y install treetree /app/mysql/data 2.3.3 MySQL初始化故障排错 初始化出现的故障都必须排除，必须出现两个yes字样，否则后面数据库服务可以启动，也会出现登录不了MySQL数据库等各种问题。 2.4 配置并启动MySQL数据库 2.4.1 配置启动脚本--- 复制启动脚本至启动目录cp /app/mysql/support-files/mysql.server /etc/init.d/mysqldchmod 700 /etc/init.d/mysqldls -l /etc/init.d/mysqld 2.4.2 启动MySQL数据库--- 启动MySQL数据库方法1/etc/init.d/mysqld start --- 停止MySQL数据库/etc/init.d/mysqld stop --- 启动MySQL数据库方法2/app/mysql/bin/mysqld_safe --user=mysql &# “&”表示后台运行mysql服务注：MySQL启动报错：mysqld_safe directory '/app/mysql/tmp' for UNIX socket file don't exists.需创建tmp目录 解决方案--- 创建tmp目录mkdir -p /app/mysql/tmpchown -R mysql.mysql /app/mysql/tmp 2.4.3 检查mysql端口状态netstat -lntup | grep mysql 2.4.4 查看mysql启动日志tail -500f /app/mysql/data/ocean.err 2.4.5 配置MySQL开机自启动--- 方案一chkconfig --add mysqldchkconfig --list | grep mysqld --- 方案二vim /etc/rc.local/etc/init.d/mysqld start
2.5 MySQL命令添加到全局路径 之前配置启动和停止mysql都使用的绝对路径‘/app/mysql/bin/mysql’，很繁琐。添加全局路径后可直接敲mysql。下面来看看配置方法 2.5.1 确认mysql路径ls /app/mysql/bin/mysql 2.5.2 PATH变量中加入mysql路径--- profile中添加mysql路径echo 'export PATH=/app/mysql/bin:$PATH' >> /etc/profile --- 查看profile配置 tail -l /etc/profile --- 生效变更的配置source /etc/profile --- 查看PATH echo $PATH 2.5.3 软连接方式--- mysql所有命令软连接到全局路径下 ln -s /app/mysql/bin/* /usr/local/sbin 2.6 登录mysql，验证服务状态--- 使用临时密码登录mysql -uroot -p'eFhu7agc5A%W' --- 首次登录后需更改密码才能查询数据 ALTER USER USER() IDENTIFIED BY 'root1234'; 查看数据库配置--- 查看当前数据库show databases; --- 查看当前用户select user(); --- 退出ctrl + dquitexit 2.7 MySQL 安全配置2.7.1 root配置密码MySQL管理员root密码默认为空，通过mysqladmin配置密码--- root用户配置密码mysqladmin -u root password 'ocean123' --- 登录验证mysql -uroot -p 键入 ocean123 --- 交互模式配置密码 mysql_secure_installation 2.7.2 清理mysql无用账户--- 查看用户信息select user,host from mysql.user; --- 删除无用账户drop user root@'::1';drop user root@'node1';drop user ''@'node1';drop user ''@'localhost'; --- 验证用户信息select user,host from mysql.user; --- DML删除无用账户delete from mysql.user where user='' and host='MySQL'; flush privileges; 2.7.3 清理无用库--- 删除无用库drop database test; --- 查看库信息 show databases; FAQ1. 系统最小化安装，很多组件包没有安装yum -y install bc binutils compat-libcap1 compat-libstdc++-33 glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender-devel libXrender libgcc libstdc++ libstdc++-devel libxcb make nfs-utils net-tools python python-configshell8 python-rtslib python-six smartmontools sysstat targetcli libgcc_s.so.1 libc.so.6 gcc gcc-c++ 2. 高版本mysql需要boots库的安装才可以正常运行cmake的报错信息CMake Error at cmake/boost.cmake:81 (MESSAGE): You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST= This CMake script will look for boost in . If it is not there, it will download and unpack it (in that directory) for you. If you are inside a firewall, you may need to use an http proxy: export http_proxy=http://example.com:80 Call Stack (most recent call first): cmake/boost.cmake:238 (COULD_NOT_FIND_BOOST) CMakeLists.txt:507 (INCLUDE) 下载boost包，创建目录mkdir -p /usr/local/boost tar -zxvf boost_1_59_0.tar.gz -C /usr/local/boostcmake需要添加“-DWITH_BOOST=/usr/local/boost \” 3. 缺少bison组件包 cmake报错信息CMake Warning at cmake/bison.cmake:20 (MESSAGE): Bison executable not found in PATHCall Stack (most recent call first): sql/CMakeLists.txt:562 (INCLUDE) CMake Warning at cmake/bison.cmake:20 (MESSAGE): Bison executable not found in PATHCall Stack (most recent call first): libmysqld/CMakeLists.txt:194 (INCLUDE)yum -y install bison