MySQL アカウント情報をエレガントにバックアップする方法

序文：

最近、インスタンスの移行の問題に遭遇しました。データの移行後、データベースのユーザーと権限も移行する必要がありました。論理バックアップを実行するときは通常、バックアップにデータベースユーザーに関連する情報が含まれないように、MySQL システムライブラリを除外します。現時点では、ユーザー関連情報を移行する場合は、次の 3 つのソリューションを使用できます。同様に、データベースアカウント関連情報をバックアップする場合も、次の 3 つのソリューションを使用できます。 (このソリューションは MySQL 5.7 用であり、他のバージョンでは若干異なります)

1.mysqldumpはユーザー関連情報を論理的にエクスポートします

データベースユーザーのパスワードと権限関連の情報は、システムライブラリ mysql に保存されていることがわかっています。 mysqldump を使用して関連するテーブルデータをエクスポートします。ユーザーを移行する必要がある場合は、必要に応じてこのデータを別のインスタンスに挿入できます。これを実証してみましょう:

#mysql データベースの user、db、tables_priv テーブルのみをエクスポートします。#列の権限がある場合は、columns_priv テーブル データをエクスポートできます。#データベースで GTID が有効になっている場合は、エクスポート時に --set-gtid-purged=OFF を追加するのが最適です。
mysqldump -uroot -proot mysql ユーザー db tables_priv -t --skip-extended-insert > /tmp/user_info.sql

