項目上 MySQL?還原 SQL 備份經(jīng)常會碰到一個錯誤如下，且通常出現(xiàn)在導(dǎo)入視圖、函數(shù)、存儲過程、事件等對象時，其根本原因就是因為導(dǎo)入時所用賬號并不具有SUPER 權(quán)限，所以無法創(chuàng)建其他賬號的所屬對象。ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation常見場景：1. 還原 RDS 時經(jīng)常出現(xiàn)，因為 RDS 不提供 SUPER 權(quán)限；2. 由開發(fā)庫還原到項目現(xiàn)場，賬號權(quán)限等有所不同。
10年積累的成都網(wǎng)站建設(shè)、網(wǎng)站設(shè)計經(jīng)驗，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識你，你也不認(rèn)識我。但先網(wǎng)站制作后付款的網(wǎng)站建設(shè)流程，更有貴溪免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
處理方式：
1. 在原庫中批量修改對象所有者為導(dǎo)入賬號或修改?SQL SECURITY?為?Invoker；2. 使用 mysqldump 導(dǎo)出備份，然后將 SQL 文件中的對象所有者替換為導(dǎo)入賬號。
二、問題原因我們先來看下為啥會出現(xiàn)這個報錯，那就得說下 MySQL 中一個很特別的權(quán)限控制機制，像視圖、函數(shù)、存儲過程、觸發(fā)器等這些數(shù)據(jù)對象會存在一個?DEFINER?和一個?SQL SECURITY?的屬性，如下所示：
--視圖定義CREATE?ALGORITHM?=?UNDEFINED?DEFINER?=?`root`@`%`?SQL?SECURITY?DEFINER?VIEW?v_test
--函數(shù)定義CREATE?DEFINER=`root`@`%`?FUNCTION?`f_test()`?RETURNS?varchar(100)?SQL?SECURITY?DEFINER
--存儲過程定義CREATE?DEFINER=`root`@`%`?PROCEDURE?`p_test`()?SQL?SECURITY?DEFINER
--觸發(fā)器定義CREATE DEFINER=`root`@`%` trigger t_test
--事件定義CREATE DEFINER=`root`@`%` EVENT `e_test`
DEFINER：對象定義者，在創(chuàng)建對象時可以手動指定用戶，不指定的話默認(rèn)為當(dāng)前連接用戶；
SQL SECURITY：指明以誰的權(quán)限來執(zhí)行該對象，有兩個選項，一個為?DEFINER，一個為?INVOKER，默認(rèn)情況下系統(tǒng)指定為 DEFINER；DEFINER：表示按定義者的權(quán)限來執(zhí)行；?INVOKER：表示按調(diào)用者的權(quán)限來執(zhí)行。
如果導(dǎo)入賬號具有 SUPER 權(quán)限，即使對象的所有者賬號不存在，也可以導(dǎo)入成功，但是在查詢對象時，如果對象的?SQL SECURITY?為?DEFINER，則會報賬號不存在的報錯。ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist
改寫好處：1. 可以避免還原時遇到 DEFINER 報錯相關(guān)問題；2. 根據(jù)輸出信息知道備份是否正常進(jìn)行，防止備份中遇到元數(shù)據(jù)鎖無法獲取然后一直卡住的情況。
使用這種方法前，我們需要先下載一個MySQL客戶端工具SqlYog。點擊這里下載并安裝\x0d\x0a\x0d\x0a下面我們開始復(fù)制數(shù)據(jù)庫：\x0d\x0a1、打開SqlYog community Edition，分別在不同的選項卡中打開源數(shù)據(jù)庫服務(wù)器與目標(biāo)數(shù)據(jù)庫服務(wù)器，這一點很重。\x0d\x0a\x0d\x0a在源數(shù)據(jù)庫服務(wù)器選項卡中你將看到所有數(shù)據(jù)庫列表。\x0d\x0a2、在需要復(fù)制遷移的數(shù)據(jù)庫上右擊，在彈出菜單中選擇“Copy Database to Different Host/Database”\x0d\x0a3、在彈出對話框中，我們能看到源數(shù)據(jù)庫服務(wù)器及目標(biāo)服務(wù)器，在左邊，通過勾選復(fù)選框來選擇需要復(fù)制遷移的對象，如表、函數(shù)、觸發(fā)器等，也可以選擇所有對象。\x0d\x0a4、在右邊選擇需要遷移的目標(biāo)服務(wù)器或數(shù)據(jù)庫\x0d\x0a5、根據(jù)你的需要選擇復(fù)制類型：“Structure and Data”或“Structure only”，即“結(jié)構(gòu)和數(shù)據(jù)”或“僅結(jié)構(gòu)”。\x0d\x0a6、選擇結(jié)束后點擊“Copy”按鈕開始復(fù)制，知道數(shù)據(jù)遷移結(jié)束。
這種架構(gòu)一般用在以下三類場景
1. 備份多臺 Server 的數(shù)據(jù)到一臺如果按照數(shù)據(jù)切分方向來講，那就是垂直切分。比如圖 2，業(yè)務(wù) A、B、C、D 是之前拆分好的業(yè)務(wù)，現(xiàn)在需要把這些拆分好的業(yè)務(wù)匯總起來備份，那這種需求也很適用于多源復(fù)制架構(gòu)。實現(xiàn)方法我大概描述下：業(yè)務(wù) A、B、C、D 分別位于 4 臺 Server，每臺 Server 分別有一個數(shù)據(jù)庫來隔離前端的業(yè)務(wù)數(shù)據(jù)，那這樣，在從庫就能把四臺業(yè)務(wù)的數(shù)據(jù)全部匯總起來，而不需要做額外的操作。那沒有多源復(fù)制之前，要實現(xiàn)這類需求，只能在匯總機器上搭建多個 MySQL 實例，那這樣勢必會涉及到跨庫關(guān)聯(lián)的問題，不但性能急劇下降，管理多個實例也沒有單臺來的容易。
2. 用來聚合前端多個 Server 的分片數(shù)據(jù)。
同樣，按照數(shù)據(jù)切分方向來講，屬于水平切分。比如圖 3，按照年份拆分好的數(shù)據(jù)，要做一個匯總數(shù)據(jù)展現(xiàn)，那這種架構(gòu)也非常合適。實現(xiàn)方法稍微復(fù)雜些：比如所有 Server 共享同一數(shù)據(jù)庫和表，一般為了開發(fā)極端透明，前端配置有分庫分表的中間件，比如愛可生的 DBLE。
3. 匯總并合并多個 Server 的數(shù)據(jù)
第三類和第一種場景類似。不一樣的是不僅僅是數(shù)據(jù)需要匯總到目標(biāo)端，還得合并這些數(shù)據(jù)，這就比第一種來的相對復(fù)雜些。比如圖 4，那這樣的需求，是不是也適合多源復(fù)制呢？答案是 YES。那具體怎么做呢？
如果從庫上表 t 數(shù)據(jù)與主庫不一致，導(dǎo)致復(fù)制錯誤，整個庫的數(shù)據(jù)量很大，重做從庫很慢，如何單獨恢復(fù)這張表的數(shù)據(jù)？通常認(rèn)為是不能修復(fù)單表數(shù)據(jù)的，因為涉及到各表狀態(tài)不一致的問題。下面就列舉備份單表恢復(fù)到從庫會面臨的問題以及解決辦法：
場景 1
如果復(fù)制報錯后，沒有使用跳過錯誤、復(fù)制過濾等方法修復(fù)主從復(fù)制。主庫數(shù)據(jù)一直在更新，從庫數(shù)據(jù)停滯在報錯狀態(tài)（假設(shè) GTID 為 aaaa:1-100）。
修復(fù)步驟：
在主庫上備份表 t （假設(shè)備份快照 GTID 為 aaaa:1-10000）；
恢復(fù)到從庫；
啟動復(fù)制。
這里的問題是復(fù)制起始位點是 aaaa:101，從庫上表 t 的數(shù)據(jù)狀態(tài)是領(lǐng)先其他表的。aaaa:101-10000 這些事務(wù)中只要有修改表 t 數(shù)據(jù)的事務(wù)，就會導(dǎo)致復(fù)制報錯 ，比如主鍵沖突、記錄不存在（而 aaaa:101 這個之前復(fù)制報錯的事務(wù)必定是修改表 t 的事務(wù)）
解決辦法：啟動復(fù)制時跳過 aaaa:101-10000 這些事務(wù)中修改表 t 的事務(wù)。
正確的修復(fù)步驟：
1. 在主庫上備份表 t （假設(shè)備份快照 GTID 為 aaaa:1-10000），恢復(fù)到從庫；
2. 設(shè)置復(fù)制過濾，過濾表 t：
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db_name.t');
3. 啟動復(fù)制，回放到 aaaa:10000 時停止復(fù)制（此時從庫上所有表的數(shù)據(jù)都在同一狀態(tài)，是一致的）;
START SLAVE UNTIL SQL_AFTER_GTIDS = 'aaaa:10000';
4. 刪除復(fù)制過濾，正常啟動復(fù)制。
注意事項：這里要用 mysqldump --single-transaction --master-data=2，記錄備份快照對應(yīng)的 GTID
場景 2
如果復(fù)制報錯后，使用跳過錯誤、復(fù)制過濾等辦法修復(fù)了主從復(fù)制。主、從庫數(shù)據(jù)一直在更新。
修復(fù)步驟：
在主庫上備份表 t （假設(shè)備份快照 GTID為 aaaa:1-10000）；
停止從庫復(fù)制，GTID為 aaaa:1-20000；
恢復(fù)表 t 到從庫；
啟動復(fù)制。
這里的問題是復(fù)制起始位點是 aaaa:20001，aaaa:10000-20000 這些事務(wù)將不會在從庫上回放，如果這里面有修改表 t 數(shù)據(jù)的事務(wù)，從庫上將丟失這部分?jǐn)?shù)據(jù)。
解決辦法：從備份開始到啟動復(fù)制，鎖定表 t，保證 aaaa:10000-20000 中沒有修改表 t 的事務(wù)。
正確修復(fù)步驟：
對表 t 加讀鎖；
在主庫上備份表 t；
停止從庫復(fù)制，恢復(fù)表 t；
啟動復(fù)制；
解鎖表 t。
如果是大表，這里可以用可傳輸表空間方式備份、恢復(fù)表，減少鎖表時間。
我google了一下大概有一下幾個方法，
1.復(fù)制表結(jié)構(gòu)及數(shù)據(jù)到新表
CREATE TABLE 新表 SELECT * FROM 舊表
2.只復(fù)制表結(jié)構(gòu)到新表
CREATE TABLE 新表 SELECT * FROM 舊表 WHERE 1=2
即:讓W(xué)HERE條件不成立.
3.復(fù)制舊表的數(shù)據(jù)到新表(假設(shè)兩個表結(jié)構(gòu)一樣)
INSERT INTO 新表 SELECT * FROM 舊表
4.復(fù)制舊表的數(shù)據(jù)到新表(假設(shè)兩個表結(jié)構(gòu)不一樣)
INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 舊表
有兩種辦法。
1、在B機器上裝mysql。
將A機器上的mysql/data下的你的數(shù)據(jù)庫目錄整個拷貝下來。
將B機器上的mysql服務(wù)停止。
找到B機器上的mysql/data目錄，將你拷貝的目錄粘貼進(jìn)去，然后啟動mysql服務(wù)就可以了。
2、使用SQL語句備份和恢復(fù)
你可以使用SELECT INTO OUTFILE語句備份數(shù)據(jù)，并用LOAD DATA INFILE語句恢復(fù)數(shù)據(jù)。這種方法只能導(dǎo)出數(shù)據(jù)的內(nèi)容，不包括表的結(jié)構(gòu)，如果表的結(jié)構(gòu)文件損壞，你必須要先恢復(fù)原來的表的結(jié)構(gòu)。
語法：
SELECT * INTO {OUTFILE | DUMPFILE} ’file_name’ FROM tbl_name
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE ’file_name.txt’ [REPLACE | IGNORE]
INTO TABLE tbl_name
SELECT ... INTO OUTFILE ’file_name’
在dos命令提示符下使用mysqldump命令進(jìn)行備份.
如下:
C:\Documents and Settings\Administratormysqldump yinshi c:\\backup.txt -uroot
-p12142022
重慶分公司
重慶分公司
