ArcEmu: [php] Backing Up A Database - ArcEmu

Jump to content

Toggle shoutbox Lastest Announcements

dfighter  : (07 December 2014 - 12:06 PM) Arcemu is in hibernation mode, please read http://arcemu.org/fo...showtopic=26903
dfighter  : (01 January 2013 - 05:56 PM) Arcemu wishes you all a happy new year!
Hasbro  : (12 September 2012 - 10:01 AM) Please excuse our outage from the web! Our web host had a major malfunction!
dfighter  : (01 September 2012 - 04:05 PM) Since the spam bots just don't want to stop, I've enabled admin verification when registering.
dfighter  : (23 January 2012 - 09:56 PM) Please note that from now on you will need to confirm your email on the wiki in order to edit it!
Hasbro  : (31 December 2011 - 12:50 PM) Happy New Years all!
Navid  : (26 December 2011 - 04:09 AM) Merry Christmas !!!!!! Happy holidays all :)
WAmadeus  : (24 December 2011 - 03:54 PM) Merry Christmas to all!
dfighter  : (24 December 2011 - 11:05 AM) The Arcemu team wishes y'all a Merry Christmukkah!
Hasbro  : (05 October 2011 - 12:53 PM) Looking for web designers for upcoming web related project. If you're interested in designing user interfaces contact me
dfighter  : (02 September 2011 - 03:47 PM) So who here wants vehicles in Arcemu? :P http://arcemu.org/fo...showtopic=25440
Hasbro  : (14 August 2011 - 03:25 PM) Join us on irc, grab an irc client and connect to irc.freenode.net join channel #arcemu /server irc.freenode.net:6667 /join #arcemu
jackpoz  : (03 August 2011 - 05:33 AM) to all Lua Engine (old one) users: please check http://arcemu.org/fo...showtopic=25274
Hasbro  : (20 May 2011 - 05:27 PM) Looking for people experienced with CMake configuration and setup! Contact me asap
Hasbro  : (15 May 2011 - 05:03 PM) ArcEmu is recruiting C++ programmers, contact Hasbro if interested.
paroxysm  : (03 May 2011 - 06:26 PM) Updated luabridge gossip example to describe the whole gossip creation process rather than just how to create menu. Gossip tutorial
paroxysm  : (23 April 2011 - 11:35 AM) Lua writers can refer to the Luabridge Tutorials section in the Wiki to learn how to write gossip code correctly.
Hasbro  : (20 April 2011 - 05:22 PM) Thank you for your continuous contribution of bug reports, we are working on them.
Hasbro  : (17 April 2011 - 03:20 AM) Please consider donating to support our bills. Donations can be sent using PayPal to donations@arcemu.org - Thank you for your support.
paroxysm  : (10 April 2011 - 12:43 AM) Refer to the Luabridge Tutorials section in the Wiki to learn the new syntax of luabridge.
Resize Shouts Area

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • This topic is locked

[php] Backing Up A Database

#1 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 20 March 2010 - 09:09 PM

So here is my code but all it does when it runs is create a zipped archive that is empty. Lol and I have no idea why. Anyone got a solution?

configs
http://halestorm.pastebin.com/mM0CDwpN

open connection
http://halestorm.pastebin.com/es1hc289

actual script
http://halestorm.pastebin.com/9PDZA5aQ

close connection
http://halestorm.pastebin.com/XfT9qRKm

So what is the problem and for those wondering it is in general and not workshop cause it is not underdevelopment.
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#2 User is offline   Cid 

  • Member
  • Pip
  • Group: Members
  • Posts: 29
  • Joined: 22-December 08
  • Gender:Male
  • Location:Georgia, U.S.A.
  • Interests:computers, aviation, and gaming

Posted 21 March 2010 - 05:27 AM

View PostHalestormXV, on 20 March 2010 - 09:09 PM, said:

So here is my code but all it does when it runs is create a zipped archive that is empty. Lol and I have no idea why. Anyone got a solution?

...


You running this server under linux or windows? I see gzip so must be linux :)

In either case I'd suggest a shell (linux) or a batch (windows) instead of using php. Linux tends to make things real complicated when permissions come into play, even more so with php under linux.

I have a remote server restarter than I can execute from my web-browser which works great under windows... but my server normally runs under linux where the same script doesn't work at all. ( probably cause I have to dig deep for some permission settings somewhere and I'm not in the mood to dig yet )
0

#3 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 24 March 2010 - 08:12 PM

Move to web development as it seems better suited

Well I need to do it in PhP because it is being done from my website and it is backing up a third party database. So it is not actually linked to my database. The code is just being run from my website as a Cronjob and backing up the database on the thirdparty computer. And yes the user that the script uses has full permissions to actually backup the database via MySQL user controls.
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#4 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 25 March 2010 - 08:35 PM

Your problem is probably that most webhosts don't allow mysqldump. Use this instead (I've slightly modified it to fit your needs):

