「mySQL」一覧

[mySQL] Illegal mix of collations のエラー

2017/10/15 mySQL

Illegal mix of collationsのエラーが出た場合

異なる属性のデータをlikeに指定している可能性があります。

intSize lile ‘あああ’

属性を合わせた値をセットしましょう。

メルマガ登録をお願いします。

素敵な？情報がいつもあなたに届くようになります。（itmnewsの記事が届きます。）お役に立つかどうかわかりませんが頑張りますっ。登録してみてください。

[MySQL] TIMESTAMP型を明示的に自動更新させない方法

2017/8/27 2017/8/28 mySQL

TIMESTAMP型で属性が「on update CURRENT_TIMESTAMP」となっているフィールドは更新時に自動で最新の日付がセットされるので便利な時もありますが、処理の都合上、更新したくないときは、明示的に更新を避けるような

SQL文を組み込むことができます。

日付フィールド名 = 日付フィールド名　のように、現在のフィールド値にフィールド名を入れるようなupdate文を作り実行すればOKです。

例

update table_name set value = ‘12345’, startYmd = startYmd where id = ‘1234’;

メルマガ登録をお願いします。

[amazon クラウド]　RDS上のMySQLでリージョン間のレプリケーションを構築する

2015/6/20 2015/7/12 amazon クラウド, mySQL, データベース, 技術報告

2015-07-12前回、前々回とRDS内でのレプリケーションの仕組み、MySQLでのレプリケーションの仕組みををまとめましたが

それを踏まえて

リージョン間でレプリケーションしたらどうなるかをまとめてみました。

つまり更新系はマスターでないと他のスレーブに配ってくれないので、データは

マスターに行く。参照系は、各リージョンにあるRDSに繋げば、データ量が多いクエリも

マスターを見に行くより早いのは間違いない。

この方法による構築での課題点は

・参照系と更新系でデータベースの接続先が違う。

・MySQLのレプリケーションは非同期レプリケーションなので、タイミングによっては、
整合性がとれていないクエリー結果になってしまう。
上記の図の例で言いますと、海外１と海外２はタイミングによっては、結果がずれる。
東京は、マスターを参照するので、ずれはない。
因みに、リージョン内での参照系レプリケーションであればRDS側のReadReprication機能で対応できます。

具体的な手順を以下に記載します。

１．まず、マスターのＥＣ２を立ち上げます。

２．MySQLをインストールしましょう。

３．マスターのリージョンにＲＤＳインスタンスをmySQLで起動します。

•MySQL5.6.13のRDS

•Publicly AccessibleをON

•Mullti-AZオプションはOFF

•セキュリティグループではお互いの通信を許可

ポートの開放

インバウンド、アウトバウンド両方

４．ＥＣ２からＲＤＳのmySQLに接続

mysql -h m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com -P 3306 -u root  -p test_db

1	mysql -h m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com -P 3306 -u root -p test_db

５．ここでレプリケーション用のユーザーを作成しておく

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';

1	GRANT REPLICATION SLAVE ON . TO 'repl'@'%' IDENTIFIED BY 'password';

６．テスト用のテーブルを作成する。

create table  test_table (id int, name varchar(128), tel char(10), mail char(100)) character set utf8; 
insert into test_table values (1, 'tanaka', '090-111-5555','tanama@exsample.com'); 
insert into test_table values (2, 'sato', '090-222-6666','sato@exsample.com');

create table test_table (id int, name varchar(128), tel char(10), mail char(100)) character set utf8;

insert into test_table values (1, 'tanaka', '090-111-5555','tanama@exsample.com');

insert into test_table values (2, 'sato', '090-222-6666','sato@exsample.com');

７．バイナリ―のログにアクセスできるようにストアドプロシージャーを実行する。

call mysql.rds_set_configuration('binlog retention hours', 72);

1	call mysql.rds_set_configuration('binlog retention hours', 72);

８．内容を控えておく

show master status; 
+----------------------------+----------+--------------+------------------+-------------------+ | File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+-------------------+ | 
mysql-bin-changelog.000333 |      954|              |                  |                   | 
+----------------------------+----------+--------------+------------------+-------------------+

