個人學(xué)習(xí)使用
cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
----------------------------------------------------------------------
getenforce
Enforcing
----------------------------------------------------------------------
systemctl status firewalld.service
Active: active (running)
----------------------------------------------------------------------
systemctl status NetworkManager
Active: active (running)
----------------------------------------------------------------------
MySQL
Server version: 5.7.26
1）下載MySQL
https://downloads.mysql.com/archives/community/
同源版本：MariaDB 程帕、Percona 、RDS瞳氓、Polar、TDSQL
2）安裝
2.1）上傳栓袖、解壓安裝包
將MySQL安裝包上傳至Linux
解壓安裝包
tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
----------------------------------------------------------------------
#創(chuàng)建軟鏈接
cd /usr/local/
ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql
----------------------------------------------------------------------
ll -d mysql
lrwxrwxrwx. 1 root root 35 Jan 7 09:51 mysql -> mysql-5.7.26-linux-glibc2.12-x86_64
2.2）創(chuàng)建所需目錄匣摘、用戶、用戶組
#創(chuàng)建目錄
mkdir -p /data/mysql/data
----------------------------------------------------------------------
#創(chuàng)建用戶裹刮、用戶組
useradd mysql
2.3）授權(quán)
chown -R mysql.mysql /data/mysql/
----------------------------------------------------------------------
ll -d /data/mysql/
drwxr-xr-x. 3 mysql mysql 18 Jan 7 10:00 /data/mysql/
2.4）清理環(huán)境
rpm -qa |grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
----------------------------------------------------------------------
yum remove -y mariadb-libs
2.5）初始化數(shù)據(jù)
vim /etc/profile
----------------------------------------------------------------------
export PATH=/usr/local/mysql/bin:$PATH
----------------------------------------------------------------------
source /etc/profile
- Oracle MySQL 5.7之前以及MariaDB版本初始化數(shù)據(jù)
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
- Oracle MySQL 5.7之后
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
- 如報錯：
yum install -y libaio-devel
2.6）配置文件準備
vim /etc/my.cnf
----------------------------------------------------------------------
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
port=3306
[mysql]
socket=/tmp/mysql.sock
2.7）啟動MySQL
cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
systemctl start/stop/restart mysqld
2.8）連接管理
- socket：本地連接
mysql> grant all on *.* to 'liyiyi'@'localhost' identified by '123';
mysql -uliyiyi -p123 -S /tmp/mysql.sock
- tcpip：遠程連接
#創(chuàng)建帶有遠程地址段的用戶,并授權(quán)
mysql> grant all on *.* to liyiyi@'10.0.0.%' identified by '123';
mysql -uliyiyi -p123 -h 10.0.0.51 -P 3306
3）用戶管理
3.1）用戶的增刪改查
- 創(chuàng)建用戶
mysql> create user 'liyiyi'@'localhost' identified by '123456';
create user 'wangerer'@'192.168.1.%' identified by '123456';
- 修改用戶
#為管理員用戶增加密碼
mysql> alter user root@'localhost' identified by '123456';
----------------------------------------------------------------------
#修改密碼
mysql> alter user liyiyi@'localhost' identified by '456';
----------------------------------------------------------------------
#鎖用戶
mysql> alter user liyiyi@'localhost' account lock;
----------------------------------------------------------------------
#解鎖用戶
mysql> alter user liyiyi@'localhost' account unlock;
- 查詢用戶
mysql> select user,host from mysql.user;
mysql> select user,host,authentication_string from mysql.user;
- 刪除用戶（危險音榜，一般用鎖定用戶代替）
mysql> drop user wangerer@'192.168.1.%';
----------------------------------------------------------------------
#用鎖定用戶方法替代刪除用戶
mysql> alter user wangerer@'192.168.1.%' account lock;
3.2）用戶權(quán)限
- 權(quán)限列表
mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
ALL權(quán)限不包括：grant option、proxy捧弃、Usage
- 權(quán)限作用范圍
全庫：*.*
單庫：test.*
單表：test.t1
列：select(id) test.t1
- 增加用戶權(quán)限
#全庫權(quán)限
mysql> grant all on *.* to 'liyiyi'@'localhost';
----------------------------------------------------------------------
#單庫權(quán)限
mysql> grant all on test.* to 'wangereri'@'192.168.1.%';
- 回收用戶權(quán)限
mysql> revoke drop on test.* from wangerer@'192.168.1.%';
- 查看用戶權(quán)限
mysql> show grants for 'liyiyi'@'localhost';
+-----------------------------------------------------+
| Grants for liyiyi@localhost |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'liyiyi'@'localhost' |
+-----------------------------------------------------+
1 row in set (0.00 sec)
3.3）忘記管理員用戶密碼
- 啟動到"單用戶模式"赠叼，無密碼登錄
mysqld_safe --skip-grant-tables --skip-networking &
- 刷新授權(quán)表到內(nèi)存，修改密碼
mysql> flush privileges;
mysql> alter user 'root'@'localhost' identified by '123456';
- 重啟MySQL
systemctl restart mysqld
