Ubuntu(Indigo) 初期設定

ローカルにあるSQLiteをIndigo上のMySQLにエクスポートする実験。

SSH接続

公開鍵方式でのSSH接続、OSでUbuntuを選択すると、自然とユーザー名はubuntuとなる模様。

ちなみに、コンソールではなく自分でSSH設定する場合。

TeraTerm>設定>SSH鍵生成
公開鍵(id_rsa.pub)、秘密鍵(id_rsa)を保存する。

サーバーホームディレクトリ>.ssh>authorized_keys
の中にid_rsa.pubの中身を貼り付け。
(他に書き込みがある場合、新しい行でも大丈夫)

ufw

最初は起動されていない

sshを許可
sudo ufw allow 22/tcp;
あるいは、
sudo ufw allow ssh;

一度だけ起動なら、
sudo ufw enable;
自動起動なら、
sudo systemctl enable ufw
あるいは、
sudo /etc/init.d/ufw start

再起動
sudo ufw reload;

状態
sudo ufw status;

MySQL

インストール
sudo apt-get install mysql-server

バージョン確認
sudo mysql –version

my.cnfを探す
sudo mysql –help | grep my.cnf
今回は
/etc/mysql/mysql.conf.d/mysqld.cnf

bind-address(ここで指定したIPのみ接続を受け付ける)をコメントアウト。接続するIPがわかる場合は指定しても大丈夫だが、複数のIPは指定できない。

文字コード確認
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/ 

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作成
create database db character set utf8;

ユーザー作成
mysql > create user user@host identified by ‘1234’;
ちなみに、auth_socket認証とする場合、
mysql > create user user@host identified with auth_socket;

MySQLのrootアカウントは標準でauth_socket認証しており、パスワード設定しても、パスワード無しでログインできる。ここで少しつまづいた。

権限設定(ユーザー作成)
mysql > grant select,insert,update,delete on db.table to user@host
ちなみに、ユーザーが存在しない場合作成されるので、
grant select,insert,update,delete on db.table to user@host identified by ‘1234’;
とすればパスワード付きで作成される。

権限設定(ユーザー作成)をシェルからワンライナー
pass=$(head /dev/urandom | tr -dc a-km-np-z1-9 | head -c 13); sudo mysql -u root -e “grant select,insert,update,delete on db.table to user@host identified by ‘${pass}'”; echo $pass;

権限確認
mysql > show grants for user@host;

権限削除
mysql > revoke all, grant option from user@host;

パスワード設定(変更)
mysql > alter user user@host identified by ‘2345’;
あるいは、
mysql > update mysql.user set authentication_string=password(‘3456′) where user=’user’;

パスワード設定をシェルからワンライナーの場合、
pass=$(head /dev/urandom | tr -dc a-km-np-z1-9 | head -c 13); sudo mysql -u root -e “alter user user@host identified by ‘${pass}'”; echo $pass;

アカウント情報確認
mysql > select host, user, authentication_string, plugin from mysql.user;

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

データディレクトリから、
client-cert.pem
client-key.pem
ca.pem (必要なら)
をダウンロードする。

ユーザー状態確認
select user,host,ssl_type from mysql.user (anyはssl必須)
show grants for app_user@’%’;
select * from mysql.user where user=’app_user’ \G;

全ての権限を削除
revoke all, grant option from app_user@’%’;

権限追加
grant all on *.* to app_user@’%’ identified by ‘123’ require X509; (SSL / X509)

SSL
暗号化接続はサーバーのcert,keyのみなので、クライアントのcert,keyは指定しなくても大丈夫。

X509
クライアントを証明する必要があるためクライアントにcert,keyを指定する必要がある。

ここの指定とは別にクライアント側からもオプションがある。

・DISABLED
・PREFERRED (default)
サーバが暗号化接続をサポートしている場合は暗号化接続。接続できない場合は通常の接続。
・REQUIRED
サーバー認証を行なって接続する。
・VERIFY_CA
サーバー認証とクライアント認証を行って接続する。

MySQL 外部からの接続

接続もとが固定IPならいいが、そうではない場合、MySQLのbind-addressは複数指定できず、ユーザーのホスト部を頻繁に変更するのも手間なのでufwで弾くのが簡単。

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アドレスを指定する。

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 = “”;
このようにした。