安装

centos 下安装

/etc/sysconfig/network-scripts/ifcfg-ens33 配置centos 网络

下载 'wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar'
解压 tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar 包含如下

mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-5.7.28-1.el7.x86_64.rpm
mysql-community-test-5.7.28-1.el7.x86_64.rpm
mysql-community-common-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-client-5.7.28-1.el7.x86_64.rpm
mysql-community-server-5.7.28-1.el7.x86_64.rpm

检查删除 mariadb

[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@localhost ~]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
[root@localhost ~]# rpm -qa | grep mariadb

强制安装 --nodeps --force

 rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
 rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
 rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm --nodeps --force
 rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
 rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
 rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm

初始化 MySQL

mysqld --initialize --user=mysql

查看默认密码

cat /var/log/mysqld.log

2021-02-28T15:00:18.447380Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-02-28T15:00:19.895801Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-02-28T15:00:20.077652Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-02-28T15:00:20.147587Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: acf21eb8-79d5-11eb-a406-00163e0c5e60.
2021-02-28T15:00:20.150256Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-02-28T15:00:21.071202Z 0 [Warning] CA certificate ca.pem is self signed.
2021-02-28T15:00:21.268681Z 1 [Note] A temporary password is generated for root@localhost: 7kpxr<L6/Eru

启动 MySQL

systemctl start mysqld.service

查看启动状态

[root@hc-aliyun-cloud ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2021-02-28 23:01:46 CST; 26s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 98081 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 98055 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 98084 (mysqld)
    Tasks: 27 (limit: 11498)
   Memory: 238.1M
   CGroup: /system.slice/mysqld.service
           └─98084 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Feb 28 23:01:05 hc-aliyun-cloud systemd[1]: Starting MySQL Server...
Feb 28 23:01:46 hc-aliyun-cloud systemd[1]: Started MySQL Server.

[root@hc-aliyun-cloud ~]# mysql -uroot -p
mysql> set password=password('root')

关闭防火墙

[root@hc-aliyun-cloud ~]# systemctl stop iptables;
Failed to stop iptables.service: Unit iptables.service not loaded.
[root@hc-aliyun-cloud ~]# systemctl stop firewalld
[root@hc-aliyun-cloud ~]# systemctl disable firewalld.service

配置主从复制

主库配置

修改 /etc/my.cnf 开启 bin-log

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log_bin=mysql-bin
server-id=1
sync-binlog=1
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

bin-log 部分

log_bin=mysql-bin
server-id=1
sync-binlog=1
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys

重启

[root@hc-aliyun-cloud ~]# systemctl restart mysqld.service

授权 MySQL 主库库权限、刷新并验证

mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                          | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------+-------------------+
| mysql-bin.000001 |      869 |              | performance_schema,information_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)

重库配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
server-id=2
# 开启 relay log
relay_log=mysql-relay-bin
# 设定只读
read_only=1
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

查看冲库状态被设置同步


mysql> show slave status;
Empty set (0.00 sec)

mysql> change master to master_host='39.105.125.98' ,master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=869;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
-- 开启从库
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

--- 查看重库状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 39.105.125.98
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 869
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 869
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

克隆的虚拟机 server_uuid 可能相同

查看 server_id/server_uuid

show variables like '%server%id%';

删除 auto.cnf 文件再 my.cnf 文件 datadir=/var/lib/mysql 目录下

查看半同步状态

cat /var/log/mysqld.log

本文由 chaoohuua 创作，采用知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: 2025/04/13 12:28

MySQL 主从复制

安装

centos 下安装

配置主从复制

主库配置

重库配置