20230114--Alma Linux 8 二进制安装数据库(
2023-01-14 本文已影响0人
負笈在线
依赖包安装
# dnf -y install wget cmake gcc gcc-c++ ncurses ncurses-devel libaio-devel openssl openssl-devel perl
创建mysql用户
# groupadd mysql
# useradd mysql -d /var/lib/mysql/ -g mysql
# tail -1 /etc/passwd
mysql:x:1000:1000::/var/lib/mysql/:/bin/bash
获取mysql-8.0.31安装包
# wget -c https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz
# tar -xf mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz
# mv mysql-8.0.31-linux-glibc2.12-x86_64 /usr/local/
# ln -s /usr/local/mysql-8.0.31-linux-glibc2.12-x86_64 /usr/local/mysql
数据库目录创建
# mkdir /usr/local/mysql/tmp
# mkdir -p /var/lib/mysql/{mysql_data,mysql_log,mysql_tmp}
# mkdir -p /var/lib/mysql/mysql_log/{binlog,undolog}
# mkdir -p /var/lib/mysql/backup
ENV设置
# cat <<- 'EOF' | tee -a /etc/profile.d/mysql.sh
### MYSQL ENV ###
MYSQL_HOME=/usr/local/mysql
PATH=$PATH:${MYSQL_HOME}/bin
export PATH
EOF
# source /etc/profile.d/mysql.sh
my.cnf配置
# cat <<- 'EOF' | tee -a /usr/local/mysql/my.cnf
[client]
port = 5550
socket = /usr/local/mysql/tmp/mysql.sock
default_character_set = utf8mb4
[mysqldump]
quick
max_allowed_packet = 67108864
[mysql]
no_auto_rehash
default_character_set = utf8mb4
[mysqld_safe]
log_error = /var/lib/mysql/mysql_log/error.log
[mysqld]
server_id = 12 # custom
port = 5550
socket = /usr/local/mysql/tmp/mysql.sock
pid_file = /usr/local/mysql/tmp/mysql.pid
basedir = /usr/local/mysql
datadir = /var/lib/mysql/mysql_data
tmpdir = /var/lib/mysql/mysql_tmp
replica_load_tmpdir = /var/lib/mysql/mysql_tmp
lower_case_table_names = 1
skip_name_resolve = ON # custom
skip_external_locking = ON
event_scheduler = ON
secure_file_priv = /var/lib/mysql/backup # custom
explicit_defaults_for_timestamp = 1
open_files_limit = 65535
mysqlx_port = 55550
mysqlx_socket = /usr/local/mysql/tmp/mysqlx.sock
### ------------------------------------------
### connection & session
### ------------------------------------------
# bind_address = ''
max_connections = 1000 # custom
max_user_connections = 800 # custom
max_connect_errors = 99999999
connect_timeout = 30
wait_timeout = 3600
interactive_timeout = 3600
init_connect = 'SET NAMES utf8mb4'
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
replica_net_timeout = 900
net_read_timeout = 30
net_write_timeout = 60
net_buffer_length = 8192
max_execution_time = 3600000 # custom: (ms)
thread_cache_size = 768
# mysqlx_bind_address = ''
mysqlx_max_connections = 800 # custom
### ------------------------------------------
### log
### ------------------------------------------
log_timestamps = system
log_error_verbosity = 2
log_error = /var/lib/mysql/mysql_log/error.log
log_bin = /var/lib/mysql/mysql_log/binlog/binlog
log_bin_index = /var/lib/mysql/mysql_log/binlog/binlog.index
max_binlog_size = 134217728
binlog_expire_logs_seconds = 604800 # custom: default(7days) /s
binlog_cache_size = 4194304
binlog_format = ROW
binlog_row_image = minimal
sync_binlog = 1
slow_query_log = ON # custom
long_query_time = 1 # custom
# log_queries_not_using_indexes = ON
# log_throttle_queries_not_using_indexes = 60
slow_query_log_file = /var/lib/mysql/mysql_log/slow.log
relay_log = /var/lib/mysql/mysql_log/relaylog
relay_log_index = /var/lib/mysql/mysql_log/relaylog.index
max_relay_log_size = 536870912
innodb_undo_directory = /var/lib/mysql/mysql_log/undolog
#innodb_undo_tablespaces_total = 4
#innodb_undo_tablespaces_implicit = 2 # innodb-created
#innodb_undo_tablespaces_explicit = 2 # user-created
#innodb_undo_tablespaces_active = 4
### ------------------------------------------
### innodb
### ------------------------------------------
innodb_file_per_table = 1
innodb_open_files = 60000
innodb_buffer_pool_size = 6012954214 # custom(mem * 70%)
innodb_log_file_size = 536870912 # custom(innodb_buffer_pool_size * 25%)
innodb_data_file_path = ibdata1:1024M:autoextend # custom
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 8388608
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_write_io_threads = 4 # custom: cpu core
innodb_read_io_threads = 4 # custom: cpu core
innodb_thread_concurrency = 0
innodb_purge_rseg_truncate_frequency = 128
innodb_page_size = 16384
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 100
innodb_autoinc_lock_mode = 2 # only for [binlog_format]=row
### ------------------------------------------
### table & query
### ------------------------------------------
transaction_isolation = REPEATABLE-READ # READ-COMMITTED
key_buffer_size = 67108864
max_allowed_packet = 67108864
table_open_cache = 512
sort_buffer_size = 2097152 # custom: memeory < 16GB, suggest to set 2M
read_rnd_buffer_size = 2097152 # custom: memeory < 16GB, suggest to set 2M
read_buffer_size = 2097152 # custom: memeory < 16GB, suggest to set 2M
join_buffer_size = 2097152 # custom: memeory < 16GB, suggest to set 2M
tmp_table_size = 67108864
max_heap_table_size = 8388608
bulk_insert_buffer_size = 16777216
group_concat_max_len = 1048576
### ------------------------------------------
### replication
### ------------------------------------------
log_replica_updates = 1
skip_replica_start = ON
gtid_mode = ON
enforce_gtid_consistency = ON
sync_source_info = 10000
relay_log_purge = 1
# replicate_wild_ignore_table = configdb.% # custom: only for slave
# replicate_wild_ignore_table = mysql.% # custom: only for slave
# relay_log_recovery = ON # custom: only for slave
# read_only = ON # custom: only for slave
EOF
修改目录权限
# chown -R mysql:mysql /usr/local/mysql-8.0.31-linux-glibc2.12-x86_64/
# chown -R mysql:mysql /usr/local/mysql
# chown -R mysql:mysql /var/lib/mysql/
数据库初始化
# mysqld --defaults-file=/usr/local/mysql/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysql/mysql_data
创建启动文件
# cat <<- 'EOF' | tee -a /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Service]
Type=forking
User=mysql
Group=mysql
PIDFile=/usr/local/mysql/tmp/mysql.pid
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --daemonize
ExecStartPost=/usr/bin/sleep 1
Restart=on-failure
ExecStop=/usr/bin/kill -s QUIT $MAINPID
TimeoutSec=0
LimitNOFILE=65535
RestartPreventExitStatus=1
PermissionsStartOnly=true
PrivateTmp=false
[Install]
WantedBy=multi-user.target
EOF
启动mysql
# systemctl daemon-reload
# systemctl start mysqld
# systemctl enable mysqld
# systemctl status mysqld
查看数据库初始化密码
# cat /var/lib/mysql/mysql_log/error.log | grep localhost
2023-01-13T22:10:15.295310+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: VR/yjfEhS24T
确认数据库版本
# mysqladmin --version
mysqladmin Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
修改mysql数据库root密码
# ln -s /usr/local/mysql/tmp/mysql.sock /tmp/mysql.sock
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
开启开启mysql的远程访问
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
错误解决
1）mysql登录报错mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
登录报错
# mysql -uroot -p
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
解决办法：
# ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
2）mysql登录报错ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
登录报错
# mysql -hlocalhost -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
解决办法：
# ln -s /usr/local/mysql/tmp/mysql.sock /tmp/mysql.sock
参考URL
http://www.mysqlcalculator.com/
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
