一、环境准备
1.1 关闭防火墙
#停止防火墙
systemctl stop firewalld.service
#禁止firewall开机启动
systemctl disable firewalld.service
#查看默认防火墙状态
firewall-cmd--state
1.2 安装必备软件
#更新软件包
yum -y update
#安装上传下载lszrz
yum -y install lszrz
#安装wget
yum -y install wget
二、安装mysql过程
1.1 检查是否安装MySQL,如果安装 卸载之
rpm -qa |grep mysql
yum remove mysql*
1.2 检查是否安装MariaDB,如果安装 卸载之(重要)
rpm -qa |grep mariadb
yum remove mariadb*
1.3 wget下载mysql5.7.26
#建立目录用来放需要安装的软件（自己习惯来建目录）
mkdir -p /app
#下载
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
有点慢，可以到官网下载，在rz -y上传
1.4 解压并且改名为mysql
[root@mysql app]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@mysql app]# tar -xzvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@mysql app]# mv mysql-5.7.26-linux-glibc2.12-x86_64/ mysql
1.5 修改环境变量
[root@mysql app]# vi /etc/profile
#在最后添加即可
exportPATH=/app/mysql/bin:$PATH
#生效
[root@mysql app]# source /etc/profile
1.6 建立mysql用户和组
useradd mysql
1.7 创建相关目录并修改权限
[root@mysql app]# mkdir /data/mysql -p
[root@mysql app]# chown -R mysql.mysql /app/*
[root@mysql app]# chown -R mysql.mysql /data/*
1.8 初始化数据库
[root@mysql mysql]# rm -rf /data/mysql/*
[root@mysql mysql]# mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql
2019-08-20T03:29:54.126839Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-20T03:29:55.345568Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-20T03:29:55.536508Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-20T03:29:55.617830Z 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: c77fff90-c2fa-11e9-bb4a-080027383c74.
2019-08-20T03:29:55.624489Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-20T03:29:55.627494Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
1.9 修改添加配置文件my.cnf
[root@mysql tmp]# vi /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
server_id=1
port=3306
socket=/tmp/mysql.sock
tmpdir = /tmp
skip_name_resolve = 1
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1
max_connections = 400
max_connect_errors = 1000
interactive_timeout = 1800
wait_timeout = 1800
tmp_table_size = 134217728
max_heap_table_size = 134217728
log_error = /tmp/error.log
[mysql]
socket=/tmp/mysql.sock
prompt=3306 [\\d]>
1.10 使用systemd管理mysql
[root@mysql mysql]# vi /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
systemctl start/stop/restart/status mysqld
1.11 简单使用
#直接mysql命令进入（此时密码为空）
[root@mysql mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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.
#用户信息 descmysql.user;
5.6版本:
select user,password,hostfrommysql.user;
5.7中用户基本信息
3306 [(none)]>select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
+---------------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)
#修改密码
[root@mysql mysql]# mysqladmin -uroot -p password '*******';
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety
#查看连接线程情况
3306 [(none)]>show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
1.12 外部第三方管理软件连接（SQLyog）
数据库IP地址为 192.168.0.11 不能连接
#查看授权
3306 [(none)]>show grants for root@'192.168.0.%';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '192.168.0.%'
#主要是由于用户和权限管理的问题，后面配置
1.13 用户
#用户的增删改查
3306 [(none)]>create user victor@'192.168.0.%' identified by 'victor';
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>desc mysql.user;
3306 [(none)]>select user ,host ,authentication_string from mysql.user;
------
3306 [(none)]>alter user victor@'192.168.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>drop user victor@'192.168.0.%';
Query OK, 0 rows affected (0.00 sec)
1.14 权限管理
#先创建test 用户
3306 [(none)]>create user test@'192.168.0.%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)
#分配test用户权限
3306 [(none)]>grant all on *.* to 'test'@'192.168.0.%';
Query OK, 0 rows affected (0.00 sec)
#用SQLyog连接
ip地址：
192.168.0.11
用户名
test
密码
test
#权限介绍
ALL:SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACEALL : 以上所有权限，一般是普通管理员拥有的withgrant option：超级管理员才具备的，给别的用户授权的功能
#查看授权
3306 [(none)]>show grants for test@'192.168.0.%';
+-----------------------------------------------------+
| Grants for test@192.168.0.% |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'192.168.0.%' |
+-----------------------------------------------------+
1 row in set (0.00 sec)
#回收授权
revoke delete on *.* from test@'192.168.0.%'；
