MySQL learning notes

Posted by brotherhewd on Tue, 01 Mar 2022 02:43:21 +0100

catalogue

MySQL overview

MySQL installation and setting up WINDOWS service

MySQL installation

Set up Windows services for MySQL

MySQL creates and authorizes users

The notes are based on the teaching course "baldness saves the earth" in station B. Course link: 7-day MySQL devil training camp (beginner to expert)_ Beep beep beep_ bilibili

MySQL overview

Relational / non relational database: MySQL is a relational database, and each column of data has a certain constraint relationship

MySQL includes a server and a client. The server provides services so that users can use the database. Users operate the database on the client side.

MySQL installation and setting up WINDOWS service

MySQL installation

Here is the windows installation address of MySQL:

MySQL Downloadhttps://dev.mysql.com/downloads/installer/ After downloading, since MySQL does not have its own configuration file, you need to write the configuration file yourself. Here are the specific code contents:

[mysqld]
# Set 3306 port
port=3306
# Set mysql installation directory
basedir=E:\\software\\mysql\\mysql-8.0.11-winx64   #Try it yourself here. Some devices use a single slash
# Set the storage directory of mysql database data
datadir=E:\\software\\mysql\\mysql-8.0.11-winx64\\Data   # Ibid
# Maximum connections allowed
max_connections=200
# Number of connection failures allowed. This is to prevent someone from trying to attack the database system from the host
max_connect_errors=10
# The character set used by the server is UTF8 by default
character-set-server=uft8mb4
# The default storage engine that will be used when creating new tables
default-storage-engine=INNODB
# The "mysql_native_password" plug-in authentication is used by default
default_authentication_plugin=mysql_native_password
[mysql]
# Set the default character set of mysql client
default-character-set=utf8
[client]
# Set the default port used by mysql client when connecting to the server
port=3306
default-character-set=utf8

Modify and paste the above text into Notepad, and then rename the file "my.ini".

After installation, CMD must be started as an administrator

After startup, execute the command under the bin directory of MySQL installation directory:

mysqld --initialize --console

This is how to install MySQL. The following characters should appear after normal installation:

A temporary password is generated for root@localhost: 

After the colon is the password of the initial user

After remembering the password, enter the following command in the MySQL Directory:

mysqld --install

After that, you can start the MySQL service through the following command

net start mysql

You can also pass

net stop mysql

Termination of service

After the service runs, enter in the MySQL installation directory

mysql -u root -p

The login user's password is the original password. After login, use

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'New password'; 

After changing the password, you can use MySQL service

Most of the contents here refer to: MySQL MySQL 8.0.11 installation tutorial - Laumians - there are many online tutorials in the blog Park, which are basically the same. However, sometimes the installation of software may fail due to one detail. I also integrated many tutorials to install it, so this tutorial may not be generally suitable. Installation environment: win7 1. Download zip installation package: MySQLhttps://www.cnblogs.com/laumians-notes/p/9069498.html

Set up Windows services for MySQL

Add the environment variable of MySQL in WINDOWS settings, and then you can directly enter and start MySQL without address

MySQL creates and authorizes users

Use instructions such as:

create user 'alex'@'192.168.1.1' definded by '123456'

To create a user, fill in the user name in the first quotation mark, fill in the IP address of the user in the second, and fill in the login password of the user in the third

MySQL has a strict creation syntax. Generally, the user needs to be confirmed with the IP address. If it is not necessary, the address can be replaced with "%", which means that the user's IP can be ignored, for example:

create user 'alex'@'192.168.%' definded by '123456'

After creation, you can set the user's permission to access a database and table. The specific code is:

grant all privileges on db1.* to 'alex'@'%'; 

A specific table name can be filled in "*" and can also be used

revoke all privileges from db1.* to 'alex'@'%'; 

Delete user permissions

Topics: Database MySQL