beaniecopter

The problem is solved; it turned out to be a matter of MySQL permissions. For the benefit of anyone else who has experienced similar difficulty, here is what I found and where I found it:

(1) The best general guide which I found was “WordPress on Debian Linux”, which is posted at:

http://www.supriyadisw.net/2006/08/wordpress-on-debian-linux

This article provides a step-by-step procedure for setting up Apache, with only a couple of minor typographical errors, and a couple of omissions, namely: install “libapache2-mod-php4” rather than “php4”, and “php4-mysql” is needed in addition to “php4-pgsql”. However, I had only limited sucess with the procedures which are specific to setting up and configuring MySQL.

(2) The best detailed guide which I found was “DJG’s Setting up MySQL users and databases”, November 25th, 1999, which is posted at:

http://www.linuxhelp.net/guides/mysql/

(3) I discovered an EXCELLENT tutorial regarding MySQL permissions, “An Introduction to MySQL permissions”, February 17, 2004, which is posted at:

http://www.databasejournal.com/features/mysql/article.php/3311731

The only things which the article failed to mention is the need for the command “use mysql;” at the first “mysql>” prompt. Also, the example commands twice omit the semicolon (“;”) which is required at the end of each MySQL command.

(4) I found helpful the MAN pages for “mysql”, “mysqladmin”, and “mysqldump”. In particular, the MAN page for “mysqldump”, at the end, provides examples of useful applications for the utility.

(5) I finally realized that life is too short to fool around with a graphical user interface (GUI) application for routine or repetive tasks such as daily or weekly backup of a database, if there exists a command-line interface which can handle the task. So, rather than continue using “mysql administrator”, I have switched to “mysqldump”, which is INFINITELY more simple, and which can be invoked by “cron” or “anacron”.

(6) I used the following commands to set permissions:

# mysqladmin -u root -p

<< mysqladmin prompts for local_database_administrative_password. >>

mysql> USE mysql;

mysql> GRANT all ON local_database_name.*
TO local_database_username@localhost
IDENTIFIED BY ‘local_database_user_password’;

mysql> QUIT

# mysqladmin -u root -p reload

(7) It turns out that, once the permissions are properly set, retrieving a copy of the database tables from the on-line, commercially-hosted blog required only the following command:

$ mysqldump
-h URL_of_remote_commercial_blog_host
-u remote_database_username
-premote_database_password
remote_database_name > name_of_copy.sql

And it turns out that loading a copy of the tables into the local “sandbox” blog required only the following command:

$ mysql -h localhost
-u local_database_username
-plocal_database_password
local_database_name < name_of_copy.sql

In the above commands, note that:

-> “$” is the terminal command prompt for a normal user.

-> Each command must be entered as a single line.

-> No space is permitted following the “-p” options.

-> The “-p” option does not provide security for the password.

-> The name of the “.sql” file created by mysqldump (“name_of_copy.sql”) is arbitrary, including the “.sql” suffix.

-> The user must substitute the actual names for:
“local_database_administrative_password”
“local_database_username”
“local_database_user_password”
“local_database_name”
“URL_of_remote_commercial_blog_host”
“remote_database_username”
“remote_database_password”
“remote_database_name”
“name_of_copy”

(8) Please note that some people use the term “sandbox” for a “static” blog, in order to experiment with themes and stylesheet without the complexity of running a database on the local host. But I am using the term “sandbox” for a fully-functional “dynamic” blog on the local host which allows me to exercise the blog normally, in every respect. If I desired, I could use my desktop machine to serve this blog to the Internet.

RLH
28 August 2006

Thanks for your interest and patience!

(1) Existing remote database is “bc_wordpress”. New local database is “wordpress”. (For the purpose of discussion, I should have called them, respectively, “remote” and “local”.) In any case, the remote database has a prefix, but the local database does not.

However, prefixes are irrelevant in this case, are they not? That is, could I not name the databases “remote_wordpress” and “local_wordpress”?

(2) Forgive me, but I do not understand what you mean by “importing the tables from the remotely-hosted database into the local mysql install”.

The blog has been running on the remote host for about a year now. During that time, I have been using “mysql admin” to back up the remote data base.

When I log into “mysql admin”, I am able to see the backup of the remote data base as one of the “catalogs”, but I don’t know how to proceed. I have been trying — without success — to use the “restore backup” function.

(3) I think that you are telling me that mysql (or perphaps mysqladmin) has the ability to copy tables from one database to another. I have tried the various menus, but I have not discovered how to do this.

(4) Also I have not discovered how to rename a data base using mysql or mysql admin.