[SOLVED] How to create MySQL cPanel on Phpmyadmin

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

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

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

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

-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

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.

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

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

[/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

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

[/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

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

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

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