show master status;

mysql-bin-changelog.000333 | 954| | | |

+----------------------------+----------+--------------+------------------+-------------------+

８．次に、スレーブ用のリージョンのＲＤＳのインスタンスを起動します。

同じようにポートを解放してください。

９．スレーブのデータベースに接続します。

mysql -h m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com -P 3306 -u root -p test_db

1	mysql -h m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com -P 3306 -u root -p test_db

１０．レプリケーション用のユーザーを作成します。

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'passward';

1	GRANT REPLICATION SLAVE ON . TO 'repl'@'%' IDENTIFIED BY 'passward';

１１．マスターのデータベースをそのままスレーブにコピーします。

最初にマスターをダンプします。

$ mysqldump -h m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com 　-u root -p --databases test_db > dumpfile

1	$ mysqldump -h m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com 　-u root -p --databases test_db > dumpfile

次にスレーブ側でダンプファイルをインポートします。

mysql -h m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com -u root -p　test_db < dumpfile

1	mysql -h m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com -u root -p　test_db < dumpfile

１２．スレーブ側　mySQLにて以下ストアドプロシージャ―を実行します。

先程の内容をセットします。

call mysql.rds_set_external_master('master.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com',3306,'repl','password','mysql-bin-changelog.000333',954,0);

1	call mysql.rds_set_external_master('master.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com',3306,'repl','password','mysql-bin-changelog.000333',954,0);

１３．スレーブ側でレプリケーションを開始します。

CALL mysql.rds_start_replication;

1	CALL mysql.rds_start_replication;

１４．状況確認します。

show slave status\G 
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000408
          Read_Master_Log_Pos: 422
               Relay_Log_File: relaylog.000203
                Relay_Log_Pos: 595
        Relay_Master_Log_File: mysql-bin-changelog.000408
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.plugin,innodb_memcache.cache_policies,mysql       .rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.conf       ig_options
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 422
              Relay_Log_Space: 818
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 17714962
                  Master_UUID: 030d3d6b-26c6-11e5-8a5c-064062dd903b
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the sla       ve I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.22 sec)

show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: m1.cyxgcupsoi5z.ap-southeast-1.rds.amazonaws.com

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin-changelog.000408

Read_Master_Log_Pos: 422

Relay_Log_File: relaylog.000203

Relay_Log_Pos: 595

Relay_Master_Log_File: mysql-bin-changelog.000408

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table: mysql.plugin,innodb_memcache.cache_policies,mysql .rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.conf ig_options

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 422

Relay_Log_Space: 818

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 17714962

Master_UUID: 030d3d6b-26c6-11e5-8a5c-064062dd903b

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the sla ve I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.22 sec)

１３．マスター側で

データを登録

insert into test_table  values (3, ’takahashi’, '999-999-999', 'takahashi@example.com');

1	insert into test_table values (3, ’takahashi’, '999-999-999', 'takahashi@example.com');

１４．スレーブ側でレコードが増えているか確認　レコードが1件増えていれば

同期確認完了。

select * from test_table  ; 
+------+------+-------+--------------+ | id   | name| tel | mail| +------+------+-------+--------------+ 
|    1 |    tanaka| 090-111-5555| tanama@exsample.com| 
|    2 |    sato | 090-222-6666| sato@exsample.com| 
|    3 |    takahashi | 999-999-999| takahashi@example.com| 
+------+------+-------+--------------+

select * from test_table ;

+------+------+-------+--------------+ | id | name| tel | mail| +------+------+-------+--------------+

| 1 | tanaka| 090-111-5555| tanama@exsample.com|

| 2 | sato | 090-222-6666| sato@exsample.com|

| 3 | takahashi | 999-999-999| takahashi@example.com|

+------+------+-------+--------------+

メルマガ登録をお願いします。

[MySQL] レプリケーションの仕様（マスタースレーブの関係）

2015/6/20 mySQL

前回のアマゾンクラウドでのお話でもお伝えしましたが、レプリケーションとは、

