Moving MySQL Database into another server

Hello friends,

I'm moving one of my websites to another server.
It uses a mysql database.

To back and move the MySQL database, first did a back with command:

mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

Code (markup):

Then, after transfering the mysql dump into the new server, i try to run the command:

mysql -u dbusername -p databasename < backupname.sql

Code (markup):

But then it always gives me errors, telling me i don't have access or i haven't access to X table.

Do i need to create the same table named X, as i had in the old server?
Please help.
Thanks

 

if i try to put database in new server with above command, i get:

and if i try to create first in cpanel the database "sms", i get this error:

any tip?

 

I would suggest creating a new database in cpanel and than trying to import into that. If your database isnt to big you can do it right from phpmyadmin. rember when importing your user name and pass for the database is boing to be the user name and pass you created for it in cpanel and that its going to start with your account name and an underscore. hope that helps.

 

your user name should be somthing like smsport_username not just smsport.

that is if i rember correctly.. but im pretty sure thats right.

 

the database it's small, 12MB i guess...
when trying to import directly in MySQLAdmin i receive this error:

 

then, if i delete that database and then try to import again the sql file, i get this error:

this is getting me nervous!

 

open the sql file in notepad delete the line at the top that says

"CREATE DATABASE `smsport_sms` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;"

You have to create the database manualy.

User name should be in the same format your databse name is. should be somthing like "smsport_smsport"

 

thanks, that did the trick....

 

Woot... glad you got it...

 

phpMyAdmin makes life easier when doing this by the way...

 

it does, but it's still good to know the basic commands

 

Firstly, I apologise for revitalising such an old topic. Believe it or not, I was directed here by my server company, who didn't know how to import a database through phpMyAdmin. Anyway, I am having the exact same problem as mentioned and have removed the line which Rexx stated, which in my case was:

CREATE DATABASE `gragle_directory` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

So now that the top of the database file looks as such:

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: mysql2.freehostia.com
-- Generation Time: Dec 17, 2007 at 01:53 AM
-- Server version: 4.1.11
-- PHP Version: 4.4.4-8+etch1
-- 
-- Database: `gragle_directory`
-- 

USE gragle_directory;

-- --------------------------------------------------------

-- 
-- Table structure for table `PLD_BANLIST`
-- 

Code (markup):

The 'table structure...'line is obviously the first line of actual database (php link directory) table code.

I had problems with phpLD so am reinstalling it with (hopefully!) all my old links intact. I created the database with the exact same username/password etc.

However, when I click Go for phpMyAdmin to import the file, I am given this error.

 SQL query:

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: mysql2.freehostia.com
-- Generation Time: Dec 17, 2007 at 01:53 AM
-- Server version: 4.1.11
-- PHP Version: 4.4.4-8+etch1
-- 
-- Database: `gragle_directory`
-- 
USE gragle_directory

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-- phpMyAdmin SQL Dump
-- version 2.6.4-pl2
-- http://www.phpmyadmin.net
-' at line 1 

Code (markup):

Can anyone offer any advice on this old topic on how to solve this error?

Many thanks!