熟悉自己的MySQL服务器

1.第一次登陆的时候没有密码，修改密码有三总方式：

这里说一种：

2.现在要监控每个IP的连接数，实现方式如下：

select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

3.下面是我的整个安装过程

Microsoft Windows [版本 10.0.16299.431]
(c) 2017 Microsoft Corporation。保留所有权利。

C:\WINDOWS\system32>D:

D:\>cd D:\Software\mysql-5.6.40-winx64\bin

D:\Software\mysql-5.6.40-winx64\bin>mysqld - install
2018-05-22 13:53:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-22 13:53:03 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2018-05-22 13:53:03 0 [Note] mysqld (mysqld 5.6.40) starting as process 10796 ...

D:\Software\mysql-5.6.40-winx64\bin>mysqld -install
Service successfully installed.

D:\Software\mysql-5.6.40-winx64\bin>net start mysql
MySQL 服务正在启动 ...
MySQL 服务无法启动。

系统出错。

发生系统错误 1067。

进程意外终止。


D:\Software\mysql-5.6.40-winx64\bin>net start mysql
MySQL 服务正在启动 ...
MySQL 服务无法启动。

系统出错。

发生系统错误 1067。

进程意外终止。


D:\Software\mysql-5.6.40-winx64\bin>net start mysql
MySQL 服务正在启动 ...
MySQL 服务无法启动。

系统出错。

发生系统错误 1067。

进程意外终止。


D:\Software\mysql-5.6.40-winx64\bin>net start mysql
MySQL 服务正在启动 ...
MySQL 服务无法启动。

系统出错。

发生系统错误 1067。

进程意外终止。


D:\Software\mysql-5.6.40-winx64\bin>mysqladmin -u root -p shutdown
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 0'

D:\Software\mysql-5.6.40-winx64\bin>
D:\Software\mysql-5.6.40-winx64\bin>mysqld -install
The service already exists!
The current server installed: D:\Software\mysql-5.6.40-winx64\bin\mysqld MySQL

D:\Software\mysql-5.6.40-winx64\bin>mysqld.exe  -install
The service already exists!
The current server installed: D:\Software\mysql-5.6.40-winx64\bin\mysqld MySQL

D:\Software\mysql-5.6.40-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

mysql> exit;
Bye

D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

mysql> exit;
Bye

D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p 123
Enter password: ***
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

mysql>  SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
Query OK, 0 rows affected (0.02 sec)

mysql> exit;
Bye

D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p 123456
Enter password: ******
ERROR 1049 (42000): Unknown database '123456'

D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p
Enter password: *******
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

D:\Software\mysql-5.6.40-winx64\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

mysql> select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
+-----------+----------+
| ip        | count(*) |
+-----------+----------+
| localhost |        1 |
+-----------+----------+
1 row in set (0.03 sec)

mysql> mysql -u root -h127.0.0.1 -e "show processlist\G;"| egrep "Host\:" | awk -F: '{ print $2 }'| sort | uniq -c
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -u root -h127.0.0.1 -e "show processlist\G;"| egrep "Host\:" | awk -F: '{ ' at line 1
mysql> show engines
    -> ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>