By Hitesh Jethva, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.
MariaDB is a free, open source and one of the most popular open source relational database management system. It is a drop-in replacement for MySQL intended to remain free under the GNU GPL. You will need to increase the instances of your MariaDB server and replicate the data on multiple servers when your traffic grows. The Master-Slave replication provides load balancing for the databases. It is not used for any failover solution.
There are two ways to replicate data:
Master-Master Replication: In this mode, data to be copied from either server. In other words, perform reads or writes from either server. So whenever one server gets the write request it will sync data to other server. This mode will be very useful when you want the best redundancy.
Master-Slave Replication: In this mode, data changes happen on the master server, while the slave server automatically replicates the changes from the master server. This mode will be best suited for data backups.
In this tutorial, we will learn how to set up MariaDB Master-Slave replication on Alibaba Cloud Elastic Compute Service (ECS) with Ubuntu 16.04.
First, log in to your Alibaba Cloud ECS Console. Create a new ECS instance, choosing Ubuntu 16.04 as the operating system with at least 2GB RAM. Connect to your ECS instance and log in as the root user.
Once you are logged into your Ubuntu 16.04 instance, run the following command to update your base system with the latest available packages.
apt-get update -yBefore starting, you will need to install MariaDB server on both instance. You can install it by running the following command:
apt-get install mariadb-server -yOnce the installation is completed, start MariaDB service and enable it to start on boot time with the following command:
systemctl start mysql
systemctl enable mysqlBy default, MariaDB is not secured. So you will need to secure it first. You can do this by running the following command:
mysql_secure_installationAnswer all the questions as shown below:
Set root password? [Y/n] n
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] yFirst, you will need to edit /etc/mysql/my.cnf and make some changes inside it.
nano /etc/mysql/my.cnfMake the following changes:
[mysqld]
bind-address = 192.168.0.101
server_id=1
log-basename=master
log-bin=/var/log/mysql/mariadb-bin
binlog-format=row
binlog-do-db=masterdbSave and close the file, when you are finished.
Next, restart MariaDB service to apply the chnages:
systemctl restart mysqlNext, log in to MariaDB shell and configure replication:
mysql -u root -pEnter your root password, then stop the slave and create a replication user and set password:
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';Next, flush the privileges:
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;Next, check the master server status:
MariaDB [(none)]> SHOW MASTER STATUS;Output:
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 615 | masterdb | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)Note: Remember the file mariadb-bin.000001 and position number 615.
Next, exit from the MariaDB shell:
MariaDB [(none)]> exit;Next, take a backup of all databases on the master server and transfer it to the slave server.
First, take all database backup with the following command:
mysqldump --all-databases --user=root --password --master-data > alldatabase.sqlNext, transfer alldatabase.sql file to the slave server with the following command:
scp alldatabase.sql root@192.168.0.102:/root/Next, log in to MariaDB console again and unlock the tables:
mysql -u root -p
MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;You will also need to edit /etc/mysql/my.cnf file and make some changes inside it:
nano /etc/mysql/my.cnfMake the following changes:
[mysqld]
bind-address = 192.168.0.102
server-id = 2
replicate-do-db=masterdbSave the file, then restart MariaDB service:
systemctl restart mysqlNext, import the alldatabase.sql which you have transferred from master server:
mysql -u root -p < alldatabase.sql Next, log in to MariaDB shell:
mysql -u root -pEnter your root password, then stop the slave:
MariaDB [(none)]> STOP SLAVE;Next, configure the slave to use the master with the following command:
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=615;Next, start the slave and check slave status:
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;Output:
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.20.10.6
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 615
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: masterdbBoth MariaDB master and slave server are now configured. It's time to test replication.
Navigate to the Master server, and log in to MariaDB shell:
mysql -u root -pEnter your root password, then create a database masterdb which you have specified in my.cnf file:
MariaDB [(none)]> create database masterdb;Next, add some tables and entries on it:
MariaDB [(none)]> use masterdb;
MariaDB [masterdb]> create table mastertable (c int);
MariaDB [masterdb]> insert into mastertable (c) values (1);Now, check the mastertable:
MariaDB [masterdb]> select * from mastertable;Output:
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)Now, navigate to the slave server and verify whether the above created table has been replicated:
First, log in to MariaDB shell:
mysql -u root -pEnter your root password, then change the database to masterdb:
MariaDB [(none)]> use masterdb;Next, check the mastertable:
MariaDB [masterdb]> select * from mastertable;Output:
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)You should see that the database and table are replicated successfully from the master server to the slave server.
Distributed Relational Database Service (DRDS) is a lightweight (stateless), flexible, stable, and efficient middleware product independently developed by Alibaba Group to resolve scalability issues with single-host relational databases DRDS is primarily used for large-scale online data operations to operate, maintain and manage distributed databases throughout their entire lifecycle. Due to its compartmentalized structure, DRDS provides an efficient way to perform operations to meet the demand of the online services for relational databases.
Alibaba Cloud ApsaraDB for RDS is an on-demand database service that frees you up from the administrative task of managing a database, and leaves you with more time to focus on your core business. ApsaraDB for RDS is a ready-to-use service that is offered on MySQL, SQL Server and PostgreSQL. RDS handles routine database tasks such as provisioning, patch up, backup, recovery, failure detection and repair.
ApsaraDB for RDS can also protect against network attacks and intercept SQL injections, brute force attacks and other types of database attacks. As a highly available managed service, ApsaraDB for RDS includes automated monitoring, backup and disaster recovery capabilities. The product includes automated provisioning and scalability, which allows you to scale your databases up and down based on your application's real-time needs.
How to Install Snipe-IT Asset Management Tool on Ubuntu 16.04
2,605 posts | 747 followers
FollowAlibaba Clouder - February 13, 2019
Alibaba Clouder - September 10, 2018
Alibaba Clouder - July 1, 2019
Alibaba Clouder - February 19, 2019
Alibaba Clouder - May 14, 2018
Alibaba Clouder - May 28, 2019
Hi, in the blog post it is stated that "In this architecture we can have RDS as master and on-prem DB as slave. We can also construct RDS as slave with on-prem DB as its master".How can i get to have- db on-prem as master- db rds as slaveusing the built-in replication feature of mysql without resorting to specific alicloud platform tools (e.g. Data Transmission Service)?According to the alicloud documentation and the permissions given by the platform on the modifiable mysql variables (RDS) it seems that this is not possible and that the only way to achieve this is to use specific platform tools (e.g. Data Transmission Service --> Data Migration).Thanks a lot.Marco
2,605 posts | 747 followers
Follow
ECS(Elastic Compute Service)
Elastic and secure virtual cloud servers to cater all your cloud hosting needs.
Learn More
OSS(Object Storage Service)
An encrypted and secure cloud storage service which stores, processes and accesses massive amounts of data from anywhere in the world
Learn MoreLearn More
More Posts by Alibaba Clouder
Raja_KT February 9, 2019 at 6:30 am
Good one. I am not sure if the High Availability edition for MariaDB is there. Hope it is there as manual is really cumbersome on many aspects.