Mysql8.0.18的源码安装
一、环境准备
OS：CentOS 7.4
DB：Mysql 8.0.18
硬件：40G以上空间，4G以上内存
二、安装配置
1、下载文件
下载mysql-boost-8.0.18.tar.gz
2、安装依赖包
# yum -y install gcc gcc-c++ ncurses-devel openssl-devel cmake3 bison wget bzip2
# ln -s /usr/bin/cmake3 /usr/bin/cmake
3、用户组设置
useradd mysql
groupadd mysql
mkdir -p /usr/local/mysql
mkdir -p /data/mysql8.0/log
chown -R mysql:mysql /data/mysql8.0/
chown -R mysql:mysql /usr/local/mysql
4、系统参数设置
echo -e "
kernel.sem = 250 32000 100128
kernel.sysrq = 0
net.core.rmem_max = 33554432
net.core.wmem_max = 33554432
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.somaxconn = 8192
net.core.netdev_max_backlog = 16384
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
net.ipv4.tcp_rmem = 409665536 16777216
net.ipv4.tcp_wmem = 409632768 16777216
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 8192
net.nf_conntrack_max = 1048576
net.netfilter.nf_conntrack_tcp_timeout_established = 12000
vm.swappiness = 0
vm.zone_reclaim_mode = 0" >> /etc/sysctl.conf
sysctl –p
sed -i '/^GRUB_CMDLINE_LINUX/ s/\"$/ numa=off\"/g' /etc/default/grub
grub2-mkconfig -o /etc/grub2.cfg
--需要reboot
--查看dmesg | grep -i numa或cat /proc/cmdline
limit="\n*\tsoft\tnofile\t65536\n*\thard\tnofile\t65536\n*\tsoft\tnproc\t131072\n*\thard\tnproc\t131072\n*\tsoft\tstack\tunlimited\n*\thard\tstack\tunlimited"
echo -e "$limit" >> /etc/security/limits.conf
echo -e "$limit" >> /etc/security/limits.d/20-nproc.conf
5、编译安装
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DCMAKE_CXX_COMPILER=/usr/bin/g++ -DMYSQL_DATADIR=/data/mysql8.0/datanode1 -DMYSQL_UNIX_ADDR=/data/mysql8.0/mysql.sock -DWITH_BOOST=/usr/local/boost -DSYSCONFDIR=/usr/local/mysql -DFORCE_INSOURCE_BUILD=1 -DWITH_SSL=system -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_EXTRA_CHARSETS=all -DFORCE_INSOURCE_BUILD=1 -DCOMPILATION_COMMENT="Kenyon's MySQL 8.0"
make && make install
--PS：可以提前下载boost_1_70_0.tar.gz放在/usr/local/boost下面,或者使用-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost下载
6、配置参数
port = 3306
socket = /data/mysql8.0/datanode1/mysql.sock
default-character-set = utf8mb4
[mysqld]
server-id = 99999 ## 适当调整，可以选取ipv4地址
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir =/data/mysql8.0/datanode1
tmpdir = /tmp
socket = /data/mysql8.0/datanode1/mysql.sock
pid-file = /data/mysql8.0/datanode1/mysql.pid
default-storage-engine = INNODB
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED
secure_file_priv =
# connection
back_log = 400
max_connections = 2000
interactive_timeout = 28800
wait_timeout = 28800
slave_net_timeout = 300
skip_name_resolve = on
# binlog
log-bin = mysql-bin
log-bin-index = mysql-bin
relay-log = relay-bin
relay-log-index = relay-bin
expire_logs_days = 7
binlog_cache_size = 8M
binlog_format = ROW
log_slave_updates = 1
relay_log_purge = 1
log-bin-trust-function-creators = 1
#slave_parallel_workers = 4
# caches & limits
bulk_insert_buffer_size = 8M
innodb_buffer_pool_size = 18G ## 适当调整:内存60%
innodb_log_buffer_size = 32M
innodb_sort_buffer_size = 4M
join_buffer_size = 4M
sort_buffer_size = 4M
key_buffer_size = 16M
innodb_max_dirty_pages_pct = 15
max_allowed_packet = 16M
max_heap_table_size = 16M
read_buffer_size = 512K
read_rnd_buffer_size = 512K
open-files-limit = 10240
table_definition_cache = 4096
table_open_cache = 4096
table_open_cache_instances = 16
# thread
thread_cache_size = 300
innodb_thread_concurrency = 0
innodb_io_capacity = 500
innodb_io_capacity_max = 1000
# innodb log
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_autoextend_increment = 128
innodb_flush_log_at_trx_commit = 2
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /data/mysql8.0/datanode1/
innodb_print_all_deadlocks
sync_binlog = 1000
# innodb file
innodb_data_home_dir = /data/mysql8.0/datanode1/
innodb_data_file_path = ibdata:2G:autoextend
innodb_file_per_table = 1
tmp_table_size = 4M
# data
innodb_autoinc_lock_mode = 1
auto_increment_increment = 3 ## 适当调整
auto_increment_offset = 1 ## 适当调整
max_prepared_stmt_count = 65530
# character
character-set-server = utf8mb4
character-set-client = utf8mb4
# logging
slow-query-log = 1
slow-query-log-file = /data/mysql8.0/log/mysql-slow.log
log-error = /data/mysql8.0/log/mysql-error.log
long_query_time = 1
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
7、初始化
/usr/local/mysql/bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/data/mysql8.0/datanode1 --lower-case-table-names=1 --user=mysql
8、设置自启动
# chkconfig --add mysqld
# chkconfig mysqld on
9、启动关闭
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18 |
+-----------+
1 row in set (0.00 sec)
三、问题集合
1、cmake版本过低
[root@kenyon mysql-8.0.18]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql8.0/data -DMYSQL_UNIX_ADDR=/data/mysql8.0/mysql.sock -DWITH_BOOST=/root/mysql-8.0.0-dmr/boost -DSYSCONFDIR=/usr/local/mysql -DWITH_SSL=system -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DENABLE_DTRACE=0 -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_EMBEDDED_SERVER=1 -DCOMPILATION_COMMENT="Kenyon's MySQL 8.0"
-- Running cmake version 2.8.12.2
CMake Warning at CMakeLists.txt:43 (MESSAGE):
Please use cmake3 rather than cmake on this platform
-- Please install cmake3 (yum install cmake3)
CMake Error at CMakeLists.txt:73 (CMAKE_MINIMUM_REQUIRED):
CMake 3.5.1 or higher is required. You are running version 2.8.12.2
-- Configuring incomplete, errors occurred!
--解决办法是用删除老的cmake版本，用新的cmake3版本替换cmake2版本
rm -f /usr/bin/cmake2
rm -f /usr/bin/cmake
yum install cmake3
ln -s /usr/bin/cmake3 /usr/bin/cmake
2、强制源码安装
-- MySQL 8.0.18
-- Source directory /root/software/mysql-8.0.18
-- Binary directory /root/software/mysql-8.0.18
CMake Error at CMakeLists.txt:341 (MESSAGE):
Please do not build in-source. Out-of source builds are highly
recommended: you can have multiple builds for the same source, and there is
an easy way to do cleanup, simply remove the build directory (note that
'make clean' or 'make distclean' does *not* work)
You *can* force in-source build by invoking cmake with
-DFORCE_INSOURCE_BUILD=1
根据提示，在cmake编译的时候加上-DFORCE_INSOURCE_BUILD=1参数
3、gcc版本过低
-- Source directory /root/software/mysql-8.0.18
-- Binary directory /root/software/mysql-8.0.18
CMake Warning at CMakeLists.txt:339 (MESSAGE):
This is an in-source build
-- CMAKE_GENERATOR: Unix Makefiles
CMake Error at cmake/os/Linux.cmake:59 (MESSAGE):
GCC 5.3 or newer is required (-dumpversion says 4.8.5)
Call Stack (most recent call first):
CMakeLists.txt:442 (INCLUDE)
解决办法是升级gcc，这是个很漫长的过程
--直接安装gcc可能会=会报错，需要安装gmp、mpfr、mpc，各组件前后有关系，按此顺序执行
configure: error: Building GCC requires GMP 4.2+, MPFR 2.4.0+ and MPC 0.8.0+.
Try the --with-gmp, --with-mpfr and/or --with-mpc options to specify
wget ftp://ftp.gnu.org/gnu/mpc/mpc-1.1.0.tar.gz -P /usr/local/src
wget ftp://ftp.gnu.org/gnu/gmp/gmp-6.1.2.tar.bz2 -P /usr/local/src
wget http://ftp.gnu.org/gnu/mpfr/mpfr-4.0.2.tar.gz -P /usr/local/src
wget http://ftp.gnu.org/gnu/m4/m4-1.4.18.tar.gz -P /usr/local/src
wget http://ftp.gnu.org/gnu/gcc/gcc-9.2.0/gcc-9.2.0.tar.gz -P /usr/local/src
tar -zxvf mpc-1.1.0.tar.gz
tar -zxvf mpfr-4.0.2.tar.gz
tar -jxvf gmp-6.1.2.tar.bz2
tar -zxvf m4-1.4.18.tar.gz
tar -zxvf gcc-9.2.0.tar.gz
--解压gmp需要yum install bzip2 -y
mkdir -p /usr/local/m4-1.4.18
mkdir -p /usr/local/gmp-6.1.2
mkdir -p /usr/local/mpfr-4.0.2
mkdir -p /usr/local/mpc-1.1.0
mkdir -p /usr/local/gcc-9.2.0
cd m4-1.4.18
./configure --prefix=/usr/local/m4-1.4.18
make && make install
ln -s /usr/local/m4-1.4.18/bin/m4 /usr/bin/m4
cd gmp-6.1.2
./configure --prefix=/usr/local/gmp-6.1.2
make && make install
cd mpfr-4.0.2
./configure --prefix=/usr/local/mpfr-4.0.2/ --with-gmp=/usr/local/gmp-6.1.2/
make && make install
cd mpc-1.1.0
./configure --prefix=/usr/local/mpc-1.1.0 --with-gmp=/usr/local/gmp-6.1.2/ --with-mpfr=/usr/local/mpfr-4.0.2/
make && make install
[root@kenyon ]# more /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/local/gmp-6.1.2/lib
/usr/local/mpfr-4.0.2/lib
/usr/local/mpc-1.1.0/lib
# ldconfig
cd gcc-9.2.0
./configure --prefix=/usr/local/gcc-9.2.0 --with-gmp=/usr/local/gmp-6.1.2 --with-mpfr=/usr/local/mpfr-4.0.2 --with-mpc=/usr/local/mpc-1.1.0 --disable-multilib
make && make install
mv /usr/bin/gcc /usr/bin/gcc4.8.5
mv /usr/bin/g++ /usr/bin/g++4.8.5
ln -s /usr/local/gcc-9.2.0/bin/gcc /usr/bin/gcc
ln -s /usr/local/gcc-9.2.0/bin/g++ /usr/bin/g++
4、编译参数问题
c++: error: unrecognized command line option ‘-std=c++14’
解决办法是编译时添加 -DCMAKE_CXX_COMPILER=/usr/bin/g++
5、动态库问题
```../runtime_output_directory/uca9dump: /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by ../runtime_output_directory/uca9dump)
--原因是升级了gcc，却没有将升级后的gcc的动态库去替换老版本的gcc动态库所致
解决办法：
cp /usr/local/src/gcc-9.2.0/x86_64-pc-linux-gnu/libstdc++-v3/src/.libs/libstdc++.so.6.0.27 /usr/lib64
cd /usr/lib64
ln -s libstdc++.so.6.0.27 libstdc++.so.6
6、源码syscall问题
在函数‘void buf_pool_create(buf_pool_t*, ulint, ulint, std::mutex*, dberr_t&)’中: /root/mysql-8.0.17/storage/innobase/buf/buf0buf.cc:1220:44: 错误：‘SYS_gettid’在此作用域中尚未声明 setpriority(PRIO_PROCESS, (pid_t)syscall(SYS_gettid), -20);
解决办法：在源文件storage/innobase/buf/buf0buf.cc的开头添加： #include "sys/syscall.h"
7、swap_lint问题
/root/mysql-8.0.18/storage/innobase/lock/lock0lock.cc:1359:9: error: ‘os_compare_and_swap_thread_id’ was not declared in this scope; did you mean ‘os_compare_and_swap_uint64’
解决办法：替换源文件lock0lock.cc中os_compare_and_swap_thread_id为os_compare_and_swap_lint
b.
/root/mysql-8.0.18/storage/innobase/trx/trx0trx.cc: In function ‘void trx_init(trx_t*)’:
/root/mysql-8.0.18/storage/innobase/trx/trx0trx.cc:222:5: error: ‘os_compare_and_swap_thread_id’ was not declared in this scope; did you mean ‘os_compare_and_swap_lint’?
222 | os_compare_and_swap_thread_id(&trx->killed_by, thread_id, 0);
| ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| os_compare_and_swap_lint
/root/mysql-8.0.18/storage/innobase/trx/trx0trx.cc: In function ‘void trx_kill_blocking(trx_t*)’:
/root/mysql-8.0.18/storage/innobase/trx/trx0trx.cc:3230:5: error: ‘os_compare_and_swap_thread_id’ was not declared in this scope; did you mean ‘os_compare_and_swap_lint’?
3230 | os_compare_and_swap_thread_id(&victim_trx->killed_by, thread_id, 0);
解决办法：
修改storage/innobase/trx/trx0trx.cc中替换os_compare_and_swap_thread_id为os_compare_and_swap_lint，注意有两处修改
8、初始化失败
2019-12-02T11:31:16.776210+08:00 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2019-12-02T11:31:16.776500+08:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-12-02T11:31:16.776570+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2019-12-02T11:31:16.777890+08:00 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.18) Kenyon's MySQL 8.0.18
解决办法：
在以上问题都处理掉后，重新cmake、make和make install
四、总结
1、mysql8版本源码安装较以前的7版本要求更高，尤其是编译器的要求更高，编译时间也更长
2、以前低版本（5.7以前）可以定制化各种引擎和初始化参数，新版本已经默认安装，不再需要编译该类参数
3、官方目前不推荐进行源码编译，从时间成本上来看，建议还是选择rpm安装，也可以自己编译完成后打包rpm自行安装
4、目前发现的一个坑是想设置大小写敏感需要在初始化数据库时就要指定（initialize --lower-case-table-names=1）,否则只能导出重新初始化
5、初始化完成后密码会打印在屏幕日志末尾，首次登陆必须先改密码才能使用
五、参考
https://www.twblogs.net/a/5d7351bebd9eee5327ff7d28/zh-cn
http://www.zhongweicheng.com/?p=2503
https://www.mysql.com
https://www.cnblogs.com/NanZhiHan/p/11017158.html
------------------------------------------------------------------------------------------------------------------------------------
--error:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
解决：
出现次错误时，也可以tcp方式连接mysql：mysql -uroot -poracle
mysql8.0.12用的配置文件为：/data/mysqldata/3306/my.cnf，socket方式连接mysql时会用到/etc/my.cnf
vi /etc/my.cnf 增加
[client]
port=3306
#socket=/var/mysql/mysql.sock
socket=/data/mysqldata/3306/mysql.sock
