0

After a lot of resistance I have recently been given MySQL access to our CRM system, via a read-only slave.

I have an issue now in that I am really limited to what I can do with the data in MySQL - cannot create views etc. The IT head said I should continue to use Access, with the tables from the slave imported using ODBC connections. I can do this but I'm not sure it's the best plan long-term - I had planned to move my processes into MySQL.

Is there any way around this? Can I somehow link the read-only tables to another MySQL database which I can edit?

Thanks

1

I have an issue now in that I am really limited to what I can do with the data in MySQL - cannot create views etc.

The biggest issue in above is "etc". Any editing operation on Slave can crash the replication process, and Your head of IT just absolutely correct want avoid regular restore of server.

The possible ways:

  • create all views on Master. It safe. Views not add additional loading to server and will be automatically replicate to Salve
  • If You do not need real-time data (it normal practice) - go from replication to nightly restore few important tables. Operation could be automate easily
  • and as mentioned in Your post - continue use Access. May be not the best, but it very good tools for personal use.
  • In addition to Access - look for PowerBI (or similar tools) they could make many transformation in memory without creating views

It is possible to create views on Slave without collisions with replication, but ... but feature unpredictable and IT just try to avoid any potentials issues, and they are right.

  • Thanks, I had expected to be given a collection of the tables that were restored nightly, but I think this involved more work for IT (as the tables are very messy), so they have gone down the slave route. My plan now will be to determine the important tables myself and then push for a nightly restore – agwah Mar 21 '17 at 8:34
  • Hi, with regards to the second bullet point, what would an ok (maximum) number of tables and/or total download size to make a nightly restore a good option? – agwah Mar 23 '17 at 10:14
  • 1
    it is not correct question - all must go from business needs. All limited by - speed of network, hardware resources and etc. The are no standard limits. Some database we refresh for developers weekly, some nightly, some by request, and size 20-40Gb, but it not answer as well - for bigger size - need create normal warehouse and we have 2 as well, internal archive server and Redshift with incremental loads. – a_vlad Mar 23 '17 at 12:10

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.