LinuxQuestions.org - [SOLVED] How to create MySQL cPanel on Phpmyadmin

- Linux - General (https://www.linuxquestions.org/questions/linux-general-1/)

- - How to create MySQL cPanel on Phpmyadmin (https://www.linuxquestions.org/questions/linux-general-1/how-to-create-mysql-cpanel-on-phpmyadmin-4175490377/)

satimis

01-06-2014 10:35 PM

How to create MySQL cPanel on Phpmyadmin

Hi all,

I tried creating new database direct on phymyadmin without success. Google search informed me that it needs creating MySQL cPanel on Phpmyadmin. But I couldn't figure out how to do it. Please help.

TIA

satimis

Habitual

01-07-2014 08:07 AM

Quote:

Originally Posted by satimis (Post 5093279)

"without success" is pretty vague.
You want to create a cpanel database or what exactly?
Are you installing cpanel/Whm?

Code:

create database cpanel;

in phpmyadmin doesn't execute?
Is Phpmyadmin setup correctly/installed?

You have root shell access or is this shared hosting?

We need way more info than " Google search informed me that it needs creating MySQL cPanel on Phpmyadmin"

http://forums.cpanel.net/f5/unable-c...in-183431.html

satimis

01-07-2014 09:27 AM

3 Attachment(s)

Quote:

Originally Posted by Habitual (Post 5093500)

"without success" is pretty vague.
You want to create a cpanel database or what exactly?
Are you installing cpanel/Whm?

Code:

create database cpanel;

Hi,

Thanks for your advice.

I'm administrator. I was following below article cloning live website on Godday to local server;

How to Clone Your Live WordPress Blog to a Local Server
http://www.maketecheasier.com/clone-...-local-server/

I can't find a way creating new database as described on the article.

phpmyadmin, aparche2 and MySQL are running. I can ran MySQL commands on terminal creating new database. The live website has been exported from phpmyadmin on Godaddy server to local server.

Followings are the screenshots of local phpmyadmin.

Screenshot_20140107_01.png

Highlight – information_schema
Screenshot_20140107_info_schema.png

Highlight – test
Screenshot_20140107_test.png

Highligh -wordpress
Screenshot_20140107_wordpress.png (on next posting because only 3 files allowed)

On highting "test" -> Import (download database - "__DB__.sql.zip"
Warning:

Code:

Error



SQL query:



--

-- Database: `information_schema`

--

CREATE DATABASE `information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;



MySQL said: Documentation

#1044 - Access denied for user 'wordpress'@'localhost' to database 'information_schema'

Rgds
satimis

satimis

01-07-2014 09:29 AM

1 Attachment(s)

Highligh -wordpress
Screenshot_20140107_wordpress.png

TenTenths

01-07-2014 09:37 AM

Quote:

Originally Posted by satimis (Post 5093542)

Code:

--

-- Database: `information_schema`

--

CREATE DATABASE `information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

information_schema is an "internal" MySQL database, it has nothing to do with wordpress.

Try backup up and restoring only your wordpress database.

satimis

01-07-2014 10:22 AM

1 Attachment(s)

Quote:

Originally Posted by TenTenths (Post 5093548)

information_schema is an "internal" MySQL database, it has nothing to do with wordpress.

Try backup up and restoring only your wordpress database.

Hi,

On phpmyadmin (Godaddy server)
Export

Following items are checked as default
[check] SQL

Structure
[check] Add AUTO_INCREMENT value
[check] Enclose table and field names with backquotes

Data
[check] Use hexadecimal for BLOB

According to the article:-
How to Clone Your Live WordPress Blog to a Local Server
http://www.maketecheasier.com/clone-...-local-server/
[check] zipped

(see attached file)

Whether I made any mistake? Thanks

satimis

TenTenths

01-07-2014 10:26 AM

Quote:

Originally Posted by satimis (Post 5093577)

Whether I made any mistake? Thanks

Yeah, don't export the information_schema database.

satimis

01-07-2014 10:52 AM

2 Attachment(s)

Quote:

Originally Posted by TenTenths (Post 5093579)

Yeah, don't export the information_schema database.

There are 2 items there:
(see Screenshot_unselect_all.png)

Select only the database - sat1318107320909
-> Export

-> import
error
(see Screenshot_error_sat1318.png)

satimis

TenTenths

01-07-2014 11:02 AM

Quote:

Originally Posted by satimis (Post 5093588)

-> import
error
(see Screenshot_error_sat1318.png)

Read the error message it states EXACTLY what the problem is. Your MySQL user "wordpress" doesn't have permissions to create a database.

Try exporting the data without the "create database" statement, there may be an option for that, I can't be bothered looking up phpmyadmin.
Create the database as "root" or another privileged user on the new server.
Grant the wordpress@localhost privs on the new database.
Try the import.

Or, run the import as "root"

satimis

01-07-2014 11:02 PM

Quote:

Originally Posted by TenTenths (Post 5093596)

Try exporting the data without the "create database" statement, there may be an option for that, I can't be bothered looking up phpmyadmin.

There is ONE option on Export

Code:

Database export options

Add DROP DATABASE

I haven't checked this option.

Quote:

Create the database as "root" or another privileged user on the new server.

Ran following commands creating the database on local server

Code:

$ mysql -u root -p

mysql> CREATE USER wordpress@localhost IDENTIFIED BY "password";

mysql> create database wordpress;

mysql> GRANT ALL ON wordpress.* TO wordpress@localhost;

mysql> FLUSH PRIVILEGES;

mysql> exit

Quote:

Grant the wordpress@localhost privs on the new database.

I don't expect adding user 'wordpress@localhost' to the database of the Live website on Godaddy server. I created a new user on the database of local website with following commands

Code:

$ mysql -u root -p

mysql> USE wordpress;

Database changed



mysql> CREATE USER sat1318107320909 IDENTIFIED BY "password";

mysql> GRANT ALL ON wordpress.* TO sat1318107320909;

mysql> FLUSH PRIVILEGES;

mysql> exit

USER "sat1318107320909" is on Live database

Quote:

Try the import.

Still failed.

Warning:

Code:

Error



SQL query:



--

-- Database: `sat1318107320909`

--

CREATE DATABASE `sat1318107320909` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;



MySQL said: Documentation

#1044 - Access denied for user 'wordpress'@'localhost' to database 'sat1318107320909'

I think I can't do the other way round instead.

Quote:

Or, run the import as "root"

Pls advise how to run it? Thanks

Edit
====
I got it. Login phpmyadmin as root with MySQL root password

Code:

Import has been successfully finished, 389 queries executed. (__DB__.sql.zip)

Will come back later

Rgds
satimis

Habitual

01-08-2014 08:16 AM

Quote:

Originally Posted by satimis (Post 5094008)

There is ONE option on Export

Code:

Database export options

Add DROP DATABASE

I haven't checked this option.

Ran following commands creating the database on local server

Code:

$ mysql -u root -p

mysql> CREATE USER wordpress@localhost IDENTIFIED BY "password";

mysql> create database wordpress;

mysql> GRANT ALL ON wordpress.* TO wordpress@localhost;

mysql> FLUSH PRIVILEGES;

mysql> exit

I don't expect adding user 'wordpress@localhost' to the database of the Live website on Godaddy server. I created a new user on the database of local website with following commands

Code:

$ mysql -u root -p

mysql> USE wordpress;

Database changed



mysql> CREATE USER sat1318107320909 IDENTIFIED BY "password";

mysql> GRANT ALL ON wordpress.* TO sat1318107320909;

mysql> FLUSH PRIVILEGES;

mysql> exit

USER "sat1318107320909" is on Live database

Still failed.

Warning:

Code:

Error



SQL query:



--

-- Database: `sat1318107320909`

--

CREATE DATABASE `sat1318107320909` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;



MySQL said: Documentation

#1044 - Access denied for user 'wordpress'@'localhost' to database 'sat1318107320909'

I think I can't do the other way round instead.

Pls advise how to run it? Thanks
root@MyKungFu:~# mysql

Edit
====
I got it. Login phpmyadmin as root with MySQL root password

Code:

Import has been successfully finished, 389 queries executed. (__DB__.sql.zip)

Will come back later

Rgds
satimis

as root:
unzip the __DB__.sql.zip file, you only need the .sql inside it...

Code:

root@localhost:~ # mysql wordpress < /path/to/file.sql

now test your privileges:

Code:

mysql -usat1318107320909 -ppassword <enter>

show databases;

you should 'see' the wordpress database.

satimis

01-08-2014 09:02 AM

2 Attachment(s)

Quote:

Originally Posted by Habitual (Post 5094270)

as root:
unzip the __DB__.sql.zip file, you only need the .sql inside it...

Code:

root@localhost:~ # mysql wordpress < /path/to/file.sql

now test your privileges:

Code:

mysql -usat1318107320909 -ppassword <enter>

show databases;

you should 'see' the wordpress database.

Performed following steps:-

satimis@mint16ssd00 ~ $ cd Downloads/
satimis@mint16ssd00 ~/Downloads $ ls

Code:

__DB__.sql.zip

satimis@mint16ssd00 ~/Downloads $ unzip __DB__.sql.zip

Code:

Archive:  __DB__.sql.zip

  inflating: __DB__.sql

satimis@mint16ssd00 ~/Downloads $ ls

Code:

__DB__.sql

__DB__.sql.zip

satimis@mint16ssd00 ~/Downloads $ su
Password:
mint16ssd00 Downloads # mysql wordpress < /home/satimis/Downloads/__DB__.sql

Code:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

That article seems not work for me.

Login phpmyadmin as user - wordpress
the import database not showing up

see Screenshot_phpmyadmin_wordpress.png

Login phpmyadmin as root
the import database showing up

see Screenshot_phpmyadmin_root.png

On browser
http://localhost/wordpress/wp-login.php
Error establishing a database connection

satimis

TenTenths

01-08-2014 09:06 AM

I would guess that your actual wordpress data is in the sat1318107320909 database as there is the "create" statement in your .sql file, I'm also guessing that there's a use sat1318107320909 in there that loads all the data into that database instead of the one you specify on the command line.

Give your wordpress@localhost user permissions on the sat1318107320909 database and use that as the database name in your wordpress config.

satimis

01-08-2014 09:30 AM

Quote:

Originally Posted by TenTenths (Post 5094304)

$ mysql -u root -p
mysql> show databases;

Code:

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sat1318107320909  |

| test              |

| wordpress          |

+--------------------+

6 rows in set (0.03 sec)

Whether run following commands to add user wordpress@localhost to sat1318107320909 database ?

Code:

mysql> USE sat1318107320909;

mysql> CREATE USER wordpress@localhost IDENTIFIED BY "password";

mysql> GRANT ALL ON sat1318107320909.* TO wordpress@localhost;

mysql> FLUSH PRIVILEGES;

mysql> exit

?

Thanks

satimis

TenTenths

01-08-2014 09:33 AM

Quote:

Originally Posted by satimis (Post 5094318)

Whether run following commands to add user wordpress@localhost to sat1318107320909 database ?

Code:

mysql> USE sat1318107320909;

mysql> CREATE USER wordpress@localhost IDENTIFIED BY "password";

mysql> GRANT ALL ON sat1318107320909.* TO wordpress@localhost;

mysql> FLUSH PRIVILEGES;

mysql> exit

?

Thanks

satimis

Yeah, looks right, try it and see how it goes.

satimis

01-08-2014 10:04 AM

Quote:

Originally Posted by TenTenths (Post 5094319)

Yeah, looks right, try it and see how it goes.

mysql> USE sat1318107320909;

Code:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> CREATE USER wordpress@localhost IDENTIFIED BY "passwd";

Code:

ERROR 1396 (HY000): Operation CREATE USER failed for 'wordpress'@'localhost'

Unable adding user "wordpress@localhost" ?

Edit:

Sorted out the problem as follow;

mysql> DROP user wordpress@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'wordpress'@'localhost' IDENTIFIED BY "password";
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON sat1318107320909.* TO wordpress@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

http://localhost/wordpress/
Error establishing a database connection

http://localhost/wordpress/wp-login.php
Error establishing a database connection

Still the same

satimis

Habitual

01-08-2014 11:03 AM

no need to create the user, just use

Code:

grant all on sat1318107320909.* to 'wordpress'@'localhost' identified by 'password'; flush privileges; exit;

This creates the user and gives access...

Do not change database! The privileges are held in mysql.user

Code:

use mysql;

if you did, then issue the "grant all" above".

then test using

Code:

mysql -usat1318107320909 -p<password> <enter>

show databases;

Edit: Looks like you fixed it :)

edit wp-config.php and adjust the

Code:

define('DB_NAME', 'sat1318107320909');

/** MySQL database username */

define('DB_USER', 'wordpress');

/** MySQL database password */

define('DB_PASSWORD', '<password>');

You'll need to use mysql to change the siteurl before you can use it at http://localhost/wordpress

satimis

01-09-2014 06:02 AM

Quote:

Originally Posted by Habitual (Post 5094354)

no need to create the user, just use

Code:

grant all on sat1318107320909.* to 'wordpress'@'localhost' identified by 'password'; flush privileges; exit;

This creates the user and gives access...

Do not change database! The privileges are held in mysql.user

Code:

use mysql;

if you did, then issue the "grant all" above".

then test using

Code:

mysql -usat1318107320909 -p<password> <enter>

show databases;

Edit: Looks like you fixed it :)

edit wp-config.php and adjust the

Code:

define('DB_NAME', 'sat1318107320909');

/** MySQL database username */

define('DB_USER', 'wordpress');

/** MySQL database password */

define('DB_PASSWORD', '<password>');

You'll need to use mysql to change the siteurl before you can use it at http://localhost/wordpress

According to:-
How to change the WordPress site URL using MySQL command line
http://www.lanexa.net/2011/08/how-to...-command-line/

$ mysql -u root -p

mysql> USE sat1318107320909;

mysql> SHOW tables;

Code:

+--------------------------------+

| Tables_in_sat1318107320909    |

+--------------------------------+

| wp_commentmeta                |

| wp_comments                    |

| wp_links                      |

| wp_nextend_smartslider_layouts |

| wp_nextend_smartslider_sliders |

| wp_nextend_smartslider_slides  |

| wp_nextend_smartslider_storage |

| wp_options                    |

| wp_postmeta                    |

| wp_posts                      |

| wp_term_relationships          |

| wp_term_taxonomy              |

| wp_terms                      |

| wp_usermeta                    |

| wp_users                      |

+--------------------------------+

15 rows in set (0.00 sec)

mysql> select * from wp_options where option_value = 'http://piano-04.satimis.com/wordpress';

Code:

Empty set (0.02 sec)

Stuck here.

satimis

Habitual

01-09-2014 09:02 AM

Quote:

Originally Posted by satimis (Post 5094833)

Stuck here.satimis

OK, you have tables... good news!

Code:

select * from wp_options where option_id = '1';

should show you something you've already seen:

Code:

http://piano-04.satimis.com/wordpress

You have your Wordpress files in /var/www/html/ ?

You could update it with this:

Code:

use sat1318107320909; update wp_options set option_value = 'http://localhost/' where option_id = 1 ;

at the mysql > prompt.

but if it is under|below /var/www/html/ (say /var/www/html/wordpress) then I'd use:

Code:

sat1318107320909 ; update wp_options set option_value = 'http://localhost/wordpress' where option_id = 1 ;

at the mysql > prompt.

satimis

01-09-2014 09:37 AM

Hi Habitual,

mysql> USE sat1318107320909

Code:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> SELECT * from wp_options where option_id = '1';

Code:

+-----------+-------------+-----------------------------+----------+

| option_id | option_name | option_value                | autoload |

+-----------+-------------+-----------------------------+----------+

|        1 | siteurl    | http://piano-04.satimis.com | yes      |

+-----------+-------------+-----------------------------+----------+

1 row in set (0.01 sec)

mysql> UPDATE wp_options set option_value = 'http://localhost/' where option_id = 1 ;

Code:

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

On browser run;

Code:

/localhost/wordpress/

The complete website is now displayed locally -> Login

Code:

WordPress has been updated! Before we send you on your way, we have to update your database to the newest version.



The update process may take a little while, so please be patient.



[Update WordPress Database]

click [Update WordPress Database]

Code:

Update Complete



Your WordPress database has been successfully updated!



[Continue]

click [Continue]

Login to the website.

Lot of thanks for your assistance !!!

It is rather tricky to copy/clone/mirror the Live WordPress website to local server. There are many suggestions/articles on Internet in this respect. Which of them can work? We have to test them. Fortunately I make this test on VM. If failure I just clone another VM on the original VM and start another test.

I'll write down all steps and post the same here later for the benefit of other readers.

I already start another test on;
Copy your WordPress site to a local server for development and testing
http://handcoded.ca/copy-your-wordpr...-local-server/

It uses "WP Migrate DB"-WordPress Plugin to export the DB. Also I have encountered problem. I'll start another posting.

Rgds
satimis

Habitual

01-09-2014 10:17 AM

So it's working now?

Warning: <opinion>
Any tutorial that "explains" how to do things using a GUI interface (phpMyAdmin included) is a crutch.
</opinion>

As you have seen, it can all be down without phpMyAdmin and possibly in less time or just as much time.

My opinion may seem harsh, but trust me after doing this maybe 3 times, it becomes rather pedestrian to move a site to a new host
and you won't even think twice about doing it again in the future.

1.) Export the .sql
2.) copy the file.sql to the new host.
3.) create wp db +user +access privileges
4.) Import the file.sql
5.) Edit wp-config.php for db, user and password
6.) edit the siteurl via mysql
7.) Enjoy your migrated site.

I have a manual script I use to backup my GoDaddy db and the hardest part was figuring out the "-h<dbhost>" part of it.
Once I had that, I can backup the entire site in less than 1 minute.

Have a Great Day!

Edit: forgot the wp-config edit step.

Enjoy the goodness

satimis

01-10-2014 10:12 AM

Quote:

Originally Posted by Habitual (Post 5094958)

So it's working now?

Yes. The Live website cloned was for testing only, its building NOT completed. Once having mastered the steps I'll clone the running websties on Godaddy server.

Quote:

Warning: <opinion>
Any tutorial that "explains" how to do things using a GUI interface (phpMyAdmin included) is a crutch.
</opinion>

Advice noted. Lot of thanks for your assistance.

Rgds
satimis

Habitual

01-10-2014 10:13 AM

You're welcome.
Glad it worked out.

satimis

01-12-2014 10:40 AM

Hi Habitual,

I tried cloning piano-03.satimis.com on Godaddy to local server but no tables exported. I tried 3 times. I paid special attention to;

Code:

[check] Enclose table and field names with backquotes

Export and Import without complaint.

mysql> show databases;

Code:

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sat1318107284790  |

| test              |

| wordpress          |

+--------------------+

6 rows in set (0.00 sec)

mysql> USE sat1318107284790; SHOW tables;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Code:

