MySQL database backup small record

MySQL database backup is the most important thing for every data administrator, and it can help us at a critical time. We recommend that you back up the database, table data, table space, triggers, functions, views of what the separate backup, so that can save backup time, when recovering data more convenient.

The backup method taken is to use the shell in conjunction with crontab, in the case of more databases, it is recommended to back up 5 to 6 libraries per script

Let's just give you a quick example

Mysql-adson database server, there are 3 databases as follows: Adson_inter,adson_adm,adson_hi

The backup method taken is table database, table space, trigger separate backup; Create/mysqlbak to store backup data, and then create separate directories: Adson_inter,adson_adm,adson_hi

Create adsonbak_1.sh under the/script/directory

#!/bin/sh

Rq= ' date-d last-day +%y%m%d '

#备份表结构

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_inter--skip-triggers-d-r | gzip >/mysqlbak/adson_inter/adson_inter_$rq.tb_str.sql.gz

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_adm--skip-triggers-d-r | gzip >/mysqlbak/adson_adm/adson_adm_$rq.tb_str.sql.gz

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_hi--skip-triggers-d-r | gzip >/mysqlbak/adson_hi/adson_hi_$rq.tb_str.sql.gz

#备份数据

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_inter--skip-triggers-t-r | gzip >/mysqlbak/adson_inter/adson_inter_$rq.dt_thing.sql.gz

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_adm--skip-triggers-t-r | gzip >/mysqlbak/adson_adm/adson_adm_$rq.dt_thing.sql.gz

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_hi--skip-triggers-t-r | gzip >/mysqlbak/adson_hi/adson_hi_$rq.dt_thing.sql.gz

#备份触发器

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_inter--triggers-d-t-r | gzip >/mysqlbak/adson_inter/adson_inter_$rq.tr_thing.sql.gz

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_adm--triggers-d-t-r | gzip >/mysqlbak/adson_adm/adson_adm_$rq.tr_thing.sql.gz

/usr/bin/mysqldump-uroot-pagfigjky--single-transaction--database adson_hi--triggers-d-t-r | gzip >/mysqlbak/adson_hi/adson_hi_$rq.tr_thing.sql.gz

Execute permissions for adsonbak_1.sh, and then set crontab

# crontab-l

0 6 * * */bin/bash/script/adsonbak_1.sh

Set 6-point execution per day

You can then set up automatic retention of backup data with the actual.

This article is from "Irene's Blog" blog, please be sure to keep this source http://adson10.blog.51cto.com/6283173/1899919

MySQL database backup small record