MySQL is one of the most popular DataBase Management Softwares (DBMS), it is used for storing, manipulating, and managing data in a database using SQL queries. MySQL is typically used for web application development, so it has a large community of developers who can help and answer questions. The major advantages of MySQL are that it is easy to use, inexpensive, reliable ( since 1995) and has a high performance and speed data processing that help enterprise users build scalable database applications. This tutorial is a step-by-step guide on how to add MySQL to the path in your Mac OS system for local development.
Step1: Download and Install MySQL server
Now, scroll down then click on MySQL Community (GPL) Downloads:
One more time, select and click on Mysql Community Server
After that the following page will appear, now you can select your operating system, in our case is Mac OS, note that the version of your OS X will be detected automatically . Make sure to download the latest version of MySQL Community Server because new releases have recent bug fixes and features.
To skip the login and sign up step click on No thanks, just start my download And that will start your download.
When the download is done, open it in finder and double click on MySQL install package you have just downloaded and start the installation process.
Then, enter you own computer device password
The next step will ask you to enter your MySQL password that you want to use for the ‘root’ user and click Finish, note that you can change this password later.
You may get a warning similar to this “macOS cannot verify that this app is free from malware”. To fix that, just go to System Preferences > Security & Privacy and click on the “Open Anyway” button, This will allow you to install MySQL in your system.
Once the MySQL installation is completed, go to System Preferences and click on the MySQL icon on the bottom left position.
Now you can see that MySQL Server is running by default but you if you want to stop running it, you just click on Stop MySQL Server.
Step2: add MySQL to the path in mac OS
The first thing to do is to define a path to our MySQL Server, for that we can add the path in the .bash_profile file OR in the /etc/paths file.
Add MySQL path in .bash_profile
First open your terminal and type this command to open the .bash_profile
open -t .bash_profile
If .bash_profile doesn’t exist in your system files you can create one by typing this command
Then add this code to your .bash_profile file:
Command + S to save the change that you just made to the .bash_profile file and that is enough to add MySQL to your paths.
Add MySQL path in /etc/paths
In this method you need to type the following command in your Terminal
If a permission error appears add “sudo” at the begging of your command.
sudo nano /etc/paths
Then add this two lines of code to you /etc/paths file
Command + S to save the changes, then you have to restart your Terminal.
MySQL Terminal Commands
When this done you can verify the status of your MySQL Server by typing:
sudo mysql.server status
To start your mysql server run the command :
sudo mysql.server start
To stop the server use this command:
sudo mysql.server stop
In order to access the root of your database and MySQL Server type the command:
sudo mysql -u root -p
Enter your device password then your MySQL password that we created earlier. A “mysql>” console will appear in your Terminal, that means that you can now interact directly with your database.
To get a list of all MySQL commands use the command help. To use this commands make sure to start line with the command_name and finish it with ‘;’.
The first thing we need to do is to create a new user rather than the root user. For that we type the command :
mysql> CREATE USER ‘username’@‘localhost’ IDENTIFIED BY ‘userpassword’;
To see all the users we just use the command:
mysql> SELECT user FROM mysql.user;
To give the new user ‘username’ the privileges of an admin to have the full access when interacting with MySQL database:
- First, type this:
mysql> GRANT ALL PRIVILEGES ON * . * TO ‘username’@‘localhost’;
- Then you need to flush all the privileges :
mysql> FLUSH PRIVILEGES;
- To check the privileges use:
mysql> SHOW GRANTS FOR ‘username’@‘localhost’;
- The command to delete a user is:
mysql> DROP USER 'username'@'localhost';
- Create database
mysql> CREATE DATABASE databasename;
- Show all the databases:
mysql> SHOW DATABASES;
- In order to create a table, we need first to select a database ‘databaseName’ then create the new table ‘Newtable’ :
mysql> USE databaseName;
mysql> CREATE TABLE Newtable ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, position varchar(255) NOT NULL, wage INT NOT NULL, 'password` varchar(255) NOT NULL, 'PRIMARY KEY (id), is_current_employee INT DEFAULT 1);
- To show all the tables in our database :
mysql> SHOW TABLES;
- If you want to delete a table or database use the command DROP with the name of table or database that you want to delete:
mysql> DROP DATABASE databaseName;
mysql> DROP TABLE tableName;
- To quit mysql use the command quit or exit.