MySQL 8.4 主从

服务器 IP	主机角色
192.168.10.2	MySQL Master
192.168.10.3	MySQL Slave

MySQL Master

1. 下载 mysql 安装包

   服务器支持访问互联网

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

   服务器不支持访问互联网

   # mysql 安装包文件下载链接，下载完成后上传到部署服务器
   https://pdpublic.mingdao.com/private-deployment/offline/common/mysql-8.4.10-linux-glibc2.17-x86_64.tar.xz

2. 解压至安装目录

   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. 创建 mysql 用户

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

4. 创建数据、日志目录并授予权限

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

5. 创建 /etc/my.cnf 配置文件

   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. 配置 systemd 管理文件

   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. 初始化

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

8. 启动 mysql 并加入开机自启动

   systemctl daemon-reload
   systemctl enable mysql
   systemctl start mysql

9. 修改 mysql 密码

   /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;

   • 命令中修改的 root 用户密码为 123456，实际部署必须修改为强口令
   • 若密码中包含特殊字符，仅允许 - 或 _，禁止使用 @ ! # & 等字符，以避免兼容性问题

10. 使用新设置的密码，通过以下命令测试数据库连接是否成功

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

MySQL Slave

1. 下载 mysql 安装包

   服务器支持访问互联网

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

   服务器不支持访问互联网

   # mysql 安装包文件下载链接，下载完成后上传到部署服务器
   https://pdpublic.mingdao.com/private-deployment/offline/common/mysql-8.4.10-linux-glibc2.17-x86_64.tar.xz

2. 解压至安装目录

   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. 创建 mysql 用户

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

4. 创建数据、日志目录并授予权限

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

5. 创建 /etc/my.cnf 配置文件

   主从节点的 server-id 必须唯一，本从库使用 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. 配置 systemd 管理文件

   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. 初始化

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

8. 启动 mysql 并加入开机自启动

   systemctl daemon-reload
   systemctl enable mysql
   systemctl start mysql

9. 修改 mysql 密码

   /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;

   • 命令中修改的 root 用户密码为 123456，实际部署必须修改为强口令
   • 若密码中包含特殊字符，仅允许 - 或 _，禁止使用 @ ! # & 等字符，以避免兼容性问题

10. 使用新设置的密码，通过以下命令测试数据库连接是否成功

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

配置主从

1. 登录 master 节点，配置主从同步用户

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

   • 命令中新建的主从同步用户为 repl，密码为 123456，实际部署必须修改为强口令
   • 若密码中包含特殊字符，仅允许 - 或 _，禁止使用 @ ! # & 等字符，以避免兼容性问题

2. 登录 slave 节点，配置启用主从

   /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;

   • change replication source to 语句中 source_host 的地址注意替换为部署时实际的 source 服务器 IP
   • source_log_file、source_log_pos 的值为在 source 节点执行 show binary log status\G 看到的输出，如有不同请以实际部署时为准

3. 检查主从同步状态

   show replica status\G
   
   # 输出结果中 Replica_IO_Running 与 Replica_SQL_Running 均为 Yes 代表主从同步正常