溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
mysql 密碼恢復及設置
[root@mysql ~]# grep password /var/log/mysqld.log#mysql 啟動后隨機生成的初始密碼
2017-12-20T02:36:18.623330Z 1 [Note] A temporary password is generated for root@localhost: 5h)>QAdqbI7t
#使用初始密碼登錄 并重置密碼 初始密碼不能對數據庫進行操作 需要重置密碼
[root@mysql4-1 ~]# mysql -hlocalhost -uroot -p'5h)>QAdqbI7t'
修改密碼驗證策略
mysql> set global validate_password_policy=0;
#策略 0 長度
1 （默認） 長度；數字，小寫/大寫，和特殊字符
2 長度；數字，小寫/大寫，和特殊字符；字典文件
修改密碼長度6 默認值是8個字符
mysql> set global validate_password_length=6;
mysql> alter user root@"localhost" identified by "123456";
mysql> show database;#測試
mysql> quit
Bye
[root@mysql4-1 ~]# mysql -hlocalhost -uroot -p'123456'
設置密碼驗證策略永久生效
[root@mysql4-1 ~]# vim /etc/my.cnf
...
[mysqld]
validate_password_policy=0
validate_password_length=6
...
[root@mysql4-1 ~]# systemctl restart mysqld
修改數據庫管理員本機管理密碼（操作系統管理員）
mysqladmin -hlocalhost -uroot -p舊密碼 password '新密碼'
[root@mysql ~]# mysqladmin -hlocalhost -uroot -p123456 password '123123'
當忘記密碼時,密碼恢復
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables#啟動時不驗證用戶密碼
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# mysql
mysql> use mysql
mysql> update user set password_expired="N" where user="root";
mysql> update mysql.user set authentication_string=password("abc123") where user="root";
mysql> flush privileges; #刷新MySQL的系統權限相關表
[root@mysql ~]# vim /etc/my.cnf
關閉啟動時不驗證用戶密碼
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# mysql -uroot -pabc123
數據管理
數據導入：把系統文件的內容存儲到數據庫的表里
/etc/passwdstudb.user
用戶名 密碼占位符 UID GID 描述信息 家目錄 shell
create database studb;
create table studb.user(
name char(50),
password char(1),
UID int(2),
GID int(2),
comment varchar(100),
homedir char(100),
shell char(25)
)engine=innodb;
select * from studb.user;
load data infile '目錄/文件名' into table '庫.表名' fields terminated by "字段間隔符號" lines terminated by "行間隔符號"
查看默認使用目錄及目錄是否存在
mysql> show variables like "secure_file_priv";
+------------------------------+-------------------------------------------+
| Variable_name | Value |
+-------------------------------+-----------------------------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+-------------------------------+------------------------------------------+
[root@mysql4-1 ~]# cp /etc/passwd /var/lib/mysql-files/
[root@mysql4-1 ~]# setenforce 0
mysql> load data infile '/var/lib/mysql-files/passwd' into table user fields terminated by ":" lines terminated by "\n";
Query OK, 44 rows affected (0.04 sec)
Records: 44 Deleted: 0 Skipped: 0 Warnings: 0
修改默認使用目錄
[root@mysql4-1 ~]# mkdir /myfile
[root@mysql4-1 ~]# chown mysql /myfile/
[root@mysql4-1 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myfile"
[root@mysql4-1 ~]# systemctl restart mysqld
mysql> show variables like "secure_file_priv";
+-------------------------------+-------------------+
| Variable_name | Value |
+-------------------------------+------------------+
| secure_file_priv | /myfile/ |
+--------------------------------+-----------------+
1 row in set (0.00 sec)
數據導出：把表記錄存儲到系統
sql 查詢 into outfile "目錄/文件名";
sql 查詢 into outfile "目錄/文件名" fields terminated by "字段間隔符號" lines terminated by "行間隔符號"；
mysql> select name,UID from user limit 5 into outfile "/myfile/user1.txt";
[root@mysql4-1 ~]# ls /myfile/
user1.txt
[root@mysql4-1 ~]# cat /myfile/user1.txt #默認字段間隔符號為<tab> 默認行間隔符號"\n"
root0
bin1
daemon2
adm3
lp4
mysql> select name,UID from user limit 5 into outfile "/myfile/user2.txt" fields terminated by "#" lines terminated by ":";
[root@mysql4-1 ~]# ls /myfile/
user1.txt user2.txt
[root@mysql4-1 ~]# cat /myfile/user2.txt
root#0:bin#1:daemon#2:adm#3:lp#4:
用戶授權 grant
就是在數據庫服務器添加新的連接用戶
grant 權限列表 on 庫名 to 用戶@"客戶端地址" identified by '密碼' [ with grant option ];
mysql> grant all on *.* to root@192.168.4.2 identified by '123456' with grant option;
權限的表示方式： all(所以權限), usage(沒有權限), select,update(name,age),delete
庫名的表示方式： 庫名.表名 庫名.* *.*
用戶名 自定義
客戶端地址表示方式： 192.168.4.117(一臺機器) 192.168.2.%（一個網段）
identified by '密碼' #登錄密碼
with grant option #可以有授權權限 可選項
客戶端測試授權
which mysql
mysql -h數據庫服務器ip -u用戶名 -p密碼
[root@localhost ~]# mysql -h292.168.4.1 -uroot -p123456
select @@hostname;
mysql> select @@hostname;
+---------------------+
| @@hostname |
+---------------------+
| mysql4-1 |
+---------------------+
1 row in set (0.00 sec)
select user();
mysql> select user();
+--------------------------------+
| user() |
+--------------------------------+
| root@192.168.4.2 |
+--------------------------------+
1 row in set (0.00 sec)
show grants;
mysql> show grants;
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.4.2 |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.4.2' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
允許從網站服務器上使bbsuser用戶連接 密碼時123456 只對bbsdb小的所以表有完全權限
mysql> grant all on bbsdb.* to bbsuser@192.168.4.3 identified by '123456';
MySQL [(none)]> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for bbsuser@192.168.4.3 |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bbsuser'@'192.168.4.3' |
| GRANT ALL PRIVILEGES ON `bbsdb`.* TO 'bbsuser'@'192.168.4.3' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL [(none)]> create database bbsdb;
Query OK, 1 row affected (0.00 sec)
運行admin用戶在數據庫服務器本機登錄 密碼123456 只有查詢記錄權限
mysql> grant select on *.* to admin@localhost identified by '123456';
授權信息存儲子授權庫mysql下的表里
mysql> use mysql;
mysql> show tables;
user 已有的授權用戶信息
db授權用戶對庫的訪問權限
tables_priv授權用戶對表的訪問權限
columns_priv 授權用戶對表中字段的訪問權限
查看服務器上有哪些授權用戶
mysql> select user,host from mysql.user;
+-------------------+-----------------------+
| user | host |
+-------------------+-----------------------+
| root | 192.168.4.2 |
| bbsuser | 192.168.4.3 |
| admin | localhost |
| mysql.sys | localhost |
| root | localhost |
+-------------------+-----------------------+
5 rows in set (0.00 sec)
mysql> show grants for bbsuser@192.168.4.3;
+---------------------------------------------------------------------------------------------------------------+
| Grants for bbsuser@192.168.4.3 |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bbsuser'@'192.168.4.3' |
| GRANT ALL PRIVILEGES ON `bbsdb`.* TO 'bbsuser'@'192.168.4.3' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
查看以有的授權用戶對服務器上庫的權限
mysql> select user,host,db from mysql.db;
+-------------------+-----------------------+------------+
| user | host | db |
+-------------------+-----------------------+------------+
| bbsuser | 192.168.4.3 | bbsdb |
| mysql.sys | localhost | sys |
+-------------------+-----------------------+------------+
2 rows in set (0.01 sec)
查看以有的授權用戶對服務器上庫中的表的權限
mysql> select host,user,db,table_name from mysql.tables_priv;
+-------------------+-------------------+----------+---------------------+
| host | user | db | table_name |
+-------------------+-------------------+----------+--------------------+
| localhost | mysql.sys | sys | sys_config |
+-------------------+--------------------+----------+-------------------+
授權用戶登錄服務器后，修改登錄密碼
set password=password("123456");
管理員重置授權用戶登錄密碼
set password for 用戶名@客戶端地址
mysql> set password for bbsuser@192.168.4.3=password('abc123');
權限撤銷 revokel
revokel 權限列表 on 庫名 for 用戶名@'客戶端地址'；
刪除授權用戶
drop user 用戶名@"客戶端地址"
查看授權用戶權限
show grants for 用戶名@"客戶端地址"
mysql> show grants for root@192.168.4.2;
撤銷root用戶在192.168.4.2主機登錄的授權權限
mysql> revoke grant option on *.* from root@192.168.4.2
撤銷root用戶在192.168.4.2主機登錄的刪除記錄和修改記錄的權限
mysql> revoke update,delete on *.* from roo
撤銷root用戶剩于所以的權限
mysql> revoke all on *.* from root@192.168.4.2;
也可以通過修改表記錄的方式撤銷用戶的權限
mysql> select * from mysql.db where db='bbsdb' and user='bbsuser' and host='192.168.4.3'\G;
修改在對應表中的記錄信息
mysql> update mysql.db set delete_priv="N",Drop_priv="N" where db='bbsdb' and user='bbsuser' and host='192.168.4.3';
mysql> flush privileges;
mysql 優化：
數據庫服務器響應客戶請求特別慢，可能是由于那些原因造成的，如何排除，請說出你的處理思路
1.網絡帶寬窄 測速軟件 花錢買帶寬
2.硬件配置低
CPU 內存 硬盤 使用率
核數 容量大 轉速 15000/秒
cpu 使用率
[root@mysql12 ~]# uptime
20:18:23 up 6 min, 1 user, load average: 0.08, 0.13, 0.09
負載
內存 使用率
[root@mysql12 ~]# free -m
total used free shared buff/cache available
Mem: 993 282 463 6 246 556
Swap: 2047 0 2047
磁盤 使用率
[root@mysql12 ~]# top
top - 20:20:52 up 8 min, 1 user, load average: 0.01, 0.08, 0.07
Tasks: 117 total, 2 running, 115 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.3 us, 0.0 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 1016916 total, 435968 free, 327600 used, 253348 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 531780 avail Mem
wa 百分比越大 等待寫入或讀取磁盤的越多
3. 提供服務的軟件版本低導致升級服務軟件版本
查看服務運行時，參數的值
查看參數值
mysql> show variables like "%關鍵字%"
修改參數值
命令行修改
set 變量名=值
set global 變量名=值#全局
永久修改
vim /etc/my.cnf
變量名=值
：wq
systemclt restart mysqld
并發連接數
mysql> show variables like "%connect%";
+------------------------------------------------------------------------------------+------------------------------+
| Variable_name | Value |
+------------------------------------------------------------------------------------+------------------------------+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| performance_schema_session_connect_attrs_size | 512 |
+------------------------------------------------------------------------------------+------------------------------+
mysql> show variables like "%max_connections%";
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| max_connections | 151 |#并發連接數最大151
+------------------------------+------------+
1 row in set (0.01 sec)
mysql> set GLOBAL max_connections=300;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%max_connections%";
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| max_connections | 300 |
+------------------------------+------------+
最大連接數/并發連接數 約等于 0.85
mysql> show global status like "Max_used_connections";
+---------------------------------------+------------+
| Variable_name | Value |
+---------------------------------------+------------+
| Max_used_connections | 1 |
+---------------------------------------+------------+
mysql> show processlist;#查看當前正在連接的
+------+-----------------------+-------------------+-----------+---------------+-----------+---------------------------------------------------------------------------------------------------+---------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------------+-------------------+-----------+---------------+-----------+---------------------------------------------------------------------------------------------------+---------------------------------+
| 1 | system user | | NULL | Connect | 1961 | Connecting to master | NULL |
| 2 | system user | | NULL | Connect | 1961 | Slave has read all relay log; waiting for more updates | NULL |
| 5 | root | localhost | NULL | Query | 0 | starting | show processlist |
+------+-----------------------+-------------------+-----------+---------------+-----------+---------------------------------------------------------------------------------------------------+---------------------------------+
超時時間
mysql> show variables like "%timeout%";
connect_timeout tcp三次握手的超時時間 超時時間太長 線程繼續 pid號不能收回 內存被占用 超時時間太短 服務端會重復生成多個線程響應一次請求
wait_timeout連接建立后等待命令執行的超時時間(等待關閉連接的不活動超時時間)
重復使用的線程的數量
mysql> show variables like "%size%";
thread_cache_size 可以重復使用保存在緩存中線程數
多個線程同時打開表的數量
mysql> show variables like "%cache%";
table_open_cache 所有線程同時打開表的數量
查詢緩存設置
mysql> show variables like "query_cache%";
query_cache_type = 0|1|2
0不允許存放
1只要查詢結果不超過限制都可以存放到查詢緩存里
2明確指定要把查詢結果存放到緩存里，才存
mysql> show global status like "qcache%";
Qcache_inserts在查詢緩存中查找一次 就自加一
Qcache_hits在查詢緩存中查找到一次 就自加一
4. 程序員編寫的訪問數據的查詢語句復雜，導致處理速度慢
啟用慢查詢日志文件，記錄超過指定時間顯示查詢結果得命令
mysql 支持四種日志文件：
binlog 日志
錯誤日志log-error=/var/log/mysqld.log#默認啟用
查詢日志記錄所有的sql操作
選項
general-log
general-log-file=文件名 #自定義日志文件
#vim /etc/my.cnf
[mysqld]
general-log
：wq
#systemctl restart mysqld
[root@mysql12 ~]# ls /var/lib/mysql
mysql12.log
[root@mysql12 ~]# mysql -uroot -p123456
mysql> show databases;
[root@mysql12 mysql]# tail -f mysql12.log
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2018-01-02T03:29:48.534719Z 3 QuerySELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%partitioned%';
2018-01-02T03:30:55.132492Z 5 Connectroot@localhost on using Socket
2018-01-02T03:30:55.132850Z 5 Queryselect @@version_comment limit 1
2018-01-02T03:31:15.772203Z 5 Queryshow databases
2018-01-02T03:31:59.756227Z 5 Quit
慢查詢日志
選項
slow-query-log 啟用慢查詢日志
slow-query-log-file=文件名#自定義日志文件
long-query-time 超過指定秒數（默認10秒）才被記錄
long-queries-not-using-indexes記錄未使用索引的查詢
#vim /etc/my.cnf
[mysqld]
slow-query-log
：wq
#systemctl restart mysqld
[root@mysql12 ~]# ls /var/lib/mysql
mysql12-slow.log
mysql> select sleep(10);
[root@mysql12 mysql]# cat mysql12-slow.log
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2018-01-02T03:27:33.280720Z
# User@Host: root[root] @ localhost [] Id: 6
# Query_time: 10.000291 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1514863653;
select sleep(10);
5. 網絡拓撲結構不合理，有數據傳輸瓶頸
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
