GuutonG

[HOW TO] Script Backup Mysql Log (Shell Script)

#!/bin/bash
###################################################
#      ____             _               ____      #
#     / ___|_   _ _   _| |_ ___  _ __  / ___|     #
#    | |  _| | | | | | | __/ _ \| '_ \| |  _      # 
#    | |_| | |_| | |_| | || (_) | | | | |_| |     #
#     \____|\__,_|\__,_|\__\___/|_| |_|\____|     #
#                                                 #
###################################################
#            ScriptMySqlLogBackup.sh              #
###################################################

timestamp=$(date +"%Y%m%d")
MyHost="localhost"
MyUser="root"
MyPass=""
logDir="/var/log/mysql"
moveToDir=""

echo "[INFO] $(date +"%Y%m%d %T") : mysql log backup start";

mv $logDir/mysql.log $logDir/mysql.log.$timestamp
mv $logDir/mysql_error.log $logDir/mysql_error.log.$timestamp
mv $logDir/mysql-slow.log $logDir/mysql-slow.log.$timestamp

if [ -f "$logDir/mysql.log" ]
then
   echo "[WARN] $(date +"%Y%m%d %T") : mysql.log Move Failed";
else
   echo "[INFO] $(date +"%Y%m%d %T") : mysql.log Move Successed";
fi

if [ -f "$logDir/mysql_error.log" ]
then
   echo "[WARN] $(date +"%Y%m%d %T") : mysql_error.log Move Failed";
else
   echo "[INFO] $(date +"%Y%m%d %T") : mysql_error.log Move Successed";
fi

if [ -f "$logDir/mysql-slow.log" ]
then
   echo "[WARN] $(date +"%Y%m%d %T") : mysql-slow.log Move Failed";
else
   echo "[INFO] $(date +"%Y%m%d %T") : mysql-slow.log Move Successed";
fi

if [ -n "$MyPass" ]
then
   /usr/bin/mysqladmin --host=$MyHost --user=$MyUser --password=$MyPass flush-logs
else 
   /usr/bin/mysqladmin --host=$MyHost --user=$MyUser flush-logs
fi

echo "[INFO] $(date +"%Y%m%d %T") : flush-logs Successed";

zip $logDir/mysql.log.$timestamp.zip $logDir/mysql.log.$timestamp
zip $logDir/mysql_error.log.$timestamp.zip $logDir/mysql_error.log.$timestamp
zip $logDir/mysql-slow.log.$timestamp.zip $logDir/mysql-slow.log.$timestamp

if [ -f "$logDir/mysql.log.$timestamp.zip" ]
then
   echo "[INFO] $(date +"%Y%m%d %T") : mysql.log.$timestamp zip Successed";
   rm -rf $logDir/mysql.log.$timestamp
   echo "[INFO] $(date +"%Y%m%d %T") : mysql.log.$timestamp Deleted";
else 
   echo "[WARN] $(date +"%Y%m%d %T") : mysql.log.$timestamp zip Failed";
fi

if [ -f "$logDir/mysql_error.log.$timestamp.zip" ]
then
   echo "[INFO] $(date +"%Y%m%d %T") : mysql_error.log.$timestamp zip Successed";
   rm -rf $logDir/mysql_error.log.$timestamp
   echo "[INFO] $(date +"%Y%m%d %T") : mysql_error.log.$timestamp Deleted";
else 
   echo "[WARN] $(date +"%Y%m%d %T") : mysql_error.log.$timestamp zip Failed";
fi

if [ -f "$logDir/mysql-slow.log.$timestamp.zip" ]
then
   echo "[INFO] $(date +"%Y%m%d %T") : mysql-slow.log.$timestamp zip Successed";
   rm -rf $logDir/mysql-slow.log.$timestamp
   echo "[INFO] $(date +"%Y%m%d %T") : mysql-slow.log.$timestamp Deleted";
else 
   echo "[WARN] $(date +"%Y%m%d %T") : mysql-slow.log.$timestamp zip Failed";
