MySQL MariaDB Storage

This option one of the 4 storage options of Dynmap. It is an advanced storage layout, for example a seperate service that hosts the database needs to be set-up, which will be explained in this guide.

Linux

Install MySQL by typing the following command:

sudo apt install mysql-server

The MySQL database software is now installed, but its configuration is not yet complete.

To secure the installation, MySQL comes with a script that will ask whether you want to modify some insecure defaults. Initiate the script by typing the following:

sudo mysql_secure_installation

This script will ask if you want to configure the VALIDATE PASSWORD PLUGIN.

Warning: Enabling this feature is a judgment call. If enabled, passwords that don’t match the specified criteria will be rejected by MySQL with
an error. This will cause issues if you use a weak password in conjunction with software that automatically configures MySQL user credentials, 
such as the Ubuntu packages for phpMyAdmin. It is safe to leave validation disabled, but you should always use strong, unique passwords for
database credentials.``

Answer Y for yes, or anything else to continue without enabling.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:

If you’ve enabled validation, the script will also ask you to select a level of password validation. Keep in mind that if you enter 2 – for the
strongest level – you will receive errors when attempting to set any password which does not contain numbers, upper and lowercase letters, 
and special characters, or which is based on common dictionary words.

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1

Next, you’ll be asked to submit and confirm a root password:

Please set the password for root here.

New password:

Re-enter new password:

For the rest of the questions, you should press Y and hit the ENTER key at each prompt. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MySQL immediately respects the changes we have made.

Note that in Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.

setup of the MySQL/MariaDB server.

Now, to setup the MySQL/MariaDB server. It is recommended to create a new user/password with limited permissions for each application. If you are setting up an external web server, you can also setup a firewall (iptables/firewalld/ufw/etc) to only allow the web server to connect to the database.

Configurating the MySQL/MariaDB database

Using a terminal on the server that is hosting the minecraft server, we will create a new database and add some database users.

Creating a new database in MySQL/MariaDB

mysql -u root -p
[Enter mysql root password if there is one]
mysql> CREATE DATABASE <dynmap_database_name>;
mysql> exit

Example (click to expand)

mysql -u root -p
[Enter mysql root password if there is one]
mysql> CREATE DATABASE DynmapDatabase;
mysql> exit

Add new users

note: If the MySQL server is on the same server as the minecraft server. <minecraftserver_ip> should just be localhost

mysql -u root -p
[Enter mysql root password if there is one]
mysql> CREATE USER '<dynmap_user>'@'<minecraftserver_ip>' IDENTIFIED BY '<password>';
mysql> GRANT CREATE, DELETE, INSERT, SELECT, UPDATE, INDEX ON <dynmap_database>.* TO '<dynmap_user>'@'<minecraftserver_ip>';
mysql> FLUSH PRIVILEGES;
mysql> exit

Example if the mysql server and minecraft server are on the same server (click me to expand)

mysql -u root -p
[Enter mysql root password if there is one]
mysql> CREATE USER 'minecraftserver'@'localhost' IDENTIFIED BY 'password5678';
mysql> GRANT CREATE, DELETE, INSERT, SELECT, UPDATE, INDEX ON dynmapDatabase.* TO 'minecraftserver'@'localhost'';
mysql> FLUSH PRIVILEGES;
mysql> exit

If you made a mistake and need to delete the mysql user. This is how you delete a user.

mysql> DROP USER '<user_to_delete>'@'<ip_you_used>';

After you create a new user, try logging into the user. (after you logout from the mysql root user via "exit")

mysql -u <webserver_user> -p
[Enter user password]
mysql> exit

MySQL Privileges/Permissions

MySQL has these permissions available to set to MySQL users.

• ALL PRIVILEGES - Grant all privileges to the user
• CREATE - Allows user to create databases/tables
• DELETE - Allows user to delete rows(data) from a table
• DROP - Allows user to drop databases and tables
• INSERT - Allows user to insert rows(data) to a table
• SELECT - Allows user to read from a database
• UPDATE - Allows user to update data in a table

Editing dynmap/configuration.txt

Firstly, if using forge or fabric, the mods created by kosma that add the SQL connectors that work on both fabric and Forge need to be installed.

The MySQL/MariaDB integration is handled by: Kosmolot's MySQL mod

The last step is to configure the dynmap configuration.txt so it connects to the database and uses it for storage. change the following part of the configuration.txt:

storage:
  # Filetree storage (standard tree of image files for maps)
  type: filetree
  # SQLite db for map storage (uses dbfile as storage location)
  #type: sqlite
  #dbfile: dynmap.db
  # MySQL DB for map storage (at 'hostname':'port' with flags "flags" in database 'database' using user 'userid' password 'password' and table prefix 'prefix')
  #type: mysql
  #hostname: localhost
  #port: 3306
  #database: dynmap
  #userid: dynmap
  #password: dynmap
  #prefix: ""
  #flags: "?allowReconnect=true&autoReconnect=true"

to:

MySQL enabled (Click to expand)

storage:
  # Filetree storage (standard tree of image files for maps)
  #type: filetree <- DONT FORGET TO COMMENT THIS OUT
  # SQLite db for map storage (uses dbfile as storage location)
  #type: sqlite
  #dbfile: dynmap.db
  # MySQL DB for map storage (at 'hostname':'port' with flags "flags" in database 'database' using user 'userid' password 'password' and table prefix 'prefix')
  type: mysql
  hostname: <mysql_ip>
  port: <mysql_port>
  database: <mysql_database>
  userid: <dynmap_mysql_user>
  password: <dynmap_mysql_password>
  prefix: "" # Can add prefix for tables if you want
  flags: "?allowReconnect=true&autoReconnect=true"

example:

MySQL example (Click to expand)

storage:
  # Filetree storage (standard tree of image files for maps)
  #type: filetree <- DONT FORGET TO COMMENT THIS OUT
  # SQLite db for map storage (uses dbfile as storage location)
  #type: sqlite
  #dbfile: dynmap.db
  # MySQL DB for map storage (at 'hostname':'port' with flags "flags" in database 'database' using user 'userid' password 'password' and table prefix 'prefix')
  type: mysql
  hostname: localhost
  port: 3306
  database: dynmap
  userid: dynmap
  password: dynmap
  prefix: "" # Can add prefix for tables if you want
  flags: "?allowReconnect=true&autoReconnect=true"

Now save the file and start the minecraft server, check if dynmap succesfully created and connected to the MySQL/MariaDB database.

Windows

for windows we refer to the following guide: guide.

After the server has been set up, follow the linux tutorial from this point on: https://github.com/webbukkit/dynmap/wiki/MySQL-MariaDB-Storage#setup-of-the-mysqlmariadb-server

Now save the file and start the minecraft server, check if dynmap succesfully created and connected to the database.

Further Reading - https://www.scaler.com/topics/sql/how-to-install-mysql-in-windows-10/