MySQL backup script for database/table sharding

MySQL backup script for database/table sharding

1. Database shard backup:

#! /Bin/sh

MYUSER = root

MYPASS = 123456

SOCKET =/data/3306/mysql. sock

MYLOGIN = "mysql-u $ MYUSER-p $ MYPASS-S $ SOCKET"

MYDUMP = "mysqldump-u $ MYUSER-p $ MYPASS-S $ SOCKET-B"

DATABASE = "$ ($ MYLOGIN-e" show databases; "| egrep-vi" Data | _ schema | mysql ")"

 

For dbname in $ DATABASE

Do

MYDIR =/server/backup/$ dbname

[! -D $ MYDIR] & mkdir-p $ MYDIR

$ MYDUMP $ dbname | gzip> $ MYDIR/$ {dbname }_$ (date must have f0000. SQL .gz

Done

View backup details:

[Root @ server001 ~] # Tree/server/backup/

/Server/backup/

── Opuser26

│ ├─Opuser26_2015-12-29. SQL .gz

│ ├─Opuser26_2015-12-30. SQL .gz

│ └──Opuser26_2015-12-31. SQL .gz

── Opuser_users26

│ ├─Opuser_users26_2015-12-29. SQL .gz

│ ├─Opuser_users26_2015-12-30. SQL .gz

│ └─Opuser_users26_2015-12-31. SQL .gz

── Users26

├──Users26_2015-12-29. SQL .gz

├──Users26_2015-12-30. SQL .gz

──

Users26_2015-12-31. SQL .gz

 

3 directories, 9 files

[Root @ server001 ~] #

2. database/table sharding backup:

#! /Bin/sh

USER = root

PASSWD = passw0rd

SOCKET =/data/3306/mysql. sock

MYLOGIN = "mysql-u $ USER-p $ PASSWD-S $ SOCKET"

MYDUMP = "mysqldump-u $ USER-p $ PASSWD-S $ SOCKET"

DATEBASE = "$ ($ MYLOGIN-e" show databases; "| egrep-vi" Data | _ schema | mysql ")"

 

For dbname in $ DATEBASE

Do

TABLE = "$ ($ MYLOGIN-e" use $ dbname; show tables; "| sed '1d ')"

For tname in $ TABLE

Do

MYDIR =/server/backup/$ dbname/$ {dbname} _ $ (date + % F)

[! -D $ MYDIR] & mkdir-p $ MYDIR

$ MYDUMP $ dbname $ tname | gzip> $ MYDIR/$ {dbname }_$ {tname }_$ (date must have f=. SQL .gz

Done

Done

 

View backup details:

[Root @ server002 scripts] # tree/server/backup/

/Server/backup/

── Opuser26

│ ── Opuser26_2015-12-30.

│ │├─Opuser26_opuser_test_2015-12-30. SQL .gz

│ │└─Opuser26_opuser_test2_2015-12-30. SQL .gz

│ ── Opuser26_2015-12-31.

│ ── Opuser26_opuser_test_2015-12-31. SQL .gz.

│ ── Opuser26_opuser_test2_2015-12-31. SQL .gz.

── Opuser_products26

│ ── Opuser_products26_2015-12-30.

│ │├─Opuser_products26_cloudstack_2015-12-30. SQL .gz

│ │├─Opuser_products26_dashborad_2015-12-30. SQL .gz

│ ── Opuser_products26_student_2015-12-30. SQL .gz

│ ── Opuser_products26_2015-12-31.

│ ├─Opuser_products26_cloudstack_2015-12-31. SQL .gz

│ ├─Opuser_products26_dashborad_2015-12-31. SQL .gz

│ └─Opuser_products26_student_2015-12-31. SQL .gz

── Products26

── Products26_2015-12-30

Zookeeper extends products26_openstack_2015-12-30. SQL .gz

Zookeeper extends products26_saltstack_2015-12-30. SQL .gz

│├──Products26_server_2015-12-30. SQL .gz

Invalid invalid products26_zabbix_2015-12-30. SQL .gz

── Products26_2015-12-31

├──Products26_openstack_2015-12-31. SQL .gz

├──Products26_saltstack_2015-12-31. SQL .gz

├──Products26_server_2015-12-31. SQL .gz

└──Products26_zabbix_2015-12-31. SQL .gz

9 directories, 18 files

[Root @ server002 scripts] #

This article permanently updates the link address: