gihyo.jpサイトのロゴ

MySQL道普請便り

第160回pt-show-grantsを使ってユーザアカウントを移行する

2021-11-30

今回は本連載において何度か登場しているPercona Toolkitのツールの1つ、pt-show-grantsについて紹介したいと思います。Percona Toolkitのツールに関しては、過去に以下の記事があります。インストール方法などもそちらで説明していますので、併せてご確認ください。

今回使用するPercona Toolkitのバージョンは、2021年11月時点で最新の3.3.1です。MySQLは8.0.26を使用しています。

pt-show-grantsを使ってみる

pt-show-grantsは指定したMySQLに登録させているユーザアカウント一覧を出力してくれます。さらに、それぞれのユーザアカウントをCREATE USER構文の形式、許可されている権限をGrant構文の形式で出力します。

例として、MySQLには'u1'@'10.0.0.1'と'u2'@'10.0.0.2'の2つのユーザアカウントのみが登録されているとします（rootユーザはもちろん登録されていますが、出力が長くなるため表示は割愛しています⁠）⁠。

% pt-show-grants -h 127.0.0.1 -P 3306 -u root --ask-pass
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 8.0.26 at 2021-11-21 07:35:46
-- Grants for 'u1'@'10.0.0.1'
CREATE USER IF NOT EXISTS 'u1'@'10.0.0.1';
ALTER USER 'u1'@'10.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*DEE59C300700AF9B586F9F2A702231C0AC373A13' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT DELETE, EXECUTE, INSERT, SELECT, UPDATE ON `test`.* TO `u1`@`10.0.0.1`;
GRANT USAGE ON *.* TO `u1`@`10.0.0.1`;
-- Grants for 'u2'@'10.0.0.2'
CREATE USER IF NOT EXISTS 'u2'@'10.0.0.2';
ALTER USER 'u2'@'10.0.0.2' IDENTIFIED WITH 'mysql_native_password' AS '*164E743F997F9503018E3E97727508D8B19569D4' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT SELECT ON *.* TO `u2`@`10.0.0.2`;

このように、存在するユーザの一覧がCREATE USERやGRANT構文の形で表示されます。

pt-show-grantsが接続するMySQLの指定方法は、mysqlコマンドと同じようなオプションになっています。

  • -h …接続するMySQLのホスト名
  • -P …接続するMySQLのポート
  • -u …接続するMySQLのユーザ名
  • -p または --ask-pass …-pのあとに接続するMySQLのユーザのパスワードを指定。--ask-passは接続時にパスワードの入力を促します。mysqlコマンドの-pにパスワード指定しないでEnterを押したときの動きと同等です。

pt-show-grantsのオプションについて

前述の例では、そのMySQLに登録しているユーザアカウントをすべて出力します。オプションにより、出力するユーザを制限したりすることもできます。いくつかのオプションについて紹介したいと思います。

●--only

指定したユーザ名またはユーザアカウントのみを出力します。複数ユーザはカンマ区切りで指定します。

% pt-show-grants -h 127.0.0.1 -P 3306 -u root --ask-pass --only u1
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 8.0.26 at 2021-11-21 07:50:58
-- Grants for 'u1'@'10.0.0.1'
CREATE USER IF NOT EXISTS 'u1'@'10.0.0.1';
ALTER USER 'u1'@'10.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*DEE59C300700AF9B586F9F2A702231C0AC373A13' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT DELETE, EXECUTE, INSERT, SELECT, UPDATE ON `test`.* TO `u1`@`10.0.0.1`;
GRANT USAGE ON *.* TO `u1`@`10.0.0.1`;

●--ignore

指定したユーザアカウントを除外して出力します。複数ユーザアカウントはカンマ区切りで指定します。

% pt-show-grants -h 127.0.0.1 -P 3306 -u root --ask-pass --ignore 'u1'@'10.0.0.1'
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 8.0.26 at 2021-11-21 07:57:37
-- Grants for 'u2'@'10.0.0.2'
CREATE USER IF NOT EXISTS 'u2'@'10.0.0.2';
ALTER USER 'u2'@'10.0.0.2' IDENTIFIED WITH 'mysql_native_password' AS '*164E743F997F9503018E3E97727508D8B19569D4' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT SELECT ON *.* TO `u2`@`10.0.0.2`;

--onlyはユーザ名とユーザアカウント（'ユーザ名'@'ホスト'）が許可され、--ignoreはユーザアカウントのみが許可されるので注意が必要です。よって、--ignoreを使って特定のユーザ名を一括で除外することはできないので、カンマ区切りで除外したいすべてのユーザアカウントを指定する必要があります。--ignore 'u1'@'10.0.0.1','u1'@'10.0.0.2','u1'@'10.0.0.3'のようになります。

●--drop

DROP USER文を出力の前に追加します。

% pt-show-grants -h 127.0.0.1 -P 3306 -u root --ask-pass --only 'u1'@'10.0.0.1' --drop
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 8.0.26 at 2021-11-21 08:08:58
DROP USER 'u1'@'10.0.0.1';
DELETE FROM `mysql`.`user` WHERE `User`='u1' AND `Host`='10.0.0.1';
-- Grants for 'u1'@'10.0.0.1'
CREATE USER IF NOT EXISTS 'u1'@'10.0.0.1';
ALTER USER 'u1'@'10.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*DEE59C300700AF9B586F9F2A702231C0AC373A13' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT SELECT ON `test`.* TO `u1`@`10.0.0.1`;
GRANT USAGE ON *.* TO `u1`@`10.0.0.1`;

