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 ?

2 Answers 2

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.

3
  • @"Michael - sqlbot" What you propose is exactly what i need. Indeed, the easyest and best way to do what i need would be to create 8 "mirror" tables of one that store informations. Did i understood what you meant ? Unfortunately, when i tryed this on local, i got this message : ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM master' at line 1. Thank you for your time Oct 11, 2014 at 4:11
  • @Jacques-AlexandreBossu I inadvertently omitted the keyword AS before SELECT in the view definition. Updated. Oct 11, 2014 at 4:20
  • That worked as i expected ! I will try it on my websites servers when they will be ready, and i will post here if that succeeded. Thank you a lot for your clear and fast explanation ! Oct 11, 2014 at 5:05
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.