fi


if [ -n "$moveToDir" ]
then
   if [ -f "$logDir/mysql.log.$timestamp.zip" ]
   then
       mv $logDir/mysql.log.$timestamp.zip $moveToDir/mysql.log.$timestamp.zip
       echo "[INFO] $(date +"%Y%m%d %T") : move mysql.log.$timestamp.zip to $moveToDir";
   else 
      echo "[WARN] $(date +"%Y%m%d %T") : mysql.log.$timestamp.zip not found or move Failed";
   fi

   if [ -f "$logDir/mysql_error.log.$timestamp.zip" ]
   then
      mv $logDir/mysql_error.log.$timestamp.zip $moveToDir/mysql_error.log.$timestamp.zip
      echo "[INFO] $(date +"%Y%m%d %T") : move mysql_error.log.$timestamp.zip to $moveToDir";
   else 
      echo "[WARN] $(date +"%Y%m%d %T") : mysql_error.log.$timestamp.zip not found or move Failed";
   fi

   if [ -f "$logDir/mysql_error.log.$timestamp.zip" ]
   then
       mv $logDir/mysql_slow.log.$timestamp.zip $moveToDir/mysql_slow.log.$timestamp.zip
       echo "[INFO] $(date +"%Y%m%d %T") : move mysql_slow.log.$timestamp.zip to $moveToDir";
   else 
      echo "[WARN] $(date +"%Y%m%d %T") : mysql_slow.log.$timestamp.zip not found or move Failed";
   fi
else 
   echo "[WARN] $(date +"%Y%m%d %T") : Move Failed";
fi

echo "[INFO] $(date +"%Y%m%d %T") : mysql log backup finish";

#============================
#edit ScriptMySqlLogBackup.sh
#============================
MyHost="localhost"
MyUser="root"
MyPass=""
logDir="/var/log/mysql"

#============================
#edit crontab
#============================
crontab -e

0 23 * * * /path/to/ScriptMySqlLogBackup/ScriptMySqlLogBackup.sh > /path/to/ScriptMySqlLogBackup/ScriptMySqlLogBackup.log

Percona XtraDB Cluster 5.6 (3 node) With Docker

1.ทำการ pull image ของ ubuntu มาก่อน

2.สร้าง directoryไว้สำหรับเก็บ image ของเรา เช่น

mkdir ubuntu-percona
cd ubuntu-percona

3.สร้าง Dockerfile ที่จะรวมคำสั่งในการสร้าง image ของเราขึ้นมา

vi Dockerfile

FROM ubuntu:trusty
MAINTAINER GuutonG credit by [Percona.com]
CMD ["/bin/bash"]
ENV DEBIAN_FRONTEND noninteractive
RUN apt-get update
RUN apt-get upgrade -y
RUN apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
ADD percona.list /etc/apt/sources.list.d/percona.list
RUN apt-get update
RUN apt-get install -y percona-xtradb-cluster-56 qpress xtrabackup
RUN apt-get install -y python-software-properties vim wget curl netcat

และสร้าง percona.list สำหรับลง มำ repo ไปยัง percona

vi percona.list
deb http://repo.percona.com/apt trusty main
deb-src http://repo.percona.com/apt trusty main

4.ทำการbuild Dockerfile ที่เราสร้างไว้

docker build -t ubuntu:gtpxc .

5. ทำการ run ubuntu:gtpxc ออกมา3 containner

for n in {1..3}; do docker run --name gtpxc$n -i -t -d ubuntu:gtpxc bash; done

6.เรียกดู docker ที่ทำงานอยู่

docker ps

7.เช็คIP ของแต่ละ container

docker inspect gtpxc1 | grep IPAddress
docker inspect gtpxc2 | grep IPAddress
docker inspect gtpxc3 | grep IPAddress

ในที่นี้จะได้ IP

8.เปิด Docker Terminal ขึ้นมาใหม่ 3 อัน แล้วใช้คำสั่ง docker attach [name]เพื่อ remote เข้าไปยัง container

