Archive

Articles taggués ‘replication’

MySQL database replication with Linux

03/04/2019 Comments off

MySQL database replication with Linux

Database replication is a technique where a given database is copied to one or more locations, so that the reliability, fault-tolerance or accessibility of the database can be improved. Replication can be snapshot-based (where entire data is simply copied over to another location), merge-based (where two or more databases are merged into one), or transaction-based (where data updates are periodically applied from master to slaves).

MySQL replication is considered as transactional replication. To implement MySQL replication, the master keeps a log of all database updates that have been performed. The slave(s) then connect to the master, read individual log entries, and perform recorded updates. Besides maintaining a transaction log, the master performs various housekeeping tasks, such as log rotation and access control. When new transactions occur and get logged on the master server, the slaves commit the same transactions on their copy of the master database, and update their position in the master server’s transaction log. This master-to-slave replication process is done asynchronously, which means that the master server doesn’t have to wait for the slaves to catch up. If the slaves are unable to connect to the master for a period of time, they will download and execute all pending transactions when connectivity is re-established.

Database replication allows one to have an exact copy of a live database of a master server at another remote server (slave server) without taking the master server offline. In case the master server is down or having any trouble, one can temporarily point database clients or DNS resolver to the slave server’s IP address, achieving transparent failover. It is must be noted that MySQL replication is not a backup solution. For example, if an unintended DELETE command gets executed in the master server by accident, the same transaction will mess up all slave servers.

In this article, we will demonstrate master-slave based MySQL replication on two Linux computers. Let’s assume that the IP addresses of master/slave servers are 192.168.2.1 and 192.168.2.2, respectively.

Setting up a Master MySQL Server

This part will explain the steps needed on the master server.

First, log in to MySQL, and create test_repl database.

$ mysql -u root -p
mysql> CREATE DATABASE test_repl;

Next, create a table inside test_repl database, and insert three sample records.

mysql> USE test_repl;
mysql> CREATE TABLE employee (EmployeeID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255));
mysql> INSERT INTO employee VALUES(1,"LastName1","FirstName1","Address1","City1"),(2,"Lastname2","FirstName2","Address2","City2"),(3,"LastName3","FirstName3","Address3","City4");

After exiting the MySQL server, edit my.cnf file using your favorite text editor. my.cnf is found under /etc, or /etc/mysql directory.

# nano /etc/my.cnf

Add the following lines under [mysqld] section.

[mysqld]
id=1
log-bin=master-bin.log
do-db=test_repl
innodb_flush_log_at_trx_commit=1
sync_binlog=1

The server-id option assigns an integer ID (ranging from 1 to 2^23) to the master server. For simplicity, ID 1 and 2 are assigned to the master server and the slave server, respectively. The master server must enable binary logging (with log-bin option), which will activate the replication. Set the binlog-do-db option to the name of a database which will be replicated to the slave server. The innodb_flush_log_at_trx_commit=1 and sync_binlog=1options must be enabled for the best possible durability and consistency in replication.

After saving the changes in my.cnf, restart mysqld daemon.

# systemctl restart mysqld

or:

# /etc/init.d/mysql restart

Log in to the master MySQL server, and create a new user for a slave server. Then grant replication privileges to the new user.

mysql> CREATE USER repl_user@192.168.2.2;
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.2.2 IDENTIFY BY 'repl_user_password';
mysql> FLUSH PRIVILEGES;

A new user for the slave server is repl_user, and its password is repl_user_password. Note that the master MySQL server must not bind to the loopback interface since a remote slave server needs to log in to the master server as repl_user. Check this tutorial to change MySQL server’s binding interface.

Finally, check the master server status by executing the following command on the server.

mysql> SHOW MASTER STATUS;

18157192466_b3cc2d5ced_o

Please note that the first and second columns (e.g., master-bin.000002 and 107) will be used by the slave server to perform master-to-slave replication.

Lire la suite…

