Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
5年弱前投稿 修正あり

MySQLのrootパスワードを、無停止でリセットする

MySQL Performance Blogの翻訳。rootパスワードが分からなくなった時の対処法としては、MySQLの再起動が必須になる方法が知られているが、本番DBではできる限り再起動を避けたいところ。そんな時に無停止でリセットするための裏技的な手順を紹介する。

原文
Recover MySQL root password without restarting MySQL (English)
原文ライセンス
CC BY-NC-SA
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
D98ee74ffe0fafbdc83b23907dda3665 doublemarket
原著者への翻訳報告
未報告


December 10, 2014 by Daniel Guzmán Burgos

注意 : ここに書かれている方法は、自身の責任で実行する事。プラガブルな認証を使用している時はこの方法は使えない。また、MySQLのシステムテーブルがInnoDBにある時も同じく適用不可能だ。

状況

定番の「MySQLのrootパスワードをリセットしたい」という状況だが、MySQLの再起動はできず(本番のマスタなど)、--skip-grant-tablesを使ったリセット方法が使えない時。 (訳注 : MySQLの再起動が必要になるリセット方法が公式な手順として広く知られている)

どうしたらいいの?

以下の方法が使える。

  • 小さくても良いのでmysqldの別インスタンスを立てる(InnoDBなし)
  • user.[frm|MYD|MYI]ファイルを、現在のdatadirから新しく立てたインスタンスのdatadirにコピーする
  • 新しいインスタンスでユーザ情報を変更した後、コピーしたファイルを元の場所に戻す

簡単だって?そうでもないが、まあ難しくはない。手順を見て行こう。