docker attach gtpxc1
docker attach gtpxc2
docker attach gtpxc3

Ubuntu:[gtpxc1]

vi /etc/mysql/my.cnf

#GT-PXC Node1
[mysqld]
user = mysql
default_storage_engine = InnoDB
basedir = /usr
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
innodb_autoinc_lock_mode = 2
log_queries_not_using_indexes = 1
max_allowed_packet = 128M
binlog_format = ROW

wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_cluster_name="docker-gtpxc"
#IP node1,node2,node3
wsrep_cluster_address = gcomm://[IP node1],[IP node2],[IP node3]
#Name node1
wsrep_node_address = [IP node1]
wsrep_node_name = gtpxc1
wsrep_slave_threads = 4
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = gtuser:gtpassword

[sst]
streamfmt = xbstream

[xtrabackup]
compress
compact
parallel = 2
compress_threads = 2

rebuild_threads = 2

ใช้คำสั่งเพื่อ start percona xtradb cluster

/etc/init.d/mysql bootstrap-pxc

และสร้าง user pass กลางสำหรับใช้ร่วมกัน

mysql>CREATE USER 'gtuser'@'localhost' IDENTIFIED BY 'gtpassword';
mysql>GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'gtuser'@'localhost';
mysql>FLUSH PRIVILEGES;

ตรวจสอบค่าต่างของ cluster

mysql>show global status like 'wsrep%';

Ubuntu:[gtpxc2]
แก้ไขไฟล์ my.cnf และใช้คำสั่งเพื่อ start mysql

vi /etc/mysql/my.cnf
/etc/init.d/mysql start

Ubuntu:[gtpxc3]
แก้ไขไฟล์ my.cnf และใช้คำสั่งเพื่อ start mysql

#vi /etc/mysql/my.cnf
#/etc/init.d/mysql start

Ubuntu:[gtpxc1]

mysql>show global status like 'wsrep%';

+------------------------------+------------------------------------------------
--+
| Variable_name | Value
|
+------------------------------+------------------------------------------------
--+
| wsrep_local_state_uuid | 1bb5bf25-4c05-11e5-b0af-ebfa440f9b9e
|
| wsrep_incoming_addresses | 172.17.0.4:3306,172.17.0.5:3306,172.17.0.6:3306
|
| wsrep_evs_repl_latency | 0.00041831/0.000710346/0.000908118/0.000180007/
4 |
| wsrep_evs_state | OPERATIONAL
|
| wsrep_gcomm_uuid | 1bb555bd-4c05-11e5-95a6-cfcc3cc31436
|
| wsrep_cluster_conf_id | 3
|
| wsrep_cluster_size | 3
|
| wsrep_cluster_state_uuid | 1bb5bf25-4c05-11e5-b0af-ebfa440f9b9e
|
| wsrep_cluster_status | Primary
|
| wsrep_connected | ON
|
| wsrep_local_bf_aborts | 0
|
| wsrep_local_index | 0
|
| wsrep_provider_name | Galera
|
| wsrep_provider_vendor | Codership Oy
|
| wsrep_provider_version | 3.11(r93aca2d)
|
| wsrep_ready | ON
|
+------------------------------+------------------------------------------------
--+

[TEST]

Time	Ubuntu:[gtpxc1]	Ubuntu:[gtpxc2]	Ubuntu:[gtpxc3]
1	create database gtpxcDB;
2		show databases; +--------------------+ | Database | +--------------------+ | gtpxcDB | +--------------------+
3			gtpxcDB; create table tbl1 (id INT PRIMARY KEY, name VARCHAR(30)); INSERT INTO gtpxcDB.tbl1 VALUES (1, 'gtpxc1'); INSERT INTO gtpxcDB.tbl1 VALUES (2, 'gtpxc2'); INSERT INTO gtpxcDB.tbl1 VALUES (3, 'gtpxc3');
4	show tables; +-------------------+ | Tables_gtpxcDB | +-------------------+ | tbl1 | +-------------------+
5		use gtpxcDB; select * from tbl1; +----+--------+ | id | name | +----+--------+ | 1 | gtpxc1 | | 2 | gtpxc2 | | 3 | gtpxc3 | +----+--------+

