How do I restore a cpanel sql backup via ssh?

[CrewXp]

Hey. I have a question that I've been working on for a week. Before my old server went down, I backed up my mysql of a site through cpanel's backup feature.

Well it won't upload the backup via phpmyadmin because it's formatted different (' 's)
Example: When done via cpanel backup, it has this:

Truncated for space...

DROP TABLE IF EXISTS airport;
CREATE TABLE airport (
id int(11) NOT NULL auto_increment,

And phpmyadmin backs up like this:

DROP TABLE IF EXISTS `airport`;
CREATE TABLE `airport` (
`id` int(11) NOT NULL auto_increment,

So my question is. Is there a way to upload my cpanel mysql backup via ssh? A certain command? Because it's WAY too big to upload via web. It's already on the server though.

Thanks!!

 

[richy]

via SSH:
mysql -D(databasename) < (sql file)

 

[DigitalN]

If you have root access then

# mysql databasename < somedump.sql

will be sufficient.

If not then

# mysql -uUSERNAME databasename -pPASSWORD < somedump.sql

 

[CrewXp]

that's not the same command the cpanel backup feature works is it...?

because that's what I tried.

I restored a smaller mysql cpanel backup through cpanel, and it worked. (a backup with the weird formatting)
Then I tried the same backup through phpmyadmin and ssh (using that command line)

And it didn't work.

It seems cpanel's backup uses a little different format. (as seen above)

Any help? Thx

 

[CrewXp]

*bump* please!! I can't get my business online without it! Thx

 

[Infopro]

mysql -u crewxp_crewxp -p crewxp_crewxpdatabase < /home/crewxp/DB_backups/crewxpsbackup.sql

Will work fine.

 

[CrewXp]

but it doesn't...

ERROR 1064 (42000) at line 88: 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 'by varchar(40) NOT NULL default '',
type enum('0','1') NOT NULL default '0',
' at line 4

I manually entered a PART of it in, and then exported via phpmyadmin (not cpanel), compared it. And it was the exact same. The difference can be seen above.

 

[CrewXp]

Did more investigation and found out this. It's only some tables in it that's not going in.

DOESNT WORK

DROP TABLE IF EXISTS donaters;
CREATE TABLE donaters (
donater_id int(11) NOT NULL auto_increment,
username varchar(40) NOT NULL default '',
donater_pass varchar(40) NOT NULL default '',
amount int(50) NOT NULL default '0',
package enum('None','1','2','3','4') NOT NULL default 'None',
on varchar(100) NOT NULL default '',
PRIMARY KEY (donater_id)
) TYPE=MyISAM;

DOESNT WORK

DROP TABLE IF EXISTS ban;
CREATE TABLE ban (
id int(11) NOT NULL auto_increment,
username varchar(40) NOT NULL default '',
by varchar(40) NOT NULL default '',
type enum('0','1') NOT NULL default '0',
reason text NOT NULL,
length varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

WORKS

DROP TABLE IF EXISTS auctions;
CREATE TABLE auctions (
id int(11) NOT NULL auto_increment,
username varchar(40) NOT NULL default '',
min_starting int(11) NOT NULL default '0',
current_bid int(11) NOT NULL default '0',
winning varchar(40) NOT NULL default '',
winning_bid int(11) NOT NULL default '0',
item_type varchar(100) NOT NULL default '',
time varchar(100) NOT NULL default '',
item_id varchar(100) NOT NULL default '',
an enum('0','1') NOT NULL default '0',
pvt enum('0','1') NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;

what could be wrong? I think it's a compatibility issue when I upgraded servers, but how do I fix it? The unzipped sql file is 150 megs!

THX

 

[Infopro]

How was the DB backed up?