カテゴリー : MySQL

[MySQL] my.cnf の設定方法 (Mac + Homebrew 編)

Mac に Homebrew でインストールした MySQL の my.cnf を設定する方法をご紹介します。

MySQL

my.cnf ファイルの場所を調べる

まず my.cnf が読み込まれる場所を調べます。

$ mysql --help | grep /my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

読み込まれる順番は左から優先して読み込まれます。自分でカスタマイズするためには /usr/local/etc/my.cnf か ~/.my.cnf に追加するのがおすすめです。

my.cnf をコピーして作成する

次に Homebrew でインストールした MySQL の設定ファイル my*.cnf のある場所を探しましょう。

$ find /usr/local/Cellar/mysql -name "my*.cnf"
/usr/local/Cellar/mysql/5.7.17/support-files/my-default.cnf

my-default.cnf をコピーして使います。

$ cp /usr/local/Cellar/mysql/5.7.17/support-files/my-default.cnf /usr/local/etc/my.cnf

あとは my.cnf の設定をいい感じにすれば完了です。

my.cnf の設定を確認する

my.cnf の設定前後を以下のコマンドで確認できます。

$ mysql -u root -p
mysql> show variables;

以上です。あとは好きなように設定していきましょう。

参考情報

MySQL の文字コードを UTF8 に変更する

MySQL 5.7 で文字コードを UTF8 に変更する手順をご紹介します。

MySQL

文字コードを show variables で確認

まず、現在の文字コードを show variables like ‘character_set%’; コマンドで確認します。

mysql> show variables like 'character_set%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | latin1                                               |
| character_set_connection | latin1                                               |
| character_set_database   | utf8                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | latin1                                               |
| character_set_server     | utf8                                                 |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/5.7.17/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.02 sec)

my.cnf に character_set_* を UTF8 にする設定を追記

次に MySQL の設定ファイル my.cnf に文字コードを UTF8 にする設定を追記します。

以下のような設定を追記してあげれば OK です。

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

MySQL を restart して設定確認

最後に MySQL を再起動して設定反映しましょう。

Linux で再起動する場合

$ service mysqld restart
$ /etc/init.d/mysqld restart

macOS で再起動する場合

$ brew services restart mysql
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
mysql> show variables like 'character_set%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8                                                 |
| character_set_connection | utf8                                                 |
| character_set_database   | utf8                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8                                                 |
| character_set_server     | utf8                                                 |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/5.7.17/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.01 sec)

文字コードの問題にぶつかる前に、最初から UTF8 に設定しておくと安心ですね。

参考情報

[MySQL] database と table の size を確認する

MySQL で database と table の size を確認する SQL をご紹介します。

全 database の size を確認する SQL

全てのデータベースのサイズを確認するクエリです。

# MB
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;
 
+--------------------+---------------+
| table_schema       | MB            |
+--------------------+---------------+
| databaseName       | 1376.11649799 |
| mysql              |    0.67155933 |
| information_schema |    0.00878906 |
| performance_schema |    0.00000000 |
+--------------------+---------------+
 
 
# GB
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;
 
+--------------------+----------------+
| table_schema       | GB             |
+--------------------+----------------+
| databaseName       | 1.343863953836 |
| mysql              | 0.000655819662 |
| information_schema | 0.000008583069 |
| performance_schema | 0.000000000000 |
+--------------------+----------------+

table 単位で size を確認する SQL

特定のデータベースにて、テーブル単位でサイズを確認するクエリです。

USE databaseName;
 
