Backup mysql database linux bash script

Пример скрипта для создания резервной копии MySQL

Данный скрипт написан на Unix Shell под управлением операционной системы CentOS. Он будет работать на большинстве систем семейств Linux и BSD.

Пример скрипта

Скрипт будет создавать для каждой базы свой дамп. Это необходимо для быстрого восстановления данных.

Создаем каталог для скриптов и сам скрипт:

1. #!/bin/bash
2. PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
3. destination=»/backup/mysql»
4. userDB=»backup»
5. passwordDB=»backup»
6. fdate=`date +%Y-%m-%d`
7. find $destination -type d \( -name «*-1[^5]» -o -name «*-[023]?» \) -ctime +30 -exec rm -R <> \; 2>&1
8. find $destination -type d -name «*-*» -ctime +180 -exec rm -R <> \; 2>&1
9. mkdir $destination/$fdate 2>&1
10. for dbname in `echo show databases | mysql -u$userDB -p$passwordDB | grep -v Database`; do
11. case $dbname in
12. information_schema)
13. continue ;;
14. mysql)
15. continue ;;
16. performance_schema)
17. continue ;;
18. test)
19. continue ;;
20. *) mysqldump —databases —skip-comments -u$userDB -p$passwordDB $dbname | gzip > $destination/$fdate/$dbname.sql.gz ;;
21. esac
22. done;

Задаем права скрипту на выполнение:

chmod +x /scripts/mysql_backup.sh

Описание скрипта

1	Указываем на путь к интерпретатору.
2	Задаем системные переменные, чтобы не пришлось в скрипте прописывать полные пути до исполняемых файлов.
4 — 7	Задаем переменные.
4	Каталог, в котором будем сохранять резервные копии.
5	Учетная запись для подключения к базе данных.
6	Пароль для подключения к базе данных.
7	Дата, когда запускается скрипт.
9	Находим все резервные копии, которые старше 30 дней и удаляем их. Оставаляем для архива файлы на 15 число.
10	Удаляем все резервные копии старше 180 дней.
11	Создаем каталог, в который будем сохранять резервные копии. В качестве имени каталога используем дату запуска скрипта в формате ГГГГ-MM-ДД.
13 — 25	Подключаемся к базе данных и вытаскиваем список всех баз данных. Для каждой делаем резервную копию.
15 — 22	Пропускаем служебные базы information_schema, mysql, performance_schema, test.
23	Делаем резервную копию для баз.

Подготовка системы

Подключаемся к базе данных и создаем учетную запись с правом на создание резервных копий:

> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS ON *.* TO backup@localhost IDENTIFIED BY ‘backup’;

* в данном примере мы создаем учетную запись backup с паролем backup.

Создаем каталог, в котором будут храниться резервные копии:

mkdir -p /backup/mysql

Сохранение данных на удаленном компьютере

Резервные копии необходимо создавать на удаленном компьютере или внешнем диске, чтобы они были доступны при выходе из строя сервера. В данном примере используется общая папка на удаленном сервере, в которой будут размещаться файлы с backup.

Чтобы упростить процесс монтирования сетевой папки, откроем на редактирование следующий файл:

и добавим в него следующую строчку:

//192.168.0.1/backup /mnt cifs user,rw,noauto,credentials=/root/.smbclient 0 0

* в данном примере выполняется монтирование общей папки backup на сервере с IP-адресом 192.168.0.1 в каталог /mnt. В качестве сетевой файловой системы используется cifs (протокол SMB: сервер samba или общая папка Windows). Параметры для подключения — user: позволяет выполнить монтирование любому пользователю, rw: с правом на чтение и запись, noauto: не монтировать автоматически при старте системы, credentials: файл, в котором написаны логин и пароль для подключения к общей папке.

Теперь создадим файл с логином и паролем:

и приведем его к следующему виду:

* username: имя пользователя, password: пароль. Само собой, в вашем случае указываются свои данные.

Теперь введите следующую команду:

Должна примонтировать сетевая папка //192.168.0.1/backup. Это можно проверить следующей командой:

Автоматический запуск по расписанию

Создадим правило в cron для автоматического запуска нашего скрипта. Выполняем команду:

И добавляем строку:

0 3 * * * /scripts/mysql_backup.sh

* в данном примере мы будем запускать резервирование каждый день в 3 часа ночи.

Источник

Shell Script To Backup MySql Database Server

Save the script and run it as a cron job:
@daily /path/to/yourmysql.sh

