# 本地登錄（默認走 localhost）
mysql -u root -p
# 指定 IP 登錄（走網(wǎng)絡(luò)連接）
mysql -h 192.168.1.100 -u root -p
# 指定端口
mysql -h 192.168.1.100 -P 3306 -u root -p
# 退出
exit;
-- 或
quit;

-- 查看所有用戶
SELECT User, Host, plugin FROM mysql.user;
-- 創(chuàng)建本地用戶
CREATE USER 'alice'@'localhost' IDENTIFIED BY '密碼';
-- 創(chuàng)建遠程用戶（允許任意 IP）
CREATE USER 'bob'@'%' IDENTIFIED BY '密碼';
-- 創(chuàng)建遠程用戶（只允許特定 IP）
CREATE USER 'charlie'@'192.168.1.50' IDENTIFIED BY '密碼';
-- 修改密碼
ALTER USER 'bob'@'%' IDENTIFIED BY '新密碼';
-- 刪除用戶
DROP USER 'bob'@'%';
-- 刷新權(quán)限（修改后必須執(zhí)行）
FLUSH PRIVILEGES;

-- 授予所有權(quán)限
GRANT ALL PRIVILEGES ON *.* TO 'bob'@'%';

-- 授予特定數(shù)據(jù)庫權(quán)限
GRANT ALL PRIVILEGES ON mydb.* TO 'bob'@'%';

-- 授予只讀權(quán)限
GRANT SELECT ON mydb.* TO 'readonly'@'%';

-- 撤銷權(quán)限
REVOKE ALL PRIVILEGES ON *.* FROM 'bob'@'%';

-- 查看用戶權(quán)限
SHOW GRANTS FOR 'bob'@'%';

-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有數(shù)據(jù)庫
SHOW DATABASES;

-- 切換數(shù)據(jù)庫
USE mydb;

-- 創(chuàng)建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表結(jié)構(gòu)
DESC users;
-- 或
SHOW CREATE TABLE users;

-- 插入數(shù)據(jù)
INSERT INTO users (name, email) VALUES ('張三', 'zhangsan@example.com');

-- 查詢
SELECT * FROM users WHERE name LIKE '%張%';

-- 更新
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 刪除
DELETE FROM users WHERE id = 1;

# 啟動
sudo systemctl start mysql
# 停止
sudo systemctl stop mysql
# 重啟
sudo systemctl restart mysql
# 查看狀態(tài)
sudo systemctl status mysql

# 允許所有網(wǎng)卡監(jiān)聽（默認 127.0.0.1 只監(jiān)聽本地）
bind-address = 0.0.0.0
# 或注釋掉該行
# bind-address = 127.0.0.1

sudo systemctl restart mysql

# UFW (Ubuntu/Debian)
sudo ufw allow 3306/tcp
# firewalld (CentOS/RHEL)
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload
# 云服務(wù)器還需在安全組中放行 3306 端口

-- 創(chuàng)建遠程專用用戶（推薦）
CREATE USER 'remote_user'@'%' IDENTIFIED BY '強密碼';
GRANT ALL PRIVILEGES ON mydb.* TO 'remote_user'@'%';
FLUSH PRIVILEGES;

mysql_real_connect failed: Host '10.136.26.183' is blocked because of many connection errors;
unblock with 'mysqladmin flush-hosts'

# 服務(wù)端執(zhí)行，清空黑名單
mysqladmin flush-hosts

FLUSH HOSTS;

mysql_real_connect failed: Authentication plugin 'caching_sha2_password' cannot be loaded: 找不到指定的模塊。

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'10.136.11.246'

SELECT User, Host FROM mysql.user;

+------+-----------+
| User | Host      |
+------+-----------+
| root | %         |
| root | localhost |
+------+-----------+

SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

-- 修改已存在的 'root'@'%' 用戶
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密碼';
FLUSH PRIVILEGES;

SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

FLUSH HOSTS;

#include <mysql.h>
MYSQL* conn = mysql_init(nullptr);
if (!mysql_real_connect(conn, 
                        "10.136.11.246",  // 服務(wù)器 IP
                        "root",           // 用戶名
                        "你的密碼",        // 密碼
                        "數(shù)據(jù)庫名",        // 數(shù)據(jù)庫
                        3306,             // 端口
                        nullptr, 0)) {
    printf("連接失敗: %s\n", mysql_error(conn));
} else {
    printf("連接成功！\n");
}
mysql_close(conn);

-- 改單個用戶
ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY '密碼';

-- 或改全局默認（my.cnf）
[mysqld]
default_authentication_plugin = mysql_native_password

# 本地建立隧道，把遠程 3306 映射到本地 3307
ssh -L 3307:localhost:3306 user@服務(wù)器IP
# 然后 C++ 連接本地 3307，實際走的是加密 SSH
mysql_real_connect(conn, "127.0.0.1", "root", ..., 3307, ...);

-- 查看當前連接
SHOW PROCESSLIST;

-- 查看連接錯誤閾值
SHOW VARIABLES LIKE 'max_connect_errors';

-- 修改閾值（臨時）
SET GLOBAL max_connect_errors = 1000;

-- 查看用戶認證方式
SELECT User, Host, plugin, authentication_string FROM mysql.user;

-- 查看被拉黑的 IP（performance_schema 需開啟）
SELECT * FROM performance_schema.host_cache WHERE SUM_CONNECT_ERRORS > 0;