IP

主机名

系统版本

配置

192.168.203.136

master

CentOS 7.6

2C2G20G

192.168.203.137

slave

CentOS 7.6

2C2G20G

1、准备环境

查看系统版本、卸载系统自带mariadb、并分别设置主机名


[root@localhost ~]# cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core) 
[root@localhost ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@localhost ~]# yum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y
[root@localhost ~]# hostnamectl set-hostname master

2、安装数据库


[root@master ~]# wget https://repo.mysql.com//mysql84-community-release-el7-1.noarch.rpm
[root@master ~]# rpm -i mysql84-community-release-el7-1.noarch.rpm 
[root@master ~]# yum makecache
[root@master ~]# yum install mysql mysql-server -y

修改master节点上配置文件,[mysqld]下面添加下面两行,server_id保持唯一


[root@master ~]# cat /etc/my.cnf
....
[mysqld]
log_bin=binlog           
server-id=100
....

修改slave节点上配置文件


[root@slave ~]# cat /etc/my.cnf
....
[mysqld]
log_bin=binlog           
server-id=101
....

两台机器都启动mysql服务

systemctl start mysqld

查看每台机器初始临时密码

grep password /var/log/mysqld.log 

分别用上面的密码在两台机器上执行安全脚本

[root@master ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done! 

3、主从配置

在master节点上登录数据库创建复制用户并授权


create user 'repl'@'192.168.203.%' identified by 'P@ssw0rd';
grant replication slave on *.* to 'repl'@'192.168.203.%';
flush privileges;
show binary log status;
    [root@master ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 14
    Server version: 8.4.5 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2025, Oracle and/or its affiliates.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> create user 'repl'@'192.168.203.%' identified by 'P@ssw0rd';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> grant replication slave on *.* to 'repl'@'192.168.203.%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show binary log status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000002 |     1690 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    在slave节点上登录数据库并配置

    
    CHANGE REPLICATION SOURCE TO
       SOURCE_HOST='192.168.203.136',     //修改为master节点ip
       SOURCE_USER='repl',                //复制用户
       SOURCE_PASSWORD='P@ssw0rd',        //复制用户的密码
       SOURCE_LOG_FILE='binlog.000002',   //上图中的file字段
       SOURCE_LOG_POS=1690,               //上图中的Position字段
       SOURCE_SSL=0,                     //参考文末来源[2]
       GET_SOURCE_PUBLIC_KEY=1;          //参考文末来源[2]
    mysql> CHANGE REPLICATION SOURCE TO
        ->    SOURCE_HOST='192.168.203.136',
        ->    SOURCE_USER='repl',
        ->    SOURCE_PASSWORD='P@ssw0rd',
        ->    SOURCE_LOG_FILE='binlog.000002',
        ->    SOURCE_LOG_POS=1690,
        ->    SOURCE_SSL=0,
        ->    GET_SOURCE_PUBLIC_KEY=1;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    
    mysql> start replica;
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> show replica status\G;
    *************************** 1. row ***************************
                 Replica_IO_State: Waiting for source to send event
                      Source_Host: 192.168.203.136
                      Source_User: repl
                      Source_Port: 3306
                    Connect_Retry: 60
                  Source_Log_File: binlog.000002
              Read_Source_Log_Pos: 1690
                   Relay_Log_File: slave-relay-bin.000002
                    Relay_Log_Pos: 325
            Relay_Source_Log_File: binlog.000002
               Replica_IO_Running: Yes
              Replica_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_Source_Log_Pos: 1690
                  Relay_Log_Space: 536
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Source_SSL_Allowed: No
               Source_SSL_CA_File: 
               Source_SSL_CA_Path: 
                  Source_SSL_Cert: 
                Source_SSL_Cipher: 
                   Source_SSL_Key: 
            Seconds_Behind_Source: 0
    Source_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Source_Server_Id: 100
                      Source_UUID: 2f65ea30-4c17-11f0-984f-000c29ac3890
                 Source_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
        Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
               Source_Retry_Count: 10
                      Source_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Source_SSL_Crl: 
               Source_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Source_TLS_Version: 
           Source_public_key_path: 
            Get_Source_public_key: 1
                Network_Namespace: 
    1 row in set (0.00 sec)

    可以看到Replica_IO_Running和Replica_SQL_Running都为yes

    4、验证

    在两台机器上查看现有数据库

    
    master节点
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    slave节点
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)

    master节点上创建数据库、表、并插入数据​​​​​​​

    mysql> create database testdb;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> use testdb;
    Database changed
    mysql> create table user(id int primary key, name varchar(50));
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> insert into user values(1, 'Tom');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | testdb             |
    +--------------------+

    slave节点上查看​​​​​​​

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | testdb             |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use testdb;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from user;
    +----+------+
    | id | name |
    +----+------+
    |  1 | Tom  |
    +----+------+
    1 row in set (0.00 sec)

    可以看到,数据已经同步过来

    来源:

    [1] https://dev.mysql.com/doc/refman/8.0/en/replication-howto-masterbaseconfig.html

    [2] https://dev.mysql.com/doc/refman/8.0/en/change-replication-source-to.html

    更多推荐