mySQLバックアップ・リストア
津路です。
久しぶりに投稿します。
今回は、mySQLを取り扱うことになり、経験談です。
環境は、Ubutu20.04LTSバージョンに、XAMPPをインストールしてます。
標準では、sudo /opt/lampp/lampp start コマンドで、Apache, mySQLが起動します。
最初に、sudo /opt/lampp/mysql -u rootでログインしちゃって、パスワードを設定するんですね。
ところが、このパスワード、しばらく使わないことになるので、忘れてしまいます。
というのが、まず問題の原因。
そして、mySQLにて、独自データベースを作成して、テーブルを作ったり、レコードを登録したりしているうちに、
mySQLのディレクトリ、/opt/lampp/var/mysqlには、巨大なファイルが出来上がります。
そこで、このibdata1, ib_logfile0/1などのファイルを小さくするにはどうするかと申しますと、一旦全体をバックアップして、
復元するのが、良いそうなんです。
と、それを実行しようとすると、日常は独自データベースとユーザで管理しているため、rootで入ろうとすると、パスワードを忘れてます。
ここで、my.iniの[mysqld]項目に行を追加すればよいのですが、linuxでは、my.cnfです。
my.cnfに、恐ろしい行を追加して、サーバー再起動して、パスワードなしでログインして、パスワード設定して、ログアウトして、追加した行を削除して、再起動しなおす。。。
さて、バックアップするときには、
1 | sudo /opt/lampp/bin/mysqldump -uroot -p --opt --all-databases <~alldb.sql |
といったコマンドで、sqlの塊をファイルに保存します。
ここに、独自のデータベース以外の、mySQLのデータベースsqlが、相当数入るのです。
ほとんどは、知らないテーブルだったりします。
次に、mySQLサーバーを停止するため、lamppコマンドでstopをかけます。
停止したら、問題の巨大なファイルを削除してしまいます。
といっても、とりあえずは、どこかに退避するのですが。。
sudo rm ibdata1 ib_logfile0 ib_logfile1
ファイルを削除しましたら、lamppを再起動します。
そして、問題の行動に移ります。
1 | sudo /opt/lampp/bin/mysql -u root -p <~alldb.sql |
とすると、必ず、データベースファイルの不整合が起こっているため、エラーが起きます。
1 | Tablespace for table '`mysql`.`???`' exists. Please DISCARD the tablespace before IMPORT |
エラーの中の、???という名のtableファイルが不安定なまま残っているというのが、問題です。
mySQLユーザが管理するディレクトリにあるため、そのままでは削除できません。Access Deniedになります。
ここで、ユーザをmySQLに切り替えればよいのですが、それも不可能となり、困ります。
権限の問題は、一旦、OSサインアウトして、サインインしなおします。
すると、sudoをつけることで、データベースファイルを削除できます。
ところが、上記で述べた通り、多くのテーブルを知らないため、リストアしようとして、そのたび別のテーブルのエラーが発生します。
ということで、全体をバックアップ・リストアする前に、知らないテーブルについては、調べて、知っているテーブルだけを対象にするのが良いでしょう。
ibdata1については、１０分の１程度になりました。