mysql8.0单机版安装

mysql8.0单机版安装

1.系统环境配置

1.1 关闭防火墙、selinux

systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalld
sed -i "s#SELINUX=enforcing#SELINUX=disabled#g" /etc/selinux/config
setenforce 0
getenforce

1.2 配置hosts（根据实际情况编辑）

echo "192.168.102.36 test36" >>  /etc/hosts

1.3 配置limits

cat>>/etc/security/limits.conf <<EOF
mysql   soft   nofile    655360
mysql   hard   nofile    655360
mysql   soft   stack     10240
mysql   hard   stack     10240
mysql   soft   nproc     65536
mysql   hard   nproc     65536
EOF

1.4 调整系统内核参数(根据实际环境配置)

#kernel.shmmax ：
#内存为 4G 时，该值为 4*1024*1024*1024-1 =  4294967295
#内存为 8G 时，该值为 8*1024*1024*1024-1 =  8589934591
#内存为 12G 时，该值为 12*1024*1024*1024-1 = 12884901887
#内存为 16G 时，该值为 16*1024*1024*1024-1 = 17179869183
#内存为 32G 时，该值为 32*1024*1024*1024-1 = 34359738367
#内存为 64G 时，该值为 64*1024*1024*1024-1 = 68719476735
#内存为 128G 时，该值为 128*1024*1024*1024-1 = 137438953471
#kernel.shmall ：
#当内存为   4G 时， 4*1024*1024*1024/4096 = 1048576
#当内存为   8G 时， 8*1024*1024*1024/4096 = 2097152
#当内存为 12G 时， 12*1024*1024*1024/4096 = 3145728
#当内存为 16G 时， 16*1024*1024*1024/4096 = 4194304
#当内次为 32G 时， 32*1024*1024*1024/4096 = 8388608
#当内存为 64G 时， 64*1024*1024*1024/4096 = 16777216
#当内存为 128G 时， kernel.shmall = 33554432
cat >>/etc/sysctl.conf<<EOF
fs.aio-max-nr = 4194304
fs.file-max = 6815744
kernel.shmall = 1048576
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 10000 40960000 10000 4096
net.ipv4.ip_local_port_range = 20000 65535
vm.swappiness=1     
EOF


sysctl -p

1.5 创建mysql相关目录

mkdir -p /home/db/mysql/product
mkdir -p /home/db/mysql/scripts
mkdir -p /home/db/mysql/data
mkdir -p /home/db/mysql/binlog
mkdir -p /home/db/mysql/tmp
mkdir -p /home/db/mysql/backup
mkdir -p /home/db/mysql/logs

1.6 创建mysql组和用户

/usr/sbin/groupadd -g 320 mysql
/usr/sbin/useradd -u 321 -g 320 -M -d /home/db/mysql mysql
cp -r /etc/skel/.bashrc /home/db/mysql
chown  -R mysql:mysql  /home/db/mysql
passwd mysql

1.7 配置mysql环境变量

vi /home/db/mysql/.bash_profile
##添加以下内容
export MYSQL_HOME=/home/db/mysql/product
export PATH=$PATH:/home/db/mysql/product/bin:/home/db/mysql/product/lib
export PS1='$LOGNAME@'`hostname`:'$PWD''$ '
if [ -t 0 ]; then
stty intr ^C
fi


source /home/db/mysql/.bash_profile

2.安装mysql

2.1 解压安装包

cd /home/backupfile/db
chown mysql:mysql mysql-8.0.28-el7-x86_64.tar.gz
su - mysql
cd /home/backupfile/db
tar -zxvf mysql-8.0.28-el7-x86_64.tar.gz --strip-components 1 -C  /home/db/mysql/product

2.2 编辑配置文件

