Configuring MySQL and Squel Pro on Mac

In response to the popularity of nodejs, we have recently used it to implement some server-side functions. For the database, we chose MySQL, which is suitable for both young and old. There will definitely be related application needs in the future. This article records how to install and configure MySQL and its management tool Squel Pro on Mac.

Why choose MYSQL:

There are many databases, why did I choose MySQL?

Relational Database or NoSQL
NoSQL has become very popular in recent years, and the best one among them, mongoDB, is particularly convenient and easy to use. It is probably not possible to discuss the details of the advantages and disadvantages of the two in one article. Here is a summary:
Projects suitable for SQL development:
The need for logically related discrete data can be defined in advance. Data consistency is essential. Standard, mature technologies with good developer experience and technical support are suitable for projects developed using NoSQL:
Unrelated, uncertain, and evolving data requirements Simpler or looser projects that can start programming quickly Speed ​​and scalability are critical Because our requirements are relatively clear, the relationships are relatively fixed, and the business volume is not large, the speed requirement is not high. On the contrary, the logic requirements are more stringent, so the traditional relational database is chosen.
Once it is decided to use a relational database, this problem is easier to solve, and simply following the trend and using MySQL seems to be the choice of most people. I personally summarize the reasons as follows:
Open source and free.
Mainstream, fast, and adequate performance.
There are many users and the community is active.
There are many supporting tools, which are simple and easy to use.

Install MySQL

1. Homebrew

You can choose to download and install it from the official website, but the command line is much more convenient after all. If you don't have homebrew, please open Terminal and use the following command to install it.

# Install homebrew
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
# Verify that the installation is correct brew doctor

Install MySQL

brew install mysql

At this point, notice the prompt as follows, so start the service first, and then run the relevant commands:

We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation

Start MySQL Service

mysql.server start

Follow the prompts for mysql_secure_installation and set your username and password

mysql_secure_installation

Here you can follow the prompts step by step to set up, mainly including selecting the password strength, setting the password, confirming the password, whether to delete the passwordless user, whether to allow remote root login, and deleting the built-in test database.

Log in to test it

mysql -u root -p

MySQL Basic Commands

Although as FE, we finally chose the graphical management tool Squel Pro, but some basic commands still need to be mastered. There are still many application scenarios, for example, just want to do a quick check, or on someone else's computer, or when logging into the database remotely.

1. Basic commands

# Check which databases are available show databases;
# Check which database is currently being used select database();
# Select database use [database-name];
# Display the tables in the database
show tables;
# Create a database CREATE DATABASE [new-database-name];

Author: Scrap the Pillar Link: http://www.jianshu.com/p/2fab19d96eb8
Source: The copyright of Jianshu belongs to the author. For commercial reproduction, please contact the author for authorization. For non-commercial reproduction, please indicate the source.

2. Create a new user.

# Create user nodejs for localhost and set the password to nodejs
create user 'nodejs'@'localhost' identified by 'nodejs';
# Synchronize user permission information from the data table to the memory (this command can avoid restarting the MySQL service)
FLUSH PRIVILEGES;

Author: Scrap the Pillar Link: http://www.jianshu.com/p/2fab19d96eb8
Source: The copyright of Jianshu belongs to the author. For commercial reproduction, please contact the author for authorization. For non-commercial reproduction, please indicate the source.

3. Grant permissions.

Next, we create a database named nodejs for the current application and grant all its permissions to user nodejs.

CREATE DATABASE nodejs; 
GRANT ALL PRIVILEGES ON nodejs.* TO 'nodejs'@'%' IDENTIFIED BY 'nodejs';

Sequel Pro

Sequel Pro

After logging in, the software interface is as shown above. You can simply see the following functions:

Select the database in the upper left corner to manage the database

TABLES on the left column can manage data tables

The main area in the middle can manage the data in the data table.

In the upper right corner, click Users to manage users.

Console in the upper right corner allows you to easily run MySQL commands.

Install

Download the installation package from the official website and run it.

Log in

The interface is as follows. Enter Host , Username , and Password to log in. It is recommended to use the newly created nodejs user to log in and manage the nodejs database. Except for creating new users and granting permissions to new users, it is not recommended to use the root user for security reasons.

use

After logging in, click the upper right corner, you can see information such as selecting a database, creating a new database, etc., and then you can start viewing and managing the database. Since the graphical interface of the tool itself focuses on ease of use, I will not go into details here.