-
Notifications
You must be signed in to change notification settings - Fork 13
Installing MariaDB
There are several options depending on your environment:
-
On Linux/*BSD, the MariaDB server, client and other packages can be installed from your OS's/Linux distro's repositories, or optionally, from MariaDB's own repository so you get the most recent version.
-
Perhaps the most convenient solution for a development environment is using containers. See the docker / podman section below for examples.
-
Note that it's also possible to download the binaries into e.g. your home directory and run MariaDB as a non-privileged user - see further down on this page. This can be useful in restricted environments where you don't have privileges to install packages, or maybe for installing multiple versions on a single computer.
Use the MariaDB configuration repository tool to get the repository configuration file needed.
Here are just some example install commands, but please refer to MariaDB for authoritative and exhaustive documentation - see here.
sudo apt update
sudo apt install mariadb-server
Optionally, to set up a password and so forth, which is crucial for production environments:
sudo mysql_secure_installation
sudo yum install MariaDB-server MariaDB-client
Optionally, to set up a password and so forth, which is crucial for production environments:
sudo mysql_secure_installation
dnf install MariaDB-server MariaDB-client
Optionally, to set up a password and so forth, which is crucial for production environments:
sudo mysql_secure_installation
After installing the MariaDB server and client, consider securing the installation by running mariadb-secure-installation (or mysql_secure_installation on MariaDB 10.3 or older).
Example commands for podman are used below, but in general I think you can simply replace 'podman' with 'docker' in the commands below if you prefer that. Podman has the advantage that it can run without sudo privileges, although you do need to make some config changes - run something like this as root:
usermod --add-subuids 200000-201000 --add-subgids 200000-201000 $YOUR_USER
Also make sure that /proc/sys/user/max_user_namespaces contains a number greater than 0:
cat /proc/sys/user/max_user_namespaces
sysctl user.max_user_namespaces=15000
Thanks to the provided Dockerfile, we can easily create a container image which includes both MariaDB and the ispyb-database schema:
podman build -t ispyb-database .
Make a note of the image hash in the last line of output.
podman create -p 127.0.0.1:3306:3306 --name ispyb-database -e MARIADB_ROOT_PASSWORD=rootpassword <image hash>
List networks and choose one - 'podman' should be installed by default:
podman network ls
podman network inspect podman
In the JSON output, the 'subnet' gives you the range of IP addresses you can use. Choose one and specify it in a command like:
podman run --net podman --ip 10.88.0.5 --name ispyb-database -e MYSQL_ROOT_PASSWORD=rootpassword -d <image hash>
podman start ispyb-database
podman ps
You can also log in to the container:
podman exec -it ispyb-database bash
And while you're there, you might want to see which time zone the container has:
ll /etc/localtime
lrwxrwxrwx 1 root root 24 Dec 7 04:55 /etc/localtime -> /usr/share/zoneinfo//UTC
You may want to set the same time zone as on your host machine:
mv /etc/localtime /etc/localtime-backup
ln -s /usr/share/zoneinfo/Europe/London /etc/localtime
Now that the container is running, you can connect to the database the usual way - but only if you have a mariadb client. This could be e.g. the standard CLI client from MariaDB a.k.a. mariadb or mysql in older versions, or it could be a client such as DBeaver or Python with the mariadb package installed.
The IP address is the one you specified in method 2 or 127.0.0.1 in method 1 - and the port is the default one - 3306. Assuming you use the standard CLI mariadb client, you can connect with:
mariadb -h10.88.0.5 -uroot -pmypass
podman stop ispyb-database
Only do this if you no longer need the container:
podman rm -v ispyb-database
You can download binaries for a different version and install e.g. in your /scratch area:
E.g. download the latest stable version from downloads.mariadb.org : https://downloads.mariadb.org/mariadb/10.3.9/#file_type=tar_gz&bits=64
Unpack the archive and rename the installation directory (assumes you've downloaded the tar.gz file into your /tmp dir):
cd /scratch
tar xvfz /tmp/mariadb-10.3.9-linux-x86_64.tar.gz
mv mariadb-10.3.9-linux-x86_64 mariadb
export MARIADB_HOME=/scratch/mariadb
Create the data directory e.g. like this:
cd $MARIADB_HOME
scripts/mysql_install_db --basedir=$MARIADB_HOME --datadir=/scratch/mariadb/data --no-defaults
Create a config file at $HOME/.my.cnf with the following content:
[client]
user = root
protocol = tcp
bin/mysqld_safe --no-defaults --datadir=/scratch/mariadb/data &
Note: --no-defaults must be the first parameter!
You can now connect to the server as the MariaDB root user – note that no password is required:
bin/mysql
The version of the binaries needs to match the version of the data directory. So if you upgrade to newer binaries, then you also need to upgrade your data directory.
Assuming you've already done "module load mariadb-server", the server is started and you have a $HOME/.my.cnf file as outlined above, then all you have to do is:
mysql_upgrade
Note that it it's fine to run this more than once, or on a data directory that doesn't need upgrading. Useful client commands
help;
shutdown;
SHOW DATABASES;
SELECT user, host FROM mysql.user;
exit;
CREATE DATABASE mydb;
USE mydb;
CREATE USER webapp@'%' IDENTIFIED BY 'myPasswordHere...';
GRANT SELECT, UPDATE, INSERT, DELETE ON mydb.* TO webapp@'%';
DROP DATABASE mydb;
DROP USER webapp@'%';
REVOKE SELECT, UPDATE, INSERT, DELETE ON mydb.* FROM webapp@'%';
Note that database, table and user names are (by default) case sensitive.
The '%' character is the wildcard character in SQL, though '*' is used in some contexts (as seen in the examples above).