Linux builds a MySQL database development environment

Linux builds a MySQL database development environment

1.mysql overview

[1] MySQL: Oracle’s open source database, which is selected here.

[2] MySQL Community Edition: MySQL Open Source Community Version, current version: 5.7.21

MySQL Community Edition is the freely downloadable version of the world’s most popular open source database. It is available under the GPL license and is supported by a huge and active community of open source developers.

[3] Select to download mysql community energy installation package:

[4] There are currently two versions, here I choose GA-5.7.21, the latest version of the latest DEV-8.0.4

2. MySQL installation

[1] This tutorial has default the Linux environment already installed SSH, using the XFTP5 tool to upload the installation package to Linux server: / usr / local / mysql:

[2] Log in to the Linux server using the Xhell5 tool and enter: CD / usr / local / mysql:

[3] Use the TAR-XVF command to decompress the installation package, Xhell5 terminal input: TAR-XVF mysql-5.7.21-1.el7.x86_64.rpm-bundle.tar

[4] Use rpm -qa | grep * command to detect if the Linux server is installed with other MySQL versions, type: rpm -qa | grep mariadb – Mariadb is a branch version to support Linux development

[5] Uninstall the above MariaDB using the rpm -e –Nodeps command, repeat the input command verification: rpm -qa | grep mariadb

[root @ marklin mysql] # rpm -e –Nodeps mariadb-server-5.5.56-2.el7.x86_64

[root @ marklin mysql] # rpm -e –Nodeps mariadb-libs-5.5.56-2.el7.x86_64

[root @ marklin mysql] # rpm -e –nodeps mariadb-5.5.56-2.el7.x86_64

[6] Use the rpm -ivh command to install in the following order:

RPM-IVH mysql-community-common-5.7.21-1.el7.x86_64.rpm

RPM-IVH mysql-community-libs-5.7.21-1.el7.x86_64.rpm

RPM-IVH mysql-community-devel-5.7.21-1.el7.x86_64.rpm

RPM-IVH mysql-community-libs-compat-5.7.21-1.el7.x86_64.rpm

RPM-IVH MYSQL-Community-Embedded-5.7.21-1.el7.x86_64.rpm

MySQL-Community-Embedded-devel-5.7.21-1.el7.x86_64.rpm

RPM-IVH mysql-community-embedded-compat-5.7.21-1.el7.x86_64.rpm

RPM-IVH mysql-community-client-5.7.21-1.el7.x86_64.rpm

RPM-IVH mysql-community-server-5.7.21-1.el7.x86_64.rpm

mysql-community-test-5.7.21-1.el7.x86_64.rpm – Need Perl Support, Enter: Yum Install Perl-Json.Noarch

3. MySQL configuration

[1] Start the MySQL service, type: systemctl start mysqld.service

Or enter: Service MySQLD Start [2] View MySQL Service Status, type: Service MySQLD Status

[3] Get the factory password, type: cat /var/log/mysqld.log | grep password – jt0! P = n! B9; 8

[4] Test Login myql: mysql -u root -p, enter your password: JT0! P = N! B9; 8

[5] Modify the default password and modify the default password security policy:

Digital + English letters, including case: 123456ABC

Digital + lowercase letters: 123456ABC

Adjust the password security policy:

Set global validate_password_policy = 0;

Set global validate_parsword_mixed_case_count = 0;

SET GLOBAL VALIDATE_PASSWORD_NUMBER_COUNT = 3;

Set global validate_parsword_special_char_count = 0;

SET GLOBAL VALIDATE_PASSWORD_LENGTH = 3;

Set password for ‘root’ @ ‘localhost’ = password (‘123456Abc’); – Modify the default password: 123456ABC

Show variables like ‘validate_password%’; – View Modified Password Verification Parameters

[6] Authorized database users and password: grant all privileges on *. * To ‘root’ @ ‘%’ Identified by ‘123456Abc’ with grant Option;

[7] Refresh strategy results in effect: Flush Privileges;

[8] Use the modified password test to log in: mysql -u root -p, enter your password: 123456ABC

[9] Open 3306 port:

Open port needs to configure a firewall launch: Systemctl Start FireWallD.Service

View firewall status: Systemctl status fireWallD.Service

Open port: firewall-cmd –zone = public –Add-port = 3306 / tcp –Permanent

Re-load configuration: firewall-cmd –Reload

[10] Using the client login verification:

4. Parameter configuration of Mysql performance optimization

[1] mysql log output record:

MySQL can record the log of the logs with General Query Log and Slow Query Log, you need to pay attention to:

(1). For performance, the General Log general log will not be turned on. Slow logs can locate some SQL with performance issues, while General Log records all SQL.

(2). MySQL5.0, if you want to turn on Slow log, General Log, you need to restart, start from MySQL5.1.6, the General Query Log and Slow Query Log began to support two ways to write or database tables.

(3). The log of the log is the modification of the output mode, can be dynamically modified at the Global level.

Slow log query output method:

System variable name

Set parameter value

Parameter description

Remark

SLOW_QUERY_LOG

[ON | OFF]

Whether to enable slow query logs

Default is OFF

SLOW_QUERY_LOG_FILE

[filename]

