MySQl Replication Setup

MySQL Replication Setup


To stop mysql asking you for root password do the following
edit the .my.cnf in the /root dir and add
[client]
user=root
password=password


  • Turn off firewalls or add firewall rules
    • systemctl stop firewalld
    • systemctl disable firewalld
  • Set permissive mode for SELINUX
    • setenforce permissive
    • edit the /etc/selinux/config file and change to "SELINUX=permissive"
  • Install mysql on Master Server ( Repo will differ for mysql version, downloading the latest repo will install latest mysql version 8 )
    • yum install mysql-community-server
  • Enable mysql
    • systemctl enable mysql
    • systemctl start mysql
  • Run mysql secure installation
    • mysql_secure_installation
  • login to mysql
    • ( Note if using mysql 8 get the root passwd from: grep 'temporary password' /var/log/mysqld.log
    • mysql -uroot -p”password"
    • show schemas;
  • create databases db1 and db2
    • create database db1;
    • create database db2;
  • create table in each database
    • use db1;
    • create table tb1 ( name varchar(10));
    • use db2;
    • create table tb2 ( name1 varchar(10));
  • Insert data into both tables
    • insert into db1.tb1 values (‘chris’);
    • insert into db2.tb2 values (‘george’);
  • edit /etc/my.cnf files
    • under the [mysqld] section add
    • server-id=1
    • log-bin=mysql-bin
  • log into master and create replication user called repl
    • > grant replication slave on *.* to rpl@‘ip-address-of-slave’ identified by ‘repl’;
    • fluch privileges;
    • IF Using MySQL 8 use the following procedure
    • create user rpl@192.168.0.10 identified by 'password';
    • grant replication slave on *.* to rpl_user@slave-ip;
  • restart master mysql
    • systemctl restart mysqld;
  • log back in to mysql
    • show master status\G
    • flash table to read lock;
  • dump the db1 database to import into the slave
    • mysqldump db1 > db1.sql
  • copy the dump file to the slave server
    • scp db1.sql root@slave:/root
    • show slave status\G
  • Install mysql on the slave server same steps as above
  • create the db1 database on the slave server
    • mysql -e “create database db1"
  • import the db1 database dump
    • mysql db1 < /root/db1.sql
    • mysql -e “show schemas"
  • log into mysql on slave
    • use db1;
    • show tables;
    • select * from tb1;
  • edit the /etc/my.cnf file on the slave and add the following under the [mysqld]
    • server-id=2
    • replicate-wild-do-table=db1.%   ( means everything under the db1 database )
  • restart mysqld
    • systemctl restart mysqld
  • log into mysql and set the master server on the slave CHANGE MASTER TO MASTER_HOST='master-ip',
    • > change master to  ( IF USING MYSQL 8 use the following syntax )
    • master_host=‘master’,
    • master_user=‘rpl’,
    • master_password=‘rpl’,
    • master_log_file=‘mysql-bin.000001’,
    • master_log_pos=245;
    • > start slave;
  • show status of slalve
    • show slave status\G
    • show processlist;
  • log back in master and add data into the table in db1
    • insert into db1.tb1 values(‘jacks’);
  • log into slave and check
    • use db1;
    • select * from tb1;
  • check slave status
  • mysql> show slave status\G
  • *************************** 1. row ***************************
  •                Slave_IO_State: Waiting for master to send event
  •                   Master_Host: 192.168.0.112
  •                   Master_User: rpl_user
  •                   Master_Port: 3306
  •                 Connect_Retry: 60
  •               Master_Log_File: mysql-bin.000003
  •           Read_Master_Log_Pos: 707
  •                Relay_Log_File: mysql2-relay-bin.000002
  •                 Relay_Log_Pos: 874
  •         Relay_Master_Log_File: mysql-bin.000003
  •              Slave_IO_Running: Yes
  •             Slave_SQL_Running: Yes
  •               Replicate_Do_DB:
  •           Replicate_Ignore_DB:
  •            Replicate_Do_Table:
  •        Replicate_Ignore_Table:
  •       Replicate_Wild_Do_Table: db1.%
  •   Replicate_Wild_Ignore_Table:
  •                    Last_Errno: 0
  •                    Last_Error:
  •                  Skip_Counter: 0
  •           Exec_Master_Log_Pos: 707
  •               Relay_Log_Space: 1083
  •               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: 0
  • Master_SSL_Verify_Server_Cert: No
  •                 Last_IO_Errno: 0
  •                 Last_IO_Error:log into master and create replication user called repl
  •                Last_SQL_Errno: 0
  •                Last_SQL_Error:
  •   Replicate_Ignore_Server_Ids:
  •              Master_Server_Id: 1
  •                   Master_UUID: ba2cb2ce-89d5-11e9-b291-0800271319d5
  •              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

Using Portainer to Manage and Deploy Oracle Containers with Pure Storage

Image
  In this blog I will show you how can we can install Portainer ( A  platform-agnostic tool for managing containerized applications ) to manage Oracle database containers and persistent volumes running Pure Storage FlashArray   Install Portainer Portainer runs as container, so installing it is just like installing any other container on Docker. Note: Docker and the Oracle container image is already installed - if you want more detail on that check out - http://bannaych.blogspot.com/2021/02/oracle-19c-database-clones-using-pure.html Lets create a persistent volume to run Portainer on, using the Pure Docker plugin # docker volume create port_vol port_vol Lets create the Portainer container # docker run -d -p 8000:8000 -p 9000:9000 --name=portainer --restart=always \ -v /var/run/docker.sock:/var/run/docker.sock -v port_vol:/data \ portainer/portainer-ce Lets confirm the new Portainer container is running # docker ps CONTAINER ID IMAGE ...
Read more

Getting Started with MySQL Shell , The Oracle MySQL Kubernetes Operator and Portworx

Image
Overview In this blog I will show you how to use the MySQL Shell to manage and create a clustered database on an InnoDB MySQL cluster which has been provisioned using the Oracle MySQL Kubernetes Operator, and Portworx to provide the data management layer and persistent volumes The benefit of using Portworx is we can create custom storage classes defining storage attributes such as replication factors, snapshot policies, dynamic PVC creation for stateful applications and intelligent pod placement. This blog will not go into the details on how to setup Kubernetes, Portworx or the Operator, my Colleague Ron Ekins has written an excellent blog on the installation process. https://ronekins.com/2021/08/31/getting-started-with-the-oracle-mysql-kubernetes-operator-and-portworx/ Environment * Kubernetes 1.21 * Portworx 2.8 * MySQL operator installed https://github.com/mysql/mysql-operator * MySQL Shell Let's check to make sure our operator is up and running Let's check to make sure our...
Read more

Oracle 19c Database clones using the Pure Storage Docker plugin

Image
  Docker and containers have become a integral part of virtualization, being able to spin up containers almost instantly have transformed the way be build, develop and manage applications. In this blog I will show you how we can rapidly create Oracle 19c container clones using the Pure Storage Docker plugin with persistent volumes and the new Oracle 19c container image on the Oracle Container Registry https://container-registry.oracle.com/pls/apex/f?p=113:4:2886566670416#     Installing the Pure Plugin ( Latest version is 3.10 ) 12:24:30 root@docker ~ # docker plugin install purestorage/docker-plugin:v3.10 --alias pure --grant-all-permissions Enter the Array Configuration in the /etc/pure-docker-plugin/pure.json file  {      "FlashArrays":[          {              "MgmtEndPoint":“192.168.111.130",              "APIToken":" 476d58bc-3c91-0d10-1b9e-0f31058c462...
Read more