ステップバイステップ

  1. 新しいdatadirを作り、そのdatadirでmysql\_install\_dbを実行する。こちらのdatadirは最終的には削除する事になる。所有者をmysqlユーザmysqlグループにするのを忘れないように。

    [[email protected] dbdata]# mkdir datadir
    [[email protected] dbdata]# chown -R mysql:mysql datadir/
    [[email protected] dbdata]# mysql_install_db --datadir=/dbdata/datadir/ --user=mysql
    Installing MySQL system tables...OK
    Filling help tables...OK
    
  2. 新しいインスタンスを起動する。datadirのパス、ソケットファイル、ポート番号に注意しよう。また、InnoDBは必要ないので、--skip-innodb--default-storage-engine=myisamを付けて無効にする。

    [[email protected] datadir]# /usr/sbin/mysqld --basedir=/usr --datadir=/dbdata/datadir --plugin-dir=/usr/lib/mysql/plugin --skip-innodb --default-storage-engine=myisam --socket=/var/run/mysqld/mysql2.sock --port=3307 --user=mysql --log-error=/dblogs/log/error2.log --pid-file=/dbdata/data/mysql.pid &
    
  3. 元のMySQLインスタンスのdatadirから新しいインスタンスのdatadirへ、user.*ファイルをコピーする。それから新しいMySQLインスタンスにログイン。

    [[email protected] ~]# cp /dbdata/data/mysql/user.* /dbdata/datadir/mysql/cp: overwrite `/dbdata/datadir/mysql/user.frm'? y
    cp: overwrite `/dbdata/datadir/mysql/user.MYD'? y
    cp: overwrite `/dbdata/datadir/mysql/user.MYI'? y
    [[email protected] datadir]# mysql --socket=/var/run/mysqld/mysql2.sock -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or g.
    
  4. flush tablesコマンドを実行し、ユーザテーブルを開きなおそう。その後、データを確認する。

    mysql2> flush tables;
    mysql2> select user, host, password from user where user like 'root';
    +------+--------------------------------------+------------------------------------------+
    | user | host                                 | password                                 |
    +------+--------------------------------------+------------------------------------------+
    | root | localhost                            | 696D727429CC43695423FA5F2F0155D92A0AAC08 |
    | root | 127.0.0.1                            | 696D727429CC43695423FA5F2F0155D92A0AAC08 |
    | root | %                                    | 696D727429CC43695423FA5F2F0155D92A0AAC08 |
    +------+--------------------------------------+------------------------------------------+
    3 rows in set (0.00 sec)
    
  5. ここで、希望の値でパスワードを更新する。

    mysql2> update mysql.user set password='*696D727429CC43695423FA5F2F0155D92A0AAC08' where user like 'root';
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    
  6. もう一度見てみる。

    mysql2> select user, host, password from user where user like 'root';
    +------+--------------------------------------+-------------------------------------------+
    | user | host                                 | password                                  |
    +------+--------------------------------------+-------------------------------------------+
    | root | localhost                            | *696D727429CC43695423FA5F2F0155D92A0AAC08 |
    | root | 127.0.0.1                            | *696D727429CC43695423FA5F2F0155D92A0AAC08 |
    | root | %                                    | *696D727429CC43695423FA5F2F0155D92A0AAC08 |
    +------+--------------------------------------+-------------------------------------------+
    3 rows in set (0.00 sec)
    
  7. flush priviledgesを実行してからログインし直して、新しいパスワードが正しく設定されたか確認。

    mysql2> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
  8. これで設定はできたので、user.*ファイルを元に戻せるようになった。この時も、ファイルの所有者と権限は、くどいほど確認しよう。

    [[email protected] ~]# cd /dbdata/datadir/mysql/
    [[email protected] mysql]# cp user.* /dbdata/data/mysql/; chown mysql:mysql /dbdata/data/mysql/user.*; chmod 660 /dbdata/data/mysql/user.*
    cp: overwrite `/dbdata/data/mysql/user.frm'? y
    cp: overwrite `/dbdata/data/mysql/user.MYD'? y
    cp: overwrite `/dbdata/data/mysql/user.MYI'? y
    
  9. この時点でもう新しく立てたインスタンスは不要なので、シャットダウンしてかまわない。元のmysqldを止めてしまわないように、よーく注意しよう。

    [[email protected] datadir]# mysqladmin --socket=/var/run/mysqld/mysql2.sock -p shutdown
    Enter password:
    141120 06:59:14 mysqld_safe mysqld from pid file /dbdata/data/mysql.pid ended
    
  10. 最後のステップは、元のmysqldの方でのFLUSH PRIVILEDGESの実行だ。(パスワードが分からず)まだアクセスできないはずなので、mysqldにSIGHUPシグナルを送る必要がある。MySQLはこのシグナルには、権限テーブルをリロードし、テーブルやログ、スレッドキャッシュ、ホストキャッシュをフラッシュする。という事は、SIGHUPを送るとパフォーマンスが悪化する(flush tableの項を見よう)。従って、どのタイミングでこれを行うかは慎重に決める必要がある。

    [[email protected] datadir]# kill -1 $(/sbin/pidof mysqld)
    
  11. これで、rootでMySQLにログインできる!

    [[email protected] datadir]# mysql -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 101208
    mysql1> select user, host, password from mysql.user where user like 'root';
    +------+--------------------------------------+-------------------------------------------+
    | user | host                                 | password                                  |
    +------+--------------------------------------+-------------------------------------------+
    | root | localhost                            | *696D727429CC43695423FA5F2F0155D92A0AAC08 |
    | root | 127.0.0.1                            | *696D727429CC43695423FA5F2F0155D92A0AAC08 |
    | root | %                                    | *696D727429CC43695423FA5F2F0155D92A0AAC08 |
    +------+--------------------------------------+-------------------------------------------+
    3 rows in set (0.00 sec)
    

    スキーマが見えるだろうか?もちろん見える!データベースも問題なし!

    mysql1> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | percona            |
    | testing            |
    +--------------------+
    4 rows in set (0.03 sec)
    

これで、MySQLを再起動させることなくrootパスワードをリカバリできて、ダウンタイムを避けられた。

こういった状況にならない事を望んでいるが、もしそうなってしまった時は、アクセス権を取り戻す時の方法の1つになるだろう。他に方法はあるだろうか?

この方法を世界中に公開しよう!

次の記事
MySQLの新しいデータディクショナリー
前の記事
プログラミング初心者に言ってはいけないこと

Feed small 記事フィード

新着記事Twitterアカウント

新着翻訳リクエストTwitterアカウント