Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Vorhergehende Überarbeitung | |||
— | centos:mariadb [22.07.2019 14:52. ] (aktuell) – Externe Bearbeitung 127.0.0.1 | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ====== MariaDB Datenbankserver unter CentOS 7.x ====== | ||
+ | {{: | ||
+ | [[http:// | ||
+ | |||
+ | 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, | ||
+ | * [[voip: | ||
+ | * [[centos: | ||
+ | * [[fun: | ||
+ | * [[centos: | ||
+ | * [[wetter: | ||
+ | * [[centos: | ||
+ | * [[centos: | ||
+ | |||
+ | ===== 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 | ||
+ | |||
+ | < | ||
+ | Epoch : 1 | ||
+ | Version | ||
+ | Release | ||
+ | Architecture: | ||
+ | Install Date: Mon 20 Feb 2017 06:20:37 PM CET | ||
+ | Group : Applications/ | ||
+ | Size : 58204272 | ||
+ | License | ||
+ | Signature | ||
+ | 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 | ||
+ | Vendor | ||
+ | URL : http:// | ||
+ | Summary | ||
+ | Description : | ||
+ | MariaDB is a multi-user, multi-threaded SQL database server. It is a | ||
+ | client/ | ||
+ | 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. | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | / | ||
+ | |||
+ | ===== Konfiguration ===== | ||
+ | ==== my.cnf ==== | ||
+ | Die Konfiguration unseres Datenbankservers erfolgt über die Konfigurationsdatei // | ||
+ | # less /etc/my.cnf | ||
+ | <file bash / | ||
+ | datadir=/ | ||
+ | socket=/ | ||
+ | # 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:// | ||
+ | |||
+ | [mysqld_safe] | ||
+ | log-error=/ | ||
+ | pid-file=/ | ||
+ | |||
+ | # | ||
+ | # include all files from the config directory | ||
+ | # | ||
+ | !includedir / | ||
+ | </ | ||
+ | Eine genau Beschreibung aller Server System Variablen ist [[https:// | ||
+ | |||
+ | Zusätzliche Beispiele finden sich übrigens auch im Verzeichnis // | ||
+ | # ll / | ||
+ | < | ||
+ | -rw-r--r--. 1 root root 20438 Nov 15 00:14 / | ||
+ | -rw-r--r--. 1 root root 4907 Nov 15 00:14 / | ||
+ | -rw-r--r--. 1 root root 4920 Nov 15 00:14 / | ||
+ | -rw-r--r--. 1 root root 2846 Nov 15 00:14 / | ||
+ | </ | ||
+ | |||
+ | Bei Bedarf, wie z.B. der Angabe eines spezifischen Datenbankverzeichnisses, | ||
+ | < | ||
+ | # Django : 2015-02-07 spezifisches Datenbankverzeichnis angegeben | ||
+ | # default: datadir=/ | ||
+ | datadir=/ | ||
+ | ... | ||
+ | </ | ||
+ | |||
+ | ==== 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 // | ||
+ | # less / | ||
+ | < | ||
+ | 170220 18:25:10 [Note] / | ||
+ | 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: | ||
+ | InnoDB: Database physically writes the file full: wait... | ||
+ | 170220 18: | ||
+ | InnoDB: Setting log file ./ | ||
+ | InnoDB: Database physically writes the file full: wait... | ||
+ | 170220 18: | ||
+ | InnoDB: Setting log file ./ | ||
+ | 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: | ||
+ | 170220 18:25:12 Percona XtraDB (http:// | ||
+ | 170220 18:25:12 [Note] Plugin ' | ||
+ | 170220 18:25:12 [Note] Server socket created on IP: ' | ||
+ | 170220 18:25:12 [Note] Event Scheduler: Loaded 0 events | ||
+ | 170220 18:25:12 [Note] / | ||
+ | Version: ' | ||
+ | </ | ||
+ | |||
+ | In unserem Datenbankverzeichnis // | ||
+ | # ll / | ||
+ | |||
+ | < | ||
+ | -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 | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | Möchten wir überprüfen, | ||
+ | - **systemctl** \\ < | ||
+ | <font style=" | ||
+ | | ||
+ | | ||
+ | Process: 3099 ExecStartPost=/ | ||
+ | Process: 3019 ExecStartPre=/ | ||
+ | Main PID: 3098 (mysqld_safe) | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]: | ||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]: | ||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]: | ||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]: | ||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]: | ||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]: | ||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mariadb-prepare-db-dir[3019]: | ||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mysqld_safe[3098]: | ||
+ | Feb 20 18:25:10 vml000117.dmz.nausch.org mysqld_safe[3098]: | ||
+ | Feb 20 18:25:12 vml000117.dmz.nausch.org systemd[1]: Started MariaDB database server.</ | ||
+ | </ | ||
+ | - **ps** \\ < | ||
+ | mysql 27198 0.0 8.3 905348 84784 ? Sl | ||
+ | root | ||
+ | - **netstat** \\ < | ||
+ | tcp 0 0 127.0.0.1: | ||
+ | tcp 0 0 0.0.0.0: | ||
+ | tcp 0 0 0.0.0.0: | ||
+ | tcp6 | ||
+ | tcp6 | ||
+ | tcp6 | ||
+ | udp 0 0 0.0.0.0: | ||
+ | udp 0 0 0.0.0.0: | ||
+ | udp 0 0 0.0.0.0: | ||
+ | udp 0 0 127.0.0.1: | ||
+ | </ | ||
+ | |||
+ | ==== 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 '/ | ||
+ | |||
+ | Wollen wir überprüfen, | ||
+ | # systemctl is-enabled mariadb | ||
+ | |||
+ | | ||
+ | |||
+ | Startet der Datenbank-Daemon nicht automatisch, | ||
+ | |||
+ | ==== Paketfilter/ | ||
+ | Damit wir später von den berechtigten Hosts Verbindungen zu unserem MariaDB-Server/ | ||
+ | |||
+ | 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 **// | ||
+ | |||
+ | In unserem Konfigurationsbeispiel hat unser MariaDB-Server die IP-Adresse 10.0.0.37 und der [[wetter: | ||
+ | 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=" | ||
+ | |||
+ | 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 | ||
+ | |||
+ | < | ||
+ | pkts bytes target | ||
+ | 10K 25K ACCEPT | ||
+ | | ||
+ | </ | ||
+ | |||
+ | 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 // | ||
+ | |||
+ | - 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 < | ||
+ | </ | ||
+ | |||
+ | |||
+ | # 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 | ||
+ | you haven' | ||
+ | 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/ | ||
+ | y | ||
+ | |||
+ | New password: | ||
+ | |||
+ | | ||
+ | < | ||
+ | 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. | ||
+ | production environment. | ||
+ | |||
+ | Remove anonymous users? [Y/ | ||
+ | y | ||
+ | < | ||
+ | |||
+ | Normally, root should only be allowed to connect from ' | ||
+ | ensures that someone cannot guess at the root password from the network. | ||
+ | |||
+ | Disallow root login remotely? [Y/ | ||
+ | y | ||
+ | < | ||
+ | |||
+ | By default, MariaDB comes with a database named ' | ||
+ | access. | ||
+ | before moving into a production environment. | ||
+ | |||
+ | Remove test database and access to it? [Y/ | ||
+ | y | ||
+ | < | ||
+ | ... 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/ | ||
+ | y | ||
+ | |||
+ | < | ||
+ | |||
+ | Cleaning up... | ||
+ | |||
+ | All done! If you've completed all of the above steps, your MariaDB | ||
+ | installation should now be secure. | ||
+ | |||
+ | Thanks for using MariaDB! | ||
+ | </ | ||
+ | |||
+ | ==== logrotate ==== | ||
+ | Bei einem unter Last stehendem MariaDB-Sserver kann unter Umständen das zugehörige Logfile // | ||
+ | # less / | ||
+ | |||
+ | < | ||
+ | # by setting the variable " | ||
+ | # in the [mysqld_safe] section as follows: | ||
+ | # | ||
+ | # [mysqld_safe] | ||
+ | # log-error=/ | ||
+ | # | ||
+ | # If the root user has a password you have to create a | ||
+ | # / | ||
+ | # content: | ||
+ | # | ||
+ | # [mysqladmin] | ||
+ | # password = < | ||
+ | # user= root | ||
+ | # | ||
+ | # where "< | ||
+ | # | ||
+ | # ATTENTION: This / | ||
+ | # for root ! | ||
+ | |||
+ | # Then, un-comment the following lines to enable rotation of mysql' | ||
+ | |||
+ | #/ | ||
+ | # create 640 mysql mysql | ||
+ | # notifempty | ||
+ | # daily | ||
+ | # rotate 3 | ||
+ | # missingok | ||
+ | # compress | ||
+ | # postrotate | ||
+ | # # just if mysqld is really running | ||
+ | # if test -x / | ||
+ | # / | ||
+ | # then | ||
+ | # / | ||
+ | # fi | ||
+ | # endscript | ||
+ | #} | ||
+ | </ | ||
+ | |||
+ | In der MariaDB-Konfigurationsdatei // | ||
+ | | ||
+ | |||
+ | Da wir dem im Kapitel [[centos: | ||
+ | # touch / | ||
+ | |||
+ | Anschließend stellen wir sicher dass auch wirklich nur **root** diese Datei lesen kann. | ||
+ | # chmod 600 / | ||
+ | |||
+ | Bevor wir die Daten in der gerade angelegten Datei hinterlegen, | ||
+ | # ll / | ||
+ | |||
+ | < | ||
+ | |||
+ | Da alles passt, befüllen wir nun die Datei // | ||
+ | # vim / | ||
+ | <file bash / | ||
+ | password = dxiFHdig10JXyRAec74j7bcPdyVGX9I1BxcYcoFs | ||
+ | user= root | ||
+ | </ | ||
+ | |||
+ | Nun aktivieren wir noch in der Datei // | ||
+ | # vim / | ||
+ | |||
+ | <file bash / | ||
+ | # by setting the variable " | ||
+ | # in the [mysqld_safe] section as follows: | ||
+ | # | ||
+ | # [mysqld_safe] | ||
+ | # log-error=/ | ||
+ | # | ||
+ | # If the root user has a password you have to create a | ||
+ | # / | ||
+ | # content: | ||
+ | # | ||
+ | # [mysqladmin] | ||
+ | # password = < | ||
+ | # user= root | ||
+ | # | ||
+ | # where "< | ||
+ | # | ||
+ | # ATTENTION: This / | ||
+ | # for root ! | ||
+ | |||
+ | # Then, un-comment the following lines to enable rotation of mysql' | ||
+ | |||
+ | # Django : 2015-03-08 | ||
+ | # logrotate aktiviert | ||
+ | / | ||
+ | create 640 mysql mysql | ||
+ | notifempty | ||
+ | daily | ||
+ | rotate 3 | ||
+ | missingok | ||
+ | compress | ||
+ | postrotate | ||
+ | # just if mysqld is really running | ||
+ | if test -x / | ||
+ | / | ||
+ | then | ||
+ | / | ||
+ | fi | ||
+ | endscript | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | ===== 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 | ||
+ | |||
+ | < | ||
+ | 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: | ||
+ | / | ||
+ | The following groups are read: mysqladmin client client-server client-mariadb | ||
+ | The following options may be given as the first argument: | ||
+ | --print-defaults | ||
+ | --no-defaults | ||
+ | --defaults-file=# | ||
+ | --defaults-extra-file=# | ||
+ | |||
+ | -c, --count=# | ||
+ | (--sleep) only. | ||
+ | --debug-check | ||
+ | --debug-info | ||
+ | -f, --force | ||
+ | multiple commands, continue even if an error occurs. | ||
+ | -C, --compress | ||
+ | --character-sets-dir=name | ||
+ | Directory for character set files. | ||
+ | --default-character-set=name | ||
+ | Set the default character set. | ||
+ | -?, --help | ||
+ | -h, --host=name | ||
+ | -b, --no-beep | ||
+ | -p, --password[=name] | ||
+ | Password to use when connecting to server. If password is | ||
+ | not given it's asked from the tty. | ||
+ | -P, --port=# | ||
+ | order of preference, my.cnf, $MYSQL_TCP_PORT, | ||
+ | / | ||
+ | --protocol=name | ||
+ | memory). | ||
+ | -r, --relative | ||
+ | used with -i. Currently only works with extended-status. | ||
+ | -s, --silent | ||
+ | -S, --socket=name | ||
+ | -i, --sleep=# | ||
+ | --ssl | ||
+ | other flags). | ||
+ | --ssl-ca=name | ||
+ | --ssl). | ||
+ | --ssl-capath=name | ||
+ | --ssl-cert=name | ||
+ | --ssl-cipher=name | ||
+ | --ssl-key=name | ||
+ | --ssl-verify-server-cert | ||
+ | Verify server' | ||
+ | hostname used when connecting. This option is disabled by | ||
+ | default. | ||
+ | -u, --user=name | ||
+ | -v, --verbose | ||
+ | -V, --version | ||
+ | -E, --vertical | ||
+ | prints output vertically. | ||
+ | -w, --wait[=# | ||
+ | --connect-timeout=# | ||
+ | --shutdown-timeout=# | ||
+ | --plugin-dir=name | ||
+ | --default-auth=name Default authentication client-side plugin to use. | ||
+ | |||
+ | Variables (--variable-name=value) | ||
+ | and boolean options {FALSE|TRUE} | ||
+ | --------------------------------- ---------------------------------------- | ||
+ | count 0 | ||
+ | debug-check | ||
+ | debug-info | ||
+ | force FALSE | ||
+ | compress | ||
+ | character-sets-dir | ||
+ | default-character-set | ||
+ | host (No default value) | ||
+ | no-beep | ||
+ | port 0 | ||
+ | relative | ||
+ | socket | ||
+ | sleep 0 | ||
+ | ssl FALSE | ||
+ | ssl-ca | ||
+ | ssl-capath | ||
+ | ssl-cert | ||
+ | ssl-cipher | ||
+ | ssl-key | ||
+ | ssl-verify-server-cert | ||
+ | user root | ||
+ | verbose | ||
+ | vertical | ||
+ | connect-timeout | ||
+ | shutdown-timeout | ||
+ | plugin-dir | ||
+ | default-auth | ||
+ | |||
+ | Where command is a one or more of: (Commands may be shortened) | ||
+ | create databasename | ||
+ | debug | ||
+ | drop databasename | ||
+ | extended-status | ||
+ | flush-all-statistics | ||
+ | flush-all-status | ||
+ | flush-client-statistics Flush client statistics | ||
+ | flush-hosts | ||
+ | flush-index-statistics | ||
+ | flush-logs | ||
+ | flush-privileges | ||
+ | flush-slow-log | ||
+ | flush-status | ||
+ | flush-table-statistics | ||
+ | flush-tables | ||
+ | flush-threads | ||
+ | flush-user-statistics | ||
+ | kill id, | ||
+ | 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 | ||
+ | reload | ||
+ | refresh | ||
+ | shutdown | ||
+ | status | ||
+ | start-slave | ||
+ | stop-slave | ||
+ | variables | ||
+ | version | ||
+ | </ | ||
+ | |||
+ | So können wir z.B. die verwendete Version von **MariaDB** abfragen. | ||
+ | # mysqladmin version | ||
+ | |||
+ | < | ||
+ | Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. | ||
+ | |||
+ | Server version | ||
+ | Protocol version | ||
+ | Connection | ||
+ | UNIX socket | ||
+ | Uptime: | ||
+ | |||
+ | Threads: 1 Questions: 27 Slow queries: 0 Opens: 1 Flush tables: 2 Open tables: 27 Queries per second avg: 0.002 | ||
+ | </ | ||
+ | |||
+ | ==== 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 | ||
+ | |||
+ | < | ||
+ | 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 ' | ||
+ | |||
+ | MariaDB [(none)]> | ||
+ | </ | ||
+ | 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. | ||
+ | < | ||
+ | -------------- | ||
+ | mysql Ver 15.1 Distrib 5.5.41-MariaDB, | ||
+ | |||
+ | Connection id: 12 | ||
+ | Current database: | ||
+ | Current user: | ||
+ | SSL: Not in use | ||
+ | Current pager: | ||
+ | Using outfile: | ||
+ | Using delimiter: | ||
+ | Server: | ||
+ | Server version: | ||
+ | Protocol version: | ||
+ | Connection: | ||
+ | Server characterset: | ||
+ | Db | ||
+ | Client characterset: | ||
+ | Conn. characterset: | ||
+ | UNIX socket: | ||
+ | Uptime: | ||
+ | |||
+ | Threads: 1 Questions: 31 Slow queries: 0 Opens: 1 Flush tables: 2 Open tables: 27 Queries per second avg: 0.003 | ||
+ | -------------- | ||
+ | |||
+ | MariaDB [(none)]> | ||
+ | </ | ||
+ | Die Verbindung zum Datenbank-Daemon beenden wir mit dem Befehl **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 / | ||
+ | |||
+ | 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 / | ||
+ | |||
+ | Nun efüllen wir noch unser Script. | ||
+ | # vim / | ||
+ | <file bash / | ||
+ | |||
+ | ################################################################################## | ||
+ | # Script-Name : mysqldump.sh | ||
+ | # Description : Datenbank-Dump der kompletten (alle Tabellen) unserer | ||
+ | # | ||
+ | # Drei Datensicherungen werden aufgehoben, ältere werden gelöscht. # | ||
+ | # # | ||
+ | # # | ||
+ | # # | ||
+ | # Last update : 13.05.2013 | ||
+ | # Version | ||
+ | ################################################################################## | ||
+ | |||
+ | ################################################################################## | ||
+ | # H I S T O R Y # | ||
+ | ################################################################################## | ||
+ | # Version | ||
+ | # Description : initial release | ||
+ | # ------------------------------------------------------------------------------ # | ||
+ | # Version | ||
+ | # Description : < | ||
+ | ################################################################################## | ||
+ | |||
+ | # Source function library. | ||
+ | . / | ||
+ | |||
+ | # Definition der systemindividuellen Variablen | ||
+ | |||
+ | # Script-Name. | ||
+ | SCRIPT_NAME=' | ||
+ | |||
+ | # Backup-Verzeichnis. | ||
+ | DIR_TARGET='/ | ||
+ | DUMP_FILES=" | ||
+ | |||
+ | # Mail-Empfänger | ||
+ | MAIL_RECIPIENT=' | ||
+ | |||
+ | # Status-Mail versenden? [J|N]. | ||
+ | MAIL_STATUS=' | ||
+ | |||
+ | # Datenbankdefinitionen | ||
+ | DB_HOST=" | ||
+ | DB_USER=" | ||
+ | DB_SECRET=" | ||
+ | |||
+ | # 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='/' | ||
+ | FILE_LOCK='/ | ||
+ | FILE_LOG='/ | ||
+ | FILE_LAST_LOG='/ | ||
+ | FILE_MAIL='/ | ||
+ | VAR_HOSTNAME=`uname -n` | ||
+ | VAR_SENDER=' | ||
+ | VAR_EMAILDATE=`$DATE_COMMAND '+%a, %d %b %Y %H:%M:%S (%Z)'` | ||
+ | |||
+ | # Functionen | ||
+ | function log() { | ||
+ | echo $1 | ||
+ | echo `$DATE_COMMAND ' | ||
+ | } | ||
+ | |||
+ | function movelog() { | ||
+ | $CAT_COMMAND $FILE_LAST_LOG >> $FILE_LOG | ||
+ | $RM_COMMAND -f $FILE_LAST_LOG | ||
+ | $RM_COMMAND -f $FILE_LOCK | ||
+ | } | ||
+ | |||
+ | function sendmail() { | ||
+ | case " | ||
+ | ' | ||
+ | MAIL_SUBJECT=' | ||
+ | ;; | ||
+ | *) | ||
+ | MAIL_SUBJECT=' | ||
+ | ;; | ||
+ | esac | ||
+ | |||
+ | $CAT_COMMAND << | ||
+ | Subject: $MAIL_SUBJECT | ||
+ | Date: $VAR_EMAILDATE | ||
+ | From: $VAR_SENDER | ||
+ | To: $MAIL_RECIPIENT | ||
+ | |||
+ | |||
+ | |||
+ | $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 " | ||
+ | log " | ||
+ | log " | ||
+ | log " | ||
+ | log "" | ||
+ | |||
+ | # Prüfung ob alle benötigten Programme und Befehle vorhanden sind. | ||
+ | if [ ! -s " | ||
+ | log " | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 10 | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | if [ ! -s " | ||
+ | log " | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 11 | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | if [ ! -s " | ||
+ | log " | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 12 | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | if [ ! -s " | ||
+ | log " | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 13 | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | if [ ! -s " | ||
+ | log " | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 14 | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | if [ ! -s " | ||
+ | log " | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 15 | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | if [ ! -s " | ||
+ | log " | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 16 | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | if [ ! -e " | ||
+ | log " | ||
+ | |||
+ | $TOUCH_COMMAND $FILE_LOCK | ||
+ | else | ||
+ | log " | ||
+ | log "" | ||
+ | log " | ||
+ | log " | ||
+ | log "" | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 20 | ||
+ | fi | ||
+ | |||
+ | if [ ! -d " | ||
+ | log " | ||
+ | log "" | ||
+ | log " INFO: Erstelle Zielverzeichnis!" | ||
+ | log " INFO: --> " | ||
+ | log "" | ||
+ | |||
+ | $MKDIR_COMMAND -p $DIR_TARGET | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | if [ " | ||
+ | log " | ||
+ | log "" | ||
+ | sendmail ERROR | ||
+ | movelog | ||
+ | exit 21 | ||
+ | else | ||
+ | log " | ||
+ | fi | ||
+ | |||
+ | # Start dumping. | ||
+ | log "" | ||
+ | log " | ||
+ | log "| .................... Start des Datenbank-Dumps .............................. |" | ||
+ | log " | ||
+ | log "" | ||
+ | |||
+ | log " | ||
+ | |||
+ | $MYSQLDUMP_COMMAND -h $DB_HOST -u $DB_USER --password=$DB_SECRET --all-databases --events > $DIR_TARGET$FILE_NAME | ||
+ | |||
+ | if [ " | ||
+ | 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 [ " | ||
+ | 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....[ | ||
+ | log "" | ||
+ | fi | ||
+ | |||
+ | # Finish syncing. | ||
+ | log " | ||
+ | log "| .......................... Ende des MariaDB-Dumps ........................... |" | ||
+ | log " | ||
+ | log "" | ||
+ | |||
+ | # Status eMail versenden | ||
+ | if [ $MAIL_STATUS = ' | ||
+ | sendmail STATUS | ||
+ | fi | ||
+ | |||
+ | # Temporäres Logfile permanent sichern | ||
+ | movelog | ||
+ | |||
+ | exit 0 | ||
+ | </ | ||
+ | |||
+ | |||
+ | Anschließend tragen wir noch in der Datei **/ | ||
+ | # vim / | ||
+ | |||
+ | <file bash / | ||
+ | PATH=/ | ||
+ | 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, | ||
+ | # | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun, | ||
+ | # | | | | | | ||
+ | # * * * * * user-name command to be executed | ||
+ | |||
+ | # Django: 2013-05-13 täglicher MariaDB-Datenbankdump | ||
+ | 20 3 * * root / | ||
+ | |||
+ | </ | ||
+ | |||
+ | ==== phpMyAdmin ==== | ||
+ | Zur komfortablen Administration unserer [[centos: | ||
+ | |||
+ | ====== Links ====== | ||
+ | * **[[wiki: | ||
+ | * **[[http:// | ||
+ | |||