Isolated MySQL Environment (Running MySQL Locally without using Global OS Resources like Sockets) #mysql #sql

isolated_mysql_environment.md

Isolated MySQL Environment

The advantage of this, is that you can work on multiple independent MySQL servers at the same time.

Further more you won't use global sockets, only filesystem unix domain sockets, which means you will not have to deal with port conflicts. Also this is much lower overhead than using something like Docker.

# first we create our isolated project directory
mkdir my_project
cd my_project

# within the directory we will create a hidden .mysql directory to serve as the data directory
# make sure to ignore this directory and its contents in your VCS
mkdir .mysql

# this command should only be used at the beginning or when you're restarting a completely new mysql server
mysqld --datadir="$(pwd)/.mysql" --initialize-insecure

# at this point we can launch the server, we skip networking so that we don't pollute the global namespace with a port usage
# almost all mysql clients can instead connect using the unix domain socket
mysqld --datadir="$(pwd)/.mysql" --socket="$(pwd)/.mysql/mysql.sock --skip-networking &

# connect to mysqld using the unix domain socket, this completely negates any conflicting port issues!
mysql --socket='./.mysql/mysql.sock' &

# shutdown with the same way
mysqladmin --socket='./.mysql/mysql.sock' shutdown

It is possible to create a project specific .my.cnf, and use the --defaults-file or --defaults-extra-file option for mysqld, mysql and mysqladmin. This way you can make sure to enable things like the event scheduler. If you use --defaults-file, mysql will ignore any other setting files, while the --defaults-extra-file can be used to append extra settings defined in system and user profile. Because the --defaults-extra-file does not override existing settings, using it may not work if there are existing settings that set the user name and socket location. Therefore the only foolproof solution is to use --defaults-file Doing this can allow you to avoid needing to always set the socket option on your commands, and instead you can just alias mysqld, mysql and mysqladmin within your project to always use a relative .my.cnf.

The only problem with using a local .my.cnf, is that you cannot set a relative path for the socket and the datadir options for the [mysqld]. You can get around this by using a bash script to embed the config file using heredoc syntax. Or just use the above shell commands instead.

Note that SSH can do socket forwarding with unix domain sockets, so you can even use this method over the network!