credit by: Docker,Percona XtraDB Cluster 5.6 Documentation,Jericho Rivera

[HOW TO] Auto Backup Mysqldump with Python and crontab

Dowload

# wget https://github.com/Guutong/pySQLdump/releases/download/v.0.0.1/pySQLdumpSetup.sh

# chmod +x pySQLdumpSetup.sh

# mkdir /root/pySQLBackup/

# ./pySQLdumpSetup.sh

Set vi conf/settings.ini

How to setting Auto backup with crontab

#!/usr/bin/python
######################################################
#____________________________________________________#
#_______________Mr.Pornmongkon Pongsai_______________#
#____________________pySQLdump v.0.1_________________#
#______________Create Date : 25-07-2015______________#
#____________________________________________________#
######################################################
from ConfigParser import SafeConfigParser
from os import path
from subprocess import Popen, PIPE
import shlex
import datetime
import logging

def main():
    config_file = 'conf/settings.ini'
    logs_path = 'logs/pySQLdump.log'
    logging.basicConfig(filename=logs_path,format='%(asctime)s:%(levelname)s:%(message)s',level=logging.DEBUG)
    # Read in all the settings
    config = SafeConfigParser()
    config.read(config_file)
    user = config.get('mysql', 'user')
    password = config.get('mysql', 'password')
    database = config.get('mysql', 'dbname')
    destination = config.get('mysql', 'destination')
    date = datetime.datetime.now().strftime('%Y%m%d-%H%M%S')
    file_name = 'pySQLdump-' + database + '-' + date

    logging.info('==[RUN]== Running mysqldump')
    cmd = 'mysqldump -u' + user + ' ' + database + ' -p"' + password + '" --result-file="' + file_name + '.sql"'
    logging.debug(cmd) 
    run_cmd(cmd)

    logging.info('Creating Zip file')
    run_cmd('zip ' + file_name + '.zip ' + file_name + '.sql')

    logging.info('Removing dump file')
    run_cmd('rm ' + file_name + '.sql')

    logging.info('Moving zipped tarball to destination')
    run_cmd('mv ' + file_name + '.zip ' + destination)
    logging.info('==[PASS]== Mysqldump success!')
    
def run_cmd(cmd):
    process = Popen(shlex.split(cmd), stdout=PIPE)
    dump_output = process.communicate()[0]
    exit_code = process.wait()
    if exit_code != 0:
        print(dump_output)
        raise Exception(str(exit_code) + ' - Error executing command.  Please review output.')

if __name__ == '__main__':
    main()

#!/usr/bin/python
######################################################
#____________________________________________________#
#_______________Mr.Pornmongkon Pongsai_______________#
#________________Computer Engineering________________#
#_____________Generate Word for Project______________#
#____________Create Date : 28 April 2015_____________#
#____________________________________________________#
######################################################
import sys
import getopt

def main(argv):
 fileName = 'default'
 lineNumber = 0
 wordGen = 'default'
 try:
  opts, args = getopt.getopt(argv,"hf:n:w:",["file=","number=","word="])
 except getopt.GetoptError:
  print 'Help : genword.py -f  -n  -w '
  sys.exit(2)
 for opt, arg in opts:
  if opt == '-h':
   print 'Help : genword.py -f  -n  -w '
   sys.exit()
  elif opt in ("-f", "--file"):
   fileName = arg
  elif opt in ("-n", "--number"):
   lineNumber = int(arg)
  elif opt in ("-w", "--word"):
   wordGen = arg  
 print '[log] : generate word start!'
 dataFile = open(fileName, 'w')
 i = 0
 while ( i < lineNumber ):
  dataFile.write(wordGen+'\n')
  i = i + 1
 dataFile.close()
 print '[log] : generate word finish!'
