This is the 31st day of my participation in the August More Text Challenge

This is the first MySQL/MariaDB tutorial to explain how to install the latest version of MySQL 8.0 and how to configure the Workbench and MySQL command line.

I won’t go into MySQL. The latest version is 8.0.26.

MySQL was developed using C++.

Download the installation directly below, and configure the use!

download

Open dev.mysql.com and click on MySQL 8.0 in the upper left corner of the image below to enter the download page.

As shown below, select the system platform to install and click the following to enter the download and installation page

You can download the online or offline installation package as follows:

Since MySQL was acquired by Oracle, there is a screen that requires you to log in using an Oracle account when downloading MySQL. However, this is not required, so select “Start downloading only” below:

The installation

After downloading, double-click to run MySQL installer.

Installation type

As follows, the first step in the installation is to select the type of installation.

  • Developer default

Install the MySQL server and all the tools needed to develop MySQL.

  • Only the server

Only the MySQL server is installed. Select this type if the MySQL server is deployed but applications are not developed.

  • The client only

Only the client is installed, which is only used for connection management of the existing MySQL server.

  • all

Install all components and tools including MySQL server

  • The custom

You can customize what you install as needed

Keep the default here and install developer type installations.

Environmental requirements

The next step is to check the installation requirements.

Here are the two missing components. You can install as needed.

Since you won’t be using the above two development tools, click “Next” to install.

MySQL components

To install the MySQL component, click “Execute”

  1. MySQL Server: MySQL database Server, which is the core component of MySQL.
  2. MySQL Workbench: Graphical tool for managing MySQL.
  3. MySQL Shell: command line management tool for MySQL. In addition to SQL statements, it supports JavaScript and Python scripts and calls to the MySQL API.
  4. MySQL Router: A lightweight plug-in for routing and load balancing between applications and database servers.
  5. Connector/ODBC: ODBC driver for MySQL database.

ODBC — Open DatabaseConnectivity, Open database interconnection. Database access interface is a database access interface standard proposed by Microsoft. It is designed to be database and technology neutral. Provides a common data access layer.

ODBC requires access to the data source using RDBMS specific drivers.

Other components are used to develop connections, as well as official documentation and sample databases.

To install the software package online, download required components first, click Next, and then click Execute to install the software package.

As follows, wait for all installations to complete. And then click next

configuration

Configuring the Server

After the component is installed, you can configure the server Type, Connectivity, and Advanced Configuration.

  • Server Type

Development Computer, Sever Computer and Dedicated Computer

The difference between computer types is how much memory the MySQL database server takes up.

  • Development computer: the MySQL database service takes up the minimum memory required
  • Server computer: Assume that there are multiple MySQL database server instances and other server applications running on the computer, occupying moderate memory.
  • Dedicated computer: occupies all memory resources of the computer.

The type of memory allocation option is very similar to that used during Oracle installation.

Select Develop computer. If you are a production database server, you should select the other two.

  • The connection method

The connection modes of MySQL include TCP/IP, Named Pipe, and Shared Memory.

Named pipes and shared memory are fast, but can only be accessed from the native MySQL database server.

Select the default TCP/IP address for network access.

MySQL’s default TCP/IP access port is 3306; The default X port number is 33060. The X plug-in of MySQL uses the X protocol, mainly to realize the file storage service similar to MongoDB.

Here, click next:

Authentication configuration

Caching_sha2_password is the new encryption algorithm of SHA256 recommended by default. In the case of the same password, the new algorithm does not generate the same encryption result, which is more secure.

Password and user permissions

The following is to set the password of user ROOT. Root is the MySQL superuser and has the highest privileges.

Additional users can be added as needed.

Configure MySQL as a Windows service

It runs in the background as a Windows service and can be booted up.

Click Next to apply the configuration.

Production configuration

The production configuration is mainly routing and sample database configuration.

The Router is only available if the MySQL cluster is installed. Therefore, after clicking Next, click Finish to configure the Router.

Then, click Next to configure the sample database (primarily for learning or reference purposes). It is not usually necessary to install the sample database.

Select the MySQL Server that has just been installed. Enter the user password and click Check.

Click Next, apply the configuration, and Execute.

Complete the configuration as shown below, click Next, and complete the installation.

Workbench

Workbench is MySQL’s own graphical management tool.

Start Workbench from the Start menu (MySQL Workbench 8.0 CE under MySQL)

Once started, you can see that the lower left corner lists the local MySQL connection by default. MySQL > install MySQL; Click “+” to add a new connection.

Click open local area connection, enter your password and enter.

Here is a brief introduction to the Workbench:

MySQL > select * from ‘MySQL’;

SELECT @@VERSION;
select version();
show variables like '%version%';
Copy the code

MySQL is used on the command line

MySQL Shell

After the default installation, to use MySQL on the command line, you can only open MySQL Shell.

MySQL Shell is in JS mode by default.

By \help or \? View more parameters

  • through\sqlYou can switch to SQL mode.

  • \connector\cConnect to a MySQL.

Syntax for connection: \connect [user[:password]@]hostname[:port], for example: \c root@localhost.

The full syntax is: \connect [

]

.

is — MC, –mysql (connect to mysql, which can be omitted) or –mx, –mysqlx (connect using the X protocol).


  • Use \s in the MySQL command line to check the status of the MySQL server, including version, encoding, and so on.
 MySQL  localhost:33060+ ssl  SQL > \s
MySQL Shell version 8.026.

Connection Id:                21
Default schema:
Current schema:
Current user:                 root@localhost
SSL:                          Cipher in use: TLS_AES_256_GCM_SHA384 TLSv13.
Using delimiter:              ;
Server version:               8.026. MySQL Community Server - GPL
Protocol version:             X protocol
Client library:               8.026.
Connection:                   localhost via TCP/IP
TCP port:                     33060
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Enabled (DEFLATE_STREAM)
Uptime:                       1 hour 26 min 45.0000 sec

Copy the code

\s can only be used on the command line.

Use the MySQL command line in a global environment

After the default installation, you cannot connect to MySQL using tools such as CMD or Powershell.

‘MySQL’ is not recognized… A similar error.

Add MySQL Server PATH to PATH ‘environment variables’ :

Right-click “This computer” -> “Properties” -> “Advanced System Settings” and open “Environment Variables”.

After adding, click “OK” at the bottom, and then click OK:

At this point, you can reopen the command line tool and use the MySQL command to connect to the MySQL server.

Log in to user root using mysql -uroot -p.

Type exit to exit:

mysql> exit
Bye
Copy the code

In most cases, it’s easier to use the command line.