MySQL

Table of Contents

リンク

インストール

MariaDB

# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
# dnf install -y MariaDB-server mariadb-devel

MySQL

# yum -y install mysql-server mysql-devel

初期設定

MariaDB

  • /etc/my.cnf.d/mysql-clients.cnf (抜粋) 
    [mysql]
default-character-set = utf8mb4

[mysqladmin]
default-character-set = utf8mb4

[mysqldump]
default-character-set = utf8mb4
  • /etc/my.cnf.d/server.cnf (抜粋) 
    [server]
character-set-server = utf8mb4

[mysqld]
character-set-server = utf8mb4

[mariadb]
character-set-server = utf8mb4
    ## MySQL
  • /etc/my.cnf (抜粋) 
    [mysqld]
default-character-set = utf8
skip-character-set-client-handshake

[mysqldump]
default-character-set = utf8

[mysql]
default-character-set = utf8

[client]
default-character-set = utf8

[odbc]
default-character-set = utf8

MySQL起動

CentOS7,8

# systemctl start mysqld
# systemctl enable mysqld
# systemctl status mysqld

CentOS6

# service mysqld start
# chkconfig mysqld on
# chkconfig --list mysqld

セキュリティ初期設定

# mysql_secure_installation

rootとしてログイン 

# mysql -u root -p

現在登録されているユーザ、ホスト、パスワード確認

SELECT 
	CONVERT(user USING utf8) AS user, 
	CONVERT(host USING utf8) AS host, 
	password 
FROM 
	mysql.user;

rootパスワード登録 

set password for root@localhost=password('rootパスワード');
set password for root@'自分のホスト名'=password('rootパスワード');

一旦ログアウトし、ログインし直してみてパスワードを確認 

mysql> exit
# mysql -u root -p

匿名ユーザ削除 

delete from mysql.user where user='';

既存データベース一覧表示 

show databases;

testデータベース削除 

drop database test;

文字コード設定確認

mysql> status
または 
mysql> show variables like 'char%';

新規データベースおよびユーザの追加

データベース仕様 :データベース|testdb :ユーザ|testuser :パスワード|testpass

rootとしてログイン 

# mysql -u root -p

TakeAsh.netドメイン内全体からのアクセスを許可する場合 

grant all privileges on testdb.* to testuser@'%.TakeAsh.net' identified by 'testpass';

192.168.0.xからのアクセスを許可する場合 

grant all privileges on testdb.* to testuser@'192.168.0.%' identified by 'testpass';

一旦ログアウトし、testuserでログインしてテスト 

mysql> exit
# mysql -u testuser -p

既存データベース一覧表示 

show databases;

新規データベース作成 

create database testdb;

testdbに接続 

use testdb

既存テーブル表示 

show tables;

testtbl作成 

create table testtbl(num int, name varchar(50));

レコード登録 

insert into testtbl values(1,'山田太郎');
insert into testtbl values(2,'岳');

レコード表示 

select * from testtbl;

レコード更新 

update testtbl set name='山田次郎' where num=1;

レコード削除 

delete from testtbl where num=1;

テーブル削除 

drop table testtbl;

root パスワードのリセット

タイムゾーン確認/設定

  • /etc/my.cnf で設定する場合 
    default-time-zone='+09:00'
  • 対話的に設定 
    SET time_zone = '+09:00';
SET time_zone = 'SYSTEM';
  • 確認 
    SELECT @@global.time_zone, @@session.time_zone;

  • 現在時刻の表示 

    SELECT CURDATE(), CURTIME(), NOW();

  • MySQL サーバのタイム ゾーン サポート

ファイヤウォール設定

外部からmysqlへのアクセスを許可する 

3306:tcp

バックアップ

mysqldump コマンド

  • mysqldump コマンド
    データベース/テーブルの内容を SQL スクリプトとして書き出す。 
    $ mysqldump -u ユーザ名 -p データベース名 [テーブル名] -r バックアップファイル名.sql
  • 主なオプション
    • --all-databases, -A
      すべてのデータベース内のすべてのテーブルをダンプする。
    • --force, -f
      テーブルダンプの最中にSQLエラーが発生しても続行する。
    • --result-file=name, -r
      ファイルへ出力。
    • --no-data, -d
      データを出力しない(定義のみを出力)。
    • --xml, -X
      XML として出力。

リストア

テーブルを個別にバックアップ

  • 特定のデータベースについて、テーブル毎に個別にバックアップを行うスクリプト

  • bash 版 

    #!/bin/bash

