MySQL

| XAMPP |

MySQL

Database | CentOS |

link

• Django インストール 1.5.1
• CentOS 初期設定

インストール

Windows

• Windows8.1にMySQL5.6をインストール

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

$ sudo apt-get install mysql-server

• 途中でパスワードの設定を求められる

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

• https://dev.mysql.com/doc/refman/5.6/ja/linux-installation-yum-repo.html

手順

• 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

ダウンロード

• http://www.mysql.com/
• http://dev.mysql.com/downloads/

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

権限の初期設定

• 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 初期パスワードの場所

• 確認

$ 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

• https://qiita.com/arm_band/items/12208908041a5506d7f4

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...

簡易設定

バージョンの確認

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.77    | 
+-----------+
1 row in set (0.00 sec)

MySQL 簡易設定

• MySQL 簡易設定

データベースの作成

作成

# mysql -u root -p
mysql> create database test_db default character set utf8;
Query OK, 1 row affected (0.00 sec)

文字コード

• https://yoku0825.blogspot.com/2017/04/mysql-801utf8mb4ja0900ascs.html

確認

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

• Ubuntu

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 ＝１２７．０.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

• https://dev.mysql.com/doc/connector-python/en/

Install

• https://dev.mysql.com/doc/connector-python/en/connector-python-installation-binary.html

$ pip install --upgrade pip

Insert

• https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

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

• https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

    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

• phpMyAdmin

管理

データファイルの場所

---

/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

制約を無視してテーブルをtruncateする

---

• 一旦、FOREIGN_KEY_CHECKSを0にする

mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> truncate table hoge;
mysql> SET FOREIGN_KEY_CHECKS=1;

パーティショニング

---

• https://dev.mysql.com/doc/refman/5.6/ja/partitioning-range.html

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

リストパーティショニング

---

• https://dev.mysql.com/doc/refman/5.6/ja/partitioning-list.html

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

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

• http://dev.mysql.com/downloads/connector/

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