How to install MySQL on Mac and allow remote access

MySQL is an open source database management system. It is used to implement databases. For any website or any other application, there must be some database required. This database is a type of server. You can deploy a database and connect it to the server. It will help to extract data from the database using quarries. It is the same operation that we do in the SQL query language. We can add, access, format data in MySQL table.

Feature:

It is open source and easy to download and install.
It is a very fast database language.
Implementing a database and using it is very simple.
It can be installed on any operating system.

There are several ways to install MysQL on MacOs, here we bring you two with their respective videos, you can install it using the installer that you can download from the official website, although the most recommended way is to use a package manager like Homebrew, which is the one we are going to use. explain in this entry.

We are going to learn how to install MySQL manually on a Mac using Homebrew, which will be a fairly simple process with just a few commands; These steps work for you if you use a Mac with Intel or M1.

After installing Homebrew, open your terminal and run the following commands:

Step 1: Install MySQL with Brew

brew install mysql

With this service we install MySQL with brew; It's that easy, now we have to start the service:

Start your mysql server

brew services start mysql

And with this we have the service started and ready to use it.

Two installation way

We have two configuration ways, the first, the simplest that would work for you in most cases:

Enter the mysql command prompt and modify your user information

mysql -u root -p

The second way would be the most secure way that allows us to establish a password and username.

Step 1: Go through the mysql secure installation workflow

mysql_secure_installation

then enter your password

password: <insert your password>

Step 2 – Change the root user password (optional)

# login as mysql root user
mysql -uroot -p # Log in and then type your password

Step 3: Create a new user

CREATE USER 'user'@'localhost' IDENTIFIED BY 'userpassword';
GRANT ALL ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

If you want to create a user so that it can be used remotely:

Step 4: Create a remote user

CREATE USER 'user'@'%' IDENTIFIED BY 'yourpassword';
GRANT ALL ON *.* TO 'user'@'%';
FLUSH PRIVILEGES;

Step 5: edit my.cnf

sudo nano /usr/local/etc/my.cnf
Editar bind-address a 0.0.0.0
bind-address = 0.0.0.0 
mysqlx-bind-address = 127.0.0.1

Try to log in with your user and try remote communication with your IP address. In case it doesn't work, restart your PC.

Using Sequel Pro

Es posible que te de un error como el siguiente al intentar conectarte a tu base de datos desde tu sistema gestor de base de datos; por ejemplo Sequel Pro:

change your user info to handle this error MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

To do this, adapt the following query defining your username and password.

ALTER USER '<username>'@'<localhost>' IDENTIFIED WITH mysql_native_password BY '<your_password>'

username: root
localhost: localhost
your_password: ************

With this you can use your Sequel Pro; Another great GUI (graphical) software that we can use to interact with a MySQL database is TablePlus.

It comes with a free trial which is perfect for our use, because it is not time based, but instead limits the number of simultaneous connections you can make to the database. There are macOS, Windows and Linux versions.

10-10-2023 - Andrés Cruz

En español

Instalar MacOS

Develop with Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter

Andrés Cruz In Udemy