MySQL權(quán)限與備份管理方式詳解
MariaDB 是一個(gè)多用戶數(shù)據(jù)庫,具有功能強(qiáng)大的訪問控制系統(tǒng),可以為不同用戶指定允許的權(quán)限.MariaDB用戶可以分為普通用戶和ROOT用戶.ROOT用戶是超級(jí)管理員,擁有所有權(quán)限,包括創(chuàng)建用戶、刪除用戶和修改用戶的密碼等管理權(quán)限,普通用戶只擁有被授予的各種權(quán)限.
MariaDB 權(quán)限概述
MariaDB 服務(wù)器通過權(quán)限表來控制用戶對(duì)數(shù)據(jù)庫的訪問,權(quán)限表存放在MariaDB的數(shù)據(jù)庫中,由MySQL_install_db腳本初始化，,存儲(chǔ)賬戶權(quán)限信息表主要有:user、db、host、table_priv,columns_priv和procs_priv.
USER表
user表是MariaDB中最重要的一個(gè)權(quán)限表,記錄允許連接到服務(wù)器的賬號(hào)信息,里面的權(quán)限是全局的,MariaDB中user表一共有42個(gè)字段,用戶列可分為4類,分別是用戶列,權(quán)限列,安全列和資源控制列,下面我們介紹介個(gè)常用列的作用.
| 字段名 | 數(shù)據(jù)類型 | 默認(rèn)值 |
|---|---|---|
| Host | char | NULL |
| User | char | NULL |
| Password | char | NULL |
| ssl_cipher | blob | NULL |
| x509_issuer | blob | NULL |
| x509_subject | blob | NULL |
| max_questions | int | 0 |
| max_updates | int | 0 |
| max_connections | int | 0 |
| max_user_connections | int | 0 |
用戶列:
user表的用戶列包括Host、User、Password,分別表示主機(jī)名、用戶名和密碼.其中User和Host為User表的聯(lián)合主鍵,當(dāng)用戶與服務(wù)器之間建立連接時(shí),輸入的賬戶信息中的用戶名稱、主機(jī)名和密碼必須匹配User表中對(duì)應(yīng)的字段,只有3個(gè)值都匹配的時(shí)候,才允許連接建立.這3個(gè)字段的值就是創(chuàng)建賬戶時(shí)保存的賬戶信息,修改用戶密碼時(shí),實(shí)際就是修改user表的Password字段的值.
權(quán)限列:
權(quán)限列的字段決定了用戶的權(quán)限,描述了在全局范圍內(nèi)允許對(duì)數(shù)據(jù)和數(shù)據(jù)庫進(jìn)行的操作.包括查詢權(quán)限、修改權(quán)限等普通權(quán)限,還包括了關(guān)閉服務(wù)器、超級(jí)權(quán)限和加載用戶等高級(jí)權(quán)限,普通權(quán)限用于操作數(shù)據(jù)庫,高級(jí)權(quán)限用于數(shù)據(jù)庫管理.
安全列:
安全列只有6個(gè)字段,其中兩個(gè)是SSL相關(guān)的,兩個(gè)是 x509 相關(guān)的,另外兩個(gè)是授權(quán)插件相關(guān)的,SSL用于加密, x509 標(biāo)準(zhǔn)可用于標(biāo)識(shí)用戶,Plugin字段標(biāo)識(shí)可以用于驗(yàn)證用戶身份的插件,如果該字段為空,服務(wù)器使用內(nèi)建授權(quán)驗(yàn)證機(jī)制驗(yàn)證用戶身份.
資源控制列:
資源控制列用來限制用戶使用的資源,包括以下4個(gè)字段,分別為:
- max_questions:用戶每小時(shí)允許執(zhí)行查詢次數(shù).
- max_updates:用戶每小時(shí)允許執(zhí)行更新次數(shù).
- max_connections:用戶每小時(shí)允許執(zhí)行的連接次數(shù).
- max_user_connection:用戶允許同時(shí)建立的連接次數(shù).
DB權(quán)限表
DB表和HOST表,在數(shù)據(jù)庫中非常重要的權(quán)限表,DB表中存儲(chǔ)了用戶對(duì)某個(gè)數(shù)據(jù)庫的操作權(quán)限,決定用戶能從哪個(gè)主機(jī)存取哪個(gè)數(shù)據(jù)庫.host表中存儲(chǔ)了某個(gè)主機(jī)對(duì)數(shù)據(jù)庫的操作權(quán)限,配合db權(quán)限表對(duì)給定主機(jī)上數(shù)據(jù)庫級(jí)操作權(quán)限做更細(xì)致的控制.這個(gè)權(quán)限表不受GRANT 和 REVOKE語句的影響,db表比較常用,host表一般很少使用.db表和host表結(jié)構(gòu)相似,字段大致可以分為兩類:用戶列和權(quán)限列.
select_priv,Insert_priv,update_priv,delete_priv,Create_priv,Drop_priv,Alter_priv,Grant_priv
| 字段名 | 數(shù)據(jù)類型 | 默認(rèn)值 |
|---|---|---|
| Host | char | NULL |
| DB | char | NULL |
| USER | char | NULL |
| select_priv | enum(N,Y) | NO |
| insert_priv | enum(N,Y) | NO |
| update_priv | enum(N,Y) | NO |
| delete_priv | enum(N,Y) | NO |
| create_priv | enum(N,Y) | NO |
| drop_priv | enum(N,Y) | NO |
| alter_priv | enum(N,Y) | NO |
| grant_priv | enum(N,Y) | NO |
DB表用戶列有3個(gè)字段,分別是Host、User、Db標(biāo)識(shí)從某個(gè)主機(jī)連接某個(gè)用戶對(duì)某個(gè)數(shù)據(jù)庫的操作權(quán)限,這3個(gè)字段的組合構(gòu)成了db表的主鍵.host表不存儲(chǔ)用戶名稱,用戶列只有2個(gè)字段,分別是Host和DB,表示從某個(gè)主機(jī)連接的用戶對(duì)某個(gè)數(shù)據(jù)庫的操作權(quán)限,其主鍵包括Host和Db兩個(gè)字段,host很少用到,一般情況下db表就可以滿足權(quán)限控制需求了.
MariaDB 賬戶管理
MariaDB提供了許多語句來管理用戶賬號(hào),這些語句可以用來管理包括登陸和退出MariaDB服務(wù)器,創(chuàng)建用戶,刪除用戶,密碼管理和權(quán)限管理等,MariaDB數(shù)據(jù)庫的安全性,需要通過賬戶管理來保證.
查詢?cè)诰€用戶
本地查詢: 當(dāng)我們本地登錄到數(shù)據(jù)庫時(shí),可以使用本地查詢,查詢SQL語句如下.
MariaDB [(none)]> show processlist; +----+---------+-----------+------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+---------+-----------+------+---------+------+-------+------------------+----------+ | 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | | 5 | lyshark | localhost | NULL | Sleep | 4 | | NULL | 0.000 | +----+---------+-----------+------+---------+------+-------+------------------+----------+ 2 rows in set (0.08 sec)
遠(yuǎn)程查詢: 如果在遠(yuǎn)程終端機(jī)上查詢遠(yuǎn)程數(shù)據(jù)庫,前提是數(shù)據(jù)庫開啟了遠(yuǎn)程授權(quán)我們可以使用以下SQL語句.
[root@localhost ~]# mysqladmin -uroot -p123 processlist +----+---------+-----------+----+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+---------+-----------+----+---------+------+-------+------------------+----------+ | 5 | lyshark | localhost | | Sleep | 154 | | | 0.000 | | 11 | root | localhost | | Query | 0 | | show processlist | 0.000 | +----+---------+-----------+----+---------+------+-------+------------------+----------+ [root@localhost ~]#
查全部用戶: 我們通過構(gòu)建Select語句查詢指定字段(Host,User,Password),查詢mysql.user這個(gè)數(shù)據(jù)表,SQL語句如下.
MariaDB [none]> select Host,User,Password from mysql.user; +-----------+---------+-------------------------------------------+ | Host | User | Password | +-----------+---------+-------------------------------------------+ | localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | ::1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | localhost | lyshark | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +-----------+---------+-------------------------------------------+ 4 rows in set (0.01 sec)
去重查詢: 通過使用distinct命令使查詢結(jié)果不重復(fù),自動(dòng)過濾重復(fù)的記錄.
MariaDB [(none)]> select distinct User,Password from mysql.user; +---------+-------------------------------------------+ | User | Password | +---------+-------------------------------------------+ | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | lyshark | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +---------+-------------------------------------------+ 2 rows in set (0.00 sec)
新建普通用戶
創(chuàng)建新用戶,必須有相應(yīng)的權(quán)限來執(zhí)行創(chuàng)建操作.在MariaDB數(shù)據(jù)庫中,有兩種方式創(chuàng)建新用戶:一種是使用CREATE USER或GRANT語句,另一種是直接操作MariaDB授權(quán)表,最好的方法是使用GRANT語句,因?yàn)檫@樣更精確,如果使用create語句創(chuàng)建用戶后用戶無權(quán)限,需要手動(dòng)添加權(quán)限,而直接使用grant語句可以一步到位.
使用create user語句創(chuàng)建新用戶:
1.使用create user創(chuàng)建一個(gè)用戶,名稱為jeffrey,密碼是mypass,指定開啟%遠(yuǎn)程權(quán)限.
MariaDB [(none)]> create user 'jeffrey'@'%' identified by 'mypass'; Query OK, 0 rows affected (0.05 sec)
2.接著繼續(xù)創(chuàng)建一個(gè)新用戶jeffreys,密碼是mypass,并指定使用localhost本地權(quán)限.
MariaDB [(none)]> select password('mypass');
+-------------------------------------------+
| password('mypass') |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> create user 'jeffreys'@'localhost' identified by password '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
Query OK, 0 rows affected (0.00 sec)使用grant user語句創(chuàng)建新用戶:
使用grant語句創(chuàng)建一個(gè)新用戶myuser,密碼是123123,并授予用戶對(duì)所有表的select和update權(quán)限,SQL語句如下:
MariaDB [(none)]> grant select,update ON *.* TO 'myuser'@'localhost' identified by '123123'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> select Host,User,Select_priv,Update_priv from mysql.user where user='myuser'; +-----------+--------+-------------+-------------+ | Host | User | Select_priv | Update_priv | +-----------+--------+-------------+-------------+ | localhost | myuser | Y | Y | +-----------+--------+-------------+-------------+ 1 row in set (0.00 sec)
刪除普通用戶
使用drop user語句刪除用戶:
MariaDB [(none)]> select distinct User,Host from mysql.user; +---------+-----------+ | User | Host | +---------+-----------+ | root | 127.0.0.1 | | root | ::1 | | lyshark | localhost | | root | localhost | +---------+-----------+ 4 rows in set (0.07 sec) MariaDB [(none)]> drop user lyshark@"localhost"; Query OK, 0 rows affected (0.37 sec) MariaDB [(none)]> select distinct User,Host from mysql.user; +------+-----------+ | User | Host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | root | localhost | +------+-----------+ 3 rows in set (0.00 sec) MariaDB [(none)]>
使用delete語句刪除用戶:
MariaDB [(none)]> delete from mysql.user where host='localhost' and user='myuser'; Query OK, 1 row affected (0.01 sec)
修改用戶密碼
修改自身密碼: 修改自己用戶的用戶名和密碼.
MariaDB [(none)]> set password=password("123123");
Query OK, 0 rows affected (0.00 sec)修改指定用戶的密碼: 修改lyshark用戶授權(quán)方式為localhost的密碼為123123
MariaDB [(none)]> set password for "lyshark"@"localhost"=password("123123");
Query OK, 0 rows affected (0.00 sec)ROOT密碼找回
1.關(guān)閉MariaDB數(shù)據(jù)庫
[root@localhost ~]# systemctl stop mariadb [root@localhost ~]# systemctl status mariadb
2.修改MariaDB的主配置文件,在Mysqld區(qū)域添加skip-grant-table語句,保存退出
[root@localhost etc]# ll /etc/my.cnf -rw-r--r--. 1 root root 570 6月 8 2017 /etc/my.cnf [root@localhost etc]# [root@localhost etc]# vim /etc/my.cnf [mysqld] skip-grant-table #添加我就可以了 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks
3.重啟MariaDB數(shù)據(jù)庫
[root@localhost ~]# systemctl restart mariadb [root@localhost ~]# systemctl status mariadb
4.免密碼登陸數(shù)據(jù)庫,并執(zhí)行修改數(shù)據(jù)庫的SQL語句
[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> update mysql.user set password=password("123") where user="root";
MariaDB [(none)]> Ctrl-C -- exit!5.修改完成后將主配置文件的skip-grant-table屬性去掉,重啟數(shù)據(jù)庫即可,下次使用密碼登陸即可
[root@localhost ~]# vim /etc/my.cnf [root@localhost ~]# systemctl restart mariadb [root@localhost ~]# mysql -uroot -p123
MariaDB 權(quán)限管理
權(quán)限管理主要是對(duì)登錄到MariaDB的用戶進(jìn)行權(quán)限驗(yàn)證所有用戶的權(quán)限都存儲(chǔ)在MariaDB的權(quán)限表中,不合理的權(quán)限規(guī)劃會(huì)給MariaDB服務(wù)器帶來安全隱患,數(shù)據(jù)庫管理員要對(duì)所有用戶的權(quán)限進(jìn)行合理規(guī)劃管理.
查看權(quán)限
查詢所有權(quán)限: 查詢數(shù)據(jù)庫中所有用戶列表和權(quán)限信息.
MariaDB [(none)]> select distinct concat("用戶:",user," 權(quán)限:",host," : ") as query from mysql.user;
+------------------------------------+
| query |
+------------------------------------+
| 用戶:root 權(quán)限:localhost : |
| 用戶:root 權(quán)限:127.0.0.1 : |
| 用戶:root 權(quán)限:::1 : |
| 用戶:lyshark 權(quán)限:localhost : |
| 用戶:lyshark 權(quán)限:% : |
+------------------------------------+
8 rows in set (0.46 sec)
MariaDB [(none)]>查詢指定權(quán)限: 查詢關(guān)于lyshark的所有權(quán)限信息
MariaDB [(none)]> show grants for lyshark; +----------------------------------------------+ | Grants for lyshark@% | +----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' | +----------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
查詢指定權(quán)限: 查詢lyshark用戶的遠(yuǎn)程%權(quán)限,和本地localhost權(quán)限
MariaDB [(none)]> show grants for "lyshark"@"%"; +----------------------------------------------+ | Grants for lyshark@% | +----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' | +----------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> show grants for "lyshark"@"localhost"; +----------------------------------------------------------------------------------------------------------------+ | Grants for lyshark@localhost | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'lyshark'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | +----------------------------------------------------------------------------------------------------------------+ 1 row in set (0.37 sec) MariaDB [(none)]>
授予權(quán)限
創(chuàng)建用戶并授權(quán): 創(chuàng)建wang用戶并給予%遠(yuǎn)程登陸的權(quán)限,并對(duì)所有數(shù)據(jù)庫全部授權(quán)
MariaDB [(none)]> grant all on *.* to "wang"@"%" identified by "123"; Query OK, 0 rows affected (0.15 sec) MariaDB [(none)]> show grants for "wang"@"%"; +--------------------------------------------------------------------------------------------------------------+ | Grants for wang@% | +--------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'wang'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | +--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
創(chuàng)建用戶并授權(quán): 創(chuàng)建用戶名wang1,并允許localhost本機(jī)訪問,對(duì)mysql庫中的所有表具有select查詢權(quán)限,密碼為123
MariaDB [(none)]> grant select on mysql.* to "wang1"@"localhost" identified by "123"; Query OK, 0 rows affected (0.36 sec) MariaDB [(none)]> show grants for "wang1"@"localhost"; +--------------------------------------------------------------------------------------------------------------+ | Grants for wang1@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'wang1'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT SELECT ON `mysql`.* TO 'wang1'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]>
創(chuàng)建用戶并授權(quán): 創(chuàng)建用戶名wang2,且可在任意主機(jī)%登陸,并對(duì)所有數(shù)據(jù)庫有(增刪改查)權(quán)限,密碼為123
MariaDB [(none)]> grant insert,delete,update,select on *.* to "wang2"@"%" identified by "123"; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show grants for "wang2"@"%"; +-------------------------------------------------------------------------------------------------------------------------------+ | Grants for wang2@% | +-------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'wang2'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | +-------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
創(chuàng)建用戶并授權(quán): 創(chuàng)建用戶名wang3,且只能在192.168.1.59上登陸,并對(duì)mysql數(shù)據(jù)庫有(查)權(quán)限,密碼為123
MariaDB [(none)]> grant select on mysql.* to "wang3"@"192.168.1.59" identified by "123"; Query OK, 0 rows affected (0.15 sec) MariaDB [(none)]> show grants for "wang3"@"192.168.1.59"; +-----------------------------------------------------------------------------------------------------------------+ | Grants for wang3@192.168.1.59 | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'wang3'@'192.168.1.59' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT SELECT ON `mysql`.* TO 'wang3'@'192.168.1.59' | +-----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.50 sec) MariaDB [(none)]>
創(chuàng)建用戶并授權(quán): 創(chuàng)建一個(gè)普通用戶wang4,且僅有mysql庫的(查)權(quán)限,密碼為123
MariaDB [(none)]> grant usage,select on mysql.* to "wang4"@"localhost" identified by "123"; Query OK, 0 rows affected (0.35 sec)
只授權(quán)用戶權(quán)限: 授權(quán)用戶wang4,對(duì)所有數(shù)據(jù)庫的全部權(quán)限,密碼123
MariaDB [(none)]> grant all privileges on *.* to "wang4"@"localhost" identified by "123"; Query OK, 0 rows affected (0.36 sec)
只授權(quán)用戶權(quán)限: 授權(quán)一個(gè)已存在賬號(hào)允許遠(yuǎn)程登陸最大權(quán)限
MariaDB [(none)]> grant all on *.* to "root"@"%"; Query OK, 0 rows affected (0.07 sec)
收回權(quán)限
收回用戶授權(quán): 撤銷lyshark用戶,對(duì)所有數(shù)據(jù)庫的遠(yuǎn)程%用戶權(quán)限
MariaDB [(none)]> show grants for lyshark; +----------------------------------------------+ | Grants for lyshark@% | +----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' | +----------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> revoke all on *.* from "lyshark"@"%"; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show grants for lyshark; +-------------------------------------+ | Grants for lyshark@% | +-------------------------------------+ | GRANT USAGE ON *.* TO 'lyshark'@'%' | +-------------------------------------+ 1 row in set (0.00 sec)
收回用戶授權(quán): 撤銷lyshark用戶的遠(yuǎn)程登陸權(quán)限
MariaDB [(none)]> revoke create on *.* from "lyshark"@"%"; Query OK, 0 rows affected (0.01 sec)
刷新權(quán)限: 修改完成以后,記得執(zhí)行權(quán)限的刷新操作.
MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec)
MariaDB 數(shù)據(jù)備份與恢復(fù)
盡管采取了一些管理措施來保證數(shù)據(jù)庫的安全,但是不確定的意外情況總是有可能造成數(shù)據(jù)的損失,例如意外的停電、管理員不小心的操作失誤都可能會(huì)造成數(shù)據(jù)的丟失,保證數(shù)據(jù)安全的最重要的一個(gè)措施是確保對(duì)數(shù)據(jù)進(jìn)行定期備份,如果數(shù)據(jù)庫中的數(shù)據(jù)丟失或者出現(xiàn)錯(cuò)誤,可以使用備份的數(shù)據(jù)進(jìn)行恢復(fù),這樣就盡可能地降低了意外原因?qū)е碌膿p失.
數(shù)據(jù)備份是理員非常重要的工作之一,系統(tǒng)意外崩潰或者硬件的損壞都可能導(dǎo)致數(shù)據(jù)庫的丟失,因此MariaDB管理員應(yīng)該定期地備份數(shù)據(jù)庫,使得在意外情況發(fā)生時(shí),盡可能減少損失.
數(shù)據(jù)備份還原
全局鎖定: 在備份數(shù)據(jù)庫之前,我們應(yīng)該先將數(shù)據(jù)庫設(shè)置為只讀模式,下面將設(shè)置全局為只讀模式.
MariaDB [(none)]> show global variables like "%read_only%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> set global read_only=1; #1是只讀,0是讀寫 Query OK, 0 rows affected (0.08 sec) MariaDB [(none)]> show global variables like "%read_only%"; #再次查詢讀寫狀態(tài) +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec)
表的鎖定: 在備份數(shù)據(jù)庫之前,我們應(yīng)該先將數(shù)據(jù)庫設(shè)置為只讀模式,下面將設(shè)置指定表為只讀模式.
MariaDB [(none)]> flush tables with read lock; #鎖定數(shù)據(jù)表 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> unlock tables; #解除表鎖定 Query OK, 0 rows affected (0.00 sec)
MySQLDump備份數(shù)據(jù): 通過內(nèi)置備份命令,來備份數(shù)據(jù)庫,以下是常用語法.
[root@localhost ~]# mysqldump -u root -p 數(shù)據(jù)庫名 >備份文件.sql #備份單個(gè)數(shù)據(jù)庫 [root@localhost ~]# mysqldump -u root -p 數(shù)據(jù)庫名 表名 >備份文件.sql #備份數(shù)據(jù)庫中單個(gè)表 [root@localhost ~]# mysqldump -u root -p --databases 數(shù)據(jù)庫名 >備份文件.sql #備份指定的數(shù)據(jù)庫 [root@localhost ~]# mysqldump -u root -p --all-databases >備份文件.sql #備份整個(gè)數(shù)據(jù)庫
MySQL還原數(shù)據(jù): 通過內(nèi)置備份命令,來還原數(shù)據(jù)庫,以下是常用語法.
[root@localhost ~]# mysql -u root -p 數(shù)據(jù)庫名 < 備份文件.sql #還原單個(gè)數(shù)據(jù)庫 [root@localhost ~]# mysql -u root -p 還原到的數(shù)據(jù)庫名 < 備份文件.sql #還原數(shù)據(jù)庫中單個(gè)表 [root@localhost ~]# mysql -u root -p < 備份文件.sql #還指定的數(shù)據(jù)庫 [root@localhost ~]# mysql -u root -p < 備份文件.sql #還原整個(gè)數(shù)據(jù)庫
MySQLHotcopy熱備份: 使用hotcopy完成一次熱備份.
[root@localhost ~]# mysqlhotcopy --flushlog -u="root" -p="123" --regexp=^l /root/ #以上參數(shù)解釋: -u 用戶名 -p 密碼 --regexp=^l 備份開頭是l的數(shù)據(jù)庫 /root 備份文件保存位置 [root@localhost ~]# ll 總用量 0 drwxr-x---. 2 mysql mysql 39 9月 15 04:31 lyshark
MySQLHotcopy冷恢復(fù): 使用hotcopy完成一次冷恢復(fù),冷恢復(fù)需要關(guān)閉數(shù)據(jù)庫.
[root@localhost ~]# systemctl stop mariadb [root@localhost ~]# cp -a /root/lyshrk /usr/local/mysql/data [root@localhost ~]# chown -R mysq.mysql /usr/local/mysql/data [root@localhost ~]# systemctl start mariadb
數(shù)據(jù)導(dǎo)入導(dǎo)出
select 語句導(dǎo)出:
MariaDB [none]> select * from 數(shù)據(jù)庫名稱.表名稱 INTO OUTFILE "/root/xxx.txt" [root@localhost ~]# cat /root/xxx.txt
mysql命令導(dǎo)出文件
[root@localhost ~]# mysql -uroot -p --execute="select * from 表名;" 數(shù)據(jù)庫名 > filename.txt [root@localhost ~]# mysql -uroot -p --verical --execute="select * from person;" 數(shù)據(jù)庫名稱 > /root/lyshark.txt [root@localhost ~]# mysql -uroot -p --html --execute="select * from person;" 數(shù)據(jù)庫名稱 > /root/lyshark.html [root@localhost ~]# mysql -uroot -p --xml --execute="select * from person;" 數(shù)據(jù)庫名稱 > /root/lyshark.xml
load data 導(dǎo)入數(shù)據(jù):
[root@localhost ~]# load data INFILE '/root/wang.txt' INTO TABLE 數(shù)據(jù)庫.表名稱; [root@localhost ~]# mysqlmport -uroot -p 數(shù)據(jù)庫名稱 filename.txt
MariaDB 日志配置與管理
MariaDB 日志記錄了MariaDB數(shù)據(jù)庫日常操作和錯(cuò)誤信息,MariaDB有不同類型的日志文件(各自存儲(chǔ)了不同類型的日志),從日志當(dāng)中可以查詢到MaraiDB數(shù)據(jù)庫的運(yùn)行情況、用戶操作、錯(cuò)誤信息等,可以為MariaDB管理和優(yōu)化提供必要的信息,對(duì)于MariaDB的管理工作而言,這些日志文件是不可缺少的.
MariaDB默認(rèn)分為以下4類,使用這些日志可查看MariaDB內(nèi)部發(fā)生的事情,4類分別是:
- 錯(cuò)誤日志: 記錄MaraiDB服務(wù)的啟動(dòng),運(yùn)行或停止MariaDB服務(wù)時(shí)出現(xiàn)的問題
- 查詢?nèi)罩? 記錄建立的客戶端連接和執(zhí)行的語句
- 二進(jìn)制日志: 記錄所有更改數(shù)據(jù)的語句,可以用于數(shù)據(jù)復(fù)制
- 慢查詢?nèi)罩? 記錄所有執(zhí)行時(shí)間超過默認(rèn)值的所有查詢或不適用索引的查詢
默認(rèn)情況下,所有日志創(chuàng)建于MariaDB數(shù)據(jù)目錄中,通過刷新日志,可以強(qiáng)制關(guān)閉和重新打開日志文件(或者在某些情況下切換到一個(gè)新的日志),當(dāng)執(zhí)行一個(gè)FLUSH LOGS語句或執(zhí)行mysqlladmin flush-logs或mysqladmin refresh時(shí),將刷新日志.
如果正使用MariaDB復(fù)制功能,在復(fù)制服務(wù)器上可以維護(hù)更多日志文件,這種日志稱為接替日志.啟動(dòng)日志功能會(huì)降低數(shù)據(jù)庫的性能,如果開啟慢查詢?nèi)罩緞t會(huì)占用大量的磁盤空間.
二進(jìn)制日志
二進(jìn)制日志主要記錄數(shù)據(jù)庫的變化,二進(jìn)制日志以一種有效的格式,并且是事務(wù)安全的方式包含更新日志中可用的所有信息,二進(jìn)制日志包含了所有更新了數(shù)據(jù)或者己經(jīng)潛在更新了數(shù)據(jù),語句以"事件"的形式保存,描述數(shù)據(jù)更改.
二進(jìn)制日志還包含關(guān)于每個(gè)更新數(shù)據(jù)庫的語句的執(zhí)行時(shí)間信息,它不包含沒有修改任何數(shù)據(jù)的語句,如果想要記錄所有語句,需要使用一般查詢?nèi)罩?使用二進(jìn)制日志的主要目的是最大可能地恢復(fù)數(shù)據(jù)庫,因?yàn)槎M(jìn)制日志包含備份后進(jìn)行的所有更新.
啟動(dòng)二進(jìn)制日志:
1.默認(rèn)情況下二進(jìn)制日志是關(guān)閉狀態(tài)的,可以通過修改數(shù)據(jù)庫配置文件來設(shè)置開啟日志.
[root@localhost ~]# vim /etc/my.cnf [mysqld] log-bin="/tmp" #設(shè)置開啟日志,也可不指定日志保存位置 expire_logs_days = 10 #設(shè)置日志自動(dòng)清理天數(shù) max_binlog_size = 100M #定義了單個(gè)文件的大小限制
2.添加完畢后,重啟數(shù)據(jù)庫進(jìn)程,即可打開二進(jìn)制日志啦.
[root@localhost ~]# systemctl restart mariadb [root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show variables like 'log_%'; +---------------------------------+------------------------------------------------- | Variable_name | Value +---------------------------------+------------------------------------------------- | log_bin | ON | log_bin_trust_function_creators | OFF | log_error | /var/log/mariadb/mariadb.log | log_output | FILE | log_queries_not_using_indexes | OFF | log_slave_updates | OFF | log_slow_filter | admin,filesort,filesort_on_disk,full_join, | log_slow_queries | OFF | log_slow_rate_limit | 1 | log_slow_verbosity | | log_warnings | 1 +---------------------------------+------------------------------------------------- 11 rows in set (0.01 sec)
查看二進(jìn)制日志:
1.可以使用show binary logs語句查看二進(jìn)制日志文件個(gè)數(shù)及文件名,SQL語句如下:
MariaDB [(none)]> show binary logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+ 1 row in set (0.02 sec)
可以看到當(dāng)前只有一個(gè)二進(jìn)制日志,日志文件的個(gè)數(shù)與MariaDB服務(wù)啟動(dòng)的次數(shù)相同,每次啟動(dòng)一次數(shù)據(jù)庫,將會(huì)產(chǎn)生一個(gè)新的日志文件.
2.也可以使用mysqlbinlog命令查看日志內(nèi)容,SQL語句如下:
[root@localhost ~]# mysqlbinlog mariadb-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; mysqlbinlog: File 'mariadb-bin.000001' not found (Errcode: 2) DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
刪除二進(jìn)制日志:
1.我們可以手動(dòng)刪除二進(jìn)制日志,通過使用reset master語句刪除所有日志,SQL語句如下:
[root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> reset master; Query OK, 0 rows affected (0.01 sec)
2.也可以單獨(dú)刪除指定的日志文件,如下刪除第一個(gè)日志文件,SQL代碼如下:
MariaDB [(none)]> show binary logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 264 | | mariadb-bin.000002 | 264 | | mariadb-bin.000003 | 245 | +--------------------+-----------+ 3 rows in set (0.00 sec) MariaDB [(none)]> purge master logs to "mariadb-bin.000001"; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show binary logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000002 | 264 | | mariadb-bin.000003 | 245 | +--------------------+-----------+ 3 rows in set (0.00 sec)
3.或者單獨(dú)刪除指定日期的日志文件,如下刪除2018/01/01以前所有日志,SQL代碼如下:
MariaDB [(none)]> purge master logs before "20180101"; Query OK, 0 rows affected (0.00 sec)
系統(tǒng)錯(cuò)誤日志
錯(cuò)誤日志包含了數(shù)據(jù)庫啟動(dòng)和停止時(shí),以及服務(wù)器在運(yùn)行過程中發(fā)生任何嚴(yán)重錯(cuò)誤時(shí)的相關(guān)信息,錯(cuò)誤日志對(duì)于數(shù)據(jù)庫異常排查,有很大的幫助.
開啟錯(cuò)誤日志:
1.默認(rèn)情況下二進(jìn)制日志是關(guān)閉狀態(tài)的,可以通過修改數(shù)據(jù)庫配置文件來設(shè)置開啟日志.
[root@localhost ~]# vim /etc/my.cnf [mysqld] log-error="/var/log/mariadb/mariadb.log"
2.添加完畢后,重啟數(shù)據(jù)庫進(jìn)程,即可打開二進(jìn)制日志啦.
[root@localhost ~]# systemctl restart mariadb [root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show variables like 'log_error'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | log_error | /var/log/mariadb/mariadb.log | +---------------+------------------------------+ 1 row in set (0.01 sec) [root@localhost ~]# cat /var/log/mariadb/mariadb.log |head -n 10 181224 20:28:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 181224 20:28:49 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 1622 ... 181224 20:28:49 InnoDB: The InnoDB memory heap is disabled 181224 20:28:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins 181224 20:28:49 InnoDB: Compressed tables use zlib 1.2.7 181224 20:28:49 InnoDB: Using Linux native AIO 181224 20:28:50 InnoDB: Initializing buffer pool, size = 128.0M 181224 20:28:50 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created!
刪除錯(cuò)誤日志:
[root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> flush logs; Query OK, 0 rows affected (0.01 sec)
慢查詢?nèi)罩?/h3>
慢查詢?nèi)罩臼怯涗洸樵儠r(shí)長超過指定時(shí)間的日志,慢查詢?nèi)罩局饕脕碛涗泩?zhí)行時(shí)間較長的查詢語句,通過慢查詢?nèi)罩?可以找出執(zhí)行時(shí)間較長、執(zhí)行效率較低的語句,然后進(jìn)行優(yōu)化.
開啟錯(cuò)誤日志:
1.默認(rèn)情況下二進(jìn)制日志是關(guān)閉狀態(tài)的,可以通過修改數(shù)據(jù)庫配置文件來設(shè)置開啟日志.
[root@localhost ~]# vim /etc/my.cnf [mysqld] log-slow-queries="/tmp" long_query_time=n
2.添加完畢后,重啟數(shù)據(jù)庫進(jìn)程,即可打開二進(jìn)制日志啦.
[root@localhost ~]# systemctl restart mariadb [root@localhost ~]# mysql
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
win11設(shè)置mysql開機(jī)自啟的實(shí)現(xiàn)方法
本文主要介紹了win11設(shè)置mysql開機(jī)自啟的實(shí)現(xiàn)方法，要通過命令行方式設(shè)置，具有一定的參考價(jià)值，感興趣的可以了解一下2024-03-03
這篇文章主要介紹了MySQL 5.7.27詳細(xì)下載安裝配置教程,本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-08-08
這篇文章主要介紹了MySQL之InnoDB中的redo日志類型,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2025-06-06
這篇文章主要給大家介紹了關(guān)于MySQL中int最大值的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02
這篇文章主要介紹了MySQL truncate table語句的使用，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
本文詳細(xì)介紹了MySQL中的游標(biāo),包括其定義、使用步驟及案例,以及觸發(fā)器的概念、創(chuàng)建和管理,游標(biāo)用于逐行處理查詢結(jié)果,而觸發(fā)器則在數(shù)據(jù)操作后自動(dòng)執(zhí)行特定操作,提升數(shù)據(jù)庫操作的靈活性和自動(dòng)化,感興趣的朋友跟隨小編一起看看吧2025-12-12
window下mysql 8.0.15 winx64安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了window下mysql 8.0.15 winx64安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-03-03
本篇是一篇關(guān)于MySQL專題知識(shí)點(diǎn)的內(nèi)容，詳細(xì)講述了InnoDB的存儲(chǔ)文件的相關(guān)內(nèi)容，感興趣的朋友學(xué)習(xí)下。2018-02-02
這篇文章主要介紹了MySQL查詢語句的優(yōu)化方法，分別介紹了子查詢優(yōu)化，分頁查詢優(yōu)化以及排序查詢優(yōu)化，對(duì)學(xué)習(xí)有一定的幫助，需要的小伙伴可以參考一下2023-04-04
clickhouse復(fù)雜時(shí)間格式的轉(zhuǎn)換方式
這篇文章主要介紹了clickhouse復(fù)雜時(shí)間格式的轉(zhuǎn)換方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12