MySQL5.7 Linux下单机部署

1.云硬盘挂载

# 查看挂盘名称
fdisk -l

# 创建物理卷
pvcreate /dev/vdb

# 创建卷组
vgcreate data_vg /dev/vdb

# 创建逻辑卷
lvcreate -l +100%free -n data data_vg

# 格式化磁盘
mkfs.xfs /dev/data_vg/data

# 创建挂载目录
mkdir -p /data1

# 挂载
mount /dev/data_vg/data /data1

# 添加挂载信息
echo "/dev/mapper/data_vg-data /data1 xfs     defaults        0 0" >> /etc/fstab

2.系统配置

# 关闭防火墙
systemctl stop firewalld

# 设置防火墙开机不启动
systemctl disable firewalld

# 配置SELINUX
sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config

# 重启
reboot

# 卸掉自带的mariadb
yum -y remove mariadb*

3.MySQL安装

# 安装MySQL
rpm -Uvh Percona-Server-client-57-5.7.34-37.1.el7.x86_64.rpm \
         Percona-Server-server-57-5.7.34-37.1.el7.x86_64.rpm \
         Percona-Server-shared-57-5.7.34-37.1.el7.x86_64.rpm \
         Percona-Server-shared-compat-57-5.7.34-37.1.el7.x86_64.rpm


# 创建数据目录
mkdir -p /data1/mysql/{data,log,binlog,iblog}
mkdir -p /data1/mysql/relay/relaylog

# 修改拥有者
chown -R mysql:mysql /data1/mysql

# 修改配置文件
cp /etc/my.cnf /etc/my.cnf-20210405
vi /etc/my.cnf

[mysqld]
user=mysql
port=3306

#每个节点的server-id不相同，master为1，slave为2...
server-id=1

#directory
ignore-db-dir=lost+found
datadir=/data1/mysql/data
socket=/data1/mysql/mysql.sock
pid_file=/data1/mysql/mysqld.pid
#global
bind_address = 0.0.0.0
lower_case_table_names=1
#validate_password_policy=0
default_storage_engine=InnoDB
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
open_files_limit=20000
connect_timeout=15
query_cache_size = 0
query_cache_type = 0
back_log=2000
connect_timeout=15
skip_name_resolve=ON
max_connections=1000
max_user_connections=800
table_definition_cache=4096
table_open_cache=4096
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = ON
log_output=file
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#client buffer
read_rnd_buffer_size = 2M
sort_buffer_size = 2M
tmp_table_size = 64M
join_buffer_size = 2M
max_allowed_packet=536870912  #512m
group_concat_max_len=10240000

#mysql log
log_error=/data1/mysql/log/mysql-error.log
log_timestamps=SYSTEM
default_time_zone='+08:00'

#innodb
innodb_buffer_pool_size=6g
innodb_open_files=4096
innodb_sort_buffer_size=2M
innodb_log_buffer_size=67108864  #64m
innodb_log_file_size= 268435456  #256m
innodb_log_files_in_group=4
innodb_locks_unsafe_for_binlog=1
innodb_log_group_home_dir=/data1/mysql/iblog
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_file_per_table           = 1
innodb_autoinc_lock_mode=2
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G

#innodb_force_recovery=1

#binlog
log_bin=/data1/mysql/binlog/mysql_bin
log_slave_updates=ON
binlog_cache_size=4M
max_binlog_cache_size=268435456 #256m
expire_logs_days=2
max_binlog_size=268435456 #256m
binlog_format=ROW
sync_binlog=500
enforce_gtid_consistency=1
gtid_mode=on
log_bin_trust_function_creators=ON



#semi_sync after install semi_sync plugin turn on
#rpl_semi_sync_master_enabled=ON
#rpl_semi_sync_slave_enabled=ON


#slow log
slow_query_log=ON
slow_query_log_file=/data1/mysql/log/slow-sql.log
log_queries_not_using_indexes=0
log_slow_admin_statements=1
long_query_time=1
#only percona
log_slow_rate_limit=100
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=2
slow_query_log_use_global_control=all

#monitor
innodb_monitor_enable=all
performance_schema=ON

[client]
port=3306
socket=/data1/mysql/mysql.sock

[mysql_safe]
socket=/data1/mysql/mysql.sock
pid_file=/data1/mysql/mysqld.pid


# 启动MySQL
systemctl start mysqld

# 查看密码
grep -rn "temporary" /data1/mysql/log

# 登陆MySQL，修改密码
mysql -uroot -p
mysql> SET PASSWORD = PASSWORD('Duzi@1023');
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Duzi@1023' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

# 创建函数
mysql> CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so';
mysql> CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so';
mysql> CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so';