+----------------------------+

| Tables_in_sat1318107284790 |

+----------------------------+

| wp_commentmeta            |

| wp_comments                |

| wp_links                  |

| wp_options                |

| wp_postmeta                |

| wp_posts                  |

| wp_term_relationships      |

| wp_term_taxonomy          |

| wp_terms                  |

| wp_usermeta                |

| wp_users                  |

| wp_wpeditor_settings      |

+----------------------------+

12 rows in set (0.01 sec)

Please help. Thanks

Rgds
satimis

Habitual

01-13-2014 10:21 AM

That looks like it imported, else the tables would not be there.
Locally, in terminal issue this command:

Code:

mysql -p -e "use sat1318107284790; select * from wp_options;" -Ns

Is there data returned?

Also, the db names you create on a new host do NOT have to be named as they are on GoDaddy servers, ie:
sat1318107320909 and sat1318107284790...

I suggest simpler names on new hosts, such as
piano-04
piano-03

so, your "Create database" statement would be something like

Code:

CREATE DATABASE piano-0n DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

where n is a number that represents one of your piano sites. The choice is up to you. It is merely a
recommendation.

Export from GoDaddy using the settings shown in 1.png attached.
Once saved locally, open the file and remove the lines near|on lines 22 and 23

Code:

CREATE DATABASE `sat1318107284790` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `sat1318107284790`;

