Documentation

Difference between revisions of "MySQL"

(Setting up a MySQL client in TARGET 3001!)
Line 149: Line 149:
  
 
Now all clients will have access to the MySQL database.
 
Now all clients will have access to the MySQL database.
 +
 +
 +
== Does MariaDB also work instead of MySQL? ==
 +
 +
[https://office.ibfriedrich.com/EineFrageDE/t31/q6/question.html Here] there is a tip from a customer.<br><br>
 +
 +
 +
 +
 +
 +
[[en:MySQL]][[fr:MySQL]]
 +
 +
 +
 +
  
 
[[de:MySQL]][[fr:MySQL]]
 
[[de:MySQL]][[fr:MySQL]]

Revision as of 12:41, 23 August 2021

SQLiteMySQL


Have a look at the article Component database as well.

MySQL: The powerful database for TARGET 3001! multiuser

If you are in a network with multiple workstations and want to use one common TARGET 3001! component database you will need to have the components stored in a MySQL database. The SQLite database being pre installed with TARGET 3001! performs in multi user situations with normal transfer rates very slowly though in standalone mode in our experience the SQLite database is in the lead.

Note: Please use MySQL version 5.1.12 to 5.7. The use of tools like "MySQL Workbench" may cause problems.


Download MySQL

http://dev.mysql.com/downloads/mysql/


Install a MySQL server

While the SQLite database works with only one DLL and one single database file, a MySQL database needs a MySQL server. This should preferably be installed on a separate server within the network or as an auxiliary on one of the workstations. The server has always to be on power if a client in the network wants to use TARGET 3001!.

Typically you will ask your system administrator to set up a MySQL server. Linux as well as Windows computers are suitable to run such a MySQL server. You will find several tutorials in the Internet to install MySQL to the various systems:

Google: MySQL installation


Set up / adapt a MySQL server and insert a TARGET 3001! data base

You have two options:


NOTE: User names ans passwords must not have more than 16 characters.


Using a console

Set up, adapt

Please set the following options to configure the server so that TARGET 3001! can access to the database correctly:

[mysqld]
thread_stack = 256K
query_cache_size = 32M     #query_cache_size ist ab MySQL 5.7.20 deprecated, also hinfällig
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
max_allowed_packet=2M

[mysql]
max_allowed_packet=2M

The Linux configuration file is named "my.cnf", the Windows configuration file "my.ini".

Insert the initial TARGET 3001! database

In order to insert the initial TARGET 3001! component database to the MySQL server, you need a DUMP file of the database and a brief script file for the import. Whether you have a Linux or a Windows system, different scripts need to be started. Following the link please find a zip file containing all you need:

Dump and scripts

Each script needs to be adapted according to the number of users. The MySQL server is setup after that.

Using phpMyAdmin or similar

The following section explains how to install the MySQL-DB target3001db with a browser application in order to administrate MySQL-databases (e.g. phpMyAdmin).

The dump import can take up to two hours. You should use the console (s. 4) not to block the server for too long time.

In your application you may enter the SQL-commands as text or by menus and dialogs. In terms of common understanding we denote the commands here as text.


The database target3001db etc.:

-- Create User 1, furnish him with rights needed  *** if needed replace target3001user1 and target3001user1_password ***
GRANT USAGE ON *.* TO 'target3001user1'@'%' IDENTIFIED BY 'target3001user1_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE,
ALTER ROUTINE, TRIGGER ON target3001db.* TO 'target3001user1'@'%';
-- Create User 2, furnish him with rights needed  *** if needed replace  target3001user2 and target3001user2_password ***
GRANT USAGE ON *.* TO 'target3001user2'@'%' IDENTIFIED BY 'target3001user2_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE,
ALTER ROUTINE, TRIGGER ON target3001db.* TO 'target3001user2'@'%'; -- further User
-- create database
CREATE DATABASE target3001db CHARACTER SET utf8 COLLATE utf8_general_ci;
-- modify variables
-- To change a global variable you need the permission SUPER.
SHOW VARIABLES LIKE 'thread_stack';
 -- if smaller than 256K, then:
 SET GLOBAL thread_stack = 256K;
SHOW VARIABLES LIKE 'query_cache_size';
 -- if smaller than 32M, then:
 SET GLOBAL query_cache_size = 32M;
SHOW VARIABLES LIKE 'max_allowed_packet';
 -- if smaller than 2M, then:
 SET GLOBAL max_allowed_packet = 2M;


Now the DB-Dump is going to be imported without components (empty3001db.sql):

Not the whole dump with components at once! Remember the db has over 60MB but unfortunately the dump import always is limited, see image. Example phpMyAdmin:

Dumpimport e.png

Download the Dump here.

TARGET 3001! Online DB Update: Load all components etc. from our server. The download only takes a few seconds but the dump import would take up to two hours. Your server/network would be charged accordingly.



Particularity for Windows server

Required for Windows server (with console):

$ mysql -u root -p
or without password: $ mysql -u root
mysql> use mysql
mysql> GRANT ALL ON *.* to root@'%' IDENTIFIED BY '<your-mysql-root-password>';
mysql> FLUSH PRIVILEGES;

These setting you can also do with phpMyAdmin or the workbench.

Setting up a MySQL client in TARGET 3001!

In the same directory as the TARGET 3001! program you will find a file called "libmysql.dll". It represents the client driver to connect to the MySQL server. In TARGET 3001! itself you need to enter few things in order to get response from the MySQL server:

Open an new project and press function key [F2] to open the component browser. At the end of the top menu line you will see that you are still connected to the SQLite database:

Mysql1 e.jpg
(Image 1: Component browser, migration)

Now click on the menu entry "[ connected with ... ]". In the opening menu choose option "Settings". Now enter the following:

Start TARGET 3001! using MySQL database (enter "Yes")
MySQL Server (name or IP of your MySQL server)
MySQL User (your user name for the MySQL server)
MySQL Password (your respective password)
MySQL SSH Key (if SSH is used: SSH key file)
MySQL SSH Cert (if SSH is used: certificate file)
MySQL SSH CA (if SSH is used: certificate authority file)


Multiple users can use the same MySQL user name.

Insert your customized components (migration)

If you already created your own USER components in SQLite (target3001.db), you can import them once only from your TARGET 3001! version to the bare database provided by IBF. All component IDs and custom component types and so on remain the same in the database. We call this action "migration". Select the most important SQLite database from one of the several workstations. Further components from the other users can be added later on.

First use menu "[connected with...]" in order to switch to the MySQL database. In this menu select "Migration from SQLite to MySQL". Then enter this most important SQLite database target3001.db, which shall be migrated to MySQL. So the easiest way to perform the migration, is to do it on the computer with that most important SQLite database.

Now all clients will have access to the MySQL database.


Does MariaDB also work instead of MySQL?

Here there is a tip from a customer.



en:MySQL