未加星标

How to set up MariaDB Master-Slave replication with SSL on Ubuntu Linux

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

H ow do I setup Master-slave data replication using MariaDB server on Ubuntu linux 16.04 LTS server? How do I encrypt replication traffic so that the data get encrypted in transit, protecting my data and users from snooping?

Master-slave data replication allows you to copy databases to multiple MariaDB servers. This is useful for backup, data recovery, load balancing and much more. In this tutorial, you will learn how to configure SSL-protected MariaDB replication between a master and slave server. Our sample setup
How to set up MariaDB Master-Slave replication with SSL on Ubuntu Linux

Fig.01: Set Up Master Slave Replication in MariaDB

This tutorial will use the following IP addresses for db1 and db2 servers:

192.168.2.5 (db1) Master MariaDB server 192.168.2.6 (db2) Slave MariaDB server

I am going to assume that both db1 and db2 are brand new servers. There is no existing database on server.

What you need to setup MariaDB master-slave cluster? Minimum two servers (can be a cloud or bare metal boxes) A private network (LAN/VLAN) between servers A VPN between two data center if setting between two IDCs Ubuntu Linux 16.04 LTS on both servers Update your /etc/hosts

First updated your /etc/hosts file as follows on db1 and db2 server:

$ sudo vi /etc/hosts

Edit/append as follows:

192.168.2.5 db1 192.168.2.6 db2

Save and close the file. Test it as follows:

$ ping -c4 db1 $ ping -c4 db2

Step 1 Install MariaDB latest stable version on Ubuntu 16.04 LTS

Type the followingapt-get command on both db1 and db2 server to install MariaDB:

$ sudo apt-get install software-properties-common $ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8 $ sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.lstn.net/mariadb/repo/10.1/ubuntu xenial main' $ sudo apt-get update $ sudo apt-get install mariadb-server mariadb-client

Sample outputs:


How to set up MariaDB Master-Slave replication with SSL on Ubuntu Linux

Fig.02: Installing MariaDB server

Step 2 Secure MariaDB

Type the following command:

$ mysql_secure_installation

Sample outputs:


How to set up MariaDB Master-Slave replication with SSL on Ubuntu Linux

Fig.02: Secure the MariaDB installation

Step 3 Create SSL keys and certificates for MariaDB

Type thefollowing commands on the db1 server only:

$ sudo mkdir -p /etc/mysql/ssl/
$ cd /etc/mysql/ssl/
$ sudo openssl genrsa 2048 > ca-key.pem
## set CA command name to "MariaDB admin" ##
$ sudo openssl req -new -x509 -nodes -days 730 -key ca-key.pem -out ca-cert.pem
## set server certificate command name to "MariaDB server" ##
$ sudo openssl req -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem -out server-req.pem
$ sudo openssl rsa -in server-key.pem -out server-key.pem
$ sudo openssl x509 -req -in server-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
## set client command name to "MariaDB client" ##
$ sudo openssl req -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem -out client-req.pem
$ sudo openssl rsa -in client-key.pem -out client-key.pem
$ sudo openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
$ sudo openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

To avoid any issues you must common name them as follows:

CA common Name : MariaDB admin Server common Name: MariaDB server Client common Name: MariaDB client

Also copy all certificates to the db2 slave server:

$ ssh [email protected] mkdir /tmp/ssl/ $ cd /etc/mysql/ssl/ $ scp * [email protected]:/tmp/ssl/

Step 4 Configure the master MariaDB server

Type the following commands on the db1 master server . You need to edit the /etc/mysql/my.cnf file, run:

$ sudo vi /etc/mysql/my.cnf
Configure the MariaDB client (add in [client] section) to use SSL: ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/client-cert.pem ssl-key=/etc/mysql/ssl/client-key.pem

Set bind address i.e. listen only on db1’s private IP address 192.168.2.5 (make sure you replace the IP 192.168.2.5 with your actual IP address)

bind-address = 192.168.2.5

Mark this server as a Master server. This number must be unique:

server-id = 1

Make sure server has binlogs enabled i.e. make sure log_bin and lob_bin_index are set as follows:

log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index Configure the MariaDB server (add in [mysqld] section) to use SSL: ssl ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem

Save and close the file. Restart MariaDB server, run:

$ sudo systemctl restart mysql.service Configure replication

Type the following command on the master db1 server :

$ mysql

OR

$ mysql -u root -p

You must create a MySQL user the master server (db1). The syntax is:

grant replication slave on *.* TO {username}@'{ip_of_db2_server}' identified by '{password}' REQUIRE SSL;

For example:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'db_slave_usr'@'192.168.2.6' IDENTIFIED BY 'secretePassword' REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
mysql> quit Find out master status

Type the following command at shell:

$ mysql -u root -p

Once the MySQL shell opened, type:

mysql> SHOW MASTER STATUS;

Sample outputs:


How to set up MariaDB Master-Slave replication with SSL on Ubuntu Linux

Fig.03: Find out master status and note down the position

Record the file and position details for the slave server named db2.

Step 5 Configure the slave MariaDB server

Type the following commands on the db2 server . First, move all certificates from /tmp/ssl/ to /etc/mysql/ directory:

$ sudo mv /tmp/ssl/ /etc/mysql/

You need to edit the /etc/mysql/my.cnf file, run:

$ sudo vi /etc/mysql/my.cnf
Configure the MariaDB client (add in [client] section) to use SSL: ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/client-cert.pem ssl-key=/etc/mysql/ssl/client-key.pem

Set bind address i.e. listen only on db2’s private IP address 192.168.2.6 (make sure you replace the IP 192.168.2.6 with your actual IP address)

bind-address = 192.168.2.6

Mark this server as a slave server. This number must be unique:

server-id = 2
Configure the MariaDB server (add in [mysqld] section) to use SSL: ssl ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem

Save and close the file. Restart MariaDB server, run:

$ sudo systemctl restart mysql.service

Type the following command on the slave db2 server:

$ mysql -u root -p

Type the following sql command:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.5', MASTER_USER='db_slave_usr', MASTER_PASSWORD='secretePassword', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=661; MASTER_SSL=1;
mysql> SLAVE START;
mysql> SHOW SLAVE STATUS\G

Now add or delete data on your master server named db1 and it will get replicated on the db2 slave server.

A note about failover

You need to modify your web app written inphp/Perl/python to write data only to the master db1 server. If the master db1 server is down read data from the slave db2 server.

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: UbuntuLinuxMySQLSQLPerlPHPTIPythonVPN
分页:12
转载请注明
本文标题:How to set up MariaDB Master-Slave replication with SSL on Ubuntu Linux
本站链接:http://www.codesec.net/view/558468.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(44)