macOS & MySQL 8.x

[xgqfrms]

macOS & MySQL 8.x

https://www.cnblogs.com/xgqfrms/tag/MySQL/default.html?page=1

# Sql@2022 ❌ 密码错误
➜  ~ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
➜  ~ 

https://www.cnblogs.com/xgqfrms/p/16791658.html

# sql@2022 ✅
➜  ~ mysql -u root -p
Enter password: 

# sql@2022 ✅
➜  ~ mysql -u root -h 192.168.18.195 -P 3306  -p
Enter password: 

登录 MySQL

mysql -h 主机名 -u 用户名 -p
参数说明：

-h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
-u : 登录的用户名;
-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

https://www.runoob.com/mysql/mysql-install.html#:~:text=%E5%8D%B3%E5%8F%AF%E5%90%AF%E5%8A%A8%20mysql%E3%80%82-,%E7%99%BB%E5%BD%95%20MySQL,-%E5%BD%93%20MySQL%20%E6%9C%8D%E5%8A%A1

MySQL 8.x 远程登录不了问题及解决方案

# 修改 ❓  'root'@'%'  不存在，所以修改不了
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'sql@2022';
#  ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%' ❌

# 授权 ❓  'root'@'%'  不存在，所以授权不了
mysql> GRANT ALL ON *.* TO 'root'@'%';
# ERROR 1410 (42000): You are not allowed to create a user with GRANT ❌

# 更新 ✅ host 的值可以更新
mysql> update user set host = '%' where user = 'root';

# 刷新
mysql> flush privileges;

https://www.jianshu.com/p/435307de1c29

[xgqfrms]

how to install MySQL on macOS

https://www.cnblogs.com/xgqfrms/p/13570542.html

MySQL Community Server 8.0.21

# version
$ mysqladmin --version
# 8.0.21

$ mysql --version
# mysql  Ver 8.0.21 for osx10.15 on x86_64 (Homebrew)

$ mysqladmin --version
# mysqladmin  Ver 8.0.21 for osx10.15 on x86_64 (Homebrew)

https://dev.mysql.com/downloads/mysql/

https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.31-macos12-x86_64.dmg

[xgqfrms]

[xgqfrms]

https://github.com/xgqfrms/mysql/blob/master/mysql-tutorials/mysql-commands.md

https://www.runoob.com/mysql/mysql-install.html

[xgqfrms]

libmysqlclient

Sql@2022

[xgqfrms]

mysqladmin --version

zsh: command not found: mysqladmin

vim ~/.zshrc  

code ~/.zshrc  

export PATH=${PATH}:/usr/local/mysql/bin/

export PATH=${PATH}:/usr/local/mysql/bin/

source ~/.zshrc   # If you use Oh-My-Zsh
source ~/.bashrc  # If you use Default Bash

[xgqfrms]

vim ~/.zshrc  

code ~/.zshrc  

export PATH=${PATH}:/usr/local/mysql/bin/

export PATH=${PATH}:/usr/local/mysql/bin/

source ~/.zshrc   # If you use Oh-My-Zsh
source ~/.bashrc  # If you use Default Bash

[xgqfrms]

git history & Xcode

[xgqfrms]

[xgqfrms]

update VSCode ✅

[xgqfrms]

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'

[xgqfrms]

https://segmentfault.com/a/1190000037533801

[xgqfrms]

https://www.runoob.com/mysql/mysql-union-operation.html

https://www.runoob.com/sql/sql-func-count.html

https://www.runoob.com/sql/sql-having.html

[xgqfrms]

局域网 ip 访问 MySQL

https://zhuanlan.zhihu.com/p/48600691

提示 'root'@'localhost'有误，查询发现root对应的host是%

# 开通权限
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

# 刷新授权
mysql> flush privileges;

# sql@2022 ✅
➜  ~ mysql -u root -h 192.168.18.195 -P 3306  -p
Enter password: 

https://www.cnblogs.com/yang842718540/p/9205424.html

通过 URL 访问 MySQL

var mysql  = require('mysql');  
var connection = mysql.createConnection({
    //host为目标实例的连接地址。 
    host     : 'dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com',  
    //user为您的AccessID。可在安全访问代理页面的被授权人列表中查看。     
    user     : 'AccessID', 
    //password为您的AccessSecret。可在安全访问代理页面的被授权人列表中查看。             
    password : 'AccessSecret', 
    //port为目标实例的端口号。      
    port     : '3306',  
    //database为目标实例的数据库名称。                 
    database : 'schema' 
}); 
connection.connect();
//使用execute方法执行SQL语句。
// 本示例以SHOW DATABASES为例，您也可以换成其他SQL语句。 
connection.query('SHOW DATABASES', function(err, result) {
    console.log(result);
});
connection.end();

https://help.aliyun.com/document_detail/209345.html

[xgqfrms]

mysql connect from remote host

mysql connect from command line

https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql

$ mysql -u user -h database_server_ip -p

https://stackoverflow.com/questions/15872543/access-mysql-remote-database-from-command-line

$ mysql -u {username} -p'{password}' \
    -h {remote server ip or name} -P {port} \
    -D {DB name}

# For example
$ mysql -u root -p'sql@2022'  -h 127.0.0.1 -P 3306  -D test

[xgqfrms]

local

$ mysql -u root -p ✅

$ mysql -u root -h localhost -P 3306 -p ✅

remote

$ mysql -u root -h 192.168.18.195 -P 3306 -p ❌

sql@2022

