Before proceding with the MySql database configuration, be sure you have identified which ThinMan Operation Mode you will use.
| ThinMan Operation modes with External Database | ||
|---|---|---|
| Single Server with External Database | Primary and Secondary Server with Single Database (HALF redundant configuration | Primary and Secondary Server with Dual Database (FULL redundant configuration) |
 |
 |
 |
Follow one of the procedures explained below, depending on the architecture type you will use: Single or Dual Database.
¶ Common parameter setting
Regardless of the ThinMan Operation Mode you chose, a specific parameter have to be set in the database initialization file.
The MySQL configuration file must be edited, in the mysqld section add/change the following parameter:
[mysqld]
max_allowed_packet=16M
After changing the MySQL initialization file, MySQL Server must be restarted. Refer to you DB Administrator about the correct procedure for instance startup.
¶ Single Database Instance Configuration
¶ Creation of Database Schema
A new schema must be created in the database server through MySQL tools. E.g. through MySQL Command Line Client on the server host once logged-id as root:
> create database <schema_name>;
Substitute the "<schema_name>" string with the new name for the ThinMan schema (E.g. "thinman_db" or "thinman_schema").
¶ DB user creation for ThinMan Server connection
Create a user in the database that is allowed to access the previously created schema.
Do not use the MySQL root user for ThinMan Server operation connection
Because of many limitations the MySQL root user is affected in database remote connection, do not use it. Create instead a new dedicated user for normal operation and use it for connection between ThinMan Server end MySQL database.
For example, using MySQL Command Line Client, once connected as root, create the ThinMan-dedicated database user, using the following commands:
> create user <user> identified by '<password>';
> grant ALL on <schema_name>.* to <user>;
¶ Creation of database tables
The creation of database tables is automatically performed on the first run of the ThinMan server. Manual procedure is not required.
Manual Creation of Database Tables:
Follow the steps below in case the automatic creation of tables failed.
- copy mysql_install_ddl_x.y.z.sql and mysql_install_dml_x.y.z.sql files on the database host. Files are contained in the Server\script directory situated in the ThinMan installation directory.
- open an MS-DOS shell or a Unix shell and enter to the directory where the previous sql files were copied.
- open a MySQL shell with the command:
mysql --database=<schema_name> --user=<user> --password=<password>Be sure to provide correct values for <schema_name> (name of the database schema), <user> (username to use for database connection) and <password> (password for the user) previously created.- wait for the shell prompt mysql>_ and type the following commands:
source mysql_install_ddl_x.y.z.sqlandsource mysql_install_dml_x.y.z.sqlthe two commands should not return error messages.
¶ Dual Database Configuration
The picture below shows the ThinMan Architecture with high availability (HA).This conceptual schema can involve up to 4 different servers (one for each distinct component: ThinMan Primary Server, ThinMan Secondary Server, MySql Database Master, MySql Database Slave).
The architecture could also involve only 2 servers.
The machines involved can be either physical or virtual.
WARNING
Any other server grouping different from the above one will not assure any business continuity
Two different scenarios with the Full Redundant Configuration Mode:
- New installation of Primary and Secondary Server: read below Database MySQL Replication configuration from scratch.
- Adding a Secondary Server to a pre-existing Primary Server: read below Database MySQL Replication starting from a preexisting Primary Server Installation with unique Database.
Note:
The instructions below correspond to an installation based on Microsoft Windows O.S. machines with MySQL Server 5.5 version.
¶ Database MySQL Replication Configuration from scratch
Stop ThinMan Server Services
ThinMan Server services on Primary and Secondary ThinMan server must be stopped until the database replica setting up is completed.
¶ Setup Master Database for MySQL Replication
On the first host (hosting ThinMan Primary Server and MySQL Master Database Server) must be enabled the binary log and must be set a unique Server ID.
First of all, MySQL Server must be stopped. Refer to you DB Administrator about the correct procedure for instance shutdown.
The MySQL configuration file must be edited right after the shutdown has completed. Edit on the section [mysqld] of the file the following content:
[mysqld]
log-bin=mysql-bin
server-id=1
After changing the file, MySQL Master Server must be restarted. Refer to you DB Administrator about the correct procedure for instance startup.
¶ Setup Slave Database for MySQL Replication
On the second host (hosting Secondary Server and MySQL Slave Database Server) must be set a unique Server ID.
First of all, the MySQL Server must be stopped. Refer to you DB Administrator about the correct procedure for instance shutdown.
The MySQL configuration file must be edited right after the shutdown has completed. Edit on the section mysqld the following content:
[mysqld]
server-id=2
After changing the file, MySQL Slave Server must be restarted. Refer to you DB Administrator about the correct procedure for instance startup.
¶ Creating Database Schema and Creating Replication between MySQL Master and MySQL Slave Server
Below are specified the operations that must be executed in both MySQL Servers in chronological order.
On the host where the Primary Server resides must be executed MySQL Master operations, whilst on the host where the Secondary Server resides, must be executed MySQL Slave operation.
The operations must be executed via MySQL Command Line Client.
Parameters between "<" and ">" are only placeholder, they have to be substituted with your values.
root privileges
Please remind that the following operations both on ThinMan Master and Slave Server have to be done with root privileges
¶ Master
> create database <schema_name>;
> create user <user_master> identified by '<password>';
> grant ALL on <schema_name>.* to <user_master>;
> create user '<repl_user>' identified by '<repl_password>';
> grant replication slave on *.* to '<repl_user>';
> show master status;
The last command will show a table similar to the one below:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | | |
+------------------+----------+--------------+------------------+
Write down the file name and the position of the listed file, as they will be required in the Slave Server replica configuration(following steps).
MySql 8
For MySql version 8 or higher, it is necessary to add a parameter to the create user '<repl_user>'... sentence:
create user '<repl_user>' identified with mysql_native_password by '<repl_password>';
¶ Slave
> create database <schema_name>;
> create user <user_slave> identified by '<password>';
> grant ALL on <schema_name>.* to <user_slave>;
> change master to MASTER_HOST='<master_hostname_ip>', MASTER_USER='<repl>', MASTER_PASSWORD='<repl_pass>', MASTER_LOG_FILE='<recorded_file_name>', MASTER_LOG_POS=<recorded_position>;
> start slave;
> show slave status;
The last command "show slave status" will return output as follow:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <master_hostname_ip>
Master_User: TM_Repl_DBUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 73
Relay_Log_File: Aberdeen-relay-bin.000009
Relay_Log_Pos: 1042
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 73
Relay_Log_Space: 5027
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
In order to understand if the database replica on slave server is working properly check the below highlighted row of the show slave status command output. Values between "< >" are placeholders for values you input in the above slave replica configuration commands sequence.
They have to match the following conditions:
- Master_Host: have to match the Master MySQL Database hostname/ip;
- Master_Log_File: have to match the master log file name shown by the show master status command run on MySQL Master database;
- Read_Master_Log_Pos: have to match the master log file position shown by the show master status command run on MySQL Master database;
- Slave_IO_Running: have to be Yes;
- Slave_SQL_Running: have to be Yes;
- Last_IO_Errno: have to be 0;
- Last_IO_Error: have to be empty;
- Last_SQL_Errno: have to be 0
- Last_SQL_Error: have to be empty;
- Master_Server_Id: 1
DATABASE REPLICA INACTIVE
The database replica is inactive if at least one of the previous listed conditions does not match. Check the reason with your Database Administrator.
¶ Master
Finally, test the Database replica full efficiency by running the following command on the Master database:
> show slave hosts;
The command will report an output similar to the one below:
+------------------+------+------+-----------+
| Server_id | Host | Port | Master_ID |
+------------------+------+------+-----------+
| 2 | | 3306 | 1 |
+------------------+------+------+-----------+
This output means that the database identified with Server_id 2 is a slave host for the Master database identified with server ID 1.
The values 1 and 2 are the ones defined into the section [mysqld] of the MySQL configuration file on the Master and Slave database.
¶ Database MySQL Replication starting from a preexisting Primary Server Installation
Stop ThinMan Server Services
ThinMan Server services both on Primary ThinMan Server and Secondary ThinMan server must be stopped until the database replica setting up is completed.
In this case, the following assumptions are made:
- A ThinMan Primary Server with MySQL Server is already installed and working.
- A new ThinMan Secondary Server with MySQL Slave Server will be added.
¶ Setup Master for MySQL Replication
Stop all ThinMan Services on Primary Server host.
On the first host (hosting Primary Server and MySQL Master Database Server) the binary log must be enabled and a unique Server ID must be set.
First of all the MySQL Server master database must be stopped. Refer to you DB Administrator about the correct procedure for instance shutdown.
The MySQL configuration file must be edited, right after the shutdown has completed. The section [mysqld] of the file must be edited and saved with the following content:
[mysqld]
log-bin=mysql-bin
server-id=1
After changing the file, MySQL Master Server must be restarted. Refer to you DB Administrator about the correct procedure for instance startup.
Do not restart the ThinMan Services yet.
¶ Setup Slave for MySQL Replication
On the second host (hosting Secondary Server and MySQL Slave Database Server) must be set a unique Server ID.
First of all the MySQL Server Slave Database must be stopped. Refer to you DB Administrator about the correct procedure for instance shutdown.
The MySQL configuration file must be edited, right after the shutdown has completed. The section [mysqld] of the file must be edited and saved with the following content:
[mysqld]
server-id=2
After changing the file, MySQL Slave Server must be restarted. Refer to you DB Administrator about the correct procedure for instance startup.
¶ Backup Database Data on Primary Server
On Primary Server make a backup of MySQL Database data. The backup can be done on MS-DOS console via this command:
C:\> <path_to_mysql_bin>\mysqldump.exe --user=<user> --password=<pass> --result-file=<file> --databases <nome_schema> --add-drop-database --single-transaction --master-data=2
where <path_to_mysql_bin> is the bin directory of MySQL Server installation (e.g. "C:\Program Files\MySQL\MySQL Server 5.1\bin". If this directory is inserted in the system variable "PATH", it can be omitted from the command).
Administrative privileges
Please notice that in order to carry out successfully the data backup, you have to be connected to the MySQL Slave instance as root or with administrative privileges.
¶ Restore Database Data on Secondary Server
The file generated in the previous step must be copied in the Secondary Server in order to restore the Master MySQL database backup into the Slave MySQL database.
On the Secondary Server the restoring operation can be done on MS-DOS console through this command:
C:\> <path_to_mysql_bin>\mysql.exe --user=<root_user> --password=<root_password> < <file>
where <file> is the filename copied from the Primary Server and <path_to_mysql_bin> is the bin directory of MySQL Server installation (e.g. "C:\Program Files\MySQL\MySQL Server 5.1\bin". If this directory is inserted in the system variable "PATH", it can be omitted from the command).
Administrative privileges
Please notice that in order to carry out successfully the data restore, you have to be connected to the MySQL Slave instance as root or with administrative privileges.
¶ Create Replication between MySQL Master and MySQL Slave Server
Hereinafter, are specified the operations that must be executed on MySQL Master Server and MySQL Slave Server in chronological order.
MySQL Master operations must be executed on the host where the Primary Server resides, whilst MySQL Slave operations run on the host of the Secondary Server.
Operations must be executed via MySQL Command Line Client. Parameters between "<" and ">" are only placeholder, they have to be substituted with your values.
Administrative privileges
Please remind that the following operations both on ThinMan Master and Slave Server have to be done with administrative privileges or root account
¶ Master
> create user '<repl_user>' identified by '<repl_password>';
> grant replication slave on *.* '<repl_user>';
> show master status;
The last command will visualize a table similar to the one below:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | | |
+------------------+----------+--------------+------------------+</code></pre>
Write down the File name and the position of the listed file, as they will be required for the Slave Server replica configuration in the following steps.
¶ Slave
> create user <user_slave> identified by'<password>';
> grant ALL on <schema_name>.* to <user_slave>;
> change master to MASTER_HOST='<master_hostname_ip>', MASTER_USER='<repl>', MASTER_PASSWORD='<repl_pass>', MASTER_LOG_FILE='<recorded_file_name>', MASTER_LOG_POS=<recorded_position>;
> start slave;
> show slave status;
The last command show slave status will return an output similar to the one below:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host:
Master_User: TM_Repl_DBUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 73
Relay_Log_File: Aberdeen-relay-bin.000009
Relay_Log_Pos: 1042
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 73
Relay_Log_Space: 5027
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
In order to understand if the database replica on slave server is working properly check the below highlighted rows of the show slave status command output.
Values between "< >" are placeholders for values you input in the above slave replica configuration commands sequence.
They have to match the following conditions:
- Master_Host: have to match the Master MySQL Database hostname/ip;
- Master_Log_File: have to match the master log file name shown by the show master status command run on MySQL Master database;
- Read_Master_Log_Pos:have to match the master log file position shown by the show master status command run on MySQL Master database;
- Slave_IO_Running: have to be Yes;
- Slave_SQL_Running: have to be Yes;
- Last_IO_Errno: have to be 0;
- Last_IO_Error: have to be empty;
- Last_SQL_Errno: have to be 0
- Last_SQL_Error: have to be empty;
- Master_Server_Id: 1
DATABASE REPLICA INACTIVE
If even only one of the previous listed requirements are not matching, it means that the database replica is inactive, therefore ThinMan can't assure user business continuity. Check with your Database Administrator the fault reason. Repeat check until it will match expected results
¶ Master
Finally, test about Database replica full efficiency, on Master database run the following command:
> show slave hosts;
The command will report the output as below
+------------------+------+------+-----------+
| Server_id | Host | Port | Master_ID |
+------------------+------+------+-----------+
| 2 | | 3306 | 1 |
+------------------+------+------+-----------+
This command output means that the database identified by Server_id 2 is a slave host for the Master database identified by server ID 1. The values 1 and 2 are the ones defined into the section [mysqld] of the MySQL configuration file on the Master and Slave database.
¶ Restart ThinMan Services on both ThinMan Primary and Secondary Server
Start all ThinMan Services on both the Primary and Secondary ThinMan Server host (see How to Stop and Start ThinMan Services).
¶ Additional Information
Additional and exhaustive information on MySQL Replication, Master and Slave MySQL Servers and links to related arguments on MySQL site can be found in the chapter Additional Information on MySQL Replication Environment .