Category	List of Unix and Linux commands
Documentation	help • mandb • man • pinfo
Disk space analyzers	df • duf • ncdu • pydf
File Management	cat • cp • less • mkdir • more • tree
Firewall	Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04
Linux Desktop Apps	Skype • Spotify • VLC 3
Modern utilities	bat • exa
Network Utilities	NetHogs • dig • host • ip • nmap
OpenVPN	CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04
Package Manager	apk • apt
Processes Management	bg • chroot • cron • disown • fg • glances • gtop • jobs • killall • kill • pidof • pstree • pwdx • time • vtop
Searching	ag • grep • whereis • which
Shell builtins	compgen • echo • printf
Text processing	cut • rev
User Information	groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w
WireGuard VPN	Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04

Great script! I really like the addition of the which command.

this looks super cool, however can anyone explain me the meaning of

MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”
CHOWN=”$(which chown)”
CHMOD=”$(which chmod)”
GZIP=”$(which gzip)”

what does the which command do ? do we have to write tht

it gives the full path of the executable

which mysql
/usr/bin/mysql

all, you are done great server works,

Just my one cent:
run rsync daemon using the backup directory just to be sure you are playing safe. Is worthless to backup MySQL not having a copy in remote location in case of a server crash.

I have wasted hours trying to do this in perl. One small copy and paste from your site and all of the mysql databases are now being automatically backed up. Thank you so much. You are worth your weight in gold.

Forgive the ignorance but would you have to stop the mySQL service before running this script?

this script is very good.

Can anybody having a script to extract database from MYSQLDUMP.

