Backing Up And Restoring Mysql Databases

Pages: 1, 2
free web hosting
Open Discussion > CONTRIBUTE > Tutorials

Backing Up And Restoring Mysql Databases

s2city
If you're an Administrator on a Forum, you probably know the importance of regular data backups. My Forum is always being hacked by someone and they always delete our SQL Databases. Well this tutorial is for all of you who want to protect your data and restore it if necessary!

Okay, backing up your data is the first part.

I use Cron Jobs in my cPanel to automate the backup process.
Just use this code for backing up all your SQL Databases:
CODE
mysqldump -u root -psecret --all-databases > backup.sql


OR if you wish to backup only a single database:

CODE
mysqldump -u root -psecret stocksdb > stocksdb.sql


-u = your cpanel username
-p = your cpanel password
stocksdb = the database you wish to backup (if backing up only a single database)
backup.sql = the filename of the backup and it's location, in this case it would dump the backup into your root directory (outside public_html)

You can set the backup intervals in the Cron Task Manager, I usually have it backup every night at Midnight.

On to the Restoration process.

My Forum is rather large and runs vBulletin as the Forum Software. PHPMyAdmin is a great tool to use for importing small databases, but in my case, it times out the server. So I use Shell, you may have to ask your hosting provider for access to this feature, but trust me when I say that it's well worth it!

Once you've logged into your Shell Command Line, simply input this code to restore all the databases from your backup.sql file.

CODE
mysql -u root -psecret < backup.sql


OR if you wish to restore only a single database:

CODE
mysql -u root -psecret -D stocks2 < stocksdb.sql


-D = the database you wish to restore the backup into (you must create this yourself, I use PHPMyAdmin.)

Well, I hope this makes all the worrysome administrators out there sleep a little easier, knowing that if they should lose their data... they've got backups! biggrin.gif

 

 

 


Reply

MarCrush
What about using a GUI to backup MySQL databases, using the GUI is easier, found them at http://www.mysql.com/

Anyways, great info for people who are lazy or takes forever (like 56k people) to download the GUI.

Reply

s2city
I prefer the Cron over GUI, but yes you can use that as well.

Reply

areawe
Thanks for this great information. I have always wondered how to do this. It will definitely come in handy for the web site I'm currently working on. Hmmm now I just need to decide which one is better to attempt, GUI or Cron Jobs. How difficult is this Cron Jobs to work with? It sounds like the GUI would be easier but also slower to use? I've heard of Cron Jobs, but don't really know what it is or how to use it. I'm very new to working with mySQL databases. Any info is much appreciated!

Reply

snlildude87
QUOTE(areawe @ Jul 10 2005, 12:30 AM)
I've heard of Cron Jobs, but don't really know what it is or how to use it. I'm very new to working with mySQL databases. Any info is much appreciated!
*

I didn't know what they were either, but OpaQue (admin here) explained it pretty nicely: http://www.trap17.com/forums/cron-jobs-cro...anel-t6321.html

Reply

s2city
Yeah, Crons are really just a simple way of automating a shell command. That's pretty much it... it's like having someone else type in the command for you, so you don't have to remember!

Reply

