今回は本連載において何度か登場しているPercona Toolkitのツールの1つ、
- 第9回 pt-query-digestを使って遅いクエリーを発見する
- 第116回 pt-upgradeを使ってインデックスチューニングの成果を確認する
- 第156回 pt-archiverを使って部分的にテーブルから情報を取得する
今回使用するPercona Toolkitのバージョンは、
pt-show-grantsを使ってみる
pt-show-grantsは指定したMySQLに登録させているユーザアカウント一覧を出力してくれます。さらに、
例として、'u1'@'10.
と'u2'@'10.
の2つのユーザアカウントのみが登録されているとします
% 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`;
このように、
pt-show-grantsが接続するMySQLの指定方法は、
- -h …接続するMySQLのホスト名
- -P …接続するMySQLのポート
- -u …接続するMySQLのユーザ名
- -p または --ask-pass …-pのあとに接続するMySQLのユーザのパスワードを指定。--ask-passは接続時にパスワードの入力を促します。mysqlコマンドの-pにパスワード指定しないでEnterを押したときの動きと同等です。
pt-show-grantsのオプションについて
前述の例では、
●--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.
のようになります。
●--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.
pt-show-grantsの使いどころについて
pt-show-grantsの使いどころとしては、
- ユーザアカウントのバックアップ
- ユーザアカウントのバージョン管理
- ユーザアカウントの差分抽出
- MySQLのアップグレード時のユーザアカウント移行手段
ユーザアカウントの差分抽出
先ほど説明した--separete
を使って出力することで、
MySQLのアップグレード時のユーザアカウント移行手段
筆者は主にこの用途でpt-show-grantsを使用します。MySQLのインプレースアップグレードではなくアップグレードした別のMySQLインスタンスを用意してそこへ移行するときに役に立ちます。pt-show-grantsで出力された内容はそのまま実行できる形式になっているので、
% 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インスタンスへの移行の大まかな流れとしては、
- アップグレードしたMySQLインスタンス
(移行先DB) を用意 - pt-show-grantsにて移行元DBからユーザアカウントを抽出し、
移行先DBに適用。 - mysqldumpにて移行元DBからユーザデータベースのみエクスポートし、
移行先DBにインポート。 - 移行元DB→移行先DBでレプリケーション構築
- アプリケーションの向き先を移行先DBに切り替える
ユーザアカウントの設定は、mysql
データベースのテーブルに格納されています。移行の際に、mysql
データベースをインポートせずにpt-show-grantsを使用する理由としてはバージョンによりmysql
データベースのテーブル構成が異なるためです。mysql
データベースをインポートすると、mysql
データベースのテーブル構成を更新する必要があります。
pt-show-grantsでユーザアカウントを移行することで、
また、
まとめ
今回はpt-show-grantsについて紹介しました。Percona社が提供しているマニュアルpt-show-grantsを元に紹介しましたので、