SELECT
TABLE_NAME, engine, table_rows AS tbl_rows, avg_row_length AS rlen,  
FLOOR((data_length+index_length)/1024/1024) AS allMB,  # TOTAL SIZE
FLOOR((data_length)/1024/1024) AS dMB,  # DATA SIZE
FLOOR((index_length)/1024/1024) AS iMB   # INDEX SIZE
FROM information_schema.tables  
WHERE table_schema=DATABASE()  
ORDER BY (data_length+index_length) DESC; 
 
 
+-----------------------------+--------+----------+------+-------+------+------+
| TABLE_NAME                  | engine | tbl_rows | rlen | allMB | dMB  | iMB  |
+-----------------------------+--------+----------+------+-------+------+------+
| wp_commentmeta              | MyISAM |  1188843 |  596 |   863 |  824 |   38 |
| wp_comments                 | MyISAM |   312819 | 1067 |   382 |  358 |   24 |
| wp__wsd_plugin_live_traffic | InnoDB |   730696 |  176 |   122 |  122 |    0 |
| wp_posts                    | MyISAM |     2542 | 1910 |     4 |    4 |    0 |
| wp_postmeta                 | MyISAM |    13165 |   55 |     1 |    0 |    0 |
| wp_options                  | MyISAM |      237 | 1410 |     0 |    0 |    0 |
| wp_term_relationships       | MyISAM |     3553 |   21 |     0 |    0 |    0 |
| wp_terms                    | MyISAM |      800 |   46 |     0 |    0 |    0 |
| wp_redirection_logs         | InnoDB |        0 |    0 |     0 |    0 |    0 |
| wp_redirection_items        | InnoDB |        0 |    0 |     0 |    0 |    0 |
| wp_term_taxonomy            | MyISAM |      839 |   39 |     0 |    0 |    0 |
| wp__wsd_plugin_alerts       | InnoDB |       19 | 2586 |     0 |    0 |    0 |
| wp_redirection_modules      | InnoDB |        3 | 5461 |     0 |    0 |    0 |
| wp_redirection_groups       | InnoDB |        2 | 8192 |     0 |    0 |    0 |
| wp_usermeta                 | MyISAM |       29 |  519 |     0 |    0 |    0 |
| wp_sitemeta                 | MyISAM |       26 |  115 |     0 |    0 |    0 |
| wp_site                     | MyISAM |        1 |   28 |     0 |    0 |    0 |
| wp_users                    | MyISAM |        1 |  100 |     0 |    0 |    0 |
| wp_blogs                    | MyISAM |        1 |   48 |     0 |    0 |    0 |
| wp_tweet_urls               | MyISAM |        0 |    0 |     0 |    0 |    0 |
| wp_signups                  | MyISAM |        0 |    0 |     0 |    0 |    0 |
| wp_links                    | MyISAM |        7 |   77 |     0 |    0 |    0 |
| wp_blog_versions            | MyISAM |        0 |    0 |     0 |    0 |    0 |
| wp_registration_log         | MyISAM |        0 |    0 |     0 |    0 |    0 |
+-----------------------------+--------+----------+------+-------+------+------+

stored procedure に SQL を登録する

毎回、長い SQL を実行するのは面倒なのでストアドプロシージャに登録しておくと便利です。

show_table_size() という名前で登録する SQL

delimiter //
CREATE PROCEDURE show_table_size()
BEGIN
SELECT
TABLE_NAME, engine, table_rows AS tbl_rows, avg_row_length AS rlen,  
FLOOR((data_length+index_length)/1024/1024) AS allMB,  # total SIZE
FLOOR((data_length)/1024/1024) AS dMB,  # DATA SIZE
FLOOR((index_length)/1024/1024) AS iMB   # INDEX SIZE
FROM information_schema.tables  
WHERE table_schema=DATABASE()  
ORDER BY (data_length+index_length) DESC; 
END
//
delimiter ;

登録した stored procedure を使う

CALL show_table_size;

以上です。

参考情報

さくらVPS(CentOS6.3)で nginx + php-fpm + MySQL で WordPress環境を構築

Nginx のインストール

■ Nginx リポジトリを登録

# vim /etc/yum.repos.d/nginx.repo
 
[nginx]
name=nginx
baseurl=http://nginx.org/packages/centos/$releasever/$basearch/
gpgcheck=0
enabled=0

■ インストール可能な Nginx のバージョン確認

# yum info --enablerepo=nginx nginx

■ Nginx のインストール

# yum -y install --enablerepo=nginx nginx

Nginx が正常にインストールされたことを確認する。

# nginx -v
nginx version: nginx/1.4.2

■ Nginx の各種設定

# vim /etc/nginx/nginx.conf
user  nginx;
worker_processes  2;
 
