mysql 初始化安装脚本

#!/bin/bash
##step 1 创建目录

mkdir -p /u01/my3306/data
mkdir -p /u01/my3306/run
mkdir -p /u01/my3306/log/binlog
mkdir -p /u01/my3306/log/iblog
mkdir -p /u01/my3306/tmp
mkdir -p /u01/my3306/share/mysql
mkdir -p /u01/my3306/log/undolog

yum remove -y mariadb-libs-1:5.5.41-2.el7_0.x86_64

##step 2 解压安装包
tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

##更改权限
chown -R mysql. /u01
chmod -R 775 /u01

##step 3 准备参数文件

cat > /etc/my.cnf <<EOF
[mysql]

CLIENT

port = 3306
socket = /u01/my3306/run/mysql.sock
#pid_file=/u01/my3306/run/mysqld.pid

[mysqld]

GENERAL

port = 3306
report_host = 192.168.30.140
datadir=/u01/my3306/data
#pid_file=/u01/my3306/run/mysqld.pid
socket=/u01/my3306/run/mysql.sock
tmpdir=/u01/my3306/tmp

##GTID##
gtid_mode = ON
enforce_gtid_consistency = ON

##SLAVE PARALLEL##
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 2

LOG

log-queries-not-using-indexes
log-slow-admin-statements
slow-query-log = 1
long_query_time = 3
binlog_format = row
log_bin=/u01/my3306/log/binlog/binlog
expire_logs_days = 7
relay_log_recovery = 1
log_error=/u01/my3306/log/error.log
slow_query_log_file=/u01/my3306/log/slow.log
relay_log=/u01/my3306/log/relaylog
relay_log_index=/u01/my3306/log/relay.index
relay_log_info_file=/u01/my3306/log/relay-log.info

CACHE AND LIMIT##

tmp_table_size = 256M
max_heap_table_size = 256M
read_buffer_size = 512K
sort_buffer_size = 512K
max_connections = 1000
max_connect_errors = 100000
open-files-limit = 65535
table_open_cache = 10240
query-cache-type = 0
query-cache-size = 0

INNODB

innodb_data_home_dir=/u01/my3306/log/iblog
innodb_log_group_home_dir=/u01/my3306/log/iblog
innodb-log-files-in-group = 2
innodb-file-per-table = 1
innodb-log-file-size = 512M
innodb_buffer_pool_size = 1G
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT
sql_mode = ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’
sync_binlog = 1

MYISAM

key_buffer_size = 50M

SAFETY

skip-name-resolve
skip-symbolic-links
skip-external-locking
max_allowed_packet = 16M
wait_timeout = 3600
interactive_timeout = 3600
binlog_cache_size = 4M

CHATSET

character_set_server = utf8mb4

server-id = 20
log_slave_updates = 1
skip-slave-start = 1

MySQL5.7

explicit-defaults-for-timestamp
log_timestamps = SYSTEM

master_info_repository = TABLE
relay_log_info_repository = TABLE

innodb_undo_tablespaces=3 #设置为3个
innodb_undo_logs=128 #默认128个
innodb_undo_directory =/data/my3306/undologs

EOF

##step 4.初始化数据库

##root 身份运行 mysql 服务，初始化
mysqld --initialize --user=mysql

##1 error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
##解决方法：yum install -y libaio
##2 TIMESTAMP with implicit DEFAULT value is deprec
##解决方法：在/etc/my.cnf [mysqld]下添加 explicit_defaults_for_timestamp=true
##3 initialize specified but the data directory has files in it. Aborting.
##解决方法：rm -fr /var/lib/mysql 。

##step 5.启动数据库并修改密码
systemctl start mysqld.service

##获取mysql自动生成的密码：
##命令：cat /u01/my3306/log/error.log |grep password

password=cat /u01/my3306/log/error.log |grep "password is generated for root@localhos"|awk -F ' ' '{print $11}'|awk 'END {print}'

##修改初始密码

mysql -uroot --connect-expired-password -p"$password" -e “alter user root@localhost identified by ‘12wsxCDE#’;”