DragonPrime - LoGD Resource Community
Welcome Guest
  • Good evening, Guest.
    Please log in, or register.
  • April 20, 2018, 11:08:19 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 8555 times)
0 Members and 1 Guest are viewing this topic.
Aeolus
Mod God
*****
Offline Offline

Posts: 1862


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
Stephen.Kise
Codemeister
****
Offline Offline

Posts: 390


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
Pages: 1 2 [3]   Go Up
  Print  
 
Jump to:  


*
DragonPrime Notices
Welcome to DragonPrime - The LoGD Resource Community!

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