MySQL ダンプデータから行単位の抜き出し

間違えて削除した行を復旧するため、ダンプのバックアップデータから特定の行を抜き出すことになった。

条件が簡単なら、mysqldumpだけでもできる。

必要ならダンプデータを別環境にインポートし、
mysqldump -t -u user –where “xxx = xxx” database_name table_name > dump.sql

そして、書き出したファイルをインポート。

***

今回は子テーブルも削除されていてSQLを使いたいので、新しいテーブルにデータをコピーしそのテーブルを書き出すことに。

・(必要なら)ダンプデータを別環境にインポート

・スキーマコピー
create table AA like A

・データコピー
insert into AA select * from A where exists (
select * from B where A.ID = B.ID);
このような感じ(AテーブルをAAテーブルに書き出す時、Bテーブルに特定の値が含まれているかどうか)

・データ書き出し
mysqldump -t -u user database_name tmp_tbl > dump.sql
(Workbenchなら、Server>DataExportから)

・書き出したファイルでテーブル名などを修正してインポート。

MySQL バックアップスクリプト

・cron

/etc/cron.d
の中に、
適当な名前でファイル作成、今回は
mysql_backup
とする。

所有者rootで、パーミッションは644

中には、
* 23 * * * root /home/ubuntu/backup.sh
と記入する。

起動しているかどうか
sudo systemctl start/stop cron

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

 

 

 

Windows上にサーバー作成 Nginx/PHP/MariaDB/WordPress

以前は必要になるとXamppを使ってたけど、今回はNginx環境を手動で作ってみる。

Nginx

Windows用のzipファイルをダウンロードし解凍する。設定ファイルはもともとのコメント部を消して、とりあえずこんな感じ。

nginx-1.19.2/conf/nginx.conf

Nginxフォルダのパスに日本語があると起動しない場合があるので注意。

PHP

ダウンロードして解凍したら、phpというフォルダでnginxの中に入れる。
(nginx-1.19.2/php)

php.ini-development
をコピーしてphp.iniにし、
extension_dir=”ext”
extension=php_mysqli.dll
を追記する。

MariaDB

ダウンロードして解凍したら、mariadbというフォルダでnginxの中に入れる。
(nginx-1.19.2/mariadb)

nginx-1.19.2/mariadb/bin
の中にある、mysql_install_db.exeを実行すると、
nginx-1.19.2/mariadb/data
が作成され、my.iniも入っている。
(Nginxフォルダを移動したらmy.iniを修正しないと起動しない)

手動でDB作成等に使う場合、まずbinの中のmysqld.exeを起動。次にコマンドプロンプトからmysql -u root のような感じでログインできる。

・設定
nginx-1.19.2/mariadb/bin
に移動しmysqldを起動後、
mysql -u root
とするとログインできる。

mysql> set password = password(“1234”);

・データベース作成
mysql> create database wpd character set utf8;
(utf8を指定しないとWPのインストールでエラー)

起動・終了バッチ

start.bat

my.iniを自動で書き換える場合このような感じ

stop.bat

あるいは全て強制終了
(こうしないとプロセスが残る場合あり)

mysql.bat

mysqlのディレクトリに移動するためだけのバッチ

WordPress

nginx-1.19.2\html\wp
にWordPressを解凍したので、ブラウザから、
http://localhost/wp

にアクセス。

DBの情報を入れて送信したところ、
サイトに重大なエラーがありました。
と表示されエラーになった。
(1度目のインストール)

・DBをリセット
フォルダを手動で削除せず、mysqlのコマンドからdrop databaseする。
そして再度create databaseする。

・wp-config.phpを削除
wp-config-sample.phpを複写し、名前をwp-config.phpに変更する。

wp-config.phpが存在しないと自動でwp-config.phpは作成されるが、ここでは手動でwp-config.phpを設定する。

wp-config.phpの以下の部分をtrueに変更する。
define( ‘WP_DEBUG’, true);

以下の部分にDB情報を記入する。
define( ‘DB_NAME’, ‘wpd’ );
define( ‘DB_USER’, ‘root’ );
define( ‘DB_PASSWORD’, ‘1234’ );

再度挑戦するが、
サイトに重大なエラーがありました。
と表示されるだけで、特にエラー内容は表示されない。
(2度目のインストール)

Nginxのログ(logs/error.log)
を確認すると、
Fatal error: Maximum execution time of 30 seconds exceeded
とあるので、
php.iniで下記の部分を30から120に変更
max_execution_time = 120
(3度目のインストール)

