DBA基础（一）用户授权
生活随笔
收集整理的這篇文章主要介紹了
DBA基础（一）用户授权
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
用戶授權(quán)
用戶授權(quán)
grant授權(quán)
- 授權(quán)：添加用戶并設(shè)置權(quán)限
- 作用：在數(shù)據(jù)庫服務(wù)器上添加用戶，設(shè)置訪問權(quán)限及登錄密碼，給客戶端連
接使用 - 命令格式：
mysql >grant ?權(quán)限列表 on ?庫名 ?to ? 用戶名@"客戶端地址" ?identified ?by
? "密碼" ? with ? grant ?option ;? ? ? ? ? ??#with ? grant ?option ;讓新添加的用戶也有授權(quán)的權(quán)限，可選項(xiàng)
- 權(quán)限列表
all? ? ? ? ? ? ? ? ? ? ? ? ? ? //所有權(quán)限
usage? ? ? ? ? ? ? ? ? ? ?//無權(quán)限
select,update，insert? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?//個別權(quán)限
select，update（字段1，……，字段N） ?//指定字段
- 庫名
*.*? ? ? ? ? ? ? ? ? ? 代表所有庫所有表
庫名.*? ? ? ? ? ? ? 代表一個庫下的所有表
庫名.表名? ? ? ??代表一個庫下的一張表
- 用戶名
授權(quán)時自定義要有標(biāo)識性
存儲在mysql庫里的user表中
- 客戶端地址
%? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?//所有主機(jī)
192.168.4.%? ? ? ? ? ? ? ? ? ? ? //網(wǎng)段內(nèi)所有主機(jī)
192.168.4.1? ? ? ? ? ? ? ? ? ? ? ?//1臺主機(jī)
localhost? ? ? ? ? ? ? ? ? ? ? ? ? ? //數(shù)據(jù)庫服務(wù)器本機(jī)
應(yīng)用示例：
1.添加admin用戶，允許從192.168.4.0/24網(wǎng)段連接，對db3庫的user表有查詢權(quán)限，密碼為123qqq...A
2.添加admin2用戶，允許從本機(jī)連接，允許對db3庫的所有表有查詢/更新/插入/刪除記錄權(quán)限，密碼為123qqq...A
mysql> grant select on db3.user to admin@"192.168.4.%" identified by "123qqq...A"; Query OK, 0 rows affected, 1 warning (0.00 sec)在host52、53上面查看是否能連接數(shù)據(jù)庫，以及權(quán)限[root@host52 ~]# mysql -h192.168.4.51 -uadmin -p123qqq...A mysql> show grants; +-------------------------------------------------------+ | Grants for admin@192.168.4.% | +-------------------------------------------------------+ | GRANT USAGE ON *.* TO 'admin'@'192.168.4.%' | | GRANT SELECT ON `db3`.`user` TO 'admin'@'192.168.4.%' | +-------------------------------------------------------+ 2 rows in set (0.00 sec)[root@host53 ~]# mysql -h192.168.4.51 -uadmin -p123qqq...A mysql> use db3; mysql> show tables; +---------------+ | Tables_in_db3 | +---------------+ | user | +---------------+ 1 row in set (0.00 sec)mysql> insert into db3.user(name) values("haha"); ERROR 1142 (42000): INSERT command denied to user 'admin'@'192.168.4.53' for table 'user'mysql> grant select ,insert ,update ,delete on db3.* to admin2@"localhost" identified by"123qqq...A"; Query OK, 0 rows affected, 1 warning (0.00 sec)在host51、52、53上面查看是否能連接數(shù)據(jù)庫[root@host52 ~]# mysql -h192.168.4.51 -uadmin2 -p123qqq...A mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'admin2'@'192.168.4.52' (using password: YES)[root@host53 ~]# mysql -h192.168.4.51 -uadmin2 -p123qqq...A mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'admin2'@'192.168.4.53' (using password: YES)[root@host51 ~]# mysql -uadmin2 -p123qqq...A mysql> select user(); +------------------+ | user() | +------------------+ | admin2@localhost | +------------------+ 1 row in set (0.00 sec)- ?相關(guān)命令：登錄用戶使用
| 命令 | 作用 |
| select? user(); | 顯示登錄用戶名及客戶端地址 |
| show? grants; | 用戶顯示自身訪問權(quán)限 |
| show grants? ?for? ?用戶名@"客戶端地址"; | 管理員查看已有授權(quán)用戶權(quán)限 |
| set? password=password("密碼"); | 授權(quán)用戶連接后修改連接密碼 |
| set? password? for? 用戶名@"客戶端地址"? =password("密碼"); | 管理員重置授權(quán)用戶連接密碼 |
| drop? ?user? ?用戶名@"客戶端地址"; | 刪除授權(quán)用戶（必須有管理員權(quán)限） |
測試with ? grant ?option：
mysql> grant all on *.* to root@"192.168.4.52" identified by "123qqq...A" with grant option; Query OK, 0 rows affected, 1 warning (0.00 sec)用192.168.4.52測試，查看其權(quán)限 [root@host52 ~]# mysql -h192.168.4.51 -uroot -p123qqq...Amysql> show grants; +------------------------------------------------------------------------+ | Grants for root@192.168.4.52 | +------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.4.52' WITH GRANT OPTION | +------------------------------------------------------------------------+ 1 row in set (0.01 sec)在host52主機(jī)上授權(quán)tian用戶對tian庫下可以進(jìn)行任何操作mysql> grant all on tian.* to tian@"localhost" identified by "123qqq...A"; Query OK, 0 rows affected, 1 warning (0.00 sec)[root@host51 ~]# mysql -utian -p123qqq...Amysql> show grants; +--------------------------------------------------------+ | Grants for tian@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tian'@'localhost' | | GRANT ALL PRIVILEGES ON `tian`.* TO 'tian'@'localhost' | +--------------------------------------------------------+ 2 rows in set (0.00 sec)mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)mysql> create database tian; #如果沒有tian庫，可以創(chuàng)建 Query OK, 1 row affected (0.00 sec)mysql> create database aaa; #該用戶只能對tian庫下進(jìn)行操作 ERROR 1044 (42000): Access denied for user 'tian'@'localhost' to database 'aaa'授權(quán)庫
- mysql庫記錄授權(quán)信息，主要表如下：
user表? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?記錄已有的授權(quán)用戶的權(quán)限
db表? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?記錄已有授權(quán)用戶對數(shù)據(jù)庫的訪問權(quán)限
tables_priv表? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 記錄已有授權(quán)用戶對表的訪問權(quán)限
columns_priv表? ? ? ? ? ? ? ? ? ? ? ? ? ?記錄已有授權(quán)用戶對字段的訪問權(quán)限
查看表記錄可以獲取用戶權(quán)限，也可以通過更新記錄，修改用戶權(quán)限
查看本機(jī)上已有的授權(quán)用戶，以host51為例 mysql> select host ,user from mysql.user; +--------------+-----------+ | host | user | +--------------+-----------+ | 192.168.4.% | admin | | 192.168.4.52 | root | | localhost | admin2 | | localhost | mysql.sys | | localhost | root | | localhost | tian | +--------------+-----------+ 6 rows in set (0.00 sec)mysql> show grants; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) #GRANT PROXY可以將自己的權(quán)限復(fù)制給其他用戶 GRANT ALL PRIVILEGES，它的權(quán)限記錄在mysql庫下的user表mysql> select * from mysql.user where host="localhost" and user="root" \G #查看root用戶的權(quán)限 *************************** 1. row ***************************Host: localhostUser: rootSelect_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv: YFile_priv: YGrant_priv: YReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: Y Create_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9password_expired: Npassword_last_changed: 2020-02-13 17:06:50password_lifetime: NULLaccount_locked: N 1 row in set (0.06 sec)db表： mysql> select host ,user ,db from mysql.db; +-----------+-----------+------+ | host | user | db | +-----------+-----------+------+ | localhost | admin2 | db3 | | localhost | mysql.sys | sys | | localhost | tian | tian | +-----------+-----------+------+ 3 rows in set (0.00 sec)mysql> show grants for tian@"localhost"; +--------------------------------------------------------+ | Grants for tian@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tian'@'localhost' | | GRANT ALL PRIVILEGES ON `tian`.* TO 'tian'@'localhost' | +--------------------------------------------------------+ 2 rows in set (0.00 sec)mysql> select * from mysql.db where db="tian" \G *************************** 1. row ***************************Host: localhostDb: tianUser: tianSelect_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: Y Create_tmp_table_priv: YLock_tables_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YExecute_priv: YEvent_priv: YTrigger_priv: Y 1 row in set (0.00 sec)mysql> update mysql.db set insert_priv="N" ,delete_priv="N" where host="localhost" and user="tian" and db="tian"; #修改授權(quán)，讓tian用戶取消修改和刪除權(quán)限 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from mysql.db where db="tian" \G #查看權(quán)限 *************************** 1. row ***************************Host: localhostDb: tianUser: tianSelect_priv: YInsert_priv: NUpdate_priv: YDelete_priv: NCreate_priv: YDrop_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: Y Create_tmp_table_priv: YLock_tables_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YExecute_priv: YEvent_priv: YTrigger_priv: Y 1 row in set (0.00 sec)mysql> show grants for tian@localhost; #此事再次查看tian用戶的權(quán)限，發(fā)現(xiàn)依舊沒有變化 +--------------------------------------------------------+ | Grants for tian@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tian'@'localhost' | | GRANT ALL PRIVILEGES ON `tian`.* TO 'tian'@'localhost' | +--------------------------------------------------------+ 2 rows in set (0.00 sec)mysql> flush privileges; #刷新 Query OK, 0 rows affected (0.00 sec)mysql> show grants for tian@localhost; #再次查看，發(fā)現(xiàn)此時權(quán)限發(fā)生了變化 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for tian@localhost | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'tian'@'localhost' | | GRANT SELECT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `tian`.* TO 'tian'@'localhost' | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec)tables_priv表:mysql> select * from mysql.tables_priv \G *************************** 1. row ***************************Host: localhostDb: sysUser: mysql.sysTable_name: sys_config #授權(quán)時間Grantor: root@localhost #授權(quán)用戶Timestamp: 2020-02-13 17:01:31Table_priv: Select #對表的訪問權(quán)限 Column_priv: *************************** 2. row ***************************Host: 192.168.4.%Db: db3User: adminTable_name: userGrantor: root@localhostTimestamp: 0000-00-00 00:00:00Table_priv: Select Column_priv: 2 rows in set (0.00 sec)mysql> show grants for admin@"192.168.4.%"; +-------------------------------------------------------+ | Grants for admin@192.168.4.% | +-------------------------------------------------------+ | GRANT USAGE ON *.* TO 'admin'@'192.168.4.%' | | GRANT SELECT ON `db3`.`user` TO 'admin'@'192.168.4.%' | +-------------------------------------------------------+ 2 rows in set (0.00 sec)mysql> update mysql.tables_priv set table_priv="select,update,insert" where host="192.168.4.%" and user="admin"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for admin@"192.168.4.%"; +-----------------------------------------------------------------------+ | Grants for admin@192.168.4.% | +-----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'admin'@'192.168.4.%' | | GRANT SELECT, INSERT, UPDATE ON `db3`.`user` TO 'admin'@'192.168.4.%' | +-----------------------------------------------------------------------+ 2 rows in set (0.00 sec)columns_priv表：mysql> select * from mysql.columns_priv; Empty set (0.00 sec)mysql> grant select , update(name) on db3.user to haha@"%" identified by "123qqq...A"; #授權(quán)haha用戶對數(shù)據(jù)庫有查看權(quán)限，對name字段有更新權(quán)限 Query OK, 0 rows affected, 1 warning (0.05 sec)mysql> select * from mysql.columns_priv; +------+-----+------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +------+-----+------+------------+-------------+---------------------+-------------+ | % | db3 | haha | user | name | 0000-00-00 00:00:00 | Update | +------+-----+------+------------+-------------+---------------------+-------------+ 1 row in set (0.00 sec)撤銷權(quán)限
命令格式：revoke ?權(quán)限列表 ?on ?庫名.表 ?from ? 用戶名@"客戶端地址"
mysql> select user ,host from mysql.user; +-----------+--------------+ | user | host | +-----------+--------------+ | haha | % | | webadmin | % | | admin | 192.168.4.% | | root | 192.168.4.52 | | admin2 | localhost | | mysql.sys | localhost | | root | localhost | | tian | localhost | +-----------+--------------+ 8 rows in set (0.00 sec)mysql> show grants for webadmin@"%"; +-----------------------------------------------------------------------+ | Grants for webadmin@% | +-----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'webadmin'@'%' | | GRANT ALL PRIVILEGES ON `bbsdb`.* TO 'webadmin'@'%' WITH GRANT OPTION | +-----------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke grant option on bbsdb.* from webadmin@"%"; Query OK, 0 rows affected (0.01 sec)mysql> show grants for webadmin@"%"; +-----------------------------------------------------+ | Grants for webadmin@% | +-----------------------------------------------------+ | GRANT USAGE ON *.* TO 'webadmin'@'%' | | GRANT ALL PRIVILEGES ON `bbsdb`.* TO 'webadmin'@'%' | +-----------------------------------------------------+ 2 rows in set (0.00 sec)mysql> revoke update ,insert on bbsdb.* from webadmin@"%"; Query OK, 0 rows affected (0.00 sec)mysql> show grants for webadmin@"%"; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for webadmin@% | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'webadmin'@'%' | | GRANT SELECT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `bbsdb`.* TO 'webadmin'@'%' | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)root密碼
- root密碼忘了怎么辦？
（只適合線下修改）
[root@host51 ~]# systemctl stop mysqld[root@host51 ~]# vim /etc/my.cnf [mysqld] #skip-grant-tables secure_file_priv=/myload #validate_password_policy=0 #validate_password_length=6[root@host51 ~]# systemctl start mysqld[root@host51 ~]# mysql mysql> update mysql.user set authentication_string=password("123qqq...A") where user="root" and host="localhost"; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)[root@host51 ~]# vim /etc/my.cnf [mysqld] #skip-grant-tables secure_file_priv=/myload validate_password_policy=0 validate_password_length=6[root@host51 ~]# systemctl restart mysqld[root@host51 ~]# mysql -uroot -p123qqq...A mysql> alter user root@"localhost" identified by "123456"; Query OK, 0 rows affected (0.01 sec)[root@host51 ~]# mysql -uroot -p123456修改管理員root密碼的其他方法
方法一：以root用戶登錄mysql后，使用set? password指令設(shè)置，這個與新安裝MySQL-server后首次修改密碼時的要求方式相同，平時也可以使用
mysql> SET PASSWORD FOR root@localhost=PASSWORD('123456'); Query OK, 0 rows affected, 1 warning (0.00 sec)方法二：以root用戶登錄mysql后，使用grant授權(quán)工具的設(shè)置
mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec)方法三：以root登錄后，使用update更新相應(yīng)的表記錄，這種方法與恢復(fù)密碼時的操作相同
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('123456')-> WHERE user='root' AND host='localhost'; //重設(shè)root的密碼 Query OK, 0 rows affected, 1 warning (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)啟動時，如果未執(zhí)行“FLUSH PRIVILEGES;”操作，是無法通過SET PASSWORD或者GRANT方式來設(shè)置密碼的。比如，驗(yàn)證這兩種方式時，都會看到ERROR 1290的出錯提示：
mysql> SET PASSWORD FOR root@localhost=PASSWORD('123456'); 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 '123456'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement- 重置root密碼
在shell命令行修改登錄密碼，需要驗(yàn)證舊密碼
[root@host51 ~]# mysqladmin -uroot -p"123456" password "tiantian" 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. #此處警告，最好使用ssl服務(wù)更安全 [root@host51 ~]# mysql -uroot -p"tiantian" #也可以-p之后回車交互式輸入密碼完全備份
備份概述
數(shù)據(jù)備份方式
- 物理備份：冷備：cp、tar
- 邏輯備份：根據(jù)備份時已有的數(shù)據(jù)生成的對應(yīng)的SQL命令，保存到指定的文件里
也可以分為冷備和熱備
- 冷備：在進(jìn)行備份時不允許做其他的任何操作
- 熱備：在服務(wù)器執(zhí)行時可以同時進(jìn)行備份
物理備份及恢復(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/mysql
- chown? -R? mysql:mysql? ?/var/lib/mysql
邏輯備份策略
- 完全備份：備份所有數(shù)據(jù)
- 增量備份：備份上次備份后所有新產(chǎn)生的新數(shù)據(jù)
- 差異備份：備份完全備份后所有新產(chǎn)生的數(shù)據(jù)
完全備份，完全備份+差異備份 ，完全備份+增量備份的區(qū)別
完全備份
假設(shè)，每天都把當(dāng)天的內(nèi)容全部備份一遍
完全備份+增量備份
? ? ? ? ? ? ? ? ?18:00 ? user ? ?文件 ? ? ?備份
1 ? ?完全? ? ? ? ? ? ? ? ?10 ? ? ?1.sql? ? ? ? 10
2 ? ?增量 ? ?18:00? ? ? 5 ? ? ?2.sql? ? ? ? ? 5
3 ? ?增量 ? ?18:00? ? ?10 ? ? ?3.sql? ? ? ?10
4 ? ?增量 ? ?18:00? ? ?20 ? ? ?4.sql? ? ? ?20 ?
5 ? ?增量 ? ?18:00? ? ? ?1 ? ? ?5.sql? ? ? ? ?1
6 ? ?增量 ? ?18:00? ? ?10 ? ? ?6.sql? ? ? ?10
7 ? ?增量 ? ?18:00? ? ? ?5 ? ? ?7.sql? ? ? ? ?5
完全備份+差異備份
? ? ? ? ? ? ? ? ?18:00 ? user? ? ? 文件 ? ? ?備份
1 ? ?完全? ? ? ? ? ? ? ? ? 10 ? ? ?1.sql? ? ? ? 10
2 ? ?差異 ? ?18:00? ? ? ?5 ? ? ?2.sql? ? ? ? ? 5
3 ? ?差異 ? ?18:00? ? ?10 ? ? ?3.sql? ? ? ? 15
4 ? ?差異 ? ?18:00? ? ?20 ? ? ?4.sql? ? ? ? 35 ?
5 ? ?差異 ? ?18:00? ? ? ?1 ? ? ?5.sql? ? ? ? 36
6 ? ?差異 ? ?18:00? ? ?10 ? ? ?6.sql? ? ? ? 46
7 ? ?差異 ? ?18:00? ? ? 5? ? ? ?7.sql? ? ? ? 51
完全備份及恢復(fù)
- 完全備份：mysqldump ?-uroot ? -p密碼 ?庫名 ?> ?目錄/xxx.sql
- 完全恢復(fù)：mysql ? ??-uroot ? -p密碼 ?[庫名] ?< ?目錄/xxx.sql
- 備份時庫名的表示方式：
--all-databases ?或 ?-A ? ? ?//所有庫
數(shù)據(jù)庫名? ? ? ? ? ? ? ? ? ? ? ? ? ?//單個庫
數(shù)據(jù)庫名 表名? ? ? ? ? ? ? ? ???//單張表
-B ?數(shù)據(jù)庫1 ? 數(shù)據(jù)庫2 ? ? ??//多個庫
注意事項(xiàng)：無論是備份還是恢復(fù)，都要驗(yàn)證用戶權(quán)限！！！
應(yīng)用示例：
將所有庫備份為allbak.sql文件
將db3庫備份為db3.user文件
[root@host51 ~]# mkdir /mybak [root@host51 ~]# mysqldump -uroot -p"123456" -A > /mybak/fulldata.sql[root@host51 ~]# wc -l /mybak/fulldata.sql 1370 /mybak/fulldata.sql[root@host51 ~]# scp /mybak/fulldata.sql root@192.168.4.52:/opt[root@host52 ~]# ls /opt fulldata.sql [root@host52 ~]# mysql -uroot -p123qqq...A < /opt/fulldata.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@host52 ~]# mysql -uroot -p123qqq...A [root@host52 ~]# ls /var/lib/mysql auto.cnf db1 ib_buffer_pool mysql public_key.pem ca-key.pem db2 ibdata1 mysql.sock server-cert.pem ca.pem db3 ib_logfile0 mysql.sock.lock server-key.pem client-cert.pem db4 ib_logfile1 performance_schema sys client-key.pem db5 ibtmp1 private_key.pem tian [root@host52 ~]# systemctl stop mysqld [root@host52 ~]# systemctl start mysqld [root@host52 ~]# mysql -uroot -p123qqq...A mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@host52 ~]# mysql -uroot -p123456[root@host51 ~]# mysqldump -uroot -p"123456" db1 > /mybak/db1.sql [root@host51 ~]# mysqldump -uroot -p"123456" -B db2 db3 db5 > /mybak/thdb.sql [root@host51 ~]# mysqldump -uroot -p"123456" -B db3 user > /mybak/db3_user.sql [root@host51 ~]# ls /mybak/ db1.sql db3_user.sql fulldata.sql thdb.sql [root@host52 ~]# mysql -uroot -p123456 mysql> drop database db1; [root@host51 ~]# scp /mybak/db1.sql root@192.168.4.52:/root [root@host52 ~]# ls db1.sql[root@host52 ~]# mysql -uroot -p123456 db1 < /root/db1.sql #一定要在數(shù)據(jù)庫內(nèi)創(chuàng)建新的db1庫 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1049 (42000): Unknown database 'db1' [root@host52 ~]# mysql -uroot -p123456 mysql> create database db1; [root@host52 ~]# mysql -uroot -p123456 db1 < /root/db1.sql ********************************************************************* [root@host52 ~]# mysql -uroot -p123456 mysql> use db3; mysql> show tables; +---------------+ | Tables_in_db3 | +---------------+ | user | +---------------+ 1 row in set (0.00 sec)mysql> drop table user; mysql> show tables; Empty set (0.00 sec [root@host51 ~]# scp /mybak/db3_user.sql root@192.168.4.52:/root [root@host52 ~]# ls db1.sql db3_user.sql [root@host52 ~]# mysql -uroot -p123456 db3 < /root/db3_user.sql [root@host52 ~]# mysql -uroot -p123456 mysql> use db3; mysql> show tables; +---------------+ | Tables_in_db3 | +---------------+ | user | +---------------+ 1 row in set (0.01 sec)mysql> select count(*) from db3.user; +----------+ | count(*) | +----------+ | 26 | +----------+ 1 row in set (0.00 sec)備份單個表，單個庫時要指定庫名，多個庫，所有庫時不需要指定庫名 如果導(dǎo)入的庫名與現(xiàn)在數(shù)據(jù)庫的庫名重復(fù)，那么現(xiàn)在數(shù)據(jù)庫該庫的數(shù)據(jù)會被覆蓋完全備份的缺點(diǎn)：如果一直每天用完全備份，在執(zhí)行第一天和第二天的備份之間，如果不小心刪除了數(shù)據(jù)，只能找前一天的數(shù)據(jù)進(jìn)行恢復(fù)，但完全備份只備份了前一天的數(shù)據(jù)，新產(chǎn)生的數(shù)據(jù)恢復(fù)不了增量備份
binlog日志
- 什么是binlog日志？
- 啟用日志
| 配置項(xiàng) | 用途 |
| server_id=數(shù)字 | 指定id值（1-255） |
| log_bin=[目錄名/文件名] | 啟用binlog日志 |
| max_binlog_size=數(shù)值m | 指定日志文件容量，默認(rèn)1G |
- binlog相關(guān)文件
主機(jī)名-bin.index? ? ? ? ? ? ? ? ? ? ? ? ?索引文件
主機(jī)名-bin.000001? ? ? ? ? ? ? ? ? ? ? 第一個二進(jìn)制文件
主機(jī)名-bin.000002? ? ? ? ? ? ? ? ? ? ? 第二個二進(jìn)制文件?
- 手動生成新的日志文件
- 只有當(dāng)文件大于1G才會自動生成，默認(rèn)數(shù)據(jù)都會寫在新生成的日志文件中，初始的日志文件大小為15mysql> flush ?logs; ? ?
第一種方法：進(jìn)入數(shù)據(jù)庫，輸入flush ?logs命令
ysql> flush logs; #進(jìn)入數(shù)據(jù)庫，輸入flush logs命令 Query OK, 0 rows affected (0.01 sec)mysql> show master status; #查看，此時生成了一個新的日志文件 +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | host51-bin.000002 | 154 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) [root@host51 lib]# ls /var/lib/mysql #再次查看數(shù)據(jù)庫主目錄，此時多出了一個002的新的二進(jìn)制文件 auto.cnf db1 host51-bin.000001 ib_logfile0 mysql.sock.lock server-key.pem ca-key.pem db2 host51-bin.000002 ib_logfile1 performance_schema sys ca.pem db3 host51-bin.index ibtmp1 private_key.pem tian client-cert.pem db4 ib_buffer_pool mysql public_key.pem client-key.pem db5 ibdata1 mysql.sock server-cert.pem第二種方法：
[root@host51 ~]# mysql -uroot -p123456 -e"flush logs"[root@host51 ~]# ls /var/lib/mysql auto.cnf db4 ib_logfile0 public_key.pem ca-key.pem db5 ib_logfile1 server-cert.pem ca.pem host51-bin.000001 ibtmp1 server-key.pem client-cert.pem host51-bin.000002 mysql sys client-key.pem host51-bin.000003 mysql.sock tian db1 host51-bin.index mysql.sock.lock db2 ib_buffer_pool performance_schema db3 ibdata1 private_key.pem第三種方法：
[root@host51 ~]# mysqldump -uroot -p123456 --flush-logs db1 > /mybak/db1.sql[root@host51 ~]# ls /var/lib/mysql auto.cnf db4 ibdata1 private_key.pem ca-key.pem db5 ib_logfile0 public_key.pem ca.pem host51-bin.000001 ib_logfile1 server-cert.pem client-cert.pem host51-bin.000002 ibtmp1 server-key.pem client-key.pem host51-bin.000003 mysql sys db1 host51-bin.000004 mysql.sock tian db2 host51-bin.index mysql.sock.lock db3 ib_buffer_pool performance_schema [root@host51 ~]# mysqldump -uroot -p123456 --flush-logs -B db2 db3 > /mybak/db1.sql #導(dǎo)出多個數(shù)據(jù)庫，會自動生成相對應(yīng)的新的日志文件[root@host51 ~]# ls /var/lib/mysql auto.cnf db4 host51-bin.index mysql.sock.lock ca-key.pem db5 ib_buffer_pool performance_schema ca.pem host51-bin.000001 ibdata1 private_key.pem client-cert.pem host51-bin.000002 ib_logfile0 public_key.pem client-key.pem host51-bin.000003 ib_logfile1 server-cert.pem db1 host51-bin.000004 ibtmp1 server-key.pem db2 host51-bin.000005 mysql sys db3 host51-bin.000006 mysql.sock tian第四種方法：
[root@host51 ~]# systemctl restart mysqld [root@host51 ~]# ls /var/lib/mysql auto.cnf db5 ib_buffer_pool private_key.pem ca-key.pem host51-bin.000001 ibdata1 public_key.pem ca.pem host51-bin.000002 ib_logfile0 server-cert.pem client-cert.pem host51-bin.000003 ib_logfile1 server-key.pem client-key.pem host51-bin.000004 ibtmp1 sys db1 host51-bin.000005 mysql tian db2 host51-bin.000006 mysql.sock db3 host51-bin.000007 mysql.sock.lock db4 host51-bin.index performance_schema清理日志
刪除指定編號之前的binlog日志文件
Mysql? >? purge? master? logs? to "binlog文件名";
刪除所有binlog日志，重新建日志
Mysql? >? reset? master;
[root@host51 ~]# ls /var/lib/mysql auto.cnf db5 ib_buffer_pool private_key.pem ca-key.pem host51-bin.000001 ibdata1 public_key.pem ca.pem host51-bin.000002 ib_logfile0 server-cert.pem client-cert.pem host51-bin.000003 ib_logfile1 server-key.pem client-key.pem host51-bin.000004 ibtmp1 sys db1 host51-bin.000005 mysql tian db2 host51-bin.000006 mysql.sock db3 host51-bin.000007 mysql.sock.lock db4 host51-bin.index performance_schema [root@host51 ~]# mysql -uroot -p123456 mysql> purge master logs to "host51-bin.000003"; #刪除編號是000003之前的binlog日志 [root@host51 ~]# ls /var/lib/mysql auto.cnf db4 ib_buffer_pool performance_schema ca-key.pem db5 ibdata1 private_key.pem ca.pem host51-bin.000003 ib_logfile0 public_key.pem client-cert.pem host51-bin.000004 ib_logfile1 server-cert.pem client-key.pem host51-bin.000005 ibtmp1 server-key.pem db1 host51-bin.000006 mysql sys db2 host51-bin.000007 mysql.sock tian db3 host51-bin.index mysql.sock.lock mysql> reset master; #刪除所有binlog日志，重建新的日志 Query OK, 0 rows affected (0.01 sec) [root@host51 ~]# ls /var/lib/mysql auto.cnf db1 host51-bin.000001 ib_logfile1 performance_schema sys ca-key.pem db2 host51-bin.index ibtmp1 private_key.pem tian ca.pem db3 ib_buffer_pool mysql public_key.pem client-cert.pem db4 ibdata1 mysql.sock server-cert.pem client-key.pem db5 ib_logfile0 mysql.sock.lock server-key.pem自定義日志目錄和名稱?? ?
[root@host51 ~]# vim /etc/my.cnf 17 log_bin=/mylog/tian [root@host51 ~]# mkdir /mylog [root@host51 ~]# chown mysql /mylog [root@host51 ~]# ls -ld /mylog drwxr-xr-x. 2 mysql root 6 2月 18 14:10 /mylog [root@host51 ~]# systemctl restart mysqld [root@host51 ~]# ls /mylog tian.000001 tian.index [root@host51 ~]# mysql -uroot -p123456 -e"flush logs" [root@host51 ~]# ls /mylog tian.000001 tian.000002 tian.index [root@host51 ~]# mysql -uroot -p123456 mysql> show master status; +-------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------+----------+--------------+------------------+-------------------+ | tian.000002 | 154 | | | | +-------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)mysql> flush logs; mysql> show master status; +-------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------+----------+--------------+------------------+-------------------+ | tian.000003 | 154 | | | | +-------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)mysql> flush logs;mysql> show master status; +-------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------+----------+--------------+------------------+-------------------+ | tian.000004 | 154 | | | | +-------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)mysql> purge master logs to "tian.000004";[root@host51 ~]# ls /mylog tian.000004 tian.index當(dāng)想用某個日志文件只記錄某個庫下的數(shù)據(jù)，可以使用生成日志文件恢復(fù)數(shù)據(jù)
分析日志
- 查看日志當(dāng)前的記錄格式
mysql? ?>? show? ?variables? like? "binlog_format"
- 日志的三種記錄方式
1.statement? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?報表模式
2.row? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?行模式（默認(rèn)）
3.mixed? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 混合模式?
- 修改日志記錄格式
[root@localhost ~]#? ?vim? ?/etc/my.cnf
[mysqld]
....
binlog_format="記錄方式名"
[root@localhost ~]# systemctl? restart? ?mysqld
- 查看日志內(nèi)容
mysqlbinlog? ? [選項(xiàng)]? ? ?binlog日志文件名?
| 選項(xiàng) | 用途 |
| --strat-datetime="yyyy-mm-dd? hh:mm:ss" | 起始時間 |
| --stop-datetime="yyyy-mm-dd? hh:mm:ss" | 結(jié)束時間 |
| --start-position | 起始偏移量 |
| --stop-postion | 結(jié)束偏移量 |
?
mysql> show variables like "%binlog%"; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log_statements_unsafe_for_binlog | ON | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sync_binlog | 1 | +-----------------------------------------+----------------------+ 20 rows in set (0.02 sec)[root@host51 ~]# vim /etc/my.cnf 4 [mysqld]5 #skip-grant-tables6 secure_file_priv=/myload7 validate_password_policy=08 validate_password_length=69 server_id=5110 binlog_format="mixed"[root@host51 ~]# systemctl restart mysqld [root@host51 ~]# mysql -uroot -p123456 mysql> show master status; +-------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------+----------+--------------+------------------+-------------------+ | tian.000006 | 154 | | | | +-------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)mysql> show variables like "%binlog%"; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | MIXED | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log_statements_unsafe_for_binlog | ON | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sync_binlog | 1 | +-----------------------------------------+----------------------+ 20 rows in set (0.00 sec)mysql> show variables like "binlog_format"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.03 sec)恢復(fù)數(shù)據(jù)
- 基本思路
使用mysqlbinlog提取歷史SQL操作
通過管道交給mysql命令執(zhí)行
- 命令格式
mysqlbinlog? ? ?日志文件? ? ?|? mysql? -uroot? -p密碼
- 恢復(fù)所有數(shù)據(jù)
- 恢復(fù)部分新產(chǎn)生的數(shù)據(jù)
查看和修改日志文件記錄的命令格式
區(qū)分記錄的多條SQL命令：偏移量，binlog日志文件的默認(rèn)初始偏移量為154；時間
mysql> show variables like "%binlog%"; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log_statements_unsafe_for_binlog | ON | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sync_binlog | 1 | +-----------------------------------------+----------------------+ 20 rows in set (0.02 sec)[root@host51 ~]# vim /etc/my.cnf4 [mysqld]5 #skip-grant-tables6 secure_file_priv=/myload7 validate_password_policy=08 validate_password_length=69 server_id=5110 binlog_format="mixed"[root@host51 ~]# systemctl restart mysqld [root@host51 ~]# mysql -uroot -p123456 mysql> show master status; +-------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------+----------+--------------+------------------+-------------------+ | tian.000006 | 154 | | | | +-------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)mysql> show variables like "binlog_format"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.03 sec)mysql> insert into db3.user(name,uid) values("bob" ,888); mysql> insert into db3.user(name,uid) values("bob" ,888); mysql> insert into db3.user(name,uid) values("bob" ,888);mysql> delete from db3.user where name like "aaa%"; Query OK, 5 rows affected (0.03 sec)mysql> update db3.user set gid=2020 where id<=10; Query OK, 10 rows affected (0.02 sec) Rows matched: 10 Changed: 10 Warnings: 0mysql> show master status; +-------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------+----------+--------------+------------------+-------------------+ | tian.000006 | 1696 | | | | +-------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)[root@host51 ~]# ls /mylog tian.000004 tian.000005 tian.000006 tian.index [root@host51 ~]# scp /mylog/tian.000006 root@192.168.4.52:/root#######在host52（192.168.4.52）主機(jī)上讀取日志內(nèi)容恢復(fù)數(shù)據(jù) [root@host52 ~]# mysqlbinlog /root/tian.000006 #讀取日志文件內(nèi)容，查看其起始偏移量以及結(jié)束偏移量 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200218 15:23:44 server id 51 end_log_pos 123 CRC32 0x5e885f9a Start: binlog v 4, server v 5.7.17-log created 200218 15:23:44 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' AJFLXg8zAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAkUteEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AZpfiF4= '/*!*/; # at 123 #200218 15:23:44 server id 51 end_log_pos 154 CRC32 0xf9838a23 Previous-GTIDs # [empty] # at 154 #200218 15:26:17 server id 51 end_log_pos 219 CRC32 0x4aecb3cc Anonymous_GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #200218 15:26:17 server id 51 end_log_pos 293 CRC32 0xde3a9032 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010777/*!*/; SET @@session.pseudo_thread_id=3/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 293 # at 325 #200218 15:26:17 server id 51 end_log_pos 325 CRC32 0xa1ecd5ea Intvar SET INSERT_ID=75/*!*/; #200218 15:26:17 server id 51 end_log_pos 446 CRC32 0xf0564a48 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010777/*!*/; insert into db3.user(name,uid) values("bob" ,888) /*!*/; # at 446 #200218 15:26:17 server id 51 end_log_pos 477 CRC32 0x39358b0d Xid = 6 COMMIT/*!*/; # at 477 #200218 15:26:18 server id 51 end_log_pos 542 CRC32 0x405ebe42 Anonymous_GTID last_committed=1 sequence_number=2 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 542 #200218 15:26:18 server id 51 end_log_pos 616 CRC32 0x30796267 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010778/*!*/; BEGIN /*!*/; # at 616 # at 648 #200218 15:26:18 server id 51 end_log_pos 648 CRC32 0xade407c5 Intvar SET INSERT_ID=76/*!*/; #200218 15:26:18 server id 51 end_log_pos 769 CRC32 0xe5bfc8f4 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010778/*!*/; insert into db3.user(name,uid) values("bob" ,888) /*!*/; # at 769 #200218 15:26:18 server id 51 end_log_pos 800 CRC32 0xf8b13fe7 Xid = 7 COMMIT/*!*/; # at 800 #200218 15:26:18 server id 51 end_log_pos 865 CRC32 0x9029a771 Anonymous_GTID last_committed=2 sequence_number=3 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 865 #200218 15:26:18 server id 51 end_log_pos 939 CRC32 0x7b608b92 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010778/*!*/; BEGIN /*!*/; # at 939 # at 971 #200218 15:26:18 server id 51 end_log_pos 971 CRC32 0x27c7f729 Intvar SET INSERT_ID=77/*!*/; #200218 15:26:18 server id 51 end_log_pos 1092 CRC32 0x78ab151e Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010778/*!*/; insert into db3.user(name,uid) values("bob" ,888) /*!*/; # at 1092 #200218 15:26:18 server id 51 end_log_pos 1123 CRC32 0x613af939 Xid = 8 COMMIT/*!*/; # at 1123 #200218 15:26:59 server id 51 end_log_pos 1188 CRC32 0xb2ef9056 Anonymous_GTID last_committed=3 sequence_number=4 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1188 #200218 15:26:59 server id 51 end_log_pos 1262 CRC32 0x913ad69e Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010819/*!*/; BEGIN /*!*/; # at 1262 #200218 15:26:59 server id 51 end_log_pos 1380 CRC32 0xeb0f4e12 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010819/*!*/; delete from db3.user where name like "aaa%" /*!*/; # at 1380 #200218 15:26:59 server id 51 end_log_pos 1411 CRC32 0x2f718d62 Xid = 9 COMMIT/*!*/; # at 1411 #200218 15:27:43 server id 51 end_log_pos 1476 CRC32 0x9022267b Anonymous_GTID last_committed=4 sequence_number=5 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1476 #200218 15:27:43 server id 51 end_log_pos 1550 CRC32 0x99883f65 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010863/*!*/; BEGIN /*!*/; # at 1550 #200218 15:27:43 server id 51 end_log_pos 1665 CRC32 0xb4a84776 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1582010863/*!*/; update db3.user set gid=2020 where id<=10 /*!*/; # at 1665 #200218 15:27:43 server id 51 end_log_pos 1696 CRC32 0xb21b5029 Xid = 10 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@host52 ~]# mysqlbinlog --start-position=325 --stop-position=1123 /root/tian.000006 | mysql -uroot -p123456[root@host52 ~]# mysql -uroot -p123456mysql> select * from db3.user where name regexp "^bob"; +----+------+------+----------+------+------+---------+---------+-------+ | id | name | age | password | uid | gid | comment | homedir | shell | +----+------+------+----------+------+------+---------+---------+-------+ | 55 | bob | 20 | NULL | NULL | NULL | NULL | NULL | NULL | | 75 | bob | 20 | NULL | 888 | NULL | NULL | NULL | NULL | | 76 | bob | 20 | NULL | 888 | NULL | NULL | NULL | NULL | | 77 | bob | 20 | NULL | 888 | NULL | NULL | NULL | NULL | +----+------+------+----------+------+------+---------+---------+-------+ 4 rows in set (0.00 sec)?
總結(jié)
以上是生活随笔為你收集整理的DBA基础（一）用户授权的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MySQL数据库基础（三）数据的导入导出
- 下一篇： DBA（二）:percona软件、inn