new to making database backup

[iffoiffy]

Hi,

I have my website on a hosting compnay server. They are using phpmyadmin for MYSQL. I need to make a back of my database. I was told to use the option export, but I really don't understand it. When I click on "Export" many options

I leave the default setting enter the file name and click "go" and endup saving a file with sql extension which look like this

*************
# Host: mysql4.easycgi.com
# Generation Time: Apr 01, 2005 at 08:28 PM
# Server version: 4.0.22
# PHP Version: 4.2.2
# Database : `subzimundi`
# --------------------------------------------------------

#
# Table structure for table `movies`
#
# Creation: Dec 27, 2004 at 02:37 PM
# Last update: Dec 27, 2004 at 02:39 PM
#

CREATE TABLE `movies` (
`movie_id` varchar(100) NOT NULL default '',
`movie_category` varchar(100) NOT NULL default '',
`movie_name` varchar(100) NOT NULL default '',
`movie_image` varchar(100) NOT NULL default '',
`status` varchar(100) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
PRIMARY KEY (`movie_id`)
) TYPE=MyISAM;

#
# Dumping data for table `movies`
#

INSERT INTO `movies` VALUES ('mv1', 'PG13', 'Hunter ', 'movie1.jpg', 'IN', 'English Movies');

************

Is this the right way? Is this how we make a back, table by table?

Thanks

 

[captlid]

yep thats the right way when you want to save both the table and the data in it. Save the .sql file in a safe place. When you need to transfer the database to another computer.
Go into mysql command window or phpmyadmin, create a database, then load that file in. It will generate all the tables and the data in them.
(make sure when you load up your php or asp scripts that the connection parameters are correct for the new location.)

 

[iffoiffy]

thanks, so I guess you can not make a back up of whole database in one shot, you have to go table by table.

So I guess suppose I wipe out my whole database. first I will create a database manually, then run the files for each table to put tables back, right?

thanks

 

[nego78]

So I guess suppose I wipe out my whole database. first I will create a database manually, then run the files for each table to put tables back, right?

No

With the export you receive a set of commnads to recreate tables and data from your db.

You do not have to run multiple files. You can export whole database, every table and every record.

if you have a shell you can dump whole database with one query

mysqldump -u nameofuser -p nameofdb > file.sql

and after to recreate it you can do

mysql -u nameofuser -p -D nameofdb < file.sql



gry online

 

[captlid]

Another note; You can put the commands in the sql file to create the database, the only bad thing about it is that most hosting providers have a naming convention for databases. So like if your database at home on your development machine is named customers, then one the hosting providers website it will automatically be named yourdomain_customers. So its better to just use the create database feature in phpmyadmin or mysql command line. AND then run the sql file which will create ALL the tables, columns, and in your case import all the data in to fill those tables,columns,rows.

 

[iffoiffy]

thanks a lot for your replies

 

[iffoiffy]

just one question if the sql file is small then you can if you want open in note pad and actually see, but if it really big suppose 1 million records, the is there any way if you want you can open it?