MySQL8.0.15二进制包安装

MySQL8.0.15二进制包安装一、部署环境
二、开始安装
1.系统配置
1.1 安装需要的软件包
1.2 修改系统限制参数
1.3 修改内核参数
2.MySQL安装配置
2.1 解压安装包并建立相关链接
2.2 创建用户和相应目录与授权
2.3 编写配置文件
2.4 使用免密方式初始化数据库
2.5 首次启动和登陆数据库
3.安装后优化
3.1 数据库启动问题
3.2 数据库登陆问题
3.3 修改密码

toc

一、部署环境

二、开始安装

1.系统配置

1.1 安装需要的软件包

[root@mysql8 ~]# yum -y install make gcc-c++ cmake bison-devel ncurses-devel  readline-devel  libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz

1.2 修改系统限制参数

[root@mysql8 ~]# vim /etc/security/limits.conf
#追加以下内容
*           soft   nofile       20480
*           hard   nofile       65535
*           soft   nproc        20480
*           hard   nproc        65535

1.3 修改内核参数

[root@mysql8 ~]# cat /etc/sysctl.conf 
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
vm.swappiness=0
#增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
##减少断开连接时 ，资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
##改变本地的端口范围
net.ipv4.ip_local_port_range = 1024 65535
##允许更多的连接进入队列
net.ipv4.tcp_max_syn_backlog = 4096  
##对于只在本地使用的数据库服务器
net.ipv4.tcp_fin_timeout = 30
##端口监听队列
net.core.somaxconn=65535
##接受数据的速率
net.core.netdev_max_backlog=65535
net.core.wmem_default=87380
net.core.wmem_max=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216
---
#修改完成使生效
[root@mysql8 ~]# sysctl -p

2.MySQL安装配置

2.1 解压安装包并建立相关链接

[root@mysql8 ~]# tar -xJf mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz -C /opt/
[root@mysql8 ~]# ln -s /opt/mysql-8.0.15-linux-glibc2.12-x86_64 /usr/local/mysql

2.2 创建用户和相应目录与授权

[root@mysql8 ~]# groupadd mysql
[root@mysql8 ~]# useradd -g mysql mysql -d /home/mysql -s /sbin/nologin
[root@mysql8 ~]# mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}
[root@mysql8 ~]# chown -R mysql.mysql /data/mysql/mysql_3306/

2.3 编写配置文件

这里通常会在/etc下自动生成一个my.cnf但是我们的需求可能满足不了所以需要自己编写。

#把自带的参数文件备份
[root@mysql8 etc]# mv my.cnf my.cnf.bak
#或者在任意位置执行
if [ -f /etc/my.cnf ]; then
    mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%m`.bak
fi
#编写自定义配置文件
[root@mysql8 ~]cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"
[client]
port    = 3306
socket    = /data/mysql/mysql_3306/tmp/mysql_3306.sock

[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
admin_address = 127.0.0.1
basedir    = /usr/local/mysql
datadir    = /data/mysql/mysql_3306/data
socket    = /data/mysql/mysql_3306/tmp/mysql_3306.sock
pid-file = mysql_3306.pid
character-set-server = utf8mb4
skip_name_resolve = 1


#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

# Two-Master configure
#server-1 
#auto-increment-offset = 1
#auto-increment-increment = 2 

#server-2                          
#auto-increment-offset = 2
#auto-increment-increment = 2


# semi sync replication settings #
#plugin_dir = /usr/local/mysql/lib/mysql/plugin
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

open_files_limit    = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1536
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log
log-error = /data/mysql/mysql_3306/logs/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /data/mysql/mysql_3306/logs/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_expire_logs_seconds=2592000 
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_row_image=FULL
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

#transaction_isolation = REPEATABLE-READ
transaction_isolation = READ-COMMITTED
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2867M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/mysql_3306/undolog
innodb_undo_tablespaces = 95

# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0

# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 2592000
#innodb_dedicated_server = 0

innodb_status_file = 1
# 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_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]
quick
max_allowed_packet = 32M

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535

EOF

2.4 使用免密方式初始化数据库

[root@mysql8 ~]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --initialize-insecure  --user=mysql &
#错误日志位置
[root@mysql8 ~]# tail -100f /data/mysql/mysql_3306/logs/error.log

2.5 首次启动和登陆数据库

#启动
[root@mysql8 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &
#登陆
[root@mysql8 ~]# /usr/local/mysql/bin/mysql --socket=/data/mysql/mysql_3306/tmp/mysql_3306.
sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3.安装后优化

3.1 数据库启动问题

因为是我们自己配置的参数文件所以启动时需要进行参数文件指定，但是很显然这样会很麻烦，所以我们把它加入到systemctl启动的服务中具体操作如下：

[root@mysql8 ~]# vim /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server 8.0.15
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  #这里就是我们第一次启动的命令
LimitNOFILE = 5000

保存退出后，测试一下

#启动
[root@mysql8 ~]# systemctl start mysqld
[root@mysql8 ~]# ps -ef | grep mysqld
mysql     29476      1  0 15:53 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf
mysql     31100  29476 10 15:53 ?        00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/mysql/mysql_3306/logs/error.log --open-files-limit=65535 --pid-file=mysql_3306.pid --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock --port=3306
root      31165  22391  0 15:53 pts/2    00:00:00 grep --color=auto mysqld
#关闭
[root@mysql8 ~]# systemctl stop mysqld
[root@mysql8 ~]# ps -ef | grep mysqld
root      31184  22391  0 15:54 pts/2    00:00:00 grep --color=auto mysqld
#设置开机自启
[root@mysql8 ~]# systemctl enable mysqld

3.2 数据库登陆问题

首先把命令导入到/usr/bin下

[root@mysql8 bin]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@mysql8 ~]# source/etc/profile

上面把启动问题解决但是登陆问题还在，我们用的是自己的配置文件，如果按原来登陆的方式登陆会出现找不到socket文件的错误

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql_3306/tmp/mysql_3306.sock' (2)

把mysql命令设置别名中指定套接字文件位置

[root@mysql8 ~]# vim .bash_profile
#追加
alias mysql="/usr/local/mysql/bin/mysql  --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock"
[root@mysql8 ~]# source .bash_profile
[root@mysql8 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3.3 修改密码

mysql> alter user user() identified by '1234';
Query OK, 0 rows affected (0.00 sec)
#登陆测试
[root@mysql8 ~]# mysql -u root -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

配置及优化完成