mysql -u root -h 192.168.18.195 -P 3306 -p
Enter password: 
ERROR 1130 (HY000): Host '192.168.18.207' is not allowed to connect to this MySQL server

mysql -u root -h 192.168.18.195 -P 3306 -p
Enter password: 
ERROR 1130 (HY000): Host '192.168.18.195' is not allowed to connect to this MySQL server

solution ✅

# 开通权限
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'sql@2022';

# ??? ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('sql@2022');
# 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 'PASSWORD('sql@2022')' at line 1

# 刷新授权
mysql> flush privileges;

mysql> use mysql;

mysql> select user,host from user;

mysql> select user,host from user \G;

https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

[xgqfrms]

# help
mysql> ?

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout or file

For server side help, type 'help contents'

mysql> 

# 查看状态
mysql> status;
--------------
mysql  Ver 8.0.31 for macos12 on x86_64 (MySQL Community Server - GPL)

Connection id:      21
Current database:   mysql
Current user:       root@localhost
SSL:            Not in use
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.31 MySQL Community Server - GPL
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /tmp/mysql.sock
Binary data as:     Hexadecimal
Uptime:         16 hours 44 min 8 sec

Threads: 2  Questions: 54  Slow queries: 0  Opens: 176  Flush tables: 3  Open tables: 97  Queries per second avg: 0.000
--------------

mysql> 

[xgqfrms]

https://stackoverflow.com/questions/5555328/error-1396-hy000-operation-create-user-failed-for-jacklocalhost

https://stackoverflow.com/questions/56946299/how-to-resolve-error-1396-hy000-operation-alter-user-failed-for-rootlocal

[xgqfrms]

mysql_native_password vs caching_sha2_password

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

mysql> 

demo

$ mysql -uroot -p 
Enter password: 

mysql> show databases;

mysql> use mysql;

mysql> show tables;

mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

mysql> create user `dev`@`%` identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| dev              | %         | caching_sha2_password |
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

mysql> alter user `dev`@`%` identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| dev              | %         | mysql_native_password |
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

# ❌
GRANT ALL ON *.* TO `developer`@`127.0.0.1` IDENTIFIED BY 'passowrd' WITH GRANT OPTION; 

# ✅ 创建用户
CREATE USER `developer`@`%` IDENTIFIED BY '123456';

# ✅ 授权
GRANT ALL ON *.* TO `developer`@`%` WITH GRANT OPTION;

# 加密连接方式 plugin 不对，修改一下
# ✅ 修改授权
ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '123456'

# 最后要刷新权限 ✅ 
FLUSH PRIVILEGES;

https://cloud.tencent.com/developer/article/1592379#:~:text=1-,MySQL8.0%E8%AE%BE%E7%BD%AE%E8%BF%9C%E7%A8%8B%E8%AE%BF%E9%97%AE%E6%9D%83%E9%99%90,-%E5%8F%91%E5%B8%83%E4%BA%8E2020

https://blog.csdn.net/wd2014610/article/details/89023562

[xgqfrms]

detail & summary

<details>
  <summary>点击展开代码</summary>
  <div>code...</div>
</details>

点击展开代码

```sql select * from user\G; *************************** 1. row *************************** Host: % User: root Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: 0x x509_issuer: 0x x509_subject: 0x max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: caching_sha2_password authentication_string: $A$005$9YJ)1~1'm / /6jpeMy4W5PDD5Cpq4R9lMe1gOk43JHcNyL1wUyviK./MCSd86 password_expired: N password_last_changed: 2022-10-14 18:46:11 password_lifetime: NULL account_locked: N Create_role_priv: Y Drop_role_priv: Y Password_reuse_history: NULL Password_reuse_time: NULL Password_require_current: NULL User_attributes: NULL *************************** 2. row *************************** Host: localhost User: mysql.infoschema Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: 0x x509_issuer: 0x x509_subject: 0x max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: caching_sha2_password authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED password_expired: N password_last_changed: 2022-10-14 18:46:09 password_lifetime: NULL account_locked: Y Create_role_priv: N Drop_role_priv: N Password_reuse_history: NULL Password_reuse_time: NULL Password_require_current: NULL User_attributes: NULL *************************** 3. row *************************** Host: localhost User: mysql.session Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: Y Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: Y Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: 0x x509_issuer: 0x x509_subject: 0x max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: caching_sha2_password authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED password_expired: N password_last_changed: 2022-10-14 18:46:09 password_lifetime: NULL account_locked: Y Create_role_priv: N Drop_role_priv: N Password_reuse_history: NULL Password_reuse_time: NULL Password_require_current: NULL User_attributes: NULL *************************** 4. row *************************** Host: localhost User: mysql.sys Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: 0x x509_issuer: 0x x509_subject: 0x max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: caching_sha2_password authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED password_expired: N password_last_changed: 2022-10-14 18:46:09 password_lifetime: NULL account_locked: Y Create_role_priv: N Drop_role_priv: N Password_reuse_history: NULL Password_reuse_time: NULL Password_require_current: NULL User_attributes: NULL 4 rows in set (0.00 sec) ERROR: No query specified mysql> ```

[xgqfrms]

<details> & <summary>

markdown & github

https://developer.mozilla.org/en-US/docs/Web/HTML/Element/details

https://developer.mozilla.org/en-US/docs/Web/HTML/Element/summary

https://www.w3schools.com/TAGS/tag_details.asp#:~:text=%3Cwbr%3E-,HTML%20%3Cdetails%3E%20Tag,-%E2%9D%AE%20Previous

https://html.com/tags/details/