MySQL Master / Slave Replication

23/03/2019 Comments off

Source: Uptime Made Easy

Master Slave MySQL Replication Summary

Master / Slave replication in MySQL is a great way to store an exact replica of your database on another machine in another location as part of a disaster recovery plan.  Before setting up Master / Slave replication there are a few things to remember.

  • Writes – Writes to the master database should make it to the slave.  But writes to the slave will not make it to the master.  If you do write records to the slave database directly, be prepared to have to either recreate the records or back them up separately and recover them if the replication breaks.  Many times the only way to get the databases to replicate again is to backup the master and recover it over the top of the slave deleting anything that was in the slave database before.
  • Broken Replication – Writes made directly to the slave can cause the replication to break due to duplicate key rows, etc..  Always write to the master.
  • Reads – Reads should be possible from either server.  Many organizations will use replication so as to create another database to read from thereby taking the load of all of their select statements and reports off the master server.

 

MySQL Replication schemeMaster Slave MySQL Replication

Steps to Setup MySQL Master / Slave Replication

Prerequisites

We will be assuming that the following prerequisites are done prior to beginning the steps listed below:

  • MySQL has been installed on both the master and the slave servers
  • The slave server is able to communicate directly to the mysqld port (typically 3306) on the master server, meaning that there is no firewall, routing, NAT or other problems preventing communication.
  • You have an administrator MySQL user that can create users on both the master and the slave machines.
  • You have permissions to edit the /etc/my.cnf files on both machines and enough privileges to restart mysql.

That should be it!  Let’s begin setting it up.

Lire la suite…

MySQL – Migrate Users from Server to Server

22/03/2019 Comments off

mysql migrate usersSometimes we need to migrate our databases to a new MySQL server.  It is easy to move the databases, but without the users and their permissions, our new databases would be worthless.  Below is a step-by-step on migrating MySQL users to a new MySQL server

Step 1 – Create a Query List That We Can Use to Get Grants for All Users

I use these options so that I wouldn’t get any formatting characters that I would have to manually delete later.

  • -N skip column names in the output
  • -p password – Asks me to type the password so nobody can get it from the command line history
  • -s  silent mode – less formatting output that we don’t want like “|” and “-“

So, let’s get a list of the users in a query that we can use to get the grants.  Our query will be output into the “myfile” file

$ mysql -uroot -N -p -s > myfile
Enter password:
select Distinct CONCAT(‘show grants for `’, user, ‘`@`’, host, ‘`;’) as query from mysql.user;
quit

If we want to see what our query file look like, we can take a quick peek:

[root@classes-dev-mysql ~]# cat myfile
show grants for `user1`@`%`;
show grants for `user2`@`%`;
show grants for `user3`@`10.%`;
show grants for `user4`@`10.%`;
show grants for `jeff`@`10.%`;

Step 2 – Create the MySQL Grant File

We don’t have quite what we want and need yet.  We are looking for a query that will create all of our users on the new MySQL server.  We need to run the query that we just created and it will give us the query that we will use later to create the users.  It will create our grant permission statements in a file named “grantfile

[root@classes-dev-mysql ~]# mysql -uroot -N -p -s -r < myfile > grantfile
Enter password:

We can take a peek at what our grantfile contains:

$ cat grantfile
GRANT USAGE ON *.* TO ‘user1’@’%’ IDENTIFIED BY PASSWORD ‘5ea9af6g6t27032f’
GRANT ALL PRIVILEGES ON `database1`.* TO ‘user1’@’%’
GRANT USAGE ON *.* TO ‘user2’@’10.%’ IDENTIFIED BY PASSWORD ‘2a123b405cbfe27d’
GRANT SELECT ON `database1`.`table1` TO ‘user2’@’10.%’GRANT ALL PRIVILEGES ON *.* TO ‘user3’@’10.%’ IDENTIFIED BY PASSWORD ‘753af2za1be637ea’
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO ‘user3’@’10.%’ IDENTIFIED BY PASSWORD ’08ad9be605rfgcb’…

