MySQL 5.7 for CentOS安装（shell方式）

1 前言

　　手动安装MySQL步骤也不复杂，但是难免会出现误操作，因此把常规安装MySQL步骤写成shell脚本，进一步提升安装效率。

2 mysql.sh

#!/bin/bash
set -e
MYSQL_PASSWD="Z2OrB2#mGkNuaj5j0"
MYSQL_DATADIR=/data/mysql
MYSQL_PORT='3306'
TOTAL_MEM=`free -g | grep Mem | awk '{print $2}'`
TOTAL_MEM_PERCENT_70=`echo "($TOTAL_MEM*0.7)/1" | bc -q`
INNODB_BUFFER_POOL_SIZE=`echo $TOTAL_MEM_PERCENT_70"G"`

#操作系统参数调整
system_parameter_update() {
echo "========================================操作系统参数调整==================================="
#磁盘调度
for disk_name in `ls /sys/block/`
do
    echo noop > /sys/block/$disk_name/queue/scheduler
done

#大页
echo  never >> /sys/kernel/mm/transparent_hugepage/enabled
echo  never >> /sys/kernel/mm/transparent_hugepage/defrag

#资源池限制
cat >> /etc/security/limits.conf <<EOF
* soft nproc 655350
* hard nproc 655350
* soft nofile 655350
* hard nofile 655350
EOF

echo "fs.file-max=655350" >> /etc/sysctl.conf

#SWAP
echo "vm.swappiness=0" >> /etc/sysctl.conf 

#dirty page
echo "5" > /proc/sys/vm/dirty_background_ratio
echo "10" > /proc/sys/vm/dirty_ratio
}

#下载安装包
wget_mysql_binary() {
echo "=========================================下载MySQL安装包================================"
if read -t 60 -p "Do you download the MySQL installation package(y/n)?" WGET_VALUE
then
    if [ "$WGET_VALUE" = "y" ]
    then
        wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-el7-x86_64.tar.gz
    fi
else
    WGET_VALUE = "n"
fi
}

#删除mariadb包
drop_mariadb() {
echo "=========================================删除mariadb==================================="
mariadb_num=`rpm -qa | grep mariadb | wc -l`
if [ $mariadb_num -ne 0 ]
then
    rpm -qa | grep mariadb | xargs rpm -e --nodeps
fi
}

#安装依赖包
yum_install_depend() {
echo "==========================================安装依赖包==================================="
yum install libaio -y
}

#创建mysql用户
user_add() {
echo "=========================================创建mysql用户================================="
useradd mysql >>/dev/null
if read -t 60 -p "(Please input the mysql password(default:$MYSQL_PASSWD):" SYS_MYSQL_PASSWORD
then
    if [ "$SYS_MYSQL_PASSWORD" = "" ]; then
        SYS_MYSQL_PASSWORD="$MYSQL_PASSWD"
    fi
else
    SYS_MYSQL_PASSWORD="$MYSQL_PASSWD"
fi

echo "$SYS_MYSQL_PASSWORD" | passwd mysql --stdin
echo "export PATH=\$PATH:/usr/local/mysql/bin" >>/home/mysql/.bashrc
echo "export PATH=\$PATH:/usr/local/mysql/bin" >>/root/.bashrc
}

#配置MySQL目录
install_mysql_path() {
echo "=======================================MySQL DATADIR目录================================"
echo -e "Please input the DATADIR of MySQL:"
read -t 60 -p "(Default DATADIR:$MYSQL_DATADIR):" DATADIR
if [ "$DATADIR" = "" ]; then
    DATADIR=$MYSQL_DATADIR
fi

mkdir -p $DATADIR
chown -R mysql:mysql $DATADIR/
chmod -R 750 $DATADIR

if [ $? -eq 0 ]
then
    echo "Mkdir path Successfully."
else
    echo "Mkdir path Failed!!!"
fi
}

#生成MySQL参数文件
conf_parameter_file_1() {
echo "======================================编辑my.cnf配置文件==============================="
if [ -f /etc/mysql/my.cnf ]
then
    mv /etc/mysql/my.cnf /etc/mysql/my.cnf_$(date +%Y-%m-%d-%H:%M)    
fi

if [ -f /etc/my.cnf ]
then
    mv /etc/my.cnf /etc/my.cnf_$(date +%Y-%m-%d-%H:%M)    
fi

SERVER_ID=$(date +%s)
mkdir -p /etc/mysql/

cat >> /etc/mysql/my.cnf <<EOF
[client]
user = root
#password = $PASSWD

[mysqld]
bind_address = 0.0.0.0
port = $MYSQL_PORT
basedir = /usr/local/mysql
datadir = $DATADIR
socket = /tmp/mysql.sock
lower-case-table-names = 1

[mysql]
prompt=(\u@\h) [\d]>\_
no-auto-rehash
EOF
chown -R mysql:mysql /etc/mysql
}

