MySQL 8.0 : Replication

     
Configure MySQL Replication settings. This configuration is general Master-Slave settings.

 
[1]. On all Hosts, Install and Start MySQL Server, refer to here.


[2]. On Master Host, Configure SSL/TLS setting.

 
It's not requirements for Replication but some warnings is shown when configure Replication setting because a user's password for Replication is sent with plain text.

 
[3]. On Master Host, change settings and also create users for replication and clone.

 
[root@www ~]# vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
# add follows in [mysqld] section : get binary logs

log-bin=mysql-bin
# define server ID (uniq one)

server-id=101
# enable clone plugin

plugin-load=mysql_clone.so
[root@www ~]# systemctl restart mysqld

[root@www ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

# create replication user (set any password for [password] section)
mysql> create user 'repl_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.10 sec)

mysql> grant replication slave on *.* to repl_user@'%';
Query OK, 0 rows affected (0.05 sec)

# create clone user (set any password for [password] section)
mysql> create user 'clone_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.07 sec)

mysql> grant backup_admin on *.* to 'clone_user'@'%';
Query OK, 0 rows affected (0.06 sec)

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

mysql> exit
Bye

[4]. On Slave Host, change settings.

 
[root@node01 ~]# vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
# add follows in [mysqld] section : get binary logs

log-bin=mysql-bin
# define server ID (uniq one)

server-id=102
# enable clone plugin

plugin-load=mysql_clone.so
# read only yes

read_only=1
# define own hostname

report-host=node01.srv.world
[root@node01 ~]# systemctl restart mysqld

[root@node01 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

# create clone user (set any password for [password] section)
mysql> create user 'clone_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.07 sec)

mysql> grant clone_admin on *.* to 'clone_user'@'%';
Query OK, 0 rows affected (0.06 sec)

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

mysql> exit
Bye

[5]. On Slave Host, Run Clone job to copy data on Master Host and Start replication.
After starting replication, make sure replication works normally to create test database or insert test data and so on.

 
[root@node01 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

# [set global clone_valid_donor_list = (Master Host IP address:port)]
mysql> set global clone_valid_donor_list = '10.0.0.31:3306';
Query OK, 0 rows affected (0.00 sec)

# start Clone
mysql> clone instance from clone_user@10.0.0.31:3306 identified by 'password';
Query OK, 0 rows affected (3.01 sec)

# confirm clone status
# Ok if status is [Completed]
mysql> select ID,STATE,SOURCE,DESTINATION,BINLOG_FILE,BINLOG_POSITION from performance_schema.clone_status;
+------+-----------+----------------+----------------+------------------+-----------------+
| ID   | STATE     | SOURCE         | DESTINATION    | BINLOG_FILE      | BINLOG_POSITION |
+------+-----------+----------------+----------------+------------------+-----------------+
|    1 | Completed | 10.0.0.31:3306 | LOCAL INSTANCE | mysql-bin.000001 |            1345 |
+------+-----------+----------------+----------------+------------------+-----------------+
1 row in set (0.01 sec)

mysql> change master to
    -> master_host='10.0.0.31',             # Master Host IP address
    -> master_ssl=1,                        # enable SSL connection
    -> master_log_file='mysql-bin.000001',  # specify [BINLOG_FILE] value it is displayed above
    -> master_log_pos=1345;           # specify [BINLOG_POSITION] value it is displayed above
Query OK, 0 rows affected (0.35 sec)

# start replication
# specify replication user and password for [user/password] value
mysql> start slave user='repl_user' password='password';
Query OK, 0 rows affected, 1 warning (0.07 sec)

# show status
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.31
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1345
               Relay_Log_File: node01-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            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: 1345
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
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: 101
                  Master_UUID: 974ef6ea-1180-11ea-9fd7-52540085998e
             Master_Info_File: mysql.slave_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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

Comments

Post a Comment

Popular posts from this blog

Java : Variables Declaring

[1]. Java Variables Variables are containers for storing data values. In Java, there are different types of variables, for example: String - stores text, such as "Hello". String values are surrounded by double quotes int - stores integers (whole numbers), without decimals, such as 123 or -123 float - stores floating point numbers, with decimals, such as 19.99 or -19.99 char - stores single characters, such as 'a' or 'B'. Char values are surrounded by single quotes boolean - stores values with two states: true or false [2]. Declaring (Creating) Variables To create a variable, you must specify the type and assign it a value: Syntax type variable = value ; Where type is one of Java's types (such as int or String), and variable is the name of the variable (such as x or name). The equal sign is used to assign values to the variable. To create a variable that should store text, look at the following example: Example Create a variable called  name  of type  Stri...
Read more

Install DNF in RHEL/CentOS 7

  Install DNF in RHEL/CentOS 7 DNF stands for Dandified yum. DNF is a software package manager for RPM-based Linux distributions such as Fedora, RHEL and CentOS. It is the next upcoming major version of Yum. DNF is first introduced in Fedora and has replaced to become the default package manager of the Fedora distributions. DNF is same as Yum that installs, updates and removes packages on RPM bas4ed Linux systems. DNF is introduced for improving the bottlenecks of Yum such as performance, Memory usages, Dependency resolution, speed, and some other factors. The latest stable release of DNF is 1.0 and it is written in Python.   Installation of DNF in RHEL/CentOS 7 1) To install DNF on RHEL/CentOS 7 systems, you need to set up and enable epel YUM REPO before installing DNF. # yum install epel-release 2) Install DNF # yum install DNF 3) You can now start to run commands using DNF. To view the man page you can use the following command: # dnf –help
Read more

SQL Self JOIN

SQL Self JOIN A self JOIN is a regular join, but the table is joined with itself. Self JOIN Syntax SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; T1 and T2 are different table aliases for the same table.
Read more