mysql5.7 installation and introduction

1. Inspection work

1. Check if mysql has been installed

rpm -qa|grep mariadb check if mariadb is installed

rpm -e --nodeps mariadb-libs delete if there is

2. View mysql's dependent environment

rpm -qa|grep libaio

rpm -qa|grep net-tools

3. Confirm the permissions of the /tmp directory

cd ..

ll

Two, installation

Download the installation package address http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.16-1.el7.x86_64.rpm-bundle.tar , decompress it with the tar -xvf package name command, total 12 rpm files, only 4 are installed.

1. Install the following rpm packages in turn

rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.16-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.16-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.16-1.el7.x86_64.rpm  
2. View version

mysqladmin --version

3. mysql service initialization

mysqld --initallize --user=mysql

Log in with the initial password in the log, and check the log directory as follows, where [email protected]: the initial password is the following

cat var/log/mysqd.log

Login command

mysql -u root -p password in the log

The first time you log in, you must modify the root password to have permission

ALTER USER'root'@'localhost' IDENTITIED BY '123456'; (Note: the sql statement should end with a semicolon)

Three, users and permissions

1. Modify the character set

Build a database: create database mydb

Display the character set of the database command: show create database mydb

After the table tb is created, the attribute value of Chinese is inserted, but it cannot be inserted. The following three steps must be modified:

1. Modify the configuration file character set settings, modify /etc/my.cnf, the last line is set as follows

character_set_server=utf8

2. Modify the existing database character set: alter database character set'utf8';

3. Modify the character set of the existing table: alter table tb convert to character set'utf8';

After the modification is completed, quit, and then restart the mysql service systemctl restart mysqld

2. Create a new database user

2.1 Create a zhangsan user

create user zhangsan identified by '123456';

View database user: authentication_string: password

2.2. Create a user and authorize

grant all privileges on *.* to [email protected]'%' identified by '123123';

Four, logical architecture

1. Miscellaneous configuration

View sql_mode configuration:

SHOW VARIABLES LIKE'sql_mode'

Group query conditions, only group fields and functions can be added after select.

2. Logical architecture

2.1 General overview

The following will use the query cache and SQL execution cycle. Come experience the principles of architecture.

2.2. Use show profile to view the execution cycle of sql

Turn on query cache:

vim /etc/my.cnf

systemctl restart mysqld

profile configuration:

View profile configuration, open profile (query cache) configuration

View sql execution plan

The execution cycle of query sql:

show profile cpu, block io for query 5;. The picture above is 5.

The execution plan of the first query is not cached, and is stored in the cache after the query.

Second query:

Query plan:

Query cycle:

show profile cpu, block, io for query 6;

3. Storage Engine

3.1. View command

Comparison of two commonly used and important storage engines: (Commonly used InnoDB)