故障分析|MySQL 5.7 mysqldumpを使用する重要なピット回避事項
著者:王向
愛可生DBAチームメンバーは、会社DMP製品の運営と顧客MySQL問題の処理を担当しています.データベースの故障処理が得意です.データベース技術とpythonに深い興味を持っています.
オリジナル投稿
*爱可生开源社区出品,原创内容无许可使用,转载请联系小编,并注明出所.
背景
筆者は、お客様のMySQLの問題を一度に処理する際に、お客様のMySQLのsysライブラリが使用できなくなり、エラーを投げ出しました.
まず、この問題は実は解決しにくいわけではありませんが、この問題が引き起こした現象は面白いです.
よくある質問のチェック
まず、いくつかの一般的な問題を特定します.権限が足りない. sysライブラリfunctionsとproceduresが失われました. mysqldump全備後にバージョンをまたいで回復【問題2の現象が発生する】; mysqlアップグレードmysqlを実行していません_upgrade【問題2が発生する現象】
まず、権限の問題に権限があるかどうかを確認します.
明らかにそうではありません.sysライブラリに関連するfunctionsとproceduresが失われたかどうかを確認します.
Sysライブラリfunctionsとproceduresが失われました.それは、問題
疑問を持って長い調査過程を開始する.お客様に対する根掘り葉掘りの問題を経て、上記の状況は発生していないことがわかりました.ユーザーのバックアップ習慣はすべて完全に準備されています(-A)、しかもすべてバックアップが回復した後にsysライブラリ
まずバックアップを元に戻してみましょう
筆者は、お客様がバージョンを超えたことによるものだと強く考え、お客様に証拠を与えています.同じバージョンのMySQLがmysqldumpを使用して完全にリカバリされたことを確認した後、sysライブラリ
バックアップ前にsysライブラリを1波検出し、完全にOKであることを確認してからオープンします.
私たちがよく使うコマンドを使用して、すべてのライブラリをバックアップします.
バックアップが完了したら、データのリカバリを開始します.
リカバリが完了したらsysライブラリを検出します.
パチパチ顔をして、意外にも同じバージョンも現れますか?それはいったい何の問題ですか.
他のバージョンを見てみましょう
MySQL 5.7.13、5.7.21、5.7.25、5.7.28、5.7.31などのいくつかのバージョンのテストを経て、銃を横にしました.奇妙な現象は、彼らの唯一の共通性は、バックアップがどのように復元されても
突破点を探す
共通の法則はバックアップSQLファイルのDROPは 後CREATEは新しい
これは、sys schema routinesのない新しい
真相が明らかになる
公式ドキュメント【sys-schema-usage】
https://dev.mysql.com/doc/ref...
ページにはこのような言葉があります(ここでは公式原文を直接引用します):
However, those statements display the definitions in relatively unformatted form. To view object definitions with more readable formatting, access the individual
Neither mysqldump nor mysqlpump dump the sys schema by default. To generate a dump file, name the sys schema explicitly on the command line using either of these commands:
To reinstall the schema from the dump file, use this command:
公式ドキュメントはsysライブラリをバックアップしないことを明確に示しています.しかしmysqldumpを使用すると
BUG接続: https://bugs.mysql.com/bug.ph... https://bugs.mysql.com/bug.ph... https://bugs.mysql.com/bug.ph... https://github.com/mysql/mysq...
ソリューションとシーンの使用
このバグに対して4つのソリューションをまとめて参考にして、実際の環境シーンに合わせて選択して使用します.
1、mysql_upgrade install or upgrade sys schema
このシナリオはsysライブラリがmysqldumpインポートによって破損した場合に適用されます.
注意:mysql_upgradeはsysライブラリを修理すると同時に、mysqlライブラリとユーザーライブラリテーブル(期間中ロックされ、速度が一般的)を修理し、誤傷する可能性が極めて低い.mysql_の使用upgradeの場合は追加します
2、sysライブラリをフルバックアップする
このスキームは復元が必要なデータベースに適用され、sysライブラリも正常ではない場合に使用されます.完全なバックアップ後にsysライブラリを追加バックアップして修復します.
注意:マスタースレーブとして使用する場合は適用されません.
3、databasesを使用して完全に準備する
このスキームは、すべてのシーンの完全な準備要件に適用され、100%安全です.
4、mysql-sysオープンソースコードを使用する
もしあなたのデータベースsysがすべて中招されたら、また生産ライブラリです.あなたはこの方法しか使えません.
【mysql-sys】
https://github.com/mysql/mysq...
にsysライブラリの作成文を記録してファイルをローカルにダウンロードし、データベースのバージョンに基づいて次のコマンドを実行します.
【追加1】MySQL 8を試してみる
テストMySQL 8.0.0からMySQL 8.0.2までの全シリーズは影響を受けません.具体的には、MySQL 8.0.0からmysql.procという表を削除します.詳細については、公式文書を参照してください. data-dictionary-usage-differences news-8-0-0
Previously, tables in the mysql system database were visible to DML and DDL statements. As of MySQL 8.0, data dictionary tables are invisible and cannot be modified or queried directly. However, in most cases there are corresponding INFORMATION_SCHEMA tables that can be queried instead. This enables the underlying data dictionary tables to be changed as server development proceeds, while maintaining a stable INFORMATION_SCHEMA interface for application use.
【追加2】質問があれば?
ついでに
dumpすべてのライブラリテーブル(--all-databases)のソースコード:
functionsとproceduresのソースコードをバックアップします.
愛可生DBAチームメンバーは、会社DMP製品の運営と顧客MySQL問題の処理を担当しています.データベースの故障処理が得意です.データベース技術とpythonに深い興味を持っています.
オリジナル投稿
*爱可生开源社区出品,原创内容无许可使用,转载请联系小编,并注明出所.
背景
筆者は、お客様のMySQLの問題を一度に処理する際に、お客様のMySQLのsysライブラリが使用できなくなり、エラーを投げ出しました.
mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
まず、この問題は実は解決しにくいわけではありませんが、この問題が引き起こした現象は面白いです.
よくある質問のチェック
まず、いくつかの一般的な問題を特定します.
まず、権限の問題に権限があるかどうかを確認します.
mysql> SHOW GRANTS FOR root@'localhost';
+---------------------------------------------------------------------+
| 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)
明らかにそうではありません.sysライブラリに関連するfunctionsとproceduresが失われたかどうかを確認します.
mysql> SELECT * FROM mysql.proc;
Empty set (0.00 sec)
mysql> SHOW PROCEDURE STATUS WHERE Db = 'sys';
Empty set (0.00 sec)
mysql> SHOW FUNCTION STATUS WHERE Db = 'sys';
Empty set (0.00 sec)
Sysライブラリfunctionsとproceduresが失われました.それは、問題
3が問題4であり、mysqldumpに振られ、mysql_upgradeの完全な準備とアップグレードが実行されていません.疑問を持って長い調査過程を開始する.お客様に対する根掘り葉掘りの問題を経て、上記の状況は発生していないことがわかりました.ユーザーのバックアップ習慣はすべて完全に準備されています(-A)、しかもすべてバックアップが回復した後にsysライブラリ
ERROR 1356が現れて、ユーザーMySQL環境の主要ないくつかの大きいバージョンがMySQL 5.7.13、5.7.25、5.7.28を分布することを検査します.そこで、mysqldumpのバックアップに問題を特定しました.まずバックアップを元に戻してみましょう
筆者は、お客様がバージョンを超えたことによるものだと強く考え、お客様に証拠を与えています.同じバージョンのMySQLがmysqldumpを使用して完全にリカバリされたことを確認した後、sysライブラリ
ERROR 1356が表示されるかどうかを確認します.バックアップ前にsysライブラリを1波検出し、完全にOKであることを確認してからオープンします.
mysql> SELECT * FROM sys.version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.2 | 5.7.31-log |
+-------------+---------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> SELECT COUNT(*) FROM mysql.proc;
+----------+
| COUNT(*) |
+----------+
| 48 |
+----------+
1 row in set (0.00 sec)
私たちがよく使うコマンドを使用して、すべてのライブラリをバックアップします.
mysqldump --all-databases --set-gtid-purged=OFF \
--master-data=2 --single-transaction --routines \
--events --triggers --max_allowed_packet=256M > all.sql
バックアップが完了したら、データのリカバリを開始します.
mysql -uroot -S /tmp/mysql.sock < all.sql
リカバリが完了したらsysライブラリを検出します.
mysql> SELECT * FROM sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> SELECT COUNT(*) FROM mysql.proc;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW PROCEDURE STATUS WHERE Db = 'sys';
Empty set (0.00 sec)
mysql> SHOW FUNCTION STATUS WHERE Db = 'sys';
Empty set (0.00 sec)
パチパチ顔をして、意外にも同じバージョンも現れますか?それはいったい何の問題ですか.
他のバージョンを見てみましょう
MySQL 5.7.13、5.7.21、5.7.25、5.7.28、5.7.31などのいくつかのバージョンのテストを経て、銃を横にしました.奇妙な現象は、彼らの唯一の共通性は、バックアップがどのように復元されても
--all-databases(-A)を使用すればERROR 1356に報告することです.思わず考え込んだ.突破点を探す
共通の法則は
--all-databases(-A)会ERROR 1356しか使わない以上、彼がいったい何をバックアップしたのか見てみましょう.そこで同僚を呼んで一緒にlessを見て、上下に目をそらした.突然発見:mysql.procを落としました.mysql.procを作った.LOCK TABLESとUNLOCK TABLESの間にCREATE ROUTINEのデータがバックアップされていないのですか?これは、sys schema routinesのない新しい
mysql.procテーブルをインポートするたびに私に与えることに相当します.それは異常な気まずいことではないでしょうか.--
-- Table structure for table `proc`
--
DROP TABLE IF EXISTS `proc`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `proc` (
`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char(64) NOT NULL DEFAULT '',
`type` enum('FUNCTION','PROCEDURE') NOT NULL,
`specific_name` char(64) NOT NULL DEFAULT '',
`language` enum('SQL') NOT NULL DEFAULT 'SQL',
`sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
`is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
`security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
`param_list` blob NOT NULL,
`returns` longblob NOT NULL,
`body` longblob NOT NULL,
`definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`body_utf8` longblob,
PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `proc`
--
LOCK TABLES `proc` WRITE;
/*!40000 ALTER TABLE `proc` DISABLE KEYS */;
/*!40000 ALTER TABLE `proc` ENABLE KEYS */;
UNLOCK TABLES;
真相が明らかになる
公式ドキュメント【sys-schema-usage】
https://dev.mysql.com/doc/ref...
ページにはこのような言葉があります(ここでは公式原文を直接引用します):
However, those statements display the definitions in relatively unformatted form. To view object definitions with more readable formatting, access the individual
.sql files found under the scripts/sys_schema in MySQL source distributions. Prior to MySQL 5.7.28, the sources are maintained in a separate distribution available from the sys schema development website at https://github.com/mysql/mysq... Neither mysqldump nor mysqlpump dump the sys schema by default. To generate a dump file, name the sys schema explicitly on the command line using either of these commands:
mysqldump --databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql
To reinstall the schema from the dump file, use this command:
mysql < sys_dump.sql
公式ドキュメントはsysライブラリをバックアップしないことを明確に示しています.しかしmysqldumpを使用すると
--all-databasesを実行するとmysql.procが空になりsysが正常に使用できなくなります.これはBUGで、MySQL 5.7.xにしか存在しません!BUG接続:
ソリューションとシーンの使用
このバグに対して4つのソリューションをまとめて参考にして、実際の環境シーンに合わせて選択して使用します.
1、mysql_upgrade install or upgrade sys schema
このシナリオはsysライブラリがmysqldumpインポートによって破損した場合に適用されます.
# sys schema （An error occurs if a sys schema exists but has no version view）
mysql> DROP DATABASE sys;
# sys schema
mysql> SHOW DATABASES;
# ， mysql_upgrade sys schema
mysql_upgrade --upgrade-system-tables --skip-verbose --force
mysql> SHOW DATABASES;
mysql> SELECT COUNT(*) FROM mysql.proc;
注意:mysql_upgradeはsysライブラリを修理すると同時に、mysqlライブラリとユーザーライブラリテーブル(期間中ロックされ、速度が一般的)を修理し、誤傷する可能性が極めて低い.mysql_の使用upgradeの場合は追加します
--upgrade-system-tablesです.そうしないと、ユーザー・ライブラリ・テーブルがスキャンされます.2、sysライブラリをフルバックアップする
このスキームは復元が必要なデータベースに適用され、sysライブラリも正常ではない場合に使用されます.完全なバックアップ後にsysライブラリを追加バックアップして修復します.
mysqldump -A --set-gtid-purged=OFF --master-data=2 --single-transaction --routines --events --triggers > all.sql
mysqldump --databases --routines sys > sys_dump_`mysql -V|awk '{print $5}'|cut -b 1-6`.sql
注意:マスタースレーブとして使用する場合は適用されません.
3、databasesを使用して完全に準備する
このスキームは、すべてのシーンの完全な準備要件に適用され、100%安全です.
select_databases="
SELECT
GROUP_CONCAT(schema_name SEPARATOR ' ')
FROM
information_schema.schemata
WHERE
schema_name NOT IN ('performance_schema','information_schema');"
databases=`mysql -NBe "$select_databases"`
mysqldump --set-gtid-purged=OFF --master-data=2 \
--single-transaction --routines --events --triggers \
--max_allowed_packet=256M --databases > all.sql
4、mysql-sysオープンソースコードを使用する
もしあなたのデータベースsysがすべて中招されたら、また生産ライブラリです.あなたはこの方法しか使えません.
【mysql-sys】
https://github.com/mysql/mysq...
にsysライブラリの作成文を記録してファイルをローカルにダウンロードし、データベースのバージョンに基づいて次のコマンドを実行します.
# ， sql_log_bin， 。
mysql> source before_setup.sql
# sys ， sql
# 、 、 、
mysql> source sys_57.sql
# ， sql_log_bin
mysql> source after_setup.sql
【追加1】MySQL 8を試してみる
テストMySQL 8.0.0からMySQL 8.0.2までの全シリーズは影響を受けません.具体的には、MySQL 8.0.0からmysql.procという表を削除します.詳細については、公式文書を参照してください.
Previously, tables in the mysql system database were visible to DML and DDL statements. As of MySQL 8.0, data dictionary tables are invisible and cannot be modified or queried directly. However, in most cases there are corresponding INFORMATION_SCHEMA tables that can be queried instead. This enables the underlying data dictionary tables to be changed as server development proceeds, while maintaining a stable INFORMATION_SCHEMA interface for application use.
【追加2】質問があれば?
ついでに
mysqldumpのソースコードを見てみましょう(このソースコードのデザインも面白いので、後ろの文章に入れるつもりです)、まずこの変数を見てみましょう./**
First mysql version supporting the information schema.
*/
#define FIRST_INFORMATION_SCHEMA_VERSION 50003
/**
Name of the information schema database.
*/
#define INFORMATION_SCHEMA_DB_NAME "information_schema"
/**
First mysql version supporting the performance schema.
*/
#define FIRST_PERFORMANCE_SCHEMA_VERSION 50503
/**
Name of the performance schema database.
*/
#define PERFORMANCE_SCHEMA_DB_NAME "performance_schema"
/**
First mysql version supporting the sys schema.
*/
#define FIRST_SYS_SCHEMA_VERSION 50707 /* sys schema MySQL 5.7.7 */
/**
Name of the sys schema database.
*/
#define SYS_SCHEMA_DB_NAME "sys"
dumpすべてのライブラリテーブル(--all-databases)のソースコード:
.........
/* dump_all_databases */
if (opt_alldbs)
{
if (!opt_alltspcs && !opt_notspcs)
dump_all_tablespaces();
dump_all_databases();
}
.........
/* dump_all_databases */
static int dump_all_databases()
{
MYSQL_ROW row;
MYSQL_RES *tableres;
int result=0
/* ：SHOW DATABASES */
if (mysql_query_with_error_report(mysql, &tableres, "SHOW DATABASES"))
return 1;
while ((row= mysql_fetch_row(tableres)))
{
/* information_schema */
if (mysql_get_server_version(mysql) >= FIRST_INFORMATION_SCHEMA_VERSION &&
!my_strcasecmp(&my_charset_latin1, row[0], INFORMATION_SCHEMA_DB_NAME))
continue;
/* performance_schema */
if (mysql_get_server_version(mysql) >= FIRST_PERFORMANCE_SCHEMA_VERSION &&
!my_strcasecmp(&my_charset_latin1, row[0], PERFORMANCE_SCHEMA_DB_NAME))
continue;
/* sys */
/* MySQL >= SYS_SCHEMA 。 && row[0] SYS_SCHEMA_DB_NAME ， */
if (mysql_get_server_version(mysql) >= FIRST_SYS_SCHEMA_VERSION &&
!my_strcasecmp(&my_charset_latin1, row[0], SYS_SCHEMA_DB_NAME))
continue;
if (is_ndbinfo(mysql, row[0]))
continue;
/* dump */
/* dump dump_all_tables_in_db */
if (dump_all_tables_in_db(row[0]))
result=1;
}
.........
functionsとproceduresのソースコードをバックアップします.
/** --all-databases sys dump_routines_for_db 。
sys ， .sql mysql.proc CREATE ROUTINE sys */
static uint dump_routines_for_db(char *db)
{
........
/* 0, retrieve and dump functions, 1, procedures */
for (i= 0; i <= 1; i++)
{
/* SHOW FUNCTION/PROCEDURE STATUS WHERE Db = xx， functions procedures */
my_snprintf(query_buff, sizeof(query_buff),
"SHOW %s STATUS WHERE Db = '%s'",
routine_type[i], db_name_buff);
if (mysql_query_with_error_report(mysql, &routine_list_res, query_buff))
DBUG_RETURN(1);
if (mysql_num_rows(routine_list_res))
{
while ((routine_list_row= mysql_fetch_row(routine_list_res)))
{
routine_name= quote_name(routine_list_row[1], name_buff, 0);
DBUG_PRINT("info", ("retrieving CREATE %s for %s", routine_type[i],
name_buff));
/* SHOW CREATE FUNCTION/PROCEDURE xxx， functions、procedures */
my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE %s %s",
routine_type[i], routine_name);
........