They are NOT needed.

save the file. Note the location of the saved file.
create the db locally as root (mysql -p <enter>)

Code:

CREATE DATABASE piano-0n DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; exit;

locate the file downloaded to your machine from the GoDaddy phpMyAdmin interface. then your grant statement to allow php/www to connect (for wp-config.php...?)

Code:

grant all on db_name.* to 'wordpress'@'localhost' identified by 'password'; flush privileges; exit;

Now edit your wp-config.php for the credentials and dbname

Now you import it,
as root:
mysql -p dbname < /path/to/file.sql

check the data with

Code:

mysql -p -e "use dbname; select * from wp_options;" -Ns

http://blog.brianjohnsondesign.com/h...erly-tutorial/

satimis

01-14-2014 05:23 AM

Hi

Something strange happened here.

mysql> SHOW databases;

Code:

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test              |

| wordpress          |

+--------------------+

5 rows in set (0.03 sec)

sat1318107284790 database not found?

$ mysql -p -e "use sat1318107284790; select * from wp_options;" -Ns
Enter password:

Code:

ERROR 1045 (28000): Access denied for user 'satimis'@'localhost' (using password: YES)

The password didn't work.

I can't resolve WHY?

satimis

satimis

01-14-2014 10:35 AM

Hi Habitual,

