MySQL」タグアーカイブ

MySQL Workbench設定

ローカル

ローカルにある別PCの場合、先ずHost側でユーザの追加が必要。

+マークを押して、Hostnameを該当ipに直し、Username、Passwordを入力するだけ。

Xserver

Xserver側でMySQLとsshの設定が必要。

Connection Method: Standart TCP/IP over SSH
SSH Hostname: xxx.xserver.jp:10022
SSH Username: SSHユーザ
SSH Password: SSHパスワード
SSH Key File: ローカルのパス
MySQL Hostname: mysqlxx.xserver.jp
MySQL Server Port: 3306
Username: DBユーザ
Password: DBパスワード

VBA C# MySQLに接続する基本

VBA

MySQL側でroot@192.168.1.%のような外部ホストから接続可能なユーザーを作成しておく。

http://dev.mysql.com/downloads/connector/odbc/
からmsiファイルでインストール。

コントロールパネル > データソース > ユーザー DSN > 追加

ユーザー DSNを利用してVBAでの接続の基本。

エクセル側の設定

ツール > 参照設定
Microsoft ActiveX Data Objects x.x Library

ユーザー DSNを利用しない場合は直接DSNをコードに書いておく。
dsn = “Driver={MySQL ODBC 5.3 Unicode Driver}; SERVER=192.168.1.4; DATABASE=db;USER=root; PASSWORD=1234;”

ちなみに

こんな感じ

C#

http://dev.mysql.com/downloads/connector/net/
Connector/Net 6.9.8を保存し、typicalでインストール。

参照設定で、
MySQL.Data
というライブラリを追加。

using MySql.Data.MySqlClient
の追加。

MySQL ストアド

CREATE ROUTINEの権限が必要。

基本

create procedure sample01()

select now();

mysql>call sample01;

削除

mysql>drop procedure sample01;

一覧

show procedure status;

中身

show create procedure sample01;

引数をとる

create procedure sample01(in a int, in b int)

select a + b;

mysql>call sample01(10,5);

返り値

create procedure sample01(out x int)

set x = 3;

mysql> call sample01(@var);

mysql> select @var;

複数のクエリを発行

delimiter //

create procedure sample01()

begin

select 1;

select 2;

end//

delimiter ;

mysql> call sample01;

IF文

delimiter //

create procedure sample02(in x int)

begin

if x = 1 then

select “inp 1”;

elseif x = 2 then

select “int 2”;

else

select “inp else”;

end if

end //

delimiter ;

call sample02(1);

select~into文で変数へ

delimiter //

create procedure p(in a int, out sum int)

begin

select a * 2 into @tmp;

set sum = @tmp;

end //

delimiter ;

call p (4,@sum);

select @sum;

カーソル単位で処理する

declare xxx cursor という形で宣言。

for select でselectの結果を定義。

fetch cursor for カラムで代入。

処理前にopenして最後にcloseする。

delimiter //

create procedure p(in i_id int, in i_str varchar(10), out o_id int, out o_str varchar(10))

begin

declare myid int;

declare mystr varchar(10);

declare mycur cursor for select id,str from test_tbl; 

set @pos = 0;

select count(*) into @total from test_tbl;

set o_id = 0;

set o_str = ”;

open mycur;

while @total > @pos do

fetch mycur into myid,mystr;

if myid = i_id or mystr = i_str then

set o_id = myid;

set o_str = mystr;

set @pos = @total;

end if;

set @pos = @pos + 1;

end while;

close mycur;

end //

delimiter ;

mysql> CALL p( 4, ‘cc’, @id, @str );

mysql> SELECT @id;

MySQLをWindowsにインストール + mysqldumpでバックアップ + Xampp設定

インストール

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

ここからはworkbenchでもOK

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));
のように指定。