1. 创建账户
2. 软件的安装目录
3. 数据库目录创建
4. 配置文件准备
5. 清理环境&安装依赖
6. 初始化MySQL
7. 检查error log 初始化是不是正常
8. 启动数据库
9. 添加环境变量
   10.修改数据库密码
   11.关机

1.1 useradd -s /sbin/nologin -M mysql
2.1 mkdir /opt/mysql
2.2 cd /opt/mysql
2.3 wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz
2.4 tar xf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz
2.5 cd /usr/local/
2.6 ln -s /opt/mysql/mysql-8.0.24-linux-glibc2.12-x86_64/ mysql
2.7 chown -R mysql:mysql mysql/
3.1 mkdir /data/mysql/3306/{data,logs,tmp} -p
3.2 cd /data/mysql/
3.3 chown -R mysql:mysql 3306/
4.1 vim /data/mysql/3306/my3306.cnf
4.2 chown mysql:mysql /data/mysql/3306/my3306.cnf
5.1 yum -y remove rpm -qa |grep mariadb
6.1 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3306/my3306.cnf --initialize-insecure

7.1 cat /data/mysql/3306/data/error.log
8.1 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3306/my3306.cnf &

9.1 cat >> /etc/profile << EOF
export PATH=/usr/local/mysql/bin:$PATH
EOF

9.2 source /etc/profile

10.1 mysql -S /tmp/mysql3306.sock -p
10.2 alter user user() identified by '123';

11.1 mysqladmin -S /tmp/mysql3306.sock -p shutdown

脚本部署

#!/bin/bash
#1. 创建账户
useradd -s /sbin/nologin -M mysql
#2. 软件的安装目录
mkdir /opt/mysql
cd /opt/mysql
if [ ! -f "(mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz" ];then
    wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz
    tar xf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz
    elif [ ! -d "mysql-8.0.24-linux-glibc2.12-x86_64" ];then
        tar xf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz
    else
    echo "already exists"
fi
cd /usr/local/
ln -s /opt/mysql/mysql-8.0.24-linux-glibc2.12-x86_64/ mysql
chown -R mysql:mysql mysql/
#3. 数据库目录创建
mkdir /data/mysql/3306/{data,logs,tmp} -p
cd /data/mysql/
chown -R mysql:mysql 3306/
#4. 配置文件准备
cat >/data/mysql/3306/my3306.cnf <<EOF
[client]
port = 3306
socket = /tmp/mysql3306.sock
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
#pager="less -i -n -s"
#tee=/opt/mysql/query.log
no-auto-rehash
[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
port = 3306
socket = /tmp/mysql3306.sock
mysqlx_port = 33060
mysqlx_socket = /tmp/mysqlx3306.sock
event_scheduler = 0
tmpdir = /data/mysql/3306/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8mb4
open_files_limit = 65535
max_connections = 1000
max_connect_errors = 100000
#lower_case_table_names =1

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
pid-file = mysql.pid
long_query_time = 1
#log-slaw-adnin-statements = 1
#log-queries-not-using-indexes= 1
log-slow-slave-statements = 1

#binlog
#binlogformat = STATEMENT
binlog_format = row
server-id = 3306
log-bin = /data/mysql/3306/logs/mysql-bin
binlog_cache_size = 1M
max_binlog_size = 256M
max_binlog_cache_size = 4M
sync_binlog = 0
#expire_logs_days = 10  #5.7版本
binlog_expire_logs_seconds = 864000
log_bin_trust_function_creators = 1

gtid-mode = on
enforce_gtid_consistency = on

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 300M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_print_all_deadlocks = on
innodb_deadlock_detect = on
innodb_lock_wait_timeout = 30


##for performance_schema
performance_schema                                         = on
performance_schema_consumer_global_instrumentation         = on  # on
performance_schema_consumer_thread_instrumentation         = on  # on
performance_schema_consumer_events_stages_current          = on  # off
performance_schema_consumer_events_stages_history          = on  # off
performance_schema_consumer_events_stages_history_long     =off   # off
performance_schema_consumer_statements_digest              = on # on
performance_schema_consumer_events_statements_current      = on # on
performance_schema_consumer_events_statements_history      = on # on
performance_schema_consumer_events_statements_history_long = off # off
performance_schema_consumer_events_waits_current           = on # off
performance_schema_consumer_events_waits_history           = on # off
performance_schema_consumer_events_waits_history_long      = off # off
performance-schema-instrument                              ='memory/%=COUNTED'
EOF

chown mysql:mysql /data/mysql/3306/my3306.cnf
#5. 清理环境&安装依赖
yum -y remove `rpm -qa |grep mariadb`
yum install libaio -y
#6. 初始化MySQL
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3306/my3306.cnf --initialize-insecure

#8. 启动数据库
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3306/my3306.cnf &
#9. 添加环境变量
cat >> /etc/profile << EOF
export PATH=/usr/local/mysql/bin:\$PATH
EOF

source /etc/profile
bash
#10.修改数据库密码
#mysql -S /tmp/mysql3306.sock -p
#alter user user() identified by '123';
#11.关机
#mysqladmin -S /tmp/mysql3306.sock -p shutdown

================================================

#!/bin/bash
mkdir /data/mysql/3307/{data,logs,tmp} -p
cd /data/mysql/
chown -R mysql:mysql 3307/

cat >/data/mysql/3307/my3307.cnf <<EOF
[client]
port = 3307
socket = /tmp/mysql3307.sock
[mysql]
prompt=”\\u@\\h [\\d]>”
#pager="less -i -n -s"
#tee=/opt/mysql/query.log
no-auto-rehash
[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/3307/data
port = 3307
socket = /tmp/mysql3307.sock
mysqlx_port = 33070
mysqlx_socket = /tmp/mysqlx3307.sock
event_scheduler = 0
tmpdir = /data/mysql/3307/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8mb4
open_files_limit = 65535
max_connections = 1000
max_connect_errors = 100000
#lower_case_table_names =1

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
pid-file = mysql.pid
long_query_time = 1
#log-slaw-adnin-statements = 1
#log-queries-not-using-indexes= 1
log-slow-slave-statements = 1

#binlog
#binlogformat = STATEMENT
binlog_format = row
server-id = 3307
log-bin = /data/mysql/3307/logs/mysql-bin
binlog_cache_size = 1M
max_binlog_size = 256M
max_binlog_cache_size = 4M
sync_binlog = 0
#expire_logs_days = 10  #5.7版本
binlog_expire_logs_seconds = 864000
log_bin_trust_function_creators = 1

gtid-mode = on
enforce_gtid_consistency = on

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 300M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_print_all_deadlocks = on
innodb_deadlock_detect = on
innodb_lock_wait_timeout = 30

##for performance_schema
performance_schema                                         = on
performance_schema_consumer_global_instrumentation         = on  # on
performance_schema_consumer_thread_instrumentation         = on  # on
performance_schema_consumer_events_stages_current          = on  # off
performance_schema_consumer_events_stages_history          = on  # off
performance_schema_consumer_events_stages_history_long     =off   # off
performance_schema_consumer_statements_digest              = on # on
performance_schema_consumer_events_statements_current      = on # on
performance_schema_consumer_events_statements_history      = on # on
performance_schema_consumer_events_statements_history_long = off # off
performance_schema_consumer_events_waits_current           = on # off
performance_schema_consumer_events_waits_history           = on # off
performance_schema_consumer_events_waits_history_long      = off # off
performance-schema-instrument                              ='memory/%=COUNTED'
EOF

chown mysql:mysql /data/mysql/3307/my3307.cnf
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my3307.cnf --initialize-insecure

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my3307.cnf &