mysql資料庫-運維合集
RDBMS 術語
- 資料庫: 資料庫是一些關聯表的集合
- 數據表: 表是數據的矩陣。在一個資料庫中的表看起來像一個簡單的電子表格。
- 表頭(header): 每一列的名稱;
- 列row: 一列(數據元素) 包含了相同類型的數據, 例如郵政編碼的數據。每一行用來描述某物的具體資訊;
- 行col：一行（=元組，或記錄）是一組相關的數據，例如一條用戶訂閱的數據。
- 值value: 行的具體資訊, 每個值必須與該列的數據類型相同;
- 冗餘：存儲兩倍數據，冗餘降低了性能，但提高了數據的安全性。
- 主鍵：主鍵是唯一的。一個數據表中只能包含一個主鍵。你可以使用主鍵來查詢數據。
- 鍵(key): 表中用來識別某個特定的人\物的方法, 鍵的值在當前列中具有唯一性。
- 外鍵：外鍵用於關聯兩個表。
- 複合鍵：複合鍵（組合鍵）將多個列作為一個索引鍵，一般用於複合索引。
- 索引：使用索引可快速訪問資料庫表中的特定資訊。索引是對資料庫表中一列或多列的值進行排序的一種結構。類似於書籍的目錄。
- 參照完整性: 參照的完整性要求關係中不允許引用不存在的實體。與實體完整性是關係模型必須滿足的完整性約束條件，目的是保證數據的一致性。
整刪改查操作
庫操作
create databae dbname;
mysqladmin -u root -p create DBNAME
use dbname;
select database()； #查詢當前正在使用的資料庫名稱
drop database dbname;
表操作
# 創建表
# CREATE TABLE table_name (column_name column_type);
create table mytable(name varchar(20),sex(char(1),birth date);
CREATE TABLE IF NOT EXISTS `test_tb`(
`tb_id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `tb_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
show tables;
DROP TABLE table_name;
# 插入數據
INSERT INTO test_tb
(title,author, submission_date)
VALUES
("MySQL", "LESSON", NOW());
# 查詢數據
select * from test_tb;
SELECT * from test_tb WHERE title='MySQL';
# 更新數據
UPDATE test_tb SET title='python' WHERE id=1;
# 刪除數據
DELETE FROM test_tb WHERE runoob_id=1;
# 查看錶結構
SHOW COLUMNS FROM test_tb;
SHOW CREATE TABLE test_tb;
帳號與授權
create user 'admin@'%' identified by '123456'; #創建遠程管理員賬戶
set password for admin=password('123456');
rename user admin to guest;
drop user guest; show grants for guest;
grant all on mysql.* to guest;
grant all privileges on *.* to 'admin'@'%';
update user set password=password('123456') where user='root' and host='localhost';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON dbname.* TO 'admin'@'%' IDENTIFIED BY '123456';
revoke select on mysql.* from admin; #撤銷許可權
revoke all on mysql.* from jifei; alter user root@'localhost' identified by 'root';
alter user root@'localhost' identified by 'root';
FLUSH PRIVILEGES; #用於資料庫用戶資訊更新後,重啟mysql伺服器也可以
SELECT host, user, password FROM mysql.user WHERE user = 'admin';
#修改不同客戶端連接的限制 Client does not support authentication protocol requested by server；
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
匹配符(條件查詢)
> 、< 、<= 、>= 、= 、<>
BETWEEN...AND
IN( 集合)
LIKE 模糊查詢
_ 單個任意字元
% 多個任意字元
IS NULL
and 或 &&
or 或 ||
not 或 !
MySQL三大類數據類型
數字、日期\時間、字元串, 這三大類中又更細緻的劃分了許多子類型
數字類型
整數: tinyint、smallint、mediumint、int、bigint
浮點數: float、double、real、decimal
日期和時間: date、time、datetime、timestamp、year
字元串類型
字元串: char、varchar
文本: tinytext、text、mediumtext、longtext
二進位(可用來存儲圖片、音樂等): tinyblob、blob、mediumblob、longblob
函數
MySQL函數用來實現資料庫操作的一些高級功能, 這些函數大致分為以下幾類: 字元串函數、數學函數、日期時間函數、搜索函數、加密函數、資訊函數
其他操作
查看資料庫的佔用空間大小
use information_schema;
#查看所有資料庫大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data_size from tables;
#查看home庫的大小 #查看home庫下的members表的大小
SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESCselect concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';
# 查看所有資料庫容量大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';
#查看所有資料庫各表容量大小
select table_schema as '資料庫', sum(table_rows) as '記錄數', sum(truncate(data_length/1024/1024, 2)) as '數據容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from
information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc;
select table_schema as '資料庫', table_name as '表名', table_rows as '記錄數',truncate(data_length/1024/1024, 2) as '數據容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables order by data_length desc, index_length desc;
開啟慢查詢
show variables like '%version%'; #顯示當前資料庫中與版本號相關的東西
slow_query_log 的值為ON為開啟慢查詢日誌，OFF則為關閉慢查詢日誌
slow_query_log_file 的值是記錄的慢查詢日誌到文件中（注意：默認名為主機名.log，慢查詢日誌是否寫入指定文件中，需要指定慢查詢的輸出日誌格式為文件，相關命令為：show variables like 『%log_output%』；去查看輸出的格式）
long_query_time 指定了慢查詢的閾值，即如果執行語句的時間超過該閾值則為慢查詢語句，默認值為10秒
log_queries_not_using_indexes 如果值設置為ON，則會記錄所有沒有利用索引的查詢（注意：如果只是將log_queries_not_using_indexes設置為ON，而將slow_query_log設置為OFF，此時該設置也不會生效，即該設置生效的前提是slow_query_log的值設置為ON），一般在性能調優的時候會暫時開啟
show variables like '%query%';
show global status like 『%slow%』; #查詢當前慢查詢的語句的個數
select sleep(1); #製造慢查詢語句
set global slow_query_log=1; #開啟慢查詢功能，1是開啟，0是關閉
set global long_query_time=1; #慢查詢時間設置為1秒，5.6之後允許設置少於1秒，例如0.1秒
set global slow_query_log_file='/tmp/slow_querys.log';
set global log_output='TABLE'; #設置慢查詢記錄到表中
#查看零時慢查詢功能設置是否成功
show variables like 'long%';
show variables like 'slow%';
mysqldump slow -s c -t 10 `/usr/local/mysql/data/community-dev3-slow.log`
狀態查詢
select user(); #查詢當前登陸的用戶
show engine innodb status; #看看InnoDB所有的數據都已經同步到磁碟上去了
how status like '%threads%'; #查看連接數
show variables like '%connection%';
mysql -uroot -p123456 -h127.0.0.1 hellodb -e show tables
字符集設置
遵循的標準：資料庫，表，欄位和頁面或文本的編碼碼統一
show variables like '%character%'; 查看資料庫字符集
/etc/my.cnf default-character-set = utf8 修改字符集為utf8
alter database mydb character set utf8;
create database mydb character set utf8;
臨時設置資料庫字符集
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;
忘記密碼重置
忘記root密碼更改配置文件採取使用免密啟動，更改密碼後再還原重啟，跳過許可權表的限制，不用密碼驗證，直接登錄數據my.cnf在 [mysqld] 中添加 skip-grant-tables
# 1 my.cnf [mysqld] 中添加 skip-grant-tables欄位
# 2 重啟DB，重置密碼
service mysqld restart
mysql -h localhost
use mysql;
update user set password = password('new-password') where user='root';
update mysql.user set authentication_string=password('123456') where user='root' and host='localhost';
flush privileges;
# 3 刪除skip-grant-tables重啟mysql服務即可
