Connecting remotely to a local MySQL Server

So I tried it, and it works. Here's some notes.

ssh -N -L 3306:127.0.0.1:3306 mrbear@bears.jelastic.eapps.com -p 3022
mysql -u root -p mrbeardb —port=3306

References

Linuxize - How to Connect to MySQL through SSH Tunnel

https://linuxize.com/post/mysql-ssh-tunnel/

Running Mariadb on the Raspberry Pi

This is going to be a small blurb, on what I did to get this working.

Nothing out of the ordinary, really.

$ apt-get install mariadb-server
$ cd /etc/mysql/mariadb.conf.d
$ joe 50-server.cnf
# started vi and replaced 127.0.0.1 with 0.0.0.0
$ service mysql restart

And of course allow the user accounts to access mariadb remotely over the network.

Attention! I'm only doing this locally on a testing server! It's not a great idea to run mariadb connections remotely if you do not have to.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

Adding MariaDB JDBC Connector to Paraya 5 - Followup

Just decided to do a followup on Adding MariaDB JDBC Connector to Payara 5¹.

I wanted to see if it was possibly very easy to do this using the command line.

Installing a MariaDB driver

$ ./asadmin add-library /home/mrbear/software/mariadb-java-client-2.3.0.jar
Command add-library executed successfully.

Creating a new JDBC Connection pool

The syntax:

Usage: asadmin [asadmin-utility-options] create-jdbc-connection-pool
        [--datasourceclassname ] [--restype ]
        [--steadypoolsize ]
        [--maxpoolsize ]
        [--maxwait ]
        [--poolresize ]
        [--idletimeout ] [--initsql ]
        [--isolationlevel ]
        [--isisolationguaranteed[=]]
        [--isconnectvalidatereq[=]]
        [--validationmethod ]
        [--validationtable ]
        [--failconnection[=]]
        [--allownoncomponentcallers[=]]
        [--nontransactionalconnections[=]]
        [--validateatmostonceperiod ]
        [--leaktimeout ]
        [--leakreclaim[=]]
        [--creationretryattempts ]
        [--creationretryinterval ]
        [--sqltracelisteners ]
        [--statementtimeout ]
        [--statementleaktimeout ]
        [--statementleakreclaim[=]]
        [--lazyconnectionenlistment[=]]
        [--lazyconnectionassociation[=]]
        [--associatewiththread[=]]
        [--driverclassname ]
        [--matchconnections[=]]
        [--maxconnectionusagecount ]
        [--ping[=]] [--pooling[=]]
        [--statementcachesize ]
        [--validationclassname ]
        [--wrapjdbcobjects[=]]
        [--description ] [--property ]
        [-?|--help[=]] jdbc_connection_pool_id

Note the following:

• The escape character backslash (\) is used in the --property option to distinguish the semicolon (;).
• Two backslashes (\\) are used to distinguish the equal sign (=).
• Two backslashes (\\) are used to distinguish the colon (:).

./asadmin create-jdbc-connection-pool --driverclassname org.mariadb.jdbc.Driver --restype java.sql.Driver --property databaseName=mmud:password=topsecretpassword:user=root:URL=jdbc\\:mariadb\\://localhost\\:3306/mmud:serverName=localhost mmudpool

Let's test it!

./asadmin ping-connection-pool mmudpool

In case you wish to delete it, try:

./asadmin delete-jdbc-connection-pool mmudpool

Creating the JNDI

./asadmin create-jdbc-resource --connectionpoolid mmudpool jdbc/mmud

References

[1] Adding MariaDB JDBC Connector to Payara 5

https://randomthoughtsonjavaprogramming.blogspot.com/2018/12/adding-mariadb-jdbc-connector-to-paraya.html

[2] Payara Blog - An intro to connection pools in payar server 5

https://blog.payara.fish/an-intro-to-connection-pools-in-payara-server-5

Backup & Restore of Mariadb Server

Hello, again!

This is just a small blog post on how to backup and restore a MariaDB server, without using MysqlDump, but by directly copying the files in /var/lib/mysql.

Backing up

First of course we need to shutdown mariadb.

# systemctl status mariadb

# systemctl stop mariadb

Copying files:

tar zcvf database_backup_`date +%F`.tgz /var/lib/mysql

Restoring

When putting things back, things start to go wrong¹.

Copying the files to a new host, causes this:

