今までbackupに関しては、
検証環境
今回は、
pt-achiverを使用する
pt-achiverは、
では、
$ pt-archiver --souce u=root,p=password,h=127.0.01,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'"
さて、
Cannot find an ascendable index in table at /usr/bin/pt-archiver line 3233.
こちらのエラー内容ですが、
root@267e35517618:/# mysql -uroot -ppassword zipcode mysql> ALTER TABLE zipcode ADD INDEX index_prefecture(prefecture);
これでもう一度実行してみましょう。--sourceにはDSN形式で接続情報を記載します。今回はローカルのファイルにデータを取得するため--fileオプションで指定します。--no-deleteオプションですが、
--whereオプションでWHERE句に指定したいWHERE句を書くことで取得ができます。
$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'" $ cat zipcode 01101 060 0600000 ﾎｯｶｲﾄﾞｳ ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ 北海道 札幌市中央区 以下に掲載がない場合 $
このように、--dry-runオプションです。こちらを着けて実行してみます。
$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'" --dry-run SELECT /*!40001 SQL_NO_CACHE */ `code`,`old_zipcode`,`zip_code`,`prefecture_kana`,`city_kana`,`town_kana`,`prefecture`,`city`,`town` FROM `zipcode`.`zipcode` FORCE INDEX(`index_prefecture`) WHERE (prefecture='北海道') ORDER BY `prefecture` LIMIT 1 SELECT /*!40001 SQL_NO_CACHE */ `code`,`old_zipcode`,`zip_code`,`prefecture_kana`,`city_kana`,`town_kana`,`prefecture`,`city`,`town` FROM `zipcode`.`zipcode` FORCE INDEX(`index_prefecture`) WHERE (prefecture='北海道') AND ((`prefecture` > ?)) ORDER BY `prefecture` LIMIT 1
上記のように、
mysql> select prefecture, COUNT(*) FROM zipcode where prefecture = '北海道'; +------------+----------+ | prefecture | COUNT(*) | +------------+----------+ | 北海道 | 8248 | +------------+----------+ 1 row in set (0.01 sec)
北海道の8248件を--limitオプションで指定をして全部取得をしてみましょう。取得した値は上書きされてしまうので、
$ rm zipcode
$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'" --limit 8248 --statistics
$ cat zipcode |wc -l
8248
ということで、
便利なオプションに関して
その他の便利なオプションとして、--statisticsオプションがあります。このオプションを付けて実行すると、
$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'" --limit 8248 --statistics Started at 2021-10-01T12:48:55, ended at 2021-10-01T12:49:09 Source: D=zipcode,h=127.0.0.1,p=...,t=zipcode,u=root SELECT 8248 INSERT 0 DELETE 0 Action Count Time Pct commit 8249 11.7163 89.55 print_file 8248 0.3384 2.59 select 2 0.0300 0.23 other 0 0.9989 7.64
データの取得時に一貫性を保ちたいこともあると思いますが、--for-updateオプションを付けてみると、
kimurakouichirounoMacBook-Pro:for_training_sql koichiro.kimura$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --for-update --dry-run --where "prefecture='北海道'" zipcode SELECT /*!40001 SQL_NO_CACHE */ `code`,`old_zipcode`,`zip_code`,`prefecture_kana`,`city_kana`,`town_kana`,`prefecture`,`city`,`town` FROM `zipcode`.`zipcode` FORCE INDEX(`index_prefecture`) WHERE (prefecture='北海道') ORDER BY `prefecture` LIMIT 1 FOR UPDATE SELECT /*!40001 SQL_NO_CACHE */ `code`,`old_zipcode`,`zip_code`,`prefecture_kana`,`city_kana`,`town_kana`,`prefecture`,`city`,`town` FROM `zipcode`.`zipcode` FORCE INDEX(`index_prefecture`) WHERE (prefecture='北海道') AND ((`prefecture` > ?)) ORDER BY `prefecture` LIMIT 1 FOR UPDATE
上記のようにdry-runをしてみると、
また、--whereオプションは必須のオプションとなるため、1=1のような必ずtrueになる条件式を入れてあげれば取得することができます。
mysql> select count(*) from zipcode; +----------+ | count(*) | +----------+ | 124340 | +----------+ 1 row in set (0.02 sec)
zipcodeの行数は124340行になるので、
$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --where "1=1" --limit 124340 $ cat zipcode |wc -l 124340
以上のように取得ができることがわかります。
最後に、--no-deleteオプションを付けないとどうなるかを確認していきたいと思います。
こちらのオプションは付けず実行してしまうと対象のDBから行が削除されてしまうという様に説明をしましたが、--statisticsオプションを付けて実行してみましょう。
$ rm zipcode $ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --statistics --where "1=1" --limit 124340 Started at 2021-10-01T13:14:04, ended at 2021-10-01T13:23:09 Source: D=zipcode,h=127.0.0.1,p=...,t=zipcode,u=root SELECT 124340 INSERT 0 DELETE 124340 Action Count Time Pct commit 124341 378.4032 69.43 deleting 124340 148.2469 27.20 print_file 124340 2.4886 0.46 select 2 0.3625 0.07 other 0 15.4997 2.84
deletingが実行されていることがわかります。MySQLに入って確認してみましょう。
mysql> select COUNT(*) from zipcode; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec)
ということで、--no-deleteオプションが必要かどうか、
まとめ
pt-archiverを使用すると、
ただし、--no-deleteオプションを着け忘れて実行してしまうと、
また、