[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()

sudo apt-get install update

sudo apt-get install upgrade

sudo apt-get install python

sudo apt-get install python-pip

wget https://github.com/carpedm20/LINE/archive/master.zip

unzip master.zip

cd LINE-master/

def ready(self):
  """
  After login, make `client` and `client_in` instance
  to communicate with LINE server
  """

+        self.transport = THttpClient.THttpClient(self.LINE_HTTP_URL)
 +        self.transport_in = THttpClient.THttpClient(self.LINE_HTTP_IN_URL)
 +        self.transport.setCustomHeaders(self._headers)
 +        self.transport_in.setCustomHeaders(self._headers)
 +        self.protocol = TCompactProtocol.TCompactProtocol(self.transport)
 +        self.protocol_in = TCompactProtocol.TCompactProtocol(self.transport_in)
 +        self._client = CurveThrift.Client(self.protocol)
 +        self._client_in = CurveThrift.Client(self.protocol_in)
 +        self.transport.open()
 +        self.transport_in.open()

python config.py

python setup.py install

vi echobot.py

vi check.py

import subprocess
from line import LineClient, LineGroup, LineContact

try:
    client = LineClient("ID", "PASS")
    #client = LineClient(authToken="TOKEN")
except:
    print "Login Failed"

while True:
    op_list = []

    for op in client.longPoll():
        op_list.append(op)

    for op in op_list:
        sender   = op[0]
        receiver = op[1]
        message  = op[2]

        msg = message.text
        if 'GetRAM' in msg:
                proc=subprocess.Popen('egrep --color "Mem|Cache|Swap" /proc/meminfo', shell=True, stdout=subprocess.PIPE, )
                output=proc.communicate()[0]
                sender.sendMessage("[%s] %s" % (sender.name, output))

        if 'GetDATE' in msg:
                proc=subprocess.Popen('date', shell=True, stdout=subprocess.PIPE, )
                output=proc.communicate()[0]
                sender.sendMessage("[%s] %s" % (sender.name, output))

        if 'GetKERNAL' in msg:
                proc=subprocess.Popen('uname -a', shell=True, stdout=subprocess.PIPE, )
                output=proc.communicate()[0]
                sender.sendMessage("[%s] %s" % (sender.name, output))

#!/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)

โปรแกรม GT - Wifi Hotspot บน Windows 7/8/8.1

GT - Wifi Hotspot: 

GT-WifiHotspot v1.0.0-b.2

add feature

add feature
-Hide App
-Check for Update

GT-WifiHotspot v1.0.0-b.1

วิธีใช้
1.ทำการเปิดโปรแกรม คลิกขวา Run as Administrator

2.ตัวโปรแกรมจะทำการตรวจสอบให้ก่อนว่าอุปกรณ์ของเครื่องนั้นพร้อมสำหรับการทำหรือไม่

3.กำหนด Hotspot SSID และ Password จากนั้นกดปุ่ม setting เพื่อทำการสร้าง hotspot ขึ้นมาเหมือนกับการใช้คำสั่ง

netsh wlan set hostednetwork mode=allow ssid=namehotspot key=passhotspot

4.ทำการกดปุ่ม Start เพื่อเริ่มการปล่อยสัญญาณเหมือนกับการใช้คำสั่ง

netsh wlan start hostednetwork

จะมีอุปกรณืเพิ่มเข้ามา

ตอนนี้ยังเล่นอินเตอร์เน็ตไม่ได้

5. หลังจากการเริ่มทำงาน ต้องทำการแชร์อินเตอร์เน็ตไปให้ Wifi hotspot ที่เราสร้างขึ้นมาจึงจะสมบูรณ์
ไปที่ Start-->Search พิมพ์ ncpa.cpl เพื่อเปิด Network connection

6.คลิกขวาที่ connection ที่ใช้ต่ออินเตอร์เน็ตอยู่ปัจจุบัน (Local Area Network or Wifi) แล้วเลือก Properties

7.แท็บ Sharing

ติ๊กถูกที่ Allow other network users to connect through this computer’s internet connection 

ช่อง Home networking connection ให้เลือกเป็น Local Area Connection* xx 

(ตัวอย่างเป็น Local Area Connection* 6) กด Ok เป็นอันเสร็จเรียบร้อย

8.หากไม่ใช้แล้วให้ทำการกดปุ่ม Stop เพื่อหยุดการปล่อยสัญญาณเหมือนกับการใช้คำสั่ง 

netsh wlan stop hostednetwork

creditby : thaicreate,jirayu

netsh wlan show driver

netsh wlan set hostednetwork mode=allow ssid=namehotspot key=passhotspot

netsh wlan start hostednetwork

netsh wlan stop hostednetwork