`mysql -u $user -p Deven J Feb 4, 2010 @ 15:56

Undoubtedly, very simple and useful.

Just a note that will hopefully help others. I’m just starting to learn shell scripting all my other ones have been using phpcli so take this with a grain of salt. I was getting a bunch of these errors.

I had to change these lines and use the single = comparison instead of ==

[ “$db” = “$i” ] && skipdb=1 || :
done
fi

if [ “$skipdb” = “-1” ] ; then

I would think that changing the operator to = would be assigning the “$i” to “$db”

From my knowledge of bash scripting, or the use of bourne shell commands, it is necessary for the use of == for comparisons as well as the spaces around the subscript operators.

Did changing those to = instead of == allow it to run properly?

Zeb is right, interesting, the double equal worked for me in Ubuntu 16.04 but did not work in Ubuntu 14.04 (not the same bash versions).
Actually, to declare or fill a variable you should not leave any space like:
$db=$i

This script is still useful almost 2017 so thanks again.

Nice Post.. I have use this post as you say and It work exactly as I want. but Is it possible to copy backup directory to other server for security If this server is totally crash? Only daily folder has to copy to other server.

if i need to backup each database in seperate file and want to know whether the databse dump was succesful or not.

could anyone please help with that

Thank you for the handy script.

At least on our Fedora 10 installation, the gzip binary looks for an env var GZIP and uses it as a default argument list if it’s set.

So this
$GZIP -9
is equivalent to this (for our binaries)
/bin/gzip /bin/gzip -9

Since I wasn’t running the script as root, this manifested itself as a somewhat misleading permissions error that took a while to track down since I mistook the /bin/gzip.gz as a reference to a helper binary (like fsck.ext2, for instance):
gzip: /bin/gzip.gz: Permission denied

Could I request that something besides GZIP be used (e.g. GZIPBIN, GZ)?

Thanks so much for taking the time to put up this script; you’re a real life-saver. I realised today (after a big scare with fsck) that I have no facility for backing up my databases on my development machine! I shall be adding this forthwith.

Excellent script .. Thanks mate

For some reason when I tried this it seemed to work — it created the .gz files in my backup directory. But when I tried an experiment of dropping one of the databases and then using the backed-up .gz file to restore from (via cpanel utility), it created an empty database with no tables in it. Not sure why 🙁

I have this exact same problem. Does anyone have any ideas about this?

If your file name is db2.11_02_35am.gz, then use the following commands to reinstall the database:

This script is a lifesaver, thank you.

I do have a quick question, for the IGGY, do you put the databases you do not want backed up just separated by commas?

No, just separated by white space:

F##ing Great! Thanks a lot for the good work!

Does it output some information if something goes wrong? i´m trying to write to a logfile, just wondering it outputs success or failure results? Sorry, i just lack the skills to tell if it does so.

Is possible send mysql database to e-mail with daily cron?

mutt -a /home/website/sql_dump.zip -s “Sql Backup” youremail@email.com

Hi Vivek, thanks for such a good script.
in this script you are creating one file for one db. Can I create one file for each table of a db.
File name would be like dbname-filename-date … etc….

Thanks in advance..

# File to store current backup file
FILE=””
# Store list of databases
DBS=””

# DO NOT BACKUP these databases
IGGY=”test”

Hey I want some help on above.
What shall i add in file?
And can I add multiple dbs in DBS and IGGY ?If so? then how?gimme one example.
THanks

You can enter databases separated with a space here that you don’t want to backup.

Example:
IGGY=”test test2 test3”

You dont need to add any databases to the “file” or “dbs” variables. Those empty values just initialize the variable. If you want to skip databases, use a standard list, no commas etc.
Example:

I try this script in Ubuntu 10.04 and works great. When I move to centos 5.6 I get the following error:

No such file or Directory /usr/bin/mysql

I try the line: DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)” manually and its works great. Any ideas?

Sorry. Mispelld centos 5.4

I am new to MYSQL. I saw your script. Please help me understand what you are doing
in
MyUSER=”SET-MYSQL-USER-NAME” # USERNAME
MyPASS=”SET-PASSWORD” # PASSWORD
MyHOST=”localhost” # Hostname
eg: variable MyUSER will contain SET-MYSQL-USER-NAME, but i have not see this being used as mysqldump anywhere.

is INFORMATION_SCHEMA backed up in your script ? Is this useful to backup?

-all-databases will dump ALL databases, is there any advantage for backing up
each database?

Q1: …but i have not see this being used as mysqldump anywhere.
A1: see the last if condition.

Q2a: is INFORMATION_SCHEMA backed up in your script ?
Q2b: Is this useful to backup?
A2a: …if it appears in the show databases result, and it is not set to be skipped in the script, then yes.
A2b: could be. The point is data recovery. Preview that database to see what you could potentially lose if not.

Q3: …any advantage for backing up each database?
A3: this is merely an example that you can tailor to your need(s). Having them separately could be advantageous when not “all databases” need to be restored.

thanks! works like a charm! do you rotate or, delete old backups?

I want to use this mysql backup script with incremental an weekly full backup.

Can you i use this for this purpose and also facing error during the run this script.

Error:- [: 70: -1: unexpected operator

I am facing error during run the script

[: 64: =-1: unexpected operator

You can the below lines before creating directory for today’s backup.

RDIR=”$DEST/mysql/$(date -d “7 day ago” +”%d%m%Y”)”
RDIR=
if [ -d $RDIR ];
then
rmdir $RDIR;
echo “Removing MySQL backup of date $(date -d “7 day ago” +”%d%m%Y”)”
fi
Thanks,
Narendra Gollapilli

The above lines i have mentioned for backup rotation for 7 days i.e removing data older than 7 days.

instead of # DO NOT BACKUP these databases,
how can i do it as they other way around? (selecting specific database).

i am very new to linux. please help. thanks alot!

you can do so by modifying

DBS=””
or
DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’ |grep )”
as it will return a list of your databases only which contain the given text.

It works for my without any error as i saved this as .sh file

Here is a few tips for things that I ran into.

First off, root doesnt have access to locked tables, so this is what I did to fix that.

Edit this line:
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE

To:
$MYSQLDUMP –skip-lock-tables -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE

By adding the –skip-lock-tables, the command will ignore the locktable feedback and continue on what it can actually do.

Secondly, I also was recieving an error like “\r’: command not found”. This is caused from the way DOS(windows) writes a return key. This can be fixed by the following 2 commands:

apt-get install dos2unix #if it is not installed already
dos2unix /path/to/file.sh

I hope this helps someone.
Enjoy

Nice little script, handy and quick to use.

Ashok: Try to delete one of the equal signs on the actual rows you are having problems with.

Using Narendra Gollapilli’s prune lines in Ubuntu Server 11.04, had to change from “rmdir” to:
rm -rf $RDIR;
First day so I did not test the seven days cicle, other than that it is working. Also have to replace html left and right “” for “.
About Niftyapple’s –skip-lock-tables note that html on this page replaced — for –.
The correct is –skip-lock-tables
Thanks!

I mean TWO dashes, sorry it did with me again – – .
WordPress changes a lot of code stuff…
two – to one —

Can you help my problem ?

in line 55 = DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)”

when i try to execute, this syntax has a problem, can anyone help to fix it ?

This is a really nice script although I did some changes for it, nothing major:

– Commented the CHOWN. For some reason is was doing a root wide CHOWN, being that I am not running the backups as root, I do not need to CHOWN the files anyway, they’ll be owned by the administrative account.
– I had to add the list of databases that I wanted not to back up, I though that just adding the ones that I wanted would exclude the ones that I did not wanted to.

But, it is a very handy script and I appreciate you posting it. Thanks.

Источник