自动部署 mysql_MySQL5.7自动部署脚本（持续更新）

脚本里面没有把同步时间写进去，这个写在最前面

yum install -y ntp ntpdate

cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

执行下列语句之前，请详细了解ntp和ntpdate的区别，由于我这里是测试环境，所以ntpdate并无问题，正式环境请慎用ntpdate

(ntp是逐渐校正时间，ntpdate是跳跃式的直接修改时间，可能会对应用造成不良影响)

(另外，在虚拟机上测试时，ntpd不是太明显)

service ntpdate start

/sbin/chkconfig --add ntpd

/sbin/chkconfig --level 234 ntpd on

在阿里CentOS7环境下，会存在缺少libaio.so.1的报错

yum install -y libaio

脚本正文

#!/bin/bash

#      FlyDido

#      V1.0    2018.6.4

#      部署MySQL5.7，请提前为数据存储路径"/data"划分好空间

#      V1.1    2018.9.11

#      将V1.0版本的固定下载部署5.7.22版本更新为自动识别5.7最新版本

#关闭SeLinux

sed -i 's/enforcing/disabled/g' /etc/sysconfig/selinux

setenforce 0

#用户及路径创建

groupadd mysql

useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql

mkdir /data/mysql/mysql3306/{data,tmp,logs} -p

mkdir /opt/mysql -p

#部署前置环境

yum install -y numactl vim wget &> /tmp/install.log &&

#获取mysql5.7官网最新版本号

wget https://dev.mysql.com/downloads/mysql/5.7.html#downloads &>> /tmp/install.log &&

#将新版本号写入变量

#注意这里是截取的277行的版本号，但是5.7.html有时候会有变化，所以如果更新之后版本号不在277行需要修改成对应的行号

M=`cat 5.7.html |grep '

MySQL Community Server'|awk '{print $4}'`

echo -e "当前版本号为$M\n"

N=mysql-$M-linux-glibc2.12-x86_64

#删除wget下来的网页文件

rm -rf 5.7.html

#开始下载MySQL，如果下载失败，请检查官网版本是否为脚本中版本

echo -e "开始下载$M\n如果下载失败，请登陆：https://dev.mysql.com/downloads/mysql/5.7.html#download确认当前可用版本\n并手动将脚本中第39行的M值改写成可下载版本的版本号(格式范例:5.7.23)"

echo -e "3\n"

sleep 1

echo -e "2\n"

sleep 1

echo -e "1\n"

sleep 1

echo -e "Ready,Go!\n"

sleep 1

cd /opt/mysql

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/$N.tar.gz

#下载情况判定

if [ $? == 0 ];then

echo -e "下载完成\n正在解压\n..."

else

echo -e "下载失败，请检查版本\n或者是检查脚本中的那句cat 5.7.html |sed -n 277p...，确定版本号是否在277行，并修改成相应的行号\n"

exit 1

fi

tar -xf $N.tar.gz

echo -e "解压完成\n"

#制作软链接

ln -s /opt/mysql/$N /usr/local/mysql

#更改权限

chown -R mysql.mysql /data

chown -R mysql.mysql /opt/mysql

chown -R mysql.mysql /usr/local/mysql

#定义server-id相关因子

echo -e "开始定义server-id\n请确认当前IP是否使用eth0，如果不是请将脚本中IP变量定义中的eth0改成当前使用的网卡\n确认端口是否为3306，如果不是请将变量PORT定义的3306改成需要的端口"

IP=`ip addr show|grep eth0|grep inet|awk '{print $2}'|awk -F '/' '{print $1}'|awk -F '.' '{print $4}'`

PORT=3306

echo -e "server-id生成完成，server-id为：$IP$PORT\n"

#生成cnf

echo -e "正在/data/mysql/mysql3306/下生成本次初始化使用的cnf\n"

cat << EOF > /data/mysql/mysql3306/my3306.cnf