データベースを複製することで、以下の役割を満たします。

・データベースがぶっ壊れたときのための予備

・もうひとつ同じデータベースを作っておくことで負荷分散スピードアップ

mysqlは

非同期レプリケーションによるマスタースレーブ型です。

非同期レプリケーションとはマスターの更新状況の保全を待たず同時に書き込むこと。

マスタースレーブ型はマスター１つに複数のスレーブを持つ。更新はマスターに全てやらせる

その内容をスレーブが取りにいく仕組み。

高機能のデータベースソフトはマルチマスター型もできる。

常に自分もマスターになれるということ。みんなで更新する感じですね。

mySQLは、マスタースレーブの関係は出来上がっています。

メルマガ登録をお願いします。

[mySQL] 指定したテーブルの最大カラム数を取得したい

2015/6/16 2015/7/9 mySQL

select count(*) from sys.columns
where object_id=object_id(‘テーブル名’)

sys.columsというシステムで用意されたテーブル（ディクショナリ）に

を参照して取得するというパターン

メルマガ登録をお願いします。

ASP.NETとLAMP環境のデータ連携を実現

2014/8/3 2015/7/5 mySQL, PHP, SQLServer, VisualBasic, プログラム技術研究所, 技術報告

■構想検討

構想としましては
OSが異なるので、お互いAPIを用意し、API経由でデータを連携させるように構想しました。

お互いのOSからクロスで放り込む方法も見つけましたが
LINUX→SQLServerの場合、ドライバーをLINUX上にインストールする必要が発生する可能性があり
難易度が少し上がります。

■仕様
クロス同期つまり、ひとつの会社テーブルの情報を常に同期させる。ASP側で更新されたらLAMPにも更新するし
逆も同様にする。

■環境準備
LAMPのシステムとASP.NETシステムの環境のデータ同期ということ。
データベースの方は一緒。SQLServerのデータをMySQLに移行。

workbenchを使います。

データ変換には癖がありますが、何回か行っていると規則性が見えてきます。
■具体的なコーディング
お互いPOSTで渡す仕組みを実現します。

phpからASPへは

file_gets_contents関数です。
$url = $strHost.'/api/CompanyUpdate.aspx';
$data = http_build_query($data, "", "&");

//header
$header = array(
"Content-Type: application/x-www-form-urlencoded",
"Content-Length: ".strlen($data)
);

$context = array(
"http" => array(
"method"  => "POST",
"header"  => implode("\r\n", $header),
"content" => $data
)
);
$contents = file_get_contents($url, false, stream_context_create($context));
if(!$contents){

//エラー時制御
}

file_gets_contents関数です。

$url = $strHost.'/api/CompanyUpdate.aspx';

$data = http_build_query($data, "", "&");

//header

$header = array(

"Content-Type: application/x-www-form-urlencoded",

"Content-Length: ".strlen($data)

);

$context = array(

"http" => array(

"method" => "POST",

"header" => implode("\r\n", $header),

"content" => $data

)

);

$contents = file_get_contents($url, false, stream_context_create($context));

if(!$contents){

//エラー時制御

}

curlのライブラリから行ったほうが実績があったのですが、

なるべく標準関数（curlもすでに共通ですが）で行った方が望ましいかと考えました。

ASPからphpへは

//バイト型配列に変換

string postData = "StrCompanyID=" + HttpUtility.UrlEncode(CommonUtil.Security.FromBase64(StrCompanyID.Value), enc) +
"&StrPassword=" + HttpUtility.UrlEncode(StrPwd.Value, enc);

byte[] postDataBytes = System.Text.Encoding.ASCII.GetBytes(postdata);

System.Net.WebRequest req =
// System.Net.WebRequest.Create(_url + "/api/set_entry.php");
System.Net.WebRequest.Create(_url + "/index.php?controller=ApiRev&action=set_update2");

///
//メソッドにPOSTを指定
req.Method = "POST";
//ContentTypeを"application/x-www-form-urlencoded"にする
req.ContentType = "application/x-www-form-urlencoded";
//POST送信するデータの長さを指定
req.ContentLength = postDataBytes.Length;