vi /home/db/mysql/product/my.cnf
################my.cnf配置文件开始####################
[client]
port = 13306
socket = /home/db/mysql/product/mysql.sock
[mysql]
init_command = set names utf8mb4
port = 13306
socket = /home/db/mysql/product/mysql.sock
prompt= \\u@<本机hostname>\\R:\\m:\\s [\d]>       #hostname is the server hostname
[mysqld]
server_id = 10   #denpend on server_id rule (slave:20/30/40)
port = 13306
user = mysql
basedir = /home/db/mysql/product
datadir = /home/db/mysql/data
tmpdir = /home/db/mysql/tmp
socket = /home/db/mysql/product/mysql.sock
character_set_server=utf8mb4
collation_server= utf8mb4_bin
lower_case_table_names=1
pid-file=/home/db/mysql/product/mysqld.pid
log_timestamps=SYSTEM
#report_host=<本机生产IP>
max_connect_errors=18446744073709551615
local_infile=ON
disabled_storage_engines='MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY'
skip_name_resolve=ON
############# binlog #############
log_bin=/home/db/mysql/binlog/log-bin
binlog_cache_size=2M
binlog_expire_logs_seconds =864000
binlog_rows_query_log_events=1
binlog_transaction_dependency_tracking=WRITESET
############# replication #############
#master_info_repository=table
#relay_log_info_repository=table
#relay_log=/home/db/mysql/binlog/relay/master-relay-bin
#relay_log_recovery = ON
#gtid_mode = ON
#enforce_gtid_consistency=ON
#log_slave_updates=ON
#slave_parallel_type=LOGICAL_CLOCK
#slave_preserve_commit_order=1
#slave_parallel_workers=32
#slave_pending_jobs_size_max=128M
############# slow log #############
slow_query_log=ON
slow_query_log_file = /home/db/mysql/logs/mysql_slow.log
long_query_time = 1
############# error log #############
log_error =/home/db/mysql/logs/mysql_error.log
############# thread #############
max_connections = 4000        #dependent on machine parameter
key_buffer_size = 256M    
max_allowed_packet = 128M    
table_open_cache = 6000   
table_open_cache_instances = 4    #CPU COUNT and <64
sort_buffer_size = 8M
read_rnd_buffer_size=16M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
############# innodb #############
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_buffer_pool_size = 2G     #physical memory’s 50%
innodb_buffer_pool_instances = 4    #CPU COUNT and <64
innodb_log_file_size = 1G
innodb_log_files_in_group = 4
innodb_log_buffer_size = 32M
innodb_lock_wait_timeout = 600
innodb_print_all_deadlocks=ON
innodb_print_ddl_logs=ON
innodb_thread_concurrency = 4   #CPU COUNT and <64
innodb_flush_method=O_DIRECT
innodb_read_io_threads =32      
innodb_write_io_threads =32    
innodb_io_capacity = 20000       #HDD 800  SSD 4000--
innodb_temp_data_file_path=ibtmp1:512M:autoextend:max:32G
innodb_flush_log_at_timeout=2
log_bin_trust_function_creators=ON
transaction_isolation=READ-COMMITTED
innodb_undo_directory=/home/db/mysql/data
innodb_undo_log_truncate=ON
innodb_max_undo_log_size=2G
innodb_purge_rseg_truncate_frequency=16
innodb_page_cleaners = 4   #CPU COUNT and <64
innodb_numa_interleave=ON
innodb_online_alter_log_max_size=2G
############# other #############    
#init_file=/home/db/mysql/scripts/performance_collection
default-time-zone='+08:00'
default_authentication_plugin=mysql_native_password
#[mysqld_safe]
#malloc-lib=/usr/lib/libtcmalloc.so

################my.cnf配置文件结束####################

2.3 创建软链接，root账户执行

[root@localhost db]# ln -s /home/db/mysql/product/my.cnf /etc/my.cnf

3. 初始化

su - mysql
mysqld --initialize-insecure
exit

3.1 创建启动脚本

#root账户执行
cp /home/db/mysql/product/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
sed -i "s#lockdir='/var/lock/subsys'#lockdir='/home/db/mysql/data'#g"  /etc/init.d/mysqld
cat /etc/init.d/mysqld |grep lockdir

chmod +x /etc/rc.d/rc.local

cat >>/etc/rc.d/rc.local<<EOF
su - mysql -c "/etc/init.d/mysqld start"
EOF

cat >> /etc/profile<<EOF
export PATH=\$PATH:/home/db/mysql/product/bin:/home/db/mysql/product/lib
EOF

source /etc/profile

3.2 配置mysqld_safe

su - mysql
cp /home/db/mysql/product/bin/mysqld_safe /home/db/mysql/product/bin/mysqld_safe.bak
sed -i "s%/usr/local/mysql/data%/home/db/mysql/data%g" /home/db/mysql/product/bin/mysqld_safe
sed -i "s%/usr/local/mysql%/home/db/mysql/product%g" /home/db/mysql/product/bin/mysqld_safe

4.启动mysql服务

service mysqld start
service mysqld status

4.1修改root密码

mysql -uroot
alter user 'root'@'localhost' identified with mysql_native_password by 'rootroot';
exit

4.2创建远程登录用户

mysql -uroot -p
use mysql;
create user 'root'@'%' identified with mysql_native_password by 'root';
grant all privileges on *.* to 'root'@'%' with grant option;
exit