DB/MySQL
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
-日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bu...
#contents
*サブクエリ limit [#je80204b]
select * from road_times where id in (
select id from (
select id from road_times where road_id = 1 limit 10
) as tmp
)
*ツール [#abf9e455]
-Adminer (1ファイル管理ツールでPHPMyAdmin対抗)
*利用履歴 [#g4c42f5d]
|2010-2015|MySQL 5.1|
|2015-2019|Mariadb 5.5(MySQL 5.5相当)|
|2018-2019|仕事場でMariaDB 10.2.9|
*8.0 [#f1c1ece5]
-パスワードが初回起動時に設定される。/var/lib/mysql
A temporary password is generated for root@localhost: A....
-パスワードポリシーが厳しい
ALTER USER 'root'@'localhost' identified BY 'Txxxxxxx99...
*Percona(5.7)移行 [#y2ff26d0]
**install [#odfb9a2c]
yum install http://www.percona.com/downloads/percona-rel...
yum install Percona-Server-server-57.x86_64
pt-online-schema-change --ask-pass --alter="ADD TEST VAR...
pt-online-schema-change --ask-pass --alter="add column c...
**trouble [#te5162ed]
-mysqlのgeneral_logの定義でエラー！
-wordpressで「お使いのサーバーの PHP では WordPress に必...
*オンラインDDL変更 [#cd1b7d48]
MySQL 5.6からオンラインDDL変更ができるようになったが、pt-...
Percona Toolkitに同梱
-pt-online-schema-change(旧テーブルにトリガーを作成して、...
https://www.percona.com/downloads/percona-toolkit/3.0.10/...
**概念 [#g058f302]
-空の新テーブル作成
-トリガーを新テーブルに向ける
-全データコピーが終わったら元のテーブルをリネームして、新...
*Top 50メモ [#ab0d657d]
-シーケンスが上限に達するとエラーになる！
-Heap テーブルスペースはメモリ上に展開する領域。一時テー...
-safe_updateのオプションを有効にするとwhereなしupdateなど...
-TIMESTAMP型はdefault値を指定しないと、更新時に勝手に時刻...
-Oracle DBLink=Federated Table
**便利な関数 [#vc92d783]
-DATEDIFF
-group_concat
-group by RAND()
-SELECT USER()
**便利なツール [#i12ccc72]
-mysql workbench(移行にも)
-mysqlsh(SHELLやJS,Python)
**セキュリティのために [#v5159d43]
-unixアカウント連携
-rootで動かさない
-FILE権限は最小限に
-接続数の上限
*binログからの復旧 [#jebaaf2d]
mysqlbinlog -uroot -p -D /var/lib/mysql/mysql-bin.000002...
オペレーションミスで消すというアフォなことをしてしまった...
*文字コード [#c8b6524f]
PostgreSQL等と違ってテーブルクリエート時に指定できないの...
-mysqlのコンソールの状態でも見ることができる。
mysql> status
-以下のコマンドを打ち込むと現状が見える。
SHOW VARIABLES LIKE 'character\_set\_%';
-テーブル作成の時に指定する(5.0で確認）
create database codezine default charset utf8;
-テーブル作成時の文字コードをみる
show create database データベース名
**クライアント側の設定 [#fa9032fd]
通常のmysqlで実行される文字コードは初期設定だとlatin1。こ...
mysql> status;
mysql> set character set utf8;
**mysql.iniでの文字コードの設定 [#hb3b0d76]
デフォルトのcharsetを設定ファイルに記載しておくと手間が省...
[mysql]
default-character-set=文字コード
[mysqld]
default-character-set=文字コード
[client]
default-character-set=文字コード
**接続ログを出したい [#p59c8f97]
-/etc/my.cnf
[mysqld]
general_log=1
general_log_file="/var/log/mysql_general.log"
*最大接続数・現在の接続数を確認したい [#hdb813c0]
show global variables like '%connection%';
show status like 'Threads_connected';
**ERROR 1040 (08004): Too many connectionsがでて繋げない...
netstat -anp | grep 3306
**デフォルト接続設定 ~/.my.cnf [#g2ec61cb]
[client]
host=localhost
database=mysql
user=root
password=hogehoge
*管理ツール [#i83ef508]
Windowsで出来るGUIツールを開発元が配布しているのが心強い...
*データベース作成、ユーザ作成などの主に管理系タスク [#a52...
**データベース [#x0dddb01]
***mysqlのコマンドラインから [#w8b16c78]
文字コードを指定しないとlatin1になってしまう。
|DB作成|mysql> create database wordpress default characte...
***コマンドラインから [#g6e08aac]
|DB一覧|mysqlshow|
|DB作成|mysqladmin -u root create TEST|
|DB削除|mysqladmin -u root drop TEST|
|オートコミット確認。0以外はオートコミット|SELECT @@autoc...
|オートコミット無効|SET AUTOCOMMIT=0;|
|ファイルの実行|mysql -u root -phogehoge < sql.sql > out....
|mysqlクライアントから実行|mysql>source sql.sql|
**ユーザ作成、権限付与 [#g5bf94f7]
ユーザの考え方が特殊でなれないとハマる。ユーザ名+アクセス...
*コマンド集 [#sc19b022]
|インポート|load data infile 'c:/ken_all.csv' into table ...
|エクスポート|select * from テーブル名 into outfile '絶対...
|データベース毎の使用量|select table_schema, sum(data_len...
|DB接続|mysql -u root -p DB_NAME|
|ユーザ作成|mysql> grant select,insert,delete,update,crea...
|バッチ処理|mysql -u root -p DB_NAME < sql.txt|
|バッチ処理 on シェル|mysql> source ファイル名|
|操作ログをファイルも記録|mysql> \t ログファイル名|
|TABLE一覧|SHOW TABLES;|
|プロセスの確認|SHOW processlist|
|DBダンプ|$ mysqldump -u root データベース名 > dump.sql|
|列追加|ALTER TABLE テーブル名 ADD フィールド名 varchar(2...
|列削除|ALTER TABLE テーブル名 DROP フィールド名|
|シーケンスを任意の値に|ALTER TABLE tbl AUTO_INCREMENT = ...
|曜日取得(日曜1-土曜7)|SELECT DAYOFWEEK(now());|
|曜日取得(月曜0-日曜6)|SELECT WEEKDAY(now());|
*テーブル構造やデータ型 [#x177f3ed]
**Tableの形式 [#v6a964eb]
-InnoDB
--トランザクション対応
-MyISAM
--トランザクション非対応で速度重視。
-INNODB形式でテーブルを作成するサンプル
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) engine=InnoDB;
**データ型 [#r35b4386]
-CHAR,VARCHARは4.1以降文字数。それ以前はバイト数なので日...
-最大以上のサイズを入れた場合、はみだし分が切り落とされる...
**順序型 [#w169db1c]
-フィールドにAUTO_INCREMENTをつける。ただしひとつのテーブ...
**順序に任意の値を設定 [#pacf79b9]
alter table table_name auto_increment=12345;
*注意点 [#kea134ff]
**データベース名やテーブル名は大文字小文字を区別する。 [#...
これはOSが大文字小文字区別するならば、DBとdbは別のデータ...
*総使用量 [#m38311bf]
select table_schema, sum(data_length+index_length) /1024...
*GUI管理ツール [#d0d01049]
**[[phpMyAdmin:http://www.phpmyadmin.net/home_page/index....
WEBからDB管理ができてしまうというツール。日本語化されてい...
PHP 4,5.1では2.9系、PHP 5.2では3.x系を利用できる。
CentOS5では5.1.6なので2.xを使う。
その他MySQLのバージョン指定もかなり厳しくCentOSを使ってい...
-インストール
+解凍する。3.x系ならアクセスすれば自動で設定画面に飛ぶと...
-設定
config.inc.phpでAllowNoPasswordをtrueにしないと、パスワー...
*トラブルシューティング [#y36815c1]
**文字コード間違えてテーブル作ってしまった。 [#c3f56127]
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4...
**パスワードを忘れて再設定したい [#a54a1c1d]
起動引数に--skip-grant-tablesをつける。
centos4.8なら以下のようにする。
/usr/bin/mysqld_safe --skip-grant-tables
mysql -u root
mysql> use mysql
mysql> UPDATE user SET Password=PASSWORD('') WHERE User...
mysql> flush privileges;
*FAQ [#s3050dca]
**パスワード忘れた [#j79ba2da]
-1./etc/my.cnfを編集
[mysqld]
skip-grant-tables
-2.mysqlを再起動
-3.mysqlデータベースに接続
mysql -u root mysql
-4.以下のSQLでパスワード変更
mysql> UPDATE user SET Password=PASSWORD('mynewpassword'...
mysql> flush-privileges;
-mysqldの引数に --skip-grant-tables をつける(ちょっとめん...
しかしこれだと固まりやがる場合があり（超不安定ＤＢ）
mysql> grant all on *.* to root@localhost identified by ...
これでパスワード認証を求められる
mysql -u root -p xoops
/var/lib/mysql/mysql以下を全部消してinitする
**SQLファイル、Javaからの実行時のみ文字化け [#u4a16b93]
mysqlコンソールからは大丈夫だが、ファイル実行させるとだめ...
MariaDB [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
-Clientから利用する場合はclient,connection,resultsの３つ...
mysql> CHARSET utf8
Charset changed
mysql> SHOW VARIABLES LIKE 'character\_set\_%';
-ただし上記は一時的にそのセッションだけ変更するので、再起...
#counter
終了行:
-日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bu...
#contents
*サブクエリ limit [#je80204b]
select * from road_times where id in (
select id from (
select id from road_times where road_id = 1 limit 10
) as tmp
)
*ツール [#abf9e455]
-Adminer (1ファイル管理ツールでPHPMyAdmin対抗)
*利用履歴 [#g4c42f5d]
|2010-2015|MySQL 5.1|
|2015-2019|Mariadb 5.5(MySQL 5.5相当)|
|2018-2019|仕事場でMariaDB 10.2.9|
*8.0 [#f1c1ece5]
-パスワードが初回起動時に設定される。/var/lib/mysql
A temporary password is generated for root@localhost: A....
-パスワードポリシーが厳しい
ALTER USER 'root'@'localhost' identified BY 'Txxxxxxx99...
*Percona(5.7)移行 [#y2ff26d0]
**install [#odfb9a2c]
yum install http://www.percona.com/downloads/percona-rel...
yum install Percona-Server-server-57.x86_64
pt-online-schema-change --ask-pass --alter="ADD TEST VAR...
pt-online-schema-change --ask-pass --alter="add column c...
**trouble [#te5162ed]
-mysqlのgeneral_logの定義でエラー！
-wordpressで「お使いのサーバーの PHP では WordPress に必...
*オンラインDDL変更 [#cd1b7d48]
MySQL 5.6からオンラインDDL変更ができるようになったが、pt-...
Percona Toolkitに同梱
-pt-online-schema-change(旧テーブルにトリガーを作成して、...
https://www.percona.com/downloads/percona-toolkit/3.0.10/...
**概念 [#g058f302]
-空の新テーブル作成
-トリガーを新テーブルに向ける
-全データコピーが終わったら元のテーブルをリネームして、新...
*Top 50メモ [#ab0d657d]
-シーケンスが上限に達するとエラーになる！
-Heap テーブルスペースはメモリ上に展開する領域。一時テー...
-safe_updateのオプションを有効にするとwhereなしupdateなど...
-TIMESTAMP型はdefault値を指定しないと、更新時に勝手に時刻...
-Oracle DBLink=Federated Table
**便利な関数 [#vc92d783]
-DATEDIFF
-group_concat
-group by RAND()
-SELECT USER()
**便利なツール [#i12ccc72]
-mysql workbench(移行にも)
-mysqlsh(SHELLやJS,Python)
**セキュリティのために [#v5159d43]
-unixアカウント連携
-rootで動かさない
-FILE権限は最小限に
-接続数の上限
*binログからの復旧 [#jebaaf2d]
mysqlbinlog -uroot -p -D /var/lib/mysql/mysql-bin.000002...
オペレーションミスで消すというアフォなことをしてしまった...
*文字コード [#c8b6524f]
PostgreSQL等と違ってテーブルクリエート時に指定できないの...
-mysqlのコンソールの状態でも見ることができる。
mysql> status
-以下のコマンドを打ち込むと現状が見える。
SHOW VARIABLES LIKE 'character\_set\_%';
-テーブル作成の時に指定する(5.0で確認）
create database codezine default charset utf8;
-テーブル作成時の文字コードをみる
show create database データベース名
**クライアント側の設定 [#fa9032fd]
通常のmysqlで実行される文字コードは初期設定だとlatin1。こ...
mysql> status;
mysql> set character set utf8;
**mysql.iniでの文字コードの設定 [#hb3b0d76]
デフォルトのcharsetを設定ファイルに記載しておくと手間が省...
[mysql]
default-character-set=文字コード
[mysqld]
default-character-set=文字コード
[client]
default-character-set=文字コード
**接続ログを出したい [#p59c8f97]
-/etc/my.cnf
[mysqld]
general_log=1
general_log_file="/var/log/mysql_general.log"
*最大接続数・現在の接続数を確認したい [#hdb813c0]
show global variables like '%connection%';
show status like 'Threads_connected';
**ERROR 1040 (08004): Too many connectionsがでて繋げない...
netstat -anp | grep 3306
**デフォルト接続設定 ~/.my.cnf [#g2ec61cb]
[client]
host=localhost
database=mysql
user=root
password=hogehoge
*管理ツール [#i83ef508]
Windowsで出来るGUIツールを開発元が配布しているのが心強い...
*データベース作成、ユーザ作成などの主に管理系タスク [#a52...
**データベース [#x0dddb01]
***mysqlのコマンドラインから [#w8b16c78]
文字コードを指定しないとlatin1になってしまう。
|DB作成|mysql> create database wordpress default characte...
***コマンドラインから [#g6e08aac]
|DB一覧|mysqlshow|
|DB作成|mysqladmin -u root create TEST|
|DB削除|mysqladmin -u root drop TEST|
|オートコミット確認。0以外はオートコミット|SELECT @@autoc...
|オートコミット無効|SET AUTOCOMMIT=0;|
|ファイルの実行|mysql -u root -phogehoge < sql.sql > out....
|mysqlクライアントから実行|mysql>source sql.sql|
**ユーザ作成、権限付与 [#g5bf94f7]
ユーザの考え方が特殊でなれないとハマる。ユーザ名+アクセス...
*コマンド集 [#sc19b022]
|インポート|load data infile 'c:/ken_all.csv' into table ...
|エクスポート|select * from テーブル名 into outfile '絶対...
|データベース毎の使用量|select table_schema, sum(data_len...
|DB接続|mysql -u root -p DB_NAME|
|ユーザ作成|mysql> grant select,insert,delete,update,crea...
|バッチ処理|mysql -u root -p DB_NAME < sql.txt|
|バッチ処理 on シェル|mysql> source ファイル名|
|操作ログをファイルも記録|mysql> \t ログファイル名|
|TABLE一覧|SHOW TABLES;|
|プロセスの確認|SHOW processlist|
|DBダンプ|$ mysqldump -u root データベース名 > dump.sql|
|列追加|ALTER TABLE テーブル名 ADD フィールド名 varchar(2...
|列削除|ALTER TABLE テーブル名 DROP フィールド名|
|シーケンスを任意の値に|ALTER TABLE tbl AUTO_INCREMENT = ...
|曜日取得(日曜1-土曜7)|SELECT DAYOFWEEK(now());|
|曜日取得(月曜0-日曜6)|SELECT WEEKDAY(now());|
*テーブル構造やデータ型 [#x177f3ed]
**Tableの形式 [#v6a964eb]
-InnoDB
--トランザクション対応
-MyISAM
--トランザクション非対応で速度重視。
-INNODB形式でテーブルを作成するサンプル
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) engine=InnoDB;
**データ型 [#r35b4386]
-CHAR,VARCHARは4.1以降文字数。それ以前はバイト数なので日...
-最大以上のサイズを入れた場合、はみだし分が切り落とされる...
**順序型 [#w169db1c]
-フィールドにAUTO_INCREMENTをつける。ただしひとつのテーブ...
**順序に任意の値を設定 [#pacf79b9]
alter table table_name auto_increment=12345;
*注意点 [#kea134ff]
**データベース名やテーブル名は大文字小文字を区別する。 [#...
これはOSが大文字小文字区別するならば、DBとdbは別のデータ...
*総使用量 [#m38311bf]
select table_schema, sum(data_length+index_length) /1024...
*GUI管理ツール [#d0d01049]
**[[phpMyAdmin:http://www.phpmyadmin.net/home_page/index....
WEBからDB管理ができてしまうというツール。日本語化されてい...
PHP 4,5.1では2.9系、PHP 5.2では3.x系を利用できる。
CentOS5では5.1.6なので2.xを使う。
その他MySQLのバージョン指定もかなり厳しくCentOSを使ってい...
-インストール
+解凍する。3.x系ならアクセスすれば自動で設定画面に飛ぶと...
-設定
config.inc.phpでAllowNoPasswordをtrueにしないと、パスワー...
*トラブルシューティング [#y36815c1]
**文字コード間違えてテーブル作ってしまった。 [#c3f56127]
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4...
**パスワードを忘れて再設定したい [#a54a1c1d]
起動引数に--skip-grant-tablesをつける。
centos4.8なら以下のようにする。
/usr/bin/mysqld_safe --skip-grant-tables
mysql -u root
mysql> use mysql
mysql> UPDATE user SET Password=PASSWORD('') WHERE User...
mysql> flush privileges;
*FAQ [#s3050dca]
**パスワード忘れた [#j79ba2da]
-1./etc/my.cnfを編集
[mysqld]
skip-grant-tables
-2.mysqlを再起動
-3.mysqlデータベースに接続
mysql -u root mysql
-4.以下のSQLでパスワード変更
mysql> UPDATE user SET Password=PASSWORD('mynewpassword'...
mysql> flush-privileges;
-mysqldの引数に --skip-grant-tables をつける(ちょっとめん...
しかしこれだと固まりやがる場合があり（超不安定ＤＢ）
mysql> grant all on *.* to root@localhost identified by ...
これでパスワード認証を求められる
mysql -u root -p xoops
/var/lib/mysql/mysql以下を全部消してinitする
**SQLファイル、Javaからの実行時のみ文字化け [#u4a16b93]
mysqlコンソールからは大丈夫だが、ファイル実行させるとだめ...
MariaDB [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
-Clientから利用する場合はclient,connection,resultsの３つ...
mysql> CHARSET utf8
Charset changed
mysql> SHOW VARIABLES LIKE 'character\_set\_%';
-ただし上記は一時的にそのセッションだけ変更するので、再起...
#counter
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
