Skip to main content

MySQL 8.0 Master-Slave

Server IPHost Role
192.168.10.2MySQL Master
192.168.10.3MySQL Slave

MySQL Master​

  1. Download the MySQL installation package

    wget https://pdpublic.mingdao.com/private-deployment/offline/common/mysql-8.0.42-linux-glibc2.17-x86_64.tar.xz

  2. Extract to the installation directory

    tar -xvf mysql-8.0.42-linux-glibc2.17-x86_64.tar.xz
    mv mysql-8.0.42-linux-glibc2.17-x86_64 /usr/local/mysql

  3. Create a MySQL user

    useradd -U -M -s /sbin/nologin mysql

  4. Create data and log directories and grant permissions

    mkdir -p /data/mysql/ /data/logs/mysql
    chown -R mysql:mysql /usr/local/mysql/ /data/mysql/ /data/logs/mysql/

  5. Configure the systemd management file

    cat > /etc/systemd/system/mysql.service <<EOF
    [Unit]
    Description=MySQL Server
    Documentation=man:mysqld(8)
    Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
    After=network.target
    After=syslog.target
    [Service]
    User=mysql
    Group=mysql
    Type=forking
    PIDFile=/usr/local/mysql/mysqld.pid
    TimeoutSec=0
    PermissionsStartOnly=true
    ExecStart=/usr/local/mysql/bin/mysqld --daemonize --log-error=/data/logs/mysql/mysqld.log --datadir=/data/mysql --socket=/usr/local/mysql/mysql.sock --character-set-server=utf8mb4 --pid-file=/usr/local/mysql/mysqld.pid --server-id=1 --log-bin=mysql-bin --max_connections=2000 --slow_query_log=1 --slow_query_log_file=/data/logs/mysql/mysql-slow.log
    LimitNOFILE=102400
    Restart=on-failure
    RestartPreventExitStatus=1
    PrivateTmp=false
    [Install]
    WantedBy=multi-user.target
    EOF

  6. Initialize

    /usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql/ --user=mysql --log-error=/data/logs/mysql/mysqld.log

  7. Start MySQL and enable autostart at boot

    systemctl daemon-reload
    systemctl enable mysql
    systemctl start mysql

  8. Change MySQL password

    /usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p$(grep 'temporary password' /data/logs/mysql/mysqld.log  | awk '{print $NF}')
    ALTER USER USER() IDENTIFIED BY '123456';
    update mysql.user set host='%' where user='root';
    FLUSH PRIVILEGES;
    grant all privileges on *.* to 'root'@'%' with grant option;
    FLUSH PRIVILEGES;
    • The root password changed in the command is 123456, be sure to replace it during actual deployment.

MySQL Slave​

  1. Download the MySQL installation package

    wget https://pdpublic.mingdao.com/private-deployment/offline/common/mysql-8.0.42-linux-glibc2.17-x86_64.tar.xz

  2. Extract to the installation directory

    tar -xvf mysql-8.0.42-linux-glibc2.17-x86_64.tar.xz
    mv mysql-8.0.42-linux-glibc2.17-x86_64 /usr/local/mysql

  3. Create a MySQL user

    useradd -U -M -s /sbin/nologin mysql

  4. Create data and log directories and grant permissions

    mkdir -p /data/mysql/ /data/logs/mysql
    chown -R mysql:mysql /usr/local/mysql/ /data/mysql/ /data/logs/mysql/

  5. Configure the systemd management file

    cat > /etc/systemd/system/mysql.service <<EOF
    [Unit]
    Description=MySQL Server
    Documentation=man:mysqld(8)
    Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
    After=network.target
    After=syslog.target
    [Service]
    User=mysql
    Group=mysql
    Type=forking
    PIDFile=/usr/local/mysql/mysqld.pid
    TimeoutSec=0
    PermissionsStartOnly=true
    ExecStart=/usr/local/mysql/bin/mysqld --daemonize --log-error=/data/logs/mysql/mysqld.log --datadir=/data/mysql --socket=/usr/local/mysql/mysql.sock --character-set-server=utf8mb4 --pid-file=/usr/local/mysql/mysqld.pid --server-id=2 --log-bin=mysql-bin --max_connections=2000 --slow_query_log=1 --slow_query_log_file=/data/logs/mysql/mysql-slow.log
    LimitNOFILE=102400
    Restart=on-failure
    RestartPreventExitStatus=1
    PrivateTmp=false
    [Install]
    WantedBy=multi-user.target
    EOF

  6. Initialize

    /usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql/ --user=mysql --log-error=/data/logs/mysql/mysqld.log

  7. Start MySQL and enable autostart at boot

    systemctl daemon-reload
    systemctl enable mysql
    systemctl start mysql

  8. Change MySQL password

    /usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p$(grep 'temporary password' /data/logs/mysql/mysqld.log  | awk '{print $NF}')
    ALTER USER USER() IDENTIFIED BY '123456';
    update mysql.user set host='%' where user='root';
    FLUSH PRIVILEGES;
    grant all privileges on *.* to 'root'@'%' with grant option;
    FLUSH PRIVILEGES;
    • The root password changed in the command is 123456, be sure to replace it during actual deployment.

Configure Master-Slave​

  1. Log into the master node and configure the master-slave synchronization user

    /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -uroot -p123456
    # Create repl user
    create user 'repl'@'%' identified by '123456';
    grant replication slave on *.* to 'repl'@"%";
    flush privileges;
    show master status;
    • The master-slave synchronization user created in the command is repl, with the password 123456. Be sure to replace it during actual deployment.

  2. Log into the slave node and configure to enable master-slave

    /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -uroot -p123456
    change master to master_host="192.168.10.2",master_port=3306,master_user="repl",master_password="123456",master_log_file="mysql-bin.000001",master_log_pos=2936,get_master_public_key=1;
    start slave;
    • In the change master statement, replace the master_host address with the actual master server IP during deployment.
    • The values for master_log_file and master_log_pos in the change master statement should correspond with the output from executing show master status; on the master node. Adjust them according to actual deployment conditions.

  3. Check master-slave synchronization status

    show slave status\G

    # Both Slave_IO_Running and Slave_SQL_Running being "Yes" in the output indicates normal master-slave synchronization