mysql事務(wù)管理

RDBMS : ACID (原子性、一致性、隔離性、持久性)

automicity：原子性，事務(wù)所引起的數(shù)據(jù)操作，要么都完成，要么都不執(zhí)行

consistency : 一致性，

isolation : 隔離性

事務(wù)調(diào)度：事務(wù)之間影響最小

MVCC：多版本并發(fā)控制

durability : 一旦事務(wù)成功完成，系統(tǒng)必須保證任何故障都不會影響事務(wù)表現(xiàn)出不一致性

1、事務(wù)提交之前就已經(jīng)寫出數(shù)據(jù)至持久性存儲

2、結(jié)合事務(wù)日志完成

事務(wù)日志：順序IO

數(shù)據(jù)文件：隨機(jī)IO

事務(wù)的狀態(tài)：

活動的：active

部分提交的：最后一條語句執(zhí)行后

失敗的：

終止的：
提交的：

事務(wù)：并發(fā)執(zhí)行

1、提高吞吐量和資源利用率

2、減少等待時間

事務(wù)調(diào)度：

可恢復(fù)調(diào)度：
無級聯(lián)調(diào)度：

并發(fā)控制依賴的技術(shù)手段：

鎖

餓死

死鎖

時間戳

多版本快照隔離

多事務(wù)同時執(zhí)行：彼此之間互不影響的方式進(jìn)行并行

事務(wù)之間交互

通過數(shù)據(jù)集

事務(wù)日志：

重做日志

redo log

撤銷日志

undo log

隔離性

隔離級別

read uncommitted ：讀未提交

read committed ： 讀提交

repatable read：可重讀

seriablizable：可串行

start tansaction：啟動事務(wù)

commit：提交

rollback : 回滾

如果沒有明確啟動事務(wù)：

autocommit ：能實(shí)現(xiàn)自動提交，每一個操作都直接提交

建議：明確使用事務(wù)，并且關(guān)閉自動提交：

保存點(diǎn)：savepoint sid

回滾至保存點(diǎn)： rollback to  sid

mysql用戶和權(quán)限管理

user: Contains user accounts, global privileges, and other non-privilege columns.
user: 用戶帳號、全局權(quán)限
db: Contains database-level privileges.
db: 庫級別權(quán)限
host: Obsolete.
host: 廢棄
tables_priv: Contains table-level privileges.
表級別權(quán)限
columns_priv: Contains column-level privileges.
列級別權(quán)限
procs_priv: Contains stored procedure and function privileges.
存儲過程和存儲函數(shù)相關(guān)的權(quán)限
proxies_priv: Contains proxy-user privileges.
代理用戶權(quán)限

用戶賬號：

用戶名@主機(jī)

用戶名：16字符以內(nèi)

主機(jī)：

主機(jī)名：www.test.com,mysql

IP:172.16.10.1

網(wǎng)絡(luò)地址：172.16.0.0/255.255.0.0

通配符：%，_

172.16.%.%

%.test.com

--skip-name-resolve 略過名稱解析

權(quán)限級別

全局級別

庫

表：delete、alter、trigger、

列：select、insert、update

存儲過程和存儲函數(shù)

臨時表：內(nèi)存表

heap:16MB

觸發(fā)器，主動數(shù)據(jù)庫

創(chuàng)建用戶：

create user 'usernam'@'hostname' identified by 'password'

insert into mysql.user

grant

grant all privileges on [object_type] db.* to username@'%'

object_type

table

function

procedure

grant all privileges on function db.abc to username@'localhost'

resource_option: 

MAX_QUERIES_PER_HOUR count 限制資源使用權(quán)限

MAX_UPDATES_PER_HOUR count 限制每小時update使用次數(shù)

MAX_CONNECTIONS_PER_HOUR count 限制新的鏈接請求

MAX_USER_CONNECTIONS count 某一個用戶最多允許鏈接多少次

刪除用戶：drop user 'uername'@'host'

重命名用戶：rename user old_name to new_name

取消授權(quán)：revoke all privileges ,grant option from user@host

重置mysql root密碼

啟動mysqld_safe時傳遞兩個參數(shù)

--skip-grant-tables

--skip-networking

通過更新授權(quán)表方式直接修改其密碼，后移出此兩個選項(xiàng)重啟服務(wù)

mysql日志管理

錯誤日志：

log_error

log_warnigs

服務(wù)器啟動和關(guān)閉過程總的信息

服務(wù)器運(yùn)行過程中的錯誤信息

事件調(diào)度器運(yùn)行一個事件時產(chǎn)生的信息

在從服務(wù)器上啟動從服務(wù)器進(jìn)程時產(chǎn)生的信息

