The most common dilemma we face today in production is with servers running
older versions of Mysql and if we need to upgrade to a relatively latest version what will happen to clients who use the older version of Mysql and their applications. Will they stop working? Do they need to upgrade their applications related to the database as well? What about who is reluctant to upgrade?
No problem we can have two different instances of Mysql running on the same server residing together but with different configurations and entirely different parameters that will avoid any major conflicts. We can either use the same binary files or different
binary files for different instances.
Below are the conditions which are mandatory while running two Mysql instances on the same machine.
–> Different data directories (datadir) path to store the data
–> Different port numbers to control the connections
–> Different pid file name from where the process is written
–> Handling of entire logs and related bin files (general_log_file, log-bin, slow_query_log_file, log-error)
–> Path of tmpdir mention to handle the server load
This cannot be achieved by default installation procedure and there is an alternative to have this achieved using Docker where it runs a different version of Mysql in its own container within the server with container’s port.
Hope this was helpful. Take a moment to check: SQL Server error 906 Troubleshooting