1

I will use eight wordpress websites and i need to sync ther wp-user tables so each single user can connect through all websites. All of these will be on the same server, using the same MySQL user, but they will each have their own database. The server will be a mutualised one, so i cannot install any other software that MySQL.

What i thought of doing was this :

  • create a master table which will collect recursively all other tables.
  • When it's finished, it is supposed to have every information of the other up to date... Isn't it ?
  • Then i would sync all previous databases on this master one.

What do you think about it ? But i don't know how to do that. I thought of using CRON to run that once a day, but i don't know anything about how to write MySQL scripts, and didn't understand much about the "MySQL Replication" topics i found until now...

Thank you in advance.

EDIT : Michael - sqlbot suggested using the "merge algorithm" to do what i need. I like what it looks like, but did not succeed using it. Maybe because I run MariaDB on Localhost. What do you thnik, and n=know about this solution ?

Jacques-Alexandre Bossu
  • 13
  • 4

2 Answers2

2

There's actually a very simple solution to "syncing" multiple identical tables in MySQL, and that is by not trying to "sync" actual base tables, at all... but instead creating something that looks like another table but is essentially something along the lines of a "reference" or "alias" to the base table, using a simple view like this:

CREATE ALGORITHM=MERGE VIEW this_database.users AS SELECT * FROM that_database.users;

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table...

— http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html

The view definition, above, meets these criteria, and would be updatable. Queries referencing a view with a simple definition like this will behave, essentially, no differently than if they were actually written to update (and insert into, and delete from) the actual underlying base table.

So INSERT INTO this_database.users is exactly the same as INSERT INTO that_database.users, and the same is true for UPDATE and DELETE.

If there are foreign keys involved, then the child tables have to have their foreign keys declared against the actual base parent table, but MySQL (InnoDB) has no issue with foreign key constraints that cross database boundaries.

Indexes on the base table will still be used where appropriate, since the MERGE algorithm essentially causes MySQL to "merge" the logic of the view definition into the query you've actually written -- thereby accomplishing the magic of a table that functionally is a reference to another table.

You don't actually have to explicitly declare ALGORITHM=MERGE, since the optimizer will always choose it on such a simple view, but it's still probably a good choice to include that in the definition.

While it is possible that Wordpress might do something internally that prevents this from working, I can't think of anything offhand. It seems like it would be worth testing in your environment.

Michael - sqlbot
  • 22,715
  • 2
  • 49
  • 76
1

I can think of 2 options that you can do within MySQL:

  1. Use event scheduler (it's like a crontab for mysql) to do what you described.
  2. Create triggers on each user tables to synchronize the updates to other tables.

Both have pros and cons, so it's up to dev's preference.

Manny Calavera
  • 463
  • 3
  • 10