やる気の無いサイトでゴメンナサイ。
Home » 旧 ConoHa VPS » MySQL

MySQL

ConoHa 側はごくシンプルな構成とし、他の VPS との通信はローカルネットワークで。外部クライアントからは SSH トンネルを掘ってインターネット経由で接続し、あたかもローカルに設置したデータベースのように扱います。

ローカルネットワークの追加

ConoHa でローカルネットワークの設定をしてリブート後、ネットワークインターフェースの設定をします。

# ifconfig -a

として eth1 の MAC アドレスをメモしておきます。

# vi /etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE="eth1"
HWADDR="YourMacAddress"
BOOTPROTO="static"
IPV6INIT="no"
IPADDR="YourLocalAddress"
NETMASK="255.255.255.0"
NM_CONTROLLED="no"
PEERDNS="no"
TYPE="Ethernet"
ONBOOT="yes"
# service network restart

インストールとコンフィグ設定

# yum install -y mysql-server
# vi /etc/my.conf
[client]
default-character-set = utf8

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# default-character-set=utf8
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci

[mysqldump]
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# chkconfig mysqld on
# service mysqld start
# mysql_secure_installation

Enter current password for root (enter for none): Enter
Set root password? [Y/n] y
New password: YourPassword
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

root で接続して動作確認しておきます。

# mysql -uroot -pYourPassword

mysql> select user,host,password from mysql.user;
mysql> show databases;
mysql> exit

ConoHa の標準ディスク構成に合わせて MySQL のデータベースは容量が大きく速度も速い /data 以下に配置して運用します。

# service mysqld stop
# vi /etc/my.cnf
# [client] と [mysqld] をそれぞれ以下のように編集

[client]
socket=/data/mysql.sock

[mysqld]
# datadir=/var/lib/mysql
# socket=/var/lib/mysql/mysql.sock
datadir=/data/mysql
socket=/data/mysql.sock
# mkdir /data/mysql
# chown mysql:mysql /data/mysql
# cp -pR /var/lib/mysql/* /data/mysql
# mv /var/lib/mysql /var/lib/mysql.bak
# service mysqld restart

外部から MySQL に接続するための設定

インターネットを経由して社内や自宅から MySQL のダンプを取ったり phpMyAdmin を使用したりする際は、 SSH トンネルを作成して通信を暗号化します。

MySQL ポートフォワードのために sshd_config を修正

# vi /etc/ssh/sshd_config
X11Forwarding yes
AllowTcpForwarding yes
# service sshd restart

特定のアクセス元のみを許可する

# vi /etc/hosts.allow
sshd : 203.0.113.1 203.0.113.2
mysqld : 192.168.0.
# vi /etc/init.d/firewall
$IPTABLES -A INPUT -p tcp --dport 22 -m state --state NEW -s 203.0.113.1 -j ACCEPT    # SSH
$IPTABLES -A INPUT -p tcp --dport 22 -m state --state NEW -s 203.0.113.2 -j ACCEPT    # SSH

#
# Services for the Localnet
#

$IPTABLES -A INPUT -i eth1 -p tcp --dport 3306 -m state --state NEW -j ACCEPT     # MySQL

接続元の設定

自宅や会社等 ConoHa の MySQL に接続する側の設定です。
先ずは接続の確認をしておきます。

# ssh -i ConoHa.key -2 -4 -N -f -L 3306:localhost:3306 YourAccount@ConoHaHost.example.com -p 22

SSHオプション

接続の確認

# mysql -uroot -pYourPassword -h127.0.0.1 --port=3306

mysql> exit

接続コマンド

PID を調べて SSH トンネルを停止しておきます。

# ps aux|grep ssh
# kill ProcessID

SSH の接続設定を保存しておく

# cp /Path/To/Your/Conoha/Key/ConoHa.key ~/.ssh/ConoHa.key
# vi ~/.ssh/config
Host ConoHaHost
  HostName        ConoHaHost.example.com
  IdentityFile    ~/.ssh/ConoHa.key
  User            YourAccount
  LocalForward    3306 127.0.0.1:3306
  Port            22
  ServerAliveInterval   20
  ServerAliveCountMax   3
  StrictHostKeyChecking no
