Install MySQL in MacOS with Brew | Run Multiple MySQL Version with Separate Database

Scenario

Note : If your need match one of following criteria you can read the article, if not then, you can skip the article to save your time.
Thanks

You are new in MacOS – need to install MySQL
You have knowledge – but need to upgrade MySQL version
You need to install Multiple MySQL version
You need to Install Multiple MySQL version in keep database in separate
You need to switch between different MySQL version

All of the Scenario can be solved by two method

Using Software
Using Homebrew (If you like to Brew Very Much & some Learning Purpose)

Using Software to Solved all the Scenario

A software called DBngin by TablePlus where you can install mysql and run by MySQL with a good UI.

You can install single MySQL version
You can install multiple MySQL version & data saved separately.
You can switch Between differnent MySQL version by stop & stop

Create Database on the Software :

On the top right corner a plus button click will prompt the window where you can create MySQL database.
I already create a MySQL 5.7 with port 3306 so i create the new one with 3310 for MySQL 8, if you don’t want to run both same time then just use 3306

To use this software you need to know following things

Any dependancy to use this software?
You need to install table plus – https://tableplus.com. It’s not dependency but can help you.
It is free?
You completely free.
What is the username & password of DB after start first time?
Username: root
Password: empty (no password needed just hit enter if ask password)
Host: 127.0.0.1
Note : But recommended to change password later
Does the mysql start on start up?
No. it’s a drawback. overtime you logged in you need to start the mysql server, from visual button.

Possible Issue Found.

mysql -V or mysql command line not working

To open the command line. On server arrow here a option Export Environment Variable to Terminal Option. Now a terminal open and you can use mysql cli. If you face issue Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) read the next part.

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

Issue : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

Solution :
Why this happen?
We can create many MySQL versions in DBngin, so it’s not possible to use the same socket path for all versions. The current solution is add the port suffix to the local socket.

Go to /tmp folder by cd /tmp & ls -la command, you will found list of socket service run.

Can't connect to local MySQL server through socket '/tmp/mysql.sock' Can't connect to local MySQL server through socket '/tmp/mysql.sock' — With DBngin Running

If you run any Brew mysql version then Go to /tmp folder by cd /tmp & ls -la command, you will found list of socket service running like this.

Note : Both running in different way, one in port specific one in default.
here on homebrew mysql if this issue arrive mean (Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)) that means problem with connection the mysql server and just type
mysql.server start (If you default brew mysql)
/usr/local/opt/mysql@5.7/bin/mysql.server start (if you use specific version)

If your mysql service working well try to connect with full path

#service running on port 3306
mysql --socket /tmp/mysql_3306.sock -uroot

#service running on port 3310
mysql --socket /tmp/mysql_3310.sock -uroot

Details Official Git Issue with this here

mysqli::real_connect(): (HY000/2002): No such file or directory

If running well but if you still found error to connect software like wordpress, magento, laravel and phpmyadmin and otheres.
Please do use 127.0.0.1 instead of localhost as database host.

Note : Following Scenario we will cover with the software

You need to install Multiple MySQL version
You need to Install Multiple MySQL version in keep database in separate
You need to switch between different MySQL version

For all those process i would recommend to use only software DBngin. Because, there are lot’s of possibility of the following technology get changed like, brew, mysql. So the multiple install process get legecy but if we use a realiable software then it will do the update job, they will adopt the technology of changes, we can focus on coding things.

Now the Home Brew Method to Install MySQL

Some Necessary Command :

brew services list – list of running brew services
brew search mysql – list of mysql version and tools in brew
brew install mysql – to install latest MySQL version (currently 8 is latest)
brew install mysql@5.7 – to install specific MySQL version
brew link mysql or brew link mysql@5.7 – creates a symlink from packages installed in Cellar. (regular packages installed with homebrew will already be linked).
brew unlink mysql or brew unlink mysql@5.7 – remove symlink from Cellar. If you care using multiple version of a Formulae
brew info mysql – check details info of specific mysql version
brew services start mysql – start mysql brew service
brew services stop mysql – stop mysql brew services
mysql.server start – with background service start
mysql.server stop – stop the mysql service, this will not stop brew services

Install MySQL from Home brew

brew install mysql

Final Output : you will get this type of output in terminal. Don’t worry about warning, i am using MacOS beta version, that’s why error showing.

Completing the Setting of the Database :

If you read the details you will found there is no password is assign for your MySQL. You can follow two way to set the password

Method 1: mysql_secure_installation

mysql_secure_installation

Enter password for user root: >> give your desire password
Would you like to setup VALIDATE PASSWORD component? >> Y
Change the password for root ? >> N (as you already assign password)
Remove anonymous users? (Press y|Y for Yes, any other key for No) : >> Y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : >> Y (as you are using in local environment so don’t need to allow remote login)
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : >> Y (optional, if we want to test DB then give n, recommend Y)
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : >> Y

Validating the MySQL password changes work :

mysql -u root -p

It will ask for password give the same password you choose in first mysql_secure_installation time. If mysql> show then okay.
Check mysql -V to check the command line mysql is okay or not.
Note : if mysql -V not working : then
brew link mysql –force (for default)
brew link mysql@5.7 –force (for specific version)

Method 2: Changing MySQL password

At first time after installation complete no mysql password setup, so that time.

mysql -uroot
use mysql;
update user set authentication_string=password('your_password') where user='root';
update user set plugin="mysql_native_password";
FLUSH PRIVILEGES;

mysql -u root -p
>> Enter your current password
use mysql;
update user set authentication_string=password(‘your_password’) where user=’root’;
update user set plugin=”mysql_native_password”;
FLUSH PRIVILEGES;
If you want to change MySQL password you can follow same procedure.

For MySQL 8 it will be like this

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
FLUSH PRIVILEGES;

All MySQL location- configuration, Database & files

Default MySQL location will be — /usr/local/Cellar/mysql/
Version Specific MySQL location will be — /usr/local/Cellar/mysql@5.7/
Database Files – /usr/local/var/mysql
mysql –help : you will found all config and path in running MySQL
mysql –help | grep cnf : specifically this will found path list order
my.cnf order : /etc/my.cnf >>/etc/mysql/my.cnf >> /usr/local/etc/my.cnf >> ~/.my.cnf
brew bin directory is /usr/local/bin/ and mysql /usr/local/bin/mysql
it just the symlink of /usr/local/Cellar/mysql/mysql_version

Important Part : Configure MySQL Start/Stop or configure startup MySQL start

If you want MySQL to run on startup
brew services start mysql (for latest version)
brew services start mysql@5.7 (for specific latest version)
Now to Start MySQL server type
mysql.server start (If you default one)
/usr/local/opt/mysql@5.7/bin/mysql.server start (if you use specific version)
MySQL Server Start

Upgrade MySQL Version

To upgrade all package use this command

brew upgrade

To Upgrade specific use

brew upgrade mysql

Before upgrade by following command you can check the which package is upgradeable

brew outdated

Remove the MySQL Fully

Remove MySQL Fully

But if you still interested to install Multiple Mysql with Brew then you can give a look of this Article