How to export from old db, and import into fresh new v4.7 db

Discussion in 'Installation' started by brucetech3, Jan 27, 2016.

  1. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    HI, I am looking for some help on How to export user data from an old AMember v4.2 db, and import it into a newly installed v4.7 AMember Pro db, which is on a new (different) hosting service.
    I will be needing to do this selectively, that is not a blanket total export and total import/overwrite.

    The new AMember Pro 4.7 MySQL db contains only the tables built by the setup program, and which includes the two very important must-not-be-messed-with tables: (am_admin, am_config). 10 tables have data at this early point in the setup process.

    I have asked Tech Support, but I confess, their reply, pasted below for reference, just does not provide me with enough information to actually do anything.
    And their second sentence that "Import/Export function does not allow to move all data" is disturbingly scary and does not help to explain what or why cannot be moved...

    AMember Tech Support Reply:

    My case is that I wish to move about 80% of the old tables data into the new AMember v4.7 db.
    (there are reasons...)

    I have been searching for tutorials on the web re selective export and import and related topic to/from MySQL databases.
    Most seem to assume you want to move 100% tables out and import 100% tables in. Not so in my case.
    Some suggest that a combination of something to export (not specified) and MySQL command line Insert Replace or Insert Ignore should be used.
    One -- the most promising -- suggests a selective PHPMyAdmin export from the old db and then an import of that subset into the new db.

    And Tech Support (noted above) advises that two of the new v4.7 tables must not be overwritten : am_admin, and am_config.
    So a blanket overwrite of the entire database is not advised and not planned.

    For the tables that I wish to move, I will be moving all the data, which in two cases implies a merge rather than an overwrite.
    Therefor the solution I am looking for should, I believe, allow me to move (or import or insert or whatever) a subset of the db tables, and not the whole database.

    Can someone who has experience in MySQL moving or export/import or selective restoring suggest some procedural steps I can take to accomplish this migration?

    (As may be evident, while I am a technical person, I have very little MySQL experience. Thus my feeling that the Tech Support reply does not give me enough to actually do anything - other than wonder...)

    FYI the database size of the old db is relatively small, I believe, at 1.5 MiB gzipped, 13 MiB plain sql data size.

    Thank you.

    (P.S. I have created a Word chart which lists the exact tables list and total number records in them comparing the old v4.2 vs the new v4.7, which I pulled from the PHPMyAdmin GUI on the old and new hosting server sites. For my work reference, of which need to be moved, and which definitely or likely not. I don't think I need to post that, at least not at this stage.)
  2. thehpmc

    thehpmc Member

    Joined:
    Aug 24, 2006
    Messages:
    901
    Might help if you gave some indication of the criteria involved in what was transferred or not.

    One method might be, depending on above comment, using a PHP script, read data from one database and using criteria contained within the script, filter out entries not required but those not filtered out write the data to a new database.

    Use this new database in new aMember installation
  3. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    Thanks Thehpmc, for the question/suggestion, a valid point.
    Our Data considerations when migrating to this new v4.7 AMember installation on a new hosting service.
    Primary criteria: Assume that the 10 tables which are installed and populated by the fresh new AMember v4.7 setup process are necessary and 'pristine' data. CGI Central Tech Support said that tables am_admin and am_config must be kept and not overwritten. In browsing the new v4.7 data tables, it looks like am_store is also a critical table to keep in its v4.7 state and not overwite with old data.
    Keep these tables as-is in new v4.7 database:
    AM Table Name . Data Content Notes ................ Action
    --------------- ----------------------------------- ----------
    am_admin ...... Specified as Critical* to keep .... Keep as is
    am_admin_log .. log data, applies to installed db . Keep
    am_config ..... Specified as Critical* to keep .... Keep as is
    am_country .... Country Codes ..................... Keep as is
    am_email_template ??
    am_error_log .. log data, applies to installed db . Keep as is
    am_resource_access_sort ??
    am_session .... ??
    am_state ...... State Codes ....................... Keep as is
    am_store ...... Likely Critical to keep ........... Keep as is
    --------

    Secondary criteria:
    Copy/move the actual user membership data from the old AMember v4.2 db tables - the records of our membership users, dates, subscription status and the like.
    Third consideration:
    Probably do not need to copy or move log file data or errors related to the rather hazy development of the old v4.2 db.
    In this way, to be able to start with as clean a set of valid data as possible on the new hosting service with the fresh new AMember Pro v4.7 db.

    Note, I have just set up two additional MySQL dbs on the hosting service. One to take a copy of the fresh v4.7 installation, plan to use it to test my data migration(s) before putting it into the real v4.7 db.
    The second to be filled with the old (yet still active and live) data from the existing v4.2 Amember on the current (soon to be old) service, in order to pull or extract or whatever from.
    Thanks for any ideas.

    [hard to get my 'table' columns to align in a forum posting environment where all multi-spaces are stripped out...]
    Last edited: Jan 28, 2016
  4. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    2,295
    I am a bit confused. What do you want to achieve with such actions? Please note that DB structure is different in different aMember versions.

    Do you want to move your installation to another server?
    Do you want to upgrade your installation to latest version?
    Do you want to move to new installation only subset of user database?

    What is main purpose of this merge? Please explain then I will be able to suggest some approach to achieve it.
  5. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    Hi Caesar, thanks for checking in.
    Question 1:
    Yes, we are moving off of an old and unreliable server hosting, with an old 'customized' version (4.2.15) AMember Pro.
    And we wish to be moving our non-profit membership data to our new, fresh and mostly empty AMember Pro v4.7.0. Newly installed on new different hosting service.
    Question 2:
    And so, at the same time, yes we are upgrading from old v4.2.15 to new v4.7.0.
    (And (obviously) we have no data for the 5 new tables that have been added to the newer AMember, which should not be a problem.)
    Question 3:
    We wish to take only the essential user member ship data. Because we do not have confidence in any of the very old development related data (logs and such) which is also contained in that 4.2.15 database.

    I have had, as noted above, only a two sentence official reply from Tech Support regarding the data migration.
    In which they state it can be accomplished, but no specific help on how to do so. (I was hoping for some command example and syntax, perhaps for PHPMyAdmin or if necessary, MySQL command line.)

    And in which they state that we must preserve (not overwrite) two critical tables:
    am_admin and am_config.
    I have been browsing the 10 tables populated in the v4.7 installation.
    It looks to me like there is at least one more critical table to preserve: am_store
    as it contains version info (specifically states the version is 4.7.0, so obviously do not want to overlay that with 4.2.15).
    As well, I do not want to overwrite the latest am_country and am_state tables.

    I have a working document which compares the tables in the two version dbs, and # recs, and highlights what I think we need to copy, looks like 30 tables. And also highlights a couple of tables I am not sure what the purpose is, so they are unknown to me if we should copy , or not. Looks like most of the tables we need will be able to just copy in (since they will be going into empty tables). Possibly need a merge of old with new on two of them.
    Shall I attach that?
    (I am trying to not provide too much info if not needed)
    Last edited: Jan 28, 2016
  6. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    2,295
  7. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    Thanks for replying.
    It is not as simple and clear a scenario as we would wish.
    Given that the existing host has billed us for 'upgrades' to the db, that is one of the reasons we wish to be off and gone from them.
    So, to try to upgrade the 4.2 to 4.7 on their server would be very problematic -- (a) they would likely wish to do it themselves and bill us for a new $$. And (b) as the site is live, I do not ever recommend to update a live site and just 'hope it keeps working for the users'. And if we were to sett up a parallel dev install on the old hosting server would run into issue (a) that they would charge us $$ to do that. So, not an option.
    For more details, please ask either Anton or Alex, or I could PM you if this board has a PM feature (I have not seen it yet).

    What I am going to try instead is:
    (a) Keep the successfully installed v4.7 pristine. It took a while to work through the login failure, needed Tech Support to go in and fix things. So I do not want to overwrite that, given the time and effort it took.

    (b) Create a new db (second db), import all of the v4.2 data from older server, then see if I can make an extract of the 4.2 selective data (29 tables). Done.
    Then import (set to INSERT IGNORE I think) that into a test copy of the 4.7 fresh db (third db).
    Then see if that works. So test test test.......

    (c) If I try something similar to your suggestion, it would be to copy amember program installation files from old server to different directory on the new server. (similar to the 'Move Amember To New Host' above).
    Create a (fourth) db, which will be the v4.2 user data to be used for running an in-place upgrade.
    Then find out how to make that work, what with this test amember v4.2 installation being in a non-standard location (see a above - my not wishing to undo days of work on the fresh good new v4.7).
    Then run the upgrade process from v4.2 to v4.7 on this alternate location and its db. (similar to the 'Upgrade Kit' instructions above.)
    Then test test test.
    As I may have mentioned above, this second upgrade method means we end up with a lot of stuff in the db related to all the unknowable updates done by the previous people on the server we are trying to get away from.

    Do either of these paths seem more likely to succeed?
    I am attaching pdf of my db tables comparison which may be useful.
    Thank you.

    Attached Files:

  8. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    2,295
    I can recommend:
    • close your current site for maintenance
    • move to new server
    • upgrade to latest version
    • check everything
    • change DNS
    • open site
    You can send email to all your user before move with notification that site will be closed for some period.
  9. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    I am part way through the migration process. Cannot do exactly as you suggest, as we have to keep the old site running till the new site is fully tested... lots of wrinkles to work through.

    Latest issue is,
    I have imported user data tables from the old v4.2 AMember database into the newly installed v4.7 AMember database. Essentially merged the user data into the new v4.7 structure, keeping a few of the new tables as is. Anton said we must keep
    am_admin, and
    am_config
    tables in the new 4.7 and not overwrite them.
    (Especially as he had to do something to the am_admin to get past AMember never allowing my login.)
    In looking at the tables content, I also left 4 other new tables as-is:
    am_admin_log,
    am_country,
    am_state,
    am_store.​
    I then imported 31 tables from the old 4.2 which contained actual user data and order information. I did not import error nor access tables/logs.
    Table .. Records .. IMPORTED
    am_billing_plan 3 IMP
    am_coupon 1 IMP
    am_coupon_batch 1 IMP
    am_data 8,982 IMP
    am_email_sent 13 IMP
    am_email_template 31 IMP
    am_helpdesk_message 10 IMP
    am_helpdesk_snippet 1 IMP
    am_helpdesk_ticket 6 IMP
    am_invoice 4,704 IMP
    am_invoice_item 4,704 IMP
    am_invoice_log 2,632 IMP
    am_invoice_payment 3,868 IMP
    am_invoice_refund 8 IMP
    am_link 1 IMP
    am_mail_queue 168 IMP
    am_newsletter_list 1 IMP
    am_newsletter_user_subscription 3,759 IMP
    am_product 3 IMP
    am_product_category 1 IMP
    am_product_product_category 2 IMP
    am_resource_access 5 IMP
    am_resource_access_sort 33 IMP
    am_saved_form 4 IMP
    am_saved_pass 7,942 IMP
    am_saved_report 4 IMP
    am_saved_search 3 IMP
    am_user 3,920 IMP
    am_user_group 1 IMP
    am_user_status 3,761 IMP
    am_user_user_group 9 IMP

    I then went through the configuration steps as the Amember Installation setup wizard suggested, making the config look like it had looked on the old host.
    The user data displays in the member list browser in Amember Admin, and I tested adding a brand new user via Paypal. That worked. hurray.
    But I noticed that almost all listed user records have a status of "Future".
    Four are "Pending" and the only single "Active" is the brand new test user we signed up via Paypal tonight.
    Why are all the other records in status "Future"?
    I could not find any definitive help searching through documentation.
    Most of the user records should actually be in the "Expired" state, and about 600 should be in the "Active" state. As per the true status of those in the old yet still live 4.2 database on the old hoster.
    I did a Rebuild Database and Rebuild Invoices, but that did not help.

    Any thoughts on why they are in "Future" status and
    How I can get them to be their proper Active or Expired status?
    I can clear the db and reimport if needed, but I would probably need to do something differently.
    Is there some step that I have missed or do not know about (obviously ;-)
    Thanks for any help.
  10. thehpmc

    thehpmc Member

    Joined:
    Aug 24, 2006
    Messages:
    901
    In your situation I think I would have downloaded, from existing server, your current, working, aMember installation and database to local computer and then uploaded same to new server.

    Get the, old version of aMember, working on new server.

    Once working upgrade that installation to latest version 4.7 as part of the upgrade aMember would upgrade database to latest version automatically including adding any additional tables required.
  11. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    Thanks for the reply Thehpmc,
    The major reason we resist doing that is that the old yet working installation was modified in unknown (and unsavory) ways by some folks no longer involved in the project. That and when I have run the files through my PC, as part of moving the site, a number of files were stripped out of the collection by my antivirus program, with virus/mal/unsafe files alerts popping up. So we -- the management team and I can not and do not trust the original installation.
    Thus we are trying to start with a clean copy of AMember from the new 4.7 install and setup.

    I had asked Tech Support for a set of installation files for AMember Pro for ver 4.2.15 -- the version our live site is running -- so that I could have tried something close to your and Carsars suggested path.
    But Tech Support has so far ignored or declined to provide me with such. So the only release version I can get and install is the latest 4.7.x. Thus the only good clean trustworthy copy of AMember I have is the latest version.

    Any idea on why member records were changed from their individual "Active" or "Expired" status to "Future"?

    And does anyone know what a status of "Future" means? I have not found a definition or explanation on the knowledge base.
    Thanks.
  12. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    2,295
    We are happy to give you clean 4.2.15 version if you need it. Did you submit ticket with such request? What is ticket number?

    Then you can make diff between your version and clean one to find exact changes that was done by previous developer.
  13. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    Hi Caesar.
    Yes I think it could help us to get a clean installation set for version 4.2.15.
    My Tech Support Ticket is Reference: DRI-23471-249
    (Though , it is a very long thread. )

    Please provide a link for a clean installer for 4.2.15.
    You can either send to my email (on file with customer support) or however seems best to you all.
    Thank you.
  14. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    2,295
    I build 4.2.15 and uploaded archive with it to your server. You can find details in your ticket in helpdesk.
  15. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    Thank you Caesar. I will be using that over the next day or so to test my upgrade procedure.

    An additional question -- to anyone.
    As part of my testing process, I created a new user with a real Paypal payment.
    Is there a way to export that newly created user record, so that I could import it back into the database after I go through my next round of testing, which involves clean out the db and restore it to its previous snapshot content (which would not have the new user record)?
    I would assume the new user has data in several of the tables - access, invoice, etc.
    Thanks.
  16. thehpmc

    thehpmc Member

    Joined:
    Aug 24, 2006
    Messages:
    901
    Why not just backup the database with just this user to your local computer, 'play' with new version of aMember as much as you want then finally restore the backup to the server?
  17. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    HI Thehpmc,
    Yes, that is what I would like to be doing:
    Take just the record for the user I created in my testing 4.7 db, save or export that users record, and then when I 'blow away' (empty, purge) the database, and reload it from a prior snapshot, I could then import or restore just that one user's record. That is, merge it into the restored database.
    Currently, I do not know enough to know what command in Amember or PHPMyAdmin to do that.
    I know how to export entire db, export db in a selective tables fashion, but that copies all data for all users in the db or the table.
    Can you suggest a particular command which would save just one user ID 3972 data and payment record?
    Thanks
  18. thehpmc

    thehpmc Member

    Joined:
    Aug 24, 2006
    Messages:
    901
    I would have backed up the database with the one user (or renamed it on the server) and then played with the new aMember version.

    When you wanted to go back to the database with one user I would have deleted the database I had used for testing purposes and restored database with the single user.

    Other method I have used in the past was to rename database on server, create a new database and then run aMember config to use this new, temporary, database. When finished testing again switched back to database containing single user.

    I could come up with a command that should save a single users complete record BUT, and this is the problem, it would be untested so cannot be guaranteed to work for all the users records. You might also need to transfer product details that the user was subscribed to. Would need to think about that one.

    The command would be better suggested by aMember support.
  19. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    Thanks for the thoughts, Thehpmc.

    I have been doing something similar, I have four database 'shells' that I have been clearing and reloading, importing and merging so as to test my migration attempts. But re the one recently created real purchase record, It was made in a database filled with lots of other older records, thus a bit harder to extract just that one user and transaction.
    I was apparently too eager and optimistic that it would all be working well in that particular migrate/upgrade attempt ...
    Re trying to pull that one record set out for later restore, I even made a list of fields in the tables to see which tables I might need to pick, to do a single record only extract from the relevant ones. But it is a bit of a chore.
    I agree that Support may have the answer, but my support ticket primary contact does not like to provide such detail. So I have abandoned the idea of salvaging that one payment record and just start over with next round of testing.
    Still, thanks for the reply and suggestion, I appreciate it.
  20. brucetech3

    brucetech3 aMember Pro Customer

    Joined:
    Jun 1, 2014
    Messages:
    33
    Hi Caesar,
    a little further detail, just to keep the records straight, (partly also because the frustrated management team I answer to wants these details known... )
    I first asked for a set of v4.2.15 installation files about 41 days ago, but that request was ignored and not responded to by Anton, who has been the primary person we've been dealing with in Tech Support.
    Ticket Reference: DRI-23471-249
    I wrote: "If you think it would go smoother for me to install the comparable older Amember v4.2.15 on our new Sustainable web site, and then to upgrade it to v4.7.0, that would be OK by me. Just provide a link that I can use to get the 4.2.15 files. ... Friday, January 8, 2016 1:45 AM"

    Again, thanks for getting the v4.2.15 installer files made available to us. That's the path I'll be working on now that the license issue seems cleared. Onward...

Share This Page