MySQL 8.4 Master-Slave

Server IP	Host Role
192.168.10.2	MySQL Master
192.168.10.3	MySQL Slave

MySQL Master

1. Download the MySQL installation package

   Server supports internet access

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

   Server does not support internet access

   # Download link for the MySQL installation package; upload it to the deployment server after downloading
   https://pdpublic.mingdao.com/private-deployment/offline/common/mysql-8.4.10-linux-glibc2.17-x86_64.tar.xz

2. Extract the package to the installation directory

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

3. Create the 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. Create the /etc/my.cnf configuration file

   cat > /etc/my.cnf <<'EOF'
   [mysqld]
   user                         = mysql
   basedir                      = /usr/local/mysql
   datadir                      = /data/mysql
   socket                       = /usr/local/mysql/mysqld.sock
   pid-file                     = /usr/local/mysql/mysqld.pid
   log-error                    = /data/logs/mysql/mysqld.log
   server-id                    = 1
   bind-address                 = 0.0.0.0
   port                         = 3306
   skip-name-resolve            = ON
   max_connections              = 5000
   character_set_server         = utf8mb4
   collation_server             = utf8mb4_0900_ai_ci
   default_storage_engine       = InnoDB
   innodb_buffer_pool_size      = 2G
   innodb_flush_log_at_trx_commit = 1
   slow_query_log               = ON
   slow_query_log_file          = /data/logs/mysql/mysql-slow.log
   long_query_time              = 1
   log_bin                      = /data/mysql/mysql-bin
   sync_binlog                  = 1
   binlog_expire_logs_seconds   = 2592000
   
   [client]
   port                         = 3306
   socket                       = /usr/local/mysql/mysqld.sock
   default-character-set        = utf8mb4
   
   [mysql]
   default-character-set        = utf8mb4
   EOF

6. Configure the systemd management file

   cat > /etc/systemd/system/mysql.service <<'EOF'
   [Unit]
   Description=MySQL Database Server
   Documentation=man:mysqld(8) http://dev.mysql.com/doc/
   After=network.target
   Wants=network-online.target
   After=network-online.target
   
   [Service]
   Environment=MYSQLD_PARENT_PID=1
   User=mysql
   Group=mysql
   Type=forking
   PIDFile=/usr/local/mysql/mysqld.pid
   ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize
   ExecStop=/bin/kill -s SIGTERM $MAINPID
   LimitNOFILE=102400
   LimitMEMLOCK=infinity
   Restart=on-failure
   OOMScoreAdjust=-500
   TimeoutSec=0
   
   [Install]
   WantedBy=multi-user.target
   EOF

7. Initialize MySQL

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

8. Start MySQL and enable it to start automatically at boot

   systemctl daemon-reload
   systemctl enable mysql
   systemctl start mysql

9. Change the 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';
   grant all privileges on *.* to 'root'@'%' with grant option;

   • The root user password changed by the command is 123456; use a strong password in actual deployments.
   • If the password contains special characters, only - or _ are allowed. Do not use characters such as @ ! # & to avoid compatibility issues.

10. Test the database connection with the newly configured password

    /usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p123456

MySQL Slave

1. Download the MySQL installation package

   Server supports internet access

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

   Server does not support internet access

   # Download link for the MySQL installation package; upload it to the deployment server after downloading
   https://pdpublic.mingdao.com/private-deployment/offline/common/mysql-8.4.10-linux-glibc2.17-x86_64.tar.xz

2. Extract the package to the installation directory

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

3. Create the 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. Create the /etc/my.cnf configuration file

   The server-id values of the master and slave nodes must be unique. This slave uses server-id = 2.

   cat > /etc/my.cnf <<'EOF'
   [mysqld]
   user                         = mysql
   basedir                      = /usr/local/mysql
   datadir                      = /data/mysql
   socket                       = /usr/local/mysql/mysqld.sock
   pid-file                     = /usr/local/mysql/mysqld.pid
   log-error                    = /data/logs/mysql/mysqld.log
   server-id                    = 2
   bind-address                 = 0.0.0.0
   port                         = 3306
   skip-name-resolve            = ON
   max_connections              = 5000
   character_set_server         = utf8mb4
   collation_server             = utf8mb4_0900_ai_ci
   default_storage_engine       = InnoDB
   innodb_buffer_pool_size      = 2G
   innodb_flush_log_at_trx_commit = 1
   slow_query_log               = ON
   slow_query_log_file          = /data/logs/mysql/mysql-slow.log
   long_query_time              = 1
   log_bin                      = /data/mysql/mysql-bin
   sync_binlog                  = 1
   binlog_expire_logs_seconds   = 2592000
   
   [client]
   port                         = 3306
   socket                       = /usr/local/mysql/mysqld.sock
   default-character-set        = utf8mb4
   
   [mysql]
   default-character-set        = utf8mb4
   EOF

6. Configure the systemd management file

   cat > /etc/systemd/system/mysql.service <<'EOF'
   [Unit]
   Description=MySQL Database Server
   Documentation=man:mysqld(8) http://dev.mysql.com/doc/
   After=network.target
   Wants=network-online.target
   After=network-online.target
   
   [Service]
   Environment=MYSQLD_PARENT_PID=1
   User=mysql
   Group=mysql
   Type=forking
   PIDFile=/usr/local/mysql/mysqld.pid
   ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize
   ExecStop=/bin/kill -s SIGTERM $MAINPID
   LimitNOFILE=102400
   LimitMEMLOCK=infinity
   Restart=on-failure
   OOMScoreAdjust=-500
   TimeoutSec=0
   
   [Install]
   WantedBy=multi-user.target
   EOF

7. Initialize MySQL

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

8. Start MySQL and enable it to start automatically at boot

   systemctl daemon-reload
   systemctl enable mysql
   systemctl start mysql

9. Change the 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';
   grant all privileges on *.* to 'root'@'%' with grant option;

   • The root user password changed by the command is 123456; use a strong password in actual deployments.
   • If the password contains special characters, only - or _ are allowed. Do not use characters such as @ ! # & to avoid compatibility issues.

10. Test the database connection with the newly configured password

    /usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p123456

Configure Master-Slave Replication

1. Log in to the master node and configure the replication user

   /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysqld.sock -uroot -p123456
   # Create repl
   create user 'repl'@'%' identified by '123456';
   grant replication slave on *.* to 'repl'@"%";
   show binary log status\G

   • The replication user created by the command is repl, and its password is 123456; use a strong password in actual deployments.
   • If the password contains special characters, only - or _ are allowed. Do not use characters such as @ ! # & to avoid compatibility issues.

2. Log in to the slave node and configure replication

   /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysqld.sock -uroot -p123456
   change replication source to
       source_host='192.168.10.2',
       source_port=3306,
       source_user='repl',
       source_password='123456',
       source_log_file='mysql-bin.000001',
       source_log_pos=2936,
       get_source_public_key=1;
   start replica;

   • Replace source_host in the change replication source to statement with the actual IP address of the source server.
   • The source_log_file and source_log_pos values come from the output of show binary log status\G on the source node. Use the actual values from your deployment.

3. Check the master-slave synchronization status

   show replica status\G
   
   # In the output, Replica_IO_Running and Replica_SQL_Running being Yes means that master-slave synchronization is normal.