Centos 7.6 下Mysql8.0.16编译安装

Centos 7.6 下Mysql8.0.16编译安装

1.最小化安装系统

2.安装基础包

yum -y install ntpdate gcc gcc-c++ ncurses ncurses-devel cmake readline-devel zlib.x86_64 zlib-devel.x86_64 bison libcurl-* net-tool* tree nmap sysstat lrzsz dos2unix telnet.x86_64 nethogs iftop iotop unzip ftp.x86_64 xfs* expect vim wget psmisc openssh-client* libaio libaio1 libnuma openssl-devel bzip2

3.基础环境配置

systemctl stop firewalldsystemctl disabled firewalldsystemctl stop postfixsystemctl disable postfixsed -i 's/^SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config && cat /etc/selinux/config >> ${tmplog_dir}/base.log 2>&1setenforce 0getenforceuseradd -M -s /sbin/nologin mysqlmkdir -pv /data/{conf,dumpdir,backupsets,scripts,mysql3306/{data,tmp,logs}}

4.内核参数修改

echo "vm.swappiness=10">>/etc/sysctl.confecho "net.ipv4.tcp_tw_recycle=1">>/etc/sysctl.confecho "net.ipv4.tcp_tw_reuse=1">>/etc/sysctl.confecho "vm.dirty_background_ratio=8">>/etc/sysctl.confecho "* soft nofile 1024000" >>/etc/security/limits.confecho "* hard nofile 1024000" >>/etc/security/limits.conf

数据盘挂挂载参数
rw,noatime,nodiratime,nobarrier

5.升级gcc

mkdir /usr/local/tools && cd /usr/local/toolswget http://ftp.tsukuba.wide.ad.jp/software/gcc/releases/gcc-5.5.0/gcc-5.5.0.tar.xztar xf gcc-5.5.0.tar.xzcd gcc-5.5.0./contrib/download_prerequisitesmkdir build && cd build../configure --enable-checking=release --enable-languages=c,c++ --disable-multilibmake && make install

6.升级cmake

建议另外开一个session防止环境变量未生效

cd /usr/local/toolswget https://github.com/Kitware/CMake/releases/download/v3.14.5/cmake-3.14.5.tar.Z./bootstrapmake && make install

