I use master slave a lot for speeding up reads at remote offices at the end of slow (512 kbit) connections. My implementation and experience is as follows:
Applications are written to read from the slave and write to the master. Reads inside a transaction say for a last_insert_id() need to be done from the master.
In the event of an outage (say the broadband link goes down) reads continue from the slave transparently, but writes are not possible for that remote location. Other writes continue from other locations. The slaves keep updating as normal.
When the link is restored, the slave reconnects and downloads any updates and synchronises itself, usually transparently.
This has been pretty successful for me where I have lots of people reading and only a few updating, most of whome are at head office.
You can have multiple slaves updating from the master and, if I remember correctly, a slave can also update from another slave so you can have "layers" of slaves.
The following is a useful read.
http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html
When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:
mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)
mysql>
Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.
You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.
If you want to just allow anonymous SELECT against that table, first run this:
GRANT USAGE ON *.* TO 'wordpress'@'%';
This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.
You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:
SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
SELECT CONCAT('''',user,'''@''',host,'''') userhost
FROM mysql.user WHERE user='wordpress'
) A;
Best Answer
I can't answer to question 1 as I do not have enough information about the MySQL instances offered by different hosting providers.
To answer the rest of your questions, yes, if you can leverage the features of the RDBMS you're using, you might replace PHP code with SQL scripts that can run faster than your code. Just think about the time you're saving up by not transferring your data back and forth.
On the other hand you would probably still end up using PHP code or other programs (crontab or any other kind of scheduler) to run your timed tasks as there's no built-in scheduler in MySQL.