下载地址：
https://downloads.mysql.com/archives/community/
本次测试用的安装包 ： mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
卸载原来的mariadb
[root@localhost mysql]# rpm -qa |grep mariadb
mariadb-libs-5.5.65-1.el7.x86_64
mariadb-5.5.65-1.el7.x86_64
mariadb-server-5.5.65-1.el7.x86_64
[root@localhost mysql]# yum remove mariadb-libs-5.5.65-1.el7.x86_64
解压安装包,移动并重命名
tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.26-linux-glibc2.12-x86_64 /opt/mysql
通常在生产环境下，mysql的数据文件会单独存放在另一块磁盘，这边不做测试。
添加用户，如果之前有，可以先删除再添加。
useradd -s /sbin/nologin mysql
修改环境变量
vi /etc/profile
#添加下行
export PATH=/opt/mysql/bin:$PATH
source /etc/profile
目录授权,先自己创建
[root@localhost data]# chown -R mysql:mysql /data/mysql/data
[root@localhost data]# chown -R mysql:mysql /opt/mysql/
数据初始化
[root@localhost data]# mysqld --initialize --user=mysql --basedir=/opt/mysql/ --datadir=/data/mysql/data/
如果初始化失败，有报错如下：
image.png
则安装
yum install -y libaio-devel
image.png
--initialize参数说明：
1,对于密码复杂度进行定制：12位，4种
2，密码过期时间为180天
3给root@localhost设置临时密码
--initialize-insecure参数说明：
无限制，无临时密码
这边重新采用无限制的方式初始化数据库，先把之前的删除
[root@localhost data]# rm -rf /data/mysql/data/*
[root@localhost data]# mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql/ --datadir=/data/mysql/data/
2020-06-04T06:40:53.100085Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-04T06:40:54.145999Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-04T06:40:54.382353Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-04T06:40:54.444639Z 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: 56dff5e6-a62e-11ea-9db6-000c29ceb265.
2020-06-04T06:40:54.446327Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-06-04T06:40:54.447955Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
配置文件准备
echo '''
[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
''' >>/etc/my.cnf
启动方式1
[root@localhost support-files]# cp /opt/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost support-files]# service mysqld start
Starting MySQL.Logging to '/data/mysql/data/localhost.err'.
. SUCCESS!
启动方式2
echo '''
[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=/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=5000
''' > /etc/systemd/system/mysqld.service
systemctl start mysqld.service
/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf 上面代码里这行才是启动的命令，也可以直接执行这行命令，但会直接输出日志。
修改root密码
[root@localhost system]# mysqladmin -uroot -p password 123456
数据库操作
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
登录数据库后修改密码
mysql> alter user root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看链接数
mysql> show full processlist;
+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------------+------+---------+------+----------+------------------+
| 9 | root | 10.0.2.185:58223 | NULL | Sleep | 530 | | NULL |
| 10 | root | 10.0.2.185:58224 | NULL | Sleep | 530 | | NULL |
| 11 | root | localhost | NULL | Sleep | 17 | | NULL |
| 12 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+------------------+------+---------+------+----------+------------------+
4 rows in set (0.00 sec)
数据库结构
数据库里所有的库在data下都有对应的目录
MyISAM（ext2）
user.frm:存储表结构（列，列属性）
user.MYD:存储的数据记录
user.MYI:存储索引
InnoDB(XFS)
time_zone.frm:存储表结构（列，列属性）
time_zone.ibd:存储的数据记录和索引
ibdata1 : 数据字典信息
用户管理和权限管理
允许2网段访问
mysql> create user zhiuan@'10.0.2.%' identified by '123456'
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| zhiuan | 10.0.2.% |
| mysql.session | localhost |
| mysql.sys | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
#修改用户密码
mysql> alter user zhiuan@'10.0.2.%' identified by '11111';
Query OK, 0 rows affected (0.00 sec)
#权限管理
#授权所有 库所有表*.*
mysql> grant all on *.* to zhiuan@'10.0.2.%' identified by '11111';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# grant 权限 on 作用目标 to 用户 identified by 密码 with grant option；
grant SELECT,INSERT on kuming.* to zhiuan@'10.0.2.%' identified by '11111';
#查看权限
mysql> show grants for zhiuan@'10.0.2.%'
-> ;
+----------------------------------------------------+
| Grants for zhiuan@10.0.2.% |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhiuan'@'10.0.2.%' |
+----------------------------------------------------+
1 row in set (0.00 sec)
#拿掉某个权限
mysql> show grants for peng@'10.0.%';
+--------------------------------------------------------+
| Grants for peng@10.0.% |
+--------------------------------------------------------+
| GRANT SELECT, INSERT, DELETE ON *.* TO 'peng'@'10.0.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke delete on *.* from peng@'10.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for peng@'10.0.%';
+------------------------------------------------+
| Grants for peng@10.0.% |
+------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'peng'@'10.0.%' |
+------------------------------------------------+
1 row in set (0.00 sec)