#初始化MySQL
initdb_mysql() {
echo "=========================================初始化MySQL==================================="
if [ -d /usr/local/mysql ]
then
    mv /usr/local/mysql /usr/local/mysql_$(date +%Y-%m-%d-%H-%M)
fi

tar -zxf mysql-5.7.33-el7-x86_64.tar.gz -C /usr/local/
ln -s /usr/local/mysql-5.7.33-el7-x86_64 /usr/local/mysql
chown -R root:mysql /usr/local/mysql*

#初始化MySQL
su - mysql -c "mysqld --initialize-insecure --user=mysql"

if [ $? -eq 0 ]
then
    echo "MySQL initialize Successfully."
else
    echo "MySQL initialize Failed!!!"
fi

mkdir -p $DATADIR/logs/{log-bin,log-group,log-relay}
chown -R mysql:mysql $DATADIR/
chmod -R 750 $DATADIR
}

#生成MySQL参数文件
conf_parameter_file_2() {
echo "======================================编辑my.cnf配置文件==============================="
if [ -f /etc/mysql/my.cnf ]
then
    mv /etc/mysql/my.cnf /etc/mysql/my.cnf_$(date +%Y-%m-%d-%H:%M)    
fi

if [ -f /etc/my.cnf ]
then
    mv /etc/my.cnf /etc/my.cnf_$(date +%Y-%m-%d-%H:%M)    
fi

SERVER_ID=$(date +%s)
mkdir -p /etc/mysql/

cat >> /etc/mysql/my.cnf <<EOF
[client]
user = root
#password

[mysqld]
bind_address = 0.0.0.0
port = $MYSQL_PORT
basedir = /usr/local/mysql
datadir = $DATADIR
socket = /tmp/mysql.sock

#字符
character_set_server = utf8mb4
lower-case-table-names = 1

#密码策略
#plugin-load-add = validate_password.so
default_authentication_plugin=mysql_native_password

#日志设置
sync_binlog = 1
log-bin = $DATADIR/logs/log-bin/mysql-bin
binlog_format = row
binlog_row_image = full
log_bin_trust_function_creators = 1
expire_logs_days = 30

#general_log = on
general_log_file = $DATADIR/logs/general.log 

log_error = $DATADIR/logs/error.log

slow_query_log = on
slow_query_log_file = $DATADIR/logs/slow.log
long_query_time = 2
#min_examined_row_limit = 100
#log_queries_not_using_indexes = on
#log_throttle_queries_not_using_indexes = 4
log_slow_admin_statements = on
log_output = file
log_slow_slave_statements = on

relay-log = $DATADIR/logs/log-relay/relay_log
relay_log_info_file = $DATADIR/logs/log-relay/relay-log.info

#连接
max-connections = 1000
back_log = 300
max_allowed_packet = 64M
interactive_timeout = 28800
wait_timeout = 28800

#缓存
sort_buffer_size = 32M
tmp_table_size = 32M
join_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
key_buffer_size = 64M
myisam_sort_buffer_size = 64M
max_heap_table_size = 16M
open_files_limit = 65535
thread_cache_size = 512
table_open_cache = 16384
table_definition_cache = 16384
table_open_cache_instances = 8

#InnoDB
default-storage-engine = INNODB
innodb_buffer_pool_size = $INNODB_BUFFER_POOL_SIZE
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 15
innodb_flushing_avg_loops = 30
innodb_adaptive_hash_index = ON
innodb_data_home_dir = $DATADIR
innodb_file_per_table = ON
innodb_log_group_home_dir = $DATADIR/logs/log-group/
innodb_log_files_in_group = 3
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_timeout = 1
innodb_lock_wait_timeout = 30
innodb_buffer_pool_instances = 8
innodb_change_buffering = all
innodb_change_buffer_max_size = 10
innodb_autoextend_increment = 64
innodb_thread_concurrency = 32
innodb_spin_wait_delay = 6
innodb_sync_spin_loops = 30
innodb_open_files = 65535
innodb_old_blocks_time = 30
innodb_online_alter_log_max_size = 512M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_flush_neighbors = 0
innodb_io_capacity = 3000
innodb_io_capacity_max = 16000
innodb_lru_scan_depth = 1024
innodb_purge_threads = 4
innodb_purge_batch_size = 400
innodb_buffer_pool_filename = ib_buffer_pool
innodb_buffer_pool_load_abort = OFF
innodb_buffer_pool_dump_now = OFF
innodb_buffer_pool_load_now = OFF
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 0
#索引列长度
innodb_default_row_format = dynamic
innodb_file_format = Barracuda
innodb_large_prefix = ON

#Replication
server_id = $SERVER_ID
#report_host = 10.46.203.123                   #本地IP
gtid_mode = on
enforce_gtid_consistency = on
loose-plugin_load_add = 'semisync_master.so'
loose-plugin_load_add = 'semisync_slave.so'
loose-rpl_semi_sync_master_enabled              #安装插件后启用
loose-rpl_semi_sync_slave_enabled               #安装插件后启用
loose-rpl_semi_sync_master_wait_point           #安装插件后启用
loose-rpl_semi_sync_master_timeout               #安装插件后启用
skip_slave_start = off                           #启动MySQL是否跳过启动slave进程
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = on
log_slave_updates = on

[mysql]
prompt=(\u@\h) [\d]>\_
no-auto-rehash
EOF
chown -R mysql:mysql /etc/mysql

if [ $? -eq 0 ]
then
    echo "Configure my.cnf Successfully."
else
    echo "Configure my.cnf Failed!!!"
fi
}

