12,504
回編集
差分
ナビゲーションに移動
検索に移動
→phpMyAdmin
==インストール==
===[[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>
===簡易設定===
====バージョンの確認====
===[[phpMyAdmin]]===
*[[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]]
==管理==
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)
===制約を無視してテーブルをtruncateする===
----
mysql> truncate table hoge;
mysql> SET FO[[R]]EIGN_KEY_CHECKS=1;
===パーティショニング===
----
*[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>
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 INFORMATIO
N_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>
© 2006 矢木浩人