//データをPOST送信するためのStreamを取得
System.IO.Stream reqStream = req.GetRequestStream();
//送信するデータを書き込む
reqStream.Write(postDataBytes, 0, postDataBytes.Length);
reqStream.Close();

//サーバーからの応答を受信するためのWebResponseを取得
System.Net.WebResponse res = req.GetResponse();
//応答データを受信するためのStreamを取得
System.IO.Stream resStream = res.GetResponseStream();
//受信して表示
System.IO.StreamReader sr = new System.IO.StreamReader(resStream, enc);
Console.WriteLine(sr.ReadToEnd());

string postData = "StrCompanyID=" + HttpUtility.UrlEncode(CommonUtil.Security.FromBase64(StrCompanyID.Value), enc) +

"&StrPassword=" + HttpUtility.UrlEncode(StrPwd.Value, enc);

byte[] postDataBytes = System.Text.Encoding.ASCII.GetBytes(postdata);

System.Net.WebRequest req =

// System.Net.WebRequest.Create(_url + "/api/set_entry.php");

System.Net.WebRequest.Create(_url + "/index.php?controller=ApiRev&action=set_update2");

///

//メソッドにPOSTを指定

req.Method = "POST";

//ContentTypeを"application/x-www-form-urlencoded"にする

req.ContentType = "application/x-www-form-urlencoded";

//POST送信するデータの長さを指定

req.ContentLength = postDataBytes.Length;

//データをPOST送信するためのStreamを取得

System.IO.Stream reqStream = req.GetRequestStream();

//送信するデータを書き込む

reqStream.Write(postDataBytes, 0, postDataBytes.Length);

reqStream.Close();

//サーバーからの応答を受信するためのWebResponseを取得

System.Net.WebResponse res = req.GetResponse();

//応答データを受信するためのStreamを取得

System.IO.Stream resStream = res.GetResponseStream();

//受信して表示

System.IO.StreamReader sr = new System.IO.StreamReader(resStream, enc);

Console.WriteLine(sr.ReadToEnd());

POSTを&でつなげてPOST送信します。

メルマガ登録して、小技を身に着けてください。

メルマガ登録をお願いします。

SQLServer から　MySQLへストアド移植

2014/3/4 2015/7/12 mySQL, SQLServer, データベース

名称

SQLServer ストアドプロシージャー

MySQL ルーチン

まだまだMySQLは簡易的な印象を受ける。

SQLServerでバリバリ動いていたデータベースをMySQLに移植するのは

どこまで耐久力があるかどうかというのが心配なところ。

まずはDECLARE

デクレアで変数宣言これは共通かな。

上記でまとめて宣言が必要

テンポラリーテーブルの最初の文字は＃をつけない。

ISNULLよりIFNULL

メルマガ登録をお願いします。

mySQLの動きについて

2011/7/21 2013/10/19 mySQL

http://www.sonorilo.net/2010/03/02/2718

検索条件の挙動が微妙。。

親切なのはありがたいですね。
でも親切すぎると。。

おせっかいとか言われてしまう。

メルマガ登録をお願いします。

ＭｙＳＱＬ文字化け対策

2009/4/20 2013/10/19 mySQL

ＭｙＳＱＬで？とか日本語が表示されてしまったときは

$db->query(“SET NAMES sjis”);

で直る場合がよくあります。
みなさんお試しください。

メルマガ登録をお願いします。

coreserverのmysqlメモ

2008/1/25 2013/10/19 mySQL

valuedomainから上位coreserverへのデータベース移行メモ

１．valuedomainの管理画面にてデータベースの保存を
　行います。

２．データベース間コピーを行い、mysql.dumpファイルを　coreserverへコピーします。

３．コアサーバーへログインして、データベースを作成します。

４．sshでログインして
　データベースを使用するようにします。

$ mysql -u newdatabase -p
password:

mysql >use newdatabase

mysql >source mysql.dump

でインポート完了