if __name__ == "__main__":
 print '######################################################'
 print '#____________________________________________________#'
 print '#_______________Mr.Pornmongkon Pongsai_______________#'
 print '#________________Computer Engineering________________#'
 print '#_____________Generate Word for Project______________#'
 print '#____________Create Date : 28 April 2015_____________#'
 print '#____________________________________________________#'
 print '######################################################'
 print '!!!-h for help!!!'
 main(sys.argv[1:])

#!/usr/bin/python
import commands
import time
import os
while True:
 #find process
        pc = commands.getoutput('ps -aux | grep nginx | grep -v grep').strip()

        if(len(pc) > 0):
                print 'process running. skip'
        else:
  print 'process not running'
                #stop program
                os.system('sudo service nginx stop')
                #start program
         os.system('sudo service nginx start')
  print 'process started'
 #Check every 1 minute
        time.sleep(1 * 60)

$sudo mkdir ssl-cert 

$cd ssl-cert

หมายเหตุ - *ในที่นี้ผมจะสร้าง directory ssl- cert ไว้ที่ / เราสามารถไปที่ directory / โดยใช้คำสั่ง cd / และสามารถตรวจสอบรายชื่อแฟ้มทั้งหมดที่อยู่ใน directory นั้นๆ ด้วยคำสั่ง ls
** ใน Linux เราจะเรียก แทน Folder ว่า Directory

ทำการติดตั้งโปรแกรม openssl โดยใช้คำสั่ง

$sudo apt-get install openssl 

*openssl เป็นซอร์ฟแวร์ที่ใช้สำหรับทำ SSL

 ต่อมาแก้ไขไฟล์ openssl.cnf  ซึ่งเก็บไว้ใน /etc/ssl  โดยการใช้คำสั่ง

$ sudo vi /etc/ssl/openssl.cnf

แก้ไขดังนี้

CountryName_default = TH

State0rProvinceName_default =   ค่าว่าง

สร้างไฟล์ Certificate โดยใช้คำสั่ง

$ sudo openssl genrsa –out server.key 1024 

สร้าง Certificate Signing Request (CSP)  โดยการใช้คำสั่ง

$ sudo openssl req -config /etc/ssl/openssl.cnf -new -key server.key -out newcsr.csr

$ sudo openssl x509 -req -days 365 -in newcsr.csr -signkey server.key -out server.crt

ต่อมากำหนดให้ apache2 ใช้งาน SSL โดยใช้คำสั่ง

$ sudo a2enmod ssl

 สร้าง /etc/apache2/sites-available/sslsite โดยใช้คำสั่งดังต่อไปนี้

$ sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/sslsite

ทำการแก้ไขไฟล์ /etc/apache2/sites-available/sslsite  โดยการใช้คำสั่งดังต่อไปนี้

$ sudo vi /etc/apache2/sites-available/sslsite

แก้ข้อความตามนี้

NameVirtualHost *:443

ServerAdmin guutong@admin.com 
SSLEngine On 
SSLCipherSuite
ALL:!ADH:!EXPORT56:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP:+eNULL
SSLCertificateFile /ssl-cert/server.crt 
SSLCertificateKeyFile /ssl-cert/server.key

แล้ว Save

กำหนดให้คอนฟิกนั้น  enable  โดยใช้คำสั่งดังต่อไปนี้

$ sudo /etc/apache2/sites-available/a2ensite sslsite

จากนั้นให้ Restart Network และ Apache  โดยใช้คำสั่ง

$ sudo /etc/init.d/networking restart
$ sudo service apache2 restart 

ทำการตรวจสอบ

ตรวจสอบการทำงานของ SSL  ซึ่งจะทำงานอยู่ที่พอร์ต 443 โดยใช้คำสั่งดัง 

$ netstat –lnt

ทดสอบการใช้บริการ HTTPS ผ่านเครื่อง Client โดยพิมพ์
URL  https://Server_IP  เช่น  https://192.168.23.129