IndigoにUbuntuを設定＋ローカルにあるSQLiteをIndigo上のMySQLにエクスポートする実験。
インスタンス生成
ダッシュボード＞インスタンス管理＞インスタンス
からインスタンス生成し、起動する。
表示されているIPアドレスにTeraTermから接続
TeraTerm 起動マクロ
SSH接続
公開鍵方式でのSSH接続、OSでUbuntuを選択すると、自然とユーザー名はubuntuとなる。
公開鍵で、TeraTermとWinSCPで接続できるように設定する。
Ubuntu セキュリティ関連設定
MySQL
・インストール
sudo apt install mysql-server
(インストール後、起動＋自動起動するようになっている)
・バージョン確認
sudo mysql –version
・起動、他
sudo systemctl (start|stop|restart|status) mysql
・my.cnfを探す
sudo mysql –help | grep my.cnf
今回は
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
・(外部から接続する場合)bind-addressをコメントアウト。
ここで指定したIPアドレスのみ接続を受け付ける。接続元のIPアドレスがわかる場合は指定しても大丈夫だが、複数のIPアドレスは指定できない。
(my.cnfを変更した場合、再起動)
MySQLのrootアカウントは標準でauth_socket認証しており、パスワード設定しても、パスワード無しでログインできる。ここで少しつまづいた。
(sudo mysql -u rootとすれば入れる)
・文字コード確認
sudo mysql -u root
show variables like ‘char%’;
|
|
character_set_client | utf8 character_set_connection | utf8 character_set_database | latin1 character_set_filesystem | binary character_set_results | utf8 character_set_server | latin1 character_set_system | utf8 character_sets_dir | /usr/share/mysql/charsets/ |
・文字コード変更
今は最初からutf8mb4になっている。
もしなっていない場合、mysqld.cnfに下記を追加する。
|
|
[mysqld] character-set-server = utf8 |
これで、utf8に変わる。
|
|
character_set_client | utf8 character_set_connection | utf8 character_set_database | utf8 character_set_filesystem | binary character_set_results | utf8 character_set_server | utf8 character_set_system | utf8 character_sets_dir | /usr/share/mysql/charsets/ |
・DB作成
sudo mysql -u root
create database check_sheet character set utf8mb4;
・ユーザー作成
(外部から接続する場合ホストは%としておく)
create user ‘app_user’@’%’;
※同時にパスワードを設定する場合、
create user ‘app_user’@’%’ identified by ‘1234’;
auth_socket認証とする場合、
create user user@host identified with auth_socket;
・ユーザー削除
drop user ‘app_user’@’%’;
・ユーザー一覧
select user, host, authentication_string, plugin from mysql.user;
・権限設定
grant create,drop,alter,select,insert,update,delete on check_sheet.* to ‘app_user’@’%’;
これを実行しないと作成したユーザーでDBが操作できない。
・権限確認
show grants for ‘app_user’@’%’;
・権限削除
revoke all, grant option from ‘app_user’@’%’;
・パスワード設定
シェルからワンライナーの場合
pass=$(head /dev/urandom | tr -dc a-km-np-z1-9 | head -c 13); sudo mysql -u root -e “alter user ‘app_user’@’%’ identified by ‘${pass}'”; echo $pass;
あるいは、
alter user ‘app_user’@’%’ identified by ‘2345’;
set password for ‘app_user’@’%’ = ‘2345’;
MySQL SSL接続
・状態確認
show variables like ‘%ssl%’;
|
|
have_openssl | YES have_ssl | YES ssl_ca | ca.pem ssl_capath ssl_cert | server-cert.pem ssl_cipher ssl_crl ssl_crlpath ssl_key |
初回起動時に、mysql_ssl_rsa_setup()が実行され、このようにSSLが有効になっていた。MySQLのデータディレクトリ以下に生成される。
ca.pem：自己署名CA証明書
ca-key.pem：CA秘密鍵
server-cert.pem：サーバ用公開鍵
server-key.pem：サーバ用秘密鍵
client-cert.pem：クライアント用公開鍵
client-key.pem：クライアント用秘密鍵
private_key.pem：caching_sha2_password, sha256_password用秘密鍵
public_key.pem：caching_sha2_password, sha256_password用公開鍵
・データディレクトリ確認
show variables like ‘%datadir%’;
(後のコピーでも必要なので、ここで実行)
permission deniedとなるので、
sudo su
を実行する。
・データディレクトリに移動
cd /var/lib/mysql
・コピー＋パーミッション変更
データディレクトリから、
client-cert.pem
client-key.pem
ca.pem (必要なら)
をダウンロードするため、先ずホームディレクトリにコピーする。
(これらのファイルはWorkbechで利用するけど、もしC#だけなら、そのまま変換するのでダウンロード不要)
今回は/var/lib/mysql/にあったので、コピーする。
cp /var/lib/mysql/client-cert.pem /home/ubuntu/client-cert.pem;
cp /var/lib/mysql/client-key.pem /home/ubuntu/client-key.pem;
cp /var/lib/mysql/ca.pem /home/ubuntu/ca.pem
・パーミッション変更
ダウンロードするのにパーミッション変更が必要なので、ホームディレクトリ(コピー先)に移動し、
chmod go+r ca.pem;
chmod go+r client-key.pem;
chmod go+r client-cert.pem
のようにする。
これでWinSCPなどでダウンロードできる。
・ユーザー戻す
su ubuntu
・SSLのユーザー状態確認
select user,host,ssl_type from mysql.user;
ssl_typeの部分を確認する。
・権限追加
alter user ‘app_user’@’%’ require X509;
・全ての権限を削除
revoke all, grant option from ‘app_user’@’%’;
requireで指定するオプションについて
・SSL
暗号化接続はサーバーのcert,keyのみなので、クライアントのcert,keyは指定しなくても大丈夫。
・X509
クライアントを証明する必要があるためクライアントにcert,keyを指定する必要がある。
ここの指定とは別にクライアント側からもオプションがある。
・DISABLED
・PREFERRED (default)
サーバが暗号化接続をサポートしている場合は暗号化接続。接続できない場合は通常の接続。
・REQUIRED
サーバー認証を行なって接続する。
・VERIFY_CA
サーバー認証とクライアント認証を行って接続する。
MySQL 外部からの接続
IPアドレスの指定がない、またはIPアドレスの指定をしたいが固定ではない場合、MySQLのbind-addressは複数指定できず、ユーザーのホスト部を頻繁に変更するのも手間なのでufwで弾くのが簡単。
sudo ufw allow 3306/tcp
sudo ufw reload
もしIPアドレスを指定したい場合
sudo ufw allow from IPアドレス to any port 3306 proto tcp
これで、特定のIPのみMySQLに接続できる。
IPアドレスが変更になったら、
sudo ufw status numbered
でNoを確認し、
sudo ufw delete 番号
(複数不可)
で該当ルールを削除し、再度、
sudo ufw allow from IPアドレス to any port 3306 proto tcp
で変更後のIPアドレスを指定する。
Apache+PHP
今回は利用しない。
Ubuntu Apache(PHP+MySQL) 設定
クライアント設定
GCP Cloud SQL(MySQL) 使い方
SQLiteをMySQLへエクスポート
とりあえず動かすためだけの設定で実際は細かく調整する必要がある
DB Browser for SQLiteにて、
ファイル＞エクスポート＞データベースをSQLファイルへ
・insert into にカラム名を保持
・insert文に複数行(VALUES)
にチェック。
・すべてをエクスポート
・古いスキーマを上書き(DROP TABLE した後に CREATE TABLE)
を選択して保存。(DROPしないと何度か実行するときに作成済みのテーブルが残ったりする)
それぞれデータの内容によって違うが今回は書き出したSQLファイルを下記の通り修正。
・先頭のBIGIN TRANSACTIONをSTART TRANSACTIONに変更。
・全てのダブルクォーテーションの削除。
テーブル名やカラム名がダブルクォーテーションで囲われているため。
・CREATE TABLEで型が抜けている部分にtextを追加。
・全ての型をtextに変更。
insertの部分でint等も空の部分がシングルクォーテーションになっているため
・PRIMARY KEY()を削除。
上の行末のカンマも削除
ファイルサイズが大きいので、WinSCPでアップロードし、
mysql -u root -p db名 < SQLファイル.sql
として実行。
※ちなみに、エクスポートは、
mysqldump –single-transaction -u ユーザ名 DB名 > 出力先ファイル名
Workbenchの場合、
Server＞Data Import＞Import from Self-Contained File
Default Target Schemaからデータベース名を選択しStart Importを実行。
ERROR 2006 (HY000) at line 1820: MySQL server has gone away
Operation failed with exitcode 1
というエラーがでた場合、
set global max_allowed_packet = 16777216;
(一時的)
インポート後にカラム1つずつデータ型を変更する。
alter table table_name modify column_name int not null primary key auto_increment;
のように処理し、
intに関しては、SQLiteで空文字が入ってしまっているとエラーがでるので、
update table_name set column_name = null where column_name = “”;
このようにした。