Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

centos:mariadb [20.04.2018 09:08. ]
centos:mariadb [22.07.2019 14:52. ] (aktuell)
Zeile 1: Zeile 1:
 +====== MariaDB Datenbankserver unter CentOS 7.x ======
 +{{:​centos:​mariadb-seal-shaded-browntext.png?​nolink&​300 |Bild: MariaDB Logo}} Nachdem [[http://​www.oracle.com/​de/​index.html|Oracle]] die Firma [[http://​de.wikipedia.org/​wiki/​Sun_Microsystems|Sun Microsystems]] im Jahr 2009 [[http://​www.golem.de/​0904/​66578.html|aufkaufte]],​ entschloss sich der Hauptentwickler [[http://​monty-says.blogspot.de/​|Ulf Michael Widenius]] der OpenSource Datenbank ​
 +[[http://​www.mysql.de|mySQL]] als eigenständigen Fork weiter zu entwickeln. Für das neue relationales Open-Source-Datenbankverwaltungssystem,​ welcher grundsätzlich zu MySQL kompatibel ist, wählte Widenius den Namen MariaDB((in Anlehnung an den Vornamen seiner Tochter)). Im [[http://​www.informatik-aktuell.de/​betrieb/​datenbanken/​mariadb-als-strategische-entscheidung.html|folgenden Artikel]] findet man tiefergehende Informationen zu den Unterschieden beider Datenbank-Daemon.
 +
 +MariaDB löste mit CentOS7 die bis dahin verwendete MYSQL-Datenbank ab. Wir werden uns in diesem Kapitel mit der Installation von MariaDB beschäftigen,​ nicht zuletzt, da wir die Datenbank für die viele unserer Projekte verwenden werden, wie z.B.:
 +  * [[voip:​start|Asterisk]]
 +  * [[centos:​cacti_-_installation_und_konfiguration|cacti]]
 +  * [[fun:​energie-funk-mess-system_em_1000|fhem]]
 +  * [[centos:​mailserver:​horde_-_create._communicate._collaborate|horde]]
 +  * [[wetter:​wview:​start|wview]]
 +  * [[centos:​piwik|Piwik]]
 +  * [[centos:​web_c7:​nextcloud|Nextcloud]]
 +
 +===== Installation =====
 +Die Installation unseres Datenbankservers gestaltet sich recht einfach, das das notwendige Programmpaket als RPM aus dem Base-Repository unserer CentOS-Installation zur Verfügung gestellt wird.
 +Die Installation selbst erfolgt mit dem Paketverwaltungs-Utility **yum** von CentOS 7.
 +   # yum install mariadb-server -y
 +
 +Neben dem Server-Part **mysql-server** wird auch der Client-Part **mysql** sowie weitere Perl-Datenbankmodule installiert. ​
 +Was uns die einzelnen Programmpakete mitbringen, erkunden wir bei Bedarf mit der Option //**qil**// beim Programm **rpm**. ​
 +   # rpm -qil mysql-server
 +
 +<​code>​Name ​       : mariadb-server
 +Epoch       : 1
 +Version ​    : 5.5.52
 +Release ​    : 1.el7
 +Architecture:​ x86_64
 +Install Date: Mon 20 Feb 2017 06:20:37 PM CET
 +Group       : Applications/​Databases
 +Size        : 58204272
 +License ​    : GPLv2 with exceptions and LGPLv2 and BSD
 +Signature ​  : RSA/SHA256, Sun 20 Nov 2016 08:24:51 PM CET, Key ID 24c6a8a7f4a80eb5
 +Source RPM  : mariadb-5.5.52-1.el7.src.rpm
 +Build Date  : Tue 15 Nov 2016 02:20:59 AM CET
 +Build Host  : c1bm.rdu2.centos.org
 +Relocations : (not relocatable)
 +Packager ​   : CentOS BuildSystem <​http://​bugs.centos.org>​
 +Vendor ​     : CentOS
 +URL         : http://​mariadb.org
 +Summary ​    : The MariaDB server and related files
 +Description :
 +MariaDB is a multi-user, multi-threaded SQL database server. It is a
 +client/​server implementation consisting of a server daemon (mysqld)
 +and many different client programs and libraries. This package contains
 +the MariaDB server and some accompanying files and directories.
 +MariaDB is a community developed branch of MySQL.
 +/​etc/​logrotate.d/​mariadb
 +/​etc/​my.cnf.d/​server.cnf
 +/​usr/​bin/​innochecksum
 +/​usr/​bin/​myisam_ftdump
 +/​usr/​bin/​myisamchk
 +/​usr/​bin/​myisamlog
 +/​usr/​bin/​myisampack
 +/​usr/​bin/​mysql_convert_table_format
 +/​usr/​bin/​mysql_fix_extensions
 +/​usr/​bin/​mysql_install_db
 +/​usr/​bin/​mysql_plugin
 +/​usr/​bin/​mysql_secure_installation
 +/​usr/​bin/​mysql_setpermission
 +/​usr/​bin/​mysql_tzinfo_to_sql
 +/​usr/​bin/​mysql_upgrade
 +/​usr/​bin/​mysql_zap
 +/​usr/​bin/​mysqlbug
 +/​usr/​bin/​mysqld_multi
 +/​usr/​bin/​mysqld_safe
 +/​usr/​bin/​mysqldumpslow
 +/​usr/​bin/​mysqlhotcopy
 +/​usr/​bin/​mysqltest
 +/​usr/​bin/​perror
 +/​usr/​bin/​replace
 +/​usr/​bin/​resolve_stack_dump
 +/​usr/​bin/​resolveip
 +/​usr/​lib/​systemd/​system/​mariadb.service
 +/​usr/​lib/​tmpfiles.d/​mariadb.conf
 +/​usr/​lib64/​mysql/​INFO_BIN
 +/​usr/​lib64/​mysql/​INFO_SRC
 +/​usr/​lib64/​mysql/​mysqlbug
 +/​usr/​lib64/​mysql/​plugin
 +/​usr/​lib64/​mysql/​plugin/​adt_null.so
 +/​usr/​lib64/​mysql/​plugin/​auth_0x0100.so
 +/​usr/​lib64/​mysql/​plugin/​auth_pam.so
 +/​usr/​lib64/​mysql/​plugin/​auth_socket.so
 +/​usr/​lib64/​mysql/​plugin/​auth_test_plugin.so
 +/​usr/​lib64/​mysql/​plugin/​daemon_example.ini
 +/​usr/​lib64/​mysql/​plugin/​dialog_examples.so
 +/​usr/​lib64/​mysql/​plugin/​ha_innodb.so
 +/​usr/​lib64/​mysql/​plugin/​ha_sphinx.so
 +/​usr/​lib64/​mysql/​plugin/​handlersocket.so
 +/​usr/​lib64/​mysql/​plugin/​libdaemon_example.so
 +/​usr/​lib64/​mysql/​plugin/​mypluglib.so
 +/​usr/​lib64/​mysql/​plugin/​qa_auth_client.so
 +/​usr/​lib64/​mysql/​plugin/​qa_auth_interface.so
 +/​usr/​lib64/​mysql/​plugin/​qa_auth_server.so
 +/​usr/​lib64/​mysql/​plugin/​query_cache_info.so
 +/​usr/​lib64/​mysql/​plugin/​semisync_master.so
 +/​usr/​lib64/​mysql/​plugin/​semisync_slave.so
 +/​usr/​lib64/​mysql/​plugin/​server_audit.so
 +/​usr/​lib64/​mysql/​plugin/​sphinx.so
 +/​usr/​lib64/​mysql/​plugin/​sql_errlog.so
 +/​usr/​libexec/​mariadb-prepare-db-dir
 +/​usr/​libexec/​mariadb-wait-ready
 +/​usr/​libexec/​mysqld
 +/​usr/​share/​man/​man1/​innochecksum.1.gz
 +/​usr/​share/​man/​man1/​msql2mysql.1.gz
 +/​usr/​share/​man/​man1/​myisam_ftdump.1.gz
 +/​usr/​share/​man/​man1/​myisamchk.1.gz
 +/​usr/​share/​man/​man1/​myisamlog.1.gz
 +/​usr/​share/​man/​man1/​myisampack.1.gz
 +/​usr/​share/​man/​man1/​mysql.server.1.gz
 +/​usr/​share/​man/​man1/​mysql_convert_table_format.1.gz
 +/​usr/​share/​man/​man1/​mysql_fix_extensions.1.gz
 +/​usr/​share/​man/​man1/​mysql_install_db.1.gz
 +/​usr/​share/​man/​man1/​mysql_plugin.1.gz
 +/​usr/​share/​man/​man1/​mysql_secure_installation.1.gz
 +/​usr/​share/​man/​man1/​mysql_setpermission.1.gz
 +/​usr/​share/​man/​man1/​mysql_tzinfo_to_sql.1.gz
 +/​usr/​share/​man/​man1/​mysql_upgrade.1.gz
 +/​usr/​share/​man/​man1/​mysql_zap.1.gz
 +/​usr/​share/​man/​man1/​mysqlbinlog.1.gz
 +/​usr/​share/​man/​man1/​mysqlbug.1.gz
 +/​usr/​share/​man/​man1/​mysqlcheck.1.gz
 +/​usr/​share/​man/​man1/​mysqld_multi.1.gz
 +/​usr/​share/​man/​man1/​mysqld_safe.1.gz
 +/​usr/​share/​man/​man1/​mysqldumpslow.1.gz
 +/​usr/​share/​man/​man1/​mysqlhotcopy.1.gz
 +/​usr/​share/​man/​man1/​mysqlimport.1.gz
 +/​usr/​share/​man/​man1/​mysqltest.1.gz
 +/​usr/​share/​man/​man1/​perror.1.gz
 +/​usr/​share/​man/​man1/​replace.1.gz
 +/​usr/​share/​man/​man1/​resolve_stack_dump.1.gz
 +/​usr/​share/​man/​man1/​resolveip.1.gz
 +/​usr/​share/​man/​man8/​mysqld.8.gz
 +/​usr/​share/​mysql/​README.mysql-cnf
 +/​usr/​share/​mysql/​errmsg-utf8.txt
 +/​usr/​share/​mysql/​fill_help_tables.sql
 +/​usr/​share/​mysql/​my-huge.cnf
 +/​usr/​share/​mysql/​my-innodb-heavy-4G.cnf
 +/​usr/​share/​mysql/​my-large.cnf
 +/​usr/​share/​mysql/​my-medium.cnf
 +/​usr/​share/​mysql/​my-small.cnf
 +/​usr/​share/​mysql/​mysql_performance_tables.sql
 +/​usr/​share/​mysql/​mysql_system_tables.sql
 +/​usr/​share/​mysql/​mysql_system_tables_data.sql
 +/​usr/​share/​mysql/​mysql_test_data_timezone.sql
 +/​var/​lib/​mysql
 +/​var/​log/​mariadb
 +/​var/​log/​mariadb/​mariadb.log
 +/​var/​run/​mariadb</​code>​
 +
 +===== Konfiguration =====
 +==== my.cnf ====
 +Die Konfiguration unseres Datenbankservers erfolgt über die Konfigurationsdatei //​**/​etc/​my.cnf**//,​ die uns bei der Installation bereits mitgeliefert wurde. ​
 +   # less /etc/my.cnf
 +<file bash /​etc/​my.cnf>​[mysqld]
 +datadir=/​var/​lib/​mysql
 +socket=/​var/​lib/​mysql/​mysql.sock
 +# Disabling symbolic-links is recommended to prevent assorted security risks
 +symbolic-links=0
 +# Settings user and group are ignored when systemd is used.
 +# If you need to run mysqld under a different user or group,
 +# customize your systemd unit file for mariadb according to the
 +# instructions in http://​fedoraproject.org/​wiki/​Systemd
 +
 +[mysqld_safe]
 +log-error=/​var/​log/​mariadb/​mariadb.log
 +pid-file=/​var/​run/​mariadb/​mariadb.pid
 +
 +#
 +# include all files from the config directory
 +#
 +!includedir /​etc/​my.cnf.d
 +</​file>​
 +Eine genau Beschreibung aller Server System Variablen ist [[https://​mariadb.com/​kb/​en/​mariadb/​server-system-variables/​|hier]] zu finden.
 +
 +Zusätzliche Beispiele finden sich übrigens auch im Verzeichnis //​**/​usr/​share/​mysql/​**//​.
 +   # ll /​usr/​share/​mysql/​*.cnf
 +<​code>​-rw-r--r--. 1 root root  4920 Nov 15 00:14 /​usr/​share/​mysql/​my-huge.cnf
 +-rw-r--r--. 1 root root 20438 Nov 15 00:14 /​usr/​share/​mysql/​my-innodb-heavy-4G.cnf
 +-rw-r--r--. 1 root root  4907 Nov 15 00:14 /​usr/​share/​mysql/​my-large.cnf
 +-rw-r--r--. 1 root root  4920 Nov 15 00:14 /​usr/​share/​mysql/​my-medium.cnf
 +-rw-r--r--. 1 root root  2846 Nov 15 00:14 /​usr/​share/​mysql/​my-small.cnf
 +</​code>​
 +
 +Bei Bedarf, wie z.B. der Angabe eines spezifischen Datenbankverzeichnisses,​ tragen wir unsere individuelle Konfiguration in der **my.cnf** nach.
 +<​code>​[mysqld]
 +# Django : 2015-02-07 spezifisches Datenbankverzeichnis angegeben
 +# default: datadir=/​var/​lib/​mysql
 +datadir=/​var/​lib/​mysql/​data
 +...
 +</​code>​
 +
 +==== erster Start ====
 +Nun ist es an der Zeit unseren Datenbank-Server das erste mal zu starten.
 +   # systemctl start mariadb.service
 +
 +Der Start wird im Logfile des Datenbankservers //​**/​var/​log/​mariadb/​mariadb.log**//​ entsprechend dokumentiert.
 +   # less /​var/​log/​mariadb/​mariadb.log
 +<​code>​170220 18:25:10 mysqld_safe Starting mysqld daemon with databases from /​var/​lib/​mysql
 +170220 18:25:10 [Note] /​usr/​libexec/​mysqld (mysqld 5.5.52-MariaDB) starting as process 3256 ...
 +170220 18:25:10 InnoDB: The InnoDB memory heap is disabled
 +170220 18:25:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
 +170220 18:25:10 InnoDB: Compressed tables use zlib 1.2.7
 +170220 18:25:10 InnoDB: Using Linux native AIO
 +170220 18:25:10 InnoDB: Initializing buffer pool, size = 128.0M
 +170220 18:25:10 InnoDB: Completed initialization of buffer pool
 +InnoDB: The first specified data file ./ibdata1 did not exist:
 +InnoDB: a new database to be created!
 +170220 18:​25:​10 ​ InnoDB: Setting file ./ibdata1 size to 10 MB
 +InnoDB: Database physically writes the file full: wait...
 +170220 18:​25:​10 ​ InnoDB: Log file ./​ib_logfile0 did not exist: new to be created
 +InnoDB: Setting log file ./​ib_logfile0 size to 5 MB
 +InnoDB: Database physically writes the file full: wait...
 +170220 18:​25:​10 ​ InnoDB: Log file ./​ib_logfile1 did not exist: new to be created
 +InnoDB: Setting log file ./​ib_logfile1 size to 5 MB
 +InnoDB: Database physically writes the file full: wait...
 +InnoDB: Doublewrite buffer not found: creating new
 +InnoDB: Doublewrite buffer created
 +InnoDB: 127 rollback segment(s) active.
 +InnoDB: Creating foreign key constraint system tables
 +InnoDB: Foreign key constraint system tables created
 +170220 18:​25:​11 ​ InnoDB: Waiting for the background threads to start
 +170220 18:25:12 Percona XtraDB (http://​www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 0
 +170220 18:25:12 [Note] Plugin '​FEEDBACK'​ is disabled.
 +170220 18:25:12 [Note] Server socket created on IP: '​0.0.0.0'​.
 +170220 18:25:12 [Note] Event Scheduler: Loaded 0 events
 +170220 18:25:12 [Note] /​usr/​libexec/​mysqld:​ ready for connections.
 +Version: '​5.5.52-MariaDB' ​ socket: '/​var/​lib/​mysql/​mysql.sock' ​ port: 3306  MariaDB Server
 +</​code>​
 +
 +In unserem Datenbankverzeichnis //​**/​var/​lib/​mysql/​data**//​ wurden auch die ersten Datenbankdateien angelegt.
 +   # ll /​var/​lib/​mysql/​data
 +
 +<​code>​total 28700
 +-rw-rw----. 1 mysql mysql    16384 Mar  7 22:10 aria_log.00000001
 +-rw-rw----. 1 mysql mysql       52 Mar  7 22:10 aria_log_control
 +-rw-rw----. 1 mysql mysql 18874368 Mar  7 22:10 ibdata1
 +-rw-rw----. 1 mysql mysql  5242880 Mar  7 22:10 ib_logfile0
 +-rw-rw----. 1 mysql mysql  5242880 Mar  7 22:10 ib_logfile1
 +drwx------. 2 mysql mysql     4096 Mar  7 22:10 mysql
 +srwxrwxrwx. 1 mysql mysql        0 Mar  7 22:10 mysql.sock
 +drwx------. 2 mysql mysql     4096 Mar  7 22:10 performance_schema
 +drwx------. 2 mysql mysql        6 Mar  7 22:10 test
 +</​code>​
 +
 +
 +
 +Möchten wir überprüfen,​ ob der MariaDB-Server läuft, haben wir mehrere Möglichkeiten.
 +  - **systemctl** \\ <​code>​ # systemctl status mariadb -l</​code>​ <​html><​pre class="​code">​
 +<font style="​color:​ rgb(0, 255, 0)"><​b>​● </​b></​font><​font style="​color:​ rgb(0, 0, 0)">​mariadb.service - MariaDB database server
 +   ​Loaded:​ loaded (/​usr/​lib/​systemd/​system/​mariadb.service;​ disabled; vendor preset: disabled)
 +   ​Active:​ <font style="​color:​ rgb(0, 255, 0)"><​b>​active (running) </​b></​font><​font style="​color:​ rgb(0, 0, 0)">​since Mon 2017-02-20 18:25:12 CET; 1min 6s ago
 +  Process: 3099 ExecStartPost=/​usr/​libexec/​mariadb-wait-ready $MAINPID (code=exited,​ status=0/​SUCCESS)
 +  Process: 3019 ExecStartPre=/​usr/​libexec/​mariadb-prepare-db-dir %n (code=exited,​ status=0/​SUCCESS)
 + Main PID: 3098 (mysqld_safe)
 +   ​CGroup:​ /​system.slice/​mariadb.service
 +           ​├─3098 /bin/sh /​usr/​bin/​mysqld_safe --basedir=/​usr
 +           ​└─3256 /​usr/​libexec/​mysqld --basedir=/​usr --datadir=/​var/​lib/​mysql --plugin-dir=/​usr/​lib64/​mysql/​plugin --log-error=/​var/​log/​mariadb/​mariadb.log --pid-file=/​var/​run/​mariadb/​mariadb.pid --socket=/​var/​lib/​mysql/​mysql.sock
 +
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]:​ To start mysqld at boot time you have to copy
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]:​ support-files/​mysql.server to the right place for your system
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]:​ PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]:​ To do so, start the server, then issue the following commands:
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]:​ '/​usr/​bin/​mysqladmin'​ -u root password '​new-password'​
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]:​ '/​usr/​bin/​mysqladmin'​ -u root -h vml000117.dmz.nausch.org password '​new-password'​
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]:​ Alternatively you can run:
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mysqld_safe[3098]:​ 170220 18:25:10 mysqld_safe Logging to '/​var/​log/​mariadb/​mariadb.log'​.
 +Feb 20 18:25:10 vml000117.dmz.nausch.org mysqld_safe[3098]:​ 170220 18:25:10 mysqld_safe Starting mysqld daemon with databases from /​var/​lib/​mysql
 +Feb 20 18:25:12 vml000117.dmz.nausch.org systemd[1]: Started MariaDB database server.</​font>​
 +</​pre></​html>​
 +  - **ps** \\ <​code>​ # ps aux | grep mysql</​code><​code>​mysql ​   27039  0.0  0.1 115344 ​ 1620 ?        Ss   ​22:​10 ​  0:00 /bin/sh /​usr/​bin/​mysqld_safe --basedir=/​usr
 +mysql    27198  0.0  8.3 905348 84784 ?        Sl   ​22:​10 ​  0:01 /​usr/​libexec/​mysqld --basedir=/​usr --datadir=/​var/​lib/​mysql/​data --plugin-dir=/​usr/​lib64/​mysql/​plugin --log-error=/​var/​log/​mariadb/​mariadb.log --pid-file=/​var/​run/​mariadb/​mariadb.pid --socket=/​var/​lib/​mysql/​mysql.sock
 +root     ​27302 ​ 0.0  0.0 112640 ​  924 pts/0    R+   ​22:​41 ​  0:00 grep --color=auto mysql</​code>​
 +  - **netstat** \\ <​code>​ # netstat -tulpn</​code><​code>​Proto Recv-Q Send-Q Local Address ​          ​Foreign Address ​        ​State ​      ​PID/​Program name    ​
 +tcp        0      0 127.0.0.1:​25 ​           0.0.0.0:​* ​              ​LISTEN ​     1728/​master ​        
 +tcp        0      0 0.0.0.0:​3306 ​           0.0.0.0:​* ​              ​LISTEN ​     27198/​mysqld ​       ​
 +tcp        0      0 0.0.0.0:​22 ​             0.0.0.0:​* ​              ​LISTEN ​     1357/​sshd ​          
 +tcp6       ​0 ​     0 ::​1:​25 ​                 :::*                    LISTEN ​     1728/​master ​        
 +tcp6       ​0 ​     0 :::80                   :::​* ​                   LISTEN ​     26249/​httpd ​        
 +tcp6       ​0 ​     0 :::22                   :::​* ​                   LISTEN ​     1357/​sshd ​          
 +udp        0      0 0.0.0.0:​40525 ​          ​0.0.0.0:​* ​                          ​571/​avahi-daemon:​ r 
 +udp        0      0 0.0.0.0:​52944 ​          ​0.0.0.0:​* ​                          ​588/​chronyd ​        
 +udp        0      0 0.0.0.0:​5353 ​           0.0.0.0:​* ​                          ​571/​avahi-daemon:​ r 
 +udp        0      0 127.0.0.1:​323 ​          ​0.0.0.0:​* ​                          ​588/​chronyd ​        
 +</​code>​
 +
 +==== automatisches Starten des Dienste beim Systemstart ==== 
 +Damit nun unser MariaDBL-Server beim Booten automatisch gestartet wird, nehmen wir noch folgenden Konfigurationsschritt vor.
 +   # systemctl enable mariadb
 +
 +   ln -s '/​usr/​lib/​systemd/​system/​mariadb.service'​ '/​etc/​systemd/​system/​multi-user.target.wants/​mariadb.service'​
 +
 +Wollen wir überprüfen,​ ob der Datenbank-Daemon beim Serverstart automatisch gestartet wird, fragen wir dies mit folgendem Befehl ab.
 +   # systemctl is-enabled mariadb
 +
 +   ​enabled
 +
 +Startet der Datenbank-Daemon nicht automatisch,​ wird ein **disabled** zurück gemeldet.
 +
 +==== Paketfilter/​Firewall ====
 +Damit wir später von den berechtigten Hosts Verbindungen zu unserem MariaDB-Server/​-Daemon aufbauen können, müssen wir für diese noch Änderungen am Paketfilter **firewalld** vornehmen.
 +
 +Unter **CentOS 7** wird als Standard-Firewall die dynamische **firewalld** verwendet. Ein großer Vorteil der dynamischen Paketfilterregeln ist unter anderem, dass zur Aktivierung der neuen Firewall-Regel(n) nicht der Daemon durchgestartet werden muss und somit alle aktiven Verbindungen kurz getrennt werden. Sondern unsere Änderungen können **//​on-the-fly//​** aktiviert oder auch wieder deaktiviert werden.
 +
 +In unserem Konfigurationsbeispiel hat unser MariaDB-Server die IP-Adresse 10.0.0.37 und der [[wetter:​start|Wetterstations-Server]] die 10.0.0.27. Wir brauchen also eine Firewall-Definition,​ die ausschließlich Verbindungen von der **Source-IP** 10.0.0.27 auf die **Destination-IP** 10.0.0.37 auf Port **3306** gestattet.
 +Mit Hilfe des Programms **firewall-cmd** legen wir nun eine **permanente** Regel in der Zone **public**, dies entspricht in unserem Beispiel das Netzwerk-Interface **eth0** mit der IP **10.0.0.37** an. Als Source-IP geben wir die IP-Adresse unseres Wetterstations-Servers also die **10.0.0.27** an. Genug der Vorrede, mit nachfolgendem Befehl wird diese restriktive Regel angelegt.
 +   # firewall-cmd --permanent --zone=public --add-rich-rule="​rule family="​ipv4"​ source address="​10.0.0.27/​32"​ port protocol="​tcp"​ port="​3306"​ destination address="​10.0.0.37/​32"​ accept"​
 +
 +Zum Aktivieren brauchen wir nun nur einen reload des Firewall-Daemon vornehmen.
 +   # firewall-cmd --reload
 +
 +Fragen wir nun den Regelsatz unserer **iptables**-basieten Firewall ab, finden wir in der Chain **IN_public_allow** unsere aktive Regel.
 +   # iptables -nvL IN_public_allow
 +
 +<​code>​Chain IN_public_allow (1 references)
 + pkts bytes target ​    prot opt in     ​out ​    ​source ​              ​destination ​        
 +  10K   25K ACCEPT ​    ​tcp ​ --  *      *       ​10.0.0.27 ​           10.0.0.37 ​           tcp dpt:3306 ctstate NEW
 + ​2656 ​ 159K ACCEPT ​    ​tcp ​ --  *      *       ​0.0.0.0/​0 ​           0.0.0.0/​0 ​           tcp dpt:22 ctstate NEW
 +</​code>​
 +
 +Natürlich können wir auch mit dem Befehl **firewall-cmd** abfragen, welche Dienste in der Zone **public** geöffnet sind.
 +
 +   # firewall-cmd --zone=public --list-services
 +
 +   mysql ssh
 +
 +==== Installation absichern ====
 +Wie bei der doch großen Ausgabe beim erstmaligen Start des Datenbank-Daemons angeraten, werden wir nun die __sicherheitsrelevanten Konfigurationsänderungen__ vornehmen.
 +
 +Hierzu benutzen wir einfach das mitgelieferte Script //​**/​usr/​bin/​mysql_secure_installation**//,​ welches folgende Änderungen vornimmt:
 +
 +  - Datenbankpasswort des MySQL-Datenbankuser **root** setzen
 +  - Anonyme Benutzerkonten löschen
 +  - Deaktivieren der Remote-Zugriffsmöglichkeit für den MySQL-Datenbankuser **root**
 +  - Löschen der nicht benötigten Testdatenbank **test**
 +
 +<WRAP center round info 85%>
 +Die Fehlermeldung <​code>/​bin/​mysql_secure_installation:​ line 379: find_mysql_client:​ command not found</​code>​ kann ignoriert werden, da aktuell((Stand:​ März 2015)) in dem Script das Unterprogramm **find_mysql_client** nicht definiert wurde.
 +</​WRAP>​
 +
 +
 +   # mysql_secure_installation
 +<​code>/​bin/​mysql_secure_installation:​ line 379: find_mysql_client:​ command not found
 +
 +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
 +you haven'​t set the root password yet, the password will be blank,
 +so you should just press enter here.
 +
 +Enter current password for root (enter for none): ​
 +OK, successfully used password, moving on...
 +
 +Setting the root password ensures that nobody can log into the MariaDB
 +root user without the proper authorisation.
 +
 +Set root password? [Y/​n]</​code>​
 +   y
 +
 +   New password: ​
 +
 +   ​Re-enter new password: ​
 +<​code>​Password updated successfully!
 +Reloading privilege tables..
 + ... Success!
 +
 +
 +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]</​code>​
 +   y
 +<​code>​ ... 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]</​code>​
 +   y
 +<​code>​ ... 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]</​code>​
 +   y
 +<​code>​ - 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]</​code>​
 +   y
 +
 +<​code>​ ... 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!
 +</​code>​
 +
 +==== logrotate ====
 +Bei einem unter Last stehendem MariaDB-Sserver kann unter Umständen das zugehörige Logfile //​**/​var/​log/​mariadb/​mariadb.log**//​ recht schnell anwachsen. In der Datei **mariadb** aus dem Verzeichis //​**/​etc**//​ finden wir dazu alle nötigen Informationen.
 +   # less /​etc/​logrotate.d/​mariadb
 +
 +<​code>#​ This logname can be set in /etc/my.cnf
 +# by setting the variable "​log-error"​
 +# in the [mysqld_safe] section as follows:
 +#
 +# [mysqld_safe]
 +# log-error=/​var/​log/​mariadb/​mariadb.log
 +#
 +# If the root user has a password you have to create a
 +# /​root/​.my.cnf configuration file with the following
 +# content:
 +#
 +# [mysqladmin]
 +# password = <​secret> ​
 +# user= root
 +#
 +# where "<​secret>"​ is the password. ​
 +#
 +# ATTENTION: This /​root/​.my.cnf should be readable ONLY
 +# for root !
 +
 +# Then, un-comment the following lines to enable rotation of mysql'​s log file:
 +
 +#/​var/​log/​mariadb/​mariadb.log {
 +#        create 640 mysql mysql
 +#        notifempty
 +#       daily
 +#        rotate 3
 +#        missingok
 +#        compress
 +#    postrotate
 +#       # just if mysqld is really running
 +#       if test -x /​usr/​bin/​mysqladmin && \
 +#          /​usr/​bin/​mysqladmin ping &>/​dev/​null
 +#       then
 +#          /​usr/​bin/​mysqladmin flush-logs
 +#       fi
 +#    endscript
 +#}
 +</​code>​
 +
 +In der MariaDB-Konfigurationsdatei //​**/​etc/​my.cnf**//​ aus dem RPM ist in der Sektion **[mysqld_safe]** das Error-Log gesetzt: ​
 +   ​log-error=/​var/​log/​mariadb/​mariadb.log
 +
 +Da wir dem im Kapitel [[centos:​mariadb#​installation_absichern]] für den User **root** ein Passwort vergeben haben, werden wir nun die Authentifizierungsdaten im Verzeichnis des Users **root** ablegen. Hierzu legen wir erst einmal diese Datei an.
 +   # touch /​root/​.my.cnf
 +
 +Anschließend stellen wir sicher dass auch wirklich nur **root** diese Datei lesen kann.
 +   # chmod 600 /​root/​.my.cnf
 +
 +Bevor wir die Daten in der gerade angelegten Datei hinterlegen,​ überprüfen wir noch, ob auch wirklich die Berechtigungen passen.
 +   # ll /​root/​.my.cnf ​
 +
 +<​code>​-rw-------. 1 root root 0 Mar  8 00:15 /​root/​.my.cnf</​code>​
 +
 +Da alles passt, befüllen wir nun die Datei //​**/​root/​.my.cnf**//​.
 +   # vim /​root/​.my.cnf ​
 +<file bash /​root/​.my.cnf>​[mysqladmin]
 +password = dxiFHdig10JXyRAec74j7bcPdyVGX9I1BxcYcoFs
 +user= root
 +</​file>​
 +
 +Nun aktivieren wir noch in der Datei //​**/​etc/​logrotate.d/​mariadb**//​ die Zeilen in der unteren Hälfte, damit der logrotate-Mechanismus auch scharf geschalten ist.
 +   # vim /​etc/​logrotate.d/​mariadb
 +
 +<file bash /​etc/​logotate.d/​mariadb>#​ This logname can be set in /etc/my.cnf
 +# by setting the variable "​log-error"​
 +# in the [mysqld_safe] section as follows:
 +#
 +# [mysqld_safe]
 +# log-error=/​var/​log/​mariadb/​mariadb.log
 +#
 +# If the root user has a password you have to create a
 +# /​root/​.my.cnf configuration file with the following
 +# content:
 +#
 +# [mysqladmin]
 +# password = <​secret> ​
 +# user= root
 +#
 +# where "<​secret>"​ is the password. ​
 +#
 +# ATTENTION: This /​root/​.my.cnf should be readable ONLY
 +# for root !
 +
 +# Then, un-comment the following lines to enable rotation of mysql'​s log file:
 +
 +# Django : 2015-03-08
 +# logrotate aktiviert
 +/​var/​log/​mariadb/​mariadb.log {
 +        create 640 mysql mysql
 +        notifempty
 +        daily
 +        rotate 3
 +        missingok
 +        compress
 +    postrotate
 +        # just if mysqld is really running
 +        if test -x /​usr/​bin/​mysqladmin && \
 +           /​usr/​bin/​mysqladmin ping &>/​dev/​null
 +        then
 +           /​usr/​bin/​mysqladmin flush-logs
 +        fi
 +    endscript
 +}
 +</​file>​
 +
 +===== Datenbankhandling =====
 +==== mysqladmin ====
 +Mit Hilfe des Hilfsprogrammes **mysqladmin** aus dem Clientpaket **mysql** können umfangreiche Abfrage gegen unsere Datenbank gefahren werden. Startet man das Programm ohne weitere Angaben von Optionen, werden die möglichen Optionen am Bildschirm ausgegeben.
 +   # mysqladmin
 +
 +<​code>​mysqladmin ​ Ver 9.0 Distrib 5.5.41-MariaDB,​ for Linux on x86_64
 +Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 +
 +Administration program for the mysqld daemon.
 +Usage: mysqladmin [OPTIONS] command command....
 +
 +Default options are read from the following files in the given order:
 +/​etc/​mysql/​my.cnf /etc/my.cnf ~/​.my.cnf ​
 +The following groups are read: mysqladmin client client-server client-mariadb
 +The following options may be given as the first argument:
 +--print-defaults ​       Print the program argument list and exit.
 +--no-defaults ​          ​Don'​t read default options from any option file.
 +--defaults-file=# ​      Only read default options from the given file #.
 +--defaults-extra-file=#​ Read this file after the global files are read.
 +
 +  -c, --count=# ​      ​Number of iterations to make. This works with -i
 +                      (--sleep) only.
 +  --debug-check ​      Check memory and open file usage at exit.
 +  --debug-info ​       Print some debug info at exit.
 +  -f, --force ​        ​Don'​t ask for confirmation on drop database; with
 +                      multiple commands, continue even if an error occurs.
 +  -C, --compress ​     Use compression in server/​client protocol.
 +  --character-sets-dir=name ​
 +                      Directory for character set files.
 +  --default-character-set=name ​
 +                      Set the default character set.
 +  -?, --help ​         Display this help and exit.
 +  -h, --host=name ​    ​Connect to host.
 +  -b, --no-beep ​      Turn off beep on error.
 +  -p, --password[=name] ​
 +                      Password to use when connecting to server. If password is
 +                      not given it's asked from the tty.
 +  -P, --port=# ​       Port number to use for connection or 0 for default to, in
 +                      order of preference, my.cnf, $MYSQL_TCP_PORT,​
 +                      /​etc/​services,​ built-in default (3306).
 +  --protocol=name ​    The protocol to use for connection (tcp, socket, pipe,
 +                      memory).
 +  -r, --relative ​     Show difference between current and previous values when
 +                      used with -i. Currently only works with extended-status.
 +  -s, --silent ​       Silently exit if one can't connect to server.
 +  -S, --socket=name ​  The socket file to use for connection.
 +  -i, --sleep=# ​      ​Execute commands repeatedly with a sleep between.
 +  --ssl               ​Enable SSL for connection (automatically enabled with
 +                      other flags).
 +  --ssl-ca=name ​      CA file in PEM format (check OpenSSL docs, implies
 +                      --ssl).
 +  --ssl-capath=name ​  CA directory (check OpenSSL docs, implies --ssl).
 +  --ssl-cert=name ​    X509 cert in PEM format (implies --ssl).
 +  --ssl-cipher=name ​  SSL cipher to use (implies --ssl).
 +  --ssl-key=name ​     X509 key in PEM format (implies --ssl).
 +  --ssl-verify-server-cert ​
 +                      Verify server'​s "​Common Name" in its cert against
 +                      hostname used when connecting. This option is disabled by
 +                      default.
 +  -u, --user=name ​    User for login if not current user.
 +  -v, --verbose ​      Write more information.
 +  -V, --version ​      ​Output version information and exit.
 +  -E, --vertical ​     Print output vertically. Is similar to --relative, but
 +                      prints output vertically.
 +  -w, --wait[=#​] ​     Wait and retry if connection is down.
 +  --connect-timeout=# ​
 +  --shutdown-timeout=# ​
 +  --plugin-dir=name ​  ​Directory for client-side plugins.
 +  --default-auth=name Default authentication client-side plugin to use.
 +
 +Variables (--variable-name=value)
 +and boolean options {FALSE|TRUE} ​ Value (after reading options)
 +--------------------------------- ----------------------------------------
 +count                             0
 +debug-check ​                      FALSE
 +debug-info ​                       FALSE
 +force                             FALSE
 +compress ​                         FALSE
 +character-sets-dir ​               (No default value)
 +default-character-set ​            auto
 +host                              (No default value)
 +no-beep ​                          FALSE
 +port                              0
 +relative ​                         FALSE
 +socket ​                           (No default value)
 +sleep                             0
 +ssl                               FALSE
 +ssl-ca ​                           (No default value)
 +ssl-capath ​                       (No default value)
 +ssl-cert ​                         (No default value)
 +ssl-cipher ​                       (No default value)
 +ssl-key ​                          (No default value)
 +ssl-verify-server-cert ​           FALSE
 +user                              root
 +verbose ​                          FALSE
 +vertical ​                         FALSE
 +connect-timeout ​                  43200
 +shutdown-timeout ​                 3600
 +plugin-dir ​                       (No default value)
 +default-auth ​                     (No default value)
 +
 +Where command is a one or more of: (Commands may be shortened)
 +  create databasename ​    ​Create a new database
 +  debug                   ​Instruct server to write debug information to log
 +  drop databasename ​      ​Delete a database and all its tables
 +  extended-status ​        Gives an extended status message from the server
 +  flush-all-statistics ​   Flush all statistics tables
 +  flush-all-status ​       Flush status and statistics
 +  flush-client-statistics Flush client statistics
 +  flush-hosts ​            Flush all cached hosts
 +  flush-index-statistics ​ Flush index statistics
 +  flush-logs ​             Flush all logs
 +  flush-privileges ​       Reload grant tables (same as reload)
 +  flush-slow-log ​         Flush slow query log
 +  flush-status ​           Clear status variables
 +  flush-table-statistics ​ Clear table statistics
 +  flush-tables ​           Flush all tables
 +  flush-threads ​          Flush the thread cache
 +  flush-user-statistics ​  Flush user statistics
 +  kill id,​id,​... ​       Kill mysql threads
 +  password [new-password] Change old password to new-password in current format
 +  old-password [new-password] Change old password to new-password in old format
 +  ping                  Check if mysqld is alive
 +  processlist ​          Show list of active threads in server
 +  reload ​               Reload grant tables
 +  refresh ​              Flush all tables and close and open logfiles
 +  shutdown ​             Take server down
 +  status ​               Gives a short status message from the server
 +  start-slave ​          Start slave
 +  stop-slave ​           Stop slave
 +  variables ​            ​Prints variables available
 +  version ​              Get version info from server
 +</​code>​
 +
 +So können wir z.B. die verwendete Version von **MariaDB** abfragen.
 +   # mysqladmin version
 +
 +<​code>​mysqladmin ​ Ver 9.0 Distrib 5.5.41-MariaDB,​ for Linux on x86_64
 +Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 +
 +Server version ​         5.5.41-MariaDB
 +Protocol version ​       10
 +Connection ​             Localhost via UNIX socket
 +UNIX socket ​            /​var/​lib/​mysql/​mysql.sock
 +Uptime: ​                2 hours 33 min 19 sec
 +
 +Threads: 1  Questions: 27  Slow queries: 0  Opens: 1  Flush tables: 2  Open tables: 27  Queries per second avg: 0.002
 +</​code>​
 +
 +==== mysql ====
 +Der Zugriff auf unseren MariaDB-Server nehmen wir in der Regel mit dem Werkzeug **mysql** vor. So können wir z.B. sehr leicht und einfach den Status unseres Datenbankservers abfragen.
 +   # mysql -h localhost -u root -p
 +
 +   Enter password: dxiFHdig10JXyRAec74j7bcPdyVGX9I1BxcYcoFs
 +
 +<​code>​Welcome to the MariaDB monitor. ​ Commands end with ; or \g.
 +Your MariaDB connection id is 12
 +Server version: 5.5.41-MariaDB MariaDB Server
 +
 +Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 +
 +Type '​help;'​ or '​\h'​ for help. Type '​\c'​ to clear the current input statement.
 +
 +MariaDB [(none)]>​
 +</​code>​
 +Auch hier können wir uns nun den Status des Daemon anzeigen lassen. Hierzu verwenden wir den SQL-Befehl **status**, den wir mit einem Strichpunkt **;** abschließen.
 +<​code>​MariaDB [(none)]>​ status;
 +--------------
 +mysql  Ver 15.1 Distrib 5.5.41-MariaDB,​ for Linux (x86_64) using readline 5.1
 +
 +Connection id:          12
 +Current database:
 +Current user:           ​root@localhost
 +SSL:                    Not in use
 +Current pager: ​         stdout
 +Using outfile: ​         ''​
 +Using delimiter: ​       ;
 +Server: ​                ​MariaDB
 +Server version: ​        ​5.5.41-MariaDB MariaDB Server
 +Protocol version: ​      10
 +Connection: ​            ​Localhost via UNIX socket
 +Server characterset: ​   latin1
 +Db     ​characterset: ​   latin1
 +Client characterset: ​   utf8
 +Conn.  characterset: ​   utf8
 +UNIX socket: ​           /​var/​lib/​mysql/​mysql.sock
 +Uptime: ​                2 hours 38 min 10 sec
 +
 +Threads: 1  Questions: 31  Slow queries: 0  Opens: 1  Flush tables: 2  Open tables: 27  Queries per second avg: 0.003
 +--------------
 +
 +MariaDB [(none)]>​
 +</​code>​
 +Die Verbindung zum Datenbank-Daemon beenden wir mit dem Befehl **quit**.
 +   ​MariaDB [(none)]>​ quit;
 +
 +   Bye
 +
 +==== Datenbank-Dump ====
 +Zur Sicherung unserer MariaDB-Tabellen legen wir uns ein kleines Script an, mit dessen Hilfe wir täglich eine Sicherung der kompletten Datenbank vornehmen können.
 +   # touch /​root/​bin/​mariadb_fulldump
 +
 +Damit das Script später auch nur vom User **root** gelesen udn ausgeführt werden kann, setzen wir noch kurz die Dateirechte entsprechend.
 +   # chmod 700 /​root/​bin/​mariadb_fulldump
 +
 +Nun efüllen wir noch unser Script.
 +   # vim /​root/​bin/​mariadb_fulldump
 +<file bash /​root/​bin/​mariadb_fulldump>#​!/​bin/​bash
 +
 +##################################################################################​
 +# Script-Name : mysqldump.sh ​                                                    # ​
 +# Description : Datenbank-Dump der kompletten (alle Tabellen) unserer ​           #
 +#               ​MariaDB nach /​root/​mysql/​dumps ​                                  #
 +#               Drei Datensicherungen werden aufgehoben, ältere werden gelöscht. # 
 +#                                                                                # 
 +#                                                                                # 
 +#                                                                                # 
 +# Last update : 13.05.2013 ​                                                      # ​
 +# Version ​    : 0.01                                                             # ​
 +##################################################################################​
 +
 +##################################################################################​
 +#                                  H I S T O R Y                                 # ​
 +##################################################################################​
 +# Version ​    : 0.01                                                             # ​
 +# Description : initial release ​                                                 #
 +# ------------------------------------------------------------------------------ # 
 +# Version ​    : x.xx                                                             # ​
 +# Description : <​Description> ​                                                   #
 +##################################################################################​
 +
 +# Source function library.
 +. /​etc/​init.d/​functions
 +
 +# Definition der systemindividuellen Variablen
 +
 +# Script-Name.
 +SCRIPT_NAME='​mariadb_fulldump'​
 +
 +# Backup-Verzeichnis.
 +DIR_TARGET='/​root/​mysql/​dump'​
 +DUMP_FILES="​$DIR_TARGET/​*.sql"​
 +
 +# Mail-Empfänger
 +MAIL_RECIPIENT='​django@nausch.org'​
 +
 +# Status-Mail versenden? [J|N].
 +MAIL_STATUS='​J'​
 +
 +# Datenbankdefinitionen
 +DB_HOST="​127.0.0.1"​
 +DB_USER="​root"​
 +DB_SECRET="​immNI+32$cHU551n5Kn13gn1uS4W6HYu0SAJwH8W"​
 +
 +# Variablen
 +MYSQLDUMP_COMMAND=`command -v mysqldump`
 +TOUCH_COMMAND=`command -v touch`
 +RM_COMMAND=`command -v rm`
 +PROG_SENDMAIL=`command -v sendmail`
 +CAT_COMMAND=`command -v cat`
 +DATE_COMMAND=`command -v date`
 +MKDIR_COMMAND=`command -v mkdir`
 +FILE_NAME='/'​$SCRIPT_NAME'​.'​`$DATE_COMMAND '​+%Y-%m-%d-%H%M%S'​`'​.sql'​
 +FILE_LOCK='/​tmp/'​$SCRIPT_NAME'​.lock'​
 +FILE_LOG='/​var/​log/'​$SCRIPT_NAME'​.log'​
 +FILE_LAST_LOG='/​tmp/'​$SCRIPT_NAME'​.log'​
 +FILE_MAIL='/​tmp/'​$SCRIPT_NAME'​.mail'​
 +VAR_HOSTNAME=`uname -n`
 +VAR_SENDER='​root@'​$VAR_HOSTNAME
 +VAR_EMAILDATE=`$DATE_COMMAND '+%a, %d %b %Y %H:%M:%S (%Z)'`
 +
 +# Functionen
 +function log() {
 +        echo $1
 +        echo `$DATE_COMMAND '​+%Y/​%m/​%d %H:​%M:​%S'​` " INFO:" $1 >>​${FILE_LAST_LOG}
 +}
 +
 +function movelog() {
 +        $CAT_COMMAND $FILE_LAST_LOG >> $FILE_LOG
 +        $RM_COMMAND -f $FILE_LAST_LOG
 +        $RM_COMMAND -f $FILE_LOCK
 +}
 +
 +function sendmail() {
 +        case "​$1"​ in
 +        '​STATUS'​)
 +                MAIL_SUBJECT='​Status execution '​$SCRIPT_NAME'​ script.'​
 +        ;;
 +        *)
 +                MAIL_SUBJECT='​ERROR while execution '​$SCRIPT_NAME'​ script !!!'
 +        ;;
 +        esac
 +
 +$CAT_COMMAND <<​MAIL >​$FILE_MAIL
 +Subject: $MAIL_SUBJECT
 +Date: $VAR_EMAILDATE
 +From: $VAR_SENDER
 +To: $MAIL_RECIPIENT
 +
 +MAIL
 +
 +$CAT_COMMAND $FILE_LAST_LOG >> $FILE_MAIL
 +
 +$PROG_SENDMAIL -f $VAR_SENDER -t $MAIL_RECIPIENT < $FILE_MAIL
 +
 +$RM_COMMAND -f $FILE_MAIL
 +
 +}
 +
 +# Main.
 +log ""​
 +log "​+-------------------------------------------------------------------------------+"​
 +log "| ........................ Start des MariaDB-Dumps ............................ |"
 +log "​+-------------------------------------------------------------------------------+"​
 +log ""​
 +log "Das Datenbank-Backupscript wurde mit folgenden Parametern aufgerufen:"​
 +log ""​
 +log "​SCRIPT_NAME ​    : $SCRIPT_NAME"​
 +log "​ZIEL-VERZEICHNIS:​ $DIR_TARGET"​
 +log "​MAIL_EMPFÄNGER ​ : $MAIL_RECIPIENT"​
 +log "​MAIL_STATUS ​    : $MAIL_STATUS"​
 +log ""​
 +
 +# Prüfung ob alle benötigten Programme und Befehle vorhanden sind.
 +if [ ! -s "​$MYSQLDUMP_COMMAND"​ ]; then
 +        log "​Prüfen,​ ob das Programm '​$MYSQLDUMP_COMMAND'​ vorhanden ist.................[FEHLER]"​
 +        sendmail ERROR
 +        movelog
 +        exit 10
 +else
 +        log "​Prüfen,​ ob das Programm '​$MYSQLDUMP_COMMAND'​ vorhanden ist.................[ ​ OK  ]"
 +fi
 +
 +if [ ! -s "​$TOUCH_COMMAND"​ ]; then
 +        log "​Prüfen,​ ob das Programm '​$TOUCH_COMMAND'​ vorhanden ist.........................[FEHLER]"​
 +        sendmail ERROR
 +        movelog
 +        exit 11
 +else
 +        log "​Prüfen,​ ob das Programm '​$TOUCH_COMMAND'​ vorhanden ist.........................[ ​ OK  ]"
 +fi
 +
 +if [ ! -s "​$RM_COMMAND"​ ]; then
 +        log "​Prüfen,​ ob das Programm '​$RM_COMMAND'​ vorhanden ist............................[FEHLER]"​
 +        sendmail ERROR
 +        movelog
 +        exit 12
 +else
 +        log "​Prüfen,​ ob das Programm '​$RM_COMMAND'​ vorhanden ist............................[ ​ OK  ]"
 +fi
 +
 +if [ ! -s "​$CAT_COMMAND"​ ]; then
 +        log "​Prüfen,​ ob das Programm '​$CAT_COMMAND'​ vorhanden ist..........................[FEHLER]"​
 +        sendmail ERROR
 +        movelog
 +        exit 13
 +else
 +        log "​Prüfen,​ ob das Programm '​$CAT_COMMAND'​ vorhanden ist...........................[ ​ OK  ]"
 +fi
 +
 +if [ ! -s "​$DATE_COMMAND"​ ]; then
 +        log "​Prüfen,​ ob das Programm '​$DATE_COMMAND'​ vorhanden ist...........................[FEHLER]"​
 +        sendmail ERROR
 +        movelog
 +        exit 14
 +else
 +        log "​Prüfen,​ ob das Programm '​$DATE_COMMAND'​ vorhanden ist..........................[ ​ OK  ]"
 +fi
 +
 +if [ ! -s "​$MKDIR_COMMAND"​ ]; then
 +        log "​Prüfen,​ ob das Programm '​$MKDIR_COMMAND'​ vorhanden ist..........................[FEHLER]"​
 +        sendmail ERROR
 +        movelog
 +        exit 15
 +else
 +        log "​Prüfen,​ ob das Programm '​$MKDIR_COMMAND'​ vorhanden ist.........................[ ​ OK  ]"
 +fi
 +
 +if [ ! -s "​$PROG_SENDMAIL"​ ]; then
 +        log "​Prüfen,​ ob das Programm '​$PROG_SENDMAIL'​ vorhanden ist.................[FEHLER]"​
 +        sendmail ERROR
 +        movelog
 +        exit 16
 +else
 +        log "​Prüfen,​ ob das Programm '​$PROG_SENDMAIL'​ vorhanden ist.................[ ​ OK  ]"
 +fi
 +
 +if [ ! -e "​$FILE_LOCK"​ ]; then
 +        log "​Prüfen,​ ob das Programm nicht bereits oder noch läuft......................[ ​ OK  ]"
 +
 +        $TOUCH_COMMAND $FILE_LOCK
 +else
 +        log "​Prüfen,​ ob das Programm nicht bereits oder noch läuft......................[FEHLER]"​
 +        log ""​
 +        log "​FEHLER:​ Das Script läuft bereits bzw. immer noch, oder die LOCK-Datei"​
 +        log "​existiert noch von einem früheren Programmaufruf!"​
 +        log ""​
 +        sendmail ERROR
 +        movelog
 +        exit 20
 +fi
 +
 +if [ ! -d "​$DIR_TARGET"​ ]; then
 +        log "​Prüfen,​ ob Zielverzeichnis existiert.......................................[FEHLER]"​
 +        log ""​
 +        log " INFO: Erstelle Zielverzeichnis!"​
 +        log " INFO: --> "​$DIR_TARGET
 +        log ""​
 +
 +        $MKDIR_COMMAND -p $DIR_TARGET
 +else
 +        log "​Prüfen,​ ob Zielverzeichnis existiert.......................................[ ​ OK  ]"
 +fi
 +
 +if [ "​$UID"​ -ne 0 ]; then
 +        log "​Prüfen,​ ob das Script mit root-Rechten gestartet wurde.......................[FEHLER]"​
 +        log ""​
 +        sendmail ERROR
 +        movelog
 +        exit 21
 +else
 +        log "​Prüfen,​ ob das Script mit root-Rechten gestartet wurde.....................[ ​ OK  ]"
 +fi
 +
 +# Start dumping.
 +log ""​
 +log "​+-------------------------------------------------------------------------------+"​
 +log "| .................... Start des Datenbank-Dumps .............................. |"
 +log "​+-------------------------------------------------------------------------------+"​
 +log ""​
 +
 +log "​$MYSQLDUMP_COMMAND -h "​$DB_HOST"​ -u "​$DB_USER"​ --all-databases --events > $DIR_TARGET$FILE_NAME"​
 +
 +$MYSQLDUMP_COMMAND -h $DB_HOST -u $DB_USER --password=$DB_SECRET --all-databases --events > $DIR_TARGET$FILE_NAME
 +
 +if [ "​$?"​ != 0 ]; then
 +        log ""​
 +        $RM_COMMAND -f $FILE_LOCK
 +        sendmail ERROR
 +        movelog
 +        exit 99
 +else
 +        log ""​
 +        log "​+-------------------------------------------------------------------------------+"​
 +        log "| ........................ Datenbank-Dump beendet ............................. |"
 +        log "​+-------------------------------------------------------------------------------+"​
 +        log ""​
 +fi
 +
 +# Bis auf die letzten drei Datenbankbackups alle anderen Dateien löschen.
 +cd $DIR_TARGET/​
 +(ls $DUMP_FILES -t|head -n 3;ls $DUMP_FILES )|sort|uniq -u|xargs rm
 +if [ "​$?"​ != "​0"​ ]; then
 +        log "alte Datenbanksicherungen aus Zielverzeichnis $DIR_TARGET gelöscht....[FEHLER]"​
 +        log ""​
 +        sendmail ERROR
 +        movelog
 +        exit 69
 +else
 +        log "alte Datenbanksicherungen aus Zielverzeichnis $DIR_TARGET gelöscht....[ ​ OK  ]"
 +        log ""​
 +fi
 +
 +# Finish syncing.
 +log "​+-------------------------------------------------------------------------------+"​
 +log "| .......................... Ende des MariaDB-Dumps ........................... |"
 +log "​+-------------------------------------------------------------------------------+"​
 +log ""​
 +
 +# Status eMail versenden
 +if [ $MAIL_STATUS = '​J'​ ]; then
 +        sendmail STATUS
 +fi
 +
 +# Temporäres Logfile permanent sichern
 +movelog
 +
 +exit 0
 +</​file>​
 +
 +
 +Anschließend tragen wir noch in der Datei **/​etc/​crontab** ein, daß das Script täglich um 3:20 Uhr laufen soll.
 +   # vim /​etc/​crontab
 +
 +<file bash /​etc/​crontab>​SHELL=/​bin/​bash
 +PATH=/​sbin:/​bin:/​usr/​sbin:/​usr/​bin
 +MAILTO=root
 +HOME=/
 +
 +# For details see man 4 crontabs
 +
 +# Example of job definition:
 +# .---------------- minute (0 - 59)
 +# |  .------------- hour (0 - 23)
 +# |  |  .---------- day of month (1 - 31)
 +# |  |  |  .------- month (1 - 12) OR jan,​feb,​mar,​apr ...
 +# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,​mon,​tue,​wed,​thu,​fri,​sat
 +# |  |  |  |  |
 +# *  *  *  *  * user-name command to be executed
 +
 +# Django: 2013-05-13 täglicher MariaDB-Datenbankdump
 +20  3  *  *  root /​root/​bin/​mariadb_fulldump 1>/​dev/​null 2>&1
 +
 +</​file>​
 +
 +==== phpMyAdmin ====
 +Zur komfortablen Administration unserer [[centos:​mariadb|MariaDB unter CentOS 7.x]] greifen wir auf das PHP-Projekt [[http://​phpmyadmin.sourceforge.net/​|phpMyAdmin]] zurück. Im Kapitel [[centos:​web_c7:​phpmyadmin|phpMyAdmin unter CentOS 7.x installieren und einrichten]] ist die Installation und Konfiguration des PHP Projektes unter CentOS 7.x beschrieben.
 +
 +====== Links ======
 +  * **[[wiki:​start|Zurück zu Projekte und Themenkapitel]]**
 +  * **[[http://​dokuwiki.nausch.org/​doku.php/​|Zurück zur Startseite]]**
 +