一般查詢?nèi)罩?/span>

general_log

general_log_file

log_output=[file|table|none]

log

慢查詢?nèi)罩?/span>

long_query_time

log_slow_queries={yes|no}

slow_query_log

slow_query_log_file

二進(jìn)制日志：DML,DDL 任何引起或可能引起數(shù)據(jù)庫變化的操作

復(fù)制、即時點(diǎn)恢復(fù)

mysqlbinlog命令：查看mysql二進(jìn)制日志

--start-datetime 'yyyy-mm-dd hh:mm:ss'

--stop-datetime

--start-position

--stop-position

二進(jìn)制日志的格式：

基于語句：statement

基于行：row

混合模式：mixed

二進(jìn)制日志事件：

產(chǎn)生的時間

相對位置

二進(jìn)制日志文件：

索引文件

二進(jìn)制日志文件

查看當(dāng)前正在使用的二進(jìn)制日志文件

show master status;

查看所有的二進(jìn)制日志文件

show binary logs;

刪除日志文件

purge binary logs to '二進(jìn)制日志文件'

查看事件

show binlog events in '二進(jìn)制日志文件名' [from pos]

flush logs：讓日志滾動

中繼日志

從主服務(wù)器的二進(jìn)制日志文件中復(fù)制而來的事件，并保存為日志

事務(wù)日志

事務(wù)性儲存引擎用于保證原子性、一致性、隔離性和持久性

innodb_flush_log_at_trx_commit:

0：每秒同步，并執(zhí)行磁盤flush操作

1：沒事務(wù)同步，并執(zhí)行磁盤flush操作

2：每事務(wù)同步，但不執(zhí)行磁盤flush操作

數(shù)據(jù)庫引擎

MyISAM

不支持事務(wù)

表鎖

不支持外鍵

B樹索引、fulltext索引、空間索引

支持表壓縮

InnoDB

支持事務(wù)

行級鎖

B數(shù)索引、聚簇索引、自適應(yīng)hash索引

表空間、raw磁盤設(shè)備

MRG_MYISAM：

expire_logs_days={0..99}

設(shè)定二進(jìn)制日志的過期天數(shù)，超出此天數(shù)的二進(jìn)制日志文件將被自動刪除。默認(rèn)為0，表示不啟用過期自動刪除功能。如果啟用此功能，自動刪除工作通常發(fā)生在MySQL啟動時或FLUSH日志時。作用范圍為全局，可用于配置文件，屬動態(tài)變量。

general_log={ON|OFF}

設(shè)定是否啟用查詢?nèi)罩荆J(rèn)值為取決于在啟動mysqld時是否使用了--general_log選項(xiàng)。如若啟用此項(xiàng)，其輸出位置則由--log_output選項(xiàng)進(jìn)行定義，如果log_output的值設(shè)定為NONE，即使用啟用查詢?nèi)罩?，其也不會記錄任何日志信息。作用范圍為全局，可用于配置文件，屬動態(tài)變量。

general_log_file=FILE_NAME

查詢?nèi)罩镜娜罩疚募Q，默認(rèn)為“hostname.log"。作用范圍為全局，可用于配置文件，屬動態(tài)變量。

binlog-format={ROW|STATEMENT|MIXED}

指定二進(jìn)制日志的類型，默認(rèn)為STATEMENT。如果設(shè)定了二進(jìn)制日志的格式，卻沒有啟用二進(jìn)制日志，則MySQL啟動時會產(chǎn)生警告日志信息并記錄于錯誤日志中。作用范圍為全局或會話，可用于配置文件，且屬于動態(tài)變量。

log={YES|NO}

是否啟用記錄所有語句的日志信息于一般查詢?nèi)罩?general query log)中，默認(rèn)通常為OFF。MySQL 5.6已經(jīng)棄用此選項(xiàng)。

log-bin={YES|NO}

是否啟用二進(jìn)制日志，如果為mysqld設(shè)定了--log-bin選項(xiàng)，則其值為ON，否則則為OFF。其僅用于顯示是否啟用了二進(jìn)制日志，并不反應(yīng)log-bin的設(shè)定值。作用范圍為全局級別，屬非動態(tài)變量。

log_bin_trust_function_creators={TRUE|FALSE}

此參數(shù)僅在啟用二進(jìn)制日志時有效，用于控制創(chuàng)建存儲函數(shù)時如果會導(dǎo)致不安全的事件記錄二進(jìn)制日志條件下是否禁止創(chuàng)建存儲函數(shù)。默認(rèn)值為0，表示除非用戶除了CREATE ROUTING或ALTER ROUTINE權(quán)限外還有SUPER權(quán)限，否則將禁止創(chuàng)建或修改存儲函數(shù)，同時，還要求在創(chuàng)建函數(shù)時必需為之使用DETERMINISTIC屬性，再不然就是附帶READS SQL DATA或NO SQL屬性。設(shè)置其值為1時則不啟用這些限制。作用范圍為全局級別，可用于配置文件，屬動態(tài)變量。

