backup mysql databases

steps to perform mysql databases backup:
  • put the server  in a read-only state:
    FLUSH TABLES WITH READ LOCK;
    SET GLOBAL read_only = ON;
  • backup data via this script or:
    mysqldump -h host_name --user=user_name --password=password --events --opt --single-transaction db_for_backup | gzip > backup_name.gz
  • turn off read-only mode:
    SET GLOBAL read_only = OFF;
    UNLOCK TABLES;
to restore db from backup:
  • you can use this script
  • or:  mysql -u user "db_name" < backup_name
  • or: gunzip < backup_name.gz | mysql -u user -p password db_name

mysql_backup_by_db.sh

#!/bin/bash

read -e -p "Type host for backup, followed by [ENTER]: " -i "127.0.0.1" host
read -e -p "Type username for $host, followed by [ENTER]: " -i "root" user
read -e -p  "Type password for $user, followed by [ENTER]: " -i "" passwd
read -e -p  "Database which should be backuped [ENTER]: " -i "all" db

DB_BACKUP=mysql_dump_$(date +%F)

mkdir $DB_BACKUP  
cd $DB_BACKUP
if [ $db == "all" ] 
then
	for db in $(mysql  -h $host --user=$user --password=$passwd -e 'show databases' -s --skip-column-names|grep -viE '(staging|performance_schema|information_schema)'); do 
		file_name="mysqldump-$db-$(date +%Y-%m-%d).gz";
		mysqldump -h $host --user=$user --password=$passwd --events  --log-error=mysql_dump.log --opt --single-transaction $db | gzip > $file_name;
		[ -s $file_name ] && res=Done || res=FAIL
		echo $db"|"$file_name"|"$res
	done | column -s "|" -t
else 
                file_name="mysqldump-$db-$(date +%Y-%m-%d).gz";
                mysqldump -h $host --user=$user --password=$passwd --events  --log-error=mysql_dump.log --opt --single-transaction $db | gzip > $file_name;
                [ -s $file_name ] && res=Done || res=FAIL
                echo $db"|"$file_name"|"$res
fi        	| column -s "|" -t

useful bash commands and constructs

  • create files with spaces using xargs:
    ls /your/dir | tr -d "'" | xargs -t -I {} touch "{}"
  • send notification from console to desktop:
    notify-send --expire-time=10000 "message1" "message2"
  • debug gluster client connection:
    export LC_ALL=C; /usr/sbin/glusterfs --debug --volfile-server=‹server› --volfile-id=‹volume› /mount/to/
  • Reverse string (when server doesn’t has rev tool, like solaris…)
    echo 123| perl -ne 'chomp;print scalr reverse . "\n";'

start TeamViewer via ssh

I’ve had situation when I urgently need access to my desktop. And I had only one program for remote control – teamviewer.
So here described one of the ways to run it when you have only ssh (i.e. command line):

  •  first of all connect to remote machine via ssh.
  • check if daemon is running fine:
    teamviewer --daemon status
  • determine Partner ID (remote machine id):
    teamviewer --info | grep "TeamViewer ID"
  • start it via one of the these methods. In my case worked only one:
    export DISPLAY=":0.0"
    teamviewer
  • obtain password. Here is two possible ways… in command line: teamviewer --passwd [PASSWD]
    this one din’t work for me 🙁 so lets  take screen-shot of  teamviewer window (with password) and via scp download it. for this:
    xwd -name TeamViewer | xwdtopnm | pnmtopng > passwd.png

start program with GUI via ssh without X-server

There are a lot of methods, few of them are described below:

  1. ssh X-forwarding:
    • Please ensure such parameters are enabled in your ssh server:
      #Specifies whether TCP forwarding is permitted
      AllowTcpForwarding yes
      X11Forwarding yes
      #Specifies the first display number available for sshd’s X11 forwarding:
      X11DisplayOffset 10  
      #sshd should bind the X11 forwarding server to the loopback address or to the wildcard address:
      X11UseLocalhost yes
    • Turn on X-forwarding by executing command: ssh -X user@host
    • If you see any errors whit -X. the remote machine is treated as trusted client.. run: ssh -Y user@host
  2. use -display :0.0 after name of program name. In this case very comfortable to use screen command.
  3. Export global variable: export DISPLAY=”:0.0″