Install & Configure MySQL on Mac

Installing MySQL with Homebrew

The first step is to install MySQL using Homebrew, a popular package manager for macOS. Open terminal and run the following command:

brew install mysql

This will download and install MySQL on your Mac.

Starting the MySQL Service

Once installed, start the MySQL service by running the following command:

brew services start mysql

This will start the MySQL server in the background.

Configure MySQL Database

Now that we have MySQL up and running, let's configure it to create a new database and user. We'll use the following commands:

• Delete Anonymous Users:

MYSQL_ROOT_PWD='S1r0ngP@ssw0rd'
DEMOUSER_PWD='P@ssword1234'

mysql -u root -e "DELETE FROM mysql.user WHERE User=''"

• Delete Non-local Connections for Root Accounts:

mysql -u root -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')"

• Delete any Test Database:

mysql -u root -e "DROP DATABASE IF EXISTS test"
mysql -u root -e "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'"

• Change the Root Password:

mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '"${MYSQL_ROOT_PWD}"'"

• Create a New Database:

mysql -u root -p${MYSQL_ROOT_PWD} -e "CREATE DATABASE demo DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci"
mysql -u root -p${MYSQL_ROOT_PWD} -e "SHOW DATABASES"

• Create a New Database User:

# Note - This user can only access from the localhost
mysql -u root -p${MYSQL_ROOT_PWD} -e "CREATE USER 'demouser'@'localhost' IDENTIFIED BY '"${DEMOUSER_PWD}"'"
mysql -u root -p${MYSQL_ROOT_PWD} -e "SELECT user, host FROM mysql.user"

• Grant Database Access:

mysql -u root -p${MYSQL_ROOT_PWD} -e "GRANT ALL PRIVILEGES ON demo.* TO demouser@'localhost'"

• Flush Privileges:

mysql -u root -p${MYSQL_ROOT_PWD} -e "FLUSH PRIVILEGES"

Testing MySQL Database

Finally, let's test our new database by creating a table and inserting some data:

# Create Table
mysql -u demouser -p${DEMOUSER_PWD} demo -e "CREATE TABLE dept (deptno NUMERIC PRIMARY KEY, dname VARCHAR(30))"
mysql -u demouser -p${DEMOUSER_PWD} demo -e "SHOW TABLES"

That's it! You now have a fully functional MySQL setup on your Mac, complete with a new database and user. With this guide, you're ready to start building your web applications using MySQL as your database management system.