if [ $# -lt 2 ]; then
  cmd=`basename $0`
  echo "usage: ${cmd} <DataBase> <UserName>"
  exit 1
fi

DataBase=$1
UserName=$2
Dir=Tables

if [ ! -d ${Dir} ]; then
  mkdir ${Dir}
fi

WorkFile=${Dir}/_tables.txt

echo "enter password for ${UserName}:"
read Password

mysql -u "${UserName}" --password="${Password}" \
  --database "${DataBase}" -e "show tables;" > "${WorkFile}"

exec 3< "${WorkFile}"

# drop header line
read Table 0<&3

while read Table 0<&3
do
  echo "${Table}"
  mysqldump -u "${UserName}" --password="${Password}" --force \
    "${DataBase}" "${Table}" > "${Dir}/${Table}.sql"
done

exec 3<&-

  • PowerShell 版 

    # Backup database by tables.

$username = 'testuser'
$database = 'testdb'
$fileTables = '_Tables.txt'
$tablesSkip = @('table', 'names', 'to', 'skip')

$today = Get-Date -Format "yyyyMMdd"
$dirOut = New-Item "C:/Backup/${env:COMPUTERNAME}_${database}_Tables_${today}" -ItemType Directory -Force
Write-Output "Output: ${dirOut}"

$passSecure = Read-Host -AsSecureString "Input password for '${username}'"
$passPlain = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($passSecure))

mysql -u $username --password="$passPlain" --database $database -e "show tables;" >"${dirOut}/${fileTables}" 2>$null

$tables = @(Get-Content "${dirOut}/${fileTables}")
$tables[1..($tables.length - 1)] |
  Where-Object { -not $tablesSkip.Contains($_) } |
  ForEach-Object { Write-Output $_; mysqldump -u $username --password="$passPlain" --force $database $_  -r "${dirOut}/${_}.sql" 2>$null }

ダンプをテーブル毎に分割

  • GitHub:TakeAsh/p-mysqlUtil
  • splitSqlDump.zip 
    #!/usr/bin/perl
# split MySQL dump into each tables.

use strict;
use warnings;
use utf8;
use Encode;

my $charsetConsole = 'CP932';
my $charsetFile    = 'UTF-8';

binmode( STDIN,  ":encoding($charsetConsole)" );
binmode( STDOUT, ":encoding($charsetConsole)" );
binmode( STDERR, ":encoding($charsetConsole)" );

@ARGV = map { decode( $charsetConsole, $_ ); } @ARGV;

my $infile         = $ARGV[0] or die("usage: splitSqlDump.pl <dump.sql>\n");
my $dirSqls        = './sqls/';
my $structuresfile = $dirSqls . '_structures.sql';

mkdir($dirSqls);
unlink <"${dirSqls}*">;

my $regSkip = qr{^\s*(
    LOCK\sTABLES\s`[^`]+`\sWRITE|
    /\*!40000\sALTER\sTABLE\s`[^`]+`\sDISABLE\sKEYS\s\*/|
    /\*!40000\sALTER\sTABLE\s`[^`]+`\sENABLE\sKEYS\s\*/|
    UNLOCK\sTABLES
);}x;
my $regInsert = qr{^\s*INSERT\sINTO\s`(?<table>[^`]+)`\sVALUES\s\([\s\S]+\);};
my $regBreak  = qr{(VALUES\s|\),)(\()};

open( my $fhIn, "<:raw", encode( $charsetConsole, $infile ) )
    or die("$infile: $!");
open( my $fhStructures, ">:raw", encode( $charsetConsole, $structuresfile ) )
    or die("$structuresfile: $!");
my $prevTable = '';
my $fhTable   = undef;
while ( defined( my $line = <$fhIn> ) ) {
    if ( $line =~ $regSkip ) {
        next;
    } elsif ( $line !~ $regInsert ) {
        print $fhStructures $line;
    } else {
        renewTable($1);
        $line =~ s/$regBreak/$1\n\t$2/g;
        print $fhTable $line;
    }
}
close($fhIn);
close($fhStructures);
renewTable('');

sub renewTable {
    my $newTable = shift;
    if ( $prevTable eq $newTable ) {
        return;
    }
    if ($fhTable) {
        print $fhTable "/*!40000 ALTER TABLE `${prevTable}` ENABLE KEYS */;\n";
        print $fhTable "UNLOCK TABLES;\n";
        close($fhTable);
    }
    if ( !$newTable ) {
        return;
    }
    my $tableFile = $dirSqls . $newTable . '.sql';
    open( $fhTable, ">:raw", encode( $charsetConsole, $tableFile ) )
        or die("$tableFile: $!");
    print $fhTable "LOCK TABLES `${newTable}` WRITE;\n";
    print $fhTable "/*!40000 ALTER TABLE `${newTable}` DISABLE KEYS */;\n";
    $prevTable = $newTable;
}

# EOF

テーブルの修復

テーブルの最適化

データベースのリネーム

Windowsからのアクセス

注意) MySQL 5.0.37 / ODBC Driver 3.51.12 / Windows の構成では文字化けする。

ODBCドライバの登録

  1. Connector/ODBC のWindows MSI版をダウンロード
  2. インストール (構成は「Typical」でOK)
  3. 「設定 - コントロールパネル - 管理ツール - データソース(ODBC)」を起動
  4. 「ユーザーDSN」または「システムDSN」を選択
    • 特定のユーザーのみが使用する場合は、「ユーザーDSN」を選択
    • 全ユーザーが使用する場合は、「システムDSN」を選択
  5. 「追加」をクリック
  6. 「MySQL ODBC x.xx Driver」を選択(x.xxはバージョンナンバー)
  7. 「完了」をクリック
  8. 「Login」タブを記入
    1. 「Data Source Name」は任意の名前でOK
      例) MySQL ODBC Connector
    2. 「Description」は任意。空白も可
    3. 「Server」にサーバ名またはIPアドレスを記入。
      例) mysql1.TakeAsh.net
    4. 「User」にユーザ名を記入
      例) testuser
    5. 「Password」にパスワードを記入
      例) testpass
    6. 「Test」をクリック
      サーバ側の設定が正常にできていれば「Success; connection was made!」と表示される。
      エラーダイアログが出た場合は「Diagnostics」をクリックするとエラーメッセージの詳細が出る。
    7. 「Database」を選択
  9. 「Advanced」タブを選択しoptionを設定する
    Connector/ODBC Connection Parameters
    • Microsoft Access、Visual Basic 用推奨設定
      • Don't Optimized Column Width にチェックを入れる
      • Return Matching Rows にチェックを入れる
    • Read Options From my.cnf にチェックを入れる
  10. 「OK」をクリック

Common SQL Environment (CSE)

  • インストール

    1. CSE のダウンロード
      つみきウェブ
    2. 圧縮ファイルを展開し、適当なフォルダに配置する
      例) C:\Program Files\CSE
    3. CSEを実行する
    4. メニューの「ツール - 設定」を選択
    5. 「機能」タブで「SQLエクスプローラ」の「ルートフォルダ」を指定する
      例) C:\Program Files\CSE\SQL

  • データベースへの接続

    1. メニューの「データベース - 接続」を選択
    2. 「DBMS」は「ODBC汎用」を選択
    3. 「データソース」はODBCドライバとして登録したものを選択
      例) MySQL ODBC Connector
    4. 「ユーザ名」にユーザ名を記入
      例) testuser
    5. 「パスワード」にパスワードを記入
      例) testpass
    6. 「OK」をクリック

  • テーブルの全内容を表示

    1. 「DBエクスプローラ」で、「テーブル」を展開
    2. 内容を表示したいテーブルの上で右クリックし「全データを開く」を選択
    3. 「コンソール」に該当テーブルの内容が表示される

  • テーブルの更新

    1. 「コンソール」に表示されたテーブルの該当箇所を更新する
    2. メニューの「データベース - 編集の反映」を選択
    3. 確認ダイアログの「OK」をクリック

  • テーブルのエクスポート

    1. 該当テーブルの上で右クリックし「全データをエクスポート」を選択
    2. フォルダとファイル名を指定し「保存」をクリック
    3. CSVファイルが書き出される (Shift_JIS)

  • データのインポート

    1. インポートしたいテーブルの上で右クリックし「データをインポート」を選択
    2. CSVファイルを指定し「開く」をクリック

  • SQLの実行

    1. 「新規作成」でスクリプトウィンドウを開く
    2. SQLを記述する
    3. メニューの「データベース - 実行」を選択

ODBC/ConnectionString による接続

DRIVER={MySQL ODBC x.xx Driver}; SERVER=サーバ名; DATABASE=データベース名; UID=ユーザ名; PASSWORD=パスワード; OPTION=オプション
例) DRIVER={MySQL ODBC x.xx Driver}; SERVER=mysql1.TakeAsh.net; DATABASE=testdb; UID=testuser; PASSWORD=testpass; OPTION=65539

A5:SQL Mk-2

Home