Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Beide Seiten der vorigen Revision Vorhergehende Überarbeitung | |||
| centos:mysql [13.05.2013 10:38. ] – [Datenbank-Dump] django | centos:mysql [20.04.2018 09:08. ] (aktuell) – Externe Bearbeitung 127.0.0.1 | ||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| + | ====== mySQL-Datenbankserver unter CentOS 6.x einrichten ====== | ||
| + | {{: | ||
| + | \\ | ||
| + | [[http:// | ||
| + | |||
| + | Für viele unserer Anwendungen benötigen wir eine geeignete Datenbank, so z.B. für: | ||
| + | * [[voip: | ||
| + | * [[centos: | ||
| + | * [[fun: | ||
| + | * [[centos: | ||
| + | * [[wetter: | ||
| + | * [[wetter: | ||
| + | * [[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 6. | ||
| + | |||
| + | # yum install mysql-server -y | ||
| + | |||
| + | Neben dem Server-Part **mysql-server** wird auch der Client-Part **mysql** sowie zwei Perl-Datenbankmodule installiert. | ||
| + | Was uns die einzelnen Programmpakete mitbringen, erkunden wir bei Bedarf mit der Option //**qil**// beim Programm **rpm**. | ||
| + | ==== mysql-server ==== | ||
| + | # rpm -qil mysql-server | ||
| + | |||
| + | < | ||
| + | Version | ||
| + | Release | ||
| + | Install Date: Tue 25 Oct 2011 09:32:39 PM CEST Build Host: c6b6.bsys.dev.centos.org | ||
| + | Group : Applications/ | ||
| + | Size : 24481725 | ||
| + | Signature | ||
| + | Packager | ||
| + | URL : http:// | ||
| + | Summary | ||
| + | Description : | ||
| + | MySQL is a multi-user, multi-threaded SQL database server. MySQL is a | ||
| + | client/ | ||
| + | and many different client programs and libraries. This package contains | ||
| + | the MySQL server and some accompanying files and directories. | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | </ | ||
| + | ==== mysql ==== | ||
| + | # rpm -qil mysql | ||
| + | |||
| + | < | ||
| + | Version | ||
| + | Release | ||
| + | Install Date: Tue 25 Oct 2011 09:32:35 PM CEST Build Host: c6b6.bsys.dev.centos.org | ||
| + | Group : Applications/ | ||
| + | Size : 2529479 | ||
| + | Signature | ||
| + | Packager | ||
| + | URL : http:// | ||
| + | Summary | ||
| + | Description : | ||
| + | MySQL is a multi-user, multi-threaded SQL database server. MySQL is a | ||
| + | client/ | ||
| + | and many different client programs and libraries. The base package | ||
| + | contains the standard MySQL client programs and generic MySQL files. | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | </ | ||
| + | ==== perl-DBD-MySQL ==== | ||
| + | # rpm -qil perl-DBD-MySQL | ||
| + | |||
| + | < | ||
| + | Version | ||
| + | Release | ||
| + | Install Date: Tue 25 Oct 2011 09:32:33 PM CEST Build Host: c6b3.bsys.dev.centos.org | ||
| + | Group : Development/ | ||
| + | Size : 345831 | ||
| + | Signature | ||
| + | Packager | ||
| + | URL : http:// | ||
| + | Summary | ||
| + | Description : | ||
| + | An implementation of DBI for MySQL for Perl. | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | </ | ||
| + | ==== perl-DBI ==== | ||
| + | # rpm -qil perl-DBI | ||
| + | |||
| + | < | ||
| + | Version | ||
| + | Release | ||
| + | Install Date: Tue 25 Oct 2011 09:32:30 PM CEST Build Host: c6b1.bsys.dev.centos.org | ||
| + | Group : Development/ | ||
| + | Size : 1733598 | ||
| + | Signature | ||
| + | Packager | ||
| + | URL : http:// | ||
| + | Summary | ||
| + | Description : | ||
| + | DBI is a database access Application Programming Interface (API) for | ||
| + | the Perl Language. The DBI API Specification defines a set of | ||
| + | functions, variables and conventions that provide a consistent | ||
| + | database interface independent of the actual database being used. | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | / | ||
| + | </ | ||
| + | ===== Konfiguration ===== | ||
| + | ==== my.cnf ==== | ||
| + | Die Konfiguration unseres Datenbankservers erfolgt über die Konfigurationsdatei // | ||
| + | |||
| + | <file | / | ||
| + | [mysqld] | ||
| + | datadir=/ | ||
| + | socket=/ | ||
| + | user=mysql | ||
| + | # Disabling symbolic-links is recommended to prevent assorted security risks | ||
| + | symbolic-links=0 | ||
| + | |||
| + | [mysqld_safe] | ||
| + | log-error=/ | ||
| + | pid-file=/ | ||
| + | </ | ||
| + | Weitere Beispiele finden sich im Verzeichnis // | ||
| + | # ll / | ||
| + | < | ||
| + | -rw-r--r-- 1 root root 4780 Jun 25 07:22 my-huge.cnf | ||
| + | -rw-r--r-- 1 root root 20164 Jun 25 07:22 my-innodb-heavy-4G.cnf | ||
| + | -rw-r--r-- 1 root root 4754 Jun 25 07:22 my-large.cnf | ||
| + | -rw-r--r-- 1 root root 4765 Jun 25 07:22 my-medium.cnf | ||
| + | -rw-r--r-- 1 root root 2403 Jun 25 07:22 my-small.cnf | ||
| + | </ | ||
| + | Bei Bedarf, wie z.B. der Angabe eines spezifischen Datenbankverzeichnisses, | ||
| + | < | ||
| + | # Django : 2011-10-25 spezifisches Datenbankverzeichnis angegeben | ||
| + | # default: datadir=/ | ||
| + | datadir=/ | ||
| + | ... | ||
| + | </ | ||
| + | ==== erster Start ==== | ||
| + | Nun ist es an der Zeit unseren Datenbank-Server das erste mal zu starten; hierzu benutzen wir einfach das mitgelieferte Startscript **mysqld** im Verzeichnis // | ||
| + | # ll / | ||
| + | |||
| + | < | ||
| + | # service mysqld start | ||
| + | < | ||
| + | OK | ||
| + | Filling help tables... | ||
| + | OK | ||
| + | |||
| + | To start mysqld at boot time you have to copy | ||
| + | support-files/ | ||
| + | |||
| + | PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! | ||
| + | To do so, start the server, then issue the following commands: | ||
| + | |||
| + | / | ||
| + | / | ||
| + | |||
| + | Alternatively you can run: | ||
| + | / | ||
| + | |||
| + | which will also give you the option of removing the test | ||
| + | databases and anonymous user created by default. | ||
| + | strongly recommended for production servers. | ||
| + | |||
| + | See the manual for more instructions. | ||
| + | |||
| + | You can start the MySQL daemon with: | ||
| + | cd /usr ; / | ||
| + | |||
| + | You can test the MySQL daemon with mysql-test-run.pl | ||
| + | cd / | ||
| + | |||
| + | Please report any problems with the / | ||
| + | |||
| + | | ||
| + | Starting mysqld: | ||
| + | </ | ||
| + | Der Start unseres Servers wir uns in der Logdatei des mySQl-Daemons entsprechend vermerkt: | ||
| + | # less / | ||
| + | < | ||
| + | InnoDB: The first specified data file ./ibdata1 did not exist: | ||
| + | InnoDB: a new database to be created! | ||
| + | 111025 22: | ||
| + | InnoDB: Database physically writes the file full: wait... | ||
| + | 111025 22: | ||
| + | InnoDB: Setting log file ./ | ||
| + | InnoDB: Database physically writes the file full: wait... | ||
| + | 111025 22: | ||
| + | InnoDB: Setting log file ./ | ||
| + | InnoDB: Database physically writes the file full: wait... | ||
| + | InnoDB: Doublewrite buffer not found: creating new | ||
| + | InnoDB: Doublewrite buffer created | ||
| + | InnoDB: Creating foreign key constraint system tables | ||
| + | InnoDB: Foreign key constraint system tables created | ||
| + | 111025 22: | ||
| + | 111025 22:25:22 [Note] Event Scheduler: Loaded 0 events | ||
| + | 111025 22:25:22 [Note] / | ||
| + | Version: ' | ||
| + | </ | ||
| + | Gemäß unserer Konfiguration wurden im unseren data-Verzeichnis die ersten Datenbankfile angelegt. | ||
| + | # ll / | ||
| + | < | ||
| + | -rw-rw---- 1 mysql mysql 10485760 Oct 25 22:25 ibdata1 | ||
| + | -rw-rw---- 1 mysql mysql 5242880 Oct 25 22:25 ib_logfile0 | ||
| + | -rw-rw---- 1 mysql mysql 5242880 Oct 25 22:25 ib_logfile1 | ||
| + | drwx------ 2 mysql mysql 4096 Oct 25 22:25 mysql | ||
| + | </ | ||
| + | |||
| + | ==== automatisches Starten des Dienste beim Systemstart ==== | ||
| + | Damit nun unser mySQL-Datenbankserver beim Booten automatisch gestartet wird, nehmen wir noch folgende Konfigurationsschritte vor. | ||
| + | # chkconfig mysqld on | ||
| + | Anschließend überprüfen wir noch unsere Änderung: | ||
| + | # chkconfig --list | grep mysqld | ||
| + | |||
| + | | ||
| + | |||
| + | ==== 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** | ||
| + | |||
| + | < | ||
| + | |||
| + | |||
| + | NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL | ||
| + | SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! | ||
| + | |||
| + | |||
| + | In order to log into MySQL to secure it, we'll need the current | ||
| + | password for the root user. If you've just installed MySQL, 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 MySQL | ||
| + | root user without the proper authorisation. | ||
| + | |||
| + | Set root password? [Y/n] y | ||
| + | New password: Klausi-is-a-Geek! | ||
| + | Re-enter new password: Klausi-is-a-Geek! | ||
| + | Password updated successfully! | ||
| + | Reloading privilege tables.. | ||
| + | ... Success! | ||
| + | |||
| + | |||
| + | By default, a MySQL installation has an anonymous user, allowing anyone | ||
| + | to log into MySQL 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/n] y | ||
| + | ... Success! | ||
| + | |||
| + | 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/n] y | ||
| + | ... Success! | ||
| + | |||
| + | By default, MySQL comes with a database named ' | ||
| + | access. | ||
| + | before moving into a production environment. | ||
| + | |||
| + | Remove test database and access to it? [Y/n] y | ||
| + | - 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] y | ||
| + | ... Success! | ||
| + | |||
| + | Cleaning up... | ||
| + | |||
| + | |||
| + | |||
| + | All done! If you've completed all of the above steps, your MySQL | ||
| + | installation should now be secure. | ||
| + | |||
| + | Thanks for using MySQL! | ||
| + | |||
| + | |||
| + | </ | ||
| + | ==== logrotate ==== | ||
| + | Bei einem unter Last stehendem mySQl-Datenbankserver kann unter Umständen das zugehörige Logfile // | ||
| + | # vim / | ||
| + | <file | / | ||
| + | rotate 4 | ||
| + | weekly | ||
| + | compress | ||
| + | notifempty | ||
| + | size 5M | ||
| + | missingok | ||
| + | create 0640 mysql mysql | ||
| + | sharedscripts | ||
| + | postrotate | ||
| + | /bin/kill -HUP `cat / | ||
| + | endscript | ||
| + | } | ||
| + | </ | ||
| + | ==== iptables-Paketfilterregeln ==== | ||
| + | Nach dem Starten unseres **mysql** Daemon können wir mit Hilfe von netstat überprüfen, | ||
| + | # netstat -tulpen | grep mysqld | ||
| + | |||
| + | | ||
| + | |||
| + | Steht unser Datenbankserver hinter einer Firewall, so müssen wir unter Umständen eine geeignete Firewallregel in der zentralen Konfigurationsdatei von **iptables** nachtragen, damit der Zugriff auf den Port 3306 (TCP) auch erfolgen kann. | ||
| + | Wir tragen in der Konfigurationsdatei / | ||
| + | # vim / | ||
| + | |||
| + | < | ||
| + | # Django : 2011-10-25 mySQL-Zugriff freigeschaltet | ||
| + | -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT | ||
| + | </ | ||
| + | Anschließend aktivieren wir die Änderungen an unserem Paketfilter, | ||
| + | # service iptables restart | ||
| + | < | ||
| + | iptables: Setting chains to policy ACCEPT: filter nat [ OK ] | ||
| + | iptables: Unloading modules: | ||
| + | iptables: Applying firewall rules: | ||
| + | </ | ||
| + | ===== 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 2000-2008 MySQL AB, 2008 Sun Microsystems, | ||
| + | This software comes with ABSOLUTELY NO WARRANTY. This is free software, | ||
| + | and you are welcome to modify and redistribute it under the GPL license | ||
| + | |||
| + | Administration program for the mysqld daemon. | ||
| + | Usage: mysqladmin [OPTIONS] command command.... | ||
| + | -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. | ||
| + | -O, --set-variable=name | ||
| + | Change the value of a variable. Please note that this | ||
| + | option is deprecated; you can set variables directly with | ||
| + | --variable-name=value. | ||
| + | -s, --silent | ||
| + | -S, --socket=name | ||
| + | -i, --sleep=# | ||
| + | --ssl | ||
| + | other flags).Disable with --skip-ssl. | ||
| + | --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=# | ||
| + | |||
| + | 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 (No default value) | ||
| + | verbose | ||
| + | vertical | ||
| + | connect_timeout | ||
| + | shutdown_timeout | ||
| + | |||
| + | Default options are read from the following files in the given order: | ||
| + | / | ||
| + | The following groups are read: mysqladmin client | ||
| + | The following options may be given as the first argument: | ||
| + | --print-defaults | ||
| + | --no-defaults | ||
| + | --defaults-file=# | ||
| + | --defaults-extra-file=# | ||
| + | |||
| + | 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 | ||
| + | flush-hosts | ||
| + | flush-logs | ||
| + | flush-status Clear status variables | ||
| + | flush-tables | ||
| + | flush-threads | ||
| + | flush-privileges | ||
| + | kill id, | ||
| + | password new-password Change old password to new-password, | ||
| + | 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 | ||
| + | version Get version info from server | ||
| + | </ | ||
| + | ==== mysql ==== | ||
| + | Der Zugriff auf unseren mySQL-Datenbankserver 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 | ||
| + | < | ||
| + | Welcome to the MySQL monitor. | ||
| + | Your MySQL connection id is 18 | ||
| + | Server version: 5.1.52 Source distribution | ||
| + | |||
| + | Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. | ||
| + | This software comes with ABSOLUTELY NO WARRANTY. This is free software, | ||
| + | and you are welcome to modify and redistribute it under the GPL v2 license | ||
| + | |||
| + | Type ' | ||
| + | |||
| + | mysql> status; | ||
| + | -------------- | ||
| + | mysql Ver 14.14 Distrib 5.1.52, for unknown-linux-gnu (x86_64) using readline 5.1 | ||
| + | |||
| + | Connection id: 18 | ||
| + | Current database: | ||
| + | Current user: | ||
| + | SSL: Not in use | ||
| + | Current pager: | ||
| + | Using outfile: | ||
| + | Using delimiter: | ||
| + | Server version: | ||
| + | Protocol version: 10 | ||
| + | Connection: | ||
| + | Server characterset: | ||
| + | Db | ||
| + | Client characterset: | ||
| + | Conn. characterset: | ||
| + | UNIX socket: | ||
| + | Uptime: | ||
| + | |||
| + | Threads: 1 Questions: 30 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 9 Queries per second avg: 0.10 | ||
| + | -------------- | ||
| + | |||
| + | mysql> quit | ||
| + | Bye | ||
| + | </ | ||
| + | |||
| + | ==== Reset des root-Passworts ==== | ||
| + | Sollte man das Root-Passwort widererwarten vergessen, verschlampt oder sich einfach nicht mehr an die 42 Stellen des vergebenen Root-Passwortes erinnern, wird es in aller Regel recht hecktisch und die Transpiration geht auf volle Leistung. | ||
| + | |||
| + | Sofern man Zugriff auf als Root auf der Konsole hat, kann man sich behelfen. Folgende Schritte sind nun notwendig. | ||
| + | - **laufenden mySQL-Daemon stoppen**: < | ||
| + | - **mySQL im sicheren Modus starten**: < | ||
| + | - **Datenbank-Verbindung aufbauen**: < | ||
| + | Your MySQL connection id is 1 | ||
| + | Server version: 5.1.66 Source distribution | ||
| + | |||
| + | Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. | ||
| + | |||
| + | Oracle is a registered trademark of Oracle Corporation and/or its | ||
| + | affiliates. Other names may be trademarks of their respective | ||
| + | owners. | ||
| + | |||
| + | Type ' | ||
| + | |||
| + | mysql> </ | ||
| + | - **mySQL Datenbank auswählen**: | ||
| + | You can turn off this feature to get a quicker startup with -A | ||
| + | |||
| + | Database changed | ||
| + | mysql> </ | ||
| + | - **Passwort des users root neu setzen**: < | ||
| + | Rows matched: 2 Changed: 2 Warnings: 0 | ||
| + | |||
| + | mysql> </ | ||
| + | - **Verbindung zum mySQL-Daemon beenden**: < | ||
| + | - **mySQL stoppen**: < | ||
| + | - **mySQL-Daemon wieder " | ||
| + | |||
| + | ==== Inhalt (alle Tabellen) einer Datenbank löschen ==== | ||
| + | Beim Testen von Applikationen die auf eine mySQL-Datenbank zurückgreifen, | ||
| + | |||
| + | Hat man keinen Zugriff auf die Datenbank mit Hilfe einer Web-GUI wie **phpMyAdmin**, | ||
| + | |||
| + | Um jetzt nicht alle Tabellen einzeln mit dem **truncate**-Befehl zu löschen greifen wir auf einen einfachen aber effektiven Einzeiler zurück. | ||
| + | # mysql -D < | ||
| + | |||
| + | Voila, schon haben wir wieder eine jungfräuliche, | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | ==== Datenbank-Dump ==== | ||
| + | Zur Sicherung unserer MySQL-Datenbank-Tabellen legen wir uns ein kleines Script an, mit dessen Hilfe wir täglich eine Sicherung der kompletten Datenbank vornehmen können. | ||
| + | # vim / | ||
| + | <file bash vim / | ||
| + | |||
| + | ################################################################################## | ||
| + | # 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 MySQL-Datenbank-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 MySQL-Datenbank-Dumps ........................ |" | ||
| + | log " | ||
| + | log "" | ||
| + | |||
| + | # Status eMail versenden | ||
| + | if [ $MAIL_STATUS = ' | ||
| + | sendmail STATUS | ||
| + | fi | ||
| + | |||
| + | # Temporäres Logfile permanent sichern | ||
| + | movelog | ||
| + | |||
| + | exit 0 | ||
| + | |||
| + | </ | ||
| + | Zum Ausführen benötigt unser script dann **X**-Rechte, | ||
| + | # chmod +x / | ||
| + | |||
| + | 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 MySQL-Datenbankdump | ||
| + | 20 3 * * root / | ||
| + | |||
| + | </ | ||
| + | ==== phpMyAdmin ==== | ||
| + | Zur komfortablen Administration unseres [[centos: | ||
| + | |||
| + | |||
| + | ====== Links ====== | ||
| + | * **[[wiki: | ||
| + | * **[[http:// | ||
| + | |||
127.0.0.1