Need help moving MySQL db from one server to another

I'm trying to move a db from a shared hosting account to a dedicated server. I exported the DB; but I can't get it to import.

I am using SSL to run bash. This is the first time I have ever done this. If anyone can tell me what the command line is to import the db I'd appreciate it.

thx,

 

did you export is as a .sql file? if so then you should be able to just enter something like

mysql < my_db.sql

 

to import the database in bash you can do this.

mysql -u USERNAME -pPASSWORD dbname < dumpfile.sql

where USERNAME is your mysql user, PASSWORD is the mysql password (there should be no space between -p and PASSWORD) dbname is the name of the database you wish to import the new data into (create it first) and dumpfile.sql is the exported sql from the old database.

 

mysql -u user_name -p database_name < sqlfile_name

it will ask for the password. enter the password, you are done.

1. Notes you will have to be in same directory as sqlfile or will have to give absolute/relative path instead of just name.
2. if it says mysql command not understood, give full path to mysql on your system.

HTH

BTW, you can use phpMyAdmin also, to import and export.

 

I tried this and I keep getting this error:

"Can't create database 'acme1001'; database exists"

This isn't the name of the new database I created. It is the name of the old one on the old shared host account. Any thoughts?

 

a really good way of doing this would be through cpanel...

1. go to cpanel of your existing server
2. click on back up
3. click on back up individual databases
4. back up to your local hard disk
5. go to cpanel of new server
6. click on back up
7. restore database from you hard disk

easy enough?

 

I tried and it didn't work for me.

Is the user name for the new db or the db dump file?

 

is there a way to launch cpanel from SSL?

 

Where to insert this command ? Sorry I dont know about sql

 

no its not

 

actually the easiest way is to just use phpmyadmin!

 

ok try without specifiying a database

in other words

mysql -u USERNAME -pPASSWORD < dumpfile.sql

 

have you tried uploading a 5Gb file to phpmyadmin before?

phpmyadmin is okay for little databases but its not too good if you have a decent sized one.

 

I have tried this over and over. I created a DB. I created an account. This is what I get each time.


New DB name = database
DB user = dbuser
password = password
DB to import = olddb.sql

What I enter in through SSL
]# mysql -u dbuser -p database < olddb.sql
enter password: password
ERROR 1007 (HY000) at line 12: Can't create database 'database'; database exists

Any thoughts? It looks like something in the import command wants to create the database, which I already did.

 

Ok, from the top, open up your backup file, near the top, theres a section that says create database{} delete that whole section, untill the first command says create table.

Login to ssh on your server, and type :

mysql -u username -p

[enter password, then press enter]

then do :

create database the_database;

then do :

use the_database;

then do :

source /home/me/www/backup.sql;

where /home/me/www/backup.sql is the location of your edited backup, your database is now imported, type exit......

The reason I choose to login to the server and have it create the database is not all server admin setup thier mysql server to use the same charset, and that can cause problems when using data structues from alien machines.....