Step 3 – Create Users and Grant MySQL Permissions on the New MySQL Machine

Now we are done working on the source machine.  We need to copy our file named “grantfile” over to the new machine.

$ scp grantfile myuser@mysql2.uptimemadeeasy.com:/home/myuser

Next, we login to the destination or the new MySQL machine that we are building and run the “grantfile” in MySQL to create our users on the new MySQL machine.

$ mysql -uroot -p < ./grantfile

That’s it.  As long as our databases are named the same in the new MySQL, our users should be ready to use the copy of the databases in the new MySQL machine.

Source: Uptime Made Easy

Installing a high availability web server cluster on Ubuntu 12.04 LTS using HAProxy, HeartBeat and Nginx

14/02/2019 Comments off

How to set-up a high-availability cluster

Here are a few notes about how to set-up a high-availability web server farm using Ubuntu 12.04 LTS using a whole load of awesome software (HAProxy, HeartBeat, Watchdog and Nginx)

The setup

In my setup I have five virtual machines, these are named and used for the following:-

haproxy1 – Our first proxy (master)/load-balancer (running HAProxy, HeartBeat and Watchdog) [IP address: 172.25.87.190]
haproxy2 – Our second proxy (failover)/load-balancer (running HAProxy, HeartBeat and Watchdog) [IP address: 172.25.87.191]
web1 – Our first web server node (running nginx) [IP address: 172.25.87.192]
web2 – Our second web server node (running nginx) [IP address: 172.25.87.193]
web3 – Our third web server node (running nginx) [IP address: 172.25.87.194]

The servers are connected in the following way:-

thesetup

In my next post I will also explain how to configure the web servers to point to a backend shared storage cluster (using NFS) and a MySQL cluster server to have a truly highly available web hosting platform.

Lire la suite…

How To Create a High Availability Setup with Heartbeat and Floating IPs on Ubuntu 14.04

30/01/2019 Comments off

Source: Digital Ocean – Mitchell Anicas

Introduction

Heartbeat is an open source program that provides cluster infrastructure capabilities—cluster membership and messaging—to client servers, which is a critical component in a high availability (HA) server infrastructure. Heartbeat is typically used in conjunction with a cluster resource manager (CRM), such as Pacemaker, to achieve a complete HA setup. However, in this tutorial, we will demonstrate how to create a 2-node HA server setup by simply using Heartbeat and a DigitalOcean Floating IP.

If you are looking to create a more robust HA setup, look into using Corosync and Pacemaker or Keepalived.

Goal

When completed, the HA setup will consist of two Ubuntu 14.04 servers in an active/passive configuration. This will be accomplished by pointing a Floating IP, which is how your users will access your services or website, to point to the primary, or active, server unless a failure is detected. In the event that the Heartbeat service detects that the primary server is unavailable, the secondary server will automatically run a script to reassign the Floating IP to itself via the DigitalOcean API. Thus, subsequent network traffic to the Floating IP will be directed to your secondary server, which will act as the active server until the primary server becomes available again (at which point, the primary server will reassign the Floating IP to itself).

ha-diagram-animated

Note: This tutorial only covers setting up active/passive high availability at the gateway level. That is, it includes the Floating IP, and the load balancer servers—Primary and Secondary. Furthermore, for demonstration purposes, instead of configuring reverse-proxy load balancers on each server, we will simply configure them to respond with their respective hostname and public IP address.

To achieve this goal, we will follow these steps:

  • Create 2 Droplets that will receive traffic
  • Create Floating IP and assign it to one of the Droplets
  • Create DNS A record that points to Floating IP (optional)
  • Install Heartbeat on Droplets
  • Configure Heartbeat to Run Floating IP Reassignment Service
  • Create Floating IP Reassignment Service
  • Test failover

Lire la suite…