8回目-17.MySQLデータ移行

さくらVPSCentOS 7 2週間無料のお試し期間 8回目

気が付いたこと / 一覧

 状態:-  閲覧数:579  投稿日:2018-03-20  更新日:2018-03-21

気が付いたこと


mysqldump
・ネットで検索する際は、用語が紛らわしいので注意が必要
・コマンドを指す場合もあるし、取得したdumpデータを指す場合もある
・「mysqldump」としか書かれていない場合、エクスポートを指すことが多い

mysqldumpコマンド実行
・sudo付与が必要

MySQL全データのエクスポートとインポート
・基本形を押さえておくことが重要
・「エクスポート」「インポート」の違いは記号の向きだけだと思っていた ← 全く違う!

エクスポート
・mysqldumpコマンド
・「--all-databases」もしくは短縮形の「-a」オプション使用
・記号の向きは「>」
mysqldump -u root -p --all-databases > 任意の出力ファイル名.任意の拡張子

インポート
・mysqlコマンド
・「--all-databases」もしくは短縮形の「-a」オプションは使用しない
・記号の向きは「<」
mysql -u ユーザー名 -p < dumpファイル名

一覧


mysqldumpコマンドのオプション確認

mysqldumpコマンドのオプション確認

 閲覧数:116 投稿日:2018-03-20 更新日:2018-03-21 

--routinesオプション


--routinesオプションを付けて取ったバックアップには、ルーチンを再作成するためのCREATE PROCEDUREおよびCREATE FUNCTION文が含まれる
・プロシージャや関数がある場合はこのオプションを付ける必要がある

xオプション


lock-all-tablesオプションの短縮形
・データベースの全てのテーブルをロックしてダンプを行う
・global read lockを取得して実現される
・自動的にsingle-transactionオプションとlock-tablesオプションはオフにされる

$ mysqldump --single-transaction --routines -u root -p -x --all-databases > mysqlallbackup20180320.dump
mysqldump: You can't use --single-transaction and --lock-all-tables at the same time.


同時使用不可なので
・xオプションを外す

新サーバでmysqldump実行 ← 誤り。意味ないやん!

 閲覧数:124 投稿日:2018-03-20 更新日:2018-03-20 
18:59実行
$ mysqldump --single-transaction --routines -u root -p --all-databases > mysqlallbackup20180320.dump
Enter password:
$


確認
▼/home/★★/mysqlallbackup20180320.dump
・あれ? もう出来てる
・ファイルサイズ。817,263
・そんなに早いわけがない
・新しいサーバでDUMPデータ取得していた
・いやー、慣れって恐ろしいわ

$ rm mysqlallbackup20180320.dump    


旧サーバでmysqldump実行

 閲覧数:145 投稿日:2018-03-20 更新日:2018-03-21 
19:07実行
$ mysqldump --single-transaction --routines -u root -p --all-databases > mysqlallbackup20180320.dump
Enter password:
$


19:13時点
・ファイルサイズ0
・大丈夫かしら?

19:17時点
・ファイルサイズ0
・取り敢えず、ご飯にする

19:57時点
・まだファイルサイズ0だけど
・失敗してる?
ファイル開いてみたら、内容書き込まれているみたいだけれども、更新押してもファイルサイズ0
・どういう状況なの?

20:09実行
・権限の問題かもと思い、sudp付与してやり直し
$ sudo mysqldump --single-transaction --routines -u root -p --all-databases > mysqlallbackup20180320.dump
[sudo] password for ★★: 
Enter password:


20:11
・ビンゴっぽい
・更新する度、ファイルサイズが増加していく
・ちなみに、前回失敗した同名ファイル名の、ファイルサイズ0があるが、何ら判断を求められることもなく、上書きされるようだ

処理後の流れ
・次の$が表示された段階で、処理が終了した、という意味みたい
Enter password:
$


現在は処理継続中なので、下記のまま
・次の$は表示されていない
Enter password:


20:12
・最終更新日時
・あっという間に終わった
・本当に全部取得できている?
・そんなに早いの?

ファイルサイズ
・1.05GB

どうやって移すの?


真っ先に思いついたのはcpコマンド
・しかし異なるサーバ間では使用不可

新サーバ設定変更。rootログイン禁止 → rootログイン許可

 閲覧数:103 投稿日:2018-03-20 更新日:2018-03-21 

新サーバ現状設定


ポート番号(を22番より)変更
rootログイン禁止
パスワード認証禁止
公開鍵認証許可

「rootログイン禁止しているサーバ」へ対するrsync


rsyncだけroot許可できる方法があるようだが、内容が難しく理解できない
さくらVPS の契約を追加して rsync でまるっと移行してみた
パスワードありsudoでrsyncするシェルスクリプト

一時的にrootログイン許可へ変更


$ sudo vi /etc/ssh/sshd_config
$ sudo systemctl restart sshd.service
#PermitRootLogin no
PermitRootLogin yes


旧サーバよりrsync実行(失敗)

 閲覧数:132 投稿日:2018-03-20 更新日:2018-03-25 

初めてのrsync


22:06
・実行
→ 失敗
$ sudo rsync -ahvnz root@◇◇.vs.sakura.ne.jp:/var/www/* /var/www/html/
ssh: connect to host ◇◇.vs.sakura.ne.jp port 22: Connection timed out
rsync: connection unexpectedly closed (0 bytes received so far) [receiver]
rsync error: unexplained error (code 255) at io.c(600) [receiver=3.0.6]


rsync ポート番号指定


rsyncコマンドでユーザー名、ポート番号、アーカイブオプションを指定して転送する
rsync で ssh のポートを指定する方法
$ sudo rsync -e "ssh -p ポート番号" -ahvnz root@◇◇.vs.sakura.ne.jp:/var/www/* /var/www/html/
The authenticity of host '[◇◇.vs.sakura.ne.jp]:ポート番号 ([IPアドレス1]:ポート番号)' can't be established.
RSA key fingerprint is ◆◆.
Are you sure you want to continue connecting (yes/no)?

no
Host key verification failed.
rsync: connection unexpectedly closed (0 bytes received so far) [receiver]
rsync error: unexplained error (code 255) at io.c(600) [receiver=3.0.6]

-bash-4.1$ sudo rsync -e "ssh -p ポート番号" -ahvnz root@◇◇.vs.sakura.ne.jp:/var/www/* /var/www/html/
The authenticity of host '[◇◇.vs.sakura.ne.jp]:ポート番号 ([IPアドレス1]:ポート番号)' can't be established.
RSA key fingerprint is ◆◆.
Are you sure you want to continue connecting (yes/no)?

yes
Warning: Permanently added '[◇◇.vs.sakura.ne.jp]:ポート番号,[IPアドレス1]:ポート番号' (RSA) to the list of known hosts.
Permission denied (publickey).
rsync: connection unexpectedly closed (0 bytes received so far) [receiver]
rsync error: unexplained error (code 255) at io.c(600) [receiver=3.0.6]


「公開鍵のみ認証許可しているサーバ」へ対するrsync


rsync -e 'ssh -i 秘密鍵ファイルのPATH -p ポート番号' コピー元ディレクトリ ユーザ名@ホスト名:リモートのバックアップ先ディレクトリ
rsync で公開鍵を指定するには
$ sudo rsync -e "ssh -i L:\0_Establishment\sakura_vps_keys\id_rsa -p ポート番号" -ahvnz root@◇◇.vs.sakura.ne.jp:/var/www/* /var/www/html/
Warning: Identity file L:\0_Establishment\sakura_vps_keys\id_rsa not accessible: No such file or directory.
Permission denied (publickey).
rsync: connection unexpectedly closed (0 bytes received so far) [receiver]
rsync error: unexplained error (code 255) at io.c(600) [receiver=3.0.6]


前提
・旧サーバと新サーバに同じ公開鍵があり、それに対する秘密鍵がクライアントPCにある

勘違いしていた
・旧サーバのCentOSにsshでログインして、クライアントPCの秘密鍵を指定できると思っていた

実際は?
・クライアントPCにある秘密鍵を旧サーバーに上げた上で指定する必要がある
・それだと秘密鍵と公開鍵が揃うからセキュリティ的に良くないよね
・そんなんやったら、「公開鍵しかアクセスできない」設定を元に戻すわ
・明日は、試用期間を本使用にするかどうか決定する日なのに
・全然進まないわ!!!!!!!!!!!!!!!!!!!!!!!

二者間と三者間の想定点


下記は二者間だから成立している
・今回試したのは三者間だからこの方法では×
rsync時にパスワード認証をしたくないのだ

二者間
・サーバA(秘密鍵/rsync起点)
・サーバB

三者間
・クライアント(秘密鍵)
・サーバA(rsync起点)
・サーバB

パスワード認証許可するよう新サーバ設定変更


$ sudo vi /etc/ssh/sshd_config
#PasswordAuthentication no
PasswordAuthentication yes

$ sudo systemctl restart sshd.service


AサーバのCentOSにある/var/www/を、BサーバのCentOSにある/var/www/へコピーしたいのですが

rsyncオプション


Saba note
rsync の使い方、オプションについてのまとめ
rsyncオプション
大きなファイルを転送するときのrsyncオプション 【scpは使ってはいけない】 

旧サーバよりrsync実行(失敗 → 成功)

 閲覧数:141 投稿日:2018-03-21 更新日:2018-03-21 
何か間違えたっぽい
・でも「htmlディレクトリ」
・どこにも作成されていない
・どういうこと?
$ sudo rsync -e "ssh -p ◆◆" -ahvnz root@◇◇.vs.sakura.ne.jp:/var/www/* /var/www/html/
[sudo] password for ★★: 
root@◇◇.vs.sakura.ne.jp's password:
Permission denied, please try again.
root@◇◇.vs.sakura.ne.jp's password:
Permission denied, please try again.
root@◇◇.vs.sakura.ne.jp's password:
receiving incremental file list
created directory /var/www/html
cgi-bin/
html/
html/e1blue.work/
html/e1blue.work/c.e1blue.work/
html/e1blue.work/c.e1blue.work/index.php
html/w4c.work/
html/w4c.work/a.w4c.work/
html/w4c.work/a.w4c.work/index.php
html/w4c.work/b.w4c.work/
html/w4c.work/b.w4c.work/index.php

sent 48 bytes  received 352 bytes  5.76 bytes/sec
total size is 58  speedup is 0.14 (DRY RUN)



23:33
$ sudo rsync -e "ssh -p ポート番号" -ahvnz /home/★★/mysqlallbackup20180320.dump root@◇◇.vs.sakura.ne.jp:/home/★★/mysqlallbackup20180320.dump
[sudo] password for ★★: 
root@◇◇.vs.sakura.ne.jp's password:
sending incremental file list
mysqlallbackup20180320.dump

sent 82 bytes  received 15 bytes  12.93 bytes/sec
total size is 1.13G  speedup is 11649424.81 (DRY RUN)



いつまで経ってもファイルコピーされない
・「sending incremental file list」ってどういうことやねん
・なんでファイル送らないの?
・rsync超分からない!!!!!!!!!!!!
・オプション多過ぎる
・なんでファイルコピーでネット検索したら、「同期」とか「差分」とか(のページが)出てくんねん。そんなん(複雑な機能情報、今は)いらんねん!

0:04
$ sudo rsync -e "ssh -p ポート番号" -avz /home/★★/mysqlallbackup20180320.dump root@◇◇.vs.sakura.ne.jp:/home/★★/mysqlallbackup20180320.dump
                     
[sudo] password for ★★: 
root@◇◇.vs.sakura.ne.jp's password:
sending incremental file list
mysqlallbackup20180320.dump

sent 173984517 bytes  received 31 bytes  2433350.32 bytes/sec
total size is 1129994207  speedup is 6.49


0:07
・コピーされた
・イケるやん!

違いはなんやねん!


騙されたわ
・鵜呑みにした私が未熟なのだろうけど
・もう日付変わってるやんけ
   
×                                       
-ahvnz
 

-avz

原因判明


nオプション
・-n --dry-run 試験モード。実際には動作せず、動作内容だけ表示する(「-v」や「--stats」を一緒に指定する)
・実際には動作しないのかよ
・何だよそれ
・検索結果上位に表示されるサイトでは「対象ファイルを確認する」としか書かれていないから、勘違いしたよ
・でも、まあこれで良かったのかも
・最初のrscncでは、「ローカル → リモート」のつもりが「リモート→ローカル」になっていたから
・既存データ飛んだら洒落にならんで
・それにしても、nオプションは「実際には動作せず、動作内容だけ表示する」のに「created directory /var/www/html」とか、紛らわしいにも程があるやろ

「DRY RUN」英語の意味調べたわ
・予行演習という意味なのね
・初めて聞いたわ
・また一つ賢くなったよ

rsync怖いなと思い、失敗で検索
・「DRY RUN」の使い方と重要性を認識した
rsync --delete で泣かないために

・ちなみに、rsyncでコピーした1GBのMySQLダンプデータをローカルで確認しようとFTPダウンロードしたら、まだ終わらない
・残り時間が1時間以上とか
・今、0:23
・もう寝たいんやけど




新サーバにおいて、mysqldumpデータインポート(失敗)

 閲覧数:141 投稿日:2018-03-21 更新日:2018-03-25 
$ sudo yum -y install pv
読み込んだプラグイン:fastestmirror, langpacks
base                                                                                                                                  | 3.6 kB  00:00:00    
epel/x86_64/metalink                                                                                                                  | 4.8 kB  00:00:00    
epel                                                                                                                                  | 4.7 kB  00:00:00    
extras                                                                                                                                | 3.4 kB  00:00:00    
mysql-connectors-community                                                                                                            | 2.5 kB  00:00:00    
mysql-tools-community                                                                                                                 | 2.5 kB  00:00:00    
mysql57-community                                                                                                                     | 2.5 kB  00:00:00    
nginx                                                                                                                                 | 2.9 kB  00:00:00    
remi-safe                                                                                                                             | 2.9 kB  00:00:00    
updates                                                                                                                               | 3.4 kB  00:00:00    
(1/4): epel/x86_64/updateinfo                                                                                                         | 905 kB  00:00:01    
(2/4): updates/7/x86_64/primary_db                                                                                                    | 6.9 MB  00:00:01    
(3/4): epel/x86_64/primary_db                                                                                                         | 6.3 MB  00:00:01    
(4/4): remi-safe/primary_db                                                                                                           | 1.2 MB  00:00:01    
Determining fastest mirrors
* base: ftp.iij.ad.jp
* epel: ftp.tsukuba.wide.ad.jp
* extras: ftp.iij.ad.jp
* remi-safe: mirrors.tuna.tsinghua.edu.cn
* updates: ftp.iij.ad.jp
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ pv.x86_64 0:1.4.6-1.el7 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

=============================================================================================================================================================
Package                          アーキテクチャー                     バージョン                                   リポジトリー                        容量
=============================================================================================================================================================
インストール中:
pv                               x86_64                               1.4.6-1.el7                                  epel                                47 k

トランザクションの要約
=============================================================================================================================================================
インストール  1 パッケージ

総ダウンロード容量: 47 k
インストール容量: 93 k
Downloading packages:
pv-1.4.6-1.el7.x86_64.rpm                                                                                                             |  47 kB  00:00:02    
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 インストール中          : pv-1.4.6-1.el7.x86_64                                                                                                        1/1
 検証中                  : pv-1.4.6-1.el7.x86_64                                                                                                        1/1

インストール:
 pv.x86_64 0:1.4.6-1.el7                                                                                                                                    

完了しました!


$ ls -la
合計 1103544
drwx------  5 ★★ ★★       4096  3月 21 00:05 .
drwxr-xr-x. 4 root       root               37  3月  9 12:15 ..
-rw-------  1 ★★ ★★       7863  3月 21 00:43 .bash_history
-rw-r--r--  1 ★★ ★★         18  8月  3  2016 .bash_logout
-rw-r--r--  1 ★★ ★★        193  8月  3  2016 .bash_profile
-rw-r--r--  1 ★★ ★★        231  8月  3  2016 .bashrc
drwxrwxr-x  3 ★★ ★★         17  3月  9 12:15 .cache
drwxrwxr-x  3 ★★ ★★         17  3月  9 12:15 .config
-rw-------  1 ★★ ★★       1815  3月 20 00:52 .mysql_history
drwx------  2 ★★ ★★         28  3月  9 12:38 .ssh
-rw-------  1 ★★ ★★        902  3月  9 12:38 .viminfo
-rw-rw-r--  1 ★★ ★★ 1129994207  3月 20 20:12 mysqlallbackup20180320.dump


mysqldumpデータインポート失敗


一瞬で終わった早過ぎる、と思っていたら
・エクスポートしていた
・ファイルサイズ「1129994207」→「817162」。上書きされている
・旧サーバより、mysqldumpデータrsyncするところからやり直し
$ sudo mysqldump -u root -p -h localhost -A > mysqlallbackup20180320.dump
Enter password:
$ ls -la          
                                                                                                             
合計 832
drwx------  5 dotinstall dotinstall   4096  3月 21 00:05 .
drwxr-xr-x. 4 root       root           37  3月  9 12:15 ..
-rw-------  1 dotinstall dotinstall   7863  3月 21 00:43 .bash_history
-rw-r--r--  1 dotinstall dotinstall     18  8月  3  2016 .bash_logout
-rw-r--r--  1 dotinstall dotinstall    193  8月  3  2016 .bash_profile
-rw-r--r--  1 dotinstall dotinstall    231  8月  3  2016 .bashrc
drwxrwxr-x  3 dotinstall dotinstall     17  3月  9 12:15 .cache
drwxrwxr-x  3 dotinstall dotinstall     17  3月  9 12:15 .config
-rw-------  1 dotinstall dotinstall   1815  3月 20 00:52 .mysql_history
drwx------  2 dotinstall dotinstall     28  3月  9 12:38 .ssh
-rw-------  1 dotinstall dotinstall    902  3月  9 12:38 .viminfo
-rw-rw-r--  1 dotinstall dotinstall 817162  3月 21 13:59 mysqlallbackup20180320.dump



mysqldumpリカバリの進捗を表示する
DBAのためのmysqldumpのtips 25選
MySQL5.7からMySQL5.7へ、全DBユーザーを移行したい
rsync -ahvnz root@ホスト名:/var/www/* /var/www/html/

迷走履歴

 閲覧数:129 投稿日:2018-03-21 更新日:2018-03-21 

旧サーバより、再度rsync実行


$ sudo rsync -e "ssh -p ポート番号" -avz /home/★★/mysqlallbackup20180320.dump root@◇◇.vs.sakura.ne.jp:/home/★★/mysqlallbackup20180320.dump
[sudo] password for ★★: 
root@◇◇.vs.sakura.ne.jp's password:
sending incremental file list
mysqlallbackup20180320.dump

sent 173974192 bytes  received 5509 bytes  2108844.86 bytes/sec
total size is 1129994207  speedup is 6.49


新サーバ


$ ls -la
                                                                                                                       
合計 1103544
drwx------  5 ★★ ★★       4096  3月 21 14:22 .
drwxr-xr-x. 4 root       root               37  3月  9 12:15 ..
-rw-------  1 ★★ ★★       7863  3月 21 00:43 .bash_history
-rw-r--r--  1 ★★ ★★         18  8月  3  2016 .bash_logout
-rw-r--r--  1 ★★ ★★        193  8月  3  2016 .bash_profile
-rw-r--r--  1 ★★ ★★        231  8月  3  2016 .bashrc
drwxrwxr-x  3 ★★ ★★         17  3月  9 12:15 .cache
drwxrwxr-x  3 ★★ ★★         17  3月  9 12:15 .config
-rw-------  1 ★★ ★★       1815  3月 20 00:52 .mysql_history
drwx------  2 ★★ ★★         28  3月  9 12:38 .ssh
-rw-------  1 ★★ ★★        902  3月  9 12:38 .viminfo
-rw-rw-r--  1 ★★ ★★ 1129994207  3月 20 20:12 mysqlallbackup20180320.dump


$ sudo mysqldump -u root -p < mysqlallbackup20180320.dump
[sudo] password for ★★: 
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help


新サーバにおいて、mysqldumpデータインポート(成功)

 閲覧数:133 投稿日:2018-03-21 更新日:2018-03-24 
$ sudo pv mysqlallbackup20180320.dump | mysql -h localhost -u root -p
Enter password: 
sudo pv mysqlallbackup20180320.dump | mysql -h localhost -u root -p 879MiB 0:02:33 [1.49MiB/s] [=============================================================1.05GiB 0:04:06 [4.37MiB/s] [=============================================================================================================>] 100%            
$      


確認


phpMyAdminへ反映させるためには?
・左上項目の一番右にある更新アイコンクリックが必要

「mysqldump -u root -p --all-databases」出力結果をimportしたことによる影響

 閲覧数:154 投稿日:2018-03-21 更新日:2018-03-21 

「mysqldump -u root -p --all-databases」出力結果をimportしたらどうなるの?


事前に作成したユーザデータ□□がどうなったか、確認してみた
・mysqlテーブル
SELECT * FROM `user`

いないことが判明

・旧サーバの全権ユーザならphpMyAdminへ入れるが、「mysqldump -u root -p --all-databases」出力結果をimportする前に新サーバで作成した全権ユーザ□□は消滅してしまった
・その結果、config.inc.php内容との間に乖離が生じている
mysqli_real_connect(): (HY000/1045): Access denied for user '□□'@'localhost' (using password: YES)
設定ファイルに定義されている管理ユーザ(controluser)での接続に失敗しました。



しょうがないので、全権ユーザ□□を新サーバにて再度作成
GRANT ALL PRIVILEGES ON *.* TO □□@localhost IDENTIFIED BY 'パスワード';  



8回目-16.phpMyAdmin 4.7.9 インストール&設定



週間人気ページランキング / 9-14 → 9-20
順位 ページタイトル抜粋 アクセス数
1 PHPのmb_send_mail関数でメール送信できない | メール処理システム 29
2 Nginx設定。エラーログレベル | Nginx(Webサーバ) 21
3 Python 3.5 アンインストール / yum remove | Python(プログラミング言語) 11
4 FFmpeg 2.8.15 を yum インストール | ソフトウェアスイート 10
5 PHP実行ユーザ設定 / CentOS6 / Apache | PHP(プログラミング言語) 9
6 PHP Version 7.1.2 php-mecabエクステンション対応 / PHP 7.0.14 からのアップグレード  | MeCab(形態素解析) 8
6 ソースからビルドしたPython 2.7.3 アンインストール失敗 | Python(プログラミング言語) 8
7 touch コマンド / viコマンド。新規ファイル作成時の違い | Linuxコマンド 7
7 さくらVPS0 7
8 9回目-13.MySQL5.7.21設定 | CentOS 7 2週間無料のお試し期間 9回目(さくらVPS) 6
8 「設定ファイルに、暗号化 (blowfish_secret) 用の非公開パスフレーズの設定を必要とするようになりました。」対応 6
9 PHPファイルでchmodエラー | PHP(プログラミング言語) 5
9 設定 2019/1/22 / 一般ユーザがmailコマンドでメール送信 / 管理者がmailコマンドでメール送信 5
10 Postfix | メール処理システム 4
10 CentOSでcpコマンド動作確認するためには、Control + T ではなく、 -v オプションを使用 | cp(Linuxコマンド) 4
10 499 (Request has been forbidden by antivirus) | HTTP(通信プロトコル) 4
10 tar | Linuxコマンド 4
10 Python 3.6 インストール / make altinstall | Python(プログラミング言語) 4
10 「CentOS6」から「CentOS7」への移行 | CentOS 7 (CentOS) 4
10 echo と cat の違い 4
2021/9/21 1:01 更新