#エクスポート固有の情報--
-- テーブル `user` のデータをダンプしています
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` でキーを無効にする */;
`user` に値を挿入します ('%','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',''、''、'_binary ''、_binary ''、0、0、0、0、'mysql_native_password'、'*
81F5E21E35407D884A6CD4A731AEBFB6AF209E1B','N','2019-03-06 03:03:15',NULL,'N');
`user` に INSERT INTO VALUES ('localhost','mysql.session','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',''、_binary ''、_binary ''、0、0、0、0、'mysql_na
tive_password','*これはここで使用できる有効なパスワードではありません','N','2019-03-06 02:57:40',NULL,'Y');
`user` に INSERT INTO VALUES ('localhost','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',''_binary '',_binary '',0,0,0,0,'mysql_native
_password','*これはここで使用できる有効なパスワードではありません','N','2019-03-06 02:57:40',NULL,'Y');
`user` に値を挿入します ('%'、'test'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、''、_binary ''、_binary ''、0、0、0、0、'mysql_native_password'、'*'
94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29','N','2019-04-19 06:24:54',NULL,'N');
`user` に値を挿入します ('%'、'read'、'Y'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、''、'_binary ''、_binary ''、0、0、0、0、'mysql_native_password'、'*'
2158DEFBE7B6FC24585930DF63794A2A44F22736','N','2019-04-19 06:27:45',NULL,'N');
`user` に値を挿入します ('%'、'test_user'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、''、'_binary ''、_binary ''、0、0、0、0、'mysql_native_password
d','*8A447777509932F0ED07ADB033562027D95A0F17','N','2019-04-19 06:29:38',NULL,'N');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
テーブルのロックを解除します。

--
-- テーブル `db` のデータをダンプしています
--

LOCK TABLES `db` WRITE;
/*!40000 ALTER TABLE `db` でキーを無効にする */;
`db` に値を挿入します ('localhost'、'performance_schema'、'mysql.session'、'Y'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N');
`db` に値 ('localhost'、'sys'、'mysql.sys'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'N'、'Y') を挿入します。
`db` に値を挿入します ('%'、'test_db'、'test'、'Y'、'Y'、'Y'、'Y'、'N'、'N'、'Y'、'N'、'Y'、'N'、'Y'、'Y'、'N'、'Y'、'N'、'Y'、'N'、'N'、'Y'、'N'、'N'、'Y'、'N'、'N');
/*!40000 ALTER TABLE `db` ENABLE KEYS */;
テーブルのロックを解除します。

--
-- テーブル `tables_priv` のデータをダンプしています
--

LOCK TABLES `tables_priv` WRITE;
/*!40000 ALTER TABLE `tables_priv` でキーを無効にする */;
`tables_priv` に値 ('localhost'、'mysql'、'mysql.session'、'user'、'boot@connecting host'、'0000-00-00 00:00:00'、'Select'、'') を挿入します。
`tables_priv` に値 ('localhost'、'sys'、'mysql.sys'、'sys_config'、'root@localhost'、'2019-03-06 02:57:40'、'Select'、'') を挿入します。
`tables_priv` に値を挿入します ('%'、'test_db'、'test_user'、't1'、'root@localhost'、'0000-00-00 00:00:00'、'Select、Insert、Update、Delete'、'');
/*!40000 ALTER TABLE `tables_priv` ENABLE KEYS */;
テーブルのロックを解除します。

#新しいインスタンスに必要なデータを挿入して、同じユーザーと権限を作成します

2. カスタムスクリプトのエクスポート

まず、ステートメントを連結してユーザーを作成します。

選択
	連結(
		'ユーザーを作成 \'',
  ユーザー、
  '\'@\''、
  ホスト、
  '\''
  ' パスワードで識別 \'',
  認証文字列、
		'\';'
	) AS CreateUserQuery
から
	mysql.`ユーザー`
どこ
	`User` が ( にありません
		'mysql.セッション',
		'mysql.sys'
	);
	
#結果: 新しいインスタンスで実行すると、同じパスワードを持つユーザーを作成できます。mysql> SELECT
  -> 連結(
  -> 'ユーザーを作成 \'',
  -> ユーザー、
  -> '\'@\''、
  -> ホスト、
  -> '\''
  -> ' パスワードで識別 \'',
  -> 認証文字列、
  -> '\';'
  -> ) AS CreateUserQuery
  -> から
  -> mysql.`ユーザー`
  -> どこ
  -> `User` が存在しません (
  -> 'mysql.session',
  -> 'mysql.sys'
  -> );
+---------------------------------------------------------------------------------------------------------------------------------+
| ユーザークエリの作成 |
+---------------------------------------------------------------------------------------------------------------------------------+
| ユーザー 'root'@'%' を作成します (パスワード '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' で識別されます)。|
| ユーザー 'test'@'%' を作成します (パスワード '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' で識別されます)。|
| ユーザー 'read'@'%' を作成します (パスワード '*2158DEFBE7B6FC24585930DF63794A2A44F22736' で識別されます)。|
| ユーザー 'test_user'@'%' を作成します。パスワード '*8A447777509932F0ED07ADB033562027D95A0F17' で識別されます。|
+---------------------------------------------------------------------------------------------------------------------------------+
セット内の 4 行 (0.00 秒)

次に、スクリプトを使用してユーザー権限をエクスポートします。

#権限スクリプトのエクスポート#!/bin/bash 
#関数ユーザー権限のエクスポート 
 
パスワード=ルート 
外国人移住者() 
{ 
 mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@'''', host, ''';' ) AS query FROM mysql.user" | \
 mysql -u'root' -p${pwd} $@ | \
 sed 's/\(GRANT .*\)/\1;/;s/^\(.*\ の許可)/-- \1 /;/--/{x;p;x;}' 
} 
 
expgrants > /tmp/grants.sql
echo "権限をフラッシュします。" >> /tmp/grants.sql

#スクリプトを実行した後の結果-- read@% の許可 
*.* に対して 'read'@'%' の SELECT 権限を付与します。

-- root@% への権限付与 
GRANT オプション付きで、*.* のすべての権限を 'root'@'%' に付与します。

-- テストの許可@% 
*.* の使用権限を 'test'@'%' に付与します。
`test_db`.* に対して SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER、EXECUTE、CREATE VIEW、SHOW VIEW 権限を 'test'@'%' に付与します。

-- test_user@% への許可 
*.* の使用権限を 'test_user'@'%' に付与します。
`test_db`.`t1` に対する SELECT、INSERT、UPDATE、DELETE 権限を 'test_user'@'%' に付与します。

-- mysql.session@localhost への許可 
'mysql.session'@'localhost' に *.* の SUPER 権限を付与します。
'mysql.session'@'localhost' に `performance_schema`.* の SELECT 権限を付与します。
`mysql`.`user` に対する 'mysql.session'@'localhost' への SELECT 権限を付与します。

-- mysql.sys@localhost への許可 
*.* の使用権限を 'mysql.sys'@'localhost' に付与します。
`sys`.* のトリガーを 'mysql.sys'@'localhost' に付与します。
`sys`.`sys_config` に対する 'mysql.sys'@'localhost' への SELECT 権限を付与します。

3.mysqlpumpはユーザーを直接エクスポートします

mysqlpump は mysqldump の派生であり、MySQL 論理バックアップ用のツールでもあります。 mysqlpump にはさらに多くのオプションが用意されており、ユーザーの作成や権限の付与のためのステートメントを直接エクスポートできます。これを実証してみましょう:

#exclude-databases はデータベースを除外します --users はエクスポートするユーザーを指定します exclude-users は除外するユーザーを指定します #--add-drop-user パラメータを追加して、drop user ステートメントを生成することもできます #データベースで GTID が有効になっている場合は、エクスポート時に --set-gtid-purged=OFF を追加する必要があります
mysqlpump -uroot -proot --exclude-databases=% --users --exclude-users=mysql.session,mysql.sys > /tmp/user.sql

#エクスポート結果 - MySQL ポンプ ユーティリティによって作成されたダンプ、バージョン: 5.7.23、linux-glibc2.12 (x86_64)
-- ダンプ開始時間: 2019 年 4 月 19 日金曜日 15:03:02
-- サーバーバージョン: 5.7.23

@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS、UNIQUE_CHECKS=0 に設定します。
@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS、FOREIGN_KEY_CHECKS=0 を設定します。
@OLD_SQL_MODE を @@SQL_MODE に設定します。
SQL_MODE を「NO_AUTO_VALUE_ON_ZERO」に設定します。
@@SESSION.SQL_LOG_BIN= 0 を設定します。
@OLD_TIME_ZONE=@@TIME_ZONE を設定します。
TIME_ZONE='+00:00' を設定します。
@OLD_CHARACTER_SET_CLIENT を @@CHARACTER_SET_CLIENT に設定します。
@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS を設定します。
@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION を設定します。
名前を設定します utf8mb4;
CREATE USER 'read'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2158DEFBE7B6FC24585930DF63794A2A44F22736' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
*.* に対して 'read'@'%' の SELECT 権限を付与します。
CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT オプション付きで、*.* のすべての権限を 'root'@'%' に付与します。
CREATE USER 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
*.* の使用権限を 'test'@'%' に付与します。
`test_db`.* に対して SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER、EXECUTE、CREATE VIEW、SHOW VIEW 権限を 'test'@'%' に付与します。
CREATE USER 'test_user'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8A447777509932F0ED07ADB033562027D95A0F17' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
*.* の使用権限を 'test_user'@'%' に付与します。
`test_db`.`t1` に対する SELECT、INSERT、UPDATE、DELETE 権限を 'test_user'@'%' に付与します。
TIME_ZONE を @OLD_TIME_ZONE に設定します。
CHARACTER_SET_CLIENT を @OLD_CHARACTER_SET_CLIENT に設定します。
CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS を設定します。
COLLATION_CONNECTION を @OLD_COLLATION_CONNECTION に設定します。
FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS を設定します。
UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS を設定します。
SQL_MODE を @OLD_SQL_MODE に設定します。
-- ダンプ終了時間: 2019 年 4 月 19 日金曜日 15:03:02

#エクスポート結果には、ユーザーの作成と権限の付与に関するステートメントのみが含まれていることがわかります。これは非常に便利です。#mysqlpump の詳細な使用方法については、以下を参照してください。
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

要約:

この記事では、データベースユーザー情報をエクスポートするための 3 つのソリューションを紹介します。各ソリューションにはスクリプトが用意されており、そのデモも行われます。同時に、これら 3 つのソリューションを少しカプセル化して、データベースユーザー権限をバックアップするためのスクリプトとして使用することもできます。おそらく、pt-show-grants などの他のソリューションがあるでしょう。ぜひ共有してください。また、それらを収集したり、より適切なスクリプトに変更したりすることもできます。いつ必要になるかはわかりません。特に、インスタンスに多くのユーザーが存在する場合は、スクリプトの方が便利であることがわかります。

上記は、MySQL アカウントをエレガントにバックアップする方法に関する詳細情報です。MySQL バックアップアカウントの詳細については、123WORDPRESS.COM の他の関連記事をご覧ください。

以下もご興味があるかもしれません: