unix mysql 백업 (shell script)

2018.10.14 08:39

엘카 조회 수:692

#!/bin/bash
# Simple script to backup MySQL databases

# Parent backup directory
backup_parent_dir="/root/backup"

# MySQL settings
mysql_user="root"
mysql_password=""

# Read MySQL password from stdin if empty
if [ -z "${mysql_password}" ]; then
  echo -n "Enter MySQL ${mysql_user} password: "
  read -s mysql_password
  echo
fi

# Check MySQL password
echo exit | mysql --user=${mysql_user} --password=${mysql_password} -B 2>/dev/null
if [ "$?" -gt 0 ]; then
  echo "MySQL ${mysql_user} password incorrect"
  exit 1
else
  echo "MySQL ${mysql_user} password correct."
fi

# Create backup directory and set permissions
#backup_date=`date +%Y_%m_%d_%H_%M`
 backup_date=`date +%Y%m%d-%H%M`
#backup_dir="${backup_parent_dir}/${backup_date}"
 backup_dir="${backup_parent_dir}"
echo "Backup directory: ${backup_dir}"
mkdir -p "${backup_dir}"
chmod 700 "${backup_dir}"

# Get MySQL databases
mysql_databases=`echo 'show databases' | mysql --user=${mysql_user} --password=${mysql_password} -B | sed /^Database$/d`

# Backup and compress each database
for database in $mysql_databases
do
  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ] || [ "${database}" == "mysql" ]; then
        # 기본 경로는 백업하지 않기로 additional_mysqldump_params="--skip-lock-tables"
continue
  else
        additional_mysqldump_params=""
  fi
  echo "Creating backup of \"${database}\" database"
  mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${backup_date}_${database}.sql.gz"
  chmod 600 "${backup_dir}/${backup_date}_${database}.sql.gz"
done

 

굿

 

링크 원본: {백업경로}/{백업날짜}/{DB}.gz

내 수정본: {백업경로}/{백업날짜}.sql.gz

번호 제목 날짜 조회 수
81 vmware with centos 7 secret 2019.08.23 0
80 php-fpm tcp to socket secret 2020.01.25 0
79 net::ERR_CONNECTION_REFUSED secret 2021.10.11 0
78 my.cnf secret 2019.04.11 1
77 The best configuration of php7.4-fpm / 8GB 2023.05.04 18
76 To download large files with rsync command. 2023.05.04 18
75 The best configuration of php7.4-fpm / 1.5GB 2023.05.04 20
74 windows hardware encrypt ssd 2024.01.23 123
73 gearman 은 무엇인가? 2023.06.21 169
72 apache2 AllowOverride config 2023.05.23 174
71 php 에서 비동기 처리하는 방법 2023.06.22 209
70 mysqldump from remote db to my local server. 2023.05.04 416
69 yum install php 7.1 [6] 2018.10.10 686
» mysql 백업 (shell script) file 2018.10.14 692
67 munin 설치 2018.10.13 785
66 yum install mariadb [2] 2018.10.10 1142
65 웹 백업 (쉘 스크립트) file 2018.10.14 1190
64 certbot apache multiple domain 2019.04.10 1411
63 yum install php-apcu 2019.03.12 1538
62 yum install latest redis file 2019.03.27 1678