「MySQL」の版間の差分
ナビゲーションに移動
検索に移動
(→データ取得) |
|||
(同じ利用者による、間の41版が非表示) | |||
1行目: | 1行目: | ||
+ | | [[XAMPP]] | | ||
==[[MySQL]]== | ==[[MySQL]]== | ||
[[Database]] | [[CentOS]] | | [[Database]] | [[CentOS]] | | ||
6行目: | 7行目: | ||
==インストール== | ==インストール== | ||
===[[Windows]]=== | ===[[Windows]]=== | ||
+ | ====5.6==== | ||
*[http://typea.info/blg/glob/2014/04/mysql56-windows-81.html Windows8.1にMySQL5.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でインストール)=== | ===[[Ubuntu]](apt-getでインストール)=== | ||
$ sudo apt-get install mysql-server | $ sudo apt-get install mysql-server | ||
73行目: | 78行目: | ||
# [[chkconfig]] | grep "mysql" | # [[chkconfig]] | grep "mysql" | ||
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off | 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/5.1-olh/ja/unix-post-installation.html | ||
78行目: | 89行目: | ||
*http://dev.mysql.com/doc/refman/4.1/ja/resetting-permissions.html | *http://dev.mysql.com/doc/refman/4.1/ja/resetting-permissions.html | ||
=====[[Ubuntu]] 初期パスワードの場所===== | =====[[Ubuntu]] 初期パスワードの場所===== | ||
+ | ---- | ||
*確認 | *確認 | ||
<pre> | <pre> | ||
169行目: | 181行目: | ||
====初期の権限変更ツール mysql_secure_installation==== | ====初期の権限変更ツール mysql_secure_installation==== | ||
+ | ---- | ||
*初期パスワード /var/log/mysqld.log | grep "temporary password" | *初期パスワード /var/log/mysqld.log | grep "temporary password" | ||
# mysql_secure_installation | # mysql_secure_installation | ||
183行目: | 196行目: | ||
Cleaning up... | 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> | ||
+ | |||
===簡易設定=== | ===簡易設定=== | ||
====バージョンの確認==== | ====バージョンの確認==== | ||
332行目: | 393行目: | ||
===例=== | ===例=== | ||
====[[バックアップ]]例==== | ====[[バックアップ]]例==== | ||
− | + | *DBすべて | |
− | Enter password: | + | $ mysqldump --default-character-set=utf8 -u USER_NAME -p DB_NAME > OUTPUT_FILE.sql |
+ | Enter password: | ||
+ | |||
+ | *テーブル指定 | ||
+ | $ mysqldump -u USER_NAME -p -h HOST_NAME -t DB_NAME TABLE_NAME > OUTPUT_FILE.sql | ||
====レストア例==== | ====レストア例==== | ||
442行目: | 507行目: | ||
</pre> | </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]] | ||
+ | |||
+ | ==管理== | ||
+ | ===データファイルの場所=== | ||
+ | ---- | ||
/etc/my.cnf | /etc/my.cnf | ||
datadir=/var/lib/mysql | datadir=/var/lib/mysql | ||
− | + | ===テーブル一覧を表示=== | |
+ | ---- | ||
# mysql -u root -p | # mysql -u root -p | ||
mysql> use mt | mysql> use mt | ||
466行目: | 544行目: | ||
+-------------------+ | +-------------------+ | ||
44 rows in set (0.00 sec) | 44 rows in set (0.00 sec) | ||
− | + | ===セッションの確認と切断=== | |
+ | ---- | ||
show processlist; | show processlist; | ||
kill [Id] | 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する=== | |
+ | ---- | ||
*一旦、FO[[R]]EIGN_KEY_CHECKSを0にする | *一旦、FO[[R]]EIGN_KEY_CHECKSを0にする | ||
mysql> SET FO[[R]]EIGN_KEY_CHECKS=0; | mysql> SET FO[[R]]EIGN_KEY_CHECKS=0; | ||
mysql> truncate table hoge; | mysql> truncate table hoge; | ||
mysql> SET FO[[R]]EIGN_KEY_CHECKS=1; | mysql> SET FO[[R]]EIGN_KEY_CHECKS=1; | ||
− | === | + | |
− | ====ソースファイルの[[SQL]]を実行=== | + | ===パーティショニング=== |
+ | ---- | ||
+ | *[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 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> | ||
+ | |||
+ | ==mysqlコマンド== | ||
+ | ===ソースファイルの[[SQL]]を実行=== | ||
+ | ---- | ||
\.<ファイル名> | \.<ファイル名> | ||
− | + | ===データベースの変更=== | |
+ | ---- | ||
use | use | ||
− | + | ===ヘルプ=== | |
+ | ---- | ||
help | help | ||
− | + | ===テーブル定義を確認=== | |
+ | ---- | ||
show create table テーブル名 | show create table テーブル名 | ||
− | + | ===テーブル一覧=== | |
+ | ---- | ||
show tables | show tables | ||
− | + | ===ポートを調べる=== | |
+ | ---- | ||
mysql> show variables like 'port'; | mysql> show variables like 'port'; | ||
+---------------+-------+ | +---------------+-------+ | ||
495行目: | 680行目: | ||
+---------------+-------+ | +---------------+-------+ | ||
1 row in set (0.00 sec) | 1 row in set (0.00 sec) | ||
− | + | ||
− | + | ==プログラミング== | |
+ | ===JDBC Download=== | ||
*http://dev.mysql.com/downloads/connector/ | *http://dev.mysql.com/downloads/connector/ | ||
− | + | ===[[C#]]から接続=== | |
− | + | ---- | |
+ | ====DML(CREATE TABLE)==== | ||
+ | ---- | ||
<pre> | <pre> | ||
using MySql.Data.MySqlClient; | using MySql.Data.MySqlClient; | ||
535行目: | 723行目: | ||
=====データ登録(INSERT)===== | =====データ登録(INSERT)===== | ||
+ | ---- | ||
<pre> | <pre> | ||
public void Load(string host, string database, string user,string password, string fileName) | public void Load(string host, string database, string user,string password, string fileName) | ||
579行目: | 768行目: | ||
} | } | ||
</pre> | </pre> | ||
− | + | ====データ取得(SELECT)==== | |
+ | ---- | ||
<pre> | <pre> | ||
using MySql.Data.MySqlClient; | using MySql.Data.MySqlClient; | ||
592行目: | 782行目: | ||
{ | { | ||
var pgm = new Program(); | var pgm = new Program(); | ||
− | pgm. | + | pgm.AccessTest(); |
} | } | ||
− | public void | + | public void AccessTest() |
{ | { | ||
− | // Unable to convert | + | // 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 | // https://stackoverflow.com/questions/2934844/unable-to-convert-mysql-date-time-value-to-system-datetime | ||
− | var conInfo = $" | + | 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)) | using(var conn = new MySqlConnection(conInfo)) | ||
611行目: | 801行目: | ||
foreach (DataTable table in ds.Tables) | foreach (DataTable table in ds.Tables) | ||
{ | { | ||
− | foreach ( | + | foreach (DataRow row in table.Rows) |
{ | { | ||
foreach (DataColumn col in table.Columns) | foreach (DataColumn col in table.Columns) | ||
624行目: | 814行目: | ||
} | } | ||
} | } | ||
+ | </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> | </pre> |
2023年11月13日 (月) 10:57時点における最新版
| XAMPP |
MySQL
インストール
Windows
5.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
- 途中でパスワードの設定を求められる
CentOS(yumからインストール)
手順
- https://dev.mysql.com/downloads/repo/yum/ に移動
- 使用しているプラットフォーム用のリリースパッケージを選択してダウンロード
- インストール
$ sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm yum update $ sudo yum install mysql-community-server
ダウンロード
MySQL Community Server
- http://dev.mysql.com/downloads/mysql/
- MySQL-5.6.13-1.linux_glibc2.5.i386.rpm-bundle.tar
アカウントの登録
- mysql グループと mysqlユーザーの作成
# groupadd mysql # useradd -g mysql mysql
解凍
$ tar xvf MySQL-5.6.13-1.linux_glibc2.5.i386.rpm-bundle.tar
インストール
- 標準の最低限のインストールには、サーバーとクライアント RPM をインストールします。
- RPM はまた MySQL サーバーを運用するためのユーザー mysql (存在しない場合) のログインアカウントを作成する
- サーバーがブート時に自動的に起動するように適切なエントリを /etc/init.d/ に作成する
# rpm -ivh MySQL-shared-5.6.13-1.linux_glibc2.5.i386.rpm # rpm -ivh MySQL-shared-compat-5.6.13-1.linux_glibc2.5.i386.rpm # rpm -ivh MySQL-server-5.6.13-1.linux_glibc2.5.i386.rpm # rpm -ivh MySQL-client-5.6.13-1.linux_glibc2.5.i386.rpm
<blockquote>serverインストール時に、初期パスワードの情報がコンソールに出力される</blockquote>
A random root password has been set. You will find it in '/root/.mysql_secret'.
<blockquote>もしくはログに出力されている</blockquote>
/var/log/mysqld.log | grep "temporary password"
開発で必要(Djangoなど)
# rpm -ivh MySQL-devel-5.6.13-1.linux_glibc2.5.i386.rpm
起動と停止
CentOS
# /sbin/service mysql start Starting MySQL... [ OK ] # /etc/init.d/mysql stop Shutting down MySQL.. [ OK ]
CentOS7
# systemctl enable mysqld # systemctl start mysqld
Ubuntu
$ sudo /etc/init.d/mysql start $ sudo /etc/init.d/mysql stop $ sudo /etc/init.d/mysql restart
mysqld_safe(mysqld のラッパ)
shell> cd mysql_installation_directory shell> bin/mysqld_safe &
起動設定がされたか確認
# chkconfig | grep "mysql" mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
Mac(brewからクライアントのみインストール)
$ brew install mysql-client
- インストールログの末尾を参照してPATHを通す
権限の初期設定
忘れたパスワードをリセット
Ubuntu 初期パスワードの場所
- 確認
$ sudo cat /etc/mysql/debian.cnf # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = KJmv1zRL0acKBftn socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = KJmv1zRL0acKBftn socket = /var/run/mysqld/mysqld.sock
- ログイン
piroto@jinmu:/etc/mysql$ mysql -u debian-sys-maint -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu) mysql> use mysql mysql> select User,Host,plugin from mysql.user; +------------------+-----------+-----------------------+ | User | Host | plugin | +------------------+-----------+-----------------------+ | debian-sys-maint | localhost | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | auth_socket | +------------------+-----------+-----------------------+ 5 rows in set (0.00 sec)
- rootパスワードの変更
mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 8.0.21-0ubuntu0.20.04.4 | +-------------------------+ 1 row in set (0.00 sec) mysql> set password for 'root'@'localhost' = 'new password'; Query OK, 0 rows affected, 1 warning (0.06 sec)
- クライアントから root でログイン
- sudo しないとERROR 1698 (28000): Access denied エラー となる
$ mysql -u root -p Enter password: ERROR 1698 (28000): Access denied for user 'root'@'localhost'
iroto@jinmu:/etc/mysql$ mysql -u root -p Enter password: ERROR 1698 (28000): Access denied for user 'root'@'localhost' piroto@jinmu:/etc/mysql$ sudo mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 26 Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu) mysql>
mysqldを--skip-grant-tables オプションで起動
# mysqld --skip-grant-tables &
mysqld サーバに接続
# mysql -u root mysql
新しいパスワードの設定
mysql> update user set Password=PASSWORD('newpassword') -> where User='root'; Query OK, 4 rows affected (0.11 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
rootパスワードの変更
mysql> set password for 'root'@'localhost' = password('newpassword'); Query OK, 0 rows affected (0.00 sec)
パスワードの設定 MySQL8
mysql> set password for 'wordpress'@'localhost' = 'your password';
初期の権限変更ツール mysql_secure_installation
- 初期パスワード /var/log/mysqld.log | grep "temporary password"
# mysql_secure_installation Change the root password? [Y/n] n Remove anonymous users? [Y/n] Y Disallow root login remotely? [Y/n] Y Remove test database and access to it? [Y/n] n Reload privilege tables now? [Y/n] Y All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! Cleaning up...
Failed! Error: SET PASSWORD has no significance for user... エラー
- 上記「Ubuntu 初期パスワードの場所」のユーザーでログイン
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.
- ループから抜け出せない、以下のプロンプトでCtrl+C
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) :
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.
- 以下で成功!
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'NewPassword'; Query OK, 0 rows affected (0.01 sec)
mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 8.0.30-0ubuntu0.20.04.2 | +-------------------------+ 1 row in set (0.00 sec)
簡易設定
バージョンの確認
mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.77 | +-----------+ 1 row in set (0.00 sec)
MySQL 簡易設定
データベースの作成
作成
# mysql -u root -p mysql> create database test_db default character set utf8; Query OK, 1 row affected (0.00 sec)
文字コード
確認
mysql> show create database test_db; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | test_db | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec)
データベースの一覧
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | song_dict | | sys | +--------------------+ 5 rows in set (0.00 sec)
権限
- 接続を許可するユーザーをmysqlデータベース内で管理している
- ユーザーがアクセスする際にはアクセス元のホスト(IPアドレス)もセットで認証が行われる
権限テーブル
テーブル名 | 説明 |
---|---|
user | ユーザーの基本的な定義 |
host | ホストに対する権限の定義 |
db | データベースに対する権限の定義 |
tables_priv | テーブルに対する権限の定義 |
clumuns_priv | カラムに対する権限の定義 |
ユーザーの作成
mysql> create user test_user@localhost identified by 'new password'; Query OK, 0 rows affected (0.03 sec)
権限の付与
限定して付与
mysql> grant create,alter,select,insert,update,delete,index on *.* to test_user@localhost; Query OK, 0 rows affected (0.00 sec)
管理権限を付与
- test_admin@localhost を作成した上で管理権限を付与
- リモートアクセスを可能とするには、test_admin@localhost の部分を test_admin@'%' とする
mysql> grant all privileges on *.* to test_admin@localhost identified by 'newpassword' with grant option; Query OK, 0 rows affected (0.00 sec)
外部から接続
ポートを開ける
# firewall-cmd --permanent --zone=public --add-port=3306/tcp # firewall-cmd --reload
piroto@jinmu:~$ sudo ufw allow 3306 ルールを追加しました ルールを追加しました (v6) piroto@jinmu:~$ sudo ufw status 状態: アクティブ To Action From -- ------ ---- 80 ALLOW Anywhere 3306 ALLOW Anywhere 80 (v6) ALLOW Anywhere (v6) 3306 (v6) ALLOW Anywhere (v6)
root@%を追加
確認
mysql> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | pma | localhost | | root | localhost | | wordpress | localhost | +------------------+-----------+
ユーザーの追加
mysql> create user 'root'@'%' identified by 'パスワード'; mysql> set password for 'root'@'%' = 'パスワード'; mysql> grant all on *.* to 'root'@'%';
確認
mysql> select user, host from user; +------------------+-----------+ | user | host | +------------------+-----------+ | root | % | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | pma | localhost | | root | localhost | | wordpress | localhost | +------------------+-----------+
バックアップとリストア
mysqldump
テーブル名を指定してダンプ
mysqldump [オプション] データベース名 [テーブル名 ...]
複数のデータベースを対象とする
mysqldump [オプション] --databases [オプション] データベース名 [データベース名 ...]
すべてのデータベースを対象とする
mysqldump [オプション] --all-databases [オプション]
例
バックアップ例
- DBすべて
$ mysqldump --default-character-set=utf8 -u USER_NAME -p DB_NAME > OUTPUT_FILE.sql Enter password:
- テーブル指定
$ mysqldump -u USER_NAME -p -h HOST_NAME -t DB_NAME TABLE_NAME > OUTPUT_FILE.sql
レストア例
- mysqldumpの出力はSQL文の羅列であるため、復元するにはリダイレクトを使ってmysqlに結果を与えれば良い
# mysql -u root -p mt < mt_backup20140120.sql
接続
外部接続
Ubuntu エラー 111で接続できない
$ msyql -u hoge -p -h 192.168.0.43 foo_db Can't connect to MySQL server on '192.168.0.43' (111)
- localhostのみをリッスンしている
- 設定ファイルを編集
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
- bind-address =127.0.0.1 に制限されているためコメントアウト
# If MySQL is running as a replication slave, this should be # changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir # tmpdir = /tmp # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. # bind-address = 127.0.0.1
- 再起動
$ sudo systemctl restart mysql
Python
Install
$ pip install --upgrade pip
Insert
import mysql.connector def ins(): add_song_element = ("INSERT INTO song_element " "(element, reading, reading_rev) " "VALUES (%s, %s, %s)") config = { 'user':'song_dict', 'password':'my password', 'host':'192.168.0.43', 'database':'song_dict' } try: cnx = mysql.connector.connect(**config) cursor = cnx.cursor() cursor.execute(add_song_element, ('hoge', 'foo', 'bar')) except Exception as ee: print(ee)
Query
query = ("SELECT id FROM song_element " "WHERE reading=%s and element=%s") cnx = mysql.connector.connect(**config) cursor = cnx.cursor() cursor.execute(query , ('hoge', 'foo')) for (id) in cursor: print(id)
Java
package info.typea.songdict.sample; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class DbAccess { public void doWork() { try (Connection conn = DriverManager.getConnection( "jdbc:mysql://hostname/dbname", "username", "password")) { Statement stmt = conn.createStatement(); ResultSet resultSet = stmt.executeQuery("select substr(reading,1,2) from song_element group by substr(reading,1,2)"); while (resultSet.next()) { System.out.println(resultSet.getString(1)); } } catch(Exception e) { System.out.println(String.format("ERROR : %s", e)); } } }
phpMyAdmin
ポートフォワーディング
- ssh -i 踏み台サーバー鍵ファイル -L ローカルポート:本当に接続したいサーバー:本当に接続したいサーバーのポート 踏み台サーバーユーザー@踏み台サーバー
$ 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
RDSにEC2を踏み台にして、SQLWorkbentchから接続
管理
データファイルの場所
/etc/my.cnf datadir=/var/lib/mysql
テーブル一覧を表示
# mysql -u root -p mysql> use mt Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_mt | +-------------------+ | mt_asset | | mt_asset_meta | | mt_association | : | mt_ts_funcmap | | mt_ts_job | +-------------------+ 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)
制約を無視してテーブルをtruncateする
- 一旦、FOREIGN_KEY_CHECKSを0にする
mysql> SET FOREIGN_KEY_CHECKS=0; mysql> truncate table hoge; mysql> SET FOREIGN_KEY_CHECKS=1;
パーティショニング
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) );
リストパーティショニング
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) );
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') );
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') -> );
- 確認
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)
mysqlコマンド
ソースファイルのSQLを実行
\.<ファイル名>
データベースの変更
use
ヘルプ
help
テーブル定義を確認
show create table テーブル名
テーブル一覧
show tables
ポートを調べる
mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.00 sec)
プログラミング
JDBC Download
C#から接続
DML(CREATE 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 = @" 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(); } }
データ登録(INSERT)
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(); } } } } }
データ取得(SELECT)
using MySql.Data.MySqlClient; using System; using System.Data; namespace Mt2Wp { class Program { static void Main(string[] args) { var pgm = new Program(); pgm.AccessTest(); } public void AccessTest() { // 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)) { conn.Open(); var da = new MySqlDataAdapter("select * from wp_posts", conn); var ds = new DataSet(); da.Fill(ds); foreach (DataTable table in ds.Tables) { foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { Console.Write($"{row[col]}\t"); } Console.WriteLine(""); } } } } } }
データ取得(カーソル)
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")); } } }
Pythonから接続
- requirements.txt
mysql-connector-python==8.0.21
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()
© 2006 矢木浩人