This blog post is the first part of a series of three tutorials dedicated to Rancher High-Availability. And the first step towards HA is to have a fault tolerant database.
By default Rancher comes with an embedded HSQLDB database. It’s fine for testing purpose but not suited to a production environment.
So this tutorial explains:
- How to start a replicated Master - Master MySQL database using Docker,
- How to export Rancher data to an external database,
- How to start Rancher using an external database.
You need at least two servers for the MySQL database and one for Rancher (can be the same as one of the database servers):
- MySQL Server One available at IP address 126.96.36.199,
- MySQL Server Two available at IP address 188.8.131.52,
- Rancher available at IP address 184.108.40.206.
All servers must have Docker installed.
MySQL Master - Master
MySQL is an open-source relational database management system, and a way to achieve High Availability is the Master - Master replication.
A Master - Master replication means that we are going to use two servers at the same time, both synchronized with each other:
You can write data into any server, it will be replicated on the two of them. If one of the MySQL drops down, the other could take over and no data would be lost.
Configuring the Database
Connect to the first server using SSH and create two folders:
mkdir ~/mysql mkdir ~/mysql/datadir
Create the file
~/mysql/my.cnf with the following content:
[mysqld] # Default configuration skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock secure-file-priv=/var/lib/mysql-files user=mysql symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # Replication configuration server-id=1 log_bin # The cattle here is our database name binlog_do_db=cattle
cattle is the common name for Rancher database but you can use any name you want.
Starting the Database
Start the MySQL server using Docker:
docker run --name mysql \ --restart=always \ -e MYSQL_ROOT_PASSWORD=rootpwd \ -e MYSQL_DATABASE=cattle \ -e MYSQL_USER=cattle \ -e MYSQL_PASSWORD=cattle \ -v ~/mysql/datadir:/var/lib/mysql \ -v ~/mysql/my.cnf:/etc/my.cnf \ -p 3306:3306 \ -d mysql/mysql-server:5.7
-e parameters are environment variables given to the MySQL Docker container.
They contain the MySQL root user password (here rootpwd) and the information to create a first database (named cattle accessed by user cattle with password cattle).
-v options are used to map a host folder to a container folder. The host is the machine where the Docker container runs.
We need to map our data directory so we don’t lose them when the container stops. We also map the configuration file my.cnf to add our replication configuration.
-p parameter tells Docker to expose the port 3306 so that our database is accessible by the two other servers.
According to the Rancher documentation our servers needs:
- 1GB RAM,
- To Run MySQL 5.7 with Barracuda where the default ROW_FORMAT is Dynamic,
- To have a
max_connectionssetting set to more than 150.
When ran using the Docker image mysql/mysql-server:5.7 I got the version 5.7.16 (
After importing the Rancher data, you can validate that the ROW_FORMAT is Dynamic using the SQL request (that was the case for me):
SELECT `table_name`, `row_format` FROM `information_schema`.`tables` WHERE `table_schema`=DATABASE();
max_connections setting you can check its value with the SQL request:
show variables like "max_connections"; and update it with
set global max_connections = 200;. Once again I had a default value of 151.
So we can keep the default configuration values and everything will run fine.
Securing the database
-p 3306:3306 exposes our database to the whole world! You may want to customize IPTables to restrict its access to only the two other servers.
iptables -L --line-numbers to list the existing firewall rules. As you can see Docker adds its own rules:
Chain DOCKER (1 references) num target prot opt source destination 1 ACCEPT tcp -- anywhere 172.17.0.2 tcp dpt:mysql
The Docker documentation about network states that:
Docker will not delete or modify any pre-existing rules from the DOCKER filter chain. This allows the user to create in advance any rules required to further restrict access to the containers.
And that’s exactly what we are going to do! Run the following commands to restrict access to port 3306:
iptables -I DOCKER -i eth0 -s 220.127.116.11 -p tcp --dport 3306 -j ACCEPT iptables -I DOCKER -i eth0 -s 18.104.22.168 -p tcp --dport 3306 -j ACCEPT iptables -I DOCKER 3 -i eth0 -p tcp --dport 3306 -j DROP
The number 3 on the last line is the position in the Chain. So you may adjust it if you want to expose the port 3306 to more than 2 IPs.
List the IPTable rules (
iptables -L --line-numbers):
Chain DOCKER (1 references) num target prot opt source destination 1 ACCEPT tcp -- 22.214.171.124 anywhere tcp dpt:mysql 2 ACCEPT tcp -- 126.96.36.199 anywhere tcp dpt:mysql 3 ACCEPT tcp -- anywhere 172.17.0.2 tcp dpt:mysql 4 DROP tcp -- anywhere anywhere tcp dpt:mysql
There is an issue here. The DROP from anywhere is after the ACCEPT from anywhere. So that’s not secured at all!
In fact modifications to the IPTable rules must me done before starting the Docker container. To fix this simply restart the MySQL one:
docker restart mysql.
Now our firewall rules are in the proper order:
Chain DOCKER (1 references) num target prot opt source destination 1 ACCEPT tcp -- 188.8.131.52 anywhere tcp dpt:mysql 2 ACCEPT tcp -- 184.108.40.206 anywhere tcp dpt:mysql 3 DROP tcp -- anywhere anywhere tcp dpt:mysql 4 ACCEPT tcp -- anywhere 172.17.0.2 tcp dpt:mysql
The modifications made to IPTables are not persistent! So they are lost if the server restart. In a production environment you may configure them on startup.
To configure replication we need to create a dedicated MySQL user.
Connect to the database container using the command
docker exec -ti mysql mysql -u root --password=rootpwd. The
mysql> prompt appears, you can now execute SQL requests.
Execute the following requests to create the aforementioned user:
create user 'replicator'@'%' identified by 'replicatorpwd'; grant replication slave on *.* to 'replicator'@'%';
Restart the MySQL Docker container
docker restart mysql and display the master status:
docker exec -ti mysql mysql -u root --password=rootpwd -e "show master status;" +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | cb3c30d50792-bin.000001 | 599 | cattle | | | +-------------------------+----------+--------------+------------------+-------------------+
Keep track of the displayed information as we will need it to configure the second server.
The second database server configuration is the same as Server One except:
~/mysql/my.cnfconfiguration file must have
- The IPTables rules are:
iptables -I DOCKER -i eth0 -s 220.127.116.11 -p tcp --dport 3306 -j ACCEPT iptables -I DOCKER -i eth0 -s 18.104.22.168 -p tcp --dport 3306 -j ACCEPT iptables -I DOCKER 3 -i eth0 -p tcp --dport 3306 -j DROP
- The result of the request
show master status;is different and must also be saved to activate the replication from Server 2 to Server 1.
Activating the replication
Now that our two servers are running and configured we only need to execute a few MySQL request to activate the replication.
On both server connect to MySQL as the root user
docker exec -ti mysql mysql -u root --password=rootpwd and execute the commands:
slave stop; CHANGE MASTER TO MASTER_HOST = 'x.x.x.x', MASTER_USER = 'replicator', MASTER_PASSWORD = 'replicatorpwd', MASTER_LOG_FILE = 'xxxxx-bin.000001', MASTER_LOG_POS = 123; slave start;
MASTER_LOG_FILE = 'xxxxx-bin.000001'is the File value displayed when executing
show master status;on the other server.
MASTER_LOG_POS = 123is the Position value.
You may see errors such as
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave start' at line 1if it’s the first time you execute
slave stop;it won’t prevent the replication from operating.
Restart both databases using
docker restart mysql.
Testing the replication
Connect as the cattle user:
docker exec -ti mysql mysql -u cattle --password=cattle cattle on both servers.
Create a table and insert values on Server One:
create table example (data VARCHAR(100)); insert into example values ('test');
List the tables and the example table content on Server Two:
show tables; select * from example;
You should the modifications replicated on Server Two. You can also insert values from Server Two and check that they are replicated the other way.
Finally drop the table:
drop table example;
Exporting the database
You may already have Rancher installed. In such case, you need to copy its existing internal database into the MySQL you just created.
- Open the Rancher UI (http://22.214.171.124:8080) and go to the Admin > High-Availability page,
- Click on the Export Database button (Step 1),
- A SQL dump file is downloaded to your computer,
- Upload it to one of your database servers,
- Extract it into
~/mysql/datadir/so it can be accessed from within the Docker container.
If your Rancher server is not secured you can also download it directly to a database server using the command:
To import it:
- Open a shell session on the MySQL Docker container:
docker exec -ti mysql bash,
- Head to the data directory
- Import the dump file:
mysql -u cattle --password=cattle cattle < rancher-mysql-dump.sql.
Connect to the database and show the tables:
MariaDB [cattle]> show tables; +-----------------------------------------------+ | Tables_in_cattle | +-----------------------------------------------+ | DATABASECHANGELOG | | DATABASECHANGELOGLOCK | | account | | agent | | agent_group | | audit_log | | auth_token | | backup | ...
You should see the Rancher tables.
Rancher with an external database
Running Rancher with an external database is probably the easiest step of this tutorial. Only one command and your server is available at
docker run -d -p 8080:8080 \ --restart=unless-stopped \ -e CATTLE_DB_CATTLE_MYSQL_HOST=126.96.36.199 \ -e CATTLE_DB_CATTLE_MYSQL_PORT=3306 \ -e CATTLE_DB_CATTLE_MYSQL_NAME=cattle \ -e CATTLE_DB_CATTLE_USERNAME=cattle \ -e CATTLE_DB_CATTLE_PASSWORD=cattle \ -v /var/run/docker.sock:/var/run/docker.sock \ rancher/server
Using a replicated external database is a first step towards High Availability. But it’s not really HA yet!
- Since Rancher only communicates with one MySQL server, if it goes down Rancher goes down too. We might need a fail-over mechanism here. A Galera database Cluster might be one.
- Also we only have one Rancher server. We need to install a multi-nodes Rancher.