Archive

Articles taggués ‘replication’

Réplication MySql Croisée

06/05/2020 Comments off

I► Nous allons voir dans cet article comment mettre en place une réplication croisée MySql en optimisant donc la répartition des charges et la sécurisation des données.

Pour des raisons de simplicité, nous testerons cette config sur le même serveur en utilisant mysqld_multi ( référez vous aux précédents articles )

Ordi2

 

Voici le fichier de conf de MySql

[root@/etc/mysql] cat multi_my.cnf.replicant.double
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password = debian

#——————– Serveur A ——————

[mysqld1]
socket = /tmp/mysql.sock1
port = 3307
pid-file = /var/lib/mysql1/hostname.pid1
datadir = /var/lib/mysql1
language = /usr/share/mysql/french
user = mysql

server-id=1
log-bin = /var/log/mysql/mysql_master_bin.log
binlog_do_db=centrale
replicate-do-db=centrale

master-host = 127.0.0.1
master-port = 3308
master-user = replicant
master-password = replicator

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

auto_increment_increment = 10
auto_increment_offset = 1

#————–Serveur B ———————-

[mysqld2]
socket = /tmp/mysql.sock2
port = 3308
pid-file = /var/lib/mysql2/hostname.pid2
datadir = /var/lib/mysql2
language = /usr/share/mysql/french
user = mysql
server-id=2
log-bin = /var/log/mysql/mysql_master_bin.log
binlog_do_db=centrale
replicate-do-db=centrale
master-host = 127.0.0.1
master-port = 3307
master-user = replicant
master-password = replicator
master-connect-retry=60
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

auto_increment_increment = 10
auto_increment_offset = 2

Important :

Les fichiers de logs doivent être les mêmes et l’utilisateur de réplication doit bien sûr être créé sur les 2 serveurs maîtres.

How to replicate a MySQL database on Linux

06/04/2020 Comments off

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).

How to replicate a MySQL database on Linux

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]
server-id=1
log-bin=master-bin.log
binlog-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=1 options 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;

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…

Réplication MySql avec PhpMyAdmin sur 2 serveurs distincts

05/04/2020 Comments off

Source: Tutoriels Web Linux MySql

I : sur le serveur Maître, configurez la réplication comme suit :

  • Dans l’onglet réplication, choisissez l’option configurer le serveur maître.

Capture1

Editer le fichier /etc/mysql/my.cnf

  • Redémarrez mysql : /etc/init.d/mysql restart
  • Puis faites exécuter dans phpMyAdmin
  • Ajouter un nouvel utilisateur pour la réplication et donner lui tous les privilèges nécessaires

Capture2

CREATE USER ‘replicant’@'localhost’ IDENTIFIED BY ‘***’;
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO ‘replicant’@'localhost’ IDENTIFIED BY ‘***’;

II : sur le serveur esclave, configurez la réplication comme suit :

  • Dans l’onglet réplication, configurez :
  • Vous devrez ajouter le server-id proposé par phpMyAdmin dans /etc/mysql/my.cnf et redémarrez mysql (pensez à ajouter slave-skip-errors=1062,1053 !)
  • puis faire éxécuter dans phpMyAdmin
  • Faites reconfigurer le serveur maître en saisissant les informations du serveur maître

Capture0

Ceci revient à faire en ligne de commande : et qui éditera au final le fichier master.info qui se trouve sur le serveur esclave :

Capture17

  • On obtient alors cet écran :

Capture3

 

On pourra synchroniser les données afin de copier toute la base de données vers le serveur esclave et ensuite démarrer complètement le serveur esclave (ce qui correspond à démarrer d’abord le fil I/O qui lit les requêtes du maître et le splace dans le relay-lo et ensuite le fil mysql qui lit le relay-log et éxécute le sql).

Lire la suite…

5 Steps to Setup MySQL Master Master Replication on Ubuntu 16.04

10/12/2019 Comments off

Source: linoxide.com

The Master-Slave replication in MySQL databases provides load balancing for the databases. But it does not provide any failover scenario. If the Master server breaks, we cannot execute queries directly on the slave server. In addition to load balancing, if we need failover in our scenario, we can setup 2 MySQL instances in Master-Master replication. This article describes how this can be achieved in 5 easy steps on Ubuntu 16.04 server.

In Master master replication, both the servers play the role of master and slave for each other like in the following diagram:

MySQL Master-Master configuration

Each server serves as Master for the other slave at the same time. So if you are familiar with the Master-Slave replication in MySQL, this must be a piece of cake for you.

Lire la suite…

How to Setup MySQL Master Master Replication

09/12/2019 Comments off

MySQL master master replication, also known as “mysql chained replication”, “multi master replication or “mysql daisy chaining replication” is an extension of mysql replication allowing the creation of multiple master servers that can then be masters of multiple slaves. In this post, I demonstrate how to setup mysql master master replication.

In a multi master mysql configuration, bar the first master server, each additional master acts as both a master and slave. Therefore, it is possible to create new masters from each additional slave added.

Take a look at this diagram which helps to illustrate one possible configuration.

mysql master master replication

Lire la suite…

Categories: Bases de données Tags: ,