下載地址:
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
通常在生產(chǎn)環(huán)境下,mysql的數(shù)據(jù)文件會單獨存放在另一塊磁盤,這邊不做測試院尔。
添加用戶乾闰,如果之前有嗤攻,可以先刪除再添加颁督。
useradd -s /sbin/nologin mysql
修改環(huán)境變量
vi /etc/profile
#添加下行
export PATH=/opt/mysql/bin:$PATH
source /etc/profile
目錄授權(quán),先自己創(chuàng)建
[root@localhost data]# chown -R mysql:mysql /data/mysql/data
[root@localhost data]# chown -R mysql:mysql /opt/mysql/
數(shù)據(jù)初始化
[root@localhost data]# mysqld --initialize --user=mysql --basedir=/opt/mysql/ --datadir=/data/mysql/data/
如果初始化失敗,有報錯如下:
image.png
則安裝
yum install -y libaio-devel
image.png
--initialize參數(shù)說明:
1,對于密碼復雜度進行定制:12位臣淤,4種
2嗦锐,密碼過期時間為180天
3給root@localhost設置臨時密碼
--initialize-insecure參數(shù)說明:
無限制嫌松,無臨時密碼
這邊重新采用無限制的方式初始化數(shù)據(jù)庫,先把之前的刪除
[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 上面代碼里這行才是啟動的命令奕污,也可以直接執(zhí)行這行命令萎羔,但會直接輸出日志。
修改root密碼
[root@localhost system]# mysqladmin -uroot -p password 123456
數(shù)據(jù)庫操作
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
登錄數(shù)據(jù)庫后修改密碼
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)
查看鏈接數(shù)
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)
數(shù)據(jù)庫結(jié)構(gòu)
數(shù)據(jù)庫里所有的庫在data下都有對應的目錄
MyISAM(ext2)
user.frm:存儲表結(jié)構(gòu)(列碳默,列屬性)
user.MYD:存儲的數(shù)據(jù)記錄
user.MYI:存儲索引
InnoDB(XFS)
time_zone.frm:存儲表結(jié)構(gòu)(列贾陷,列屬性)
time_zone.ibd:存儲的數(shù)據(jù)記錄和索引
ibdata1 : 數(shù)據(jù)字典信息
用戶管理和權(quán)限管理
允許2網(wǎng)段訪問
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)
#權(quán)限管理
#授權(quán)所有 庫所有表*.*
mysql> grant all on *.* to zhiuan@'10.0.2.%' identified by '11111';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# grant 權(quán)限 on 作用目標 to 用戶 identified by 密碼 with grant option;
grant SELECT,INSERT on kuming.* to zhiuan@'10.0.2.%' identified by '11111';
#查看權(quán)限
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)
#拿掉某個權(quán)限
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)