Legal stuff about this site and your private life:

  • We use a cookie to keep your preferences (language, NSFW status, login status) during navigation.
  • We use your local storage to save the announces you closed (like this one).
  • We don't save informations you don't give
  • We don't share your email address if you comment or register
  • There is no tracker of any kind.

If you're not OK with this, move your way.

This post is mainly an aggregation of ressources found there and there on the web in order save a MySQL database on a regular time basis, making the dumps available in a network shared folder.

Setup :

This article was written for a RaspberryPI with a Raspbian and a MySQL server. The steps should be the same for other Linux distributions.

MySQL user

First, we will create a mysql user wich only privileges will be to read the db:

On the raspberry pi, type:

  • mysql -uroot -p to log in Mysql as root (password will be prompted).
  • On the MySQL prompt : GRANT LOCK TABLES, SELECT ON DATABASE.* TO 'BACKUPUSER'@'%' IDENTIFIED BY 'PASSWORD'; to create the user BACKUPUSER with password PASSWORD with privileges on all tables in DATABASE. You can replace DATABASE with * to grant privileges on all dbs.
  • Then flush the privileges with flush privileges;.
  • You can now log out with exit;

The Cron task (Source)

First, create the folder where the dumps will be saved:

  • sudo mkdir /var/db_dumps

The task will be created on the current Raspberry, so this will be a local task:

  • Edit /etc/crontab with your favorite editor and add the following: 0 * * * * USER /usr/bin/mysqldump DATABASE -uBACKUPUSER -pPASSWORD > /var/db_dumps/filename.sql. If you plan to backup all your databases, replace DATABASE by --all-databases. USER is the a system user
  • If you want to have the date in your file name, use something like filename_`date +\%Y\%m\%d_\%H\%M`.sql (Source]
  • To help you creating the cron job, you can use the Crontab-generator

Setting up Samba share

For the files to be available on the network, i'll use Samba.

  • Install Samba with sudo apt-get install samba
  • Edit file /etc/samba/smb.conf
  • Add the following at the end of the file (got help from this):
  comment=Dumps of the MySQL server
  read only=yes
  guest ok=yes

That's it...

Following this, your database will be saved every hour on a publicly read-only folder on your network. To go further, you can disable other shares in /etc/samba/smb.conf if you don't need it

Leave a comment

You want to react to this content or ask something to the author? Just leave a comment here!

Note that the comments are not publicly visible, so don't worry if you don't see yours.

All the information you give will only be visible to the author. We don't share anything with anyone.

Don't fill this field if you want this form to be saved.