Specify the path and name of the slow query log file, you can specify the absolute path

The default is the ‘host name _slow.log’, located in the datadir directory

Long_query_time

2

SQL statement runtime threshold

The statement that is greater than the parameter value will be recorded

MIN_EXAMINED_ROW_LIMIT

100

The number of records detected by the SQL statement is less than the set value.

Will not be recorded to a slow query log, even if this statement execution time exceeds long_query_time threshold

LOG_QUERIES_NOT_USING_INDEXES

1

Will not use an indexed statement to record to a slow query log

LOG_THROTTLE_QUERIES_NOT_USING_INDEXES10

Set the number of unused indexes recorded to the log per minute

More than this number, only the number of statements and total time spent

LOG-SLOW-Admin-Statements

1

Record slow management SQL

Alter Table, Analyze Table, Check Table, Create Index, Drop Index, Optimize Table, Repair Table

Log_timestamps

SYSTEM

5.7 version of the new timestamp belonging to time zone parameters

The timestamp of the default record UTC time zone should be modified to record system time zone

LOG_OUTPUT

[None | file | TABLE]

Specifies the output of slow query logs

From version 5.5, you can record the log file (File, Slow Query Log) and Database Table (Table, MySql.slow_LOG)

Operation example:

[1] Show Slow situation: show variables like ‘% SLOW%’;

[2] Modify the database configuration parameters:

Mysql> set @@ Global.slow_Query_log = ON;

Query OK, 0 ROWS Affected (0.00 SEC)

Mysql> set @@ global.log_slow_admin_statements = 1;

Query OK, 0 ROWS Affected (0.00 SEC)

Mysql> Set Global Min_examined_row_limit = 100;

Query OK, 0 ROWS Affected (0.00 SEC)

Mysql> Set Global log_queries_not_using_indexes = 1;

Query OK, 0 ROWS Affected (0.00 SEC)

MySQL> Set Global log_throttle_queries_not_using_indexes = 10;

Query OK, 0 ROWS Affected (0.00 SEC)

[3] View the situation of modifying Slow: show variables like ‘% SLOW%’;

Mysql> show variables like ‘% SLOW%’;

+———————————– ———— +

| Variable_name | Value |

+———————————– ———— +

| log_slow_admin_statements | ON |

| log_slow_slave_statements | OFF |

| SLOW_LAUNCH_TIME | 2 |

| SLOW_QUERY_LOG | ON |

| SLOW_QUERY_LOG_FILE | /VAR/LIB/MYSQL/Marklin-slow.log |

+———————————– ———— +

5 rows in set (0.01 sec)

[5] Configuration records do not use the indexed SQL:

Mysql> set @@ global.log_queries_not_using_indexes = on;

Query OK, 0 ROWS Affected (0.00 SEC)

Mysql> show variables like ‘% index%’; + ————————————– – + —————————————- +

| Variable_name | Value |

+————————————– ——————————– +

| EQ_RANGE_INDEX_DIVE_LIMIT | 200 |

| InnoDB_Adaptive_hash_index | ON |

| InnoDB_Adaptive_hash_index_parts | 8 |

INNODB_CMP_PER_INDEX_ENABLED | OFF |

| LOG_BIN_INDEX | |

| LOG_QUERIES_NOT_USING_INDEXES | ON |

| log_throttle_queries_not_using_indexes | 10 |

| Performance_schema_max_index_stat | -1 |

| relay_log_index | /var/lib/mysql/marklin-relay-bin.index |

+————————————– ——————————– +

9 rows in set (0.00 sec)

General query log output mode:

System variable name

Set parameter value

Parameter Description

Remark

LOG_OUTPUT

[None | file | TABLE]

General query log output support format

The system defaults to file, generally recommended using File and Table format mixed output universal logs, NONE is not output log file

General_log

[ON | OFF]

Whether to enable universal query logs

The system defaults to OFF

General_LOG_FILE

[filename]

General query log location and name

Specify log file path and log name

Operation example:

[1] Query system variable: show variables like ‘% general%’;

Mysql> show variables like ‘% general%’;

+ ———————————————- +

| Variable_name | Value |

+ ———————————————- +

| general_log | OFF |

| general_log_file | /VAR/LIB/Mysql/marklin.log | + ———————————- ———— +

2 rows in set (0.00 sec)

[2] Open General_Log: Set global general_log = on;

Mysql> Set global general general_log = on;

Query OK, 0 ROWS Affected (0.01 SEC)

Mysql> show variables like ‘% general%’;

+ ———————————————- +

| Variable_name | Value |

+ ———————————————- +

| general_log | ON |

| general_log_file | /VAR/LIB/Mysql/marklin.log |

+ ———————————————- +

2 rows in set (0.34 sec)

[3] Set the log output format: set global log_output = ‘file, table’;

MySQL> Set Global log_output = ‘file, table’;

Query OK, 0 ROWS Affected (0.00 SEC)

Mysql> show variables like ‘log_output’;

+ ————– + ———— +

| Variable_name | Value |

+ ————– + ———— +

| log_output | file, table |

+ ————– + ———— +

1 row in set (0.01 sec)

Linux builds a mysql database development environment.pdf (1.3 MB) Downloads: 1

Related Posts