#安装MySQL SSL
install_ssl() {
echo "========================================安装MySQL SSL=================================="
su - mysql -c "mysql_ssl_rsa_setup"
chown mysql:mysql $DATADIR/*.pem
if [ $? -eq 0 ]
then
    echo "Install MySQL SSL Successfully."
else
    echo "Install MySQL SSL Failed!!!"
fi
}

#配置MySQL启动文件
conf_mysqld_file() {
echo "===================================配置/etc/init.d/mysqld=============================="
if [ -f /etc/init.d/mysqld ]
then
    mv /etc/init.d/mysqld /etc/init.d/mysqld_$(date +%Y-%m-%d-%H:%M)
fi

cp -f /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
if [ $? -eq 0 ]
then
    echo "Configure /etc/init.d/mysqld Successfully."
else
    echo "Configure /etc/init.d/mysqld Failed!!!"
fi

chown mysql:mysql /etc/init.d/mysqld
}

#启动MySQL
start_mysql() {
echo "====================================启动MySQl==========================================="
su - mysql -c "/etc/init.d/mysqld start"
}

#配置增强复制
config_rpl_semi() {
echo "======================================配置MySQL增强复制===================================="
su - mysql -c mysql <<EOF
install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
EOF

sed -i "s/#rpl_semi_sync_master_enabled/rpl_semi_sync_master_enabled = on/g" /etc/mysql/my.cnf
sed -i "s/#rpl_semi_sync_slave_enabled/rpl_semi_sync_slave_enabled = on/g" /etc/mysql/my.cnf
sed -i "s/#rpl_semi_sync_master_wait_point/rpl_semi_sync_master_wait_point = AFTER_SYNC/g" /etc/mysql/my.cnf
sed -i "s/#rpl_semi_sync_master_timeout/rpl_semi_sync_master_timeout = 5000/g" /etc/mysql/my.cnf

su - mysql -c "/etc/init.d/mysqld restart"
}

#修改数据库root用户的密码
change_mysql_root_password() {
echo "========================================修改MySQL用户密码==================================="
echo -e "Please set the MySQL root password:"
read -t 60 -p "(The default MySQL root password is '$MYSQL_PASSWD'):" MYSQL_ROOT_PASSWORD
if [ "$MYSQL_ROOT_PASSWORD" = "" ]; then
    MYSQL_ROOT_PASSWORD="$MYSQL_PASSWD"
fi
su - mysql -c mysql <<EOF
set sql_log_bin = off;
alter user 'root'@'localhost' identified by "$MYSQL_ROOT_PASSWORD";
create user 'root'@'%' identified by "$MYSQL_ROOT_PASSWORD";
grant all privileges on *.* to 'root'@'%';
flush privileges;
set sql_log_bin = on;
EOF

if [ $? -eq 0 ]
then
    echo "Change mysql password Successfully."
else
    echo "Change mysql password Failed!!!"
fi
}

#修改my.cnf文件
change_parater_passwd() {
echo "========================================修改my.cnf文件=================================="
sed -i "s/#password/password = '$MYSQL_ROOT_PASSWORD'/g" /etc/mysql/my.cnf
}

#操作系统参数调整
system_parameter_update

#下载安装包
#wget_mysql_binary

#删除mariadb包
#drop_mariadb

#安装依赖包
yum_install_depend

#创建用户和组
user_add

#配置MySQL目录
install_mysql_path

#生成MySQL参数文件
conf_parameter_file_1

#初始化MySQL
initdb_mysql

#生成MySQL参数文件
conf_parameter_file_2

#安装MySQL SSL
install_ssl

#配置MySQL启动文件
conf_mysqld_file

#启动MySQL
start_mysql

#配置增强复制
#config_rpl_semi

#修改数据库root用户的密码
change_mysql_root_password

#修改my.cnf文件
change_parater_passwd

echo -e "操作系统用户mysql的密码是:$SYS_MYSQL_PASSWORD"
echo -e "MySQL数据库用户mysql的密码是:$MYSQL_ROOT_PASSWORD"

注释：

　根据个人需求更改脚本中的变量。　

3 执行脚本安装

# sh mysql.sh