MySQLデータベースの構築と運用ガイド

MySQLサーバーの構築手順

MySQLのインストールと設定

パッケージのインストール

# yum -y install autoconf libaio libaio-devel
# groupadd dbuser
# useradd -r -g dbuser -s /sbin/nologin dbuser
# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
# tar -zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
# mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql-5.6.36
# ln -s /usr/local/mysql-5.6.36 /usr/local/mysql
# chown -R dbuser:dbuser /usr/local/mysql-5.6.36/

設定ファイルの作成

# vim /data/3306/my.cnf
[client]
port            = 3306
socket          = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user    = dbuser
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
tmpdir = /tmp
open_files_limit    = 65535
character-set-server = utf8mb4
back_log = 500
max_connections = 3000
max_connect_errors = 10000
max_allowed_packet = 8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 512M
max_heap_table_size = 256M
table_open_cache = 512
log_error=/data/3306/mysql_3306.err
slow_query_log_file = /data/3306/mysql-slow.log
slow_query_log = 1
long_query_time = 0.5

pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 2M
binlog_format = row
log-slave-updates
max_binlog_cache_size = 4M
max_binlog_size = 256M
expire_logs_days = 7

skip-name-resolve
skip-host-cache
replicate-ignore-db = mysql

server-id = 71

innodb_additional_mem_pool_size = 8M
innodb_buffer_pool_size = 16G
innodb_data_file_path = ibdata1:128M;ibdata2:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_file_per_table = 1

[mysqldump]
quick
max_allowed_packet = 8M

データベースの初期化

# chown dbuser.dbuser -R /data/3306/
# cd /usr/local/mysql/scripts/
# ./mysql_install_db \
--defaults-file=/data/3306/my.cnf \
--basedir=/usr/local/mysql/ \
--datadir=/data/3306/data/ --user=dbuser

# 環境変数の設定
# echo 'export PATH=/usr/local/mysql/bin/:$PATH' >> /etc/profile
# source /etc/profile

データベースの起動

# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &

パスワードの設定

mysqladmin -uroot password Secure123 -S /data/3306/mysql.sock

起動スクリプトの作成

#!/bin/bash
port=3306
db_user="root"
db_pass="Secure123"
cmd_path="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"

function start_db() {
    if [ ! -e "$mysql_sock" ]; then
        echo "Starting MySQL..."
        /bin/sh ${cmd_path}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
    else
        echo "MySQL is already running..."
        exit
    fi
}

function stop_db() {
    if [ ! -e "$mysql_sock" ]; then
        echo "MySQL is stopped..."
    else
        echo "Stopping MySQL..."
        ${cmd_path}/mysqladmin -u ${db_user} -p${db_pass} -S /data/${port}/mysql.sock shutdown
    fi
}

function restart_db() {
    echo "Restarting MySQL..."
    stop_db
    sleep 2
    start_db
}

case $1 in
start)
    start_db
    ;;
stop)
    stop_db
    ;;
restart)
    restart_db
    ;;
*)
    echo "Usage: /data/${port}/mysql {start|stop|restart}"
esac

MySQLバックアップとリカバリ

XtraBackupの概要

MySQLのバックアップにはmysqldumpを使用したコールドバックアップと、XtraBackupを使用したホットバックアップがあります。XtraBackupはInnoDBとXtraDBエンジンのテーブルをバックアップできますが、MyISAMテーブルのバックアップはできません。

XtraBackupの利点

  • 高速な物理バックアップ
  • トランザクション実行中でもバックアップ可能
  • 圧縮機能によるディスクスペースの節約
  • 自動バックアップ検証
  • 高速なリストア
  • リモートサーバーへのバックアップ転送
  • サーバー負荷を増加させずにバックアップ

XtraBackupのインストール

YUMを使用したインストール

# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
# yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

バックアップユーザーの作成

mysql> CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'backup456';
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'backupuser';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';
mysql> FLUSH PRIVILEGES;

フルバックアップとリストア

バックアップの実行

# innobackupex --user=backupuser --password=backup456 --defaults-file=/etc/my.cnf /BACKUP-DIR/

リストアの実行

# innobackupex --apply-log /backups/2018-07-30_11-04-55/
# innobackupex --copy-back --defaults-file=/etc/my.cnf /backups/2018-07-30_11-04-55/

MySQLレプリケーション設定

レプリケーション環境の準備

マスターサーバーの設定

[mysqld]
server-id=1
log_bin=mysql-bin

レプリケーションユーザーの作成

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.0.0.%' IDENTIFIED BY 'repl123';

スレーブサーバーの設定

mysql> CHANGE MASTER TO 
MASTER_HOST='10.0.0.51',
MASTER_USER='repluser',
MASTER_PASSWORD='repl123',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1461;
mysql> START SLAVE;

遅延レプリケーションの設定

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_DELAY = 180;
mysql> START SLAVE;

セミシンクロナスレプリケーション

マスターでの設定

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

スレーブでの設定

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

MySQL監視設定

Zabbixエージェントの設定

# yum install zabbix-agent php php-mysql
# rpm -ivh https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.7/binary/redhat/6/x86_64/percona-zabbix-templates-1.1.7-2.noarch.rpm
# mkdir -p /etc/zabbix/zabbix_agentd.d
# cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/

監視ユーザーの作成

mysql> GRANT PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'zabbixpass';

タグ: MySQL XtraBackup レプリケーション バックアップ データベース監視

7月23日 19:08 投稿

ホットタグ

©2026 異端開発室