环境优化
# 关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
#查看selinux
getenforce
#关闭selinux
setenforce 0
#永久关闭selinux
sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
#清除iptables 规则
iptables -F
# 关闭NetworkManager
systemctl stop NetworkManager
systemctl disable NetworkManager
# 尽量不使用交换区,默认60
echo '0' > /proc/sys/vm/swappiness
# 永久修改 尽量不使用交换区,默认60
cat >>/etc/sysctl.conf<<'EOF'
vm.swappiness=0
vm.overcommit_memory = 1
EOF
sysctl -p
ntpdate -u ntp.aliyun.com # 时间同步
hwclock -w # 更新BIOS时间
安装常用命令及依赖
yum install -y net-tools lrzsz wget vim libaio-devel ntpdate epel-release \
tree libaio-devel lsof sysstat bash-completion bash-completion-extras jemalloc
卸载mariadb
yum remove mariadb* -y
创建MySQL软件及数据目录
mkdir /mysql/{tools,tar} -p
mkdir /mydata/3306/{binlog,data,etc,lock,log,pid,socket,tmp,script} -p
下载安装包
cd /mysql/tar
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
md5验证包的完整性
[root@ds_dnr_db01 tar]# md5sum mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
d7c8436bbf456e9a4398011a0c52bc40 mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
解压MySQL包
cd /mysql/tar
tar xf /mysql/tar/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
移动tar包到mysql目录
mv /mysql/tar/mysql-5.7.44-linux-glibc2.12-x86_64 /mysql/mysql57
配置环境变量
cat >/etc/profile.d/mysql.sh<<'EOF'
export PATH=/mysql/mysql57/bin:$PATH
EOF
#修改权限
chmod 700 /etc/profile.d/mysql.sh
#生效环境变量
source /etc/profile.d/mysql.sh
创建所需用户
groupadd mysql
useradd mysql -g mysql
添加配置文件
cat >/mydata/3306/etc/my.cnf<<'EOF'
[client]
socket=/mydata/3306/socket/mysql.sock
[mysql]
socket=/mydata/3306/socket/mysql.sock
prompt='\u@\d>\_'
default-character-set=utf8mb4
no-auto-rehash
#show-warnings #查看告警信息
[mysqld]
# GENERAL #
lower_case_table_names = 1
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
user = mysql
default_storage_engine = InnoDB
port = 3306
socket = /mydata/3306/socket/mysql.sock
pid-file = /mydata/3306/pid/mysql.pid
log_timestamps = SYSTEM
default_time_zone = +08:00
secure_file_priv = ''
local_infile = ON
thread_stack = 512K
# MyISAM #
key_buffer_size = 128M
myisam_sort_buffer_size = 8M
read_rnd_buffer_size = 262144
# SAFETY #
back_log = 1024
bind-address = 0.0.0.0
innodb_strict_mode = 1
max_allowed_packet = 32M
max_connect_errors = 1000000
interactive_timeout = 900
wait_timeout = 900
skip_grant_tables = 0
skip_name_resolve = 1
host_cache_size = 0
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sysdate_is_now = 1
transaction_isolation = REPEATABLE-READ
explicit_defaults_for_timestamp = 1
# DATA STORAGE #
basedir = /mysql/mysql57
datadir = /mydata/3306/data
tmpdir = /mydata/3306/tmp
# BINARY LOGGING #
#max_binlog_files = 100
binlog_cache_size = 1M
binlog_format = ROW
expire_logs_days = 15
max_binlog_size = 512M
max_binlog_cache_size = 4G
log_bin = /mydata/3306/binlog/mysql-bin
log_bin_index = /mydata/3306/binlog/mysql-bin.index
sync_binlog = 1
innodb_support_xa = 1
master-info-repository = TABLE
# REPLICATION #
log_slave_updates = 1
read_only = 0
relay_log = /mydata/3306/binlog/relay-bin
relay_log_index = /mydata/3306/binlog/relay-bin.index
server_id = 330651
slave_skip_errors = OFF
skip_slave_start
slave_net_timeout = 60
relay-log-info-repository = TABLE
slave_compressed_protocol = OFF #5.6版本、5.7.21+、8.0.4+可以设置为ON
# CACHES AND LIMITS #
max_connections = 2000
max_heap_table_size = 128M
tmp_table_size = 128M
open_files_limit = 65535
query_cache_limit = 1M
query_cache_min_res_unit = 4096
query_cache_size = 0
query_cache_type = 0
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M
table_definition_cache = 4096
table_open_cache = 4000
thread_cache_size = 1000
# INNODB #
innodb_autoextend_increment = 64
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 1G # 设置为自己机器的内存大小
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_fast_shutdown = 1
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_file_per_table = 1
innodb_force_recovery = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 8M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 4
innodb_open_files = 10000
innodb_purge_threads = 4
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_sync_spin_loops = 30
innodb_thread_concurrency = 24
innodb_print_all_deadlocks = 1
innodb_io_capacity = 2000
# LOGGING #
general_log = 0
general_log_file = /mydata/3306/log/mysql-general.log
log_error = /mydata/3306/log/mysql-error.log
log_queries_not_using_indexes = 0
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /mydata/3306/log/mysql-slow.log
# GTID
enforce_gtid_consistency = ON
gtid_mode = ON
binlog_gtid_simple_recovery = TRUE
# SEMI-SYNC
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_slave_enabled = ON
rpl_semi_sync_master_timeout = 3000
rpl_semi_sync_master_trace_level = 32
rpl_semi_sync_slave_trace_level = 32
rpl_semi_sync_master_wait_no_slave = ON
##parallel 配置文件参数 ##
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_group_commit_sync_delay = 500
binlog_group_commit_sync_no_delay_count = 13
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 40
slave_preserve_commit_order = 1
binlog_transaction_dependency_tracking = writeset
transaction_write_set_extraction = XXHASH64
slave_checkpoint_period = 2
binlog_checksum = CRC32
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1
EOF
将配置文件链接到 etc 下
ln -s /mydata/3306/etc/my.cnf /etc/my3306.cnf
创建error文件
touch /mydata/3306/log/mysql-error.log
授权mysql数据目录
chown -R mysql:mysql /mydata/
chmod 700 /mydata/
初始化MySQL
mysqld --initialize-insecure --user=mysql --basedir=/mysql/mysql57 --datadir=/mydata/3306/data --innodb_data_file_path=ibdata1:1024M:autoextend
配置system管理
cat >/usr/lib/systemd/system/mysqld3306.service<<'EOF'
[Unit]
Description=MySQL 3306 Server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/mysql/mysql57/bin/mysqld --defaults-file=/etc/my3306.cnf
LimitNOFILE = 65536
LimitNPROC = 65535
# 重启条件
Restart=on-failure
# 退出码为1不重启,即正常shutdown
RestartPreventExitStatus=1
# 重启条件满足后等多久自动重启(秒)
ReStartSec=10
# 五分钟内只能重启两次,第三次不重启了
StartLimitInterval=300
# 五分钟内只能重启两次,第三次不重启了
StartLimitBurst=2
# 服务启动的超时时间,单位秒
TimeoutStartSec=30
# 服务关闭的超时时间,单位秒
TimeoutStopSec=30
EOF
生效
systemctl daemon-reload
启动实例
systemctl start mysqld3306
重置启动次数
systemctl reset-failed mysqld3306.service
查看状态
systemctl status mysqld3306
设置便捷登录
cat >/usr/local/bin/3306_mysql_login.sh<<'EOF'
mysql -A -S /mydata/3306/socket/mysql.sock -p
EOF
授权
chmod 700 /usr/local/bin/3306_mysql_login.sh
修改密码
alter user root@localhost identified by '123456';
flush privileges;
##########################END###########################