如果出现以下错误
ibstdc++.so.6: version `CXXABI_1.3.9' not found (required by ./destructor)
处理如下：

find / -name "libstdc++.so*"mv /usr/lib64/libstdc++.so.6.0.21 /tmp/cp /usr/local/tools/gcc-5.5.0/build/x86_64-unknown-linux-gnu/libstdc++-v3/src/.libs/libstdc++.so.6.0.21 /usr/lib64/mv /usr/lib64/libstdc++.so.6 /tmp/ln -s libstdc++.so.6.0.21 libstdc++.so.6

7.编译安装Mysql

cd /usr/local/toolstar xf  mysql-boost-8.0.16.tar.gzcd mysql-8.0.16cmake . \-DWITH_BOOST=/usr/local/tools/mysql-8.0.16/boost/boost_1_69_0 \-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DMYSQL_DATADIR=/data/mysql3306/data \-DWITHOUT_CSV_STORAGE_ENGINE=1 \-DWITHOUT_FEDERATED_STORAGE_ENGINE=1 \-DWITHOUT_ARCHIVE_STORAGE_ENGINE=1 \-DFORCE_INSOURCE_BUILD=1 \-DWITH_SSL=system \-DCMAKE_CXX_COMPILER=/usr/local/bin/g++ \-DCMAKE_C_COMPILER=/usr/local/bin/gccmake -j 8make install

8.生成配置文件

注：采用叶总的模板生成的(http://imysql.cn/my_cnf_generator)

cat /data/conf/my3306.cnf[client]port    = 3306socket  = /data/mysql3306/data/mysql.sock[mysql]prompt="\u@netdatdb01-137-32 \R:\m:\s [\d]> "no-auto-rehash[mysqld]user    = mysqlport    = 3306basedir = /usr/local/mysqldatadir = /data/mysql3306/datatmpdir = /data/mysql3306/tmpsocket  = /data/mysql3306/data/mysql.sockpid-file = netdatdb01-137-32.pidcharacter-set-server = utf8mb4skip_name_resolve = 1#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数default_time_zone = "+8:00"open_files_limit    = 65535back_log = 1024max_connections = 512max_connect_errors = 1000000table_open_cache = 1024table_definition_cache = 1024table_open_cache_instances = 64thread_stack = 512Kexternal-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_cache_size = 768interactive_timeout = 600wait_timeout = 600tmp_table_size = 32Mmax_heap_table_size = 32Mslow_query_log = 1log_timestamps = SYSTEMslow_query_log_file = /data/mysql3306/slow.loglog-error = /data/mysql3306/error.loglong_query_time = 0.1log_queries_not_using_indexes =1log_throttle_queries_not_using_indexes = 60min_examined_row_limit = 100log_slow_admin_statements = 1log_slow_slave_statements = 1server-id = 3306log-bin = /data/mysql3306/logs/mybinlogsync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1G#注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项expire_logs_days = 7master_info_repository = TABLErelay_log_info_repository = TABLEgtid_mode = onenforce_gtid_consistency = 1log_slave_updatesslave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'binlog_format = rowbinlog_checksum = 1relay_log_recovery = 1relay-log-purge = 1key_buffer_size = 32Mread_buffer_size = 8Mread_rnd_buffer_size = 4Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1lock_wait_timeout = 3600explicit_defaults_for_timestamp = 1innodb_thread_concurrency = 0innodb_sync_spin_loops = 100innodb_spin_wait_delay = 30transaction_isolation = REPEATABLE-READ#innodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 2867Minnodb_buffer_pool_instances = 4innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = ibdata1:1G:autoextendinnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 2innodb_max_undo_log_size = 4Ginnodb_undo_directory = /data/mysql3306/data/undologinnodb_undo_tablespaces = 95# 根据您的服务器IOPS能力适当调整# 一般配普通SSD盘的话，可以调整到 10000 - 20000# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_flush_sync = 0innodb_flush_neighbors = 0innodb_write_io_threads = 8innodb_read_io_threads = 8innodb_purge_threads = 4innodb_page_cleaners = 4innodb_open_files = 65535innodb_max_dirty_pages_pct = 50innodb_flush_method = O_DIRECTinnodb_lru_scan_depth = 4000innodb_checksum_algorithm = crc32innodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_file_per_table = 1innodb_online_alter_log_max_size = 4Ginnodb_stats_on_metadata = 0#注意：MySQL 8.0.16开始删除该选项#internal_tmp_disk_storage_engine = InnoDB# some var for MySQL 8log_error_verbosity = 3innodb_print_ddl_logs = 1binlog_expire_logs_seconds = 604800#innodb_dedicated_server = 0innodb_status_file = 1#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快innodb_status_output = 0innodb_status_output_locks = 0#performance_schemaperformance_schema = 1performance_schema_instrument = '%memory%=on'performance_schema_instrument = '%lock%=on'#innodb monitorinnodb_monitor_enable="module_innodb"innodb_monitor_enable="module_server"innodb_monitor_enable="module_dml"innodb_monitor_enable="module_ddl"innodb_monitor_enable="module_trx"innodb_monitor_enable="module_os"innodb_monitor_enable="module_purge"innodb_monitor_enable="module_log"innodb_monitor_enable="module_lock"innodb_monitor_enable="module_buffer"innodb_monitor_enable="module_index"innodb_monitor_enable="module_ibuf_system"innodb_monitor_enable="module_buffer_page"innodb_monitor_enable="module_adaptive_hash"[mysqldump]quickmax_allowed_packet = 32M

9.初始化数据库

mkdir -pv /data/{mysql3306/{data,logs,tmp},conf,scripts,dumpdir}/usr/local/mysql/bin/mysqld  --defaults-file=/data/conf/my3306.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3306/data

10.配置启动脚本

#!/bin/shport="3306"my_lp="3306_system"mysql_bin="/usr/local/mysql/bin/"mysql_sock="/data/mysql${port}/data/mysql.sock"config_file="/data/conf/my3306.cnf"#start Mysql Servicesfunction_start_mysql(){    if [ ! -e "$mysql_sock" ];then      printf "Starting MySQL_3306 ...\n"      ${mysql_bin}/mysqld --defaults-file=${config_file} 2>&1 > /dev/null &    else      printf "MySQL_3306 is running...\n"      exit    fi}#stop Mysql Servicesfunction_stop_mysql(){    if [ ! -e "$mysql_sock" ];then       printf "MySQL_3306 is stopped...\n"       exit    else       printf "Stoping MySQL_3306...\n"       ${mysql_bin}/mysqladmin --login-path=${my_lp} -S ${mysql_sock} shutdown   fi}#restart Mysql Servicesfunction_restart_mysql(){    printf "Restarting MySQL_3306...\n"    function_stop_mysql    sleep 2    function_start_mysql}case $1 instart)    function_start_mysql;;stop)    function_stop_mysql;;restart)    function_restart_mysql;;*)    printf "Usage: /data/scripts/mysql3306 {start|stop|restart}\n"esacchmod a+x /data/scripts/mysql3306

11.启动服务

/data/scripts/mysql3306 start

12.修改密码

mysql -uroot -p -S /data/mysql3306/data/mysql.sockalter user user() identified by "netdata";

13.设置login-path

mysql_config_editor set --login-path=3306_root -uroot -p -S /data/mysql3306/data/mysql.sock

14.设置环境变量

echo "alias mysql3306='mysql --login-path=3306_root --defaults-file=/data/conf/my3306.cnf --default-character-set=utf8mb4'">>~/.bashrcsource ~/.bashrc