[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/mysql3306/data

port = 3306

socket = /tmp/mysql3306.sock

event_scheduler = 0

tmpdir = /data/mysql/mysql3306/tmp

#timeout

interactive_timeout = 300

wait_timeout = 300

#character set

character-set-server = utf8

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

max_allowed_packet=1073741824

lower_case_table_names =1

#

explicit_defaults_for_timestamp=1

#symi replication

#rpl_semi_sync_master_enabled=1

#rpl_semi_sync_master_timeout=1000 # 1 second

#rpl_semi_sync_slave_enabled=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-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

log-slow-slave-statements = 1

log_timestamps=SYSTEM

#binlog

#binlog_format = STATEMENT

binlog_format = row

server-id = $IP$PORT

log-bin = /data/mysql/mysql3306/logs/mysql-bin

binlog_cache_size = 1M

max_binlog_size = 256M

sync_binlog = 0

expire_logs_days = 10

#procedure

log_bin_trust_function_creators=1

#GTID

gtid-mode = on

enforce-gtid-consistency=on

log-slave-updates=1

#replication

read_only=1

replicate-wild-ignore-table=mysql.%

#relay log

skip_slave_start = 1

max_relay_log_size = 128M

relay_log_purge = 1

relay_log_recovery = 1

relay-log=relay-bin

relay-log-index=relay-bin.index

#slave-skip-errors=1032,1053,1062

#skip-grant-tables=1

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 128K

join_buffer_size = 128K

thread_cache_size = 200

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_stack = 192K

tmp_table_size = 96M

key_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

#innodb

innodb_buffer_pool_size = 100M

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:100M:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 8M

innodb_log_file_size = 1G

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

loose_tokudb_cache_size=100M

loose_tokudb_directio=ON

loose_tokudb_fsync_log_period=1000

loose_tokudb_commit_sync=0

EOF

#创建一个配置在/etc下

rm -rf /etc/my.cnf

cp -f /data/mysql/mysql3306/my3306.cnf /etc/my.cnf

echo -e "开始执行初始化\n"

#初始化

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize &> /tmp/install.log

if [ $? == 0 ];then

echo -e "初始化成功\n"

else

echo -e "初始化失败\n"

exit 1

fi

#更新环境变量

echo -e "export PATH=\$PATH:/usr/local/mysql/bin" >> /etc/profile

echo -e "开始启动MySQL服务\n"

#启动MySQL服务

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &

if [ $? == 0 ];then

echo -e "MySQL启动成功\n"

else

echo -e "启动失败，请检查日志/data/mysql/mysql3306/data/error.log\n"

exit 1

fi

PD=`cat /data/mysql/mysql3306/data/error.log |grep 'temporary password'|awk '{print $NF}'`

#后续需要的操作

echo -e "仔细阅读接下来你需要做的操作\n"

echo -e "1.环境变量设置完毕，请手动执行：source /etc/profile\n"

echo -e "2.初始密码为$PD\n在完成第1步的基础上执行mysql -uroot -p'$PD'进行登陆\n登陆后执行：alter user user() identified by '密码';\n注意密码需要打上引号，初次登陆必须更改密码\n"

echo -e "3.日志路径：/data/mysql/mysql3306/logs\n数据存储路径：/data/mysql/mysql3306/data\nMySQL部署路径：/usr/local/mysql\n"

echo -e "4.初始log_file_size设置为1G\n日志组设置为3\n初始buffer_pool_size设置为100M\n请执行：sed -i 's/innodb_buffer_pool_size = 100M/innodb_buffer_pool_size = 需要的大小/g' /data/mysql/mysql3306/my3306.cnf 进行修改\n"

echo -e "5.numa并没有关闭，请参照度娘/谷哥对numa进行设置\n"

echo -e "6.执行cat /data/mysql/mysql3306/my3306.cnf |grep server-id，如果结果是3306，表明网卡不是eth0，手动更改该server-id确保不会影响主从配置\n"