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;

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/

Back in the old days, when I needed to run a script upon boot, I could always put it into the /etc/rc.d/rc.local. Apparently all this changed with Fedora Core 14. Fedora Core being my Operating System of choice. The file in question is nowhere to be found.

Granted, this hasn't been an issue, until my gateway decided to quit.

systemd

systemd is the new initialisation system used since Fedora Core 14. It replaces the System V style system of an init process that serially starts all daemons and the like.

We can use "systemctl" and "systemctl status" to display what services started and which failed.

The command "systemctl enable [service]" will enable the service upon startup. It does nothing more, as far as I can tell, than create a symbolic link in the appropriate directory. For example "ln -s '/usr/lib/systemd/system/glassfish3.service' '/etc/systemd/system/multi-user.target.wants/glassfish3.service'".

MySQL Server Script

A MySQL Server systemd service script in Fedora Core 18 is available here.

Installing mysqld on startup of the system is not automatically done in Fedora Core 18. ^[3]

The old way

Run "/etc/init.d/mysqld start". Use "restart" after an update. What should also work is "service mysqld start".
Run "chkconfig --levels 235 mysqld on" to enable the mysql-server on startup.

The new way

Run "systemctl start mysqld.service" to start mysql-server. Use "restart" after an update.
Run "systemctl enable mysqld.service" to enable the mysql-server on startup.

rc.local is back!

Systemd comes automatically with an rc-local.service, It is installed without being enabled. It is available at /usr/lib/systemd/system/rc-local.service.

Here are my steps:

1. create file /etc/rc.d/rc.local (add, for instance, two lines "#!/bin/sh" and "echo Profit!")
2. run "chmod u+x /etc/rc.d/rc.local"
3. run "systemctl enable rc-local.service"
4. run "systemctl start rc-local.service"

The command "systemctl status rc-local.service" should show:

Note

Systemd is in control on which deamons are started when, in what order, etc. This means that forking of your daemon is a bad idea, as it means systemd willl lose control of the process. For example, the process id is no longer known.

References

[1] systemd

http://www.freedesktop.org/wiki/Software/systemd

[2] systemd for Administrators, Part 1

http://0pointer.de/blog/projects/systemd-for-admins-1.html

[3] Install MySQL 5.5.30 on Fedora 18/17, CentOS/Red Hat (RHEL) 6.3/5.9

http://www.if-not-true-then-false.com/2010/install-mysql-on-fedora-centos-red-hat-rhel/

[4] System does not run /etc/rc.local

http://superuser.com/questions/278396/systemd-does-not-run-etc-rc-local