2

Is there a way I can port data from one mysql database table and transfer it to another mysql database table.

both of these mysql databases are joomla databases, and i would like to transfer table_users.

Is there a way i can automate this process, maybe use a cronjob.

  • 2
    Export the table, then import it – Lodder May 22 '16 at 12:33
  • hi, sorry i should have said it needs to be automated, i have edited the question. – user2513528 May 22 '16 at 12:35
  • Ah, so you'd like to have something along the lines of a cron job? – Lodder May 22 '16 at 12:44
  • hi, well im interested in having an automated process really, doesnt have to be a cronjob, i just thought that might be the best way – user2513528 May 22 '16 at 12:49
1

You can share the users from one Joomla! site to multiple other Joomla! sites (as long as you keep them on the same J! version) simply by linking the user tables of the databases. There is no need for creating cron jobs or complex data transfer scripts. Plus, if you only try to transfer the table_users table, you could have issues within the site.

This is how you do it in Joomla! 2.5 - 3.6 (current version):

  1. Choose the "master" site from which the users will be shared (this is the database which will actually store the data).
  2. Copy the $secret value from the master site's configuration.php file to the same file on all the "sister" sites.
  3. Ensure access to the master site's database is given to the users of each of the sister sites' databases.
  4. Run the following SQL script on each of the sister sites' databases:

    RENAME TABLE `josPrefix2_users` TO `josPrefix2_bak_users`;
    RENAME TABLE `josPrefix2_session` TO `josPrefix2_bak_session`;
    RENAME TABLE `josPrefix2_usergroups` TO `josPrefix2_bak_usergroups`;
    RENAME TABLE `josPrefix2_user_notes` TO `josPrefix2_bak_user_notes`;
    RENAME TABLE `josPrefix2_user_profiles` TO `josPrefix2_bak_user_profiles`;
    RENAME TABLE `josPrefix2_user_usergroup_map` TO `josPrefix2_bak_user_usergroup_map`;
    RENAME TABLE `josPrefix2_viewlevels` TO `josPrefix2_bak_viewlevels`;
    
    CREATE VIEW josPrefix2_users AS SELECT * FROM dbSite1.josPrefix1_users;
    CREATE VIEW josPrefix2_session AS SELECT * FROM dbSite1.josPrefix1_session;
    CREATE VIEW josPrefix2_usergroups AS SELECT * FROM dbSite1.josPrefix1_usergroups;
    CREATE VIEW josPrefix2_user_notes AS SELECT * FROM dbSite1.josPrefix1_user_notes;
    CREATE VIEW josPrefix2_user_profiles AS SELECT * FROM dbSite1.josPrefix1_user_profiles;
    CREATE VIEW josPrefix2_user_usergroup_map AS SELECT * FROM dbSite1.josPrefix1_user_usergroup_map;
    CREATE VIEW josPrefix2_viewlevels AS SELECT * FROM dbSite1.josPrefix1_viewlevels;
    

Note 1: For the script above, you will need to:

  1. Replace dbSite1 with the name of your master site's database.
  2. Replace josPrefix1 with the prefix of your master site's database tables.
  3. Replace josPrefix2 with the prefix of your sister site's database tables.

Note 2: If you already have data stored in one of the sister sites, you may need to update the user ID of many entries in the database as the user IDs will have changed. Examples: article author and modified user, LogMan access logs, etc.


Original Answer:

How to share table between 2 or more databases?

0

You can simply create 3 MySql queries (if it's not custom DB tables) like

"insert ... select ..."

so it will select data as you need it and insert into another table in 1 transaction so migration of half million records will be done in up to 20 seconds. I can give you more specific for you details if you email me kosarev@joomplace.com

  • hi, sorry, i have just seen this reply now, ill email you tonight. thanks – user2513528 Jun 12 '16 at 1:08

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.