リンク
-
-
MySQL 5.7 Reference Manual
-
7.3.6 Password Expiration Policy
MySQL 5.7.4 ~ 5.7.10 は既定で360日後にパスワードが失効する。
-
7.3.6 Password Expiration Policy
-
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 リファレンスマニュアル
- Connector/ODBC
-
MySQL 5.7 Reference Manual
- DBIx-Custom
インストール
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 (抜粋)
## MySQL[server] character-set-server = utf8mb4 [mysqld] character-set-server = utf8mb4 [mariadb] character-set-server = utf8mb4
- /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 パスワードのリセット
空のパスワードを設定することで、パスワード無しでログインできるようになる。
作業終了後はパスワードを再設定すること。> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('');
MySQL 5.1 リファレンスマニュアル :: B.1.4.1 How to Reset the Root Password
- MySQL 4.1 リファレンスマニュアル :: A.4.2 忘れたルートパスワードをリセットする方法
タイムゾーン確認/設定
- /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へのアクセスを許可する
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 として出力。
- --all-databases, -A
リストア
文字コードを指定してリストア
$ 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 版
#!/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ドライバの登録
- Connector/ODBC のWindows MSI版をダウンロード
- インストール (構成は「Typical」でOK)
- 「設定 - コントロールパネル - 管理ツール - データソース(ODBC)」を起動
- 「ユーザーDSN」または「システムDSN」を選択
- 特定のユーザーのみが使用する場合は、「ユーザーDSN」を選択
- 全ユーザーが使用する場合は、「システムDSN」を選択
- 「追加」をクリック
- 「MySQL ODBC x.xx Driver」を選択(x.xxはバージョンナンバー)
- 「完了」をクリック
- 「Login」タブを記入
- 「Data Source Name」は任意の名前でOK
例) MySQL ODBC Connector - 「Description」は任意。空白も可
- 「Server」にサーバ名またはIPアドレスを記入。
例) mysql1.TakeAsh.net - 「User」にユーザ名を記入
例) testuser - 「Password」にパスワードを記入
例) testpass - 「Test」をクリック
サーバ側の設定が正常にできていれば「Success; connection was made!」と表示される。
エラーダイアログが出た場合は「Diagnostics」をクリックするとエラーメッセージの詳細が出る。 - 「Database」を選択
- 「Data Source Name」は任意の名前でOK
- 「Advanced」タブを選択しoptionを設定する
Connector/ODBC Connection Parameters- Microsoft Access、Visual Basic 用推奨設定
- Don't Optimized Column Width にチェックを入れる
- Return Matching Rows にチェックを入れる
- Read Options From my.cnf にチェックを入れる
- Microsoft Access、Visual Basic 用推奨設定
- 「OK」をクリック
Common SQL Environment (CSE)
-
インストール
- CSE のダウンロード
つみきウェブ - 圧縮ファイルを展開し、適当なフォルダに配置する
例) C:\Program Files\CSE - CSEを実行する
- メニューの「ツール - 設定」を選択
- 「機能」タブで「SQLエクスプローラ」の「ルートフォルダ」を指定する
例) C:\Program Files\CSE\SQL
- CSE のダウンロード
-
データベースへの接続
- メニューの「データベース - 接続」を選択
- 「DBMS」は「ODBC汎用」を選択
- 「データソース」はODBCドライバとして登録したものを選択
例) MySQL ODBC Connector - 「ユーザ名」にユーザ名を記入
例) testuser - 「パスワード」にパスワードを記入
例) testpass - 「OK」をクリック
-
テーブルの全内容を表示
- 「DBエクスプローラ」で、「テーブル」を展開
- 内容を表示したいテーブルの上で右クリックし「全データを開く」を選択
- 「コンソール」に該当テーブルの内容が表示される
-
テーブルの更新
- 「コンソール」に表示されたテーブルの該当箇所を更新する
- メニューの「データベース - 編集の反映」を選択
- 確認ダイアログの「OK」をクリック
-
テーブルのエクスポート
- 該当テーブルの上で右クリックし「全データをエクスポート」を選択
- フォルダとファイル名を指定し「保存」をクリック
- CSVファイルが書き出される (Shift_JIS)
-
データのインポート
- インポートしたいテーブルの上で右クリックし「データをインポート」を選択
- CSVファイルを指定し「開く」をクリック
-
SQLの実行
- 「新規作成」でスクリプトウィンドウを開く
- SQLを記述する
- メニューの「データベース - 実行」を選択
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図ツール
後で試す。