目錄
一、用戶授權(quán)
1.1 grant授權(quán)
1.2 相關(guān)命令授權(quán)庫(kù)
1.3 revoke撤銷(xiāo)權(quán)限
二、root密碼
2.1 恢復(fù)root密碼(忘記密碼)
2.2 重置root密碼
三、MySQL備份
3.1 備份概述物理、邏輯備份
3.2 數(shù)據(jù)備份策略完全備份、增量備份
四、增量備份 binlog日志
4.1 binlog日志概述
4.2 啟用日志
4.3 分析日志
4.4 恢復(fù)數(shù)據(jù)
一、用戶授權(quán)
1.1 grant授權(quán)
- grant授權(quán)︰添加用戶并設(shè)置權(quán)限 命令格式
grant 權(quán)限列表 on 庫(kù)名 to 用戶名@”客戶端地址” identified by “密碼” //授權(quán)用戶密碼
with grant option; //有授權(quán)權(quán)限,可選項(xiàng)
mysql>grant all on db4.*to yaya@"%" identified by "123qqq..A”;
權(quán)限列表
all //所有權(quán)限
usage //無(wú)權(quán)限
select,update,insert //I個(gè)別權(quán)限
select,update (字段1,.. ..，字段N) //指定字段
用戶詳情的權(quán)限列表請(qǐng)參考MySQL官網(wǎng)說(shuō)明：https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html庫(kù)名
*.* //所有庫(kù)所有表
庫(kù)名.* //一個(gè)庫(kù)
庫(kù)名.表名 //一張表用戶名
授權(quán)時(shí)自定義要有標(biāo)識(shí)性
存儲(chǔ)在mysql庫(kù)的user表里客戶端地址
% //所有主機(jī)
192.168.4.% //網(wǎng)段內(nèi)的所有主機(jī)
192.168.4.1 //1臺(tái)主機(jī)
localhost //數(shù)據(jù)庫(kù)服務(wù)器本機(jī)
應(yīng)用示例
添加用戶mydba，對(duì)所有庫(kù)、表有完全權(quán)限
允許從任何客戶端連接，密碼abc123
且有授權(quán)權(quán)限
mysql> grant all on *.* to mydba@'%' identified by "abc123" with grant option;
Query OK, 0 rows affected, 1 warning (0.02 sec)
需要注意的是 8.0之后的新版的的mysql版本已經(jīng)將創(chuàng)建賬戶和賦予權(quán)限的方式分開(kāi)了
之前創(chuàng)建方式會(huì)報(bào)錯(cuò):
mysql> grant all on *.* to mydba@"%" identified by "abc123" with grant option;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by "123qqq...A" with grant option' at line 1
8.0 版本后 grant授權(quán) 創(chuàng)建賬戶和賦予權(quán)限的需要分兩步完成
1.創(chuàng)建賬戶:create user '用戶名'@'訪問(wèn)主機(jī)' identified by '密碼';
2.賦予權(quán)限:grant 權(quán)限列表 on 數(shù)據(jù)庫(kù) to '用戶名'@'訪問(wèn)主機(jī)' ;(修改權(quán)限時(shí)在后面加with grant option)
添加用戶mydba@"%"
mysql> create user mydba@"%" identified by "abc123";
Query OK, 0 rows affected (0.11 sec)
mysql> grant all on *.* to mydba@"%" with grant option;
Query OK, 0 rows affected (0.06 sec)
應(yīng)用示例
添加admin用戶，允許從192.168.4.0/24網(wǎng)段連接，對(duì)db3庫(kù)的user表有查詢權(quán)限，密碼123qqq.….A
添加admin2用戶，允許從本機(jī)連接，允許對(duì)db3庫(kù)的所有表有查詢/更新/插入/刪除記錄權(quán)限，密123qqq....A
mysql> grant select on db3.user to admin@"192.168.4.%" identified by "123qqq...A";
mysql> grant select,insert,update,delete on db3.* to admin2@"localhost" identified by "123qqq.….A";
1.2 相關(guān)命令授權(quán)庫(kù)
- 授權(quán)庫(kù) mysql
- mysql 庫(kù)記錄授權(quán)信息，主要表如下:
user 表記錄已有的授權(quán)用戶及權(quán)限
db 表記錄已有授權(quán)用戶對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)權(quán)限
tables_priv 表記錄已有授權(quán)用戶對(duì)表的訪問(wèn)權(quán)限
columns_priv 表記錄已有授權(quán)用戶對(duì)字段的訪問(wèn)權(quán)限
查看表記錄可以獲取用戶權(quán)限;也可以通過(guò)更新記錄，修改用戶權(quán)限
1.3 revoke撤銷(xiāo)權(quán)限
- 命令格式
mysql> revoke 權(quán)限列表 on 庫(kù)名.表 from 用戶名@"客戶端地址";
mysql> revoke insert,drop on test.* FROM sqlero2@'localhost';
Query OK,0 rows affected (0.00 sec)
案例1:用戶授權(quán)
1．允許192.168.4.0/24網(wǎng)段主機(jī)使用root連接數(shù)據(jù)庫(kù)服務(wù)器，對(duì)所有庫(kù)和所有表有完全權(quán)限、密碼為 abc123...A
2．添加用戶dba001，對(duì)所有庫(kù)和所有表有完全權(quán)限、且有授權(quán)權(quán)限，密碼為abc123...A 客戶端為網(wǎng)絡(luò)中的所有主機(jī)。
3．撤銷(xiāo)root從本機(jī)訪問(wèn)權(quán)限，然后恢復(fù)。
4．允許任意主機(jī)使用webuser用戶連接數(shù)據(jù)庫(kù)服務(wù)器，僅對(duì)webdb庫(kù)有完全權(quán)限，密碼為abc123...A．撤銷(xiāo)webuser的權(quán)限，使其僅有查詢記錄權(quán)限。
1）允許192.168.4.0/24網(wǎng)段主機(jī)使用root連接數(shù)據(jù)庫(kù)服務(wù)器，對(duì)所有庫(kù)和所有表有完全權(quán)限、密碼為 abc123...A
192.168.4.100遠(yuǎn)程登陸MySQL
[root@case100 ~]# mysql -u root -p -h 192.168.4.151
Enter password: \\輸入密碼 登陸報(bào)錯(cuò)
ERROR 1045 (28000): Access denied for user 'root'@'192.168.4.100' (using password: YES)
添加192.168.4.0/24訪問(wèn)權(quán)限
[root@mysql ~]# mysql -uroot -p"123456"
mysql> grant all on *.* to root@'192.168.4.%' identified by "abc123...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
再次從192.168.4.0/24網(wǎng)段的客戶機(jī)訪問(wèn)時(shí)，輸入正確的密碼后可登入
[root@case100 ~]# mysql -u root -p -h 192.168.4.151
Enter password:
mysql> select host,user from mysql.user ; \\登陸成功
+-------------+-----------+
| host | user |
+-------------+-----------+
| % | mydba |
| 192.168.4.% | root |
| localhost | mysql.sys |
| localhost | root |
+-------------+-----------+
4 rows in set (0.01 sec)
mysql> create database rootdb;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rootdb | //新建的rootdb庫(kù)
| sys |
+--------------------+
5 rows in set (0.01 sec)
2）添加用戶dba001，對(duì)所有庫(kù)和所有表有完全權(quán)限、且有授權(quán)權(quán)限，密碼為abc123...A 客戶端為網(wǎng)絡(luò)中的所有主機(jī)。
mysql> grant all on *.* to dba001@"%" identified by "abc123...A" with grant option; //添加用戶并授權(quán)
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show grants for dba001@"%"; //查看dba001權(quán)限
+---------------------------------------------------------------+
| Grants for dba001@% |
+---------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dba001'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
3）撤銷(xiāo)root從本機(jī)訪問(wèn)權(quán)限，然后恢復(fù)。
注意：如果沒(méi)有事先建立其他管理賬號(hào)，請(qǐng)不要輕易撤銷(xiāo)root用戶的本地訪問(wèn)權(quán)限，否則恢復(fù)起來(lái)會(huì)比較困難，甚至不得不重裝數(shù)據(jù)庫(kù)。
mysql> revoke all on *.* from root@"localhost"; //撤銷(xiāo)root@"localhost"所有權(quán)限
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for root@localhost; //查看root@localhost權(quán)限
+--------------------------------------------------------------+
| Grants for root@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> exit
Bye
[root@mysql ~]# mysql -uroot -p"123456" //重裝登陸測(cè)試
mysql> drop database rootdb; //失敗 報(bào)錯(cuò)
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'rootdb'
嘗試以當(dāng)前的root用戶恢復(fù)權(quán)限，也會(huì)失敗（無(wú)權(quán)更新授權(quán)表）：
mysql> grant all on *.* to root@localhost with grant option;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql> exit
Bye
由管理賬號(hào)dba001重新為root添加本地訪問(wèn)權(quán)限
[root@mysql ~]# mysql -udba001 -p"abc123...A"
mysql> grant all on *.* to root@localhost with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@mysql ~]# mysql -uroot -p"123456" //root帳號(hào)重新登陸測(cè)試
mysql> drop database rootdb; //權(quán)限恢復(fù) 刪除成功
Query OK, 0 rows affected (0.02 sec)
4）允許任意主機(jī)使用webuser用戶連接數(shù)據(jù)庫(kù)服務(wù)器，僅對(duì)webdb庫(kù)有完全權(quán)限，密碼為1abc123...A．撤銷(xiāo)webuser的權(quán)限，使其僅有查詢記錄權(quán)限。
mysql> create database webdb; //新建庫(kù)webdb
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| webdb |
+--------------------+
5 rows in set (0.01 sec)
mysql> grant all on webdb.* to webuser@'%' identified by "abc123...A"; //對(duì)用戶webuser授權(quán)
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show grants for webuser@'%';
+----------------------------------------------------+
| Grants for webuser@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'%' |
| GRANT ALL PRIVILEGES ON `webdb`.* TO 'webuser'@'%' |
+----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke all on webdb.* from webuser@'%'; //撤銷(xiāo)webuser@"%"所有權(quán)限
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for webuser@'%';
+-------------------------------------+
| Grants for webuser@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)
二、root密碼
2.1 恢復(fù)root密碼(忘記密碼)
root密碼忘了怎么辦?
1.停止MySQL服務(wù)程序
2.跳過(guò)授權(quán)表啟動(dòng)MySQL服務(wù)程序
3.修改root密碼
4.以正常方式重啟MySQL服務(wù)程序
主要操作過(guò)程
]# vim /etc/my.cnf
[mysqld]
......
skip_grant_tables //配置中追加跳過(guò)權(quán)限檢測(cè)
]# systemctl restart mysqld
]# mysql
mysql> update mysql.user set authentication_string=password(“密碼”)
->where user="root" and host="localhost"; //修改密碼
mysql> flush privileges; //刷新立即生效,后面我們需要重啟數(shù)據(jù)庫(kù),這步其實(shí)可以省略
mysql> quit ;
2.2 重置root密碼
修改管理員root密碼有很多種方法以下介紹幾種常用的
1）方法1，在Shell命令行下設(shè)置
[root@mysql ~]# mysqladmin -uroot -p password 'abc321...A'
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
2）方法2，以root登入mysql> 后，使用SET PASSWORD指令設(shè)置
這個(gè)與新安裝MySQL-server后首次修改密碼時(shí)要求的方式相同，平時(shí)也可以用：
mysql> SET PASSWORD FOR root@localhost=PASSWORD('1234567');
Query OK,0 rows affected,1warning(0.00 sec)
3）方法3，以root登入mysql> 后，使用GRANT授權(quán)工具設(shè)置,這個(gè)是最常見(jiàn)的用戶授權(quán)方式：
mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '1234567';
Query OK,0 rows affected,1warning(0.00 sec)
4）方法4，以root登入mysql> 后，使用UPDATE更新相應(yīng)的表記錄
這種方法與恢復(fù)密碼時(shí)的操作相同：
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('1234567')
-> WHERE user='root' AND host='localhost'; //重設(shè)root的密碼
Query OK,0 rows affected,1warning(0.00 sec)
Rows matched:1 Changed:0 Warnings:1
mysql> FLUSH PRIVILEGES; //刷新授權(quán)表
Query OK,0 rows affected(0.00 sec)
在上述方法中，需要特別注意：當(dāng)MySQL服務(wù)程序以 skip-grant-tables 選項(xiàng)啟動(dòng)時(shí)，如果未執(zhí)行“FLUSH PRIVILEGES;”操作，是無(wú)法通過(guò)SET PASSWORD或者GRANT方式來(lái)設(shè)置密碼的。比如，驗(yàn)證這兩種方式時(shí)，都會(huì)看到ERROR 1290的出錯(cuò)提示：
mysql> SET PASSWORD FOR root@localhost=PASSWORD('1234567');
ERROR 1290(HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '1234567';
ERROR 1290(HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
案例2: root密碼
具體要求如下:
1.恢復(fù)管理員root密碼123qqq...A
2.重置管理員root密碼 A...qqq321
[root@mysql ~]# systemctl stop mysqld
[root@mysql ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since 二 2020-12-22 17:38:12 CST; 6s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 21258 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 21240 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 21261 (code=exited, status=0/SUCCESS)
12月 22 11:45:03 mysql systemd[1]: Starting MySQL Server...
12月 22 11:45:04 mysql systemd[1]: Started MySQL Server.
12月 22 17:38:09 mysql systemd[1]: Stopping MySQL Server...
12月 22 17:38:12 mysql systemd[1]: Stopped MySQL Server.
[root@mysql ~]# vim /etc/my.cnf
skip_grant_tables
......
[root@mysql ~]# systemctl start mysqld
[root@mysql ~]# mysql
mysql> update mysql.user set authentication_string=password('abc123...B') where user="root" and host="localhost";
Query OK, 0 rows affected, 1 warning (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
[root@mysql ~]# vim /etc/my.cnf
#skip_grant_tables
......
[root@mysql ~]# vim /etc/my.cnf
#skip_grant_tables //刪除skip_grant_tables 重啟服務(wù)
......
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# mysql -uroot -p"abc123...B"
mysql>
三、MySQL備份
3.1 備份概述物理、邏輯備份
備份概述
數(shù)據(jù)備份方式
物理備份
冷備:cp、tar、...邏輯備份
mysqldump //備份命令
mysql //恢復(fù)命令物理備份及恢復(fù)
備份操作
cp -r /var/lib/mysql 備份目錄/mysql.bak
tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*恢復(fù)操作
cp -r 備份目錄/mysql.bak /var/lib/mysql/
tar -zxvf /root/mysql.tar.gz -C /var/lib/mysq1/
chown -R mysql:mysql /var/lib/mysql邏輯備份
數(shù)據(jù)備份策略
完全備份
備份所有數(shù)據(jù)增量備份
備份上次備份后，所有新產(chǎn)生的數(shù)據(jù)差異備份
備份完全備份后，所有新產(chǎn)生的數(shù)據(jù)完全備份及恢復(fù)
完全備份
]#mysqldump -uroot -p密碼庫(kù)名 > 目錄/xxx.sql
完全恢復(fù)
]#mysql -uroot -p密碼[庫(kù)名] < 目錄/xxx.sql備份時(shí)庫(kù)名表示方式
--all-databases 或 -A //所有庫(kù)
數(shù)據(jù)庫(kù)名 //單個(gè)庫(kù)
數(shù)據(jù)庫(kù)名表名 //單張表
-B 數(shù)據(jù)庫(kù)1 數(shù)據(jù)庫(kù)2 //多個(gè)庫(kù)
注意事項(xiàng)
無(wú)論備份還是恢復(fù)，都要驗(yàn)證用戶權(quán)限!!!
- 完全備份及恢復(fù) 應(yīng)用示例1
-將所有的庫(kù)備份為allbak.sql文件
-將db3庫(kù)備份為db3.sql文件
[root@dbsvr1 ~]# mysqldump -uroot -p密碼 -A > allbak.sql
[root@dbsvr1 ~]# mysqldump -uroot -p密碼 db3 > db3.sql
[root@dbsvr1 ~]# ls -lh *.sql
-rw-r--r--.1 root root 595K 1月2 13:54 allbak.sql-rw-r--r--. 1 root root 4.1K 1月2 13:55 db3.sql
案例3:數(shù)據(jù)備份與恢復(fù)
具體要求如下∶
1.練習(xí)mysqldump命令的使用
2.使用mysql命令恢復(fù)刪除的數(shù)據(jù)
1）備份MySQL服務(wù)器上的所有庫(kù)
將所有的庫(kù)備份為mysql-all.sql文件
[root@mysql ~]# mysqldump -u root -p --all-databases >/root/alldb.sql //備份所有庫(kù)
Enter password:
[root@mysql ~]# file /root/alldb.sql //確定備份文件類型
/root/alldb.sql: UTF-8 Unicode text, with very long lines
[root@mysql ~]# cat /root/alldb.sql|head -15 //查看備份文件alldb.sql的部分內(nèi)容：
-- MySQL dump 10.13 Distrib 8.0.22, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 8.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
[root@mysql ~]#
注意：若數(shù)據(jù)庫(kù)都使用MyISAM存儲(chǔ)引擎，可以采用冷備份的方式，直接復(fù)制對(duì)應(yīng)的
數(shù)據(jù)庫(kù)目錄即可；恢復(fù)時(shí)重新復(fù)制回來(lái)就行。
2）只備份指定的某一個(gè)庫(kù)
[root@mysql ~]# mysqldump -uroot -p db1 > db1.sql //備份db1
Enter password:
[root@mysql ~]# cat /root/db1.sql|head -15
-- MySQL dump 10.13 Distrib 8.0.22, for Linux (x86_64)
--
-- Host: localhost Database: db1
-- ------------------------------------------------------
-- Server version 8.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
3）同時(shí)備份指定的多個(gè)庫(kù)
[root@mysql ~]# mysqldump -u root -p -B mysql db1 db2 >mysql.db1.db2.sql //備份db1 db2
Enter password:
[root@mysql ~]# ll /root/mysql.db1.db2.sql
-rw-r--r-- 1 root root 1130849 12月 23 15:18 /root/mysql.db1.db2.sql
4）使用mysql 命令恢復(fù)刪除的數(shù)據(jù)
以恢復(fù)db1庫(kù)為例，可參考下列操作把數(shù)據(jù)恢復(fù)到另一臺(tái)數(shù)據(jù)庫(kù)上,如果是在原數(shù)據(jù)庫(kù)操作通常不建議直接覆蓋舊庫(kù)，而是采用建立新庫(kù)并導(dǎo)入邏輯備份的方式執(zhí)行恢復(fù)，待新庫(kù)正常后即可廢棄或刪除舊庫(kù)
mysql> create databases db1bak;
Query OK, 1 row affected (0.01 sec)
mysql> exit
[root@mysql ~]# mysql -u root -p db1bak < /root/db1.sql //恢復(fù)所有庫(kù)到db1bak
Enter password:
[root@mysql ~]# mysql -uroot -p"abc321...A"
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1bak |
| mysql |
| performance_schema |
| sys |
| webdb |
+--------------------+
6 rows in set (0.00 sec)
mysql> use db1bak
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables; //查看數(shù)據(jù)完整性
+------------------+
| Tables_in_db1bak |
+------------------+
| gz |
| school |
| t1 |
| t3 |
| t4 |
| t5 |
| t6 |
| t8 |
| tea4 |
| yg |
+------------------+
10 rows in set (0.00 sec)
mysql> select * from t1;
+------+---------+
| name | homedir |
+------+---------+
| bob | USA |
+------+---------+
1 row in set (0.00 sec)
四、增量備份 binlog日志
- 4.1 binlog日志概述
-binlog日志也稱做二進(jìn)制日志
-MySQL服務(wù)日志文件的一種
-記錄除查詢之外的所有SQL命令
-可用于數(shù)據(jù)備份和恢復(fù)
-配置mysql主從同步的必要條件
啟用日志主要操作
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
...
log_bin //啟用binlog日志
server_id=100 //指定id值
[root@mysql ~]# systemctl restart mysqld
啟用日志
binlog相關(guān)文件
主機(jī)名-bin.index \\索引文件
主機(jī)名-bin.000001 \\第1個(gè)二進(jìn)制日志
主機(jī)名-bin.000002 \\第2個(gè)二進(jìn)制日志手動(dòng)生成新的日志文件:
方法1. ]# systemctl restart mysqld
方法2. mysql> flush logs; 或 ]# mysql -uroot -p密碼 -e'flush log'
方法3.mysqldump --flush-logs清理日志
刪除指定編號(hào)之前的binlog日志文件
Mysql> purge master logs to "binlog文件名"; \\刪除所有binlog日志，重建新日志
Mysql> reset master;
案例4 : binlog日志
啟用binlog日志，具體要求如下:
- 啟用binlog日志，把日志文件存放到系統(tǒng)的/mylog目錄下，日志文件為db50
- 手動(dòng)創(chuàng)建3個(gè)新的日志文件
- 刪除編號(hào)3之前的日志文件
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
......
log_bin=/mylog/db50
server_id=1
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# ll /mylog/
總用量 8
-rw-r----- 1 mysql mysql 154 12月 23 16:49 db50.000001
-rw-r----- 1 mysql mysql 19 12月 23 16:49 db50.index
[root@mysql ~]# mysql -uroot -p"abc321...A"
mysql> flush logs; //每執(zhí)行一次都會(huì)生成新的日志文件
Query OK, 0 rows affected (0.08 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> system ls /mylog/
db50.000001 db50.000002 db50.000003 db50.000004 db50.index
mysql> show master status; //查看當(dāng)前使用的日志文件
+-------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db50.000004 | 154 | | | |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> purge master logs to "db50.000003"; //刪除db50.000003之前的日志文件
Query OK, 0 rows affected (0.05 sec)
mysql> system ls /mylog/
db50.000003 db50.000004 db50.index
mysql> cat /mylog/db50.index //查看日志索引
/mylog/db50.000003
/mylog/db50.000004
4.3 分析日志
查看日志當(dāng)前記錄格式
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.03 sec)
三種記錄方式:
1.statement報(bào)表模式
2.row行模式
3.mixed混合模式
以上3種模式具體差異可自行查找,推薦mixed混合模式結(jié)合了1,2的優(yōu)勢(shì)修改日志記錄格式操作
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
.. ..
binlog_format=“名稱”
[root@localhost ~]# systemctl restart mysqld查看日志內(nèi)容
mysqlbinlog [選項(xiàng)] binlog 日志文件名
選項(xiàng)
用途
--start-datetime="yyyy-mm-dd hh:mm:ss” 起始時(shí)間 從二進(jìn)制日志中讀取指定等于時(shí)間戳或者晚于本地計(jì)算機(jī)的時(shí)間
--stop-datetime="yyyy-mm-dd hh:mm:ss"結(jié)束時(shí)間 從二進(jìn)制日志中讀取指定小于時(shí)間戳或者等于本地計(jì)算機(jī)的時(shí)間
--start-position=數(shù)字 起始偏移量 從二進(jìn)制日志中讀取指定position 事件位置作為開(kāi)始。
--stop-position=數(shù)字 結(jié)束偏移量 從二進(jìn)制日志中讀取指定position 事件位置作為事件截至
在使用binlog數(shù)據(jù)恢復(fù)時(shí),推薦使用事件位置來(lái)確定開(kāi)始與截至段 會(huì)更精確
時(shí)間的方式只精確到秒,如果一秒內(nèi)同時(shí)發(fā)生了添加和刪除操作恢復(fù)會(huì)失敗
4.4 恢復(fù)數(shù)據(jù)
- 基本思路
使用mysqlbinlog提取歷史SQL操作,通過(guò)管道交給mysql命令執(zhí)行
·命令格式
mysqlbinlog 日志文件│mysql -uroot -p密碼 - 應(yīng)用示例
使用編號(hào)為1的日志文件恢復(fù)數(shù)據(jù)
]# cd /var/lib/mysql
]# mysqlbinlog mysql-bin.000001 | mysql -uroot -p123456
案例5:使用binlog日志恢復(fù)數(shù)據(jù)
利用binlog恢復(fù)庫(kù)表，要求如下∶
1．啟用binlog日志、并修改格式為mixed
2．創(chuàng)建db1庫(kù)和tb1表并插入3條記錄
3．刪除tb1表中剛插入的3條記錄
4．使用binlog日志恢復(fù)刪除的3條記錄
[root@mysql ~]# vim /etc/my.cnf
......
binlog_format="mixed"
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# ll /var/lib/mysql/mysql-bin.*
-rw-r----- 1 mysql mysql 177 12月 23 16:30 /var/lib/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql 154 12月 23 17:09 /var/lib/mysql/mysql-bin.000002
-rw-r----- 1 mysql mysql 38 12月 23 17:09 /var/lib/mysql/mysql-bin.index
[root@mysql ~]# systemctl restart mysqld //每次重啟服務(wù)都會(huì)生成新的日志文件
[root@mysql ~]# ls /var/lib/mysql/mysql-bin.*
/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000003
/var/lib/mysql/mysql-bin.000002 /var/lib/mysql/mysql-bin.index
[root@mysql ~]# mysql -uroot -p"abc321...A"
mysql> create database db1; //新建庫(kù)db1
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db1bak |
| mysql |
| performance_schema |
| sys |
| webdb |
+--------------------+
7 rows in set (0.00 sec)
mysql> use db1;
Database changed
mysql> create table tb1( id int(4) not null,name varchar(24));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into db1.tb1 values
-> (1,"Jack");
Query OK, 1 row affected (0.15 sec)
mysql> insert into db1.tb1 values //寫(xiě)入數(shù)據(jù)
-> (2,"Kenthy"),
-> (3,"Bob");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tb1;
+----+--------+
| id | name |
+----+--------+
| 1 | Jack |
| 2 | Kenthy |
| 3 | Bob |
+----+--------+
3 rows in set (0.02 sec)
mysql> delete from tb1;
Query OK, 3 rows affected (0.07 sec)
mysql> select * from tb1;
Empty set (0.00 sec)
mysql> exit
Bye
[root@mysql ~]# ls /var/lib/mysql/mysql-bin.*
/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000003
/var/lib/mysql/mysql-bin.000002 /var/lib/mysql/mysql-bin.index
[root@mysql ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000003 //查看mysql-bin.000003日志內(nèi)容
......
# at 310
#201223 17:23:29 server id 1 end_log_pos 375 CRC32 0xeb6b5cae Anonymous_GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 375
#201223 17:23:29 server id 1 end_log_pos 501 CRC32 0x8378de25 Query thread_id=3 exec_time=0 error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1608715409/*!*/;
create table tb1( id int(4) not null,name varchar(24))
/*!*/;
# at 501
#201223 17:26:25 server id 1 end_log_pos 566 CRC32 0xbe733bf7 Anonymous_GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 566
#201223 17:26:25 server id 1 end_log_pos 643 CRC32 0xc08d9b7f Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1608715585/*!*/;
BEGIN
/*!*/;
# at 643 //起啟位置為643
#201223 17:26:25 server id 1 end_log_pos 752 CRC32 0xc2cee70c Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1608715585/*!*/;
insert into db1.tb1 values
(1,"Jack")
/*!*/;
# at 752
#201223 17:26:25 server id 1 end_log_pos 783 CRC32 0xf25ad0e7 Xid = 17
COMMIT/*!*/;
# at 783
#201223 17:27:25 server id 1 end_log_pos 848 CRC32 0x35f44d85 Anonymous_GTID last_committed=3 sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 848
#201223 17:27:25 server id 1 end_log_pos 925 CRC32 0xbf81905c Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1608715645/*!*/;
BEGIN
/*!*/;
# at 925
#201223 17:27:25 server id 1 end_log_pos 1047 CRC32 0x494b097c Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1608715645/*!*/;
insert into db1.tb1 values
(2,"Kenthy"),
(3,"Bob")
/*!*/;
# at 1047
#201223 17:27:25 server id 1 end_log_pos 1078 CRC32 0x45782a98 Xid = 18
COMMIT/*!*/;
# at 1078 //以1078為截至
#201223 17:28:48 server id 1 end_log_pos 1143 CRC32 0x92d54ab2 Anonymous_GTID last_committed=4 sequence_number=5
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1143
#201223 17:28:48 server id 1 end_log_pos 1220 CRC32 0xc58763f7 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1608715728/*!*/;
BEGIN
/*!*/;
# at 1220
#201223 17:28:48 server id 1 end_log_pos 1307 CRC32 0xc2402c25 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1608715728/*!*/;
delete from tb1
/*!*/;
# at 1307
#201223 17:28:48 server id 1 end_log_pos 1338 CRC32 0x9be4cbf8 Xid = 20
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
......
也可以通過(guò) show binlog命令查看位置點(diǎn) 更清晰
mysql> show binlog events in "mysql-bin.000003";
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 1 | 310 | create database db1 |
| mysql-bin.000003 | 310 | Anonymous_Gtid | 1 | 375 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 375 | Query | 1 | 501 | use `db1`; create table tb1( id int(4) not null,name varchar(24)) |
| mysql-bin.000003 | 501 | Anonymous_Gtid | 1 | 566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 566 | Query | 1 | 643 | BEGIN //起啟位置為643 |
| mysql-bin.000003 | 643 | Query | 1 | 752 | use `db1`; insert into db1.tb1 values
(1,"Jack") |
| mysql-bin.000003 | 752 | Xid | 1 | 783 | COMMIT /* xid=17 */ |
| mysql-bin.000003 | 783 | Anonymous_Gtid | 1 | 848 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 848 | Query | 1 | 925 | BEGIN |
| mysql-bin.000003 | 925 | Query | 1 | 1047 | use `db1`; insert into db1.tb1 values
(2,"Kenthy"),
(3,"Bob") |
| mysql-bin.000003 | 1047 | Xid | 1 | 1078 | COMMIT /* xid=18 */ //以1078為截至 |
| mysql-bin.000003 | 1078 | Anonymous_Gtid | 1 | 1143 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1143 | Query | 1 | 1220 | BEGIN |
| mysql-bin.000003 | 1220 | Query | 1 | 1307 | use `db1`; delete from tb1 |
| mysql-bin.000003 | 1307 | Xid | 1 | 1338 | COMMIT /* xid=20 */ |
| mysql-bin.000003 | 1338 | Anonymous_Gtid | 1 | 1403 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
注意:起啟和截至位置要包含需要恢復(fù)的位置段,不能等于需要恢復(fù)位置 比如以上的起啟位置不能為752 結(jié)束不能為1047
[root@mysql ~]# mysqlbinlog --start-position="643" --stop-position="1078" /var/lib/mysql/mysql-bin.000003|mysql -u root -p"abc321...A"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -uroot -p"abc321...A"
mysql> use db1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from db1.tb1; //恢復(fù)成功
+----+--------+
| id | name |
+----+--------+
| 1 | Jack |
| 2 | Kenthy |
| 3 | Bob |
+----+--------+
3 rows in set (0.01 sec)
