MySQL備忘録

  • 外部ファイル実行

mysql -u root < cleanEPP.sql
または
mysql> source ~/DDL/640/mysql5-jbpm-schema.sql
mysql> source ~/DDL/640/quartz_tables_mysql.sql
mysql> source ~/DDL/646/mysql5-jbpm-schema.sql

drop database testbrms531;
create database testbrms531;
GRANT ALL PRIVILEGES ON testbrms531.* TO 'mysql'@'localhost' IDENTIFIED BY 'mysql' WITH GRANT OPTION;
use testbrms531;
drop database bpms640;
create database bpms640;
GRANT ALL PRIVILEGES ON bpms640.* TO 'mysql'@'localhost' IDENTIFIED BY 'mysql' WITH GRANT OPTION;
use bpms640;
drop database rhpam730;
create database rhpam730;
GRANT ALL PRIVILEGES ON rhpam730.* TO 'mysql'@'localhost' IDENTIFIED BY 'mysql' WITH GRANT OPTION;
use rhpam730;

recreatedb.sh

#!/bin/bash
mysql -u root test -e "drop database $1";
mysql -u root test -e "create database $1";
mysql -u root test -e "GRANT ALL PRIVILEGES ON $1.* TO 'mysql'@'localhost' IDENTIFIED BY 'mysql' WITH GRANT OPTION";
mysql -u root test -e "use $1";
  • バックアップ

$ mysqldump -u root -x --all-databases > dump.sql
$ mysqldump -u root データベース名 > dump.sql

  • リストア

$ mysql -u root -p < dump.sql
$ mysql -u root データベース名 < dump.sql

  • インデックス
    • show index from テーブル名
    • ALTER TABLE テーブル名 DROP INDEX インデックス名;
  • 外部キー
    • show create table テーブル名
    • ALTER TABLE テーブル名 DROP FOREIGN KEY FK名;
  • 外部キー制約を無視してtruncateする

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> truncate table xxxx;
mysql> SET FOREIGN_KEY_CHECKS = 1;

create table PARENT (id int, primary key (id));
create table CHILD (id int, p_id int, primary key (id));
insert into PARENT (id) values (1);
insert into CHILD (id, p_id) values (1, 1);
alter table CHILD add constraint fk_child_parent foreign key (p_id) references PARENT (id);
  • Column を ALTER
ALTER TABLE I18NText MODIFY COLUMN shortText VARCHAR(256);

Learn Deadlock

SHOW ENGINE INNODB STATUS\G

MySQL

SELECT * FROM sys.innodb_lock_waits\G

MariaDB

SELECT * FROM information_schema.innodb_lock_waits\G
SELECT * FROM information_schema.innodb_locks\G

/etc/my.cnf

[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8

lower_case_table_names=1

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_connections=400

slow_query_log=TRUE
slow_query_log_file=/var/log/mysql/mysql-slow.log
long-query-time=0
#log-long-format
transaction-isolation = READ-COMMITTED

general-log=TRUE
general_log_file=/var/log/mysql/mysql-general.log

#default-storage-engine=MyISAM

key_buffer = 256M

innodb_file_per_table
innodb_buffer_pool_size=512M
innodb_log_file_size=128M

expire_logs_days = 30


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysql-slow.log/mysql-query.log が作成されない -> /var/log/mysql/ ディレクトリ作ってやる。権限も

show create database test;
alter database test default character set = utf8;

"MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes"
-> とりあえず latin1 にするか

  • エンジン確認

show table status from ;

MySQL Workbench

yum install mysql-workbench-community
  • ER図
    • [Database] -> [Reverse Engineering]

MySQL 5.7

インストール時初期パスワード

# cat /var/log/mysqld.log | grep 'password is generated'

SET PASSWORD = 'Password1!';

http://qiita.com/okuzawats/items/d3cd9f8aca7262715629

int - Date 変換

mysql> select FROM_UNIXTIME(1493095351);
+---------------------------+
| FROM_UNIXTIME(1493095351) |
+---------------------------+
| 2017-04-25 13:42:31       |
+---------------------------+
select FROM_UNIXTIME((select NEXT_FIRE_TIME from qrtz_triggers) /1000);

ロックタイムアウト、デッドロック調査

https://dev.mysql.com/doc/refman/5.6/en/innodb-enabling-monitors.html

SET GLOBAL innodb_lock_wait_timeout = 10;
show variables like 'innodb_lock_wait_timeout';

プロシージャ

DELIMITER //
CREATE PROCEDURE JbpmAmendAutoIncrement()
BEGIN
      SET @max1 = (SELECT MAX(processInstanceId) + 1 FROM ProcessInstanceLog); 
      set @alter_statement1 = concat('ALTER TABLE ProcessInstanceInfo AUTO_INCREMENT = ', @max1);
      PREPARE stmt1 FROM @alter_statement1;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;

      SET @max2 = (SELECT MAX(workItemId) + 1 FROM NodeInstanceLog); 
      set @alter_statement2 = concat('ALTER TABLE WorkItemInfo AUTO_INCREMENT = ', @max2);
      PREPARE stmt2 FROM @alter_statement2;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;

      SET @max3 = (SELECT MAX(processSessionId) + 1 FROM AuditTaskImpl); 
      set @alter_statement3 = concat('ALTER TABLE SessionInfo AUTO_INCREMENT = ', @max3);
      PREPARE stmt3 FROM @alter_statement3;
      EXECUTE stmt3;
      DEALLOCATE PREPARE stmt3;

      SET @max4 = (SELECT MAX(taskId) + 1 FROM AuditTaskImpl); 
      set @alter_statement4 = concat('ALTER TABLE Task AUTO_INCREMENT = ', @max4);
      PREPARE stmt4 FROM @alter_statement4;
      EXECUTE stmt4;
      DEALLOCATE PREPARE stmt4;
END
//
DELIMITER ;

便利コマンド

SHOW CREATE PROCEDURE sample01;

SHOW PROCEDURE STATUS;

DROP PROCEDURE sample01;

起動時に実行する

jbpm-amend-auto-increment.sql

call testbpms640.JbpmAmendAutoIncrement;

/etc/my.cnf

[mysqld]
...
init-file=/var/lib/mysql/DDL/procedure/jbpm-amend-auto-increment-call.sql
...