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:// | ||
| + | |||