/* backup the db */
function backup_tables($host,$user,$pass,$db)
{
	#We don't want to backup continuously, right?
	if(file_exists('db-backup-'.@date("m-d").'sql') return;

	$link = mysql_connect($host,$user,$pass);
	mysql_select_db($db,$link);
	
	//get all of the tables
	$tables = array();
	$result = mysql_query('SHOW TABLES');
	while($row = mysql_fetch_row($result))
	{
		$tables[] = $row[0];
	}
	
	//cycle through
	foreach($tables as $table)
	{
		$result = mysql_query('SELECT * FROM '.$table);
		$num_fields = mysql_num_fields($result);
		
		$return.= 'DROP TABLE '.$table.';';
		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
		$return.= "\n\n".$row2[1].";\n\n";
		
		for ($i = 0; $i < $num_fields; $i++) 
		{
			while($row = mysql_fetch_row($result))
			{
				$return.= 'INSERT INTO '.$table.' VALUES(';
				for($j=0; $j<$num_fields; $j++) 
				{
					$row[$j] = addslashes($row[$j]);
					$row[$j] = ereg_replace("\n","\\n",$row[$j]);
					if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
					if ($j<($num_fields-1)) { $return.= ','; }
				}
				$return.= ");\n";
			}
		}
		$return.="\n\n\n";
	}	
  
	//save file
	$handle = fopen('db-backup-'.@date("m-d").'sql','w+');
	fwrite($handle,$return);
	fclose($handle);

	return $return;
}

#Here's an example:
$backup = backup_tables('127.0.0.1', 'root', 'root', 'mydb');
echo "<textarea>{$backup}</textarea>";


All I did was:
  • Made it do all the tables automatically
  • Made the file saved as db-backup-(numerical value of the month)-(numerical value of the day)
  • Changed param 'name' to 'db'
  • Added a checker to see if the database was already backed up
  • Added a return, so that you can do stuff with the SQL if needed...
  • And made my own example, that is echoed within a textarea.


The rest was done by David Walsh

P.S. Not tested.
0

#5 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 25 March 2010 - 09:49 PM

So I plan to make this backup two databases. What do you think is more effective? Having it select two databases or copy pasting the first code and then changing the variables around and have it select the second database.
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#6 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 26 March 2010 - 07:44 PM

Looking back over the code, that's going to write over the same file. So, you can just do (using your settings of course):

/*
|--------------------------------------------------------------------------
| Database Connection Settings
|--------------------------------------------------------------------------
|
| This is the connection settings for your server's database.
| 
*/

#Database 1
$db['db1']['host'] = '127.0.0.1';
$db['db1']['user'] = 'root';
$db['db1']['pass'] = 'root';
$db['db1']['dbname'] = 'mydb';

#Database 2
$db['db2']['host'] = '127.0.0.1';
$db['db2']['user'] = 'root';
$db['db2']['pass'] = 'root';
$db['db2']['dbname'] = 'mydb';


# No touchy below here ^_^
# (Unless you know what you're doing)

/* backup the db */
function backup_tables($host,$user,$pass,$db)
{
        #We don't want to backup continuously, right?
        if(file_exists('db-backup-'.@date("m-d").'sql') return;

        $link = mysql_connect($host,$user,$pass);
        mysql_select_db($db,$link);
        
        //get all of the tables
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result))
        {
                $tables[] = $row[0];
        }
        
        //cycle through
        foreach($tables as $table)
        {
                $result = mysql_query('SELECT * FROM '.$table);
                $num_fields = mysql_num_fields($result);
                
                $return.= 'DROP TABLE '.$table.';';
                $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
                $return.= "\n\n".$row2[1].";\n\n";
                
                for ($i = 0; $i < $num_fields; $i++) 
                {
                        while($row = mysql_fetch_row($result))
                        {
                                $return.= 'INSERT INTO '.$table.' VALUES(';
                                for($j=0; $j<$num_fields; $j++) 
                                {
                                        $row[$j] = addslashes($row[$j]);
                                        $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                                        if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                                        if ($j<($num_fields-1)) { $return.= ','; }
                                }
                                $return.= ");\n";
                        }
                }
                $return.="\n\n\n";
        }       
        return $return;
}

#And now, display the SQL!
$db1 = backup_tables($db['db1']['host'], $db['db1']['user'], $db['db1']['pass'], $db['db1']['dbname']);
echo "<b>Database '{$db['db1']['dbname']}':</b><br>";
echo "<textarea>{$db1}</textarea>";

$db2 = backup_tables($db['db2']['host'], $db['db2']['user'], $db['db2']['pass'], $db['db2']['dbname']);
echo "<b>Database '{$db['db2']['dbname']}':</b><br>";
echo "<textarea>{$db2}</textarea>";


Once again, this isn't tested. If there are errors, just tell me :)
0

#7 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 27 March 2010 - 01:17 AM

Quote

$return.= 'DROP TABLE '.$table.';';

Nothing should need to be dropped at all....

