mysql SQL Issues

[Joseph Stacklin]

Hello, everyone. I have a database dump from one server that I am moving to another, however, stupid me forgot that when setting up an account, cpanel (and even Sentora) uses the username as the bit before the actual DB name (ie: my username for the old server was "ohiojekq" so one of my databases would be called "ohiojekq_aban275").

 

How do I go about renaming the DB so I don't loose all my information? Should I just create a sub-account in WHM with the user "ohiojekq" to save time? I am so confused and just want to get my sites up and running again.

 

Somebody please help.

[vorticalbox]

You should be able to grab the database tables/cols and the data from the .sql and then import that.

[IAmAndre]

I'm not sure what your issue is, but if you dumped the database as a .sql file, usually the database name isn't even mentioned in the file, so just create a database and import the backup.

[Joseph Stacklin]

Just now, vorticalbox said:

You should be able to grab the database tables/cols and the data from the .sql and then import that.

How do I do that? The new database has the header/username "ohioiproj" instead of the "ohiojekq"

[Rohime]

26 minutes ago, wolfboytech said:

Hello, everyone. I have a database dump from one server that I am moving to another, however, stupid me forgot that when setting up an account, cpanel (and even Sentora) uses the username as the bit before the actual DB name (ie: my username for the old server was "ohiojekq" so one of my databases would be called "ohiojekq_aban275").

 

How do I go about renaming the DB so I don't loose all my information? Should I just create a sub-account in WHM with the user "ohiojekq" to save time? I am so confused and just want to get my sites up and running again.

 

Somebody please help.

with the cPanel i use just ....

1 - create the new DB (new name prefix).

2 - import the .SQL into the new named DB

3 - update configuration (scripts, etc) to point to the new name.

.

DB name is usually a name variable defined in only one configuration script.

 

... and you will need to update usernames and things anyway ... usually DB name is in the same place.

[Joseph Stacklin]

Just now, IAmAndre said:

I'm not sure what your issue is, but if you dumped the database as a .sql file, usually the database name isn't even mentioned in the file, so just create a database and import the backup.

 

 

1 minute ago, Rohime said:

with the cPanel i use just ....

1 - create the new DB (new name prefix).

2 - import the .SQL into the new named DB

3 - update configuration (scripts, etc) to point to the new name.

.

DB name is usually a name variable defined in only one configuration script.

 

Thank you, however, When I create the new DB and attempt to import the data into phpMyAdmin, the system says that there was a critical error and that no such database exists?

[Joseph Stacklin]

Further, I dumped the localhost file, which showed all the databases and their information (localhost-->export). Should I go through each DB instead and dump them separately?

[Rohime]

2 minutes ago, wolfboytech said:

Further, I dumped the localhost file, which showed all the databases and their information (localhost-->export). Should I go through each DB instead and dump them separately?

What is the name of the file you are trying to restore from ?  its a <backup file name>.SQL  or something other than .SQL ?

[Joseph Stacklin]

1 minute ago, Rohime said:

What is the name of the file you are trying to restore from ?  its a <backup file name>.SQL  or something other than .SQL ?

Nope it's localhost.sql It contains, as far as I can tell, all of my DB's, and their tables.

[Rohime]

1 minute ago, wolfboytech said:

Nope it's localhost.sql It contains, as far as I can tell, all of my DB's, and their tables.

so if you open it with a text editor (like notepad++) it is an SQL Dump file like this:

Or is it something different?

 

 

-- MySQL dump 10.11
--
-- Host: localhost    Database: sc2gump
-- ------------------------------------------------------
-- Server version    5.0.90

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `aaggregate_temp_1394647800`
--

