MySQL basic setting

Create user

# Add user.
CREATE USER '<user>'@'<from_domain(ipaddress)>' IDENTIFIED BY "<password>";
FLUSH PRIVILEGES;
GRANT ALL ON <db>.* to <user>@'%' IDENTIFIED BY '<user>';
SET PASSWORD FOR '<user>'@'%' = PASSWORD('<user>');

# For Admin user
GRANT ALL PRIVILEGES ON *.* to admin@localhost IDENTIFIED BY '<password>' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* to admin@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION;

# Check user.
SELECT Host, User, Password from mysql.user;

Dump DB

# Dump specifying database.
mysqldump -h <host> -u <user> -p <database name> > ./dump.sql

# Dump all database.
mysqldump -h <host> -u <user> -p --all-databases > dump.sql

# 流し込み時に既存のデータベースを削除する state を定義、外部キーを解除
mysqldump -h <host> -u <user> -p --routines --add-drop-database --disable-keys --databases <database name> | gzip > ./dump.sql.gz

# dump file > mysql
mysql -h <host> -u <user> -p <database name> < ./dump.sql

# Archive は一階層上位のディレクトリで圧縮を行う。
# パス指定で操作すると事故を起こす。
# tarの必須オプションは事故を起こすものが多い為、注意。
tar -cf <filename>.sql.tar <filename>.sql
gzip <filename>.sql.tar

# Restore Database
mysql --default-character-set=utf8 -u <user> -p <database name> < <filename>.sql

Set cnf file (適宜)

/etc/my.cnf

[mysqld]
character-set-server=utf8
slow_query_log=ON

# スロークエリ判定秒数
long_query_time=2

log-slow-queries=/var/log/slow.log

# Idle Time
wait_timeout = 15

# スレッドキャッシュ保持最大数
thread_cache_size = 100

# 同時接続数(デフォルト100)
max_connections = 300

Update RDS parameter group

• character-set-client-handshake: 1
• skip-character-set-client-handshake: 1
• character_set_client: utf8
• character_set_connection: utf8
• character_set_database: utf8
• character_set_filesystem: utf8
• character_set_results: utf8
• character_set_server: utf8
• time_zone: Asia/Tokyo
• collation_connection: utf8_general_ci
• collation_server: utf8_general_ci
• max_connect_errors: 999999999
• max_connections: {DBInstanceClassMemory/12582880} -> 2048 へ変更
• wait_timeout: 100

Update Character code

CREATE DATABASE <database name> DEFAULT CHARACTER SET utf8;
ALTER DATABASE `database_name` character set utf8;
ALTER TABLE `table_name` to character set utf8;

SHOW VARIABLES LIKE "chara%";
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8                                      |
| character_set_filesystem | utf8                                      |
| character_set_results    | utf8                                      |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.5.46.R1/share/charsets/ |
+--------------------------+-------------------------------------------+