業(yè)務(wù)場景
最近的一個項目最開始由于資源問題，mysql 數(shù)據(jù)庫是部署在一臺云服務(wù)器上的，這兩天客戶提供了云數(shù)據(jù)庫，所以原來在部署在 ECS 服務(wù)器上的數(shù)據(jù)庫，需要遷移到云數(shù)據(jù)庫。在云數(shù)據(jù)庫上的優(yōu)勢很多，它自動是分配了一主二從，自動備份等。所以這兩天的任務(wù)就是要將原來的數(shù)據(jù)庫遷移。 mysql 版本 mysql5.7.17
遷移步驟
遷移數(shù)據(jù)庫是一項需要很謹(jǐn)慎的任務(wù)。整個遷移過程大概分成以下幾步：備份原數(shù)據(jù)庫數(shù)據(jù)
//備份數(shù)據(jù)庫，并指定日期
mysqldump -uadmin -p****** databaseName | gzip > /databak/databaseName_$(date +%Y%m%d).sql.gz云數(shù)據(jù)庫上初始化數(shù)據(jù)庫、編碼、用戶名、數(shù)據(jù)庫等基礎(chǔ)信息 先通過騰訊云平臺創(chuàng)建用戶，以及相關(guān)權(quán)限
//連接數(shù)據(jù)庫
mysql -h172.16.0.1 -uUserName -p******
//創(chuàng)建數(shù)據(jù)庫，并指定編碼
CREATE DATABASE databaseName DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;執(zhí)行還原操作
//解壓備份好的.sql文件
gunzip -v /databak/databaseName_20200517.sql.gz
//還原數(shù)據(jù)庫
source /databak/databaseName_20200517.sql
產(chǎn)生的問題
正常情況下，按照以上遷移數(shù)據(jù)的步驟，應(yīng)該等還原操作完成即可，但是事情往往不會那么順利，如果很順利可能我們對數(shù)據(jù)庫遷移的認(rèn)知就到這里就可以了。
實際上在執(zhí)行還原操作時出現(xiàn)了錯誤。 主要出現(xiàn)兩次問題沒有主鍵
ERROR 1173 (42000): This table type requires a primary key表的存儲引擎不對
Can not create tables in myisam storage engine in user databases, controled by reject_create_table_myisam variable.
看到這兩個問題，感覺比較奇怪，因為最開始在測試數(shù)據(jù)庫自動備份時，已經(jīng)對備份的sql文件還原過，沒有發(fā)現(xiàn)有什么錯誤。為什么這一次遷移就出現(xiàn)這兩個問題呢？
排查方法
建表缺少主鍵
針對問題1，看日志比較容易明白意思，就是表需要主鍵，這個時候就想先看看數(shù)據(jù)庫中有哪些沒有主鍵的表，看看能否直接指定。
查詢沒有主鍵的表的sql如下：
SELECT table_schema, table_name,TABLE_ROWS
FROM information_schema.tables
WHERE (table_schema, table_name) NOT IN (
SELECT DISTINCT table_schema, table_name
FROM information_schema.columns
WHERE COLUMN_KEY = 'PRI'
)
AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');
查詢結(jié)果分析發(fā)現(xiàn)這些表和錯誤日志中的表匹配。然后查看了兩張表發(fā)現(xiàn)的確都是沒有指定主鍵的。正常 mysql 的設(shè)計中，如果在創(chuàng)建表時沒有顯式地定義主鍵,則 InnoDB 存儲引擎會按如下方式選擇或創(chuàng)建主鍵:首先判斷表中是否有非空的唯一索引,如果有,則該列即為主鍵。
如果不符合上述條件,InnoDB存儲引擎自動創(chuàng)建一個6字節(jié)大小的指針。
但是還是報那個錯。這時在想是否和數(shù)據(jù)庫的版本有關(guān)系。經(jīng)過 google 搜索大部分的結(jié)果都是定位到數(shù)據(jù)庫的參數(shù)設(shè)置了表一定要指定主鍵。
解決辦法如下：
//查詢變量查看是否開啟了強制主鍵，也就是建表必須有主鍵約束，
show global variables like 'innodb_force_primary_key';
//如果是ON則設(shè)置成OFF即可
set global innodb_force_primary_key=off;
當(dāng)然我也找找這個方法去嘗試了，但我執(zhí)行第一句時，發(fā)現(xiàn)沒有找到結(jié)果。然后也經(jīng)過了解這個參數(shù)是mysql8.0以及MariaDB中才有這個參數(shù)。強制執(zhí)行
set global innodb_force_primary_key=off;
出現(xiàn)如下錯誤：
ERROR 1193 (HY000): Unknown system variable 'innodb_force_primary_key'
所以這種方法行不通。但通過這個解決方法，我猜測問題可能就是和變量設(shè)置有關(guān)系，于是我查看了所有的 mysql 全局變量，最后找到了問題所在。
問題定位：原來騰訊云上的分布式數(shù)據(jù)庫tdsql中，設(shè)置建表需要主鍵的參數(shù)為 reject_table_no_pk 這個時候就能定位到問題所在了。
表的存儲引擎不對
通過如下sql可以查詢一個庫中所有使用MyISAM存儲引擎創(chuàng)建的表
SELECT * FROM information_schema.tables where engine='MyISAM' and TABLE_SCHEMA='databaseName'
查出來的表和還原錯誤日志報錯的表也匹配了。
通過分析問題1時，在查找全局變量時存在如下變量。
reject_create_table_myisam 意思就是拒絕使用 myisam 存儲引擎建表。所以問題2也定位到了問題源頭。
解決方案
通過上面一步一步分析問題，已經(jīng)找到了問題的源頭，找到了問題的產(chǎn)生原因。對于問題的解決就比較好處理了。
方法1
登錄超級管理員，對這兩個參數(shù)進(jìn)行設(shè)置
set global reject_table_no_pk 0;
set global reject_create_table_myisam OFF;
設(shè)置完成之后，重新執(zhí)行還原操作，發(fā)現(xiàn)問題解決。但是分布式數(shù)據(jù)庫新增了的這兩個參數(shù)其實是有他的用處的，這種默認(rèn)值最好不要輕易調(diào)整，因為云數(shù)據(jù)庫還有一個優(yōu)勢就是大部分的參數(shù)都調(diào)成了最佳。
通過資料搜索發(fā)現(xiàn)原來這兩個參數(shù)是有重要作用的。 TDSQL 內(nèi)核使用 row 格式的 binlog 復(fù)制。根據(jù)目前 MariaDB/MySQL 的實現(xiàn)方式，如果一個 update/delete 語句更新或者刪除了很多行，那么到了備機上面，更新或者刪除每個行時候，需要使用索引掃描或者全表掃描來找到這個行，導(dǎo)致備機復(fù)制變得非常慢，這是非常嚴(yán)重的問題。 在 TDSQL 的告警平臺上面就有用戶出現(xiàn)過主備延遲因此變得非常大的告警。為了避免這些致命問題的出現(xiàn)，所以才有“自動增加主鍵”和“禁止 create table/alter table 語句產(chǎn)生無主鍵的表”
方法2
方法1能夠解決還原問題，也能夠解決一般數(shù)據(jù)量不大的應(yīng)用。但是如果后面業(yè)務(wù)增長，可能還是需要將參數(shù)調(diào)整回來。此時方法1的解決方案就行不通了。
方法2實際就是針對沒有主鍵的表設(shè)置主鍵，沒有主鍵的表新增主鍵。以符合分布式數(shù)據(jù)庫要求。
而對于數(shù)據(jù)庫存儲引擎為myisam的表通過sql語句直接調(diào)整。
//修改表的存儲引擎
alter table table_name engine=innodb;
總結(jié)
數(shù)據(jù)庫內(nèi)容很多，很深，我們在處理工作中實際問題時，需要多多思考。從解決實際問題的過程中去深入知識點，擴(kuò)展知識點。這樣才能提高。
聯(lián)系客服
