Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
centos:mysql [13.05.2013 10:25. ] – [mysql_fulldump] Script zum Dumpen der MySQL-Datenbank hinterlegt. 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:// | ||
+ | |||