DragonPrime - LoGD Resource Community
Welcome Guest
  • Good evening, Guest.
    Please log in, or register.
  • October 17, 2018, 05:21:39 PM
Home Forums News Downloads Login Register Advanced Search
* * *
DragonPrime Menu
Login
 
 
Resource Pages
Search

Pages: 1 2 [3]   Go Down
  Print  
Author Topic: Site Backup (Database & Files) v2.0 : File Submission/Discussion  (Read 9650 times)
0 Members and 1 Guest are viewing this topic.
Aeolus
Mod God
*****
Offline Offline

Posts: 1896


You're welcome.


View Profile WWW
« Reply #30 on: December 11, 2017, 05:22:11 PM »

Tip for anyone using this module. To decrease the size of a database backup, saving the accounts_output table is not overly necessary (as the output is regenerated every time a player loads their page), and it constitutes about 75% of the size of a database backup.

To not backup the accounts_output table, update the ./modules/site_backup/site_backup_database.php file with the following instructions.

v1.3.1 (free server version):
Replace:
Code:
<?php
$table 
"`".$table."`";

With:
Code:
<?php
if ($table == "accounts_output") continue;
$table "`".$table."`";

v2.0 (paid server version):
Replace:
Code:
<?php
exec
('mysqldump --opt -h'.$DB_HOST.' -u'.$DB_USER.' -p'.$DB_PASS.' '.$DB_NAME.' > '.$_SERVER['DOCUMENT_ROOT'].'/'.$filename.'.sql'$output=array(), $worked);

With:
Code:
<?php
exec
('mysqldump --opt -h'.$DB_HOST.' -u'.$DB_USER.' -p'.$DB_PASS.' '.$DB_NAME.' --ignore-table='.$DB_NAME.'.accounts_output > '.$_SERVER['DOCUMENT_ROOT'].'/'.$filename.'.sql'$output=array(), $worked);
« Last Edit: December 11, 2017, 05:25:56 PM by Aeolus » Logged

phu
Guest
« Reply #31 on: December 11, 2017, 07:42:12 PM »

In this case, if you want to be able to restore directly from the backup without having to first initialize the database (or at least the now-missing accounts_output table), I think it'd be a good idea to split the database export into two statements.

Code:
<?php

exec
('mysqldump --no-data -h'.$DB_HOST.' -u'.$DB_USER.' -p'.$DB_PASS.' '.$DB_NAME.' > '.$_SERVER['DOCUMENT_ROOT'].'/'.$filename.'_schema.sql'$output=array(), $worked);

exec('mysqldump --no-create-info --opt -h'.$DB_HOST.' -u'.$DB_USER.' -p'.$DB_PASS.' '.$DB_NAME.' --ignore-table='.$DB_NAME.'.accounts_output > '.$_SERVER['DOCUMENT_ROOT'].'/'.$filename.'_data.sql'$output=array(), $worked);

I was setting up a test for this and then realized I don't even have PHP installed locally, so I'm not sure if this works exactly as-is, but the concept is sound: Get a full schema-only dump with --no-data to make sure you have all of the tables, then get the data using your original call with --no-create-info (skips creation statements) so you won't get the unnecessary account data.

This way running the schema dump first then the data dump will result in a complete database, without breaking functionality that expects accounts_output to exist.

Warning: These exec statements are not safe. A good password is likely to contain special characters, and dropping those into a command like this will likely result in failure. Ideally, this would be where something like a parameterized function call would be used; this being PHP, that probably doesn't exist, which is presumably why escapeshellcmd exists.

It'd need to be tested, but something like this should be safer:

Code:
<?php

exec
(escapeshellcmd('mysqldump ...'));
« Last Edit: December 11, 2017, 07:46:27 PM by phu » Logged
Sunday
Codemeister
****
Offline Offline

Posts: 399


So meme'd up.


View Profile
« Reply #32 on: December 13, 2017, 01:12:29 AM »

In this case, if you want to be able to restore directly from the backup without having to first initialize the database (or at least the now-missing accounts_output table), I think it'd be a good idea to split the database export into two statements.

Code:
<?php

exec
('mysqldump --no-data -h'.$DB_HOST.' -u'.$DB_USER.' -p'.$DB_PASS.' '.$DB_NAME.' > '.$_SERVER['DOCUMENT_ROOT'].'/'.$filename.'_schema.sql'$output=array(), $worked);

exec('mysqldump --no-create-info --opt -h'.$DB_HOST.' -u'.$DB_USER.' -p'.$DB_PASS.' '.$DB_NAME.' --ignore-table='.$DB_NAME.'.accounts_output > '.$_SERVER['DOCUMENT_ROOT'].'/'.$filename.'_data.sql'$output=array(), $worked);
Warning: These exec statements are not safe. A good password is likely to contain special characters, and dropping those into a command like this will likely result in failure. Ideally, this would be where something like a parameterized function call would be used; this being PHP, that probably doesn't exist, which is presumably why escapeshellcmd exists.

It'd need to be tested, but something like this should be safer:

Code:
<?php

exec
(escapeshellcmd('mysqldump ...'));

An excellent suggestion! It is nice to see someone take security into account. The exec commands will work, since it is just executing the command - noting seems out of place there. Also, escapeshellcmd() will (should) break any special character passwords if the server is running on Windows.
Logged

Slowly progressing fork with PHP 7 support: https://github.com/stephenKise/Legend-of-the-Green-Dragon
Cheap VPS Hosting (10$ credit!): https://m.do.co/c/acde75b086c5

A new server in the making...
Pages: 1 2 [3]   Go Up
  Print  
 
Jump to:  


*
DragonPrime Notices
Version 1.1.2 is the current supported version and is available for download.

Support Us
No funds raised yet this year
Your help is greatly appreciated!
Recent Topics
DragonPrime LoGD
Who's Online
39 Guests, 0 Users
Home Forums News Downloads Login Register Advanced Search