1. Sharded database basic schema

CatalogQT can take advantage of MariaDB Spider engine to achieve database sharding goals. In this setup proxy server utilizes Spider engine mechanics to balance data load between data server while not containing any data itself. Database architecture is hidden from clients, so they can use standard requests used for classic database architecture.

2. Prepare proxy and data servers

#download MariaDB from repository
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.9"
sudo yum install mariadb-server mariadb-backup

#install MariaDB
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
sudo systemctl start mariadb.service

3. Prepare data servers

#At first copy content of sql_sharding directory located in repository catalog_qt_2. This direcotry contains all scripts needed to create database schema on remote server. 
#You can also copy-paste it's content into MariaDB console via SSH.

data_server$ mysql -u root -p

#run database creation script
MariaDB> SOURCE catalog_qt_db_data_node_scheme.sql;
MariaDB> SOURCE d_001_update_variables.sql;
#or just copy-paste it into MariaDB console


4. Prepare proxy server

Remember to go through instruction from section 2 before running those commands.

4.1. Install spider engine

proxy_server$ mysql -u root -p

#install Spider engine
MariaDB> INSTALL SONAME "ha_spider";

4.2. Add server definitions

You need to define aliases for data servers. This code block shows command to create alias backend1  for server addressed by 192.168.0.111 . You must repeat this step for every data server you want to conenct to. Remember to change alias name and ip address.

proxy_server$ mysql -u root -p

MariaDB> CREATE SERVER backend1
		 foreign data wrapper mysql options (host '192.168.0.111',
		 database 'itm_catalog_qt',
		 user 'itm_catalog_rw',
		 password 'itm_catalog_rw',
		 port 3306);

MariaDB> ...repeat previous step for every data server
MariaDB> FLUSH TABLES;

4.3. Modify database schema

catalog_qt_2  repository holds spider table definitions, that have to be modified according to number of data nodes. This file is located under ` /sql_sharding/catalog_qt_db_spider_node_scheme.sql` Below code block shows example of this modification.

NOTE: Schema is configured to work with 2 remote data servers named backend1  and backend2 . If you meet this condition, you can use script without any modification.

NOTE2: See that modifications are necessary only in the last part of table definition. This part contains list of servers being target for data read-write.

CREATE TABLE IF NOT EXISTS `annotation` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `entry_id` INT NOT NULL,
  `text` VARCHAR(1024) NULL DEFAULT NULL,
  `user_id` INT NOT NULL,
  `date_added` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
DEFAULT CHARACTER SET = utf8
engine=SPIDER COMMENT='wrapper "mysql", table "annotation"'
 partition by hash (id)
 (
 partition pt1 comment = 'srv "backend1"',
 partition pt2 comment = 'srv "backend2"'
 );

#REPEAT SERVER LIST MODIFICATION FOR EVERY TABLE DEFINITION IN FILE

4.4. Load database schema

MariaDB> SOURCE catalog_qt_db_spider_node_scheme.sql;

5. Usefull information

5.1. Testing connection

You can test connection between proxy server  and data server  by utilizing mysql command. You have to execute scripts from section 3 to have database and remote user created.

proxy_server$ mysql -u itm_catalog_rw -p -h <data_server_ip> itm_catalog_qt

This method will also work for testing catalog_qt machine connection to proxy server 





  • No labels