Connect MySQL instance
- Connect via local socket
mysql -S /tmp/mysql.sock -u root -p - Connect via TCP/IP
mysql -u tbs -pTbs_SOft! - Connect through my.cnf configuration file
-- Production view socket Where are the files tbs@localhost:[(none)]>show variables like "socket%"; +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | socket | /var/run/mysqld/mysqld.sock | +---------------+-----------------------------+ 1 row in set (0.01 sec)
-- adopt socke connect tbs@dell-PowerEdge-R740:/usr/sbin$ mysql -S /var/run/mysqld/mysqld.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.35-0ubuntu0.18.04.2-log (Ubuntu) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. tbs@localhost:[(none)]>
-- Production view connection mode tbs@localhost:[(none)]>\s -------------- mysql Ver 14.14 Distrib 5.7.35, for Linux (x86_64) using EditLine wrapper Connection id: 3 Current database: Current user: tbs@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.35-0ubuntu0.18.04.2-log (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 6 min 18 sec Threads: 2 Questions: 11 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.029 --------------
SSL encrypted connection
Since mysql 5.7, SSL communication is used by default
ssl secure socket
Not using ssl may be hijacked
mysql 5.7 clients connect using ssl encryption
tbs@localhost:[(none)]>show variables like "%ssl%"; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-----------------+ 9 rows in set (0.00 sec)
root@dell-PowerEdge-R740:/home/tbs/tbs_soft/tbs_ljc# mysql_ssl_rsa_setup root@dell-PowerEdge-R740:/var/lib/mysql# ls auto.cnf crashcourse ibdata1 performance_schema sys baidubaiku dbt3 ib_logfile0 private_key.pem test bigData debian-5.7.flag ib_logfile1 public_key.pem test_db_ljc ca-key.pem dell-PowerEdge-R740.log ibtmp1 sb tpcc1000 ca.pem dell-PowerEdge-R740-slow.log mysql server-cert.pem client-cert.pem employees mysqld_multi.log server-key.pem client-key.pem ib_buffer_pool mysql_upgrade_info sggtest root@dell-PowerEdge-R740:/var/lib/mysql# ll *.pem -rw------- 1 mysql mysql 1676 6 July 15:36 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 6 July 15:36 ca.pem -rw-r--r-- 1 mysql mysql 1112 6 July 15:36 client-cert.pem -rw------- 1 mysql mysql 1676 6 July 15:36 client-key.pem -rw------- 1 mysql mysql 1680 6 July 15:36 private_key.pem -rw-r--r-- 1 mysql mysql 452 6 July 15:36 public_key.pem -rw-r--r-- 1 mysql mysql 1112 6 July 15:36 server-cert.pem -rw------- 1 mysql mysql 1676 6 July 15:36 server-key.pem
-- conversation A -------------- mysql Ver 14.14 Distrib 5.7.35, for Linux (x86_64) using EditLine wrapper Connection id: 3 Current database: Current user: tbs@10.0.1.1 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.35-0ubuntu0.18.04.2-log (Ubuntu) Protocol version: 10 Connection: 117.107.134.104 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 14503 Uptime: 22 sec Threads: 2 Questions: 9 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.409 --------------
-- conversation B -- Local connection -------------- mysql Ver 14.14 Distrib 5.7.35, for Linux (x86_64) using EditLine wrapper Connection id: 5 Current database: Current user: tbs@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.35-0ubuntu0.18.04.2-log (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 1 min 20 sec Threads: 3 Questions: 16 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.200 --------------
SSL: Not in use
The local connection is connected through socket, which does not involve network transmission, and all are not encrypted.
Encrypted connections without ssl
-- --ssl-mode=DISABLED Indicates not enabled ssl connect tbs@dell-PowerEdge-R740:~$ mysql -h 117.107.134.104 -P 14503 -u tbs -pTbs_SOft! --ssl-mode=DISABLED
Turning on ssl has a certain impact on performance
Force users to connect to the database using SSL
tbs@localhost:[(none)]>alter user david@'%' require ssl; Query OK, 0 rows affected (0.00 sec)
What does he ask for
x509 needs to provide some secret key files to connect
How to enable ssl in version 5.6
root@dell-PowerEdge-R740:/mdata# mysqld --initialize-insecure --datadir=/mdata/mysql_test_data1
Password plug-in
validate_password
View mysql password plug-in
-- install validate_password Password plug-in tbs@localhost:[(none)]>INSTALL PLUGIN validate_password SONAME 'validate_password.so'; Query OK, 0 rows affected (0.00 sec) -- View password plug-in tbs@localhost:[(none)]>show plugins; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | auth_socket | ACTIVE | AUTHENTICATION | auth_socket.so | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) validate_password
Enable validate_ The password plug-in will prompt that the password is too simple
tbs@localhost:[(none)]>alter user david@'%' identified by '123'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
Password requirements, it is recommended that you open this plug-in
-- An eight digit case consisting of numbers mixed with special characters tbs@localhost:[(none)]>show variables like "validate%"; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.01 sec)
If this parameter is enabled, add a line in the configuration file
plugin-load = validate_password.so
validate_ password_ dictionary_ The file parameter is the dictionary file path
validate_password_policy consists of three strengths
0 length meets 8
1
2 characters in the dictionary file cannot appear
validate_ password_ check_ user_ The parameter name indicates that the user name cannot appear in the password
Netease how to deal with password hidden dangers. Check the permission, check the user table, check whether there are commonly used passwords, or query the user table.
Multi instance installation
- Install multiple MySQL instances on one server
- Make full use of hardware resources
- Through mysql_multi program is enough
Multi instance installation steps
Single machine multi instance installation is very common
Create directory
mkdir /data1 chown -R mysql.mysql /data1
Add read and write permissions in apparmor
vim /etc/apparmor.d/usr.sbin.mysqld #Add the permission of datadir (the original datadir was / var/lib/mysql): /data1/ r, /data1/** rwk, service apparmor reload
root@dell-PowerEdge-R740:/mdata# mysqld --initialize-insecure --datadir=/data1 #No files are generated. Let's go on
Modify profile
root@dell-PowerEdge-R740:/mdata/data1# vim /etc/mysql/mysql.conf.d/mysqld.cnf # Add the following at the end of the configuration file [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = root log = /var/log/mysqld_multi.log [mysqld1] port = 3307 datadir = /data1 socket = /tmp/mysql.sock1
root@dell-PowerEdge-R740:/mdata/data1# mysqld_multi report Reporting MySQL servers No groups to be reported (check your GNRs)
An error was encountered
The general MySQL configuration file is / etc/mysql/my.cnf or / etc/mysql/mysql.cnf. Add the following at the beginning of the configuration file:
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin
You can see that there is no service group. Now add one - in the MySQL configuration file, add:
[mysqld1] port = 3307 datadir = /data1 socket = /tmp/mysql.sock1 server-id = 12 bind_address = 0.0.0.0 user = mysql performance_schema = off innodb_buffer_pool_size = 32M skip_name_resolve = 1 log_error = error.log pid-file = /data1/mysql.pid2
Put the configuration parameters of the group in / etc/mysql/my.cnf to solve the problem.
# Configure group information root@dell-PowerEdge-R740:/mdata/data1# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is not running
A new error occurred and the / mdata/data1 file does not exist
root@dell-PowerEdge-R740:/data1# mysqld_multi start 1 Installing new database in /data1 FATAL ERROR: Tried to start mysqld under group [mysqld1], but no data directory was found or could be created. data directory used: /data1
root@dell-PowerEdge-R740:/data1# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is not running root@dell-PowerEdge-R740:/data1# mysqld_multi start 1 root@dell-PowerEdge-R740:/data1# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is running
[mysqld] the following parameters are shared and inherited.
Multi version mysql startup
Set mysql password cycle
root@localhost:[mysql]>show variables like "default_password_lifetime"; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | default_password_lifetime | 0 | +---------------------------+-------+ 1 row in set (0.00 sec)
0 means never out of date
180 means it expires in 180 days
It is recommended to write this parameter to the configuration file
role
MySQL 8.0 supports this
Uninstall plug-in tbs@localhost:[(none)]>uninstall plugin validate_password; Query OK, 0 rows affected (0.00 sec)