Database Install

Installing MariaDB on Debian 8

This link is where to find the binary downloads for Debian ( and other operating systems as well ), as compiling the huge source code for the database is not really possible, well…. for mere mortals at least.

Step 1.    #  apt-get install software-properties-common
The following extra packages will be installed:
  dh-python gir1.2-glib-2.0 libdbus-glib-1-2 libgirepository-1.0-1 libmpdec2 
  libpython3-stdlib libpython3.4-minimal libpython3.4-stdlib python3 
  python3-apt python3-dbus python3-gi python3-minimal
  python3-software-properties python3.4 python3.4-minimal unattended-upgrades
Suggested packages:
  python3-doc python3-tk python3-venv python3-apt-dbg python-apt-doc 
  python-dbus-doc python3-dbus-dbg python3.4-venv python3.4-doc 
  binfmt-support
The following NEW packages will be installed:
  dh-python gir1.2-glib-2.0 libdbus-glib-1-2 libgirepository-1.0-1 libmpdec2
  libpython3-stdlib libpython3.4-minimal libpython3.4-stdlib python3 
  python3-apt python3-dbus python3-gi python3-minimal
  python3-software-properties python3.4 python3.4-minimal 
  software-properties-common unattended-upgrades
0 upgraded, 18 newly installed, 0 to remove and 0 not upgraded.
Need to get 6,079 kB of archives.
After this operation, 22.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Step 2.  #  apt-key adv --recv-keys --keyserver   \
                          keyserver.ubuntu.com 0xcbcb082a1bb943db
Step 3.   #  add-apt-repository 'deb [arch=amd64,i386,ppc64el]  http://mirror.aarnet.edu.au/pub/MariaDB/repo/10.1/debian  jessie main'

Step 4.  #  apt-get update   ( this takes a few minutes )

Step 5.  #  apt-get install mariadb-server  
                    ( time to get another coffee while this downloads )
Reading package lists... Done
Building dependency tree      
Reading state information... Done
The following extra packages will be installed:
  galera-3 gawk iproute libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl 
  libjemalloc1 libmariadbclient18 libmysqlclient18 libreadline5 mariadb-client-10.1 
  mariadb-client-core-10.1 mariadb-common mariadb-server-10.1 
  mariadb-server-core-10.1  mysql-common rsync socat
Suggested packages:
  gawk-doc libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl 
  libipc-sharedcache-perl libterm-readkey-perl mariadb-test netcat-openbsd tinyca
The following NEW packages will be installed:
  galera-3 gawk iproute libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl 
  libjemalloc1 libmariadbclient18 libmysqlclient18 libreadline5 mariadb-client-10.1 
  mariadb-client-core-10.1 mariadb-common mariadb-server mariadb-server-10.1 
  mariadb-server-core-10.1 mysql-common rsync socat
0 upgraded, 20 newly installed, 0 to remove and 0 not upgraded.
Need to get 23.1 MB of archives.
After this operation, 190 MB of additional disk space will be used.
Do you want to continue? [Y/n]

This install will prompt for the “root” password, which of course, is best to enter and remember by writing in an actual book and kept some place safe and handy ☺

This install also starts up the database.

[Note] /usr/sbin/mysqld (mysqld 10.1.22-MariaDB-1~jessie) starting as process 13867 ...
[Note] InnoDB: Using mutexes to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Using SSE crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 256.0M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: 128 rollback segment(s) are active.
[Note] InnoDB: Waiting for purge to start
[Note] InnoDB:  Percona XtraDB (http://www.percona.com) 
              5.6.35-80.0 started; log sequence number 1616819
[Note] InnoDB: Dumping buffer pool(s) not yet started
[Note] Plugin 'FEEDBACK' is disabled.
Setting up mariadb-server (10.1.22+maria-1~jessie) ...
Processing triggers for libc-bin (2.19-18+deb8u7) ...
Processing triggers for systemd (215-17+deb8u6) ...

The mariadb database will be found running as mysql.

# root:/var/log [1025]# ps -ef | grep mysql

        mysql    14361     1  0 10:06 ?           00:00:00 /usr/sbin/mysqld
        root     14423  8665  0 10:08 pts/0    00:00:00 grep mysql

Also, this install creates a operating system user “mysql” for the database to run under.

# systemctl status mysqld.service  ( will show the database is running etc )
# systemctl stop  mysqld.service  ( will stop the database running )
# systemctl start mysqld.service  ( will start the database running )

The startup service is created automatically by the install as:-

]# cat  /etc/systemd/system/ mysql.service

# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.
#
# Thanks to:
# Daniel Black
# Erkan Yanar
# David Strauss
# and probably others

[Unit]
Description=MariaDB database server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service
Alias=mysqld.service

[Service]
##############################################################################
## Core requirements
##

Type=notify

# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.

PrivateNetwork=false

##############################################################################
## Package maintainers
##