2020-04-14 10:45:32 0 [ERROR] InnoDB: Operating system error number 13 in a file operation. 2020-04-14 10:45:32 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory. 2020-04-14 10:45:32 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions

So, if the below code works, than SELinux is causing the issue:

sudo semanage permissive -a mysqld_t

Now you added mysql_t to permissive, as shown by the below printout:

]# semanage permissive -l

Builtin Permissive Types


Customized Permissive Types

mysqld_t

Turning it back to enforcing, means running:

# semanage permissive -d mysqld_t
libsemanage.semanage_direct_remove_key: Removing last permissive_mysqld_t module (no other permissive_mysqld_t module exists at another priority).

We need to relabel the new /var/lib/mysql directory:

sudo semanage fcontext -a -t mysqld_db_t "/var/lib/mysql(/.*)?"
sudo restorecon -Rv /var/lib/mysql

Checking the current file contexts:

$ ls --directory --context /var/lib/mysql
unconfined_u:object_r:mysqld_db_t:s0 /var/lib/mysql

MariaBackup

I need to check out MariaBackup^{2 3}, as a better solution for creating backups compared to mysqldump.

References

[1] Security-Enhanced Linux with MariaDB

https://mariadb.com/kb/en/selinux/

[2] Backing up and restoring databases

https://mariadb.com/kb/en/backing-up-and-restoring-databases/

[3] MariaBackup

https://mariadb.com/kb/en/mariabackup/

[4] Full Backup and Restore with MariaBackup

https://mariadb.com/kb/en/full-backup-and-restore-with-mariabackup/

Installing MariaDB 10 on CentOS 7

I have always used the stock install of MariaDB that came with CentOS 7, but I noticed recently that it is still only on 5.5 (at the time of writing, 5.5.60).

I never had any issues with it, until I tried to upgrade my Liferay CMS system. Apparently there are definite changes that are not incorporated into the 5.5.

I wished to moved to 10.3, which is the latest stable branch, and I can apparently do that (as indicated on the mariadb website¹) by creating a MariaDB repository for DNF and then installing as usual.

Of course, first uninstall the 5.5 version.

References

[1] MariaDB - Installing MariaDB 10 on CentOS 7 / RHEL 7

https://mariadb.com/resources/blog/installing-mariadb-10-on-centos-7-rhel-7/

MariaDB - Moving to MariaDB Backup

https://mariadb.com/resources/blog/moving-to-mariadb-backup/

Automated executing of MySQL/MariaDB scripts

I am running MariaDB and I wish to execute sql scripts without all this hassle of entering my password. Of course this carries severe security risks with it, that we need to be aware of and, if possible, mitigate.

Via the commandline

It is possible to execute sql scripts via the commandline¹, but the problem here is that the password you use is visible in the process list. So this is a security risk.

Let's not do this.

mysql_config_editor

I firstly checked out mysql_config_editor², which enables you to put the password and other options into an encrypted configuration file. But it turns out that MariaDB does not come with that specific tool. The encryption used seems quite weak, and there's an article about the security issues at [3]. There is also the blogpost at [4] giving some details.

So now what?

Well, there is always the plan to use the configuration file .my.cnf⁵, and you can store your mysql or mariadb password in there and everything would be hunky-dory.

The .my.cnf looks like this:

[client]
password=topsecretpassword

You are no doubt aware that the password is stored in cleartext.

The following security measures should be in place:

• always make sure the permissions on the file are set to -rw-------
• create a user in your database with only those permissions that are required by your scripts. In most cases, this is select/update/delete/insert statements.
• when you are finished with your scripts, it might be a good idea to remove the password from the conf file. I understand that with cron jobs and batch scripts this might not be possible.

There is an example of a my.cnf containing every possible configuration option at /usr/share/mysql/my-large.cnf when you install MariaDB.

References

[1] StackOverflow - How to execute a MySQL command from a shell script?

https://stackoverflow.com/questions/8055694/how-to-execute-a-mysql-command-from-a-shell-script

[2] MysqlManual 5.7 - mysql_config_editor

https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html

[3] MariaDb Blog -

https://mariadb.com/resources/blog/mysql-56-security-through-complacency

[4] Todd's MySQL Blog - Understanding mysql_config_editor’s security aspects

http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/

[5] MariaDB - Configuring MariaDB with my.cnf

https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/