MySQL

リンク

MySQL AB
- MySQL 5.7 Reference Manual
  - 7.3.6 Password Expiration Policy
    MySQL 5.7.4 ～ 5.7.10 は既定で360日後にパスワードが失効する。
  - 11.1.9.3 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
    character-set = utf8 だと BMP 内の文字(最大3バイト)しか格納できない。
    BMP 外の文字(絵文字等)を格納するには character-set = utf8mb4 を使う必要がある。
- MySQL 5.6 リファレンスマニュアル
  - Unicode 文字セット
  - 文字列関数 ## Split delimited strings
- MySQL 5.1 リファレンスマニュアル
  - INSERT ... ON DUPLICATE KEY UPDATE 構文
- Connector/ODBC
日本MySQLユーザ会

MySQLステップアップ講座：EnterpriseZine

How to split the name string in mysql? - Stack Overflow

インストール

# yum -y install mysql-server

▲ ▼

初期設定

/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起動

# service mysqld start

mysqld自動起動設定

# 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パスワード登録

  1
  2

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

レコード登録

  1
  2

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 パスワードのリセット

空のパスワードを設定することで、パスワード無しでログインできるようになる。
作業終了後はパスワードを再設定すること。
```
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('');
```

▲ ▼

タイムゾーン確認/設定

/etc/my.cnf で設定する場合
```
default-time-zone='+09:00'
```

対話的に設定

  1
  2

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 コマンド

データベース/テーブルの内容を SQL スクリプトとして書き出す。

$ mysqldump -u ユーザ名 -p データベース名 [テーブル名] -r バックアップファイル名.sql

主なオプション
- --all-databases, -A
  すべてのデータベース内のすべてのテーブルをダンプする。
- --force, -f
  テーブルダンプの最中にSQLエラーが発生しても続行する。
- --result-file=name, -r
  ファイルへ出力。
- --no-data, -d
  データを出力しない(定義のみを出力)。
- --xml, -X
  XML として出力。

▲ ▼

リストア

文字コードを指定してリストア

$ mysql -u ユーザ名 -p --default-character-set=utf8 データベース名
mysql> SET names 'utf8';
mysql> SOURCE バックアップファイル名.sql;

Dumping and importing from/to MySQL in an UTF-8 safe way - makandropedia

▲ ▼

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

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

bash 版

dump.zip

#!/bin/bash
UserName=testuser
DataBase=testdb
WorkFile=tables.txt
 
echo enter database password:
read PASSWORD
 
mysql -u $UserName --password=$PASSWORD --database $DataBase -e "show tables;" > $WorkFile
 
exec 3< $WorkFile
 
# drop header line
read LINE 0<&3
 
while read LINE 0<&3
do
echo ${LINE}
mysqldump -u $UserName --password=$PASSWORD --force $DataBase ${LINE} > Tables/${LINE}.sql
done
 
exec 3<&-

シェル・スクリプト・リファレンス - 【ファイルからの読み込み】：ITpro

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

▲ ▼

テーブルの修復

▲ ▼

テーブルの最適化

OPTIMIZE TABLE

▲ ▼

データベースのリネーム

How do I quickly rename a MySQL database (change schema name)? - Stack Overflow
- MySQL :: WL#4030: Deprecate RENAME DATABASE: replace with ALTER DATABASE <name> UPGRADE

▲ ▼

Windowsからのアクセス

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

▲ ▼

ODBCドライバの登録

Connector/ODBC のWindows MSI版をダウンロード
インストール (構成は「Typical」でOK)
「設定 - コントロールパネル - 管理ツール - データソース(ODBC)」を起動
「ユーザーDSN」または「システムDSN」を選択
- 特定のユーザーのみが使用する場合は、「ユーザーDSN」を選択
- 全ユーザーが使用する場合は、「システムDSN」を選択
「追加」をクリック
「MySQL ODBC x.xx Driver」を選択(x.xxはバージョンナンバー)
「完了」をクリック
「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」を選択
「Advanced」タブを選択しoptionを設定する
Connector/ODBC Connection Parameters
- Microsoft Access、Visual Basic 用推奨設定
  - Don't Optimized Column Width にチェックを入れる
  - Return Matching Rows にチェックを入れる
- Read Options From my.cnf にチェックを入れる
「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

松のページ
- A5:SQL Mk-2 フリーの汎用SQL開発ツール/ER図ツール

後で試す。

最新の25件

リンク