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

SQL Server container with Pure Storage Docker volumes

Image
SQL Server on Docker with Pure Storage Persistent Volumes Docker and containers have become an integral part of virtualisation. Having the ability to spin up containers has almost instantly transformed the way we build, develop and manage applications. Many of us have been using some sort of virtualisation for decades as this is not a new concept. Due to their speed, simplicity and automation, container usage has garnered substantial popularity and are becoming ubiquitous in the field of virtualisations. So why use containers ? Decouple application runtime from infrastructure. Runs instantly. Process and user isolation for application multi-tenancy. Key building blocks for microservices. But what happens when you delete the container ? The main issue in deleting a container is that you delete the writeable layer and any data that was there, hence no data persistence. Docker volumes provide us a way to decouple your application from its state, to the point where you c...
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

Cloning SQL Server Containers With Pure Storage Docker Volumes

Image
In my first blog  https://bannaych.blogspot.com/2020/04/mssql-server-on-docker-with-pure.html  I showed you how we can create SQL Server containers using the Pure Storage Docker plugin. In this blog I will show you how we can leverage the Pure Storage Docker plugin to instantly create a clone volume from the source volume, then use Pure Storage crash consistent snapshots to refresh the clone volume. Crash consistent snapshots provide us with a point in time copy of the volume/database, because we use metadata pointers to the original blocks on disk, the snapshots are created instantly with no initial space consumption, then those snapshots can be used to refresh volumes or create new ones. The big advantage of using snapshots is that we no longer need to backup and restore large databases to provision and refresh TEST/DEV/UAT environments. In the world of DevOps being agile is imperative and Pure Storage space efficient snapshots are key to maximising productivity of database ...
Read more