log_error=/PATH/TO/ERROR_LOG_FILENAME

定義錯誤日志文件。作用范圍為全局或會話級別，可用于配置文件，屬非動態(tài)變量。

log_output={TABLE|FILE|NONE}

定義一般查詢?nèi)罩竞吐樵內(nèi)罩镜谋４娣绞?，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的組合(用逗號隔開)，默認(rèn)為TABLE。如果組合中出現(xiàn)了NONE，那么其它設(shè)定都將失效，同時，無論是否啟用日志功能，也不會記錄任何相關(guān)的日志信息。作用范圍為全局級別，可用于配置文件，屬動態(tài)變量。

log_query_not_using_indexes={ON|OFF}

設(shè)定是否將沒有使用索引的查詢操作記錄到慢查詢?nèi)罩?。作用范圍為全局級別，可用于配置文件，屬動態(tài)變量。

log_slave_updates

用于設(shè)定復(fù)制場景中的從服務(wù)器是否將從主服務(wù)器收到的更新操作記錄進(jìn)本機(jī)的二進(jìn)制日志中。本參數(shù)設(shè)定的生效需要在從服務(wù)器上啟用二進(jìn)制日志功能。

log_slow_queries={YES|NO}

是否記錄慢查詢?nèi)罩尽Ｂ樵兪侵覆樵兊膱?zhí)行時間超出long_query_time參數(shù)所設(shè)定時長的事件。MySQL 5.6將此參數(shù)修改為了slow_query_log。作用范圍為全局級別，可用于配置文件，屬動態(tài)變量。

log_warnings=#

設(shè)定是否將警告信息記錄進(jìn)錯誤日志。默認(rèn)設(shè)定為1，表示啟用；可以將其設(shè)置為0以禁用；而其值為大于1的數(shù)值時表示將新發(fā)起連接時產(chǎn)生的“失敗的連接”和“拒絕訪問”類的錯誤信息也記錄進(jìn)錯誤日志。

long_query_time=#

設(shè)定區(qū)別慢查詢與一般查詢的語句執(zhí)行時間長度。這里的語句執(zhí)行時長為實(shí)際的執(zhí)行時間，而非在CPU上的執(zhí)行時長，因此，負(fù)載較重的服務(wù)器上更容易產(chǎn)生慢查詢。其最小值為0，默認(rèn)值為10，單位是秒鐘。它也支持毫秒級的解析度。作用范圍為全局或會話級別，可用于配置文件，屬動態(tài)變量。

max_binlog_cache_size{4096 .. 18446744073709547520}

二進(jìn)定日志緩存空間大小，5.5.9及以后的版本僅應(yīng)用于事務(wù)緩存，其上限由max_binlog_stmt_cache_size決定。作用范圍為全局級別，可用于配置文件，屬動態(tài)變量。

max_binlog_size={4096 .. 1073741824}

設(shè)定二進(jìn)制日志文件上限，單位為字節(jié)，最小值為4K，最大值為1G，默認(rèn)為1G。某事務(wù)所產(chǎn)生的日志信息只能寫入一個二進(jìn)制日志文件，因此，實(shí)際上的二進(jìn)制日志文件可能大于這個指定的上限。作用范圍為全局級別，可用于配置文件，屬動態(tài)變量。

max_relay_log_size={4096..1073741824}

設(shè)定從服務(wù)器上中繼日志的體積上限，到達(dá)此限度時其會自動進(jìn)行中繼日志滾動。此參數(shù)值為0時，mysqld將使用max_binlog_size參數(shù)同時為二進(jìn)制日志和中繼日志設(shè)定日志文件體積上限。作用范圍為全局級別，可用于配置文件，屬動態(tài)變量。

innodb_log_buffer_size={262144 .. 4294967295}

設(shè)定InnoDB用于輔助完成日志文件寫操作的日志緩沖區(qū)大小，單位是字節(jié)，默認(rèn)為8MB。較大的事務(wù)可以借助于更大的日志緩沖區(qū)來避免在事務(wù)完成之前將日志緩沖區(qū)的數(shù)據(jù)寫入日志文件，以減少I/O操作進(jìn)而提升系統(tǒng)性能。因此，在有著較大事務(wù)的應(yīng)用場景中，建議為此變量設(shè)定一個更大的值。作用范圍為全局級別，可用于選項(xiàng)文件，屬非動態(tài)變量。

