原文标题：How to Setup MariaDB Master and Slave Replication on Ubuntu 16.04
Requirements
Two fresh Alibaba Cloud instance with Ubuntu 16.04 installed.
A static IP address 192.168.0.101 is configured on the Master node and 192.168.0.102 is configured on the Slave node.
A Root password is set up on both instance.
Launch Alibaba Cloud ECS Instance
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 -y
Install MariaDB
Before 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 -y
Once the installation is completed, start MariaDB service and enable it to start on boot time with the following command:
systemctl start mysql
systemctl enable mysql
By default, MariaDB is not secured. So you will need to secure it first. You can do this by running the following command:
mysql_secure_installation
Answer 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] y
Configure Master Node
First, you will need to edit /etc/mysql/my.cnf and make some changes inside it.
nano /etc/mysql/my.cnf
Make 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=masterdb
Save and close the file, when you are finished.
Next, restart MariaDB service to apply the chnages:
systemctl restart mysql
Next, log in to MariaDB shell and configure replication:
mysql -u root -p
Enter 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.sql
Next, 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 -pMariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;
Configure Slave Server
You will also need to edit /etc/mysql/my.cnf file and make some changes inside it:
nano /etc/mysql/my.cnf
Make the following changes:
[mysqld]
bind-address = 192.168.0.102
server-id = 2
replicate-do-db=masterdb
Save the file, then restart MariaDB service:
systemctl restart mysql
Next, 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 -p
Enter 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: masterdb
Test Replication
Both 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 -p
Enter 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 -p
Enter 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.