DROP TABLE IF EXISTS `aaggregate_temp_1394647800`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `aaggregate_temp_1394647800` (
  `attachmentid` int(10) unsigned NOT NULL default '0',
  `views` int(10) unsigned NOT NULL default '0',
  KEY `attachmentid` (`attachmentid`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `aaggregate_temp_1394647800`
--

LOCK TABLES `aaggregate_temp_1394647800` WRITE;
/*!40000 ALTER TABLE `aaggregate_temp_1394647800` DISABLE KEYS */;
/*!40000 ALTER TABLE `aaggregate_temp_1394647800` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `aaggregate_temp_1397373000`
--
 

[Joseph Stacklin]

1 minute ago, Rohime said:

snip

 

Yes. Below is the first 48 lines from the dump:

 

-- phpMyAdmin SQL Dump
-- version 4.0.10.14
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Apr 15, 2017 at 06:17 AM
-- Server version: 10.1.21-MariaDB-cll-lve
-- PHP Version: 5.6.20

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `ohiojekq_aban275`
--

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

--
-- Table structure for table `abfp_addresses`
--

CREATE TABLE IF NOT EXISTS `abfp_addresses` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `company` varchar(32) NOT NULL,
  `firstname` varchar(32) NOT NULL DEFAULT '',
  `lastname` varchar(32) NOT NULL DEFAULT '',
  `address_1` varchar(128) NOT NULL,
  `address_2` varchar(128) NOT NULL,
  `postcode` varchar(10) NOT NULL DEFAULT '',
  `city` varchar(128) NOT NULL,
  `country_id` int(11) NOT NULL DEFAULT '0',
  `zone_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`address_id`),
  KEY `ac_addresses_idx` (`customer_id`,`country_id`,`zone_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;

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

--
-- Table structure for table `abfp_addresses_enc`

 

 

The dump includes the above lines, which is from my softaculous installation of Abante cart, as well as all of my WP DB's.

[Rohime]

3 minutes ago, wolfboytech said:

 

--
-- Database: `ohiojekq_aban275`
--

--

 

Not sure what having the full name of DB In quotes will do ... 

Personally, I'd take a copy of .SQL , and directly edit the

 

"-- Database: 'ohiojekq_dbname' "

 

statements names to be the new DB naming standard (ie; edit to update the prefix.)

 

"-- Database: 'newname_dbname' "

 

Hopefully there are not many dis create DB's in the file. (you should not change tables or any other references, just the Database:  statement(s).

[Joseph Stacklin]

2 minutes ago, Rohime said:

 

Not sure what having the full name of DB In quotes will do ... 

Personally, I'd take a copy of .SQL , and directly edit the

 

"-- Database: 'ohiojekq_dbname' "

 

statements names to be the new DB naming standard (ie; edit to update the prefix.)

 

"-- Database: 'newname_dbname' "

 

Hopefully there are not many dis create DB's in the file. (you should not change tables or any other references, just the Database:  statement(s).

Thank you, I will attempt to do this. And should everything just be as easy (after I dl and re-upload the files to the new server) as pointing everything to the right direction and updating the config files?

[Rohime]

1 minute ago, wolfboytech said:

Thank you, I will attempt to do this. And should everything just be as easy (after I dl and re-upload the files to the new server) as pointing everything to the right direction and updating the config files?

Should be.

"Should" is such a great word ... so flexible ... so ambiguous.

 

I do believe it was Einstein who said:

"In theory, theory and practice are the same,

In practice, they are different".

[Joseph Stacklin]

10 minutes ago, Rohime said:

Should be.

"Should" is such a great word ... so flexible ... so ambiguous.

Well.. It didn't break.. But.. Got an error...

 

Error

SQL query:

--
-- Database: `ohioipro_aban275`
--
-- --------------------------------------------------------
--
-- Table structure for table `abfp_addresses`
--
CREATE TABLE IF NOT EXISTS `abfp_addresses` (

`address_id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`customer_id` INT( 11 ) NOT NULL ,
`company` VARCHAR( 32 ) NOT NULL ,
`firstname` VARCHAR( 32 ) NOT NULL DEFAULT '',
`lastname` VARCHAR( 32 ) NOT NULL DEFAULT '',
`address_1` VARCHAR( 128 ) NOT NULL ,
`address_2` VARCHAR( 128 ) NOT NULL ,
`postcode` VARCHAR( 10 ) NOT NULL DEFAULT '',
`city` VARCHAR( 128 ) NOT NULL ,
`country_id` INT( 11 ) NOT NULL DEFAULT '0',
`zone_id` INT( 11 ) NOT NULL DEFAULT '0',
PRIMARY KEY ( `address_id` ) ,
KEY `ac_addresses_idx` ( `customer_id` , `country_id` , `zone_id` )

) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =13;
 

 

MySQL said: 

#1046 - No database selected 

[Rohime]

7 minutes ago, wolfboytech said:

Well.. It didn't break.. But.. Got an error...

 

 

MySQL said: 

#1046 - No database selected 

hmm.... did you create the DB first?

 

down vote

If you are doing this through phpMyAdmin:

I'm assuming you already Created a new MySQL Database on Live Site (by live site I mean the company your hosting with (in my case Bluehost)).

Go to phpMyAdmin on live site - log in to the database you just created.

Now IMPORTANT! Before clicking the "import" option on the top bar, select your database on the left side of the page (grey bar, on the top has PHP Myadmin written, below it two options:information_schema and name of database you just logged into.

once you click the database you just created/logged into it will show you that database and then click the import option.

 

[Joseph Stacklin]

Just now, Rohime said:

hmm.... did you create the DB first?

Yes, I just broke down and dumped each DB separately, and it is now fixed, so now I just have to wait for the 15GB to download, then upload, and then I will be (hopefully) merged with not an issue. Again, it all should work. I don't know tho. Thank you again for your help. You wouldn't happen to know an easy way to get all your cpanel settings carried over to the new server, would you?

 

 

[Rohime]

1 minute ago, wolfboytech said:

You wouldn't happen to know an easy way to get all your cpanel settings carried over to the new server, would you?

Nope, sorry.

[Joseph Stacklin]

Just now, Rohime said:

Nope, sorry.

Well, thank you anyway for what you did help me with. May your computer run cool, and may you have perfect eyesight. 

[Rohime]

1 minute ago, wolfboytech said:

Well, thank you anyway for what you did help me with. May your computer run cool, and may you have perfect eyesight. 

That sounds suspiciously like a curse.

 

The classics have always been my fav's. (curses both)

- "May you live in interesting times"

- "May your exploits come to the attention of powerful people"

[Joseph Stacklin]

Just now, Rohime said:

That sounds suspiciously like a curse.

 

The classics have always been my fav's. (curses both)

- "May you live in interesting times"

- "May your exploits come to the attention of powerful people"

Nah, I'm not that cruel. Sorry if I offended you.

[Rohime]

12 hours ago, wolfboytech said:

Nah, I'm not that cruel. Sorry if I offended you.

"Its a joke son - i'm pitching you ain't catching boy".

- Foghorn Leghorn

 

Anyway ... did you get it all working ?

 

[Joseph Stacklin]

7 hours ago, Rohime said:

"Its a joke son - i'm pitching you ain't catching boy".

- Foghorn Leghorn

 

Anyway ... did you get it all working ?

 

Very funny.  It's all working (for now) however now I need to import my 300 sub domains, and 30 websites. 

 

joy.