bureX
Try phpMyBackupPro! ( http://www.phpmybackuppro.net/ )

It allows you to make backups at any time, at any place, with a very easy-to-use GUI! You can also use it to make cron jobs in no time!

Reply

beeseven
Do you have shell access here? Or are you hosted somewhere else?

I want shell access ;_;

Reply

bureX
Yes, I think Trap17 supports exec()-like PHP functions (executing shell commands)!

(Yes, I'm hosted here at Trap17)

Reply

areawe
QUOTE(s2city @ Jul 12 2005, 01:12 AM)
Yeah, Crons are really just a simple way of automating a shell command. That's pretty much it... it's like having someone else type in the command for you, so you don't have to remember!
*



Ha! Thanks for the great explanation! You mean I wouldn't even have to touch finger to key and this Cron Jobs does it all for me? Sounds absolutely wonderful! LOL Where do I sign up? wink.gif

Reply



Got an Opinion! Express your Views! (no registration):-
Add your Reply/ Opinion/ Views/ Comments/ Suggestion/ Questions/ Queries etc.
Posts with decent grammar & English will be accepted and please refrain from profanities.
For asking a Question, We recommend you to sign-up (for free) so that you can track the topic easily.

Nature of your Post*: Opinion/ Reply/ Comments
Question/Query
Feedback to us.
       
Name   Email
Title/Question*

(Maximum characters: 10,000)
You have characters left.

Pages: 1, 2
Recent Queries:-
  1. backup - 993.09 hr back. (1)
Similar Topics

Keywords : backing restoring mysql databases

  1. Complete Login System - With PHP + MYSQL (57)
    Its an complete login sistem made and tested by me and I think itwill be very usefull for people who
    are tryn to learn PHP. First, let's make register.php: CODE <?
    include("conn.php"); // create a file with all the database connections
    if($do_register){ // if the submit button were clicked if((!$name)
    || (!$email) || (!$age) || (!$login) ||
    (!$password) || (!$password2)){ print "You can't let
    any fields in blank...
  2. Check Referrer To Prevent Linking Yours From Other Sites - Check referrer with Php and Mysql (8)
    Check Referrer Using Php To Prevent People Linking To Your Downloads From Other Sites Ever
    find that found some people are listing items, images and tuts and linking directly to the download
    url (those that are like my photoshop tutorial.php?id=0), which is a .php to count the number of
    downloads. To prevent this, you can add a piece of code to the download pages that checks which page
    referred them to the download page: if it's my domain, it downloads the file normally, if
    it's not, it will redirect to my home page instead. Important : Not all browser...
  3. Getting Started With Mysql - creating tables and insert data into them. (2)
  4. Simple User System - php, mysql driven (19)
    Hey! Maybe you've seen my other tutorials...or my signature.. Anyways I'm going to show
    you how to make a system so users of your site could register accounts and you could have protected
    - user only - pages on your site /smile.gif" style="vertical-align:middle" emoid=":)" border="0"
    alt="smile.gif" /> Ok, so we start by creating a config.php file. CODE <?php
        $dbhost   = 'database host';     $dbname   = 'database name';
        $dbusername   = 'database username';     $dbuserpass = 'database pas...
  5. Complete Login And Registration System - doesn't use mysql! (9)
    kLogin 0.1 QUOTE(readme.txt) Readme file to kLogin 0.1 To use the internet explorer fix:
    download the latest IE7 ZIP file
    (http://sourceforge.net/project/showfiles.php?group_id=109983&package_id=119707) Extract the ie7
    zip file to the root directory of your web server. Example, if you are using a unix/linux server,
    it's on "public_html/" or "home/public_html" Open kLogin.php file with your editor and edit the
    $info_text or $info_txt variable. Then, extract the kLogin.php file in to the root
    directory of your web server also. Just run kShoutBo...
  6. [phpbb] Member Last-visit Report Script - for phpbb2 databases (0)
    One flaw in the phpbb2 administration section is a report to list out the 'last-visit'
    time/date of the membership. I wrote a script to do exactly that and will be sharing the script with
    you here. the first section defines the variables required for the Database connection, finding the
    right database, supplying passwords, etc. HTML DEFINE ('DB_USER', 'YOUR DB
    USER NAME'); // change these defined values to suit your own situation DEFINE
    ('DB_PASSWORD', 'YOUR DB PASSWORD'); DEFINE ('DB_HOST', '...
  7. Adding Data To A Database And Displaying It Later - Using Forms, PHP and MySQL (1)
    Requirements: PHP Support MySQL Database access I am going to use a news program as an example.
    Ok, first you are going to need to connect to the database. Do so by using the code below. I have
    added some comments where you will need to edit to fit your server's specifications. Create a
    new file with notepad and call it config.php QUOTE //Change root to your database
    account's username $dbusername = "root"; //Add your account's password in between the
    quotations $password = " "; //Add the name of the database you are using in betw...
  8. Simple Shoutbox - PHP, MySQL driven.. (34)
    Ok, so I'm going to show you how to create a very basic shoutbox which is driven with PHP and a
    MySQL database. So, lets start - open a database management program like PHPMyAdmin and run these
    queries. SQL CREATE TABLE `shoutbox` ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT
    , `name` VARCHAR( 255 ) NOT NULL , `mail` VARCHAR( 255 ) NOT NULL , `time`
    VARCHAR( 255 ) NOT NULL , `message` TEXT NOT NULL , `ip` VARCHAR( 255 ) NOT NULL ,
    PRIMARY KEY ( `id` ) ) TYPE = MYISAM ; CREATE TABLE `shoutbox_a...
  9. Cpanel Mysql Database Management - Part 2.1 Of My 7 Part Cpanel Tutorial (6)
    This tutorial is an extension to my 7 tutorial series about the Cpanel. The 7 different Cpanel
    tutorials can be found below. Part 1: E-mail Management Part 2: Useful Site Management Tools
    Part 3: Useful Site Management Tools2.1 Part 4: Analysis/Log Files Part 5: Advanced Tools Part
    6: PreInstalled Scripts, Extras, and Cpanel Options Part 7: Fantastico Detailed Cpanel
    Tutorial Part 2.1: MySQL Management In this tutorial, i will explain how to add, edit, delete
    and over-all manage the MySQL Feature in the Cpanel. Creating a MySQL Database 1) ...
  10. Starting Or Stopping Apache And Mysql Server Via Batch File - (0)
    Hi guys, this is a litte tutorial about how we start and stop the Apache and MySQL in Windows NT
    (2000, XP, 2003) via a batch file script. As we know in Windows NT based system Apache and MySQL
    installed as Windows Services. So we can stop and start it using NET command. For more information
    about DOS command, type HELP at command prompt. I assuming that your MySQL service name is "mysql"
    and your Apache (Apache 2.0.x) service name is "apache2". If you want to chek it click Start > Run >
    services.msc > OK. Windows IS NOT Case Sensitive. Let's get started!. 1. ...
  11. Quiz With Php, But Without Mysql - (3)
    Ok let`s start! Once I wrote it for school: At first we need questions (php) CODE
    $form_block = " <p>Quiz</p> <form method=\"POST\"
    action=\"$_SERVER[PHP_SELF]\">
    <p><strong>What's The Capital City Of England?</strong><br>
    <input type=\"text\" name=\"q1\"
    value=\"$q1\" size=30></p> <p><strong>How Many
    Letters Are There In The Alphabet?</strong>&...
  12. Installing Php + Mysql + Apache + Phpmyadmin On Windows Part 2 - Continue the last section which is installing phpMyadmin (0)
    QUOTE phpMyAdmin lets you control you MySQL database from a web browser. Steps: 1. If you
    haven't done so already, download the phpMyAdmin Database Manager - You can download the
    software from the phpMyAdmin website. Be sure to download the phpMyAdmin-2.6.2-pl1.zip file. Save
    the file on your Windows Desktop. ... ... ... Go to for more info. Post Copied. Member
    Banned ...
  13. Php Dynamic Signatures - Using the GD Module and MySQL (9)
    PHP Dynamic Signatures using the GD Module After much scowering on the internet to find a
    suitable tutorial on this subject, I came up empty-handed. So I was forced to learn it on my own
    through trial & error. And since I had discovered a lack of tutorials on this subject, I dediced to
    write one! Working Example: Abstract : Using the GD Module of PHP allows a
    developer to build custom Images with Dynamic Content. Such content could be the Requesting Users
    IP Address, Web-Browser Type, Operating System, even the number of times the user has see...
  14. Searching With Php And Mysql - The easy way :P (2)
    Searching with PHP and MySQL is pretty easy when you think about it, especially if you're doing
    it the simple way (without boolean or whatever) /tongue.gif' border='0'
    style='vertical-align:middle' alt='tongue.gif' /> It consists of a few forms, a query and an
    output. As I said, simple! CODE <form name=\"form1\"
    id=\"form1\" method=\"post\" action=\"<?
    $php_self ?>\"> <table width=\"100%\"
    border=\"0\" cellspacing=\&#...
  15. Install Php 5 To Work With Mysql - (0)
    hi, all php 5 is new to all people a long time( actually, I forget how long...). it introduced a
    more object-oriented design to developer. like, access type(public, private, protected), abstract
    type(classes that can't create objects), interface support, and more . althrought it is
    widerly available, someone still can't get it running with mysql. every time the php is starting
    up. a message prompted stated that some xtensions can not be found, and therefore, not loaded. so, I
    have written this to help other and newbie get it start quickly. note: thi...
  16. A Nice Mysql Server Check - (4)
    I made this and its not very hard at all just fill in the info and it willl see if your mysql is up
    or down CODE <html> <head> <title> Mysql Connection Test
    </title> </head> <body> <h2> <?php // On this you need to put
    your host most of the times localhost username and password. $conncect = mysql_connect (
    "host", "Username", "password" ) or die (" Sorry your server
    can't connect to your mysql server <BR> Check to see you have put in the Username and...
  17. Mysql Database Setup - tutorial walk through (1)
    Post copied. Google cache to source Credits have been adjusted. Setting Up Your MySQL &
    phpMyAdmin For The First Time This tutorial will show you how to set up your MySQL and your
    phpMyAdmin with ease, regardless of whether you are going to be using PHP Nuke or PHP Nuke
    Platinum. The process is still the same. -Log into your CPanel with your User Name and Password.
    -Click on MySQL icon -Now, we need to create a database also known as Db , Your User Name will
    make up part of the necessary details, but you don't need to do this as it will be done au...
  18. How To Host Ur Own Site In 2 Mins Php+mysql Needed - (34)
    QUOTE Run you're own server for testing phpmysql or just to host you're own website or
    for you're friends. -needS: a PC that's all 8) - How to ? download : CODE
    http://server.paehl.de/apache20.zip : 30 seconds Installing:---> 1 minute
    *********************************** Unpack the exe where ever you want. after unpack run
    serverinst.exe and change Servername and your e-mail. Start the following files one time:
    start_apache.cmd --> start apache as service mysql_start_as_service.cmd  --> dito for mysql
    mysql_first_st...
  19. Php/mysql Login/register - Tutorial for login with databases. (2)
    Start register code. Register.php CODE <form method=post
    action=register.php?action=register  name=s> <table>
    <tr><td>Username:</td><td><input type=text
    name=user></td></tr>
    <tr><td>Email:</td><td><input type=text
    name=email></td></tr>
    <tr><td>Pass:</td><td><input type=password
    name=pass></td></tr> <tr><td>Verify
    Pass:</td><td><input ...
  20. Shoutbox, Made Easy - PHP+MySQL ShoutBox! Very simple... (17)
    Just create a PHP file named "kShoutBox.php" CODE <?php header("Content-type:
    text/html; charset=utf-8"); //Send to browser that the charset is utf-8 ?> <?php
    /* ******************************   kShoutBox 0.1 ****************************** */
    /* **************************************** This ShoutBox script was created by Juan Karlo Aquino
    de Guzman DO NOT MODIFY THIS CODE AND DISTRIBUTING IT WITHOUT ANY PERMISSION. E-MAIL THE AUTHOR
    FIRST. Email: 01karlo@gmail.com DO NOT REMOVE THE "POWERED BY". FOR SUGG...
  21. Clicks Counter - With PHP + MySQL (0)
    Well, in this tut, I'll show you how to do an simple link counter, with only one count for each
    IP and the date when the link were clicked. Just remember, this is only one way to do it. First,
    create an mysql database called 'links', with 2 table: first table will be called
    'links', with the columns: id, title, ref and clicks. The other one will be 3 columns and
    will be called links_info: id, ip and date. Just remember that the columns 'id' of this
    second table IS NOT auto-increment. Here is the code of the file which will count the clicks, ...
  22. A Guide On Webhosting, Php, Mysql, And Phpmyadmin - (0)
    I thought I'd submit this little guide of mines, after I gave my friend an impromptu
    introduction on this. I hope those who are new to PHP and MySQL will fin this usefull. What is
    PHP? PHP is a scripting program, you install PHP into your server, BUT, you can only do it if you
    have "physical" access to the server, (i.e. - you can close the server, add stuff to the server,
    clear the server, remove the server). People who have web hosting accounts CAN NOT install PHP,
    because, they only "own" a part of the server. You must be the server administrator in order to ins...



Looking for backing, restoring, mysql, databases

*RANDOM STUFF*





*SIMILAR VIDEOS*
Searching Video's for backing, restoring, mysql, databases

*MORE FROM TRAP17.COM*
advertisement



Backing Up And Restoring Mysql Databases



 

 

 

 

ADD REPLY / Got an Opinion! a humble request :-) RAPID SEARCH! Free Hosting [X]
Express your Opinions, Thoughts or Contribute your information that might help someone here.
Ask your Doubts & Queries to get answers.. "Together, We enlight each other!"
Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.
500MB Space *No Ads*, CPanel, FTP, PHP, MySQL, EMails - 100% FREE