●--revoke

ユーザアカウントがもっている権限のREVOKE文を、出力の前に追加にします。

% pt-show-grants -h 127.0.0.1 -P 3306 -u root --ask-pass --only 'u1'@'10.0.0.1' --revoke
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 8.0.26 at 2021-11-21 08:20:23
-- Revoke statements for 'u1'@'10.0.0.1'
REVOKE DELETE, EXECUTE, INSERT, SELECT, UPDATE ON `test`.* FROM `u1`@`10.0.0.1`;
REVOKE USAGE ON *.* FROM `u1`@`10.0.0.1`;
-- Grants for 'u1'@'10.0.0.1'
CREATE USER IF NOT EXISTS 'u1'@'10.0.0.1';
ALTER USER 'u1'@'10.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*DEE59C300700AF9B586F9F2A702231C0AC373A13' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT DELETE, EXECUTE, INSERT, SELECT, UPDATE ON `test`.* TO `u1`@`10.0.0.1`;
GRANT USAGE ON *.* TO `u1`@`10.0.0.1`;

●--separete

ユーザアカウントごとの権限を1つのステートメントにまとめずに、それぞれのステートメントで出力します。

% pt-show-grants -h 127.0.0.1 -P 3306 -u root --ask-pass --only 'u1'@'10.0.0.1' --separate
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 8.0.26 at 2021-11-21 08:17:55
-- Grants for 'u1'@'10.0.0.1'
CREATE USER IF NOT EXISTS 'u1'@'10.0.0.1';
ALTER USER 'u1'@'10.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*DEE59C300700AF9B586F9F2A702231C0AC373A13' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT DELETE ON `test`.* TO `u1`@`10.0.0.1`;
GRANT EXECUTE ON `test`.* TO `u1`@`10.0.0.1`;
GRANT INSERT ON `test`.* TO `u1`@`10.0.0.1`;
GRANT SELECT ON `test`.* TO `u1`@`10.0.0.1`;
GRANT UPDATE ON `test`.* TO `u1`@`10.0.0.1`;
GRANT USAGE ON *.* TO `u1`@`10.0.0.1`;

●--[no]include-unused-roles

MySQL 8.0以上の場合、ロールをダンプする際に未使用のロールを含めるかどうか指定します。

pt-show-grantsの使いどころについて

pt-show-grantsの使いどころとしては、以下が上げられます。

  • ユーザアカウントのバックアップ
  • ユーザアカウントのバージョン管理
  • ユーザアカウントの差分抽出
  • MySQLのアップグレード時のユーザアカウント移行手段

ユーザアカウントの差分抽出

先ほど説明した--separeteを使って出力することで、異なるMySQL間のユーザアカウントの権限の差分が比較しやすくなります。マスターとレプリカ間でユーザアカウントが異なっているか確認に使用することができます。

MySQLのアップグレード時のユーザアカウント移行手段

筆者は主にこの用途でpt-show-grantsを使用します。MySQLのインプレースアップグレードではなくアップグレードした別のMySQLインスタンスを用意してそこへ移行するときに役に立ちます。pt-show-grantsで出力された内容はそのまま実行できる形式になっているので、パイプで別のMySQLインスタンスに渡せば、ユーザアカウントの移行が可能です。

% pt-show-grants -h 127.0.0.1 -P 3306 -u root --ask-pass --only 'u1'@'10.0.0.1' | mysql -h new_mysql_host -P 3306 -u root -p

アップグレードしたMySQLインスタンスへの移行の大まかな流れとしては、以下になります。

  1. アップグレードしたMySQLインスタンス（移行先DB）を用意
  2. pt-show-grantsにて移行元DBからユーザアカウントを抽出し、移行先DBに適用。
  3. mysqldumpにて移行元DBからユーザデータベースのみエクスポートし、移行先DBにインポート。
  4. 移行元DB→移行先DBでレプリケーション構築
  5. アプリケーションの向き先を移行先DBに切り替える

ユーザアカウントの設定は、mysqlデータベースのテーブルに格納されています。移行の際に、mysqldumpを使用してmysqlデータベースをインポートせずにpt-show-grantsを使用する理由としてはバージョンによりmysqlデータベースのテーブル構成が異なるためです。mysqlデータベースをインポートすると、それらが上書きされ、mysql_upgradeコマンドでmysqlデータベースのテーブル構成を更新する必要があります。

pt-show-grantsでユーザアカウントを移行することで、mysql_upgradeコマンドの実行し忘れによる予期せぬ障害を未然に防ぐことができます（注意：MySQL 8.0.16以降では起動時にアップグレードタスクを実行するようになったため、mysql_upgradeコマンド実行は不要です⁠）⁠。

また、この方法はさまざまなバージョンのアップグレード（MySQL 5.6から5.7、MySQL 5.7から8.0など）においても同様の方式で利用できるため、アップグレード方式を標準化することができます。そして、必要なユーザアカウントのみ移行するなど、ユーザの精査もしやすくなります。

まとめ

今回はpt-show-grantsについて紹介しました。Percona社が提供しているマニュアルpt-show-grantsを元に紹介しましたので、もっと深く知りたい方はこちらをご確認ください。

おすすめ記事

記事・ニュース一覧