HyperDB Cho WordPress

#!/bin/bash

# deploy_mysql_replication.sh
# Fully automated MySQL master-slave deployment on Ubuntu 24.04 via SSH gateway

# --- Configuration ---
MASTER=10.130.0.10
SLAVE=10.130.0.11
SSH_USER=root
MYSQL_ROOT_PASSWORD='StrongRootPass123!'
REPL_USER='repl_user'
REPL_PASSWORD='ReplPass123!'

# --- Functions ---
install_mysql() {
    local server=$1
    local server_id=$2
    echo "\n--> Installing MySQL on $server"
    ssh $SSH_USER@$server "
        while fuser /var/lib/dpkg/lock-frontend >/dev/null 2>&1 || fuser /var/lib/apt/lists/lock >/dev/null 2>&1; do
            echo 'Waiting for other package management processes to finish...'
            sleep 5
        done
        apt update && apt install -y mysql-server pwgen ufw
        mysql -e \"
            ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '$MYSQL_ROOT_PASSWORD';
            CREATE USER IF NOT EXISTS '$REPL_USER'@'%' IDENTIFIED WITH mysql_native_password BY '$REPL_PASSWORD';
            GRANT REPLICATION SLAVE ON *.* TO '$REPL_USER'@'%';
            FLUSH PRIVILEGES;
        \"
        
        # Add server-id and log_bin to config
        echo '' >> /etc/mysql/mysql.conf.d/mysqld.cnf
        echo '[mysqld]' >> /etc/mysql/mysql.conf.d/mysqld.cnf
        echo 'server-id = $server_id' >> /etc/mysql/mysql.conf.d/mysqld.cnf
        if [ \"$server_id\" -eq 1 ]; then
            echo 'log_bin = /var/log/mysql/mysql-bin.log' >> /etc/mysql/mysql.conf.d/mysqld.cnf
        fi

        sed -i '/^bind-address/s/127.0.0.1/0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
        systemctl enable mysql
        systemctl restart mysql
        # Allow MySQL port through firewall
        ufw allow 3306/tcp
        ufw reload
        # Ensure root can connect from any host
        mysql -uroot -p$MYSQL_ROOT_PASSWORD -e \"ALTER USER 'root'@'%' IDENTIFIED BY '$MYSQL_ROOT_PASSWORD'; FLUSH PRIVILEGES;\"
    "
}

reset_master() {
    local server=$1
    echo "\n--> Resetting Master"
    ssh $SSH_USER@$server "
        mysql -uroot -p$MYSQL_ROOT_PASSWORD -e \"RESET MASTER;\"
    "
}

configure_slave() {
    local slave=$1
    local master=$2
    local master_status=$(ssh $SSH_USER@$master "mysql -uroot -p$MYSQL_ROOT_PASSWORD -e 'SHOW MASTER STATUS\G'")

    FILE=$(echo "$master_status" | grep File | awk '{print $2}')
    POSITION=$(echo "$master_status" | grep Position | awk '{print $2}')

    echo "\n--> Configuring $slave as Slave"
    ssh $SSH_USER@$slave "
        mysql -uroot -p$MYSQL_ROOT_PASSWORD -e \"
            CHANGE MASTER TO MASTER_HOST='$master', MASTER_USER='$REPL_USER', MASTER_PASSWORD='$REPL_PASSWORD', MASTER_LOG_FILE='$FILE', MASTER_LOG_POS=$POSITION, GET_MASTER_PUBLIC_KEY=1;
            START SLAVE;
        \"
    "
}

# --- Execution ---
# 1. Install MySQL on both servers
install_mysql $MASTER 1
install_mysql $SLAVE 2

# 2. Reset Master
reset_master $MASTER

# 3. Configure Slave
configure_slave $SLAVE $MASTER

# 4. Output connection info

# thêm hàm kiểm tra status slave
check_slave_status() {
    local server=$1
    echo "\n--> Checking Slave Status on $server"
    ssh $SSH_USER@$server "
        mysql -uroot -p$MYSQL_ROOT_PASSWORD -e 'SHOW SLAVE STATUS\G'
    "
}

check_slave_status $SLAVE

cat <<EOL

--- Deployment Complete ---
Master: $MASTER
Slave:  $SLAVE

MySQL Root User: root
Root Password: $MYSQL_ROOT_PASSWORD

Replication User: $REPL_USER
Replication Password: $REPL_PASSWORD

Test Master Connection:
  mysql -h $MASTER -uroot -p$MYSQL_ROOT_PASSWORD

Test Slave Connection:
  mysql -h $SLAVE -uroot -p$MYSQL_ROOT_PASSWORD

Check Slave Status:
  mysql -h $SLAVE -uroot -p$MYSQL_ROOT_PASSWORD -e 'SHOW SLAVE STATUS\G'

Note: Root user is now allowed to connect from any host (%), ensure your firewall and network security allows trusted access only.

EOL

MYSQL_ROOT_PASSWORD='StrongRootPass123!'
REPL_USER='repl_user'
REPL_PASSWORD='ReplPass123!'

bash Replication_sql.sh

root@vpn:~# ssh [email protected]
....................................................
Last login: Wed Sep 17 01:57:42 2025 from 10.130.0.9
root@mysql-02:~# mysql -uroot -pStrongRootPass123!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.43-0ubuntu0.24.04.1 (Ubuntu)

Copyright (c) 2000, 2025, 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.

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'repl_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

<?php


$wpdb->add_database(array(
    'host'     => '10.130.0.10',
    'user'     => 'repl_user',
    'password' => 'ReplPass123!',
    'name'     => 'mydb',
    'dataset'  => 'global',
    'write'    => 1,
    'read'     => 1000,
));

$wpdb->add_database(array(
    'host'     => '10.130.0.11',
    'user'     => 'repl_user',
    'password' => 'ReplPass123!',
    'name'     => 'mydb',
    'dataset'  => 'global',
    'write'    => 0,
    'read'     => 1,
));

wget -o /pat/to/wp-content/db.php https://raw.githubusercontent.com/vncloudsco/HyperDB/refs/heads/trunk/db.php

<?php
add_action( 'muplugins_loaded', function() {
    global $wpdb;
    $wpdb->queries = [];
    $wpdb->save_queries = true;
 
    $wpdb->query( "SELECT * FROM {$wpdb->users} LIMIT 1;" );
    print_r( $wpdb->queries );
    print_r( $wpdb->dbhs );
 
    $wpdb->query( "UPDATE $wpdb->users SET user_login = 'foo' where user_login = 'bar' LIMIT 1;" );
    print_r( $wpdb->queries );
    print_r( $wpdb->dbhs );
    die();
} );