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.
This article was written for a RaspberryPI with a Raspbian and a MySQL server. The steps should be the same for other Linux distributions.
First, we will create a mysql user wich only privileges will be to read the db:
On the raspberry pi, type:
mysql -uroot -pto 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
- You can now log out with
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:
/etc/crontabwith 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
USERis the a system user
- If you want to have the date in your file name, use something like
- 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
- Add the following at the end of the file (got help from this):
[DbDumps] comment=Dumps of the MySQL server path=/var/db_dumps/ browseable=yes read only=yes guest ok=yes public=yes
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