# 檢查MySQL當(dāng)前監(jiān)聽情況
sudo netstat -tlnp | grep mysql

# 編輯MySQL配置文件
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# 允許任意IP連接
bind-address = 0.0.0.0

# 或者指定特定IP
# bind-address = 192.168.1.100

sudo systemctl restart mysql

-- 登錄MySQL
mysql -u root -p

-- 創(chuàng)建允許遠(yuǎn)程訪問的新用戶
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'your_password';

-- 或允許現(xiàn)有用戶遠(yuǎn)程訪問（替換已存在的用戶記錄）
CREATE USER 'existing_user'@'%' IDENTIFIED BY 'your_password';

-- 授予權(quán)限（按需調(diào)整）
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%';

-- 刷新權(quán)限
FLUSH PRIVILEGES;

-- 只允許特定IP訪問
CREATE USER 'remote_user'@'192.168.1.100' IDENTIFIED BY 'your_password';

# Ubuntu/Debian系統(tǒng)
sudo ufw allow 3306/tcp

# CentOS/RHEL系統(tǒng)
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

# 使用mysql客戶端連接
mysql -h your_server_ip -u remote_user -p

# 或使用命令行指定端口
mysql -h your_server_ip -P 3306 -u remote_user -p

-- 用戶賬號(hào)和全局權(quán)限
SELECT * FROM mysql.user;

-- 數(shù)據(jù)庫級(jí)別權(quán)限
SELECT * FROM mysql.db;

-- 表級(jí)別權(quán)限
SELECT * FROM mysql.tables_priv;

-- 列級(jí)別權(quán)限
SELECT * FROM mysql.columns_priv;

-- 查看SSL是否啟用
SHOW VARIABLES LIKE '%ssl%';

-- 要求用戶使用SSL連接
ALTER USER 'remote_user'@'%' REQUIRE SSL;

-- 客戶端連接時(shí)啟用SSL
mysql -h your_server_ip -u remote_user -p --ssl-mode=REQUIRED

# 在本地創(chuàng)建SSH隧道
ssh -L 3307:localhost:3306 username@your_server_ip

# 然后在本地連接到隧道端口
mysql -h 127.0.0.1 -P 3307 -u mysql_user -p

-- 只授予特定數(shù)據(jù)庫的權(quán)限
GRANT SELECT, INSERT, UPDATE ON specific_db.* TO 'remote_user'@'%';

-- 只授予特定表的權(quán)限
GRANT SELECT ON specific_db.specific_table TO 'remote_user'@'%';

-- 只授予特定列的權(quán)限
GRANT SELECT (id, name, email) ON specific_db.users TO 'remote_user'@'%';

-- 限制用戶每小時(shí)可以執(zhí)行的查詢數(shù)
ALTER USER 'remote_user'@'%' WITH MAX_QUERIES_PER_HOUR 1000;

-- 限制用戶每小時(shí)可以執(zhí)行的更新數(shù)
ALTER USER 'remote_user'@'%' WITH MAX_UPDATES_PER_HOUR 100;

-- 限制用戶每小時(shí)可以建立的連接數(shù)
ALTER USER 'remote_user'@'%' WITH MAX_CONNECTIONS_PER_HOUR 20;

-- 限制用戶可以同時(shí)建立的連接數(shù)
ALTER USER 'remote_user'@'%' WITH MAX_USER_CONNECTIONS 5;

// Java代碼示例：使用HikariCP連接池
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://your_server_ip:3306/your_database");
config.setUsername("remote_user");
config.setPassword("your_password");
config.setMaximumPoolSize(10);
config.setMinimumIdle(5);
config.setIdleTimeout(300000);
config.setConnectionTimeout(10000);

HikariDataSource dataSource = new HikariDataSource(config);

// 使用連接
try (Connection conn = dataSource.getConnection()) {
    // 執(zhí)行數(shù)據(jù)庫操作
}

-- 檢查現(xiàn)有用戶
SELECT user, host FROM mysql.user;

-- 創(chuàng)建新的用戶記錄或修改現(xiàn)有記錄
CREATE USER 'user'@'客戶端IP' IDENTIFIED BY 'password';
-- 或
CREATE USER 'user'@'%' IDENTIFIED BY 'password';

-- 授予權(quán)限
GRANT ALL PRIVILEGES ON *.* TO 'user'@'客戶端IP';
-- 或
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

-- 刷新權(quán)限
FLUSH PRIVILEGES;

# 檢查MySQL監(jiān)聽狀態(tài)
sudo netstat -tlnp | grep mysql

# 修改配置文件中的bind-address
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# 將bind-address = 127.0.0.1改為bind-address = 0.0.0.0

# 重啟MySQL
sudo systemctl restart mysql

# 檢查防火墻
sudo ufw status
sudo ufw allow 3306/tcp

-- 重置用戶密碼
ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';

-- 檢查用戶權(quán)限
SHOW GRANTS FOR 'user'@'host';

-- 授予必要權(quán)限
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'host';
FLUSH PRIVILEGES;

-- 在MySQL配置文件中添加
skip-name-resolve

-- 或在連接字符串中使用IP而非主機(jī)名
jdbc:mysql://192.168.1.100:3306/db_name

-- 調(diào)整超時(shí)參數(shù)
SET GLOBAL connect_timeout = 10;
SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

// Java代碼示例：簡單的讀寫分離
public class DBConnection {
    private static DataSource masterDataSource;
    private static DataSource slaveDataSource;
    
    static {
        // 初始化主庫連接池
        HikariConfig masterConfig = new HikariConfig();
        masterConfig.setJdbcUrl("jdbc:mysql://master_server:3306/db_name");
        masterConfig.setUsername("master_user");
        masterConfig.setPassword("master_password");
        masterDataSource = new HikariDataSource(masterConfig);
        
        // 初始化從庫連接池
        HikariConfig slaveConfig = new HikariConfig();
        slaveConfig.setJdbcUrl("jdbc:mysql://slave_server:3306/db_name");
        slaveConfig.setUsername("slave_user");
        slaveConfig.setPassword("slave_password");
        slaveDataSource = new HikariDataSource(slaveConfig);
    }
    
    // 獲取寫連接
    public static Connection getMasterConnection() throws SQLException {
        return masterDataSource.getConnection();
    }
    
    // 獲取讀連接
    public static Connection getSlaveConnection() throws SQLException {
        return slaveDataSource.getConnection();
    }
}

# ProxySQL配置示例
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '10.0.0.1', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '10.0.0.2', 3306);

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'password', 1);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;

# Docker Compose示例
version: '3'
services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: app_db
      MYSQL_USER: app_user
      MYSQL_PASSWORD: app_password
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/conf.d/custom.cnf
    networks:
      - app_network

  app:
    build: .
    depends_on:
      - mysql
    environment:
      DB_HOST: mysql
      DB_PORT: 3306
      DB_NAME: app_db
      DB_USER: app_user
      DB_PASSWORD: app_password
    networks:
      - app_network

networks:
  app_network:

volumes:
  mysql_data: