What is the better way for selected query logging?

MySQL database
#1

Hi.

First: I’m not a professional coder, i only play a bit with php/mysql.

What i want to do:
I have a dev server and a prod server. There i have to update the content of some tables many times from dev to the prod server, so they are identical.
I know, there are ways to setup master/slave satabases mirrored, but i have my own basic idea about the way, how to update all. I want to record the queries who modify the content of needed dev-server tables and want to provide them to the prod-server(s) in a secured way.

Question:
What would be the better method:

  • Explore the entire scripts and store the wanted query types in a own ‘todo’ db or file.
  • OR use the general logging of mysql itself in the mysql table and then filter out the required statements.

I think the 1st would be the better way because the mysql general logging itself have a way bigger impact as the logging of the extra selected statements.

  • Why i dont wanna use master/slave databases:
    … i finally never got figured it out, how it works in detail and dont know something about security risks of this solution.
    … i dont want have a permanent open extra-connect and dont want have any open sql connects at the servers for security reasons.

Anyone have some suggestions to it?

  • ThanYou -
#2

This wouldn’t be a master-slave problem regardless. You must be storing content and not actual data to need to migrate anything from dev to prod as well, because this is normally frowned upon as well, content and configuration being the only reason that the two would need to be synced. But this is what you need…

https://dev.mysql.com/doc/workbench/en/wb-migration-overview-steps.html

#3

I not only need to ‘storing’ content, this i simply would transfer in secured json strings.
I need to modify and delete contents too.

I need to sync the content of 3 Tables, yes. (Tables & structures dont need to be modded.)

The link (dev.mysql) not really helps me, it looks way too expensive for the things i need. (to expensive work in setup, config, managing it, and so on … ) And i dont want / have to compare the entire database permanently, i only have to “update” the ‘prod’ database with things, who was added, edited or deleted at dev-database. :wink:

#4

That’s exactly what that does… It does comparison then handles the updates.

#5

I watched it again more intensive (the link) and still didnt got it, why i should use this expensive GUI things. Looks like, if i had to go to scool for 10 years to understand the usage and behaviours of it. (The sample there also dont match my requirements, i have debian servers and not any windows releated things nor a GUI interface at the servers.)

What i try to do needs around 25 to 50 lines of codes and some serverconfig (In theory) to get the same result directly in a safe way and faster and with way less preformance-consuming.

– I dont know if i dont complete understand waht you mean or i didnt explain in the right way, what i wanted to do.

#6

You install MySQL workbench to your local computer, not the server.
You connect it to the databases, running on the servers.
You compare them to find the differences.
You tell the program to update what you specify based on what the changes found are.

I did this every deployment for a fortune 100 company, it really isn’t that hard…

#7

Okay, i’ve got it the right way.

MayBe for others its a solution, but for my problem and usage its one of the worst solutions, it could be.

I need updates to ‘slave’ sometimes JIT.

  • What i do, when i’m not at home and the workstation is off?
  • What i do if my internetconnection fails?
  • What i do, if windows or any other program needs to update and block the action of the tool?
  • What i do when windows makes a autoreboot after win updates?
  • What i do when windows fails?
    … and so on …

The other i dont wanna have are open sql connections at the servers (and i really know why ^^)
I have a hiden crypted tunnel with a own socket between the dev and prod server, so no mim or other attacks are possible. I want to use them.

I try to figure out, what is the best way to transfer the update informations for the database from dev to prod server.

  • At the prod server the database will not be modifyed, there will only be readed from (SELECT FROM WHERE ORDER BY LIMIT … and so on …)
#8

Okay, i choosed the method to give the prod servers the content updated infos JIT, when the master will be have updates. Ans it works perfect, more then 10 updates per second are no problem at this system now. :slight_smile:

… can be closed …

Sponsor our Newsletter | Privacy Policy | Terms of Service