これでインストールできた。

LAN経由(外部)から見る場合、ファイアーウォールに新しい規則を追加する。
もしCSSが適用されていない場合は、
設定>一般>アドレス
をIPアドレスに変更してみる。

MySQLをWindowsにインストール

インストール

MySQLのサイトで、
MySQL Community Edition → MySQL on Windows (Installer & Tools) → MySQL Installer
を選択して保存。

インストール時、
Server onlyを選択。Developer Machineを選択。

ポートはそのまま3306で変更せず。パスワードはとりあえず1234。

初期設定

別のPCがクライアントになるため、rootのホスト名を変える。ホスト名が違うと別ユーザー扱い。

パスが通っていないので、mysqlのフォルダから
mysql -u root -p1234
と入力して起動する。

rename user ユーザ名@ホスト名 to 新ユーザ名@新ホスト名
でできる。ただ、変えてしまってlocalhostを消すとlocalhostからアクセスできなくなってしまうので、
別に追加したほうがいい。今回はやらない。

ユーザーを追加する。
create user usr01 identified by ‘1234’;

確認。
select user,host from mysql.user;

ここではホストは%になっているのでネットワークを指定。(やらずにそのまま%でもOK)
rename user ‘usr01’@’%’ to ‘usr’@’192.168.11.%’;

もし、ユーザー作成時にパスを設定していない場合はパスのみ追加できる
set password for ‘usr01’@’192.168.11.%’ = password(‘xxx’);

権限の確認(現在のユーザ)
show grants;

あるいは、
show grants for ‘usr’@’192.168.11.%’
grant usage on *.* to ‘usr’@’192.168.102.%’となっている。(権限がない状態)

権限を付与する(指定ユーザがいないと新規で作成される。)
grant all privileges on *.* to ‘usr’@’192.168.11.%’;

Winでの再起動は管理者のプロンプトから、
net start/stop mysql57

MySQLのDB作成等

Xampp利用の場合

WindowsでPHPを利用する方法。ここではXamppを利用する。

ファイル・フォルダ

適当に解凍して、
setup_xampp.batをクリック。
xampp_start.exeをクリック。

今回は、
apache
htdocs
install
mysql(不要な消してしまっても大丈夫)
php
tmp
だけ残して、それ以外のフォルダは削除。

xampp-control.ini

[EnableModules]
で不要な箇所を0へ

ポート番号

動かない場合はだいたい、
ポートが使われていることが多い。SoftEtherなど。

\apache\conf\httpd.conf
Listen 81に変更

\apache\conf\extra\httpd-ssl.conf
Listen 441に変更

だいたいこの方法で直る。netstatで調べてみる。

基本的なバックアップ方法(mysqldump)

Winでバッチファイルをタスクリストで処理する場合。

cd C:\Users\Administrator\Desktop\xampp\mysql\bin
mysqldump -u root -p1234 db > C:\Users\Administrator\Desktop\backup.sql
pause

Xserver等でWordPressをバックアップの場合。

先ず、
mysql -h xxx -u xxx -pxxx
にてsshログインして、
mysqldump -u xxx -pxxx -h xxx db > xxx.sql
とする。ちなみに、構造のみの場合 -dオプション、WorkbechからならDump Structure Onlyを選択。

ローカル側では、先ずxxx.sqlを置換してパスを全て置き換える。
このような感じ。
http://okamurax.com
http://192.168.102.15/blog

その後、
create database xxx charset=utf8;
としてローカルのdbを作成して
mysql -u xxx -pxxx db < xxx.sql
としてローカルのdbに流し込む。

http://192.168.102.15/blog/wp-admin
にアクセスしてdb更新。

存在しないテーマなどは存在するものに変更。

ユーザはバックアップ元になるので注意
(ここではxserverのユーザになるということ)

実際のスクリプト

MySQL 基本SQL

MySQL関連基礎(主にLinux)

外部から接続したい場合、
hostの変更とconfファイルにbind-addressを追加する。

保存場所確認
show variables like ‘%dir%’;

my.cnfの位置
mysql –help | grep /my.cnf
左から順番。開いていくと/etc/mysql/my.cnf以外は空
my.cnfを開くと
/etc/mysq/conf.d/
/etc/mysql/mysql.conf.d/
の2つのディレクトリをインクルードしている。
ディレクトリの中に適当にファイルを作っていれておけばOK

ユーザー

ユーザーの作成
CREATE USER ‘test_usr’@’192.168.102.%’ IDENTIFIED BY  ‘passw0rd’;

ホスト名の変更
RENAME USER ‘root’@’localhost’ to ‘root’@’192.168.102.%’;

ユーザー一覧
select User,Host from mysql.user;

パスワード変更(ユーザー指定)
SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘56789’);

パスワード変更(ログインユーザー)
SET PASSWORD = PASSWORD(‘5678’);

データベース

現状の状況
STATUS;

データベース一覧
SHOW DATABASES;

データベースの作成
CREATE DATABASE test_db2 CHARACTER SET sjis;

データベース選択
USE test_db2

データベース削除
DROP DATABASE test_db;

テーブル

テーブル一覧
SHOW TABLES;

エンジンの確認
SHOW TABLE STATUS;

テーブルの作成
CREATE TABLE tbl (
id int PRIMARY KEY AUTO_INCREMENT NOT NULL
) DEFAULT CHARACTER SET sjis;

テーブルのリネーム
ALTER TABLE tbl RENAME tbl;

テーブルの複製
CREATE TABLE to_tbl LIKE from_tbl

カラム

列の追加
ALTER TABLE tbl ADD col VARCHAR

列の情報
SHOW COLUMNS FROM tbl;

列の変更
ALTER TABLE tbl CHANGE col col2 int;

列の削除
ALTER TABLE tbl DROP col2;

SELECT式の評価順

FROM
ON – JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP(LIMIT)

文字コード

有効な文字コード
SHOW CHARACTER SET;

データベースの文字コード
SHOW CREATE DATABASE test_db2;

文字コード(接続中のデータベース)
SHOW VARIABLES LIKE ‘char%’;

作成済みデータベースの文字コード変更
ALTER DATABASE db_name CHARACTER SET utf8;

或いは以下のようなコードで確認

文字コードの修正方法

例えば以下のような状態

コマンド、PHPから見るとエンコが合っていないのが分かる。

[mysqld]
character-set-server = utf8
を記述して一旦再起動。

sudo /etc/mysql/init.d/mysql restart

これで問題ないはず。

以前はmy.cnfには以下のように書いていた。

[mysqld]
character-set-server=utf8 //バージョンよって違う(以前はdefault-character-set)

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8
//全クライアントで共通の設定と書かれていたが、ここは書かなくても大丈夫のようだけで書いておいた。

[client]
default-character-set=utf8

character_set_client : クライアント側で発行したsql文はこの文字コードになる
character_set_connection : クライアントから受け取った文字をこの文字コードへ変換する
character_set_database : 現在参照しているDBの文字コード
character_set_results : クライアントへ送信する検索結果はこの文字コードになる
character_set_server : DB作成時のデフォルトの文字コード
character_set_system : システムの使用する文字セットで常にutf8が使用されている

ちなみに、
PDO(‘mysql:host=localhost;dbname=db;charset=utf8’, ‘root’, ‘5678’);
として、DSNでcharset=utf8と指定して、逆にmy.cnfに何も書かないと、

となる。

つまり、データベース作成、テーブル作成、DSNでも字コードを指定しても
結局serverは変わらないので、my.cnfは必要。
逆にmy.cnfを1行入れれば、PDOのDSNはなくてもOK。

Windowsの文字コード

create database時に、sjis指定すると、
character_set_client :utf8
character_set_connection:utf8
character_set_database:sjis
character_set_filesystem:binary
character_set_results:utf8
character_set_server:utf8
character_set_system:utf8

sjis指定なしでデータベースを作成すると、
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
となる。

my.iniは
C:\ProgramData\MySQL\MySQL Server 5.7
にある。

ちなみに、インストール時は

[client]
指定なし

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

[mysqldump]
指定なし

となっていた。

ちなみに、㈱など文字化けする場合、
[mysqld]
character-set-server=sjis
とすると直る。

Winでの再起動は
sevices.msc
から実行

制約の種類

NOT NULL
CHECK
UNIQUE KEY
PRIMARY KEY
FOREIGN KEY

追加方法

ALTER TABLE tbl ADD UNIQUE (key);

ちなみに、primaryとuniqueは
複数指定すると、複合になるのでuniqueをそれぞれ複数の列の場合、
create table tbl (id int ,nm varchar(5) , unique(id) , unique(nm));
のように指定。