I. Preface

1. The main contents of this course

• apply Homebrew Install MySQL
• MySQL 8.0 Foundation for Configuration
• MySQL shell management common grammar examples (users, permissions, etc.)
• MySQL character encoding configuration
• MySQL Remote Access Configuration

2. Environmental Information and Scope of Application of this Course

• environmental information

Software/environment	Version/description
macOS	macOS High Sierra
MySQL	MySQL 8.0.12

• Scope of application

Software	Edition
macOS	macOS
MySQL	8.0.x

MySQL Installation

1. Homebrew Installation

Homebrew under macOS is equivalent to yum under CentOS or apt-get under Ubuntu.

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

2. Homebrew Installs and Starts MySQL Services

• Install mysql

brew install mysql

• Configure and start MySQL service

brew tap homebrew/services
brew services start mysql

3. Modify root password

mysqladmin -u root password 'yourpassword'

4. MySQL Installation Test

• View the MySQL version

#View the MySQL version
mysql -V

#Output example
mysql  Ver 8.0.12 for osx10.13 on x86_64 (Homebrew)

• MySQL shell test

#Enter MySQL shell
mysql -u root -p

#Successful entry will output the following information
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.12 Homebrew

#view the database
mysql> show databases;

#Sign out
mysql> exit;

3. MySQL Security Settings

1. Introduction to MySQL 8 Security Settings

MySQL 8 adds a new security setup wizard, which simplifies the operation of security settings for MySQL server deployment. It's great, but for macOS, it's not just needed. If you're not interested, you can skip this chapter directly.

Security settings are roughly divided into the following steps/options

1. Password Strength Verification Plug-in
2. Modify root account password
3. Remove anonymous users
4. Disable root account remote login
5. Remove the test database
6. Reload Authorization Table

The above steps/options can be done according to your needs.

2. MySQL 8 Security Settings Example

• Enter Security Settings

mysql_secure_installation

- Setting examples

Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: no
#Here I chose the insecure password strength verification plug-in.

Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no
#Here I chose not to change the root password.
 ... skipping.

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : yes
Success.
#Here I chose to remove anonymous users


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : yes
Success.
#Here I chose to disable root account remote login access.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : no
 ... skipping.
 #Here I chose not to remove the test database

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : yes
Success.
#Here I chose to reload the permission table because I chose to disable remote login access to root account earlier.

All done!

4. MySQL shell management grammar example

1. Examples of database-related grammar

#Create a database
mysql> CREATE DATABASE mydb;

#View all databases
mysql> SHOW DATABASES;

#Use data and create tables
mysql> USE mydb;
mysql> CREATE TABLE test(id int,body varchar(100));

#View table
mysql> SHOW TABLES;

2. Examples of User and Access Authorization Syntax

#New Local Users
mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

#New remote user
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';

#Give the specified account the specified database remote access rights
mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'test'@'%';

#Give the specified account remote access to all databases
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

#Give the specified account local access to all databases
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost';

#Refresh authority
mysql> FLUSH PRIVILEGES;

3. Examples of authorization-related grammar

#1. View permissions
SHOW GRANTS FOR 'test'@'%';

#2. Authorization
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

#3. Recovery of authority
REVOKE ALL PRIVILEGES ON *.* FROM 'test'@'%';

#4. Refresh permission
FLUSH PRIVILEGES;

#5. Delete Users
DROP USER 'test'@'localhost';

V. Character Encoding Configuration

MySQL's default encoding is not utf8. In order to be compatible with Chinese storage, you need to configure it.

1. Modifying character encoding

#Modify configuration files
vi /usr/local/etc/my.cnf

#Modification 1: Add client configuration (before [mysqld] at the beginning of the file)
[client]
default-character-set=utf8mb4

#Modification 2: Increase the mysqld configuration (after [mysqld] at the end of the file)
#charset
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

2. Restart Effective

• Restart MySQL Service

mysql.server restart
#You can also use the command: brew services restart mysql
#However, it is recommended to use the command: mysql.server restart can see more accurate and complete information in case of an error

• View character encoding

#Enter MySQL shell
mysql -u root -p

#View character encoding
mysql>  show variables like '%char%';

6. Remote Access Configuration

MySQL binds ip: 127.0.0.1 by default. If we need remote access, remove the configuration.

1. Modifying ip binding

#Modify configuration files
vi /usr/local/etc/my.cnf

#Comment out the ip-address option
[mysqld]
# Only allow connections from localhost
#bind-address = 127.0.0.1

2. Restart Effective

• Restart MySQL Service

mysql.server restart

Seven, remarks

Related reading

• utf8 in MySQL

http://www.infoq.com/cn/articles/in-mysql-never-use-utf8-use-utf8

• MySQL Remote Access and Bid-address Problem

https://serverfault.com/questions/139323/how-to-bind-mysql-server-to-more-than-one-ip-address

 

This article was first published on my independent blog: https://ken.io/note/macos-mysql8-install-config-tutorial