# chown YourAccount:YourAccount ~/.ssh/ConoHa.key
# chown YourAccount:YourAccount ~/.ssh/config
# chmod 600 ~/.ssh/ConoHa.key
# chmod 644 ~/.ssh/config

autossh の設定

SSH トンネルが切れたときに自動接続する autossh をインストールします。

# yum install autossh

autossh をデーモンとして起動するために起動スクリプトを準備

# vi /etc/init.d/autosshd
#!/bin/bash
#
# autosshd This script starts and stops the autossh daemon
#
# chkconfig: 2345 95 15
# processname: autosshd
# description: autosshd is the autossh daemon.
 
# Source function library.
. /etc/rc.d/init.d/functions
 
# Source networking configuration.
. /etc/sysconfig/network
 
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
 
TUNNEL_SSHCONFIG="/home/YourAccount/.ssh/config"
TUNNEL_HOST="ConoHaHost"
 
export AUTOSSH_PIDFILE="/var/run/autossh.pid"
 
# By default it's all good.
RETVAL=0
 
# Start function.
start() {
    local name=$1
    echo -n $"Starting ${name}: "
    if [ -e "/var/lock/subsys/${name}" ]; then
        if [ -e "/var/run/${name}.pid" ] && [ -e /proc/`cat /var/run/${name}.pid` ]; then
            echo -n $"already exists.";
            failure $"already exists.";
            echo
            return 1
        fi
    fi
    daemon /usr/bin/autossh -M 0 -f -nNT -F ${TUNNEL_SSHCONFIG} ${TUNNEL_HOST}
    RETVAL=$?
    [ $RETVAL -eq 0 ] && touch "/var/lock/subsys/${name}"
    echo
    return $RETVAL
}
 
# Stop function.
stop() {
    local name=$1
    echo -n $"Stopping ${name}: "
    killproc -p "/var/run/${name}.pid" ${name}
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && rm -f "/var/lock/subsys/${name}"
    return $RETVAL
}
 
# See how we were called.
case "$1" in
    start)
        start "autossh"
        ;;
    stop)
        stop "autossh"
        ;;
    restart)
        $0 stop
        sleep 3
        $0 start
        ;;
    status)
        status "autossh"
        RETVAL=$?
        ;;
    *)
        echo $"Usage: $0 {start|stop|restart|status}"
        exit 1
        ;;
esac
 
exit $RETVAL
# chkconfig autosshd on
# service autosshd start

MySQL のデータをリモートでバックアップ

# mkdir /home/mysql
# vi /home/mysql/backup.sh
#!/bin/bash
#backup.sh

cd /root

########## Base Settings ##########
backupDir=/home/mysql/ConoHaHost.example.com
set `date +%y%m%d`
USER=root
PASS=MySQLPassword
HOST=127.0.0.1

########## All Dump ##########
mysqldump -u$USER -p$PASS -h$HOST --all-databases --single-transaction --lock-tables > ${backupDir}/$1.mysql.All.dump;

########## Dump Each DB ##########

for n in `mysql -u$USER -p$PASS -h$HOST -e "show databases" | awk '{ print $0 }'`; do  echo "BackupDirectory >> ${backupDir}/$1.mysql.${n}.dump" 
mysqldump -u$USER -p$PASS -h$HOST --single-transaction --lock-tables ${n} > ${backupDir}/$1.mysql.${n}.dump;
done

########## Keep 30days ##########
set `date --date '30 days ago' '+%y%m%d'`
rm -f ${backupDir}/$1.mysql.*.dump
# chmod 755 /home/mysql/backup.sh
# chown root:root /home/mysql/backup.sh
# mkdir /home/mysql/ConoHaHost.example.com
# vi /etc/crontab
00 3 * * * root /home/mysql/backup.sh

ペットのおうち