| [[XAMPP]] |
==[[MySQL]]==
[[Database]] | [[CentOS]] |
==インストール==
===[[Windows]]===
====5.6====
*[http://typea.info/blg/glob/2014/04/mysql56-windows-81.html Windows8.1にMySQL5.6をインストール]
====8.0====
*[https://www.typea.info/blog/index.php/2022/02/03/mysql80_install_windows10/ Windows10 にMySQL8.0をインストール
===[[Ubuntu]](apt-getでインストール)===
$ sudo apt-get install mysql-server
# [[chkconfig]] | grep "mysql"
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
===[[Mac]](brewからクライアントのみインストール)===
<pre>
$ brew install mysql-client
</pre>
*インストールログの末尾を参照してPATHを通す
===権限の初期設定===
*http://dev.mysql.com/doc/refman/5.1-olh/ja/unix-post-installation.html
*http://dev.mysql.com/doc/refman/4.1/ja/resetting-permissions.html
=====[[Ubuntu]] 初期パスワードの場所=====
----
*確認
<pre>
====初期の権限変更ツール mysql_secure_installation====
----
*初期パスワード /var/log/mysqld.log | grep "temporary password"
# mysql_secure_installation
Cleaning up...
=====Failed! Error: SET PASSWORD has no significance for user... エラー=====
----
*上記「Ubuntu 初期パスワードの場所」のユーザーでログイン
<pre>
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
</pre>
*ループから抜け出せない、以下のプロンプトでCtrl+C
<pre>
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) :
</pre>
*https://dev.mysql.com/doc/refman/8.0/ja/resetting-permissions.html
<pre>
ubuntu@ip-172-26-11-57:~$ mysql -u debian-sys-maint -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.30-0ubuntu0.20.04.2 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password for 'root'@'localhost' = 'NewPassword';
ERROR 4102 (HY000): SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
</pre>
*以下で成功!
<pre>
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'NewPassword';
Query OK, 0 rows affected (0.01 sec)
</pre>
<pre>
mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 8.0.30-0ubuntu0.20.04.2 |
+-------------------------+
1 row in set (0.00 sec)
</pre>
===簡易設定===
====バージョンの確認====
===例===
====[[バックアップ]]例====
*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
====レストア例====
</pre>
===[[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]] ==管理=====データファイルの場所====----
/etc/my.cnf
datadir=/var/lib/mysql
====テーブル一覧を表示====----
# mysql -u root -p
mysql> use mt
+-------------------+
44 rows in set (0.00 sec)
====セッションの確認と切断====----
show processlist;
kill [Id]
==[[SQL]]==
===カンマ区切り文字列をSplitする===
SUBSTRING_INDEX(comma_string,',',1),
SUBSTRING_INDEX(SUBSTRING_INDEX(comma_string,',',1),',',1),
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(comma_string,',',1),',',1),',',1),
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(comma_string,',',1),',',1),',',1),',',1)
===[[SQL]]制約を無視してテーブルをtruncateする=======制約を無視してテーブルをtruncateする====----
*一旦、FO[[R]]EIGN_KEY_CHECKSを0にする
mysql> SET FO[[R]]EIGN_KEY_CHECKS=0;
mysql> truncate table hoge;
mysql> SET FO[[R]]EIGN_KEY_CHECKS=1;
===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]]を実行====----
\.<ファイル名>
====データベースの変更====----
use
====ヘルプ====----
help
====テーブル定義を確認====----
show create table テーブル名
====テーブル一覧====----
show tables
====ポートを調べる====----
mysql> show variables like 'port';
+---------------+-------+
+---------------+-------+
1 row in set (0.00 sec)
===プログラミング=======JDBC Download====
*http://dev.mysql.com/downloads/connector/
====[[C#]]から接続===----====DML(CREATE TABLE)====----<pre>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 = @"create table jra_data (rec_spec varchar(2) not null,rec_key varchar(100) not null,make_date varchar(10) not null,data_type varchar(1) not null,data mediumtext,primary key(rec_spec, rec_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"); Debug.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(); } } } }}</pre>====データ取得(SELECT)====----<pre>
using MySql.Data.MySqlClient;
using System;
{
var pgm = new Program();
pgm.[[Access]]TestAccessTest();
}
public void [[Access]]TestAccessTest()
{
// Unable to convert [[MySQL]] date/time value to System.DateTime
// https://stackoverflow.com/questions/2934844/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";
using(var conn = new MySqlConnection(conInfo))
foreach (DataTable table in ds.Tables)
{
foreach (Data[[R]]ow DataRow row in table.[[R]]owsRows)
{
foreach (DataColumn col in table.Columns)
}
}
</pre>
====データ取得(カーソル)====
----
<pre>
using (var conn = new MySqlConnection(GetConnStr(host, database, user, password)))
{
conn.Open();
var command = new MySqlCommand("select * from jra_data", conn);
using(var reader = command.ExecuteReader())
{
while(reader.Read())
{
Debug.WriteLine(reader.GetString("data"));
}
}
}
</pre>
===[[Python]]から接続===
*requirements.txt
<pre>
mysql-connector-python==8.0.21
</pre>
*
<pre>
def normalize(
out_base_dir,
norm_prefix,
parse_dir,
parse_prefix,
skip_when_exists=True,
host='localhost'):
add_song_element = ("INSERT INTO song_element "
"(element, reading, reading_rev) "
"VALUES (%s, %s, %s)")
query = ("SELECT id FROM song_element "
"WHERE reading=%s and element=%s")
config = {
'user':'song_dict',
'password':'',
'host':host,
'database':'song_dict'
}
try:
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
cnt = 0;
hashids = Hashids(salt=HASH_IDS_SALT)
os.makedirs(out_base_dir, exist_ok=True)
for file in glob.glob(os.path.join(parse_dir, '{0}*'.format(parse_prefix))):
key = file.split(parse_prefix)[-1]
print(key)
with open(file, 'r') as inf:
for l in inf.readlines():
try:
el = l.split('\t')
meta = el[-1].split(',')
element = el[0].strip()
reading = meta[-2].strip()
reading_rev = "".join(reversed(reading))
if element and len(element) > 1:
cursor.execute(query , (reading, element))
is_skip = False
for (id) in cursor:
is_skip = True
break;
if not is_skip:
cursor.execute(add_song_element, (element, reading, reading_rev))
print("{s} : {s}".format(cursor.lastrowid, el))
except Exception as ee:
print(ee)
cnx.commit()
except Exception as e:
print(e)
else:
cnx.close()
</pre>