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
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.