Monday, 7 August 2017

28, 'Operation too slow. Less than 1 bytes/sec transfered the last 30 seconds

I was trying to install xtrabackup in one of my virtual servers. I have connected the server via ssh from my local machine. When i was performing the install command it took some time and gave me the timeout error as shown below,

Downloading Packages:
http://repo.percona.com/release/6/RPMS/x86_64/percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm: [Errno 12] Timeout on http://repo.percona.com/release/6/RPMS/x86_64/percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm: (28, 'Operation too slow. Less than 1 bytes/sec transfered the last 30 seconds')
Trying other mirror.
(2/2): rsync-3.0.6-12.el6.x86_64.rpm                                                                                                   | 335 kB     00:00     


Error Downloading Packages:

 percona-xtrabackup-24-2.4.8-1.el6.x86_64: failure: percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm from percona-release-x86_64: [Errno 256] No more mirrors to try.


xtrabackup_failure.png



To fix the above issue we need to increase the timeout value on yum.conf
[root@gtid1 ~]# vi /etc/yum.conf

Add below two values to it  and you will be able to fix the same.

minrate=1
timeout=300

xtrabackup_success.png

Tuesday, 1 August 2017

Error: Package: percona-xtrabackup-24-2.4.8-1.el6.x86_64 (percona-release-x86_64) Requires: libev.so.4()(64bit)

I was trying to install xtrabackup version 2.4.8 on my centos 6.4 which resulted with following errors,

[root@dbtest3 ~]# yum install percona-xtrabackup-24.x86_64
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.dhakacom.com
* extras: mirror.dhakacom.com
* updates: mirror.dhakacom.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup-24.x86_64 0:2.4.8-1.el6 will be installed
--> Processing Dependency: rsync for package: percona-xtrabackup-24-2.4.8-1.el6.x86_64
--> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-24-2.4.8-1.el6.x86_64
--> Running transaction check
---> Package percona-xtrabackup-24.x86_64 0:2.4.8-1.el6 will be installed
--> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-24-2.4.8-1.el6.x86_64
---> Package rsync.x86_64 0:3.0.6-12.el6 will be installed
--> Finished Dependency Resolution
Error: Package: percona-xtrabackup-24-2.4.8-1.el6.x86_64 (percona-release-x86_64)
          Requires: libev.so.4()(64bit)
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

From the above error it is clear that i am missing the following dependencies :-

rsync
libev

Which should be installed along with the xtrabackup installation but it didn’t so i decided to install the both and finally i should be able to install xtrabackup.

rsync was successfully installed,


Screen Shot 2017-07-27 at 11.26.03 AM.png

But when i tried to install libev i was facing the below error,

[root@dbtest3 ~]# yum install libev
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.dhakacom.com
* extras: mirror.dhakacom.com
* updates: mirror.dhakacom.com
Setting up Install Process
No package libev available.
Error: Nothing to do
[root@dbtest3 ~]#

In order to fix this issue i need to install an external repo such as EPEL which will allow me to install libev.

[root@dbtest3 ~]# wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
--2017-07-26 17:13:58--  http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Resolving download.fedoraproject.org... 174.141.234.172, 209.132.181.15, 8.43.85.67, ...
Connecting to download.fedoraproject.org|174.141.234.172|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://mirrors.tuna.tsinghua.edu.cn/epel/6/x86_64/epel-release-6-8.noarch.rpm [following]
--2017-07-26 17:13:59--  https://mirrors.tuna.tsinghua.edu.cn/epel/6/x86_64/epel-release-6-8.noarch.rpm
Resolving mirrors.tuna.tsinghua.edu.cn... 101.6.6.177, 2402:f000:1:416:101:6:6:177
Connecting to mirrors.tuna.tsinghua.edu.cn|101.6.6.177|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 14540 (14K) [application/x-redhat-package-manager]
Saving to: “epel-release-6-8.noarch.rpm”

100%[==================================================================================================================================================================>] 14,540      --.-K/s   in 0s      

2017-07-26 17:14:01 (1008 MB/s) - “epel-release-6-8.noarch.rpm” saved [14540/14540]

