Mariadb
Page content
Install MariaDB on OpenBSD
Wanna install and Operate MariaDB on OpenBSD? Here a few hints …
Install Package
pkg_add mariadb-server mariadb-client
root@puffy /tmp# pkg_add mariadb-server
quirks-7.14 signed on 2024-06-15T18:27:56Z
mariadb-server-10.9.8p0v1:lzo2-2.10p2: ok
mariadb-server-10.9.8p0v1:snappy-1.1.10p1: ok
mariadb-server-10.9.8p0v1:mariadb-client-10.9.8v1: ok
mariadb-server-10.9.8p0v1:p5-FreezeThaw-0.5001p0: ok
mariadb-server-10.9.8p0v1:p5-MLDBM-2.05p0: ok
mariadb-server-10.9.8p0v1:p5-Net-Daemon-0.49: ok
mariadb-server-10.9.8p0v1:p5-PlRPC-0.2020p0: ok
mariadb-server-10.9.8p0v1:p5-Math-Base-Convert-0.11p0: ok
mariadb-server-10.9.8p0v1:p5-Clone-0.46: ok
mariadb-server-10.9.8p0v1:p5-Module-Runtime-0.016p0: ok
mariadb-server-10.9.8p0v1:p5-Params-Util-1.102: ok
mariadb-server-10.9.8p0v1:p5-SQL-Statement-1.414: ok
mariadb-server-10.9.8p0v1:p5-DBI-1.643p0: ok
mariadb-server-10.9.8p0v1:p5-DBD-MariaDB-1.23: ok
mariadb-server-10.9.8p0v1:libxml-2.12.7: ok
mariadb-server-10.9.8p0v1: ok
Running tags: ok
The following new rcscripts were installed: /etc/rc.d/mysqld
See rcctl(8) for details.
New and changed readme(s):
/usr/local/share/doc/pkg-readmes/mariadb-server
add MariaDB Tests
https://mariadb.com/kb/en/mariadb-test-overview/
pkg_add mariadb-tests
root@puffy /tmp# pkg_add mariadb-tests
quirks-7.14 signed on 2024-06-15T18:27:56Z
mariadb-tests-10.9.8v1: ok
Check Doku
you should have a look at: /usr/local/share/doc/pkg-readmes/mariadb-server
Initial Setup
/usr/local/bin/mariadb-install-db
root@puffy /tmp# /usr/local/bin/mariadb-install-db
Installing MariaDB/MySQL system tables in '/var/mysql' ...
OK
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is _mysql@localhost, it has no password either, but
you need to be the system '_mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo
See the MariaDB Knowledgebase at https://mariadb.com/kb
You can start the MariaDB daemon with:
/etc/rc.d/mysqld start
Please report any problems at https://mariadb.org/jira
The latest information about MariaDB is available at https://mariadb.org/.
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
root@puffy /tmp#
Enable & Start Service
rcctl enable mysqld
rcctl restart mysqld
root@puffy /tmp# rcctl enable mysqld
root@puffy /tmp# rcctl restart mysqld
mysqld(ok)
root@puffy /tmp#
Listen on Localhost
MariaDB should be listening on Localhost only. Of course, we can change this.
netstat -an |grep 3306
root@puffy /tmp# netstat -an |grep 3306
tcp 0 0 127.0.0.1.3306 *.* LISTEN
tcp6 0 0 ::1.3306 *.* LISTEN
Secure Installation
but before binding MariaDB to the Public Interface, you should secure the Installation a little bit …
mysql_secure_installation
root@puffy /tmp# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
Switch to unix_socket authentication [Y/n]
Enabled successfully!
Reloading privilege tables..
... Success!
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] n
... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
root@puffy /tmp#
Update my.cnf, Listen on all Interfaces
and now you can modify the config file and listen on all interfaces
sed -i 's/bind-address.*/bind-address = */' /etc/my.cnf
rcctl restart mysqld
netstat -an |grep 3306
root@puffy /tmp# netstat -an |grep 3306
tcp 0 0 *.3306 *.* LISTEN
tcp6 0 0 *.3306 *.* LISTEN
User Management
let’s have a look what kind of users do we have
mysql -u root mysql -e "SELECT Host,User,Password,Super_priv FROM mysql.user;"
root@puffy /tmp# mysql -u root mysql -e "SELECT Host,User,Password,Super_priv FROM mysql.user;"
+-----------+-------------+----------+------------+
| Host | User | Password | Super_priv |
+-----------+-------------+----------+------------+
| localhost | mariadb.sys | | N |
| localhost | root | invalid | Y |
| localhost | _mysql | invalid | Y |
+-----------+-------------+----------+------------+
Show Permission for Root
mysql -u root mysql -e "show grants for 'root'@'localhost';"
root@puffy /tmp# mysql -u root mysql -e "show grants for 'root'@'localhost';"
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
Percona-Toolkit
there is a Toolkit for Handling Permission. check: https://www.percona.com/percona-toolkit
pkg_add percona-toolkit
pt-show-grants
root@puffy /tmp# pt-show-grants
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 10.9.8-MariaDB at 2024-06-16 22:16:17
-- Grants for '_mysql'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO `_mysql`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION;
-- Grants for 'mariadb.sys'@'localhost'
GRANT DELETE, SELECT ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`;
GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`;
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;
you get the following binaries with the Toolkit.
root@puffy /tmp# pkg_info -L percona-toolkit
Information for inst:percona-toolkit-2.2.9p1v0
Files:
/usr/local/bin/pt-align
/usr/local/bin/pt-archiver
/usr/local/bin/pt-config-diff
/usr/local/bin/pt-deadlock-logger
/usr/local/bin/pt-duplicate-key-checker
/usr/local/bin/pt-fifo-split
/usr/local/bin/pt-find
/usr/local/bin/pt-fingerprint
/usr/local/bin/pt-fk-error-logger
/usr/local/bin/pt-heartbeat
/usr/local/bin/pt-index-usage
/usr/local/bin/pt-kill
/usr/local/bin/pt-mext
/usr/local/bin/pt-mysql-summary
/usr/local/bin/pt-online-schema-change
/usr/local/bin/pt-query-digest
/usr/local/bin/pt-show-grants
/usr/local/bin/pt-slave-delay
/usr/local/bin/pt-slave-find
/usr/local/bin/pt-slave-restart
/usr/local/bin/pt-summary
/usr/local/bin/pt-table-checksum
/usr/local/bin/pt-table-sync
/usr/local/bin/pt-table-usage
/usr/local/bin/pt-upgrade
/usr/local/bin/pt-variable-advisor
/usr/local/bin/pt-visual-explain
Any Comments ?
sha256: 7c5dfeaf6acbcc0bd9ac39264fb8fdc493ad5126453db9a3a3af55a49ff1be7a