https://downloads.mysql.com/archives/community/
4.5 MySQL 5.7.26 二進制版本安裝 4.5.1 下載并上傳軟件至/server/tools [root@db01 ~]# mkdir -p /server/tools [root@db01 ~]# cd /server/tools/ [root@db01 /server/tools]# yum install -y lrzsz [root@db01 /server/tools]# ls mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 4.5.2 解壓軟件 [root@db01 /server/tools]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz [root@db01 ~]# mkdir /application [root@db01 /server/tools]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql 4.5.3 用戶的創建處理原始環境 [root@db01 ~]# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y [root@db01 ~]# rpm -qa |grep mariadb [root@db01 ~]# useradd -s /sbin/nologin mysql
4.5.4 設置環境變量 vim /etc/profile export PATH=/application/mysql/bin:$PATH [root@db01 ~]# source /etc/profile [root@db01 ~]# mysql -V mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper 4.5.5 創建數據路徑并授權 1. 添加一塊新磁盤模擬數據盤 2. 格式化并掛載磁盤 [root@db01 ~]# mkfs.xfs /dev/sdb [root@db01 ~]# mkdir /data [root@db01 ~]# blkid [root@db01 ~]# vim /etc/fstab [root@db01 ~]# UUID="b21ec3e0-e251-4ded-bc12-2d940f938dd5" /data xfs defaults 0 0 [root@db01 ~]# mount -a [root@db01 ~]# df -h 3. 授權 [root@db01 ~]# chown -R mysql.mysql /application/* [root@db01 ~]# chown -R mysql.mysql /data
4.5.6 初始化數據（創建系統數據） # 5.6 版本 初始化命令 /application/mysql/scripts/mysql_install_db # 5.7 版本 [root@db01 ~]# mkdir /data/mysql/data -p [root@db01 ~]# chown -R mysql.mysql /data [root@db01 ~]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data 說明： --initialize 參數： 1. 對于密碼復雜度進行定制：12位，4種 2. 密碼過期時間：180 3. 給root@localhost用戶設置臨時密碼 報錯: mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory 解決： [root@db01 ~]# yum install -y libaio-devel [root@db01 ~]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data 2019-06-13T04:21:27.706564Z 1 [Note] A temporary password is generated for root@localhost: =mrV)_16is4U --initialize-insecure 參數： 無限制，無臨時密碼 [root@db01 /data/mysql/data]# \rm -rf /data/mysql/data/* [root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
4.5.7 配置文件的準備 cat >/etc/my.cnf <<EOF [mysqld] user=mysql basedir=/application/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=6 port=3306 [mysql] socket=/tmp/mysql.sock EOF 4.5.8 啟動數據庫 1. sys-v [root@db01 /etc/init.d]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld [root@db01 /etc/init.d]# service mysqld restart ERROR! MySQL server PID file could not be found! Starting MySQL.Logging to '/data/mysql/data/localhost.localdomain.err'. SUCCESS!
2. systemd
注意： sysv方式啟動過的話，需要先提前關閉，才能以下方式登錄
cat >/etc/systemd/system/mysqld.service <<EOF
[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=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
5. 如何分析處理MySQL數據庫無法啟動 5.1 without updating PID 類似錯誤 查看日志： 在哪？ /data/mysql/data/主機名.err [ERROR] 上下文 可能情況： /etc/my.cnf 路徑不對等 /tmp/mysql.sock文件修改過 或 刪除過 數據目錄權限不是mysql 參數改錯了
6.管理員密碼的設定（root@localhost） [root@db01 ~]# mysqladmin -uroot -p password oldboy123 Enter password: 7. 管理員用戶密碼忘記了？ 7.1 關閉數據庫 [root@db01 ~]# /etc/init.d/mysqld stop Shutting down MySQL.. SUCCESS! 7.2 啟動數據庫到維護模式 [root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking & 7.3 登錄并修改密碼 mysql> alter user root@'localhost' identified by '1'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> flush privileges; mysql> alter user root@'localhost' identified by '1'; Query OK, 0 rows affected (0.01 sec) 7.4 關閉數據庫，正常啟動驗證
登錄數據庫報錯案例：忘記密碼
7.1 關閉數據庫
[root@db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
7.2 啟動數據庫到維護模式
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
7.3 登錄并修改密碼
1，
mysql> alter user root@'localhost' identified by '1';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '1';
Query OK, 0 rows affected (0.01 sec)
2，
[root@localhost data]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
update user set authentication_string=password("123456") where user='root';
flush privileges;
killall mysqld
ps -aux|grep mysql
mysqld_safe --skip-grant-tables --skip-networking &
mysql -uroot -p
killall mysqld
ps -aux|grep mysqld
service mysqld start
mysql -uroot
use mysql;
update user set authentication_string=password("123456") where user='root';
flush privileges;
mysql遠程連接報錯： Host * is not allowed to connect to this MySQL server，解決方法
localhost改成%
1.進入mysql的BIN目錄
注：root為管理員用戶名，password為用戶root的密碼：
mysql -u root -p password
mysql>use mysql;
mysql>update user set host ='%'where user ='root' and host ='localhost';
mysql>flush privileges;
注：這時候連接此服務器的mysql客戶端需要重新啟動下，然后再連接此服務器。
mysql> select user,host ,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | sunlong | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +---------------+-----------+-------------------------------------------+ mysql> update user set host ='%'where user ='root' and host ='localhost.localdomain';
mysql> flush privileges; Query OK,
登錄mysql出現報錯：
Your password has expired. To log in you must change it using a client that supports expired passwords
錯誤直接翻譯過來即：您的密碼已過期，您必須使用支持過期密碼的客戶端進行更改
解決方法：
mysql -u root -p 輸入之前正確的密碼進入 設置新密碼 set password=password('123456') 設置密碼永不過期 alter user 'root'@'%' password expire never
用戶管理：
2.1.2 用戶的定義
用戶名@'白名單'
白名單？
oldguo@'10.0.0.51'
oldguo@'10.0.0.%'
oldguo@'10.0.0.5%'
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'%'
oldguo@'oldguo.com'
oldguo@'localhost'
oldguo@'db01'
常用的：
oldguo@'10.0.0.%'
oldguo@'10.0.0.5%'
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'localhost'
bc@'白名單' 密碼123
白名單: 允許白名單中的IP,可以通過abc用戶及123密碼登錄和管理MySQL
白名單支持定義的方式:
10.0.0.52 10.0.0.% ==> 10.0.0.1~10.0.0.254/24 10.0.0.5% ==> 10.0.0.50 ~10.0.0.59（包含10.0.0.5） www.oldguo.com % 10.0.0.0/255.255.254.0
2.2、用戶管理命令
2.2.1、創建用戶
create user abc@'10.0.0.%' identified by '123'; select user ,host from mysql.user;
2.2.2、刪除用戶
drop user abc@'10.0.0.%';
2.2.3、修改用戶密碼
alter user abc@'10.0.0.%' identified by '456';
查詢用戶
mysql> select user,host ,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | sunlong | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +---------------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec)
2.2.2 權限的定義
MySQL的權限定義就是SQL語句。
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 TABLESPACE
ALL : 以上所有權限，一般是普通管理員擁有的
with grant option：超級管理員才具備的，給別的用戶授權的功能
2.2.3 授權管理
（1）語法：
grant ALL on wordpress.* to wordpress@'10.0.0.%' identified by '123';
grant 權限 on 范圍 to 用戶 identified by '密碼'
grant select,update,insert,delete on 范圍 to 用戶 identified by '密碼'
范圍：
*.*
wordpress.*
wordpress.t1
（2）例子：
1. 從windows中的navicat軟件使用root管理mysql數據庫
grant all on *.* to root@'10.0.0.%' identified by '123';
2. 創建 zhihu業務用戶能夠對zhihu業務庫進行業務操作
grant select,update,delete ,insert on zhihu.* to zhihu@'10.0.0.%' identified by '123';
（3）思考一個問題（課后自己進行驗證）：
1. grant select,update on *.* to oldboy@'10.0.0.%';
2. grant delete on wordpress.* to oldboy@'10.0.0.%';
3. grant insert on wordpress.t1 to oldboy@'10.0.0.%';
問，oldboy@'10.0.0.%' 能對t1表具備什么權限？
MySQL中的權限是可以繼承，多次授權是疊加的。
所以，想要取消某個權限，必須通過回收的方式實現，而不能多次重復授權。
（4）查看用戶權限
mysql> show grants for root@'localhost';
（5）回收權限
mysql> revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';
3. MySQL 連接管理
3.1 自帶的客戶端工具
mysql 參數
-u
-p
-S
-h
-P
[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock
[root@db01 ~]# mysql -uoldguo -p -h10.0.0.51 -P3306
問題：
怎么證明你的數據庫是可用的？
1. 證明進程和端口存在
ps -ef |grep mysqld
netstat -lnp|grep mysqld
2. 驗證用戶遠程連接性
[root@db01 ~]# mysql -uoldguo -p -h10.0.0.51
-e
<
[root@db01 ~]# mysql -uoldguo -p123 -h10.0.0.51 -e "show databases;"
[root@db01 ~]# mysql -uroot -p <world.sql
3.2 遠程的客戶端工具
navicat sqlyog
略。
4. MySQL 啟動方式介紹
輔助腳本方式（普通的啟動關閉）：
sys-v : /etc/init.d/mysqld
/etc/init.d/mysqld start
----> /application/mysql/bin/mysqld_safe
----> /application/mysql/bin/mysqld
systemd : /etc/systemd/system/mysqld.service
systemctl start mysqld
---->/application/mysql/bin/mysqld
維護性的啟動方式：
/application/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &
/application/mysql/bin/mysqld &
5.2 初始化配置文件(my.cnf)
5.2.1 初始化配置文件默認讀取位置
[root@db01 ~]# mysqld --help --verbose |grep "my.cnf"
/etc/my.cnf --》 /etc/mysql/my.cnf --》 /usr/local/mysql/etc/my.cnf --》 ~/.my.cnf
最后一個會覆蓋前面的配置文件
指定配置文件啟動方式
--defaults-file=/opt/my.cnf
/application/mysql/bin/mysqld_safe --defaults-file=/opt/my.cnf
/application/mysql/bin/ --defaults-file=/opt/my.cnf
5.3 命令行模式
[root@db01 ~]# mysqld_safe --defaults-file=/opt/my.cnf --socket=/tmp/asdad &
5.4.3 mysql初始化配置常用參數(通用模板)
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/data/mysql.log
log_bin=/data/mysql/data/mysql-bin
[mysql]
socket=/tmp/mysql.sock
mysql多實例
一臺機器啟動多個mysql實例
5.5.1 創建目錄 mkdir -p /data/330{7,8,9}/data 5.5.2 準備配置文件 cat > /data/3307/my.cnf <<EOF [mysqld] basedir=/application/mysql datadir=/data/3307/data socket=/data/3307/mysql.sock log_error=/data/3307/mysql.log port=3307 server_id=7 log_bin=/data/3307/mysql-bin EOF cat > /data/3308/my.cnf <<EOF [mysqld] basedir=/application/mysql datadir=/data/3308/data socket=/data/3308/mysql.sock log_error=/data/3308/mysql.log port=3308 server_id=8 log_bin=/data/3308/mysql-bin EOF cat > /data/3309/my.cnf <<EOF [mysqld] basedir=/application/mysql datadir=/data/3309/data socket=/data/3309/mysql.sock log_error=/data/3309/mysql.log port=3309 server_id=9 log_bin=/data/3309/mysql-bin EOF 5.5.3 初始化三套數據 mv /etc/my.cnf /etc/my.cnf.bak mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql 5.5.4 systemd管理多實例 cd /etc/systemd/system cp mysqld.service mysqld3307.service cp mysqld.service mysqld3308.service cp mysqld.service mysqld3309.service vim mysqld3307.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf vim mysqld3308.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf vim mysqld3309.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf 5.5.5 授權 chown -R mysql.mysql /data/* 5.5.6 啟動 systemctl start mysqld3307.service systemctl start mysqld3308.service systemctl start mysqld3309.service 5.5.7 驗證多實例 netstat -lnp|grep 330 mysql -S /data/3307/mysql.sock -e "select @@server_id" mysql -S /data/3308/mysql.sock -e "select @@server_id" mysql -S /data/3309/mysql.sock -e "select @@server_id"
登錄多實例：
mysql -uroot -p -S /data/3307/mysql.sock -P3307
修改密碼：
mysql> mysql -u root -p 輸入之前正確的密碼進入 設置新密碼 mysql> set password=password('123456') 設置密碼永不過期 mysql> alter user 'root'@'localhost' password expire never
mysql> use mysql;
mysql> select user,host ,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> use mysql;
mysql> update user set host ='localhost'where user ='root' and host ='localhost';
mysql> flush privileges;
本文來自博客園，作者：孫龍-程序員，轉載請注明原文鏈接：http://www.rzrgm.cn/sunlong88/p/16610721.html
浙公網安備 33010602011771號