LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices


Reply
  Search this Thread
Old 01-08-2014, 10:04 AM   #16
satimis
Senior Member
 
Registered: Apr 2003
Posts: 3,695

Original Poster
Rep: Reputation: 56

Quote:
Originally Posted by TenTenths View Post
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

Last edited by satimis; 01-08-2014 at 10:59 AM.
 
Old 01-08-2014, 11:03 AM   #17
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
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

Last edited by Habitual; 01-08-2014 at 11:09 AM.
 
Old 01-09-2014, 06:02 AM   #18
satimis
Senior Member
 
Registered: Apr 2003
Posts: 3,695

Original Poster
Rep: Reputation: 56
Quote:
Originally Posted by Habitual View Post
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
 
Old 01-09-2014, 09:02 AM   #19
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
Quote:
Originally Posted by satimis View Post
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.
 
Old 01-09-2014, 09:37 AM   #20
satimis
Senior Member
 
Registered: Apr 2003
Posts: 3,695

Original Poster
Rep: Reputation: 56
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

Last edited by satimis; 01-09-2014 at 09:38 AM.
 
Old 01-09-2014, 10:17 AM   #21
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
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

Last edited by Habitual; 01-10-2014 at 07:13 AM.
 
Old 01-10-2014, 10:12 AM   #22
satimis
Senior Member
 
Registered: Apr 2003
Posts: 3,695

Original Poster
Rep: Reputation: 56
Quote:
Originally Posted by Habitual View Post
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>

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
Advice noted. Lot of thanks for your assistance.

Rgds
satimis
 
Old 01-10-2014, 10:13 AM   #23
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
You're welcome.
Glad it worked out.
 
Old 01-12-2014, 10:40 AM   #24
satimis
Senior Member
 
Registered: Apr 2003
Posts: 3,695

Original Poster
Rep: Reputation: 56
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
 
Old 01-13-2014, 10:21 AM   #25
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
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/

Last edited by Habitual; 06-26-2015 at 05:47 PM.
 
Old 01-14-2014, 05:23 AM   #26
satimis
Senior Member
 
Registered: Apr 2003
Posts: 3,695

Original Poster
Rep: Reputation: 56
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
 
Old 01-14-2014, 10:35 AM   #27
satimis
Senior Member
 
Registered: Apr 2003
Posts: 3,695

Original Poster
Rep: Reputation: 56
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

Last edited by satimis; 01-14-2014 at 10:50 AM.
 
Old 01-14-2014, 02:51 PM   #28
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
Quote:
Originally Posted by satimis View Post
[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.

Last edited by Habitual; 01-14-2014 at 03:13 PM.
 
Old 01-14-2014, 09:53 PM   #29
satimis
Senior Member
 
Registered: Apr 2003
Posts: 3,695

Original Poster
Rep: Reputation: 56
Quote:
Originally Posted by Habitual View Post
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
 
Old 01-15-2014, 08:03 AM   #30
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
the db sat1318107284790 has your wp data in it.

Good Luck. I'm done.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL backups cpanel style SeSoX Linux - Server 5 01-11-2011 06:31 AM
How to create subdomain in linux without having cpanel. vinoth.t Linux - Newbie 1 10-19-2010 03:18 PM
phpmyadmin shows blank page; no "phpmyadmin" database in mysql qajaq Linux - Software 2 12-20-2009 12:23 PM
Cpanel/WHM Died at /usr/local/cpanel/Cpanel/Hulk.pm line 92. liang3391 Linux - Software 1 06-22-2009 02:02 PM
cPanel & mySQL directories Alan Powell Linux - Newbie 1 04-26-2004 06:31 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - General

All times are GMT -5. The time now is 05:27 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration