How do I import a MySql Database?

[russell37uk]

Hi all, im begging for help again

I am moving one of my sites over from a free host to here as although the uptime is good (for a free host), i want to have a bit more control over the site in general.

I have figured out moving the scripts files via FTP, and thats not my problem, my problem is with the MySql side of things. I have been given some advice by other users on their forum, but they can not really give me any info for this side of the move.

When I export the MySql database are these settings correct?

Export All / Selected Tables


(If you're not sure, select both.)

Structure (selected) Data (selected)

Advanced:

Export with extended inserts (selected)
(If not sure, leave this on . It greatly reduces output size).

Add DROP TABLE IF EXISTS (selected)
(If not sure, leave this on).

Export in:

.sql or compressed .gz which should i select for this option?

So - I know WHERE to upload the MySql details from my old host, but thats as far as I have got.

Here's the tips/advice i got from the other forum

On the other server you most probably have some tools to manage databases, most commonly used is PHPMyAdmin - simply create empty database in there and import the database content from the file that you exported to from old database in here.

Create an empty database - is this a MySql database?

Most likely you will need to re-configure the script you use in some configuration/settings.php file to apply new database login credentials

I assume that this is in the script that i'm moving?

Many thanks for your advice
Russell

[Kieran@whuk]

Hi Russell,

Please refer below instructions to Export and Import database from PHPMyAdmin.


Access PHPMyAdmin >> Then click on Export >> [ keep all the setting to default ] Then click GO.

* Default settings include :

- SQL schema selected.
- Comments selected.
- Add IF NOT EXISTS selected.
- Add AUTO_INCREMENT value selected.
- Enclose table and field names with backquotes selected.
- Complete inserts selected
- Extended inserts selected.
- Use hexadecimal for BLOB selected
- Compression None selected.

As Compression is set to none so plain .SQL file will be available to download. And you don't need to create MySQL database on new server as .SQL file contains create database query which will create and fill database on same server.

Now lets import this file on new server.

Go to PHPMyAdmin >> Then click on Import >> Browse the .SQL file from local machine.
Then click on Go.

Done!

If you are facing any difficulty then do upload .SQL file under your account and contact us,
we will restore it for you.

[jon123]

as for your question :

.sql or compressed .gz which should i select for this option?

if it is a large database then use .gz otherwise no compression will be fine, but both will work either way.

Yes a mysql database. Go into cpanel and create a new database. Once created goto phpmyadmin and follow above advice.

[Dan]

Indeed, uploading .gz files is usually a faster process.

[russell37uk]

Thanks for the info and guide, but im confused now as to wether i should create a MySql database first.

Kieran@whuk

And you don't need to create MySQL database on new server as .SQL file contains create database query which will create and fill database on same server.

jon123

Yes a mysql database. Go into cpanel and create a new database. Once created goto phpmyadmin and follow above advice.

So do I need to create a MySql database first or not?

Sorry If I seem annoying, I just dont want to screw things up

Russell

ALSO AFTER I FIGURED OUT THE DATABASE MOVE
Once I have figured this problem of mine out, can someone tell me how to edit a 'index.php' page?

eg. visitors who would have bookmarked myolddomaim.com/somefolder/index.php
How do i edit that page so that I can tell visitors that i have moved to newdomain.com?

If it helps the index.php in in the coppermine gallery script.

Thanks again.

[Kieran@whuk]

Hi Russell,

With PHPmyadmin default setting, option "Add DROP DATABASE" is not selected so you don't need to create MySQL database on new server as .SQL file contains create database query which will create and fill database on same server. If the database is already created on new server then make sure your .SQL backup is generated with DROP DATABASE option so it will delete old database and fill new one.

The location of index.php page and coppermine gallery script configuration will depends on domain's DOCUMENT ROOT. I would recommend you please copy your script on our server and contact us on live chat so we will configure your application script as per your requirement.

Cheers !

[jon123]

With PHPmyadmin default setting, option "Add DROP DATABASE" is not selected so you don't need to create MySQL database on new server as .SQL file contains create database query which will create and fill database on same server

Really? I cant create a new database from phpmyadmin whether its included in the backup or if i try to create on manually! option is denied.
I have to use cpanel only to create new databases

[russell37uk]

Thanks for all the replies and advice. all done now after a bit of trial and error.

Thanks again
Russell