Skip to main content

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

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

  • This function uses sshpass to execute SSH commands on a remote server. The function takes four arguments: the SSH password, the SSH port, the server IP, and the command to execute.

    function execute_ssh_command {
        sshpass -p "$1" ssh -o StrictHostKeyChecking=no -p "$2" root@"$3" "$4"
    }

  • These prompts gather necessary details from the user to configure both the master and slave servers, such as SSH ports, passwords, and IP addresses.

    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

  •  These variables define the master server's configuration settings.

    bind_address_master="$server_ip_master"
    server_id_master="1"
    log_bin="mysql-bin"
  • SSH block for master

    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
    "

    This block does the following on the master server:

         1. Defines a function to check if a line exists in a file.
         2. Configures mysql-server.cn with the master server settings if they are not already present.
         3. Restarts the MySQL service.
         4. Creates a replica_user for replication if it doesn't already exist.
         5. Retrieves the current master status, including the File and Position.
         6. Saves the File and Position values to temporary files.

  •  This line executes the defined command block on the master server.

    execute_ssh_command "$ssh_password_master" "$ssh_port_master" "$server_ip_master" "$ssh_command_master"
  • This section retrieves the File and Position values from the master server, which are necessary for setting up the slave server.

    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"

  • These variables define the slave server's configuration settings.

    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
    "

    This block does the following on the slave server:

     

         1. Defines a function to check if a line exists in a file.
         2. Configures mysql-server.cnf with the slave server settings if they are not already present.
         3. Restarts the MySQL service.
         4. Configures the slave to replicate from the master using the retrieved File and Position values.
         5. Retrieves and prints the slave status.


  •  This line executes the defined command block on the slave server.

    execute_ssh_command "$ssh_password_slave" "$ssh_port_slave" "$server_ip_slave" "$ssh_command_slave"
  •  This line indicates that the MySQL replication setup on the slave server is complete.

    echo "MySQL replication setup on slave server completed."

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

Back to top