[root@dbtest3 ~]#
[root@dbtest3 ~]# ls
anaconda-ks.cfg  epel-release-6-8.noarch.rpm  install.log  install.log.syslog  mysql57-community-release-el6-11.noarch.rpm
[root@dbtest3 ~]#
[root@dbtest3 ~]# yum install epel-release-6-8.noarch.rpm
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.dhakacom.com
* extras: mirror.dhakacom.com
* updates: mirror.dhakacom.com
Setting up Install Process
Examining epel-release-6-8.noarch.rpm: epel-release-6-8.noarch
Marking epel-release-6-8.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:6-8 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================================================================================================================
Package                                           Arch                                        Version                                  Repository                                                     Size
============================================================================================================================================================================================================
Installing:
epel-release                                      noarch                                      6-8                                      /epel-release-6-8.noarch                                       22 k

Transaction Summary
============================================================================================================================================================================================================
Install       1 Package(s)

Total size: 22 k
Installed size: 22 k
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
 Installing : epel-release-6-8.noarch                                                                                                                                                                  1/1
 Verifying  : epel-release-6-8.noarch                                                                                                                                                                  1/1

Installed:
 epel-release.noarch 0:6-8                                                                                                                                                                                 

Complete!
[root@dbtest3 ~]#


After successful installation of  EPEL,  i tried to install libev but faced below issue,

[root@dbtest3 ~]# yum install libev
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Error: Cannot retrieve metalink for repository: epel. Please verify its path and try again
To fix the above issue you need to edit your epel repo and edit it by changing https to hhtp on mirrorlist

vi /etc/yum.repos.d/epel.repo

In the below change https to http on entry mirrorlist

Before :-
[epel]
name=Extra Packages for Enterprise Linux 6 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

After :-
[epel]
name=Extra Packages for Enterprise Linux 6 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
mirrorlist=http://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

After changing the above i was able to install libev,

[root@dbtest3 ~]# rpm -qa | grep libev
libev-4.03-3.el6.x86_64

Screen Shot 2017-07-27 at 11.43.35 AM.png

Finally after installing libev i was able to install xtrabackup successfully.

[root@dbtest3 ~]# rpm -qa | grep xtrabackup
percona-xtrabackup-24-2.4.8-1.el6.x86_64
[root@dbtest3 ~]#







Wednesday, 19 July 2017

MySQL GTID Replication


This blog explains how to set-up a GTID based Master-Slave replication.


About GTID


A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).


GTID is a represented as a pair of coordinates separated by a colon `:`


{source_id:transaction_id}


Where ,


source_id : server's UUID.
transaction_id : sequence number


An GTID looks like


68e889d2-6c60-11e7-9ae5-080027c91509:1

Why GTID?

  • Setting up MySQL replication is so simple now! (forget binlog & position)
  • Consistency is guaranteed between master and slaves.
  • Fail-over process become much easier.






Master side configuration




Do a mysql restart for the changes to take place

[root@gtid1 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@gtid1 ~]#



Create a mysql replication user for the slave to connect with master


GRANT REPLICATION SLAVE ON *.* TO 'gtid_repl'@'gtid2' IDENTIFIED BY 's3cret';



Note : gtid1 --> Master
gtid2 --> Slave

Slave side configuration




Do a mysql restart for the changes to take place

[root@gtid2 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@gtid2 ~]# 


Execute the change master to command

CHANGE MASTER TO MASTER_HOST='gtid1',

  MASTER_PORT=3306,

  MASTER_USER=gtid_repl,     

  MASTER_PASSWORD='s3cret',  
  MASTER_AUTO_POSITION=1;



Start the replication

START SLAVE;




Validate the replication from below command

SHOW SLAVE STATUS\G




Verification :

 On gtid1





On gtid2







Explanation for variables:-


server-id
Unique replication id (must if the server is involved in a replication environment)
log-bin
Enabling binary logging to log all statements that change data
binlog-format
Type of binary logging format in which server needs to store.
gtid-mode
Enable or disable gtid mode
enforce_gtid_consistency
It prevents executing the non-transactionally safe statements
log_slave_updates
Tells the slave to write the updates performed by its SQL thread to its binary log. For gtid we need it to be enabled on both servers else mysql won’t start.
[ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates