Script backup databases Mysql in a local folder

I decided to completely abandon the use of ispmanager, because it takes up resources, and is used only for one thing — data backup. But since this can be done personally, then keeping the panel on the server for ten sites does not make sense at all.

That is why the first task is to create backup copies of mysql databases. The bases are small in size, so the simplest version of backups will be used - creating archives and placing them in a specific server folder.

Of course, it would be safer to send a sftp backup somewhere to another server in another data center (and the country, which is already there), but since my sites change a couple of times a month, downloading backups every two days is not hard by occupation. :)

I had no experience in writing scripts in Bash until this moment. But it was not difficult to figure it out, given the numerous examples of mysql backup scripts on various sites about server administration.

The mysqlbackup.sh script itself:

 #! / bin / bash # Initialize the variables TMPDIR = "sqlfiles" BACKUPDIR = "/ storage" USER = "root" PASS = "xxx" TIMEY = $ (date +% Y-% m-% d) # Check the availability of a folder for backups if [!  -d "$ BACKUPDIR"];  then mkdir -m 0700 "$ BACKUPDIR" fi # Check for the presence of a temporary directory if [!  -d "$ BACKUPDIR / $ TMPDIR"];  then mkdir -m 0700 "$ BACKUPDIR / $ TMPDIR" fi # Save copies of cd "$ BACKUPDIR / $ TMPDIR" for database in `mysql -s -r -e 'SHOW DATABASES' |  grep -v Database |  grep -v information_schema |  grep -v performance_schema |  grep -v mysql`;  do mysqldump $ database> $ database.sql; done # Create a cd archive "$ BACKUPDIR" tar -cjf databases - "$ TIMEY" .tbz2 "$ TMPDIR" chmod 0600 databases - "$ TIMEY" .tbz2 # Delete the temporary folder rm - r $ TMPDIR echo "Databases are saved!"  exit 1 

Working with mysql is performed directly from the root user, since he has access to all databases at once.

Database information_schema , mysql , performance_schema are excluded from the backup. Then all databases are placed in a bzip2-archive and stored in a specific folder from where they can be downloaded later.

The task to execute the script once a day is written in the Cron scheduler, for example, at one in the morning.

  00 01 * * * sh ~ / mysqlbackup.sh 

If the correct email is set in the crown settings, then a report will be sent to the post office, in which the saved databases will be listed.

PS You can also create backups and several times a day. To do this, you need to specify the creation time in the name of the archive so that the file is not overwritten.

How do you rate the article?

(No ratings yet)

Loading...