error_log  /var/log/nginx/error.log warn;
pid        /var/run/nginx.pid;
 
 
events {
    worker_connections  1024;
}
 
 
http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;
 
    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';
 
    access_log  /var/log/nginx/access.log  main;
 
    sendfile        on;
    #tcp_nopush     on;
 
    keepalive_timeout  65;
 
    # gzip
    gzip  on;
    gzip_http_version 1.0;
    gzip_vary         on;
    gzip_comp_level   6;
    gzip_types        text/xml text/css application/xhtml+xml application/xml application/rss+xml application/atom_xml application/x-javascript application/x-httpd-php;
    gzip_disable      "MSIE [1-6]\.";
 
    # reverse proxy
    proxy_cache_path  /var/cache/nginx levels=1:2 keys_zone=czone:4m max_size=50m inactive=120m;
    proxy_temp_path   /var/tmp/nginx;
    proxy_cache_key   "$scheme://$host$request_uri";
    proxy_set_header  Host               $host;
    proxy_set_header  X-Real-IP          $remote_addr;
    proxy_set_header  X-Forwarded-Host   $host;
    proxy_set_header  X-Forwarded-Server $host;
    proxy_set_header  X-Forwarded-For    $proxy_add_x_forwarded_for;
 
    upstream backend {
        ip_hash;
        server 127.0.0.1:8080;
    }
 
    include /etc/nginx/conf.d/*.conf;
}
# vim /etc/nginx/conf.d/virtual.conf
server {
 
    listen      80;
    server_name example.com;
    root        /var/www/vhosts/example.com/www;
    access_log  /var/log/nginx/example.com/access.log   main;
    error_log   /var/log/nginx/example.com/error.log;
    client_max_body_size 36M;
 
    location /wp-admin { proxy_pass http://backend; }
    location ~ .*\.php { proxy_pass http://backend; }
    location / {
        set $mobile "";
        if ($http_user_agent ~* '(DoCoMo|J-PHONE|Vodafone|MOT-|UP\.Browser|DDIPOCKET|ASTEL|PDXGW|Palmscape|Xiino|sharp pda browser|Windows CE|L-mode|WILLCOM|SoftBank|Semulator|Vemulator|J-EMULATOR|emobile|mixi-mobile-converter)') {
            set $mobile "@ktai";
        }
        if ($http_user_agent ~* '(iPhone|iPod|Opera Mini|Android.*Mobile|NetFront|PSP|BlackBerry)') {
            set $mobile "@mobile";
        }
        if ($http_cookie ~* "comment_author_|wordpress_(?!test_cookie)|wp-postpass_" ) {
            set $do_not_cache 1;
        }
 
        proxy_no_cache     $do_not_cache;
        proxy_cache_bypass $do_not_cache;
        proxy_cache czone;
        proxy_cache_key "$scheme://$host$request_uri$is_args$args$mobile";
        proxy_cache_valid  200 301 302 60m;
        proxy_cache_valid  404 5m;
        proxy_cache_use_stale  error timeout invalid_header updating
                               http_500 http_502 http_503 http_504;
        proxy_pass http://backend;
        proxy_redirect http://example.com:8080/ /;
    }
}
 
server {
 
    listen      8080;
    server_name example.com;
    root        /var/www/vhosts/example.com/www;
    access_log  /var/log/nginx/example.com/access.log   main;
    error_log   /var/log/nginx/example.com/error.log;
    client_max_body_size 36M;
 
    location / {
        index  index.php index.html index.htm;
        # static files
        if (-f $request_filename) {
            expires 14d;
            break;
        }
        # request to index.php
        if (!-e $request_filename) {
            rewrite ^(.+)$  /index.php?q=$1 last;
        }
    }
 
    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    location ~ \.php$ {
        include        fastcgi_params;
        fastcgi_pass   127.0.0.1:9000;
        fastcgi_param  SCRIPT_FILENAME  /var/www/vhosts/example.com/www/$fastcgi_script_name;
    }
}

ログディレクトリを作成します。

mkdir /var/log/nginx/example.com

設定ファイル編集後、文法チェックを行います。

# nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

Port8080 を開放するためにファイアーウォールの設定に追記します。

# vim /etc/sysconfig/iptables
 
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 8080  -j ACCEPT

■ Nginx の自動起動設定

chkconfig nginx on

MySQL のインストール

■ インストール可能な MySQL のバージョン確認

# yum info mysql-server

■ MySQL server のインストール

yum のパッケージは mysql ではなく、mysql-server なので間違えないように。

# yum -y install --enablerepo=remi mysql-server

■ MySQL 設定ファイルの修正

# cp /etc/my.cnf /etc/my.cnf.default
# vim /etc/my.cnf
 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
# Settings user and group are ignored when systemd is used (fedora >= 15).
# 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
user=mysql
 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
 
# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so
 
# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1
 
# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema
 
character-set-server=utf8
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
 
# ここから下を追記する
 
[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqldump]
default-character-set=utf8

■ MySQL の起動&自動起動の設定

service mysqld start
chkconfig mysqld on

■ MySQL のパスワード設定

# mysqladmin -u root password
# mysqladmin -u root -h hostname password

PHP のインストール

■ インストール可能な PHP のバージョン確認

# yum info php php-mbstring php-mysql php-pear php-gd php-pecl-apc php-fpm

■ PHP のインストール

# yum -y install php php-mbstring php-mysql php-pear php-gd php-pecl-apc php-fpm

PHP が正常にインストールされたことを確認する。

# php -v
PHP 5.3.15 (cli) (built: Jul 20 2012 12:50:06) 
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

■ php.ini の設定変更

# vim /etc/php.ini
expose_php = Off
 
error_log = /var/log/php_errors.log
 
mbstring.language = Japanese
mbstring.internal_encoding = UTF-8
mbstring.http_input = pass
mbstring.http_output = pass
mbstring.encoding_translation = Off
 
[Date]
date.timezone = Asia/Tokyo

ログファイルの作成

# touch /var/log/php_error.log
# chmod 757 /var/log/php_error.log

■ APC の設定

mkdir -p /var/www/vhosts/example.com
cp /usr/share/doc/php-pecl-apc-3.1.10/apc.php /var/www/vhosts/example.com/
vim /var/www/vhosts/example.com/apc.php 
 
defaults('ADMIN_USERNAME','user');
defaults('ADMIN_PASSWORD','password');

■ Fast CGI のインストール確認

php-fpm が正常にインストールされたことを確認する。

# php-fpm -v
PHP 5.3.15 (fpm-fcgi) (built: Jul 20 2012 12:52:37)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

■ php-fpm の各種設定

# vim /etc/php-fpm.d/www.conf
user = nginx
group = nginx
 
pm = dynamic
pm.max_children =  30
pm.start_servers = 11
pm.min_spare_servers = 10
pm.max_spare_servers = 15
pm.max_requests = 500
 
php_admin_flag[expose_php] = off

php-fpm の起動

service php-fpm start

php-fpm の自動起動設定

chkconfig php-fpm on
# netstat -ltn | grep -E '(80|9000)'
tcp        0      0 127.0.0.1:9000              0.0.0.0:*                   LISTEN      
tcp        0      0 0.0.0.0:80                  0.0.0.0:*                   LISTEN

以上です。


参考情報

さくらの VPS に nginx を入れてリバースプロキシ設定するまでの作業メモ(検証用) | ウェブル

nginx+php-fpmをyumでインストールして、WordPress/CakePHPを動かす設定 – Shin x blog

さくらVPS(CentOS6.2)でnginx + spawn-fcgi + MySQLでWordPress環境を構築【その2】 | 青田んぼ.net

こちらの設定の方が良いかも。

Nginxを使ったもう一歩進んだWordPressチューニング | cloudrop

[MySQL] 予約語でハマる – ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘xxx’ at line 1

MySQL でどう見ても大丈夫そうな SQL を実行したら文法エラーが発生しました。

ERROR 1064 (42000): You have an error in your SQL syntax;

mysql> SELECT * FROM addresses ORDER BY primary;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary' at line 1

primary は予約語

primary が予約語らしいので、シングルクォートで囲んで実行すれば大丈夫でした。

mysql> SELECT * FROM addresses ORDER BY 'primary';

参考情報