CentOS7安装mysql5.7（1）—配置master节点

1、mysql数据库目录结构
MySQL Binary目录：/usr/local/mysql
MySQL Data目录：/data/mysql
MySQL Backup目录：/backup/mysql

2、安装依赖包

yum install autoconf automake libtool libaio cmake ncurses-devel openssl-devel lzo-devel zlib-devel gcc gcc-c++

3、创建用户和安装目录

groupadd -g 1000 mysql
useradd -r -s /sbin/nologin -g mysql -u 997 -d /usr/local/mysql/ mysql

cd /usr/local/
mkdir mysql
chown -R mysql:mysql /usr/local/mysql/

mkdir /data
mkdir /backup

cd /data/
mkdir mysql
chown -R mysql.mysql /data/mysql

cd /backup/
mkdir mysql
chown -R mysql.mysql /backup/mysql

4、下载mysql二进制文件

cd /tmp
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
cp -Rf /tmp/mysql-5.7.35-linux-glibc2.12-x86_64/* /usr/local/mysql/
chown -R mysql:mysql /usr/local/mysql/

5、初始化mysql

cd /usr/local/mysql/bin
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

执行结果：

2021-08-02T02:41:05.549401Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-02T02:41:06.198765Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-02T02:41:06.294202Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-02T02:41:06.309103Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1606b628-f33b-11eb-a4b5-00163cd5cd1b.
2021-08-02T02:41:06.311032Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-02T02:41:07.356781Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-02T02:41:07.356818Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-02T02:41:07.357899Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-02T02:41:07.535961Z 1 [Note] A temporary password is generated for root@localhost: u42WeRSWq+dp

注意临时登录密码

6、配置my.cnf

vi /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
user = mysql
port = 3310
character_set_server = utf8
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM
server-id=1
tmpdir = /tmp
symbolic-links=0

#数据库数据文件参数

innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table = ON

#数据库内存参数

key_buffer_size = 128M
innodb_buffer_pool_size = 1024M

#数据库线程数参数

thread_cache_size = 300
innodb_thread_concurrency = 4

#innodb日志文件参数

innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3

#文件描述符限制

open_files_limit = 10240

#数据库bin log参数

log-bin=mysql-bin
binlog-format = Row
binlog_cache_size = 4M
max_binlog_size = 256M
#binlog-ignore-db=information_schema
#binlog-ignore-db=mysql
#binlog-ignore-db=performance_schema
#binlog-ignore-db=sys
#replicate_wild_do_table=test.%
#replicate-do-db=test
#replicate-ignore-db=mysql
#log-slave-updates
#slave-skip-errors=all
#slave-net-timeout=60
#relay-log=mysqldb02-relay-bin
#master-info-repository=table
#relay-log-info-repository=table

#数据库表缓存参数

table_open_cache = 1000
table_definition_cache = 700

#查询缓存参数

query_cache_type = OFF
query_cache_size = 0
query_cache_limit = 0

tmp_table_size = 512M
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 16M

#数据库connect参数

max_connections = 1000
back_log = 100
max_connect_errors = 1000
max_allowed_packet = 32M

#数据库慢查询日志参数

slow_query_log = ON
slow_query_log_file = /data/mysql/mysqldb01-slow.log
long_query_time = 10
log_queries_not_using_indexes = ON

[mysqld_safe]
log-error = /data/mysql/error.log
pid-file = /data/mysql/mysql.pid

[mysqldump]
quick
max_allowed_packet = 16M

 7、配置mysql服务
1）复制文件

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

2）编辑init.d下mysqld

basedir=/usr/local/mysql
datadir=/data/mysql

3）编辑mysqld.service

vi /usr/lib/systemd/system/mysqld.service

[Unit]
Description=MySQL 5.7
SourcePath=/etc/init.d/mysqld
After=network.target
After=syslog.target
Before=shutdown.target

[Service]
User=mysql
Group=mysql
Type=forking
ExecStart=/etc/init.d/mysqld start
ExecStop=/etc/init.d/mysqld stop
ExecReload=/etc/init.d/mysqld restart

[Install]
WantedBy=multi-user.target

4）重新加载服务配置

systemctl daemon-reload

5）启动服务

systemctl stop mysqld
systemctl start mysqld

8、报错收集
[ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
原因：设置了bin-log，但是没有设置server-id

9、配置环境变量

vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin

source /etc/profile

10、修改root密码

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.35-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by 'xxxxxxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.35-log |
+------------+
1 row in set (0.01 sec)

11、安装密码复杂度插件

mysql> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

密码复杂度默认采用MEDIUM级别，就是密码必须需要包含至少一个数字，一个大写字母，一个小写字母，一个特殊字符，密码总长度大于9

测试MEDIUM级别密码复杂度

mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements