在CentOS7下编译安装MySQL8
How to Install and Compile MySQL 8 on CentOS 7
2021-05-29 17:04:42
Devops
298
https://github.com/torvalds/linux/blob/master/include/uapi/asm-generic/errno.h#L22
在CentOS7下编译安装MySQL8

编译MySQL

下载MySQL源代码压缩包mysql-boost-8.0.17.tar.gz存放到/data/software，使用如下命令进行编译：

yum install epel-release -y  //要求安装cmake3这个包, 需要安装扩展包，cmake3 在扩展库里面
yum install cmake3 -y

yum install centos-release-scl -y //GCC 5.3 or newer is required
yum install devtoolset-7 -y
scl enable devtoolset-7 bash
gcc --version

cd /data/software
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.17.tar.gz
groupadd mysql
useradd -r -g mysql -s /sbin/nologin -d /usr/local/mysql mysql
tar zxvf mysql-boost-8.0.17.tar.gz
cd /data/software/mysql-8.0.17

cmake3 .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DWITH_DEBUG=OFF \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNODB_MEMCACHED=ON \
-DWITH_SSL=system \
-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="zsd edition" \
-DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-7/root/usr/bin/g++ \
-DWITH_BOOST=/data/software/mysql-8.0.17/boost \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DSYSCONFDIR=/data/database/mysql

make -j8  # -j means cpu cores
make install
// make install DESTDIR="/data/program/mysql-8.0.17" 利用这条语句可以指定安装目录，通常我们采用默认安装

配置MySQL

vim /etc/my.cnf

[mysqld]
user        = mysql
port        = 3306
socket        = /tmp/mysql.sock
basedir       = /usr/local/mysql
datadir       = /data/database/mysql
bind-address = 127.0.0.1
#skip-grant-tables

slow_query_log    = 1
slow_query_log_file = /data/database/mysql/mysql_query_slow.log
long_query_time   = 0.5
log_queries_not_using_indexes = on
log_throttle_queries_not_using_indexes = 10 #设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间

secure-file-priv = /data/database/mysql

server-id         = 1
log-bin             = master-bin
log-bin-index       = master-bin.index

#binlog-do-db=bbs,blog
binlog-ignore-db=mysql,test,information_schema

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
#sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#sql_mode          = NO_AUTO_CREATE_USER,ANSI

back_log        = 511
sync_binlog       = 1 
skip-name-resolve   = 1
lower_case_table_names  = 1
binlog_format     = mixed
binlog_cache_size     = 4M
explicit_defaults_for_timestamp = true

max_connections   = 2048
max_connect_errors  = 8192

thread_cache_size   =512 #1g:16 2g:32 4g:64 16g:256 32G:512
#thread_concurrency  = 8   #2*cpu cores
key_buffer_size   = 4096M #1/8 RAM 

innodb_data_file_path       = ibdata1:1024M;ibdata2:1024M:autoextend
innodb_buffer_pool_size     = 16G    #1/2

#innodb_additional_mem_pool_size = 64M
innodb_log_file_size      = 512M
innodb_log_buffer_size      = 32M
innodb_log_files_in_group     = 3
innodb_lock_wait_timeout    = 60
innodb_file_per_table       = 0
innodb_flush_log_at_trx_commit  = 1
#innodb_file_io_threads      = 4
innodb_max_dirty_pages_pct    = 90

default-storage-engine  = InnoDB
#table_cache       = 1024
external-locking    = FALSE
max_allowed_packet    = 128M
sort_buffer_size    = 4M  
join_buffer_size    = 4M
#query_cache_size    = 128M
#query_cache_limit     = 8M
#query_cache_min_res_unit = 2k
thread_stack      = 192K
transaction_isolation   = REPEATABLE-READ
tmp_table_size      = 512M
max_heap_table_size   = 512M
max_binlog_cache_size   = 8M
max_binlog_size     = 1024M
binlog_expire_logs_seconds = 1296000
#expire_logs_days    = 7
read_buffer_size    = 2M
read_rnd_buffer_size  = 32M
bulk_insert_buffer_size = 128M

myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads   = 1
#myisam_recover      = 1

character-set-server = utf8mb4  #utf8
collation-server = utf8mb4_general_ci #utf8

[mysql]
no-auto-rehash

[mysqldump]
quick
max_allowed_packet = 512M

[mysqld_safe]
open-files-limit  = 10240

#[client]
#default-character-set = utf8

安装MySQL

# Postinstallation setup
cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .
mkdir -p /data/database/mysql
chown -R mysql /data/database/mysql
rm -rf /data/database/mysql/*
bin/mysqld --initialize --user=mysql --datadir=/data/database/mysql
bin/mysql_ssl_rsa_setup
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig mysqld on

打包编译包

可以将编译好的mysql打包，方便以后直接安装

cd /data/software/mysql-8.0.17/build/
make package
#下面的语句
ln -s /usr/local/mysql/lib/libmysqlclient.so.21.1.17 /usr/lib64/libmysqlclient.so.21
ln -s /usr/local/mysql/lib/libmysqlclient.so.21.1.17 /usr/lib64/libmysqlclient.so
ln -s /usr/local/mysql/lib/libmysqlclient.so.21.1.17 /usr/lib/libmysqlclient.so.21
ln -s /usr/local/mysql/lib/libmysqlclient.so.21.1.17 /usr/lib/libmysqlclient.so
ln -s /usr/local/mysql/lib/libmysqlclient.so.21.1.17 /usr/lib/libmysqlclient.so_r.21
ln -s /usr/local/mysql/lib/libmysqlclient.so.21.1.17 /usr/lib/libmysqlclient_r.so

初始化MySQL

mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/database/mysql --user=mysql

--initialize 生成随机密码，官方推荐使用 --initialize-insecure 生成空密码，root用户密码默认为空 --basedir MySQL的安装目录，一般放在/usr/local/mysql/ --datadir 数据库的存放路径， 放在比较安全的目录 --user 指定用户去初始化MySQL

修改root密码

mysql8默认的加密方式为caching_sha2_password 与mysql5的加密方式mysql_native_password 不同

  1. 在 /etc/mysql.cnf 的[mysqld]中加入 skip-grant-tables, 重启/etc/init.d/mysqld restart

    或者使用命令 mysqld_safe --skip-grant-tables 启动mysql

use mysql;
update user set authentication_string='' where user='root';
update user set host='localhost' where user='root';
select host,user,authentication_string from user;
flush privileges;
exit
  1. 删除 [mysqld] 中加入 skip-grant-tables, 重启 /etc/init.d/mysqld restart
use mysql
alter user 'root'@'localhost' identified by 'password';

create user 'zane'@'127.0.0.1' identified with mysql_native_password by 'password';
grant all privileges on *.* to 'zane'@'127.0.0.1';

create user 'jxva'@'localhost' identified with mysql_native_password by 'password';
grant all privileges on *.* to 'jxva'@'localhost';

create user 'jxva'@'127.0.0.1' identified with mysql_native_password by 'pass1009';
grant all privileges on *.* to 'jxva'@'127.0.0.1';

create database new_db default charset=utf8;
create user 'dbuser'@'127.0.0.1' identified with mysql_native_password by 'password';
grant execute,select,insert,update,delete on new_db.* to 'dbuser'@'127.0.0.1';

create user 'backup'@'localhost' identified with mysql_native_password by 'backup$mysql';
grant  select, reload, lock tables on *.* to 'backup'@'localhost';

select host,user,Select_priv,Insert_priv,Update_priv,Delete_priv,Drop_priv,Reload_priv,Grant_priv,authentication_string from user;
flush privileges;

回收update权限

revoke update on new_db.* from 'dbuser'@'127.0.0.1';

删除用户

drop  user 'zane'@'127.0.0.1';
drop  user 'dbuser'@'192.168.1.%' ;