I'm thinking about setting up a network configuration, where the backup server and the MySQL server are on the same machine, and where the webserver is on a separate machine.
It's on an IPv4 network. I plan to have the webserver on the DMZ all the time, but I'd like to be able to access the MySQL / Backup server from the webserver.
Now from what I know about networking, I'm pretty sure you're supposed to have your MySQL and Backup on the LAN, with a firewall rule that allows the webserver to access MySQL and the Backup.
Presently I've got it working in the following way (which I believe works, but isn't necessarily the correct way to accomplish this...) see diagram below:
Now I think the correct way to go about doing this is to keep the backup server / mysql box on the lan and access it through a rule (or is it forwarding) in the firewall. However, the backup tool I'm using requires that the webserver initiate the backup...so how does one (forward?) packets from the dmz to the machines on the lan? And is that even a good idea?
I'll agree with your second diagram. Indeed, it's always a good ideal to place the SQL server in your LAN environment, rather than the DMZ. In such a setup, you simply require the appropriate access lists on each interface of your router, so that it allows the seamless communication between the required hosts, which in your case is your Web Server and SQL server.
You'll need to identify the required ports, so you can fine-tune your router's access lists to allow communication between the two server's only for specific services e.g www, mysql etc.
Another suggestion, if the data held on the Mysql server is not critical/sensitive, is to leave the MySQL server in the DMZ zone (which solves your backup problem) , and have an automated process where the MySQL server copies its backup to another server in your LAN.
Alternatively, if the Mysql server moves to the LAN network, you'll need to find a way to overcome the initiation of your backup process.
Closing, its a good idea to have the backup machine separate from your Mysql server. If the Mysql server gets hacked, you'll end up loosing everything!
Let us know if you require any additional help or have any further questions.