Mysql Master-Slave Auto Configuration Script

Script

Replication Script  

#!/bin/bash

# Function to execute SSH commands using sshpass
function execute_ssh_command {
    sshpass -p "$1" ssh -o StrictHostKeyChecking=no -p "$2" root@"$3" "$4"
}

# Prompt for SSH port, password, MySQL root password, and server IP for master and slave servers
read -p "Enter SSH port for the master server: " ssh_port_master
read -s -p "Enter password for root user on the master server: " ssh_password_master
read -s -p "Enter MySQL root password for the master server: " mysql_root_pass_master
read -p "Enter master server IP address: " server_ip_master
echo
read -p "Enter SSH port for the slave server: " ssh_port_slave
read -s -p "Enter password for root user on the slave server: " ssh_password_slave
read -s -p "Enter MySQL root password for the master server: " mysql_root_pass_slave
read -p "Enter slave server IP address: " server_ip_slave

# Configuration variables for master
bind_address_master="$server_ip_master"
server_id_master="1"
log_bin="mysql-bin"

# SSH command block for master server
ssh_command_master="
    # Function to check if a line exists in a file
    function check_line_in_file {
        grep -qF \"\$1\" \"\$2\"
    }

    # Check and append configurations in mysql-server.cnf
    if ! check_line_in_file \"bind-address = $bind_address_master\" /etc/my.cnf.d/mysql-server.cnf; then
        echo \"bind-address = $bind_address_master\" >> /etc/my.cnf.d/mysql-server.cnf
    fi

    if ! check_line_in_file \"server-id = $server_id_master\" /etc/my.cnf.d/mysql-server.cnf; then
        echo \"server-id = $server_id_master\" >> /etc/my.cnf.d/mysql-server.cnf
    fi

    if ! check_line_in_file \"log_bin = $log_bin\" /etc/my.cnf.d/mysql-server.cnf; then
        echo \"log_bin = $log_bin\" >> /etc/my.cnf.d/mysql-server.cnf
    fi

    # Restart MySQL service
    systemctl restart mysqld

    # Check if the replica_user exists in MySQL
    if ! mysql -u root -p'$mysql_root_pass_master' -e \"SELECT User FROM mysql.user WHERE User='replica_user'\" | grep -q 'replica_user'; then
        mysql -u root -p'$mysql_root_pass_master' -e \"
            CREATE USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY '$mysql_root_pass_master';
            GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
            FLUSH PRIVILEGES;
        \"
        echo 'MySQL user replica_user created.'
    else
        echo 'MySQL user replica_user already exists. Skipping user creation.'
    fi

    # Retrieve and print master status from MySQL
    mysql_output_master=\$(mysql -u root -p'$mysql_root_pass_master' -e \"SHOW MASTER STATUS\G\")
    echo \"MySQL Output:\"
    echo \"\$mysql_output_master\"

    # Parse and print 'File' and 'Position' from master status
    mysql_file=\$(echo \"\$mysql_output_master\" | awk '/File:/ {print \$2}')
    mysql_position=\$(echo \"\$mysql_output_master\" | awk '/Position:/ {print \$2}')
    echo \"MySQL File: \$mysql_file\"
    echo \"MySQL Position: \$mysql_position\"

    # Save the parsed file and position to a temporary file for later use
    echo \$mysql_file > /tmp/mysql_file.txt
    echo \$mysql_position > /tmp/mysql_position.txt

    # Exit the SSH session
    exit
"

# Execute SSH command on master server
execute_ssh_command "$ssh_password_master" "$ssh_port_master" "$server_ip_master" "$ssh_command_master"

# Read the saved file and position from the temporary files
mysql_file=$(sshpass -p "$ssh_password_master" ssh -o StrictHostKeyChecking=no -p "$ssh_port_master" root@"$server_ip_master" "cat /tmp/mysql_file.txt")
mysql_position=$(sshpass -p "$ssh_password_master" ssh -o StrictHostKeyChecking=no -p "$ssh_port_master" root@"$server_ip_master" "cat /tmp/mysql_position.txt")

echo "MySQL configuration and user setup on master server completed."
echo "MySQL File: $mysql_file"
echo "MySQL Position: $mysql_position"

# Configuration variables for slave
bind_address_slave="$server_ip_slave"
server_id_slave="2"
log_bin="mysql-bin"

# SSH command block for slave server
ssh_command_slave="
    # Function to check if a line exists in a file
    function check_line_in_file {
        grep -qF \"\$1\" \"\$2\"
    }

    # Check and append configurations in mysql-server.cnf
    if ! check_line_in_file \"bind-address = $bind_address_slave\" /etc/my.cnf.d/mysql-server.cnf; then
        echo \"bind-address = $bind_address_slave\" >> /etc/my.cnf.d/mysql-server.cnf
    fi

    if ! check_line_in_file \"server-id = $server_id_slave\" /etc/my.cnf.d/mysql-server.cnf; then
        echo \"server-id = $server_id_slave\" >> /etc/my.cnf.d/mysql-server.cnf
    fi

    if ! check_line_in_file \"log_bin = $log_bin\" /etc/my.cnf.d/mysql-server.cnf; then
        echo \"log_bin = $log_bin\" >> /etc/my.cnf.d/mysql-server.cnf
    fi

    # Restart MySQL service
    systemctl restart mysqld

    # Access MySQL and execute replication setup queries
    mysql -u root -p'$mysql_root_pass_slave' -e \"
        STOP SLAVE;
        RESET SLAVE;
        CHANGE MASTER TO MASTER_HOST='$server_ip_master', MASTER_USER='replica_user', MASTER_PASSWORD='$mysql_root_pass_master', MASTER_LOG_FILE='$mysql_file', MASTER_LOG_POS=$mysql_position;
        START SLAVE;
        SHOW SLAVE STATUS\G;
    \" > /tmp/slave_status_output.txt

    # Print the output of SHOW SLAVE STATUS
    echo 'Slave Status Output:'
    cat /tmp/slave_status_output.txt

    # Exit the SSH session
    exit"

# Execute SSH command on slave server
execute_ssh_command "$ssh_password_slave" "$ssh_port_slave" "$server_ip_slave" "$ssh_command_slave"

echo "MySQL replication setup on slave server completed."

Script Explanation

...........................................................................................Thank you........................................................................................................................