Best way to backup MySQL database from Laravel Forge production server?

Also, I'd like to note that I would prefer to use artisan commands if possible. :)

https://github.com/backup-manager/laravel might be of use?

Ideally you wouldn't be able to connect to your MySQL installation from anywhere on the internet - It will leave you open to all kinds of security issues.

Why not add your mysqldump script to forge, and have it execute on some regular schedule using cron. Once the data dump has been generated, have the script copy it somewhere safe, like an S3 bucket. The package @ohffs mentioned can do all of this, so it would be simple to add an artisan command to wrap everything up.

I use scripts like this to backup databases on a server: https://bashy.im/blog/backup-mysql-databases-on-vagrant-vm-automatically-to-local-storage

Although it's for Vagrant/Homestead, it can be done on any server.

I have this set up using a cron job, somewhat like bashy has. If you want no hassle and scripting yourself, you could look at something like https://ottomatik.io, seems to be popular among laravel people.

I would recomend you a mysql replication, it will store every change on "real time".

@rikh I do agree that having a mysqldump script running on a regular interval using cron is a great idea. And, I currently am doing this. However, it would be extremely nice to also have a script that lets me make a dump of the remote production database, and restore it to my local development database, in 1 simple command run on my local machine. I previously had achieved this by using a simple shell script that looks like this:

#!/bin/bash

NOW=$(date +"%m-%d-%Y")
mysqldump -h mywebsite.com -p my_database_name > /var/www/mywebsite.com/mysql_backups/$NOW.sql #the -h flag of mysqldump tells it to connect to the remote production server at mywebsite.com, executes mysqldump, and saves the dump on my local machine
mysql -u root -p my_database_name < /var/www/mywebsite.com/mysql_backups/$NOW.sql #this takes the dump that is now on my local machine and restores it to my local development version

I had this saved as a shell script called "mysql_backups.sh". So I just executed this script whenever I wanted and 10 seconds later the production database was synced to my local development database. It was awesome.

In the last day I spent some time learning and familiarizing myself with backup-manager by ShawnMcCool and so far it is AWESOME. I currently am using this to backup my production database at 24-hour intervals using cron. And, it lets me use artisan commands as well! ^_^ And, it saves my dumps remotely into Dropbox which syncs to my local computer! Everything about it so far is better than my current solution.

However, I presently have not been able to find a way using backup-manager to sync my remote production database into my local development database using 1 simple command from my local machine. In order for me to do this, I need to manually ssh into the production server, do a database backup, then go back into my local development machine and restore it. It's not too bad - takes about 1 minute - but I'm used to this taking about 10 seconds.

Or, I can always just restore my local development database from the cron production backup, but then my data in my development version would be up to 24 hours old. :/

@petrit That sounds cool - got any suggestions on any?

@skcin7 refer to this article https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

By the way: You should never allow a mysql remote connection due security. I personally would go for the laravel backup manager, and store the database files in s3 / dropbox or something like that.

Another option is to bind MySQL to the server IP but only allow connections from my other servers (firewall etc).

You can also connect to MySQL through an SSH tunnel.

I use this package from within Laravel: https://github.com/spatie/laravel-backup Very easy to configure and well documented (by @freekmurze ). I highly recommend it!

@SKCIN7 - i know this is an old post but i just wanted to note that on the script, you are storing your sql file in /var/www/mywebsite.com/mysql_backups directory. i'm assuming this is where you website is. but i'm also assuming that if i were to go to http://mywebsite.com/mysql_backups/2019-02-14.sql (or something like that) i will be able to get access to your sql file. so i hope this is not what you're doing still or i hope people did not copy and paste and used a similar path.

just wanted to reply since i just ran in to this post and hope that no one is using a location that is publicly accessible.

Maybe try george? It's a command-line toolkit I built for Forge, and all you need to do to backup your database is run this command:

george mysqldump site-domain.com > dump.sql

And you're done!