Have another round
Cloned another VM and performed another test following that article mentioned before. The steps works but missing following steps;

1)
on local server
$ sudo mv /var/www/wordpress/wp-content /var/www/wordpress/wp-content.OLD

2)
start FileZilla on local server
copy Live site "wp-content", the complete folder, to local site /var/www/wordpress/

3)
Change the permalinks

Now the local site works same as Live site

The author suggests running "WordPress Database Backup". But I couldn't make it work.

Edit:

A further question.

Have you tried before copying several Live sites on the same server? It is possible creating several databases on ONE MySQL. What about WordPress? How to create several localhosts on same WordPress instead of installing several copies of WordPress on the same server.

Rgds
satimis

Habitual

01-14-2014 02:51 PM

Quote:

Originally Posted by satimis (Post 5097711)

[code]$ mysql -p -e "use sat1318107284790; select * from wp_options;" -Ns
Enter password:

Code:

ERROR 1045 (28000): Access denied for user 'satimis'@'localhost' (using password: YES)

My bad on that:
Over here, you had used sat1318107320909, so now try

Code:

$ mysql -uroot -p -e "use sat1318107320909; select * from wp_options;" -Ns

and

Code:

$ mysql -uroot -p -e "use sat1318107284790; select * from wp_options;" -Ns

One of those has your wp data.

Quote:

A further question.

Have you tried before copying several Live sites on the same server? It is possible creating several databases on ONE MySQL. What about WordPress? How to create several localhosts on same WordPress instead of installing several copies of WordPress on the same server.

Several copies of Wordpress one one host? sure can.
Several wp databases in one mysql environment? sure can.

Easier-to-remember what is what and where:
mkdir /var/www/piano3
mkdir /var/www/piano4
copy your GD Live site files using FileZilla to each of those directories.
Export each site into its own pianoN.sql file
http://piano-03.satimis.com into piano3.sql
http://piano-04.satimis.com into piano4.sql

as root:

Code:

mysql -uroot -p  

CREATE DATABASE piano3 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE DATABASE piano4 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Over here, I suggest simpler names for your dbs on the localhost, so that is what I'm using here.

Now grant the same user/pass but on those 2 dbs in mysql> using "mysql -uroot -p" (DO NOT CHANGE DBs)

Code:

grant all on piano3.* to 'wordpress'@'localhost' identified by 'password'; flush privileges; 

grant all on piano4.* to 'wordpress'@'localhost' identified by 'password'; flush privileges; exit;

or quite possibly both at the same time using:

Code:

grant all on piano*.* to 'wordpress'@'localhost' identified by 'password'; flush privileges; exit;

Import (as root):

Code:

mysql -uroot -p piano3 < /path/to/piano3.sql

mysql -uroot -p piano4 < /path/to/piano4.sql

2 dbs and 2 sites. :)

edit each wp-config.php in each of those directories to point to the "correct" db and using the correct 'wordpress' user and their <password>)

http://localhost/piano3 should have a site resembling http://piano-03.satimis.com/
http://localhost/piano4 should have a site resembling http://piano-04.satimis.com/

when checking for success and code output using mysql -p -e "some command" -Ns, use

Code:

mysql -uroot -p -e "some command;" -Ns

instead.

Editorial note: All this work for what appears to be a stock install with no posts at http://piano-04.satimis.com/ ?
You could have installed a fresh wp instance and added the theme/plugins and be done with it by now.
but then what would you have learned?

A note about DBs:
The dbs can be named anything you want.
Paris, Madrid, SanFrancisco, or Mars, Venus, Earth, etc...
Just because GoDaddy exports your data as sat1318107320909 or sat1318107284790 you do NOT have to name your dbs that way when you move it/them.

P.S. I'm done helping here.
If I have to repeat these same instructions one more time, I'll go mad.

satimis

01-14-2014 09:53 PM

Quote:

Originally Posted by Habitual (Post 5097985)