User=mysql
Group=mysql

# To allow memlock to be used as non-root user if set in configuration
CapabilityBoundingSet=CAP_IPC_LOCK

# Prevent writes to /usr, /boot, and /etc
ProtectSystem=full

# Doesn't yet work properly with SELinux enabled
# NoNewPrivileges=true

PrivateDevices=true

# Prevent accessing /home, /root and /run/user
ProtectHome=true

# Execute pre and post scripts as root, otherwise it does it as User=
PermissionsStartOnly=true

ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld

# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.

# Do not panic if galera_recovery script is not available. (MDEV-10538)
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "[ ! -e /usr/bin/galera_recovery ] && VAR= || \

VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] \
&& systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"

# Needed to create system tables etc.
# ExecStartPre=/usr/bin/mysql_install_db -u mysql

# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster

ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS 
                                                $_WSREP_NEW_CLUSTER 
                                                $_WSREP_START_POSITION

ExecStartPost=/etc/mysql/debian-start

# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
KillMode=process
KillSignal=SIGTERM
# Don't want to see an automated SIGKILL ever
SendSIGKILL=no
# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s
UMask=007

################################################################
## USERs can override
##
##
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following will override this file's settings.
# Useful options not previously available in [mysqld_safe]
# Kernels like killing mysqld when out of memory because its big.
# Lets temper that preference a little.
# OOMScoreAdjust=-600
# Explicitly start with high IO priority
# BlockIOWeight=1000
# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.

PrivateTmp=false

##
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
##
# Number of files limit. previously [mysqld_safe] open-file-limit

LimitNOFILE=16364

# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=
# Nice priority. previously [mysqld_safe] nice
# Nice=-5
# Timezone. previously [mysqld_safe] timezone
# Environment="TZ=UTC"
# Library substitutions. previously [mysqld_safe] malloc-lib with explicit paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=
# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sync
# ExecStartPre=sysctl -q -w vm.drop_caches=3
# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/sbin/mysqld......
# crash-script equalivent
# FailureAction=

——————–

Step 6.  Some looking around house keeping.
To change the root password:
               /usr/bin/mysqladmin  -u root password 'new-password'
To log into the database
              /usr/bin/mysql  -u  root  -p  ( will prompt for the root password )

             MariaDB [(none)]> help           ( will list the common database commands )
             MariaDB [(none)]> show databases; ( will list the names of the databases )
             MariaDB [(none)]> use mysql;  ( to start accessing the database’s database )
             MariaDB [mysql]> show tables;  ( lists the tables in this database )
             MariaDB [mysql]> quit; ( to log out of the database )

To display the configuration settings for this installed database:-

root:/usr/sbin [1058]# ./mysqld –help  –verbose

2017-04-12 10:45:00 140031735216064 [Note] Plugin 'FEEDBACK' is disabled.
./mysqld  Ver 10.1.22-MariaDB-1~jessie for debian-linux-gnu on x86_64 
        (mariadb.org binary distribution)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Starts the MariaDB database server.
Usage: ./mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf  ( non existent )
/etc/mysql/my.cnf   ( this is probably it )
~/.my.cnf     ( also non existent )
The following groups are read: mysqld server mysqld-10.1 mariadb mariadb-10.1 
            client-server galera
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
..... etc

root:/usr/sbin [1060]# ./mysqld --print-defaults
./mysqld would have been started with the following arguments:
--user=mysql
--pid-file=/var/run/mysqld/mysqld.pid
--socket=/var/run/mysqld/mysqld.sock
--port=3306
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc_messages_dir=/usr/share/mysql
--lc_messages=en_US
--skip-external-locking
--bind-address=127.0.0.1
--max_connections=100
--connect_timeout=5
--wait_timeout=600
--max_allowed_packet=16M
--thread_cache_size=128
--sort_buffer_size=4M
--bulk_insert_buffer_size=16M
--tmp_table_size=32M
--max_heap_table_size=32M
--myisam_recover_options=BACKUP
--key_buffer_size=128M
--table_open_cache=400
--myisam_sort_buffer_size=512M
--concurrent_insert=2
--read_buffer_size=2M
--read_rnd_buffer_size=1M
--query_cache_limit=128K
--query_cache_size=64M
--log_warnings=2
--slow_query_log_file=/var/log/mysql/mariadb-slow.log
--long_query_time=10
--log_slow_verbosity=query_plan
--log_bin=/var/log/mysql/mariadb-bin
--log_bin_index=/var/log/mysql/mariadb-bin.index
--expire_logs_days=10
--max_binlog_size=100M
--default_storage_engine=InnoDB
--innodb_buffer_pool_size=256M
--innodb_log_buffer_size=8M
--innodb_file_per_table=1
--innodb_open_files=400
--innodb_io_capacity=400
--innodb_flush_method=O_DIRECT

——————–

© 2017, James Harry Burton. All rights reserved.