MySQL8.0安装脚本-最终版-最佳实践
初始化脚本如下
#!/bin/bash
export host_ip=192.168.7.999
export password=123456
echo '1.取master主机ip的后三位作为master的server_id'
export host_server_id=`echo $host_ip |awk -F . '{print $4}'`
echo 'host_server_id is' $host_server_id
echo '2.屏蔽当前的MySQL环境'
service mysql stop
rm -f /etc/my.cnf
echo '3.检查并卸载系统中低版本的mysql'
#rpm -e mariadb-libs --nodeps
echo '4.添加用户和组'
rm -rf /home/mysql/
userdel mysql
#groupdel mysql
groupadd mysql
useradd -g mysql mysql
echo '5.创建目录及授权'
rm -rf /data
mkdir -p /data/mysql
mkdir -p /data/my3306/data
mkdir -p /data/my3306/log/iblog
mkdir -p /data/my3306/log/binlog
mkdir -p /data/my3306/log/relaylog
mkdir -p /data/my3306/run
mkdir -p /data/my3306/tmp
chown -R mysql:mysql /data/my3306
chmod -R 755 /data/my3306
echo '6.上传解包软件包'
cd /softwares
[ -d "mysql-8.0.17-linux-glibc2.12-x86_64" ] || tar xvJf mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz && echo 'file exists!'
cd /softwares/mysql-8.0.17-linux-glibc2.12-x86_64
cp -R * /data/mysql/
chown -R mysql:mysql /data/
echo '7.修改配置文件my.cnf参数'
#手动上传写好的my.cnf配置文件
cp /scripts/my.cnf /etc/
##cp /data/mysql/support-files/my-default.cnf /data/mysql/my.cnf
chown -R mysql:mysql /data/my3306
cd /data/my3306/data
rm -rf *
cd /data/my3306/log
rm -f binlog/*
rm -f iblog/*
rm -rf relaylog/
rm -rf slow.log
#sed -i 's/server\_id\=128/server\_id\=222/g' /scripts/my.cnf
sed -i "s/server_id=666/server_id=$host_server_id/g" /scripts/my.cnf
echo ' File /script/my.cnf server_id is'
cat /scripts/my.cnf |grep server_id
echo '8.执行MySQL安装脚本'
cd /data/mysql/
bin/mysqld --initialize-insecure --user=mysql --datadir=/data/my3306/data #必须到脚本的父目录执行哦
#./scripts/mysql_install_db --defaults-file=/data/mysql/my.cnf --user=mysql --datadir=/data/my3306/data
echo '#9.取master主机ip的后三位作为master的server_id'
export host_server_id=`echo $host_ip |awk -F . '{print $4}'`
echo The variables server_id is $host_server_id
echo ' File my.cnf server_id is'
cat /etc/my.cnf |grep server_id
echo '10.修改系统环境变量'
echo "PATH=/data/mysql/bin:\$PATH:/sbin" >> /etc/profile
source /etc/profile
echo '11.自启动脚本'
cp /scripts/my.cnf /etc/my.cnf
cp /data/mysql/support-files/mysql.server /etc/init.d/mysql
sed -i 's#^basedir=#basedir=/data/mysql#g' /etc/init.d/mysql
sed -i 's#^datadir=#datadir=/data/mysql/data#g' /etc/init.d/mysql
sed -i 's#^mysqld_pid_file_path=#mysqld_pid_file_path=/data/my3306/run/mysqld.pid#g' /etc/init.d/mysql
chkconfig --add mysql
/etc/init.d/mysql start
/etc/init.d/mysql stop
service mysql start
echo '12.删除默认匿名账户'
#mysql -h127.0.0.1 -uroot -P3306 -e "flush privileges;"
mysql -uroot -P3306 <<EOF
flush privileges;
update mysql.user set authentication_string=password("123456") where user='root';
delete from mysql.user where user='';
set old_passwords=0;
create user 'replusr'@'%' identified by '123456';
create user 'root'@'%' identified by '123456';
grant replication slave on *.* to 'replusr'@'%';
grant super on *.* to 'replusr'@'%';
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
update mysql.user set authentication_string=password("123456") where user='root';
flush privileges;
show variables like 'server_id';
EOF
service mysql restart
exit
my.cnf文件如下
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
#[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
[mysql]
# CLIENT #
port = 3306
socket =/data/my3306/run/mysql.sock
disable-auto-rehash
default-character-set=utf8
[mysqld]
# GENERAL #
lower_case_table_names=1
server_id = 128
port = 3306
user = mysql
explicit_defaults_for_timestamp=true
default-storage-engine = InnoDB
character_set_server = utf8
auto_increment_increment = 2
auto_increment_offset = 1
lower_case_table_names = 1
socket =/data/my3306/run/mysql.sock
pid_file=/data/my3306/run/mysqld.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 134217728
max_connections = 8192
max_user_connections = 8000
open_files_limit = 65535
skip-name-resolve
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sysdate-is-now = 1
# DATA STORAGE #
basedir = /data/mysql
datadir=/data/my3306/data/
tmpdir=/data/my3306/tmp/
# BINARY LOGGING #
log-bin=/data/my3306/log/binlog/log-bin
log-bin-index=/data/my3306/log/binlog/log-bin.index
expire-logs-days = 15
sync-binlog = 1
binlog_format = ROW
#RELAY LOGGING
relay-log=/data/my3306/log/relaylog/slave-relay-bin
relay-log-index=/data/my3306/log/relaylog/slave-relay-bin.index
sync_relay_log=1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
#query-cache-type = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# INNODB #
innodb_log_group_home_dir=/data/my3306/log/iblog
innodb_data_home_dir=/data/my3306/log/iblog
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 6G
# LOGGING #
general_log = off
log-error = /data/my3306/log/alert_3306.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /data/my3306/log/mysql-slow.log
log_slave_updates=ON