My bad on that:
Over here, you had used sat1318107320909, so now try

Code:

$ mysql -uroot -p -e "use sat1318107320909; select * from wp_options;" -Ns

and

Code:

$ mysql -uroot -p -e "use sat1318107284790; select * from wp_options;" -Ns

One of those has your wp data.

Hi Habitual,

$ mysql -uroot -p -e "use sat1318107284790; select * from wp_options;" -Ns
Enter password:

Code:

.....

.....

458";}s:5:"stats";a:3:{s:4:"name";s:5:"stats";s:4:"slug";s:5:"stats";s:5:"count";s:3:"453";}s:8:"category";a:3:{s:4:"name";s:8:"category";s:4:"slug";s:8:"category";s:5:"count";s:3:"452";}s:7:"youtube";a:3:{s:4:"name";s:7:"youtube";s:4:"slug";s:7:"youtube";s:5:"count";s:3:"436";}s:7:"comment";a:3:{s:4:"name";s:7:"comment";s:4:"slug";s:7:"comment";s:5:"count";s:3:"432";}}        yes

627        easyrotator_compatibility_shortcodesultimate_isenabled        0        yes

1070        _transient_doing_cron        1389519872.1653530597686767578125        yes

several pages of output generated.

$ mysql -uroot -p -e "use sat1318107284790; select * from wp_options;" -Ns
Enter password:

Code:

ERROR 1049 (42000) at line 1: Unknown database 'sat1318107284790'

To other advice I'll come back later. Thanks

satimis

Habitual

01-15-2014 08:03 AM

the db sat1318107284790 has your wp data in it.

Good Luck. I'm done.

satimis

01-15-2014 08:22 AM

Hi Habitual,

Thanks for your detail instruction. Performed following steps;

Cloned a new VM, WordPress and LAMP already installed and working.

$ sudo mkdir /var/www/piano1
$ sudo mkdir /var/www/piano2

$ su
Password:
# filezilla

Start FileZilla -> connect Godaddy server

copied GD Live site files.
(remark: I must start FileZilla as root otherwise I can't copy files to /var/www/piano1 and /piano2)

Ran GD online phpMyAdmin exporting;
sat1317312174333 to /Downloads/piano1.sql
sat1318208313544 to /Downloads/piano2.sql

# mysql -uroot -p
Enter password:

mysql> CREATE DATABASE piano1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Code:

Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE piano2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Code:

Query OK, 1 row affected (0.00 sec)

mysql> grant all on piano1.* to 'wordpress'@'localhost' identified by 'password';

Code:

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Code:

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on piano2.* to 'wordpress'@'localhost' identified by 'password';

Code:

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; exit;

Code:

Query OK, 0 rows affected (0.00 sec)



Bye

(remark: same password)

# mysql -uroot -p piano1 < Downloads/piano1.sql
Enter password:
# mysql -uroot -p piano2 < Downloads/piano2.sql
Enter password:

(remark: no complaint)

On browser:
http://localhost/piano1
http://localhost/pinao2

Code:

Error establishing a database connection

$ mysql -uroot -p -e "use piano1; select * from wp_options;" -Ns
Enter password:

Code:

ERROR 1146 (42S02) at line 1: Table 'piano1.wp_options' doesn't exist

$ mysql -uroot -p -e "use piano2; select * from wp_options;" -Ns
Enter password:

Code:

ERROR 1146 (42S02) at line 1: Table 'piano2.wp_options' doesn't exist

$ mysql -uroot -p

mysql> SHOW databases;

Code:

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| piano1            |

| piano2            |

| sat1317312174333  |

| sat1318208313544  |

| test              |

| wordpress          |

+--------------------+

9 rows in set (0.04 sec)

mysql> USE piano1;

Code:

Database changed

mysql> SHOW tables;

Code:

Empty set (0.00 sec)

mysql> USE sat1317312174333;

Code:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A



Database changed

mysql> SHOW tables;

Code:

+----------------------------+

| Tables_in_sat1317312174333 |

+----------------------------+

| wp_commentmeta            |

| wp_comments                |

| wp_links                  |

| wp_options                |

| wp_postmeta                |

| wp_posts                  |

| wp_term_relationships      |

| wp_term_taxonomy          |

| wp_terms                  |

| wp_usermeta                |

| wp_users                  |

+----------------------------+

11 rows in set (0.00 sec)

It seems we can't change the name of database.

Rgds
satimis

Habitual

01-16-2014 06:26 AM

Quote:

Originally Posted by satimis (Post 5098482)

It seems we can't change the name of database.

Rgds
satimis

Don't use phone-speak here, spell out entire words entirely.

Quote:

Originally Posted by Habitual (Post 5097232)

Once saved locally, open the file and remove the lines near|on lines 22 and 23

They start with

Code:

CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `dbname`;

Remove these 2 lines prior to the import and save the piano.sql file, each of them.
They "may" not exist, but look for them anyway.

and another step you missed:

Quote:

Originally Posted by Habitual (Post 5097985)

edit each wp-config.php in each of those directories to point to the "correct" db and using the correct 'wordpress' user and their <password>)

satimis

01-16-2014 08:16 AM

Quote:

Originally Posted by Habitual (Post 5099073)

