postgresql怎么解鎖表

--查詢是否鎖表了

創新互聯堅持“要么做到，要么別承諾”的工作理念，服務領域包括：網站設計制作、成都網站建設、企業官網、英文網站、手機端網站、網站推廣等服務，滿足客戶于互聯網時代的長陽網站設計、移動媒體設計的需求，幫助企業找到有效的互聯網解決方案。努力成為您成熟可靠的網絡建設合作伙伴！

1、select oid from pg_class where relname='可能被鎖掉的表的表名'

，會顯示一個oid

2、select pid from pg_locks where relation='剛剛查出來的oid'

--如果查詢到了結果（pid），表示該表被鎖 則需要釋放鎖定

select pg_cancel_backend(上面查到的pid)

事務的隔離級別 全部都是共享鎖嗎

前言:我們都知道事務的幾種性質，數據庫為了維護這些性質，尤其是一致性和隔離性，一般使用加鎖這種方式。同時數據庫又是個高并發的應用，同一時間會有大量的并發訪問，如果加鎖過度，會極大的降低并發處理能力。所以對于加鎖的處理，可以說就是數據庫對于事務處理的精髓所在。這里通過分析MySQL中InnoDB引擎的加鎖機制，來拋磚引玉，讓讀者更好的理解，在事務處理中數據庫到底做了什么。一次封鎖or兩段鎖？因為有大量的并發訪問，為了預防死鎖，一般應用中推薦使用一次封鎖法，就是在方法的開始階段，已經預先知道會用到哪些數據，然后全部鎖住，在方法運行之后，再全部解鎖。這種方式可以有效的避免循環死鎖，但在數據庫中卻不適用，因為在事務開始階段，數據庫并不知道會用到哪些數據。數據庫遵循的是兩段鎖協議，將事務分成兩個階段，加鎖階段和解鎖階段（所以叫兩段鎖）加鎖階段：在該階段可以進行加鎖操作。在對任何數據進行讀操作之前要申請并獲得S鎖（共享鎖，其它事務可以繼續加共享鎖，但不能加排它鎖），在進行寫操作之前要申請并獲得X鎖（排它鎖，其它事務不能再獲得任何鎖）。加鎖不成功，則事務進入等待狀態，直到加鎖成功才繼續執行。解鎖階段：當事務釋放了一個封鎖以后，事務進入解鎖階段，在該階段只能進行解鎖操作不能再進行加鎖操作。事務 加鎖/解鎖處理begin； insert into test ..... 加insert對應的鎖update test set... 加update對應的鎖delete from test .... 加delete對應的鎖commit; 事務提交時，同時釋放insert、update、delete對應的鎖這種方式雖然無法避免死鎖，但是兩段鎖協議可以保證事務的并發調度是串行化（串行化很重要，尤其是在數據恢復和備份的時候）的。事務中的加鎖方式事務的四種隔離級別在數據庫操作中，為了有效保證并發讀取數據的正確性，提出的事務隔離級別。我們的數據庫鎖，也是為了構建這些隔離級別存在的。隔離級別 臟讀（Dirty Read） 不可重復讀（NonRepeatable Read） 幻讀（Phantom Read）未提交讀（Read uncommitted） 可能 可能 可能 已提交讀（Read committed） 不可能 可能 可能 可重復讀（Repeatable read） 不可能 不可能 可能 可串行化（Serializable ） 不可能 不可能 不可能 未提交讀(Read Uncommitted)：允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據提交讀(Read Committed)：只能讀取到已經提交的數據。Oracle等多數數據庫默認都是該級別 (不重復讀)可重復讀(Repeated Read)：可重復讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB默認級別。在SQL標準中，該隔離級別消除了不可重復讀，但是還存在幻象讀串行讀(Serializable)：完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞Read Uncommitted這種級別，數據庫一般都不會用，而且任何操作都不會加鎖，這里就不討論了。MySQL中鎖的種類MySQL中鎖的種類很多，有常見的表鎖和行鎖，也有新加入的Metadata Lock等等,表鎖是對一整張表加鎖，雖然可分為讀鎖和寫鎖，但畢竟是鎖住整張表，會導致并發能力下降，一般是做ddl處理時使用。行鎖則是鎖住數據行，這種加鎖方法比較復雜，但是由于只鎖住有限的數據，對于其它數據不加限制，所以并發能力強，MySQL一般都是用行鎖來處理并發事務。這里主要討論的也就是行鎖。Read Committed（讀取提交內容）在RC級別中，數據的讀取都是不加鎖的，但是數據的寫入、修改和刪除是需要加鎖的。效果如下MySQL show create table class_teacher \G\ Table: class_teacher Create Table: CREATE TABLE `class_teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_teacher_id` (`teacher_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.02 sec) MySQL select * from class_teacher; +----+--------------+------------+ id class_name teacher_id +----+--------------+------------+ 1 初三一班 1 3 初二一班 2 4 初二二班 2 +----+--------------+------------+ 由于MySQL的InnoDB默認是使用的RR級別，所以我們先要將該session開啟成RC級別，并且設置binlog的模式SET session transaction isolation level read committed; SET SESSION binlog_format = 'ROW'; （或者是MIXED）事務A 事務Bbegin; begin;update class_teacher set class_name='初三二班' where teacher_id=1; update class_teacher set class_name='初三三班' where teacher_id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction commit; 為了防止并發過程中的修改沖突，事務A中MySQL給teacher_id=1的數據行加鎖，并一直不commit（釋放鎖），那么事務B也就一直拿不到該行鎖，wait直到超時。這時我們要注意到，teacher_id是有索引的，如果是沒有索引的class_name呢？update class_teacher set teacher_id=3 where class_name = '初三一班';那么MySQL會給整張表的所有數據行的加行鎖。這里聽起來有點不可思議，但是當sql運行的過程中，MySQL并不知道哪些數據行是 class_name = '初三一班'的（沒有索引嘛），如果一個條件無法通過索引快速過濾，存儲引擎層面就會將所有記錄加鎖后返回，再由MySQL Server層進行過濾。但在實際使用過程當中，MySQL做了一些改進，在MySQL Server過濾條件，發現不滿足后，會調用unlock_row方法，把不滿足條件的記錄釋放鎖 (違背了二段鎖協議的約束)。這樣做，保證了最后只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。可見即使是MySQL，為了效率也是會違反規范的。（參見《高性能MySQL》中文第三版p181）這種情況同樣適用于MySQL的默認隔離級別RR。所以對一個數據量很大的表做批量修改的時候，如果無法使用相應的索引，MySQL Server過濾數據的的時候特別慢，就會出現雖然沒有修改某些行的數據，但是它們還是被鎖住了的現象。Repeatable Read（可重讀）這是MySQL中InnoDB默認的隔離級別。我們姑且分“讀”和“寫”兩個模塊來講解。讀讀就是可重讀，可重讀這個概念是一事務的多個實例在并發讀取數據時，會看到同樣的數據行，有點抽象，我們來看一下效果。RC（不可重讀）模式下的展現事務A 事務Bbegin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=1; id class_name teacher_id 1 初三二班 1 2 初三一班 1 update class_teacher set class_name='初三三班' where id=1; commit; select id,class_name,teacher_id from class_teacher where teacher_id=1; id class_name teacher_id 1 初三三班 1 2 初三一班 1 讀到了事務B修改的數據，和第一次查詢的結果不一樣，是不可重讀的。commit; 事務B修改id=1的數據提交之后，事務A同樣的查詢，后一次和前一次的結果不一樣，這就是不可重讀（重新讀取產生的結果不一樣）。這就很可能帶來一些問題，那么我們來看看在RR級別中MySQL的表現：事務A 事務B 事務Cbegin; begin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=1; id class_name teacher_id 1 初三二班 1 2 初三一班 1 update class_teacher set class_name='初三三班' where id=1; commit; insert into class_teacher values (null,'初三三班',1); commit; select id,class_name,teacher_id from class_teacher where teacher_id=1; id class_name teacher_id 1 初三二班 1 2 初三一班 1 沒有讀到事務B修改的數據，和第一次sql讀取的一樣，是可重復讀的。沒有讀到事務C新添加的數據。commit; 我們注意到，當teacher_id=1時，事務A先做了一次讀取，事務B中間修改了id=1的數據，并commit之后，事務A第二次讀到的數據和第一次完全相同。所以說它是可重讀的。那么MySQL是怎么做到的呢？這里姑且賣個關子，我們往下看。不可重復讀和幻讀的區別很多人容易搞混不可重復讀和幻讀，確實這兩者有些相似。但不可重復讀重點在于update和delete，而幻讀的重點在于insert。如果使用鎖機制來實現這兩種隔離級別，在可重復讀中，該sql第一次讀取到數據后，就將這些數據加鎖，其它事務無法修改這些數據，就可以實現可重復讀了。但這種方法卻無法鎖住insert的數據，所以當事務A先前讀取了數據，或者修改了全部數據，事務B還是可以insert數據提交，這時事務A就會發現莫名其妙多了一條之前沒有的數據，這就是幻讀，不能通過行鎖來避免。需要Serializable隔離級別 ，讀用讀鎖，寫用寫鎖，讀鎖和寫鎖互斥，這么做可以有效的避免幻讀、不可重復讀、臟讀等問題，但會極大的降低數據庫的并發能力。所以說不可重復讀和幻讀最大的區別，就在于如何通過鎖機制來解決他們產生的問題。上文說的，是使用悲觀鎖機制來處理這兩種問題，但是MySQL、ORACLE、PostgreSQL等成熟的數據庫，出于性能考慮，都是使用了以樂觀鎖為理論基礎的MVCC（多版本并發控制）來避免這兩種問題。悲觀鎖和樂觀鎖悲觀鎖正如其名，它指的是對數據被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度，因此，在整個數據處理過程中，將數據處于鎖定狀態。悲觀鎖的實現，往往依靠數據庫提供的鎖機制（也只有數據庫層提供的鎖機制才能真正保證數據訪問的排他性，否則，即使在本系統中實現了加鎖機制，也無法保證外部系統不會修改數據）。在悲觀鎖的情況下，為了保證事務的隔離性，就需要一致性鎖定讀。讀取數據時給加鎖，其它事務無法修改這些數據。修改刪除數據時也要加鎖，其它事務無法讀取這些數據。樂觀鎖相對悲觀鎖而言，樂觀鎖機制采取了更加寬松的加鎖機制。悲觀鎖大多數情況下依靠數據庫的鎖機制實現，以保證操作最大程度的獨占性。但隨之而來的就是數據庫性能的大量開銷，特別是對長事務而言，這樣的開銷往往無法承受。而樂觀鎖機制在一定程度上解決了這個問題。樂觀鎖，大多是基于數據版本（ Version ）記錄機制實現。何謂數據版本？即為數據增加一個版本標識，在基于數據庫表的版本解決方案中，一般是通過為數據庫表增加一個 “version” 字段來實現。讀取出數據時，將此版本號一同讀出，之后更新時，對此版本號加一。此時，將提交數據的版本數據與數據庫表對應記錄的當前版本信息進行比對，如果提交的數據版本號大于數據庫表當前版本號，則予以更新，否則認為是過期數據。要說明的是，MVCC的實現沒有固定的規范，每個數據庫都會有不同的實現方式，這里討論的是InnoDB的MVCC。MVCC在MySQL的InnoDB中的實現在InnoDB中，會在每行數據后添加兩個額外的隱藏的值來實現MVCC，這兩個值一個記錄這行數據何時被創建，另外一個記錄這行數據何時過期（或者被刪除）。 在實際操作中，存儲的并不是時間，而是事務的版本號，每開啟一個新事務，事務的版本號就會遞增。 在可重讀Repeatable reads事務隔離級別下：SELECT時，讀取創建版本號=當前事務版本號，刪除版本號為空或當前事務版本號。 INSERT時，保存當前事務版本號為行的創建版本號 DELETE時，保存當前事務版本號為行的刪除版本號 UPDATE時，插入一條新紀錄，保存當前事務版本號為行創建版本號，同時保存當前事務版本號到原來刪除的行 通過MVCC，雖然每行記錄都需要額外的存儲空間，更多的行檢查工作以及一些額外的維護工作，但可以減少鎖的使用，大多數讀操作都不用加鎖，讀數據操作很簡單，性能很好，并且也能保證只會讀取到符合標準的行，也只鎖住必要行。 我們不管從數據庫方面的教課書中學到，還是從網絡上看到，大都是上文中事務的四種隔離級別這一模塊列出的意思，RR級別是可重復讀的，但無法解決幻讀，而只有在Serializable級別才能解決幻讀。于是我就加了一個事務C來展示效果。在事務C中添加了一條teacher_id=1的數據commit，RR級別中應該會有幻讀現象，事務A在查詢teacher_id=1的數據時會讀到事務C新加的數據。但是測試后發現，在MySQL中是不存在這種情況的，在事務C提交后，事務A還是不會讀到這條數據。可見在MySQL的RR級別中，是解決了幻讀的讀問題的。參見下圖讀問題解決了，根據MVCC的定義，并發提交數據時會出現沖突，那么沖突時如何解決呢？我們再來看看InnoDB中RR級別對于寫數據的處理。“讀”與“讀”的區別可能有讀者會疑惑，事務的隔離級別其實都是對于讀數據的定義，但到了這里，就被拆成了讀和寫兩個模塊來講解。這主要是因為MySQL中的讀，和事務隔離級別中的讀，是不一樣的。我們且看，在RR級別中，通過MVCC機制，雖然讓數據變得可重復讀，但我們讀到的數據可能是歷史數據，是不及時的數據，不是數據庫當前的數據！這在一些對于數據的時效特別敏感的業務中，就很可能出問題。對于這種讀取歷史數據的方式，我們叫它快照讀 (snapshot read)，而讀取數據庫當前版本數據的方式，叫當前讀 (current read)。很顯然，在MVCC中：快照讀：就是selectselect * from table ....; 當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，處理的都是當前的數據，需要加鎖。select * from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete; 事務的隔離級別實際上都是定義了當前讀的級別，MySQL為了減少鎖處理（包括等待其它鎖）的時間，提升并發能力，引入了快照讀的概念，使得select不用加鎖。而update、insert這些“當前讀”，就需要另外的模塊來解決了。寫（"當前讀"）事務的隔離級別中雖然只定義了讀數據的要求，實際上這也可以說是寫數據的要求。上文的“讀”，實際是講的快照讀；而這里說的“寫”就是當前讀了。為了解決當前讀中的幻讀問題，MySQL事務使用了Next-Key鎖。Next-Key鎖Next-Key鎖是行鎖和GAP（間隙鎖）的合并，行鎖上文已經介紹了，接下來說下GAP間隙鎖。行鎖可以防止不同事務版本的數據修改提交時造成數據沖突的情況。但如何避免別的事務插入數據就成了問題。我們可以看看RR級別和RC級別的對比RC級別：事務A 事務Bbegin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三二班 30 update class_teacher set class_name='初三四班' where teacher_id=30; insert into class_teacher values (null,'初三二班',30); commit; select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三四班 30 10 初三二班 30 RR級別： 事務A 事務B begin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三二班 30 update class_teacher set class_name='初三四班' where teacher_id=30; insert into class_teacher values (null,'初三二班',30); waiting.... select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三四班 30 commit; 事務Acommit后，事務B的insert執行。通過對比我們可以發現，在RC級別中，事務A修改了所有teacher_id=30的數據，但是當事務Binsert進新數據后，事務A發現莫名其妙多了一行teacher_id=30的數據，而且沒有被之前的update語句所修改，這就是“當前讀”的幻讀。RR級別中，事務A在update后加鎖，事務B無法插入新數據，這樣事務A在update前后讀的數據保持一致，避免了幻讀。這個鎖，就是Gap鎖。MySQL是這么實現的：在class_teacher這張表中，teacher_id是個索引，那么它就會維護一套B+樹的數據關系，為了簡化，我們用鏈表結構來表達（實際上是個樹形結構，但原理相同）如圖所示，InnoDB使用的是聚集索引，teacher_id身為二級索引，就要維護一個索引字段和主鍵id的樹狀結構（這里用鏈表形式表現），并保持順序排列。Innodb將這段數據分成幾個個區間(negative infinity, 5], (5,30], (30,positive infinity)； update class_teacher set class_name='初三四班' where teacher_id=30; 不僅用行鎖，鎖住了相應的數據行；同時也在兩邊的區間，（5,30]和（30，positive infinity），都加入了gap鎖。這樣事務B就無法在這個兩個區間insert進新數據。受限于這種實現方式，Innodb很多時候會鎖住不需要鎖的區間。如下所示：事務A 事務B 事務Cbegin; begin; begin; select id,class_name,teacher_id from class_teacher; id class_name teacher_id 1 初三一班 5 2 初三二班 30 update class_teacher set class_name='初一一班' where teacher_id=20; insert into class_teacher values (null,'初三五班',10); waiting ..... insert into class_teacher values (null,'初三五班',40); commit; 事務A commit之后，這條語句才插入成功 commit; commit; update的teacher_id=20是在(5，30]區間，即使沒有修改任何數據，Innodb也會在這個區間加gap鎖，而其它區間不會影響，事務C正常插入。如果使用的是沒有索引的字段，比如update class_teacher set teacher_id=7 where class_name='初三八班（即使沒有匹配到任何數據）',那么會給全表加入gap鎖。同時，它不能像上文中行鎖一樣經過MySQL Server過濾自動解除不滿足條件的鎖，因為沒有索引，則這些字段也就沒有排序，也就沒有區間。除非該事務提交，否則其它事務無法插入任何數據。行鎖防止別的事務修改或刪除，GAP鎖防止別的事務新增，行鎖和GAP鎖結合形成的的Next-Key鎖共同解決了RR級別在寫數據時的幻讀問題。Serializable這個級別很簡單，讀加共享鎖，寫加排他鎖，讀寫互斥。使用的悲觀鎖的理論，實現簡單，數據更加安全，但是并發能力非常差。如果你的業務并發的特別少或者沒有并發，同時又要求數據及時可靠的話，可以使用這種模式。這里要吐槽一句，不要看到select就說不會加鎖了，在Serializable這個級別，還是會加鎖的！

實現MySQL數據庫的備份與還原的幾種方法

MyISAM 表是保存成文件的形式，因此相對比較容易備份，上面提到的幾種方法都可以使用。Innodb 所有的表都保存在同一個數據文件 ibdata1 中（也可能是多個文件，或者是獨立的表空間文件），相對來說比較不好備份，免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump。1. 使用直接拷貝數據庫備份 典型的如cp、tar或cpio實用程序當你使用直接備份方法時，必須保證表不在被使用。如果服務器在你正在拷貝一個表時改變它，拷貝就失去意義。保證你的拷貝完整性的最好方法是關閉服務器，拷貝文件，然后重啟服務器。 如果你不想關閉服務器，要在執行表檢查的同時鎖定服務器。如果服務器在運行，相同的制約也適用于拷貝文件，應該使用相同的鎖定協議讓服務器“安靜下來”。當你完成了MySQL數據庫備份時，需要重啟服務器（如果關閉了它）或釋放加在表上的鎖定（如果你讓服務器運行）。 要用直接拷貝文件把一個數據庫從一臺機器拷貝到另一臺機器上，只是將文件拷貝到另一臺服務器主機的適當數據目錄下即可。要確保文件是MyIASM格式或兩臺機器有相同的硬件結構，否則你的數據庫在另一臺主機上有奇怪的內容。你也應該保證在另一臺機器上的服務器在你正在安裝數據庫表時不訪問它們。2. 使用mysqldump數據庫備份mysqldump 是采用SQL級別的備份機制，它將數據表導成 SQL 腳本文件，在不同的 MySQL 版本之間升級時相對比較合適，這也是最常用的備份方法。mysqldump程序備份數據庫較慢，但它生成的文本文件便于移植。mysqldump 的一些主要參數：1）--compatible=name它告訴 mysqldump，導出的數據將和哪種數據庫或哪個舊版本的 MySQL 服務器相兼容。值可以為 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用幾個值，用逗號將它們隔開。當然了，它并不保證能完全兼容，而是盡量兼容。2）--complete-insert，-c導出的數據采用包含字段名的完整 INSERT 方式，也就是把所有的值都寫在一行。這么做能提高插入效率，但是可能會受到 max_allowed_packet 參數的影響而導致插入失敗。因此，需要謹慎使用該參數，至少我不推薦。3）--default-character-set=charset指定導出數據時采用何種字符集，如果數據表不是采用默認的 latin1 字符集的話，那么導出時必須指定該選項，否則再次導入數據后將產生亂碼問題。4）--disable-keys告訴mysqldump 在 INSERT 語句的開頭和結尾增加 ; 和 ; 語句，這能大大提高插入語句的速度，因為它是在插入完所有數據后才重建索引的。該選項只適合 MyISAM 表。5）--extended-insert = true|false默認情況下，mysqldump 開啟 --complete-insert 模式，因此不想用它的的話，就使用本選項，設定它的值為 false 即可。6）--hex-blob使用十六進制格式導出二進制字符串字段。如果有二進制數據就必須使用本選項。影響到的字段類型有 BINARY、VARBINARY、BLOB。7）--lock-all-tables，-x在開始導出之前，提交請求鎖定所有數據庫中的所有表，以保證數據的一致性。這是一個全局讀鎖，并且自動關閉 --single-transaction 和 --lock-tables 選項。8）--lock-tables它和--lock-all-tables 類似，不過是鎖定當前導出的數據表，而不是一下子鎖定全部庫下的表。本選項只適用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 選項。9）--no-create-info，-t只導出數據，而不添加 CREATE TABLE 語句。10）--no-data，-d不導出任何數據，只導出數據庫表結構。11）--opt這只是一個快捷選項，等同于同時添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 選項。本選項能讓 mysqldump 很快的導出數據，并且導出的數據能很快導回。該選項默認開啟，但可以用 --skip-opt 禁用。注意，如果運行 mysqldump 沒有指定 --quick 或 --opt 選項，則會將整個結果集放在內存中。如果導出大數據庫的話可能會出現問題。12）--quick，-q該選項在導出大表時很有用，它強制 mysqldump 從服務器查詢取得記錄直接輸出而不是取得所有記錄后將它們緩存到內存中。13）--routines，-R導出存儲過程以及自定義函數。14）--single-transaction該選項在導出數據之前提交一個 BEGIN SQL語句，BEGIN 不會阻塞任何應用程序且能保證導出時數據庫的一致性狀態。它只適用于事務表，例如 InnoDB 和 BDB。 本選項和 --lock-tables 選項是互斥的，因為 LOCK TABLES 會使任何掛起的事務隱含提交。 要想導出大表的話，應結合使用 --quick 選項。--triggers同時導出觸發器。該選項默認啟用，用 --skip-triggers 禁用它。備份：使用mysqldump備份數據庫其實就是把數據庫轉儲成一系列CREATE TABLE和INSERT語句，通過這些語句我們就可重新生成數據庫。使用mysqldump的方法如下：% mysqldump --opt testdb | gzip /data/backup/testdb.bak#--opt選項會對轉儲過程進行優化，生成的備份文件會小一點，后的管道操作會進行數據壓縮% mysqldump --opt testdb mytable1,mytable2 | gzip /data/backup/testdb_mytable.bak#可在數據庫后接數據表名，只導出指定的數據表，多個數據表可用逗號分隔--opt選項還可激活--add-drop-table選項，它將會在備份文件的每條CREATE TABLE前加上一條DROP TABLE IF EXISTS語句。這可方便進行數據表的更新，而不會發生“數據表已存在”的錯誤。用mysqldump命令還可直接把數據庫轉移到另外一臺服務器上，不用生成備份文件。重復執行可定期更新遠程數據庫。% mysqladmin -h remote_host create testdb% mysqldump --opt testdb | mysql -h remote_host testdb另外還可通過ssh遠程調用服務器上的程序，如：% ssh remote_host mysqladmin create testdb% mysqldump --opt testdb | ssh remote_host mysql testdb 通過直接拷貝系統文件的方式備份數據庫，在備份時，要確保沒有人對數據庫進行修改操作。要做到這點，最好關閉服務器。如果不能關閉的，要以只讀方試鎖定有關數據表。下面是一些示例：% cp -r db /backup/db #備份db數據庫到/backup/db目錄% cp table_name.* /backup/db #只備份table_name數據表% scp -r db remotehot:/usr/local/mysql/data #用scp把數據庫直接拷貝到遠程服務器，在把數據庫直接拷貝到遠程主機時，應注意兩臺機器必須有同樣的硬件結構，或者將拷貝的數據表全部是可移植數據表類型。或者/usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=utf8 --opt --extended-insert=false \--triggers -R --hex-blob -x testdb testdb.sql使用以下 SQL 來備份 Innodb 表：/usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=utf8 --opt --extended-insert=false \--triggers -R --hex-blob --single-transaction testdb testdb.sql另外，如果想要實現在線備份，還可以使用 --master-data 參數來實現，如下：/usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=utf8 --opt --master-data=1 \--single-transaction --flush-logs testdb testdb.sql它只是在一開始的瞬間請求鎖表，然后就刷新binlog了，而后在導出的文件中加入CHANGE MASTER 語句來指定當前備份的binlog位置，如果要把這個文件恢復到slave里去，就可以采用這種方法來做。 還原：用mysqldump 備份出來的文件是一個可以直接倒入的 SQL 腳本，有兩種方法可以將數據導入。直接用 mysql 客戶端例如：/usr/local/mysql/bin/mysql -uroot -proot testdb testdb.sql用SOURCE 語法其實這不是標準的 SQL 語法，而是 mysql 客戶端提供的功能，例如：SOURCE /tmp/testdb.sql;這里需要指定文件的絕對路徑，并且必須是 mysqld 運行用戶(例如 nobody)有權限讀取的文件。 3. 使用mysqlhotcopy數據庫備份 使用mysqlhotcopy工具，它是一個Perl DBI腳本，可在不關閉服務器的情況下備份數據庫，mysqlhotcopy 是一個 PERL 程序，最初由Tim Bunce編寫。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 來快速備份數據庫。它是備份數據庫或單個表的最快的途徑，但它只能運行在數據庫文件（包括數據表定義文件、數據文件、索引文件）所在的機器上。mysqlhotcopy 只能用于備份 MyISAM，并且只能運行在 類Unix 和 NetWare 系統上。它主要的優點是：它直接拷貝文件，所以它比mysqldump快。可自動完成數據鎖定工作，備份時不用關閉服務器。能刷新日志，使備份文件和日志文件的檢查點能保持同步。備份：mysqlhotcopy 支持一次性拷貝多個數據庫，同時還支持正則表達。以下是幾個例子：/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb /tmp (把數據庫目錄 testdb 拷貝到 /tmp 下)/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb_1 testdb_2 testdb_n /tmp/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb./regex/ /tmp 還原：mysqlhotcopy 備份出來的是整個數據庫目錄，使用時可以直接拷貝到 mysqld 指定的 datadir (在這里是 /usr/local/mysql/data/)目錄下即可，同時要注意權限的問題，如下例：cp -rf testdb /usr/local/mysql/data/chown -R nobody:nobody /usr/local/mysql/data/ (將 testdb 目錄的屬主改成 mysqld 運行用戶) 4. 使用SQL語句數據庫備份 BACKUP TABLE 語法其實和 mysqlhotcopy 的工作原理差不多，都是鎖表，然后拷貝數據文件。它能實現在線備份，但是效果不理想，因此不推薦使用。它只拷貝表結構文件和數據文件，不同時拷貝索引文件，因此恢復時比較慢。備份：BACK TABLE tbl_test TO '/tmp/testdb/'; #把tbl_test數據庫備份到/tmp/testdb/目錄里，會自動創建一個testdb目錄 為了執行該語句，你必須擁有那些表的FILE權限和SELECT權限，備份目錄還必須是服務器可寫的。該語句執行時，會先把內存中的數據寫入磁盤，再把各個數據表的.frm(表結構定義文件)、.MYD(數據)文件從數據目錄拷貝到備份目錄。它不拷貝.MYI(索引)文件，因為它能用另外兩個文件重建。BACKUP TABLE語句備份時，依次鎖定數據表，當同時備份多個數據表時，數據表可能會被修改，所以備份0完成時，備份文件中的數據和現時數據表中的數據可能會有差異，為了消除該差異，我們可用只讀方式鎖定數據表，在備份完成后再解鎖。如：mysql LOCK TABLES tb1 READ,tb2 READ;mysql BACKUP TABLE tb1,tb2 TO 'backup/db';mysql UNLOCK TABLES;使用BACKUP TABLE語句備份的數據表可用RESTORE TABLE重新加載到服務器。注意，必須要有 FILE 權限才能執行本SQL，并且目錄 /tmp/testdb/ 必須能被 mysqld 用戶可寫，導出的文件不能覆蓋已經存在的文件，以避免安全問題。SELECT INTO OUTFILE 則是把數據導出來成為普通的文本文件，可以自定義字段間隔的方式，方便處理這些數據。 例子：SELECT * INTO OUTFILE '/tmp/testdb/tbl_test.txt' FROM tbl_test;注意，必須要有 FILE 權限才能執行本SQL，并且文件 /tmp/testdb/tbl_test.txt 必須能被 mysqld 用戶可寫，導出的文件不能覆蓋已經存在的文件，以避免安全問題。還原：用BACKUP TABLE 方法備份出來的文件，可以運行 RESTORE TABLE 語句來恢復數據表。例子：RESTORE TABLE FROM '/tmp/testdb/';權限要求類似上面所述。用SELECT INTO OUTFILE 方法備份出來的文件，可以運行 LOAD DATA INFILE 語句來恢復數據表。例子：LOAD DATA INFILE '/tmp/testdb/tbl_name.txt' INTO TABLE tbl_name;權限要求類似上面所述。倒入數據之前，數據表要已經存在才行。

文章標題：包含postgresql解鎖的詞條
文章轉載：http://vcdvsql.cn/article8/dsdggip.html

成都網站建設公司_創新互聯，為您提供網站建設、ChatGPT、微信公眾號、網站制作、動態網站、網站內鏈

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