Backup (mysql dump) all your MySQL databases in separate files

Sometimes we would like to dump all the MySQL databases. MySQL provides an easy solution to this problem:
1

mysqldump -u root -p –all-databases > all_dbs.sql

However this will dump stuff into one file. How to dump all databses into separate files? Well, here is my solution. A small bash script:

	
#! /bin/bash
 
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/backup/$TIMESTAMP"
MYSQL_USER="backup"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="password"
MYSQLDUMP=/usr/bin/mysqldump
 
mkdir -p "$BACKUP_DIR/mysql"
 
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
 
for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"
done

Be aware, that in order to execute this script from cron, you need to store password in it (so cron won’t be prompted to provide a password). That’s why, you should not use a root account. Instead just create a new user only for backups, with following privileges:

daha once bu konu hakkinda yazmistim.

bir daha yazmak icap etti.

kvm lerde kullandigim .raw imaj dosyalari sparse degiller.

halbuki sparse olsalar aktarirken 200GB yerin 20Gb aktaracagim.

haydi .raw imajlari sparse edelim.

1- ilgili virtual makinaya gir ve diskin heryerini 0 larla doldur:

dd if=/dev/zero of=/tmp/zerotxt bs=1M

islem bitince sync et

sync

zerotxt yi sil

rm /tm/zerotxt

2- simdi virtual makinadan cik ve makinayi kapat.

imaj dosyanin bulundugunu dizine git. en hizli ve en kolay yontem imaji copy edecegiz.

cp –sparse=always vm-foo.raw vm-foo.raw-sparse

3- simdi elimizde sparse dosyamiz oldu.

/var/lib/vz/images/101# ls -lash
total 252G
4,0K drwxr-xr-x 2 root root 4,0K Mar 11 08:04 .
4,0K drwxr-xr-x 12 root root 4,0K Ara 6 09:54 ..
201G -rw-r–r– 1 root root 200G Mar 11 23:40 vm-101-disk-1.eski.raw
51G -rw-r–r– 1 root root 200G Mar 11 07:55 vm-101-disk-1.raw

hemen yeni sunucuya transfer edelim 🙂

gule gule kullanalim.

Just do the following to set proxy timeout to 1800:
Code:

echo 'ProxyTimeout 1800' >> /etc/httpd/conf/extra/httpd-includes.conf
service httpd restart

LEMP stack/LAMP stack/LNMP stack installation scripts for CentOS/Redhat Debian and Ubuntu https://blog.linuxeye.com/31.html

adresler su sekilde

oncelikle cinli developerin gelistirdigi var:

https://github.com/lj2007331/lnmp

This script is written using the shell, in order to quickly deploy LEMP/LAMP/LNMP/LNMPA(Linux, Nginx/Tengine, MySQL in a production environment/MariaDB/Percona, PHP), applicable to CentOS 5~7(including RedHat), Debian 6~8, Ubuntu 12.04~15.04 of 32 and 64.

Script properties:

• Continually updated
• Source compiler installation, most stable source is the latest version, and download from the official site
• Some security optimization
• Providing a plurality of database versions (MySQL-5.7, MySQL-5.6, MySQL-5.5, MariaDB-10.1, MariaDB-10.0, MariaDB-5.5,Percona-5.7, Percona-5.6, Percona-5.5)
• Providing multiple PHP versions (php-5.3, php-5.4, php-5.5, php-5.6, php-7)
• Provide Nginx, Tengine
• Providing a plurality of Apache version (Apache-2.4, Apache-2.2)
• According to their needs to install PHP Cache Accelerator provides ZendOPcache, xcache, apcu, eAccelerator. And php encryption and decryption tool ionCube, ZendGuardLoader
• Installation Pureftpd, phpMyAdmin according to their needs
• Install memcached, redis according to their needs
• Tcmalloc can use according to their needs or jemalloc optimize MySQL, Nginx
• Providing add a virtual host script
• Provide Nginx/Tengine, MySQL/MariaDB/Percona, PHP, Redis, phpMyAdmin upgrade script
• Provide local backup and remote backup (rsync between servers) script
• Provided under HHVM install CentOS 6,7

birde bu var gotdeb

GotDeb

Interactive bash script for VPS or Dedicated servers. Build with low end systems in mind. Requires Debian version 7.x or 8.x

Installation

Run the script and follow the assistant:

wget https://gotdeb.com/setup.sh --no-check-certificate
chmod +x setup.sh && ./setup.sh

OR

wget https://raw.githubusercontent.com/eunas/gotdeb/master/setup.sh --no-check-certificate
chmod +x setup.sh && ./setup.sh

Script content

• Nginx
  • nginx 1.8.x
  • nginx 1.9.x
  • Optional SSL support with self signed certificate or Let’s Encrypt
• Blogs
  • Ghost
  • WordPress
• PHP
  • PHP-FPM 5.6
  • PHP-FPM 7.0 (Debian 8)
  • HHVM
• MySQL Server
• MariaDB server
• phpMyAdmin
• PureFTPD (FTPS enabled)
• OpenVPN Server (Works on NAT)
• SoftEtherVPS (Works on NAT)
• Squid3 Proxy Server
• sSMTP server
• Aria2 + webui
• Transmission BitTorrent Client
• X2Go + xfce Desktop
• Plex Media Server
• Observium
  • Server
  • Client
• Linux Dash server monitor
• User Management
  • Add user
  • Delete user
  • List Users
• System Management
  • Remove unneeded packages and services
  • Install essentials packages
  • Update timezone
  • System tests
  • Secure System
    • fail2ban
    • Uncomplicated Firewall
    • Unattended Upgrades
  • Speedtest.net
  • Get OS Version
• About

https://github.com/eunas/gotdeb

IP Tables Script

rules.sh

iptables -F
iptables -A INPUT -p tcp --tcp-flags ALL NONE -j DROP
iptables -A INPUT -p tcp ! --syn -m state --state NEW -j DROP
iptables -A INPUT -p tcp --tcp-flags ALL ALL -j DROP
iptables -A INPUT -i eth0 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 443 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 22 -j ACCEPT
iptables -P OUTPUT ACCEPT
iptables -P INPUT DROP

cat /etc/nginx/conf.d/domain.com.conf

server {
       listen         80;
       server_name    domain.com;
       return         301 https://$server_name$request_uri;
}
server {
      listen 443 ssl;
      server_name domain.com;

      ssl_certificate /home/ssl/cert.crt;
      ssl_certificate_key /home/ssl/cert.key;

      access_log   /home/logs/domain.com.access.log;
      error_log /home/logs/domain.com.error.log;

      location / {
            proxy_pass https://WEBSERVERIP/;
            proxy_redirect off;
            proxy_set_header Host $host;
            proxy_set_header X-Forwarded-SSL on;
            proxy_set_header X-Forwarded-Proto $scheme;
            proxy_set_header X-Real-IP $remote_addr;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            proxy_max_temp_file_size 0;
            client_max_body_size 10m;
            client_body_buffer_size 128k;
            proxy_connect_timeout 90;
            proxy_send_timeout 90;
            proxy_read_timeout 90;
            proxy_buffer_size 4k;
            proxy_buffers 4 32k;
            proxy_busy_buffers_size 64k;
            proxy_temp_file_write_size 64k;

      }
}

a sunucusundan b sunucusuna dosyalari en hizli nasil transfer ederiz?

cevap rsync tabiiki..

daha oncede yazmistim. Biraz gelistirmek zorunda kaldim.

soyle ki: su linkdeki adresde bu konuyu yazmisim.

ancak rsync malum paralel calismiyor. tek thread isi bozuyor.

o zaman 1 den cok thread calistiralim

parallel kullanarak.

yum install parallel veyahut apt-get install moreutils

sonrasinda komutumuz bu:

ls . | parallel -v -j8 rsync -raz --progress {} root@sun.ucu.ip.adres:/home/blabla/domains/hoptirirom.com.tr/public_html/{}

8 baglanti acip rsync yapacak bu.

ssh-keygen -t rsa

ve

ssh-copy-id user@123.45.56.78

yapmayi unutma bastan 🙂

biraz daha aciklama yazayim kendim icin

rsync icin -raz

-recursive

-archive

-zikistir

demek oluyor.

-j8 8 paralel demek

daha once

rsync icin -ave gibi bisi kullanmistim

-archive

-verbose

-e -e, –rsh=COMMAND specify the remote shell to use

demekmis

-e neden kullanmadim ben burada acaba?

cunku daha once sunu kullanmistim

rsync -ave 'ssh -p 22' 1.2.3.4:/home/ /home/

simdi kullanmadim. ssh yap demedim. centosdan centosa benzer zaten sheller. kendisi halletti galiba.

NOT: http://moo.nac.uci.edu/~hjm/parsync/

buda perl wrapper ayni isi yapan. bakmadim henuz buna lazim olursa bakarim. eminim daha kolaydir.


hpacucli ctrl slot=1 show config

Smart Array P410 in Slot 1 (sn: PACCRID11510NCV)

array A (SAS, Unused Space: 0 MB)

logicaldrive 1 (838.3 GB, RAID 1+0, Recovering, 13% complete)

physicaldrive 1I:1:1 (port 1I:box 1:bay 1, SAS, 450 GB, OK)
physicaldrive 1I:1:2 (port 1I:box 1:bay 2, SAS, 450 GB, OK)
physicaldrive 1I:1:3 (port 1I:box 1:bay 3, SAS, 450 GB, Rebuilding)
physicaldrive 1I:1:4 (port 1I:box 1:bay 4, SAS, 450 GB, OK)

SEP (Vendor ID PMCSIERA, Model SRC 8x6G) 250 (WWID: 500143801887352F)


3.2. hpacucli

This tool is a proprietary one created by HP. It can do both reporting and management.
3.2.1. Quickstart guide

List all controllers:

server:~# hpacucli controller all show

Smart Array 6i in Slot 0 ()

List arrays on controller in slot 0:

server:~# hpacucli ctrl slot=0 logicaldrive all show status

logicaldrive 1 (33.9 GB, RAID RAID 1+0): OK
logicaldrive 2 (136.7 GB, RAID RAID 1+0): OK

I don’t know why it reports RAID 1+0. This is regular RAID1 arrays.

List physical drives on controller in slot 0:

server:~# hpacucli ctrl slot=0 pd all show status

physicaldrive 1:0 (port 1:id 0, 36.4 GB): OK
physicaldrive 1:1 (port 1:id 1, 36.4 GB): OK
physicaldrive 1:2 (port 1:id 2, 146.8 GB): OK
physicaldrive 1:3 (port 1:id 3, 146.8 GB): OK

Summarized status:

server:~# hpacucli ctrl slot=0 show config

Smart Array 6i in Slot 0 ()

array A (Parallel SCSI, Unused Space: 0 MB)

logicaldrive 1 (33.9 GB, RAID 1+0, OK)

physicaldrive 2:0 (port 2:id 0 , Parallel SCSI, 36.4 GB, OK)
physicaldrive 2:1 (port 2:id 1 , Parallel SCSI, 36.4 GB, OK)

array B (Parallel SCSI, Unused Space: 0 MB)

logicaldrive 2 (136.7 GB, RAID 1+0, OK)

physicaldrive 2:2 (port 2:id 2 , Parallel SCSI, 146.8 GB, OK)
physicaldrive 2:3 (port 2:id 3 , Parallel SCSI, 146.8 GB, OK)

Controller policies (write cache, disk cache, battery), only interesting lines kept:

root@server:~# hpacucli ctrl slot=0 show

Smart Array P420i in Slot 0 (Embedded)
Serial Number: *SERIAL*
Controller Status: OK
Firmware Version: 3.54
Cache Board Present: True
Cache Status: OK
Cache Ratio: 25% Read / 75% Write
Drive Write Cache: Disabled
Total Cache Size: 512 MB
No-Battery Write Cache: Disabled
Cache Backup Power Source: Capacitors
Battery/Capacitor Count: 1
Battery/Capacitor Status: OK

Cache is ok, Battery is too. Write cache disabled if battery back isn’t enabled, that’s ok.

Check and enable cache for all arrays:

Check current state:

root@server:~# hpacucli ctrl slot=0 ld all show detail

Smart Array P420i in Slot 0 (Embedded)

array A

Logical Drive: 1
Size: 136.7 GB
Fault Tolerance: RAID 1
Status: OK
Caching: Disabled

Enable caching:

root@server:~# hpacucli ctrl slot=0 ld all modify arrayaccelerator=enable

Enable disks’ write cache:

root@server:~# hpacucli ctrl slot=0 modify dwc=enable

Warning: Without the proper safety precautions, use of write cache on physical
drives could cause data loss in the event of power failure. To ensure
data is properly protected, use redundant power supplies and
Uninterruptible Power Supplies. Also, if you have multiple storage
enclosures, all data should be mirrored across them. Use of this
feature is not recommended unless these precautions are followed.
Continue? (y/n) y

Warning is self-explaining I guess. Disks’s cache aren’t protected by controller’s battery. It’s up to you but I wouldn’t enable such features if your power supply isn’t protected.

Modify cache ratio between read and write:

root@server:~# hpacucli ctrl slot=0 modify cacheratio=50/50

http://hwraid.le-vert.net/wiki/SmartArray

MySQL de views diye bir sey varmis.

ilgili bir zamanda belli bir query nin cevabini virtual olarak tutmasina yariyormus MySQL in.

MySQL ci olmayinca neyin ne oldugunu detaylica bilmiyorum.

bu view denen boklari dump etmiyor default mysqldump

1 kac kez sorun yasadim, programcilar tiri viri yapiyor

su sekilde dump edilebilir ozel durumlarda:

1. mysql.view.dump.sh diye bir text dosyasi icine yaz

#!/bin/bash -e
mysql --skip-column-names --batch -e \
"select table_name from information_schema.views \
 where table_schema = database()" $* |
xargs --max-args 1 mysqldump $*

2- su sekilde calistir

sh mysql.view.dump.sh -u user -ppassword databasename > dumpfile.sql

yum install php-devel ImageMagick ImageMagick-devel
pecl install Imagick

*locate pecl*

don’t forget to add this to php.ini
extension=imagick.so

and then

service httpd restart