-snip -

They start with

Code:

CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `dbname`;

Remove these 2 lines prior to the import and save the piano.sql file, each of them.
They "may" not exist, but look for them anyway.

Can't find those lines.

Quote:

and another step you missed:

Tried;
1)
define('DB_NAME', 'piano2');
define('DB_USER', 'root');
define('DB_PASSWORD', 'mysql_rootpasswd');

2)
define('DB_NAME', 'piano2');
define('DB_USER', 'GD db_user');
define('DB_PASSWORD', 'GD db_user_passwd');

Rgds
satimis

Habitual

01-16-2014 08:55 AM

Don't EVER use root in ANY website config.php file to connect, except on localhost. It's a security risk, except on localhost.
and even that may have issues. (depends if you're behind a router, or firewall) and even then it's a BAD HABIT.

What "grant all" statement did you use earlier on piano2?

Code:

grep "grant all" /root/.mysql_history

and if you use mysql -uroot but as yourself, then

Code:

grep "grant all" ~/.mysql_history

One of those has the grant statement.

If it's still not evident, then

Code:

mysql -uroot -p -e "use mysql; select User,Host from user;"

Find the user and use that in your multiple wp-config.php files to connect to each database.

satimis

01-16-2014 09:55 AM

Quote:

Originally Posted by Habitual (Post 5099147)

Noted and thanks.

Quote:

What "grant all" statement did you use earlier on piano2?

Code:

mysql> grant all on piano2.* to 'wordpress'@'localhost' identified by 'piano0114';

same password for both sites

Quote:

Code:

grep "grant all" /root/.mysql_history

and if you use mysql -uroot but as yourself, then

Code:

grep "grant all" ~/.mysql_history

One of those has the grant statement.

If it's still not evident, then

Code:

mysql -uroot -p -e "use mysql; select User,Host from user;"

Find the user and use that in your multiple wp-config.php files to connect to each database.

# grep "grant all" /root/.mysql_history

Code:

 

grant all on piano1.* to 'wordpress'@'localhost' identified by 'piano0114';

grant all on piano2.* to 'wordpress'@'localhost' identified by 'piano0114';

grant all on piano1.* to 'wordpress'@'localhost' identified by 'piano0114';

# mysql -uroot -p -e "use mysql; select User,Host from user;"
Enter password:

Code:

+------------------+-------------+

| User            | Host        |

+------------------+-------------+

| root            | 127.0.0.1  |

| root            | ::1        |

|                  | localhost  |

| debian-sys-maint | localhost  |

| root            | localhost  |

| wordpress        | localhost  |

|                  | mint16ssd00 |

| root            | mint16ssd00 |

+------------------+-------------+

satimis

Habitual

01-16-2014 11:07 AM

Great progress, Now let's test from c-line before editing wp-config.php

Code:

mysql -uwordpress -ppiano0114 -e "show databases;"

what's the output?

satimis

01-16-2014 09:34 PM

Quote:

Originally Posted by Habitual (Post 5099242)

Great progress, Now let's test from c-line before editing wp-config.php

Code:

mysql -uwordpress -ppiano0114 -e "show databases;"

what's the output?

$ mysql -uwordpress -ppiano0114 -e "show databases;"

Code:

+--------------------+

| Database          |

+--------------------+

| information_schema |

| piano1            |

| piano2            |

| test              |

| wordpress          |

+--------------------+

$ mysql -uroot -p

mysql> USE piano1; show tables;

Code:

Database changed

Empty set (0.00 sec)

mysql> USE piano2; show tables;

Code:

Database changed

Empty set (0.00 sec)

But with no tables inside.

satimis

Habitual

01-17-2014 07:02 AM

re-do these steps...

Quote:

Originally Posted by satimis (Post 5098482)

[/code]

# mysql -uroot -p piano1 < Downloads/piano1.sql
Enter password:
# mysql -uroot -p piano2 < Downloads/piano2.sql
Enter password:

then do the "show tables" routine again.

satimis

01-17-2014 08:41 AM

Quote:

Originally Posted by Habitual (Post 5099700)

re-do these steps...

then do the "show tables" routine again.

$ su
Password:

# ls Downloads/

Code:

piano1.sql piano2.sql

# mysql -uroot -p piano1 < Downloads/piano1.sql
Enter password:

Code:

 

ERROR 1007 (HY000) at line 21: Can't create database 'sat1317312174333'; database exists

# mysql -uroot -p piano1 < Downloads/piano2.sql
Enter password:

Code:

ERROR 1007 (HY000) at line 21: Can't create database 'sat1318208313544'; database exists

Previously when I ran above commands sat1317312174333 and sat1318208313544 were imported. They are the original name of the database.

satimis

Habitual

01-17-2014 01:06 PM

Quote:

Originally Posted by satimis (Post 5099741)

[/code]

# mysql -uroot -p piano1 < Downloads/piano2.sql
Enter password:

Code:

ERROR 1007 (HY000) at line 21: Can't create database 'sat1318208313544'; database exists

Previously when I ran above commands sat1317312174333 and sat1318208313544 were imported. They are the original name of the database.

satimis

This can only mean there are

Code:

CREATE DATABASE `dbname`...<more here possibly>;

USE `dbname`;

statements in the .sql files. Please use a text editor and search for "CREATE DATABASE" in the .sql files.

satimis

01-17-2014 08:52 PM

Quote:

Originally Posted by Habitual (Post 5099857)

This can only mean there are

Code:

CREATE DATABASE `dbname`...<more here possibly>;

USE `dbname`;

statements in the .sql files. Please use a text editor and search for "CREATE DATABASE" in the .sql files.

$ cat Downloads/piano1.sql | grep 'CREATE DATABASE'

Code:

CREATE DATABASE `sat1317312174333` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

$ cat Downloads/piano2.sql | grep 'CREATE DATABASE'

Code:

CREATE DATABASE `sat1318208313544` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Habitual

01-18-2014 09:00 AM

Quote:

Code:

CREATE DATABASE `sat1317312174333` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

...

CREATE DATABASE `sat1318208313544` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Remove those lines and the "use db;" lines which should be the one below them before you re-import them.

satimis

01-18-2014 09:47 AM

Quote:

Originally Posted by Habitual (Post 5100243)

Remove those lines and the "use db;" lines which should be the one below them before you re-import them.

$ cp Downloads/piano1.sql Downloads/piano1.sql.Orig
$ cp Downloads/piano2.sql Downloads/piano2.sql.Orig
# ls Downloads/

Code:

piano1.sql piano1.sql.Orig piano2.sql piano2.sql.Orig

$ gedit Downloads/piano1.sql
deleted following lines

Code:

CREATE DATABASE `sat1317312174333` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `sat1317312174333`;

$ gedit Downloads/piano2.sql
deleted following lines

Code:

CREATE DATABASE `sat1318208313544` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `sat1318208313544`;

# mysql -uroot -p piano1 < Downloads/piano1.sql
Enter password:

# mysql -uroot -p piano2 < Downloads/piano2.sql
Enter password:
(No complaint)

# gedit /var/www/piano1/wp-config.php
Warning on terminal

Code:

....

(gedit:2729): Gtk-WARNING **: Attempting to store changes into `/root/.local/share/recently-used.xbel', but failed: Failed to create file '/root/.local/share/recently-used.xbel.24ZH9W': No such file or directory



(gedit:2729): Gtk-WARNING **: Attempting to set the permissions of `/root/.local/share/recently-used.xbel', but failed: No such file or directory



(gedit:2729): GConf-WARNING **: Client failed to connect to the D-BUS daemon:

Did not receive a reply. Possible causes include: the remote application did not send a reply, the message bus security policy blocked the reply, the reply timeout expired, or the network connection was broken.

GConf Error: No D-BUS daemon running

Can't proceed to edit

Code:

define('DB_NAME', 'piano1');



/** MySQL database username */

define('DB_USER', 'piano1');



/** MySQL database password */

define('DB_PASSWORD', 'passwd_to_login_database');



define('WP_HOME','http://localhost/piano1');

define('WP_SITEURL','http://localhost/piano1');

satimis

Habitual

01-18-2014 10:03 AM

don't use gedit?

satimis

01-18-2014 10:24 AM

Quote:

Originally Posted by Habitual (Post 5100265)

don't use gedit?

# nano /var/www/piano2/wp-config.php
made following changes

Code:

define('DB_NAME', 'piano2');



define('DB_USER', 'piano2');



define('DB_PASSWORD', 'password');

adding following lines

Code:

define('WP_HOME','http://localhost/piano2');

define('WP_SITEURL','http://localhost/piano2');

On browser;
http://localhost/piano2

Code:

Error establishing a database connection

I tried with root and root_passwd as test. But still failed,Error establishing a database connection

Edit:
Change;

Code:

define('DB_HOST', 'sat1318208313544.db.10579588.hostedresource.com');

to;

Code:

define('DB_HOST', 'localhost');

using root and root_passwd. Then it works. Pls advise how to bring it back to user-piano2 and its password?

satimis

Habitual

01-18-2014 05:12 PM

Quote:

Originally Posted by satimis (Post 5100271)

[code]
define('DB_USER', 'piano2');Pls advise how to bring it back to user-piano2 and its password?

This is WRONG.

Quote:

Originally Posted by satimis (Post 5099487)

$ mysql -uwordpress -ppiano0114 -e "show databases;"

Code:

+--------------------+

| Database          |

+--------------------+

| information_schema |

| piano1            |

| piano2            |

| test              |

| wordpress          |

+--------------------+

Aren't you tired of running in circles?

satimis

01-18-2014 06:35 PM

Quote:

Originally Posted by Habitual (Post 5100444)

This is WRONG.

Ah I got it.

I mistakenly thought the users are piano1 and piano2. They should be "wordpress", same user.

Thanks.

After changing the permalinks, the local sites work similar to the Live site. I'll make another round on other Live sites in order to further practise.

Lot of thanks for your effort and time in supporting me.

Rgds
satimis

Habitual

01-19-2014 08:19 AM

Quote:

Originally Posted by satimis (Post 5100488)

Lot of thanks for your effort and time in supporting me.

You are very welcome.
Glad it worked out.

All times are GMT -5. The time now is 03:08 PM.