| ページ一覧 | ブログ | twitter |  書式 | 書式(表) |

MyMemoWiki

「MySQL」の版間の差分

提供: MyMemoWiki
ナビゲーションに移動 検索に移動
 
(同じ利用者による、間の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行目:
 
===例===
 
===例===
 
====[[バックアップ]]例====
 
====[[バックアップ]]例====
  # mysqldump --default-character-set=utf8 -uroot -p mt &gt; mt_backup20140120.sql
+
*DBすべて
  Enter password:  
+
  $ mysqldump --default-character-set=utf8 -u USER_NAME -p DB_NAME &gt; OUTPUT_FILE.sql
 +
  Enter password:
 +
 
 +
*テーブル指定
 +
$ mysqldump -u USER_NAME -p -h HOST_NAME -t DB_NAME TABLE_NAME &gt; OUTPUT_FILE.sql
  
 
====レストア例====
 
====レストア例====
442行目: 507行目:
 
</pre>
 
</pre>
  
==[[Tips]]==
+
===[[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&gt; use mt
 
   mysql&gt; 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)
  
===[[SQL]]===
+
 
====制約を無視してテーブルをtruncateする====
+
===制約を無視してテーブルをtruncateする===
 +
----
 
*一旦、FO[[R]]EIGN_KEY_CHECKSを0にする
 
*一旦、FO[[R]]EIGN_KEY_CHECKSを0にする
 
  mysql&gt; SET FO[[R]]EIGN_KEY_CHECKS=0;
 
  mysql&gt; SET FO[[R]]EIGN_KEY_CHECKS=0;
 
  mysql&gt; truncate table hoge;
 
  mysql&gt; truncate table hoge;
 
  mysql&gt; SET FO[[R]]EIGN_KEY_CHECKS=1;
 
  mysql&gt; SET FO[[R]]EIGN_KEY_CHECKS=1;
===mysqlコマンド===
+
 
====ソースファイルの[[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]]を実行===
 +
----
 
  \.&lt;ファイル名&gt;
 
  \.&lt;ファイル名&gt;
====データベースの変更====
+
===データベースの変更===
 +
----
 
  use
 
  use
====ヘルプ====
+
===ヘルプ===
 +
----
 
  help
 
  help
====テーブル定義を確認====
+
===テーブル定義を確認===
 +
----
 
  show create table テーブル名
 
  show create table テーブル名
====テーブル一覧====
+
===テーブル一覧===
 +
----
 
  show tables
 
  show tables
====ポートを調べる====
+
===ポートを調べる===
 +
----
 
  mysql&gt; show variables like 'port';
 
  mysql&gt; show variables like 'port';
 
  +---------------+-------+
 
  +---------------+-------+
495行目: 680行目:
 
  +---------------+-------+
 
  +---------------+-------+
 
  1 row in set (0.00 sec)
 
  1 row in set (0.00 sec)
===プログラミング===
+
 
====JDBC Download====
+
==プログラミング==
 +
===JDBC Download===
 
*http://dev.mysql.com/downloads/connector/
 
*http://dev.mysql.com/downloads/connector/
====[[C#]]から接続====
+
===[[C#]]から接続===
=====DML(create table)=====
+
----
 +
====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)=====
+
====データ取得(SELECT)====
 +
----
 
<pre>
 
<pre>
 
  using MySql.Data.MySqlClient;
 
  using MySql.Data.MySqlClient;
592行目: 782行目:
 
         {
 
         {
 
             var pgm = new Program();
 
             var pgm = new Program();
             pgm.[[Access]]Test();             
+
             pgm.AccessTest();             
 
         }
 
         }
 
   
 
   
         public void [[Access]]Test()
+
         public void AccessTest()
 
         {
 
         {
             // Unable to convert [[MySQL]] date/time value to System.DateTime
+
             // 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 = $"[[Database]]=wordpress;Data Source=192.168.0.2;User Id=root;Password={password}; pooling = false; convert zero datetime=True";
+
             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 (Data[[R]]ow row in table.[[R]]ows)
+
                     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

Ubuntu(apt-getでインストール)

$ sudo apt-get install mysql-server
  • 途中でパスワードの設定を求められる

 

CentOS(yumからインストール)

手順

$ sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm
yum update
$ sudo yum install mysql-community-server

ダウンロード

MySQL Community Server


アカウントの登録

  • 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コマンドを使用する
  • データをテキストファイルとしてダンプするというシンプルな仕組みのコマンド
  • CREATE TABLE および INSERT文として出力される

テーブル名を指定してダンプ

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()