This book contains many examples related to LIXA usage, most of them require a proper configuration for the Resource Managers that must participate in the distributed transactions proposed by the examples. This appendix contains the required configurations and step by step explanation to help the set-up.
Note
The same configurations necessary to execute the examples, are used even by the test cases provided by LIXA.
Note
This appendix does not provide information related to the standard installation and configuration operations necessary for run a specific Resource Manager: only LIXA related configurations are explained.
The following diagram represents a simplified vision of the components
necessary to run an example program (example8_mys
in the picture) that uses MySQL as an XA Resource Manager.
This section has been developed using MySQL 5.5.54 (or upper) and MariaDB 5.5.52 for Linux. Here is a brief list of the tested versions for Ubuntu 12.04, 14.04, 16.04, 18.04 and CentOS/RHEL 7.3, CentOS 8 and the installed packages:
tiian@ubuntu1204-64:~$ dpkg -l | grep -i mysql
ii libdbd-mysql-perl 4.020-1ubuntu0.1 Perl5 database interface to the MySQL database
ii libmysqlclient-dev 5.5.54-0ubuntu0.12.04.1 MySQL database development files
ii libmysqlclient18 5.5.54-0ubuntu0.12.04.1 MySQL database client library
ii mysql-client-5.5 5.5.54-0ubuntu0.12.04.1 MySQL database client binaries
ii mysql-client-core-5.5 5.5.54-0ubuntu0.12.04.1 MySQL database core client binaries
ii mysql-common 5.5.54-0ubuntu0.12.04.1 MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.5.54-0ubuntu0.12.04.1 MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.5 5.5.54-0ubuntu0.12.04.1 MySQL database server binaries and system database setup
ii mysql-server-core-5.5 5.5.54-0ubuntu0.12.04.1 MySQL database server binaries
tiian@ubuntu1404-64:~$ dpkg -l | grep -i mysql
ii libdbd-mysql-perl 4.025-1ubuntu0.1 amd64 Perl5 database interface to the MySQL database
ii libmysqlclient-dev 5.5.54-0ubuntu0.14.04.1 amd64 MySQL database development files
ii libmysqlclient18:amd64 5.5.54-0ubuntu0.14.04.1 amd64 MySQL database client library
ii mysql-client-5.5 5.5.54-0ubuntu0.14.04.1 amd64 MySQL database client binaries
ii mysql-client-core-5.5 5.5.54-0ubuntu0.14.04.1 amd64 MySQL database core client binaries
ii mysql-common 5.5.54-0ubuntu0.14.04.1 all MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.5.54-0ubuntu0.14.04.1 all MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.5 5.5.54-0ubuntu0.14.04.1 amd64 MySQL database server binaries and system database setup
ii mysql-server-core-5.5 5.5.54-0ubuntu0.14.04.1 amd64 MySQL database server binaries
tiian@ubuntu1604:~$ dpkg -l | grep -i mysql
ii libmysqlclient-dev 5.7.17-0ubuntu0.16.04.1 amd64 MySQL database development files
ii libmysqlclient20:amd64 5.7.17-0ubuntu0.16.04.1 amd64 MySQL database client library
ii mysql-client-5.7 5.7.17-0ubuntu0.16.04.1 amd64 MySQL database client binaries
ii mysql-client-core-5.7 5.7.17-0ubuntu0.16.04.1 amd64 MySQL database core client binaries
ii mysql-common 5.7.17-0ubuntu0.16.04.1 all MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.7.17-0ubuntu0.16.04.1 all MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.7 5.7.17-0ubuntu0.16.04.1 amd64 MySQL database server binaries and system database setup
ii mysql-server-core-5.7 5.7.17-0ubuntu0.16.04.1 amd64 MySQL database server binaries
Last login: Sat May 12 17:29:32 2018 from 192.168.122.1
tiian@ubuntu1804:~$ dpkg -l | grep -i mysql
ii libmysqlclient-dev 5.7.22-0ubuntu18.04.1 amd64 MySQL database development files
ii libmysqlclient20:amd64 5.7.22-0ubuntu18.04.1 amd64 MySQL database client library
ii mysql-client-5.7 5.7.22-0ubuntu18.04.1 amd64 MySQL database client binaries
ii mysql-client-core-5.7 5.7.22-0ubuntu18.04.1 amd64 MySQL database core client binaries
ii mysql-common 5.8+1.0.4 all MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.7.22-0ubuntu18.04.1 all MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.7 5.7.22-0ubuntu18.04.1 amd64 MySQL database server binaries and system database setup
ii mysql-server-core-5.7 5.7.22-0ubuntu18.04.1 amd64 MySQL database server binaries
[tiian@centos71-64 ~]$ rpm -qa | grep -i maria
mariadb-5.5.52-1.el7.x86_64
mariadb-server-5.5.52-1.el7.x86_64
mariadb-libs-5.5.52-1.el7.x86_64
mariadb-devel-5.5.52-1.el7.x86_64
[tiian@rhel73 ~]$ rpm -qa | grep -i maria
mariadb-devel-5.5.52-1.el7.x86_64
mariadb-5.5.52-1.el7.x86_64
mariadb-server-5.5.52-1.el7.x86_64
mariadb-libs-5.5.52-1.el7.x86_64
[tiian@centos8 tmp]$ rpm -qa | grep -i maria
mariadb-gssapi-server-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
mariadb-connector-c-3.0.7-1.el8.x86_64
mariadb-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
mariadb-common-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
mariadb-errmsg-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
mariadb-server-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
mariadb-connector-c-config-3.0.7-1.el8.noarch
mariadb-server-utils-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
mariadb-devel-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
mariadb-connector-c-devel-3.0.7-1.el8.x86_64
mariadb-backup-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64
mariadb-java-client-2.2.5-2.el8.noarch
If you were using a different version you would need to adapt some commands to your environment.
Note
If you did not yet installed the software, please refer to the official site for your Linux distribution or to the official site of MySQL/MariaDB if your operating system does not distribute the software or you want to use a different MySQL/MariaDB version. This manual does not give you information related to MySQL/MariaDB: it is assumed that you have already installed and configured the database.
Note
This example requires you are running the database and the application on the same host: this is not a technical limitation, but a way to make it easy. Client/server configuration must work as well, but it needs some MySQL/MariaDB extra configuration: please refer to the database documentation.
Important
The LIXA software must be configured to support the MySQL/MariaDB server resource manager as explained in the section called “Linking third party resource managers”.
If your server didn't start-up automatically at boot time, you could start it with the following commands for Ubuntu:
| [Shell terminal session] |
tiian@ubuntu1204-64:/tmp$ sudo service mysql start mysql start/running, process 1607 tiian@ubuntu1204-64:/tmp$ sudo service mysql status mysql start/running, process 1607 tiian@ubuntu1204-64:/tmp$ ps -ef|grep mysql|grep -v grep mysql 1607 1 0 22:43 ? 00:00:00 /usr/sbin/mysqld |
or the following commands for CentOS:
| [Shell terminal session] |
[tiian@centos8 tmp]$ sudo systemctl start mariadb
[tiian@centos8 tmp]$ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2020-10-11 15:08:59 CEST; 57s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 2013 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
Process: 1944 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
Process: 1919 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Main PID: 1982 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 30 (limit: 11477)
Memory: 73.1M
CGroup: /system.slice/mariadb.service
└─1982 /usr/libexec/mysqld --basedir=/usr
Oct 11 15:08:58 centos8 systemd[1]: Starting MariaDB 10.3 database server...
Oct 11 15:08:59 centos8 mysql-prepare-db-dir[1944]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Oct 11 15:08:59 centos8 mysql-prepare-db-dir[1944]: If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.
Oct 11 15:08:59 centos8 mysqld[1982]: 2020-10-11 15:08:59 0 [Note] /usr/libexec/mysqld (mysqld 10.3.17-MariaDB) starting as process 1982 ...
Oct 11 15:08:59 centos8 systemd[1]: Started MariaDB 10.3 database server.
[tiian@centos8 tmp]$ ps -ef | grep mysql|grep -v grep
mysql 1982 1 1 15:08 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr
|
Create a new user authorization and a new database [57]:
| [Shell terminal session] |
tiian@ubuntu1204-64:/tmp$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 Server version: 5.5.54-0ubuntu0.12.04.1 (Ubuntu) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT ALL ON lixa.* TO 'lixa'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE DATABASE lixa; Query OK, 1 row affected (0.00 sec) mysql> quit Bye |
On newest systems like for example Ubuntu 18.04, Ubuntu 20.04 and CentOS 8, the following commands can be used instead of the previous ones that don't work anymore:
| [Shell terminal session] |
tiian@ubuntu1804:~$ sudo mysql -u root [sudo] password for tiian: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22-0ubuntu18.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER 'lixa'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT ALL ON lixa.* TO 'lixa'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE DATABASE lixa; Query OK, 1 row affected (0.00 sec) mysql> quit Bye |
Note
MySQL 8.0 requires an additional privilege to perform XA RECOVER (SQL) statement as described here:
| [Shell terminal session] |
tiian@ubuntu2004:~$ sudo mysql -u root [sudo] password for tiian: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT XA_RECOVER_ADMIN ON *.* TO 'lixa'@'localhost'; Query OK, 0 rows affected (0.07 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.04 sec) mysql> quit Bye |
The lixa@localhost user
has been created with all privileges on the “lixa”
database. Now a sample table must be created using this new user:
| [Shell terminal session] |
tiian@ubuntu1204-64:/tmp$ mysql -h localhost -u lixa lixa Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.5.54-0ubuntu0.12.04.1 (Ubuntu) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | lixa | +------------+ 1 row in set (0.01 sec) mysql> CREATE TABLE authors (id INTEGER NOT NULL PRIMARY KEY, last_name TEXT, first_name TEXT) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> DESCRIBE authors; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | last_name | text | YES | | NULL | | | first_name | text | YES | | NULL | | +------------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM authors; Empty set (0.00 sec) |
OK, the “authors” table was created using the “InnoDB” engine. If something went wrong, you should refer to MySQL documentation to fix the issue before the next step because you would not be able to execute the sample program without a basic running installation.