innodb_log_file_size={108576 .. 4294967295}

設(shè)定日志組中每個日志文件的大小，單位是字節(jié)，默認(rèn)值是5MB。較為明智的取值范圍是從1MB到緩存池體積的1/n，其中n表示日志組中日志文件的個數(shù)。日志文件越大，在緩存池中需要執(zhí)行的檢查點(diǎn)刷寫操作就越少，這意味著所需的I/O操作也就越少，然而這也會導(dǎo)致較慢的故障恢復(fù)速度。作用范圍為全局級別，可用于選項(xiàng)文件，屬非動態(tài)變量。

innodb_log_files_in_group={2 .. 100}

設(shè)定日志組中日志文件的個數(shù)。InnoDB以循環(huán)的方式使用這些日志文件。默認(rèn)值為2。作用范圍為全局級別，可用于選項(xiàng)文件，屬非動態(tài)變量。

innodb_log_group_home_dir=/PATH/TO/DIR

設(shè)定InnoDB重做日志文件的存儲目錄。在缺省使用InnoDB日志相關(guān)的所有變量時，其默認(rèn)會在數(shù)據(jù)目錄中創(chuàng)建兩個大小為5MB的名為ib_logfile0和ib_logfile1的日志文件。作用范圍為全局級別，可用于選項(xiàng)文件，屬非動態(tài)變量。

relay_log=file_name

設(shè)定中繼日志的文件名稱，默認(rèn)為host_name-relay-bin。也可以使用絕對路徑，以指定非數(shù)據(jù)目錄來存儲中繼日志。作用范圍為全局級別，可用于選項(xiàng)文件，屬非動態(tài)變量。

relay_log_index=file_name

設(shè)定中繼日志的索引文件名，默認(rèn)為為數(shù)據(jù)目錄中的host_name-relay-bin.index。作用范圍為全局級別，可用于選項(xiàng)文件，屬非動態(tài)變量。

relay-log-info-file=file_name

設(shè)定中繼服務(wù)用于記錄中繼信息的文件，默認(rèn)為數(shù)據(jù)目錄中的relay-log.info。作用范圍為全局級別，可用于選項(xiàng)文件，屬非動態(tài)變量。

relay_log_purge={ON|OFF}

設(shè)定對不再需要的中繼日志是否自動進(jìn)行清理。默認(rèn)值為ON。作用范圍為全局級別，可用于選項(xiàng)文件，屬動態(tài)變量。

relay_log_space_limit=#

設(shè)定用于存儲所有中繼日志文件的可用空間大小。默認(rèn)為0，表示不限定。最大值取決于系統(tǒng)平臺位數(shù)。作用范圍為全局級別，可用于選項(xiàng)文件，屬非動態(tài)變量。

slow_query_log={ON|OFF}

設(shè)定是否啟用慢查詢?nèi)罩尽?或OFF表示禁用，1或ON表示啟用。日志信息的輸出位置取決于log_output變量的定義，如果其值為NONE，則即便slow_query_log為ON，也不會記錄任何慢查詢信息。作用范圍為全局級別，可用于選項(xiàng)文件，屬動態(tài)變量。

slow_query_log_file=/PATH/TO/SOMEFILE

設(shè)定慢查詢?nèi)罩疚募拿Q。默認(rèn)為hostname-slow.log，但可以通過--slow_query_log_file選項(xiàng)修改。作用范圍為全局級別，可用于選項(xiàng)文件，屬動態(tài)變量。

sql_log_bin={ON|OFF}

用于控制二進(jìn)制日志信息是否記錄進(jìn)日志文件。默認(rèn)為ON，表示啟用記錄功能。用戶可以在會話級別修改此變量的值，但其必須具有SUPER權(quán)限。作用范圍為全局和會話級別，屬動態(tài)變量。

sql_log_off={ON|OFF}

用于控制是否禁止將一般查詢?nèi)罩绢愋畔⒂涗涍M(jìn)查詢?nèi)罩疚募?。默認(rèn)為OFF，表示不禁止記錄功能。用戶可以在會話級別修改此變量的值，但其必須具有SUPER權(quán)限。作用范圍為全局和會話級別，屬動態(tài)變量。

sync_binlog=#

設(shè)定多久同步一次二進(jìn)制日志至磁盤文件中，0表示不同步，任何正數(shù)值都表示對二進(jìn)制每多少次寫操作之后同步一次。當(dāng)autocommit的值為1時，每條語句的執(zhí)行都會引起二進(jìn)制日志同步，否則，每個事務(wù)的提交會引起二進(jìn)制日志同步。