文章目录

MySQL初始化安装、简单加固

优化法则三种思路

  • 数据库的性能优化首先是计算机系统的优化
  • 数据库的性能优化其次是SQL语句的优化
  • 数据库的性能优化最有效的是架构的优化

1.MySQL初始化安装

1.1下载二进制安装文件

[root@localhost ~]# cd /root/
[root@localhost ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
--2021-10-19 10:29:29--  https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

1.2创建MySQL用户

[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd mysql -r -g mysql 
[root@localhost ~]# id mysql #验证mysql
uid=987(mysql) gid=1001(mysql) 组=1001(mysql)

1.3创建程序、数据存放目录(规范)

[root@localhost ~]# mkdir /home/mysql/{program,data,conf} -p
[root@localhost ~]# mkdir /home/mysql/data/mysqldata1/{mydata,sock,tmpdir,log,innodb_ts,innodb_log,undo,slowlog,binlog,relaylog} -p
[root@localhost ~]# yum install tree
[root@localhost ~]# tree /home/mysql/
/home/mysql/
├── conf
├── data
│   └── mysqldata1
│       ├── binlog
│       ├── innodb_log
│       ├── innodb_ts
│       ├── log
│       ├── mydata
│       ├── relaylog
│       ├── slowlog
│       ├── sock
│       ├── tmpdir
│       └── undo
└── program
14 directories, 0 files

1.4解压安装文件并设置目录权限

[root@localhost ~]# tar xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /home/mysql/program/
[root@localhost ~]# chown mysql.mysql /home/mysql/ -R
[root@localhost ~]# ll /home/mysql/data/mysqldata1/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 binlog
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 innodb_log
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 innodb_ts
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 log
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 mydata
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 relaylog
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 slowlog
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 sock
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 tmpdir
drwxr-xr-x. 2 mysql mysql 6 10月 19 10:35 undo

1.5软链接程序路径,设置MySQL命令环境变量

把/home/mysql/program/mysql-5.7.35-linux-glibc2.12-x86_64路径软链接到MySQL默认的程序访问路径/usr/local/mysql下,并把/usr/local/mysql/bin/添加到系统环境变量中,以便使用mysql相关命令时不需要输入绝对路径。

[root@localhost ~]# ln -s /home/mysql/program/mysql-5.7.35-linux-glibc2.12-x86_64 /usr/local/mysql
[root@localhost ~]# ll /usr/local/mysql/  #查看basedir关键目录是否可用
总用量 272
drwxr-xr-x.  2 mysql mysql   4096 10月 19 10:41 bin
drwxr-xr-x.  2 mysql mysql     55 10月 19 10:41 docs
drwxr-xr-x.  3 mysql mysql   4096 10月 19 10:41 include
drwxr-xr-x.  5 mysql mysql    230 10月 19 10:41 lib
-rw-r--r--.  1 mysql mysql 259200 6月   7 20:52 LICENSE
drwxr-xr-x.  4 mysql mysql     30 10月 19 10:41 man
-rw-r--r--.  1 mysql mysql    566 6月   7 20:52 README
drwxr-xr-x. 28 mysql mysql   4096 10月 19 10:41 share
drwxr-xr-x.  2 mysql mysql     90 10月 19 10:41 support-files
[root@localhost ~]# export PATH=$PATH:/usr/local/mysql/bin/
[root@localhost ~]# echo 'export PATH=$PATH:/usr/local/mysql/bin/' >> /etc/profile
[root@localhost ~]# tail -1 /etc/profile #查看环境变量是否配置成功到/etc/profile文件中
export PATH=$PATH:/usr/local/mysql/bin/

1.6配置my.cnf文件参数

把样例配置文件复制到/home/mysql/conf目录下,并设置好相关路径系统参数:socket、pid-file、datadir、tmpdir、log-error、slowquery_log_file、log-bin、relay-log、 innodb_data home_dir、innodb_log_group_home_dir、innodb_undo_directory。

/home/mysql/conf目录下创建my.cnf文件,文件内容:

[client]
port = 3306
default-character-set=utf8mb4
socket=/home/mysql/data/mysqldata1/sock/mysql.sock

[mysqld]
server_id=10
port = 3306
user = mysql
character-set-server = utf8mb4
default_storage_engine = innodb
log_timestamps = SYSTEM

basedir=/usr/local/mysql
socket=/home/mysql/data/mysqldata1/sock/mysql.sock
pid-file=/home/mysql/data/mysqldata1/sock/mysqld.pid
datadir=/home/mysql/data/mysqldata1/mydata
tmpdir=/home/mysql/data/mysqldata1/tmpdir

max_connections = 1000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 128M
open_files_limit = 65535

#==[innodb]===
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 2
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 30
innodb_data_file_path=ibdata1:1024M:autoextend

#####====================================[log]==============================
log_error=/home/mysql/data/mysqldata1/log/error.log
slow_query_log = 1
long_query_time = 1 
slow_query_log = on
slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.sql

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[root@localhost etc]# ln -s /home/mysql/conf/my.cnf /etc/my.cnf

1.7初始化MySQL并查看初始密码

[root@localhost mysql]# cd /usr/local/mysql/
[root@localhost mysql]# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize
[root@localhost mysql]# cat /home/mysql/data/mysqldata1/log/error.log
2021-10-19T12:17:32.790184+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-10-19T12:17:32.790248+08:00 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-10-19T12:17:32.790254+08:00 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500
 100 200 300 400 500
2021-10-19T12:17:36.021541+08:00 0 [Warning] InnoDB: New log files created, LSN=45791
2021-10-19T12:17:36.079697+08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-10-19T12:17:36.148353+08:00 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: 7d4291e1-3093-11ec-94da-0800270e13d5.
2021-10-19T12:17:36.151235+08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-10-19T12:17:36.786326+08:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-10-19T12:17:36.786345+08:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-10-19T12:17:36.787234+08:00 0 [Warning] CA certificate ca.pem is self signed.
2021-10-19T12:17:37.124329+08:00 1 [Note] A temporary password is generated for root@localhost: ;OU:Y9?oaeVc

1.8启动MySQL

[root@localhost mysql]# cp -ar /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chmod +x /etc/init.d/mysqld 
[root@localhost mysql]# ll /etc/init.d/mysqld  #查看赋权是否成功
-rwxr-xr-x. 1 mysql mysql 10576 6月   7 22:18 /etc/init.d/mysqld
[root@localhost mysql]# service mysqld start
Starting MySQL. SUCCESS! 
[root@localhost mysql]# ps aux|grep mysqld
root      3657  0.1  0.0  11816  1628 pts/2    S    12:20   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/home/mysql/data/mysqldata1/mydata --pid-file=/home/mysql/data/mysqldata1/sock/mysqld.pid
mysql     4172  1.7 13.5 2000016 255828 pts/2  Sl   12:20   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/home/mysql/data/mysqldata1/mydata --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/home/mysql/data/mysqldata1/log/error.log --open-files-limit=65535 --pid-file=/home/mysql/data/mysqldata1/sock/mysqld.pid --socket=/home/mysql/data/mysqldata1/sock/mysql.sock --port=3306
root      4204  0.0  0.0 112724   988 pts/2    R+   12:20   0:00 grep --color=auto mysqld
[root@localhost mysql]# netstat -ntupl | grep mysqld                  
tcp6       0      0 :::3306                 :::*                    LISTEN      4172/mysqld

1.9登录并修改初始密码

第一次登录MySQL使用error.log初始密码,登录成功需要强制修改密码

[root@localhost mysql]# mysql -uroot  -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
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> select user();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER USER() IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.35-log |
+------------+
1 row in set (0.00 sec)

开启远程访问:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
flush privileges;

2.简单加固MySQL

删除test库(5.7.x没有),修改非root或非localhost的用户并修改root密码、清理mysql.db表。

3.创建用户、库、表、数据

3.1创建管理员用户并授权

不要直接使用DML语句操作mysql.user表,而是要使用grant、revoke或者create user、 drop user语句。如果必须要使用DML语句操作mysql.user表,那么请注意,在MySQL 5.7.x中password字段名称变更为了authentication_string。

mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'xwder'@'%' identified by 'xwder';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'xwder'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

重新登陆
[root@localhost ~]# mysql -uxwder -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.35-log MySQL Community Server (GPL)

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> show grants;
+--------------------------------------------------------------+
| Grants for xwder@%                                           |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'xwder'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

3.2创建库、表、程序账号

创建程序账号(在生产环境中不建议直接使用root账号,所以这里新建一个管理员账号,一个程序账号)。

程序账号一般给开发人员使用,给定权限推荐:create execute、select、delete、insert、update。

程序账号需要指定具体的库或表,且指定具体的访问来源。

….