サブクエリ limit

select * from road_times where id  in (
  select id from (
    select id from road_times where road_id = 1 limit 10
  ) as tmp
)

ツール

利用履歴

2010-2015MySQL 5.1
2015-2019Mariadb 5.5(MySQL 5.5相当)
2018-2019仕事場でMariaDB 10.2.9

8.0

A temporary password is generated for root@localhost: A.rr9-,mUtyX

 ALTER USER 'root'@'localhost' identified BY 'Txxxxxxx9999_';

Percona(5.7)移行

install

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum install Percona-Server-server-57.x86_64
pt-online-schema-change --ask-pass --alter="ADD TEST VARCHAR(100)" h=localhost,D=cakephp,t=words,u=root --dry-run
pt-online-schema-change --ask-pass --alter="add column comment2 varchar(10) not null default 'fuga'" h=localhost,D=test,t=dummy,u=root --execute

trouble

オンラインDDL変更

MySQL 5.6からオンラインDDL変更ができるようになったが、pt-online-schema-changeも健在 Percona Toolkitに同梱

https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/redhat/7/x86_64/percona-toolkit-3.0.10-1.el7.x86_64.rpm

概念

Top 50メモ

便利な関数

便利なツール

セキュリティのために

binログからの復旧

mysqlbinlog -uroot -p -D /var/lib/mysql/mysql-bin.000002 --start-datetime="2017-05-12 20:30:00" --stop-datetime="2017-05-12 20:40:00" > 2030.sql

オペレーションミスで消すというアフォなことをしてしまった。たまたまレプリケーションしてたほうがdelete反映が遅れた(60分後でも平気)ので救われた。

文字コード

PostgreSQL等と違ってテーブルクリエート時に指定できないのだろうか?/etc/my.cnfをいちいち書き換えないと駄目なのか?

mysql> status

SHOW VARIABLES LIKE 'character\_set\_%';
create database codezine default charset utf8;

クライアント側の設定

通常のmysqlで実行される文字コードは初期設定だとlatin1。これで文字化けしないわけがないので適宜文字コードを設定しておく。

mysql> status;
mysql> set character set utf8;

mysql.iniでの文字コードの設定

デフォルトのcharsetを設定ファイルに記載しておくと手間が省ける

[mysql]
default-character-set=文字コード
[mysqld]
default-character-set=文字コード
[client]
default-character-set=文字コード

接続ログを出したい

最大接続数・現在の接続数を確認したい

show global variables like '%connection%';
show status like 'Threads_connected';

ERROR 1040 (08004): Too many connectionsがでて繋げない場合はnetstatにて

netstat -anp | grep 3306 

デフォルト接続設定 ~/.my.cnf

[client]
host=localhost
database=mysql
user=root
password=hogehoge

管理ツール

Windowsで出来るGUIツールを開発元が配布しているのが心強い。MySQL AdministratorやMySQL Query Browser等がある。

データベース作成、ユーザ作成などの主に管理系タスク

データベース

mysqlのコマンドラインから

文字コードを指定しないとlatin1になってしまう。

DB作成mysql> create database wordpress default character set utf8;

コマンドラインから

DB一覧mysqlshow
DB作成mysqladmin -u root create TEST
DB削除mysqladmin -u root drop TEST
オートコミット確認。0以外はオートコミットSELECT @@autocommit;
オートコミット無効SET AUTOCOMMIT=0;
ファイルの実行mysql -u root -phogehoge < sql.sql > out.txt
mysqlクライアントから実行mysql>source sql.sql

ユーザ作成、権限付与

ユーザの考え方が特殊でなれないとハマる。ユーザ名+アクセス元ホスト名分だけユーザが存在すると考えておいた方がよい。ワイルドカードで指定する事も出来るが、localhostとその他のホストは分けることが出来ない。自宅サーバなら殆どlocalhostのユーザだけで事足りるのだろうが、他の場所からアクセスさせるなら要注意。

コマンド集

インポートload data infile 'c:/ken_all.csv' into table テーブル名
エクスポートselect * from テーブル名 into outfile '絶対パスでファイル名'
データベース毎の使用量select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables group by table_schema order by sum(data_length+index_length) desc
DB接続mysql -u root -p DB_NAME
ユーザ作成mysql> grant select,insert,delete,update,create,drop,file,alter,index on *.* to ユーザ名 identified by 'パスワード';
バッチ処理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(200) not null [DEFAULT 0] [AFTER <カラム名>];
列削除ALTER TABLE テーブル名 DROP フィールド名
シーケンスを任意の値にALTER TABLE tbl AUTO_INCREMENT = 100;
曜日取得(日曜1-土曜7)SELECT DAYOFWEEK(now());
曜日取得(月曜0-日曜6)SELECT WEEKDAY(now());

テーブル構造やデータ型

Tableの形式

CREATE TABLE animals (
            id MEDIUMINT NOT NULL AUTO_INCREMENT,
            name CHAR(30) NOT NULL,
            PRIMARY KEY (id)
            ) engine=InnoDB;

データ型

順序型

順序に任意の値を設定

alter table table_name auto_increment=12345;

注意点

データベース名やテーブル名は大文字小文字を区別する。

これはOSが大文字小文字区別するならば、DBとdbは別のデータベースとして取扱われる。テーブルに付いても同様である。WINDOWSのように区別しないOSの場合はその心配はない

総使用量

select table_schema, sum(data_length+index_length) /1024 /1024/1024 as GB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;

GUI管理ツール

phpMyAdmin

WEBからDB管理ができてしまうというツール。日本語化されていて、解凍即実行可能。海外ではよく使われているのかphpmyadminへの不正アクセスが多数・・・・

PHP 4,5.1では2.9系、PHP 5.2では3.x系を利用できる。

CentOS5では5.1.6なので2.xを使う。

その他MySQLのバージョン指定もかなり厳しくCentOSを使っているなら古いバージョンを使ったほうがよい。

  1. 解凍する。3.x系ならアクセスすれば自動で設定画面に飛ぶと思った。

トラブルシューティング

文字コード間違えてテーブル作ってしまった。

ALTER TABLE  テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

パスワードを忘れて再設定したい

起動引数に--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='root';
mysql> flush privileges;

FAQ

パスワード忘れた

mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root';
mysql> flush-privileges;
これでパスワード認証を求められる
mysql -u root -p xoops
/var/lib/mysql/mysql以下を全部消してinitする

SQLファイル、Javaからの実行時のみ文字化け

mysqlコンソールからは大丈夫だが、ファイル実行させるとだめ、設定がほとんどlatin1になっていたのが原因。

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/ |
+--------------------------+----------------------------+
mysql> CHARSET utf8
Charset changed
mysql> SHOW VARIABLES LIKE 'character\_set\_%';
Counter: 17701, today: 2, yesterday: 1

トップ   編集 凍結 差分 履歴 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2022-10-14 (金) 13:16:53