差分

*DBすべて # $ mysqldump --default-character-set=utf8 -uroot u USER_NAME -p mt DB_NAME > mt_backup20140120OUTPUT_FILE.sql Enter password:  *テーブル指定 $ mysqldump -u USER_NAME -p -h HOST_NAME -t DB_NAME TABLE_NAME > OUTPUT_FILE.sql

===[[TipsphpMyAdmin]]===*[[phpMyAdmin]] ===管理ポートフォワーディング===*ssh -i 踏み台サーバー鍵ファイル -L ローカルポート:本当に接続したいサーバー:本当に接続したいサーバーのポート 踏み台サーバーユーザー@踏み台サーバー<pre>$ ssh -i ~/.ssh/typea-dev-key.pem -L 13308:typea-dev-mysql-multiaz.c5ihdpszudfw.us-east-1.rds.amazonaws.com:3306 ec2-user@34.226.213.144</pre>RDSにEC2を踏み台にして、SQLWorkbentchから接続 [[File:port_forwarding.png|600px]] ==管理=====データファイルの場所====----

====テーブル一覧を表示====----

====セッションの確認と切断====----

===[[SQL]]制約を無視してテーブルをtruncateする=======制約を無視してテーブルをtruncateする====----

 ===mysqlコマンドパーティショニング===----*[https://dev.mysql.com/doc/refman/5.6/ja/partitioning-types.html パーティションタイプ]*https://dev.mysql.com/doc/refman/5.6/ja/partitioning-range.html<pre>CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL)PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21));</pre>====リストパーティショニング====----*https://dev.mysql.com/doc/refman/5.6/ja/partitioning-list.html<pre>CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT)PARTITION BY LIST(store_id) ( PARTITION pNorth VALUES IN (3,5,6,9,17), PARTITION pEast VALUES IN (1,2,10,11,19,20), PARTITION pWest VALUES IN (4,12,13,14,18), PARTITION pCentral VALUES IN (7,8,15,16));</pre>*https://dev.mysql.com/doc/refman/5.6/ja/partitioning-columns-list.html<pre>CREATE TABLE customers_1 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE)PARTITION BY LIST COLUMNS(city) ( PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'), PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'), PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'), PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo'));</pre> <pre>mysql> alter table jra_data -> partition by list COLUMNS(data_type) ( -> partition p0 values in ('0'), -> partition p1 values in ('1'), -> partition p2 values in ('2'), -> partition p5 values in ('5'), -> partition p7 values in ('7'), -> partition p9 values in ('9'), -> partition pA values in ('A'), -> partition pB values in ('B') -> );</pre>*確認<pre>mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='jra_data';+--------------+------------+----------------+----------------------------+------------+| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |+--------------+------------+----------------+----------------------------+------------+| jra | jra_data | p0 | 1 | 8 || jra | jra_data | p1 | 2 | 533260 || jra | jra_data | p2 | 3 | 69894 || jra | jra_data | p5 | 4 | 459127 || jra | jra_data | p7 | 5 | 1052898 || jra | jra_data | p9 | 6 | 5694 || jra | jra_data | pA | 7 | 707408 || jra | jra_data | pB | 8 | 10085 |+--------------+------------+----------------+----------------------------+------------+8 rows in set (0.06 sec) </pre> ==mysqlコマンド=====ソースファイルの[[SQL]]を実行====----

====データベースの変更====----

====ヘルプ====----

====テーブル定義を確認====----

====テーブル一覧====----

====ポートを調べる====----

===プログラミング=======JDBC Download====

====[[C#]]から接続=====----====DML(create tableCREATE TABLE)=====----

using MySql.Data.MySqlClient;using System.Data;using System.Diagnostics; public void CreateTable(string host, string database, string user, string password) { // create database jra default character set utf8; // create user 'jra'@'localhost' identified by 'new password'; // create user 'jra'@'%' identified by 'new password'; // grant all privileges on *.* to 'jra'@'%' with grant option;

var conInfo = $"Database=jra;Data Source=192.168.0.2;User Id=jra;Password=password; pooling = false; convert zero datetime=True"; using (var conn = new MySqlConnection(conInfo)) { conn.Open();

var createTable = @"

rec_spec varchar(2) not null, key rec_key varchar(100) not null, make_date varchar(10) not null, data_type varchar(1) not null, data mediumtext, primary key(rec_spec, keyrec_key)

var cmd = new MySqlCommand(createTable); cmd.Connection = conn; cmd.ExecuteNonQuery(); }}</pre> =====データ登録(INSERT)=====----<pre>public void Load(string host, string database, string user,string password, string fileName){  MySqlCommand insCmd = new MySqlCommand( @"insert into jra_data ( rec_spec, rec_key, make_date, data_type, data ) " + " values ( @rec_spec, @rec_key, @make_date, @data_type, @data )");  var conInfo = $"Database=jra;Data Source=192.168.0.2;User Id=jra;Password=password; pooling = false; convert zero datetime=True"; using (var conn = new MySqlConnection(conInfo)) { conn.Open();  insCmd.Connection = conn;  using (var reader = new StreamReader(fileName)) { string line = null; while ((line = reader.ReadLine()) != null) { var record = line.Split("\t"); cmdDebug.WriteLine(line); if (record.Length >= 5) { var recSpec = record[0]; var makeDate = record[1]; var dataType = record[2]; var recKey = record[3]; var data = record[4];  insCmd.Parameters.Clear(); insCmd.Parameters.Add(new MySqlParameter("rec_spec", recSpec)); insCmd.Parameters.Add(new MySqlParameter("rec_key", recKey)); insCmd.Parameters.Add(new MySqlParameter("make_date", makeDate)); insCmd.Parameters.Add(new MySqlParameter("data_type", dataType)); insCmd.Parameters.Add(new MySqlParameter("data", data)); insCmd.ExecuteNonQuery(); }

 =====データ取得(SELECT)=====----

pgm.[[Access]]TestAccessTest();

public void [[Access]]TestAccessTest()

// Unable to convert [[MySQL]] date/time value to System.DateTime

var conInfo = $"[[Database]]=wordpress;Data Source=192.168.0.2;User Id=root;Password={password}; pooling = false; convert zero datetime=True";

foreach (Data[[R]]ow DataRow row in table.[[R]]owsRows)