For what purpose? And as for your file names they are just going to overwrite themselves. You should probably add the name of the database that is selected to the beginning of the file name and most likely you should probably have PhP autocompress it for you like in my script above.
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#8 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 27 March 2010 - 10:17 AM

1) The tables do need to be dropped. When you're backing up a database by running the query, if the table is found the query will just stop skipt making the table... Even if the table was completely edited by someone. By deleting it, the query can then just create a new table.

2) I know the sql files overwrite themselves, which is why in the second post I changed the script so that it echos the sql in a text area.

As for compressing, go ahead if you want. I'm not such a huge fan of php's compression as it has proven itself as unreliable.
0

#9 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 27 March 2010 - 09:24 PM

View PostBob Herman, on 27 March 2010 - 10:17 AM, said:

1) The tables do need to be dropped. When you're backing up a database by running the query, if the table is found the query will just stop skipt making the table... Even if the table was completely edited by someone. By deleting it, the query can then just create a new table.

2) I know the sql files overwrite themselves, which is why in the second post I changed the script so that it echos the sql in a text area.

As for compressing, go ahead if you want. I'm not such a huge fan of php's compression as it has proven itself as unreliable.


Ya see the only problem i have with this is that....with point 1....all my data goes bye bye....
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#10 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 28 March 2010 - 12:44 PM

... Dude. It stores all the information too. It does a FULL backup, of all the information and tables.
0

#11 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 28 March 2010 - 10:30 PM

View PostBob Herman, on 28 March 2010 - 12:44 PM, said:

... Dude. It stores all the information too. It does a FULL backup, of all the information and tables.


I understand that. But this is a server backup script so therefore even though it does a FULL backup it still drops those tables which are being accessed in real time so that is why this script wouldn't be able to work because if it drops a table and then someone does something on the server that makes it try to access it there can be some severe consequences.
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#12 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 29 March 2010 - 06:26 PM

it doesn't actually run the script. It just generates the SQL for the database.
0

#13 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 01 April 2010 - 12:07 AM

Ehm, your welcome. I'm glad your were so thankful and that you rewarded all my hard work with your bountiful thanks. :angry:
0

#14 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 02 April 2010 - 05:15 PM

View PostBob Herman, on 01 April 2010 - 12:07 AM, said:

Ehm, your welcome. I'm glad your were so thankful and that you rewarded all my hard work with your bountiful thanks. :)


I haven't been around these past few days so I wasn't thankful and didn't reward anything so I am unsure as to what you are saying. However now that I am back and saw the post I am testing it and if it works then I thank you. I am unsure where the snippyness came in from and for? :)

EDIT: Yes it works, however it does not backup both files so i just made to separate scripts for that and had to modify it so it creates an actual SQL file instead of a text box and all works well. So thank you :)
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#15 User is offline   Ogchaos 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 404
  • Joined: 30-September 09

Posted 02 April 2010 - 06:25 PM

View PostHalestormXV, on 02 April 2010 - 05:15 PM, said:

I haven't been around these past few days so I wasn't thankful and didn't reward anything so I am unsure as to what you are saying. However now that I am back and saw the post I am testing it and if it works then I thank you. I am unsure where the snippyness came in from and for? :)


lol, I think it was more sarcasm, I'm sure he did not mean it in a rude way.
0

#16 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 02 April 2010 - 11:59 PM

Yeah, it was sarcasm. I hope it works (it should)! :D
0

#17 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 03 April 2010 - 02:29 PM

It works, only problem I am having is trying to have it backup my world database. This script was running for 12 hours and did not complete. Yet the same script works to backup my character database. I know the reason is that the World database is much larger but 12+ hours and it didn't complete? Any idea why that would happen?
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#18 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 03 April 2010 - 03:17 PM

It's probably because the world database is so huge. You could specify which tables to backup, that would probably cut it down some.

By the way, is SOS still up?
0

#19 User is offline   HalestormXV 

  • The Ultimatum
  • PipPipPipPipPipPip
  • Group: Members
  • Posts: 1,031
  • Joined: 07-June 08
  • Gender:Male

Posted 04 April 2010 - 01:42 PM

View PostBob Herman, on 03 April 2010 - 03:17 PM, said:

It's probably because the world database is so huge. You could specify which tables to backup, that would probably cut it down some.

By the way, is SOS still up?


No SoS is no longer up however an entirely new project is in creation. But as for only selecting certain tables to backup for the world database that won't work out so well >_> since I need it all and I use custom tables. I am going to prbly have to find an alternative method to backing up the World database weekly then.
**Owner of the Serenade of Sorrow Funserver**
My Most Recent Video: The Book of Kidou
Posted Image
0

#20 User is offline   Ogchaos 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 404
  • Joined: 30-September 09

Posted 05 April 2010 - 04:05 AM

View PostHalestormXV, on 04 April 2010 - 01:42 PM, said:

I am going to prbly have to find an alternative method to backing up the World database weekly then.


Schedule Weekly Backup's with your SQL editer.
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users