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

MySQL

提供: MyMemoWiki
ナビゲーションに移動 検索に移動

目次

MySQL

Database | CentOS |

インストール

Windows

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

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

0816 mysql password.png

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

権限の初期設定

忘れたパスワードをリセット

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)
  • クライアントからログイン
    • sudo しないとERROR 1698 (28000): Access denied エラー
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...

簡易設定

バージョンの確認

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データベース内で管理している
  • ユーザーがアクセスする際にはアクセス元のホスト(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 [オプション]

バックアップ

# mysqldump --default-character-set=utf8 -uroot -p mt > mt_backup20140120.sql
Enter password: 

レストア例

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

Tips

管理

データファイルの場所

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

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#から接続

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("");
                    }
                }
            }
        }
    }
}