Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung
Vorhergehende Überarbeitung
centos:mysql [13.05.2013 10:31. ]
django [Datenbank-Dump]
centos:mysql [20.04.2018 09:08. ] (aktuell)
Zeile 1: Zeile 1:
 +====== mySQL-Datenbankserver unter CentOS 6.x einrichten ======
 +{{:centos:mysql-logo.png?nolink& |mySQL Logo}}
 +\\
 +[[http://www.mysql.de|mySQL]] bezeichnet sich selbst als //**"Die populärste Open-Source-Datenbank der Welt"**// und steht unter CentOS 6.x als RPM-Paket zur Verfügung.
 +
 +Für viele unserer Anwendungen benötigen wir eine geeignete Datenbank, so z.B. für:
 +  * [[voip:start|Asterisk]]
 +  * [[centos:cacti_-_installation_und_konfiguration|cacti]]
 +  * [[fun:energie-funk-mess-system_em_1000|fhem]]
 +  * [[centos:mailserver:horde_-_create._communicate._collaborate|horde]]
 +  * [[wetter:boltek:software_stormforce|stormforce]]
 +  * [[wetter:wview:start|wview]]
 +  * [[centos:piwik|Piwik]]
 +===== 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
 +
 +<code>Name        : mysql-server                 Relocations: (not relocatable)
 +Version     : 5.1.52                            Vendor: CentOS
 +Release     : 1.el6_0.1                     Build Date: Sat 25 Jun 2011 07:53:02 AM CEST
 +Install Date: Tue 25 Oct 2011 09:32:39 PM CEST      Build Host: c6b6.bsys.dev.centos.org
 +Group       : Applications/Databases        Source RPM: mysql-5.1.52-1.el6_0.1.src.rpm
 +Size        : 24481725                         License: GPLv2 with exceptions
 +Signature   : RSA/8, Wed 06 Jul 2011 03:41:45 AM CEST, Key ID 0946fca2c105b9de
 +Packager    : CentOS BuildSystem <http://bugs.centos.org>
 +URL         : http://www.mysql.com
 +Summary     : The MySQL server and related files
 +Description :
 +MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
 +client/server implementation consisting of a server daemon (mysqld)
 +and many different client programs and libraries. This package contains
 +the MySQL server and some accompanying files and directories.
 +/etc/rc.d/init.d/mysqld
 +/usr/bin/innochecksum
 +/usr/bin/myisam_ftdump
 +/usr/bin/myisamchk
 +/usr/bin/myisamlog
 +/usr/bin/myisampack
 +/usr/bin/mysql_convert_table_format
 +/usr/bin/mysql_fix_extensions
 +/usr/bin/mysql_fix_privilege_tables
 +/usr/bin/mysql_install_db
 +/usr/bin/mysql_secure_installation
 +/usr/bin/mysql_setpermission
 +/usr/bin/mysql_tzinfo_to_sql
 +/usr/bin/mysql_upgrade
 +/usr/bin/mysql_zap
 +/usr/bin/mysqlbug
 +/usr/bin/mysqld_multi
 +/usr/bin/mysqld_safe
 +/usr/bin/mysqldumpslow
 +/usr/bin/mysqlhotcopy
 +/usr/bin/mysqltest
 +/usr/bin/perror
 +/usr/bin/replace
 +/usr/bin/resolve_stack_dump
 +/usr/bin/resolveip
 +/usr/lib64/mysql/plugin
 +/usr/lib64/mysql/plugin/ha_archive.so
 +/usr/lib64/mysql/plugin/ha_archive.so.0
 +/usr/lib64/mysql/plugin/ha_archive.so.0.0.0
 +/usr/lib64/mysql/plugin/ha_blackhole.so
 +/usr/lib64/mysql/plugin/ha_blackhole.so.0
 +/usr/lib64/mysql/plugin/ha_blackhole.so.0.0.0
 +/usr/lib64/mysql/plugin/ha_example.so
 +/usr/lib64/mysql/plugin/ha_example.so.0
 +/usr/lib64/mysql/plugin/ha_example.so.0.0.0
 +/usr/lib64/mysql/plugin/ha_federated.so
 +/usr/lib64/mysql/plugin/ha_federated.so.0
 +/usr/lib64/mysql/plugin/ha_federated.so.0.0.0
 +/usr/libexec/mysqld
 +/usr/libexec/mysqlmanager
 +/usr/share/doc/mysql-server-5.1.52
 +/usr/share/doc/mysql-server-5.1.52/my-huge.cnf
 +/usr/share/doc/mysql-server-5.1.52/my-innodb-heavy-4G.cnf
 +/usr/share/doc/mysql-server-5.1.52/my-large.cnf
 +/usr/share/doc/mysql-server-5.1.52/my-medium.cnf
 +/usr/share/doc/mysql-server-5.1.52/my-small.cnf
 +/usr/share/man/man1/innochecksum.1.gz
 +/usr/share/man/man1/msql2mysql.1.gz
 +/usr/share/man/man1/myisam_ftdump.1.gz
 +/usr/share/man/man1/myisamchk.1.gz
 +/usr/share/man/man1/myisamlog.1.gz
 +/usr/share/man/man1/myisampack.1.gz
 +/usr/share/man/man1/mysql.server.1.gz
 +/usr/share/man/man1/mysql_convert_table_format.1.gz
 +/usr/share/man/man1/mysql_fix_extensions.1.gz
 +/usr/share/man/man1/mysql_fix_privilege_tables.1.gz
 +/usr/share/man/man1/mysql_install_db.1.gz
 +/usr/share/man/man1/mysql_secure_installation.1.gz
 +/usr/share/man/man1/mysql_setpermission.1.gz
 +/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
 +/usr/share/man/man1/mysql_upgrade.1.gz
 +/usr/share/man/man1/mysql_zap.1.gz
 +/usr/share/man/man1/mysqlbinlog.1.gz
 +/usr/share/man/man1/mysqlbug.1.gz
 +/usr/share/man/man1/mysqlcheck.1.gz
 +/usr/share/man/man1/mysqld_multi.1.gz
 +/usr/share/man/man1/mysqld_safe.1.gz
 +/usr/share/man/man1/mysqldumpslow.1.gz
 +/usr/share/man/man1/mysqlhotcopy.1.gz
 +/usr/share/man/man1/mysqlimport.1.gz
 +/usr/share/man/man1/mysqlman.1.gz
 +/usr/share/man/man1/mysqltest.1.gz
 +/usr/share/man/man1/perror.1.gz
 +/usr/share/man/man1/replace.1.gz
 +/usr/share/man/man1/resolve_stack_dump.1.gz
 +/usr/share/man/man1/resolveip.1.gz
 +/usr/share/man/man8/mysqld.8.gz
 +/usr/share/man/man8/mysqlmanager.8.gz
 +/usr/share/mysql/config.huge.ini
 +/usr/share/mysql/config.medium.ini
 +/usr/share/mysql/config.small.ini
 +/usr/share/mysql/errmsg.txt
 +/usr/share/mysql/fill_help_tables.sql
 +/usr/share/mysql/my-huge.cnf
 +/usr/share/mysql/my-innodb-heavy-4G.cnf
 +/usr/share/mysql/my-large.cnf
 +/usr/share/mysql/my-medium.cnf
 +/usr/share/mysql/my-small.cnf
 +/usr/share/mysql/mysql_fix_privilege_tables.sql
 +/usr/share/mysql/mysql_system_tables.sql
 +/usr/share/mysql/mysql_system_tables_data.sql
 +/usr/share/mysql/mysql_test_data_timezone.sql
 +/var/lib/mysql
 +/var/log/mysqld.log
 +/var/run/mysqld
 +</code>
 +==== mysql ====
 +   # rpm -qil mysql
 +
 +<code>Name        : mysql                        Relocations: (not relocatable)
 +Version     : 5.1.52                            Vendor: CentOS
 +Release     : 1.el6_0.1                     Build Date: Sat 25 Jun 2011 07:53:02 AM CEST
 +Install Date: Tue 25 Oct 2011 09:32:35 PM CEST      Build Host: c6b6.bsys.dev.centos.org
 +Group       : Applications/Databases        Source RPM: mysql-5.1.52-1.el6_0.1.src.rpm
 +Size        : 2529479                          License: GPLv2 with exceptions
 +Signature   : RSA/8, Wed 06 Jul 2011 03:41:40 AM CEST, Key ID 0946fca2c105b9de
 +Packager    : CentOS BuildSystem <http://bugs.centos.org>
 +URL         : http://www.mysql.com
 +Summary     : MySQL client programs and shared libraries
 +Description :
 +MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
 +client/server implementation consisting of a server daemon (mysqld)
 +and many different client programs and libraries. The base package
 +contains the standard MySQL client programs and generic MySQL files.
 +/usr/bin/msql2mysql
 +/usr/bin/my_print_defaults
 +/usr/bin/mysql
 +/usr/bin/mysql_config
 +/usr/bin/mysql_find_rows
 +/usr/bin/mysql_waitpid
 +/usr/bin/mysqlaccess
 +/usr/bin/mysqladmin
 +/usr/bin/mysqlbinlog
 +/usr/bin/mysqlcheck
 +/usr/bin/mysqldump
 +/usr/bin/mysqlimport
 +/usr/bin/mysqlshow
 +/usr/bin/mysqlslap
 +/usr/lib64/mysql/mysql_config
 +/usr/lib64/mysql/mysqlbug
 +/usr/share/doc/mysql-5.1.52
 +/usr/share/doc/mysql-5.1.52/COPYING
 +/usr/share/doc/mysql-5.1.52/EXCEPTIONS-CLIENT
 +/usr/share/doc/mysql-5.1.52/README
 +/usr/share/doc/mysql-5.1.52/README.mysql-docs
 +/usr/share/man/man1/my_print_defaults.1.gz
 +/usr/share/man/man1/mysql.1.gz
 +/usr/share/man/man1/mysql_config.1.gz
 +/usr/share/man/man1/mysql_find_rows.1.gz
 +/usr/share/man/man1/mysql_waitpid.1.gz
 +/usr/share/man/man1/mysqlaccess.1.gz
 +/usr/share/man/man1/mysqladmin.1.gz
 +/usr/share/man/man1/mysqldump.1.gz
 +/usr/share/man/man1/mysqlshow.1.gz
 +/usr/share/man/man1/mysqlslap.1.gz
 +</code>
 +==== perl-DBD-MySQL ====
 +   # rpm -qil perl-DBD-MySQL
 +
 +<code>Name        : perl-DBD-MySQL               Relocations: (not relocatable)
 +Version     : 4.013                             Vendor: CentOS
 +Release     : 3.el6                         Build Date: Fri 20 Aug 2010 02:31:30 AM CEST
 +Install Date: Tue 25 Oct 2011 09:32:33 PM CEST      Build Host: c6b3.bsys.dev.centos.org
 +Group       : Development/Libraries         Source RPM: perl-DBD-MySQL-4.013-3.el6.src.rpm
 +Size        : 345831                           License: GPL+ or Artistic
 +Signature   : RSA/8, Sun 03 Jul 2011 06:55:00 AM CEST, Key ID 0946fca2c105b9de
 +Packager    : CentOS BuildSystem <http://bugs.centos.org>
 +URL         : http://search.cpan.org/dist/DBD-mysql/
 +Summary     : A MySQL interface for perl
 +Description :
 +An implementation of DBI for MySQL for Perl.
 +/usr/lib64/perl5/Bundle
 +/usr/lib64/perl5/Bundle/DBD
 +/usr/lib64/perl5/Bundle/DBD/mysql.pm
 +/usr/lib64/perl5/DBD
 +/usr/lib64/perl5/DBD/mysql
 +/usr/lib64/perl5/DBD/mysql.pm
 +/usr/lib64/perl5/DBD/mysql/GetInfo.pm
 +/usr/lib64/perl5/DBD/mysql/INSTALL.pod
 +/usr/lib64/perl5/auto/DBD
 +/usr/lib64/perl5/auto/DBD/mysql
 +/usr/lib64/perl5/auto/DBD/mysql/mysql.so
 +/usr/share/doc/perl-DBD-MySQL-4.013
 +/usr/share/doc/perl-DBD-MySQL-4.013/ChangeLog
 +/usr/share/doc/perl-DBD-MySQL-4.013/INSTALL.html
 +/usr/share/doc/perl-DBD-MySQL-4.013/README
 +/usr/share/doc/perl-DBD-MySQL-4.013/TODO
 +/usr/share/man/man3/Bundle::DBD::mysql.3pm.gz
 +/usr/share/man/man3/DBD::mysql.3pm.gz
 +/usr/share/man/man3/DBD::mysql::INSTALL.3pm.gz
 +</code>
 +==== perl-DBI ====
 +   # rpm -qil perl-DBI
 +
 +<code>Name        : perl-DBI                     Relocations: (not relocatable)
 +Version     : 1.609                             Vendor: CentOS
 +Release     : 4.el6                         Build Date: Fri 20 Aug 2010 02:34:23 AM CEST
 +Install Date: Tue 25 Oct 2011 09:32:30 PM CEST      Build Host: c6b1.bsys.dev.centos.org
 +Group       : Development/Libraries         Source RPM: perl-DBI-1.609-4.el6.src.rpm
 +Size        : 1733598                          License: GPL+ or Artistic
 +Signature   : RSA/8, Sun 03 Jul 2011 06:55:01 AM CEST, Key ID 0946fca2c105b9de
 +Packager    : CentOS BuildSystem <http://bugs.centos.org>
 +URL         : http://dbi.perl.org/
 +Summary     : A database access API for perl
 +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.
 +/usr/bin/dbilogstrip
 +/usr/bin/dbiprof
 +/usr/bin/dbiproxy
 +/usr/lib64/perl5/Bundle
 +/usr/lib64/perl5/Bundle/DBI.pm
 +/usr/lib64/perl5/DBD
 +/usr/lib64/perl5/DBD/DBM.pm
 +/usr/lib64/perl5/DBD/ExampleP.pm
 +/usr/lib64/perl5/DBD/File.pm
 +/usr/lib64/perl5/DBD/Gofer
 +/usr/lib64/perl5/DBD/Gofer.pm
 +/usr/lib64/perl5/DBD/Gofer/Policy
 +/usr/lib64/perl5/DBD/Gofer/Policy/Base.pm
 +/usr/lib64/perl5/DBD/Gofer/Policy/classic.pm
 +/usr/lib64/perl5/DBD/Gofer/Policy/pedantic.pm
 +/usr/lib64/perl5/DBD/Gofer/Policy/rush.pm
 +/usr/lib64/perl5/DBD/Gofer/Transport
 +/usr/lib64/perl5/DBD/Gofer/Transport/Base.pm
 +/usr/lib64/perl5/DBD/Gofer/Transport/null.pm
 +/usr/lib64/perl5/DBD/Gofer/Transport/pipeone.pm
 +/usr/lib64/perl5/DBD/Gofer/Transport/stream.pm
 +/usr/lib64/perl5/DBD/NullP.pm
 +/usr/lib64/perl5/DBD/Proxy.pm
 +/usr/lib64/perl5/DBD/Sponge.pm
 +/usr/lib64/perl5/DBI
 +/usr/lib64/perl5/DBI.pm
 +/usr/lib64/perl5/DBI/Changes.pm
 +/usr/lib64/perl5/DBI/Const
 +/usr/lib64/perl5/DBI/Const/GetInfo
 +/usr/lib64/perl5/DBI/Const/GetInfo/ANSI.pm
 +/usr/lib64/perl5/DBI/Const/GetInfo/ODBC.pm
 +/usr/lib64/perl5/DBI/Const/GetInfoReturn.pm
 +/usr/lib64/perl5/DBI/Const/GetInfoType.pm
 +/usr/lib64/perl5/DBI/DBD
 +/usr/lib64/perl5/DBI/DBD.pm
 +/usr/lib64/perl5/DBI/DBD/Metadata.pm
 +/usr/lib64/perl5/DBI/FAQ.pm
 +/usr/lib64/perl5/DBI/Gofer
 +/usr/lib64/perl5/DBI/Gofer/Execute.pm
 +/usr/lib64/perl5/DBI/Gofer/Request.pm
 +/usr/lib64/perl5/DBI/Gofer/Response.pm
 +/usr/lib64/perl5/DBI/Gofer/Serializer
 +/usr/lib64/perl5/DBI/Gofer/Serializer/Base.pm
 +/usr/lib64/perl5/DBI/Gofer/Serializer/DataDumper.pm
 +/usr/lib64/perl5/DBI/Gofer/Serializer/Storable.pm
 +/usr/lib64/perl5/DBI/Gofer/Transport
 +/usr/lib64/perl5/DBI/Gofer/Transport/Base.pm
 +/usr/lib64/perl5/DBI/Gofer/Transport/pipeone.pm
 +/usr/lib64/perl5/DBI/Gofer/Transport/stream.pm
 +/usr/lib64/perl5/DBI/Profile.pm
 +/usr/lib64/perl5/DBI/ProfileData.pm
 +/usr/lib64/perl5/DBI/ProfileDumper
 +/usr/lib64/perl5/DBI/ProfileDumper.pm
 +/usr/lib64/perl5/DBI/ProfileDumper/Apache.pm
 +/usr/lib64/perl5/DBI/ProfileSubs.pm
 +/usr/lib64/perl5/DBI/ProxyServer.pm
 +/usr/lib64/perl5/DBI/PurePerl.pm
 +/usr/lib64/perl5/DBI/Roadmap.pm
 +/usr/lib64/perl5/DBI/SQL
 +/usr/lib64/perl5/DBI/SQL/Nano.pm
 +/usr/lib64/perl5/DBI/Util
 +/usr/lib64/perl5/DBI/Util/CacheMemory.pm
 +/usr/lib64/perl5/DBI/Util/_accessor.pm
 +/usr/lib64/perl5/Roadmap.pod
 +/usr/lib64/perl5/TASKS.pod
 +/usr/lib64/perl5/auto/DBI
 +/usr/lib64/perl5/auto/DBI/DBI.so
 +/usr/lib64/perl5/auto/DBI/DBIXS.h
 +/usr/lib64/perl5/auto/DBI/Driver.xst
 +/usr/lib64/perl5/auto/DBI/Driver_xst.h
 +/usr/lib64/perl5/auto/DBI/dbd_xsh.h
 +/usr/lib64/perl5/auto/DBI/dbi_sql.h
 +/usr/lib64/perl5/auto/DBI/dbipport.h
 +/usr/lib64/perl5/auto/DBI/dbivport.h
 +/usr/lib64/perl5/auto/DBI/dbixs_rev.h
 +/usr/lib64/perl5/dbixs_rev.pl
 +/usr/share/doc/perl-DBI-1.609
 +/usr/share/doc/perl-DBI-1.609/README
 +/usr/share/doc/perl-DBI-1.609/ex
 +/usr/share/doc/perl-DBI-1.609/ex/perl_dbi_nulls_test.pl
 +/usr/share/doc/perl-DBI-1.609/ex/profile.pl
 +/usr/share/man/man1/dbilogstrip.1.gz
 +/usr/share/man/man1/dbiprof.1.gz
 +/usr/share/man/man1/dbiproxy.1.gz
 +/usr/share/man/man3/Bundle::DBI.3pm.gz
 +/usr/share/man/man3/DBD::DBM.3pm.gz
 +/usr/share/man/man3/DBD::File.3pm.gz
 +/usr/share/man/man3/DBD::Gofer.3pm.gz
 +/usr/share/man/man3/DBD::Gofer::Policy::Base.3pm.gz
 +/usr/share/man/man3/DBD::Gofer::Policy::classic.3pm.gz
 +/usr/share/man/man3/DBD::Gofer::Policy::pedantic.3pm.gz
 +/usr/share/man/man3/DBD::Gofer::Policy::rush.3pm.gz
 +/usr/share/man/man3/DBD::Gofer::Transport::Base.3pm.gz
 +/usr/share/man/man3/DBD::Gofer::Transport::null.3pm.gz
 +/usr/share/man/man3/DBD::Gofer::Transport::pipeone.3pm.gz
 +/usr/share/man/man3/DBD::Gofer::Transport::stream.3pm.gz
 +/usr/share/man/man3/DBD::Proxy.3pm.gz
 +/usr/share/man/man3/DBD::Sponge.3pm.gz
 +/usr/share/man/man3/DBI.3pm.gz
 +/usr/share/man/man3/DBI::Const::GetInfo::ANSI.3pm.gz
 +/usr/share/man/man3/DBI::Const::GetInfo::ODBC.3pm.gz
 +/usr/share/man/man3/DBI::Const::GetInfoReturn.3pm.gz
 +/usr/share/man/man3/DBI::Const::GetInfoType.3pm.gz
 +/usr/share/man/man3/DBI::DBD.3pm.gz
 +/usr/share/man/man3/DBI::DBD::Metadata.3pm.gz
 +/usr/share/man/man3/DBI::FAQ.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Execute.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Request.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Response.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Serializer::Base.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Serializer::DataDumper.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Serializer::Storable.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Transport::Base.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Transport::pipeone.3pm.gz
 +/usr/share/man/man3/DBI::Gofer::Transport::stream.3pm.gz
 +/usr/share/man/man3/DBI::Profile.3pm.gz
 +/usr/share/man/man3/DBI::ProfileData.3pm.gz
 +/usr/share/man/man3/DBI::ProfileDumper.3pm.gz
 +/usr/share/man/man3/DBI::ProfileDumper::Apache.3pm.gz
 +/usr/share/man/man3/DBI::ProfileSubs.3pm.gz
 +/usr/share/man/man3/DBI::ProxyServer.3pm.gz
 +/usr/share/man/man3/DBI::PurePerl.3pm.gz
 +/usr/share/man/man3/DBI::SQL::Nano.3pm.gz
 +/usr/share/man/man3/DBI::Util::CacheMemory.3pm.gz
 +/usr/share/man/man3/Roadmap.3pm.gz
 +/usr/share/man/man3/TASKS.3pm.gz
 +</code>
 +===== Konfiguration =====
 +==== my.cnf ====
 +Die Konfiguration unseres Datenbankservers erfolgt über die Konfigurationsdatei //**/etc/my.cnf**//, die uns bei der Installation bereits mitgeliefert wurde. 
 +
 +<file | /etc/my.cnf>
 +[mysqld]
 +datadir=/var/lib/mysql
 +socket=/var/lib/mysql/mysql.sock
 +user=mysql
 +# Disabling symbolic-links is recommended to prevent assorted security risks
 +symbolic-links=0
 +
 +[mysqld_safe]
 +log-error=/var/log/mysqld.log
 +pid-file=/var/run/mysqld/mysqld.pid
 +</file>
 +Weitere Beispiele finden sich im Verzeichnis //**/usr/share/doc/mysql-server-5.1.52/**//.
 +   # ll /usr/share/doc/mysql-server-5.1.52/
 +<code>total 48
 +-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
 +</code>
 +Bei Bedarf, wie z.B. der Angabe eines spezifischen Datenbankverzeichnisses, tragen wir unsere individuelle Konfiguration in der **my.cnf** nach.
 +<code>[mysqld]
 +# Django : 2011-10-25 spezifisches Datenbankverzeichnis angegeben
 +# default: datadir=/var/lib/mysql
 +datadir=/var/lib/mysql/data
 +...
 +</code>
 +==== erster Start ====
 +Nun ist es an der Zeit unseren Datenbank-Server das erste mal zu starten; hierzu benutzen wir einfach das mitgelieferte Startscript **mysqld** im Verzeichnis //**/etc/init.d**//.
 +   # ll /etc/init.d/mysqld 
 +
 +<code>-rwxr-xr-x 1 root root 5509 Jun 25 07:51 /etc/init.d/mysqld</code>
 +   # service mysqld start
 +<code>Initializing MySQL database:  Installing MySQL system tables...
 +OK
 +Filling help tables...
 +OK
 +
 +To start mysqld at boot time you have to copy
 +support-files/mysql.server to the right place for your system
 +
 +PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
 +To do so, start the server, then issue the following commands:
 +
 +/usr/bin/mysqladmin -u root password 'new-password'
 +/usr/bin/mysqladmin -u root -h mysqlhost.nausch.org password 'new-password'
 +
 +Alternatively you can run:
 +/usr/bin/mysql_secure_installation
 +
 +which will also give you the option of removing the test
 +databases and anonymous user created by default.  This is
 +strongly recommended for production servers.
 +
 +See the manual for more instructions.
 +
 +You can start the MySQL daemon with:
 +cd /usr ; /usr/bin/mysqld_safe &
 +
 +You can test the MySQL daemon with mysql-test-run.pl
 +cd /usr/mysql-test ; perl mysql-test-run.pl
 +
 +Please report any problems with the /usr/bin/mysqlbug script!
 +
 +                                                            OK  ]
 +Starting mysqld:                                            OK  ]
 +</code>
 +Der Start unseres Servers wir uns in der Logdatei des mySQl-Daemons entsprechend vermerkt:
 +   # less /var/log/mysqld.log
 +<code>111025 22:25:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/data
 +InnoDB: The first specified data file ./ibdata1 did not exist:
 +InnoDB: a new database to be created!
 +111025 22:25:20  InnoDB: Setting file ./ibdata1 size to 10 MB
 +InnoDB: Database physically writes the file full: wait...
 +111025 22:25:21  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
 +InnoDB: Setting log file ./ib_logfile0 size to 5 MB
 +InnoDB: Database physically writes the file full: wait...
 +111025 22:25:21  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
 +InnoDB: Setting log file ./ib_logfile1 size to 5 MB
 +InnoDB: Database physically writes the file full: wait...
 +InnoDB: Doublewrite buffer not found: creating new
 +InnoDB: Doublewrite buffer created
 +InnoDB: Creating foreign key constraint system tables
 +InnoDB: Foreign key constraint system tables created
 +111025 22:25:22  InnoDB: Started; log sequence number 0 0
 +111025 22:25:22 [Note] Event Scheduler: Loaded 0 events
 +111025 22:25:22 [Note] /usr/libexec/mysqld: ready for connections.
 +Version: '5.1.52'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
 +</code>
 +Gemäß unserer Konfiguration wurden im unseren data-Verzeichnis die ersten Datenbankfile angelegt.
 +   # ll /var/lib/mysql/data/
 +<code>total 20484
 +-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
 +</code>
 +
 +==== 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
 +
 +   mysqld          0:off 1:off 2:on 3:on 4:on 5:on 6:off
 +
 +==== Installation absichern ====
 +Wie bei der doch großen Ausgabe beim erstmaligen Start des Datenbank-Daemons angeraten, werden wir nun die __sicherheitsrelevanten Konfigurationsänderungen__ vornehmen.
 +
 +Hierzu benutzen wir einfach das mitgelieferte Script //**/usr/bin/mysql_secure_installation**//, welches folgende Änderungen vornimmt:
 +
 +  - Datenbankpasswort des MySQL-Datenbankuser **root** setzen
 +  - Anonyme Benutzerkonten löschen
 +  - Deaktivieren der Remote-Zugriffsmöglichkeit für den MySQL-Datenbankuser **root**
 +  - Löschen der nicht benötigten Testdatenbank **test**
 +
 +<code>
 +
 +
 +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't set the root password yet, the password will be blank,
 +so you should just press enter here.
 +
 +Enter current password for root (enter for none): 
 +OK, successfully used password, moving on...
 +
 +Setting the root password ensures that nobody can log into the 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.  You should remove them before moving into a
 +production environment.
 +
 +Remove anonymous users? [Y/n] y
 + ... Success!
 +
 +Normally, root should only be allowed to connect from 'localhost' This
 +ensures that someone cannot guess at the root password from the network.
 +
 +Disallow root login remotely? [Y/n] y
 + ... Success!
 +
 +By default, MySQL comes with a database named 'test' that anyone can
 +access.  This is also intended only for testing, and should be removed
 +before moving into a production environment.
 +
 +Remove test database and access to it? [Y/n] 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!
 +
 +
 +</code>
 +==== logrotate ====
 +Bei einem unter Last stehendem mySQl-Datenbankserver kann unter Umständen das zugehörige Logfile //**/var/log/mysqld.log**// recht schnell anwachsen. Leider wir in dem RPM-Paket **mysql-server** keine Konfigurationsdatei zur Rotation der Logdatei mitgeliefert, so dass wir uns diese kurz selbst erstellen. Hierzu benutzen wir den Editor unserer Wahl, z.B. **vim**.
 +   # vim /etc/logrotate.d/mysql
 +<file | /etc/logrotate.d/mysql>/var/log/mysqld.log {
 + rotate 4
 + weekly
 + compress
 + notifempty
 + size 5M
 + missingok
 + create 0640 mysql mysql
 + sharedscripts
 + postrotate
 + /bin/kill -HUP `cat /var/run/mysqld/mysqld.pid 2> /dev/null` 2> /dev/null || true
 + endscript
 +}
 +</file>
 +==== iptables-Paketfilterregeln ====
 +Nach dem Starten unseres **mysql** Daemon können wir mit Hilfe von netstat überprüfen, ob der Daemon auf den gewünschten Ports lauscht. 
 +   # netstat -tulpen | grep mysqld
 +
 +   tcp        0      0 0.0.0.0:3306                0.0.0.0:                  LISTEN      27         12443      2489/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 /etc/sysconfig/iptables hierzu die folgenden Zeilen am Ende der INPUT-Regeln nach. 
 +   # vim /etc/sysconfig/iptables
 +
 +<code> ...
 +# Django : 2011-10-25 mySQL-Zugriff freigeschaltet
 +-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
 +</code>
 +Anschließend aktivieren wir die Änderungen an unserem Paketfilter, indem wir den Daemon durchstarten. 
 +   # service iptables restart
 +<code>iptables: Flushing firewall rules:                          OK  ]
 +iptables: Setting chains to policy ACCEPT: filter nat      [  OK  ]
 +iptables: Unloading modules:                                OK  ]
 +iptables: Applying firewall rules:                          OK  ]
 +</code>
 +===== Datenbankhandling =====
 +==== mysqladmin ====
 +Mit Hilfe des Hilfsprogrammes **mysqladmin** aus dem Clientpaket **mysql** können umfangreiche Abfrage gegen unsere Datenbank gefahren werden. Startet man das Programm ohne weitere Angaben von Optionen, werden die möglichen Optionen am Bildschirm ausgegeben.
 +   # mysqladmin
 +<code>mysqladmin  Ver 8.42 Distrib 5.1.52, for unknown-linux-gnu on x86_64
 +Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
 +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=#       Number of iterations to make. This works with -i
 +                      (--sleep) only.
 +  --debug-check       Check memory and open file usage at exit.
 +  --debug-info        Print some debug info at exit.
 +  -f, --force         Don't ask for confirmation on drop database; with
 +                      multiple commands, continue even if an error occurs.
 +  -C, --compress      Use compression in server/client protocol.
 +  --character-sets-dir=name 
 +                      Directory for character set files.
 +  --default-character-set=name 
 +                      Set the default character set.
 +  -?, --help          Display this help and exit.
 +  -h, --host=name     Connect to host.
 +  -b, --no-beep       Turn off beep on error.
 +  -p, --password[=name] 
 +                      Password to use when connecting to server. If password is
 +                      not given it's asked from the tty.
 +  -P, --port=#        Port number to use for connection or 0 for default to, in
 +                      order of preference, my.cnf, $MYSQL_TCP_PORT,
 +                      /etc/services, built-in default (3306).
 +  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
 +                      memory).
 +  -r, --relative      Show difference between current and previous values when
 +                      used with -i. Currently only works with extended-status.
 +  -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        Silently exit if one can't connect to server.
 +  -S, --socket=name   The socket file to use for connection.
 +  -i, --sleep=#       Execute commands repeatedly with a sleep between.
 +  --ssl               Enable SSL for connection (automatically enabled with
 +                      other flags).Disable with --skip-ssl.
 +  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
 +                      --ssl).
 +  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
 +  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
 +  --ssl-cipher=name   SSL cipher to use (implies --ssl).
 +  --ssl-key=name      X509 key in PEM format (implies --ssl).
 +  --ssl-verify-server-cert 
 +                      Verify server's "Common Name" in its cert against
 +                      hostname used when connecting. This option is disabled by
 +                      default.
 +  -u, --user=name     User for login if not current user.
 +  -v, --verbose       Write more information.
 +  -V, --version       Output version information and exit.
 +  -E, --vertical      Print output vertically. Is similar to --relative, but
 +                      prints output vertically.
 +  -w, --wait[=#     Wait and retry if connection is down.
 +  --connect_timeout=# 
 +  --shutdown_timeout=# 
 +
 +Variables (--variable-name=value)
 +and boolean options {FALSE|TRUE}  Value (after reading options)
 +--------------------------------- -----------------------------
 +count                             0
 +debug-check                       FALSE
 +debug-info                        FALSE
 +force                             FALSE
 +compress                          FALSE
 +character-sets-dir                (No default value)
 +default-character-set             (No default value)
 +host                              (No default value)
 +no-beep                           FALSE
 +port                              0
 +relative                          FALSE
 +socket                            (No default value)
 +sleep                             0
 +ssl                               FALSE
 +ssl-ca                            (No default value)
 +ssl-capath                        (No default value)
 +ssl-cert                          (No default value)
 +ssl-cipher                        (No default value)
 +ssl-key                           (No default value)
 +ssl-verify-server-cert            FALSE
 +user                              (No default value)
 +verbose                           FALSE
 +vertical                          FALSE
 +connect_timeout                   43200
 +shutdown_timeout                  3600
 +
 +Default options are read from the following files in the given order:
 +/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
 +The following groups are read: mysqladmin client
 +The following options may be given as the first argument:
 +--print-defaults        Print the program argument list and exit.
 +--no-defaults           Don't read default options from any option file.
 +--defaults-file=#       Only read default options from the given file #.
 +--defaults-extra-file=# Read this file after the global files are read.
 +
 +Where command is a one or more of: (Commands may be shortened)
 +  create databasename Create a new database
 +  debug Instruct server to write debug information to log
 +  drop databasename Delete a database and all its tables
 +  extended-status       Gives an extended status message from the server
 +  flush-hosts           Flush all cached hosts
 +  flush-logs            Flush all logs
 +  flush-status Clear status variables
 +  flush-tables          Flush all tables
 +  flush-threads         Flush the thread cache
 +  flush-privileges      Reload grant tables (same as reload)
 +  kill id,id,... Kill mysql threads
 +  password new-password Change old password to new-password, MySQL 4.1 hashing.
 +  old-password new-password Change old password to new-password in old format.
 +
 +  ping Check if mysqld is alive
 +  processlist Show list of active threads in server
 +  reload Reload grant tables
 +  refresh Flush all tables and close and open logfiles
 +  shutdown Take server down
 +  status Gives a short status message from the server
 +  start-slave Start slave
 +  stop-slave Stop slave
 +  variables             Prints variables available
 +  version Get version info from server
 +</code>
 +==== 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
 +<code>Enter password: ***Klausi-is-a-Geek!***
 +Welcome to the MySQL monitor.  Commands end with ; or \g.
 +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 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 +
 +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: root@localhost
 +SSL: Not in use
 +Current pager: stdout
 +Using outfile: ''
 +Using delimiter: ;
 +Server version: 5.1.52 Source distribution
 +Protocol version: 10
 +Connection: Localhost via UNIX socket
 +Server characterset: latin1
 +Db     characterset: latin1
 +Client characterset: latin1
 +Conn.  characterset: latin1
 +UNIX socket: /var/lib/mysql/mysql.sock
 +Uptime: 48 min 56 sec
 +
 +Threads: 1  Questions: 30  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 9  Queries per second avg: 0.10
 +--------------
 +
 +mysql> quit
 +Bye
 +</code>
 +
 +==== 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**: <code> # service mysqld stop</code>
 +  - **mySQL im sicheren Modus starten**: <code> # mysqld_safe --skip-grant-tables --user=root & </code>
 +  - **Datenbank-Verbindung aufbauen**: <code> # mysql -u root</code><code>Welcome to the MySQL monitor.  Commands end with ; or \g.
 +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 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 +
 +mysql> </code>
 +  - **mySQL Datenbank auswählen**: <code>mysql> use mysql;</code><code>Reading table information for completion of table and column names
 +You can turn off this feature to get a quicker startup with -A
 +
 +Database changed
 +mysql> </code>
 +  - **Passwort des users root neu setzen**: <code>mysql> update user set password=password ("d3r_desß3n_N@mEn_n1chT_GeN@nNT_w3rd3n_dArf!") where user="root";</code><code>Query OK, 2 rows affected (0.00 sec)
 +Rows matched: 2  Changed: 2  Warnings: 0
 +
 +mysql> </code> 
 +  - **Verbindung zum mySQL-Daemon beenden**: <code>mysql> quit</code>
 +  - **mySQL stoppen**: <code> # ps aux | grep mysql</code><code>root      5703  0.0  0.0 106060  1472 pts/0    S    09:18   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables --user=root</code><code> # kill 5703</code>
 +  - **mySQL-Daemon wieder "normal" starten**: <code> # service mysqld start</code>
 +
 +==== Inhalt (alle Tabellen) einer Datenbank löschen ====
 +Beim Testen von Applikationen die auf eine mySQL-Datenbank zurückgreifen, ist es an und ab notwendig, die zugehörige Datenbank "auf Null zurückzusetzen", also alle Tabellen zu löschen, aber die Datenbank an sich nicht zu löschen.
 +
 +Hat man keinen Zugriff auf die Datenbank mit Hilfe einer Web-GUI wie **phpMyAdmin**, so kann man das gewünschte Ergebnis auch schnell und einfach von der Konsole aus mit dem Befehl **mysql** bewerkstelligen.
 +
 +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 <datenbankname> -h <datenbankhost> -u <datenbankuser> --password=<passwort> -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "drop table " $1 ";"}' | mysql -D <datenbankname> -h <datenbankhost> -u <datenbankuser> --password=<passwort>
 +
 +Voila, schon haben wir wieder eine jungfräuliche, also mit keinen Datenbanktabellen, Datenbank und wir können beim Applikationstest wieder von vorne beginnen.
 +
 +
 +
 +
 +==== 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 /root/bin/mysql_fulldump
 +<file bash vim /root/bin/mysql_fulldump>#!/bin/bash
 +
 +##################################################################################
 +# Script-Name : mysqldump.sh                                                     # 
 +# Description : Datenbank-Dump der kompletten (alle Tabellen) unserer            #
 +#               MySQL-Datenbank nach /root/mysql/dumps                           #
 +#               Drei Datensicherungen werden aufgehoben, ältere werden gelöscht. # 
 +#                                                                                # 
 +#                                                                                # 
 +#                                                                                # 
 +# Last update : 13.05.2013                                                       # 
 +# Version     : 0.01                                                             # 
 +##################################################################################
 +
 +##################################################################################
 +#                                  H I S T O R Y                                 # 
 +##################################################################################
 +# Version     : 0.01                                                             # 
 +# Description : initial release                                                  #
 +# ------------------------------------------------------------------------------ # 
 +# Version     : x.xx                                                             # 
 +# Description : <Description>                                                    #
 +##################################################################################
 +
 +# Source function library.
 +. /etc/init.d/functions
 +
 +# Definition der systemindividuellen Variablen
 +
 +# Script-Name.
 +SCRIPT_NAME='mysqldump'
 +
 +# Backup-Verzeichnis.
 +DIR_TARGET='/root/mysql/dump'
 +DUMP_FILES="$DIR_TARGET/*.sql"
 +
 +# Mail-Empfänger
 +MAIL_RECIPIENT='django@it-ignorant.de'
 +
 +# Status-Mail versenden? [J|N].
 +MAIL_STATUS='N'
 +
 +# Datenbankdefinitionen
 +DB_HOST="127.0.0.1"
 +DB_USER="root"
 +DB_SECRET="immNI+32$cHU551n5Kn13gn1uS4W6HYu0SAJwH8W"
 +
 +# Variablen
 +MYSQLDUMP_COMMAND=`command -v mysqldump`
 +TOUCH_COMMAND=`command -v touch`
 +RM_COMMAND=`command -v rm`
 +PROG_SENDMAIL=`command -v sendmail`
 +CAT_COMMAND=`command -v cat`
 +DATE_COMMAND=`command -v date`
 +MKDIR_COMMAND=`command -v mkdir`
 +FILE_NAME='/'$SCRIPT_NAME'.'`$DATE_COMMAND '+%Y-%m-%d-%H%M%S'`'.sql'
 +FILE_LOCK='/tmp/'$SCRIPT_NAME'.lock'
 +FILE_LOG='/var/log/'$SCRIPT_NAME'.log'
 +FILE_LAST_LOG='/tmp/'$SCRIPT_NAME'.log'
 +FILE_MAIL='/tmp/'$SCRIPT_NAME'.mail'
 +VAR_HOSTNAME=`uname -n`
 +VAR_SENDER='root@'$VAR_HOSTNAME
 +VAR_EMAILDATE=`$DATE_COMMAND '+%a, %d %b %Y %H:%M:%S (%Z)'`
 +
 +# Functionen
 +function log() {
 +        echo $1
 +        echo `$DATE_COMMAND '+%Y/%m/%d %H:%M:%S'` " INFO:" $1 >>${FILE_LAST_LOG}
 +}
 +
 +function movelog() {
 +        $CAT_COMMAND $FILE_LAST_LOG >> $FILE_LOG
 +        $RM_COMMAND -f $FILE_LAST_LOG
 +        $RM_COMMAND -f $FILE_LOCK
 +}
 +
 +function sendmail() {
 +        case "$1" in
 +        'STATUS')
 +                MAIL_SUBJECT='Status execution '$SCRIPT_NAME' script.'
 +        ;;
 +        *)
 +                MAIL_SUBJECT='ERROR while execution '$SCRIPT_NAME' script !!!'
 +        ;;
 +        esac
 +
 +$CAT_COMMAND <<MAIL >$FILE_MAIL
 +Subject: $MAIL_SUBJECT
 +Date: $VAR_EMAILDATE
 +From: $VAR_SENDER
 +To: $MAIL_RECIPIENT
 +
 +MAIL
 +
 +$CAT_COMMAND $FILE_LAST_LOG >> $FILE_MAIL
 +
 +$PROG_SENDMAIL -f $VAR_SENDER -t $MAIL_RECIPIENT < $FILE_MAIL
 +
 +$RM_COMMAND -f $FILE_MAIL
 +
 +}
 +
 +# Main.
 +log ""
 +log "+-------------------------------------------------------------------------------+"
 +log "| .................... Start des MySQL-Datenbank-Dumps ........................ |"
 +log "+-------------------------------------------------------------------------------+"
 +log ""
 +log "Das Datenbank-Backupscript wurde mit folgenden Parametern aufgerufen:"
 +log ""
 +log "SCRIPT_NAME     : $SCRIPT_NAME"
 +log "ZIEL-VERZEICHNIS: $DIR_TARGET"
 +log "MAIL_EMPFÄNGER  : $MAIL_RECIPIENT"
 +log "MAIL_STATUS     : $MAIL_STATUS"
 +log ""
 +
 +# Prüfung ob alle benötigten Programme und Befehle vorhanden sind.
 +if [ ! -s "$MYSQLDUMP_COMMAND" ]; then
 +        log "Prüfen, ob das Programm '$MYSQLDUMP_COMMAND' vorhanden ist.................[FEHLER]"
 +        sendmail ERROR
 +        movelog
 +        exit 10
 +else
 +        log "Prüfen, ob das Programm '$MYSQLDUMP_COMMAND' vorhanden ist.................[  OK  ]"
 +fi
 +
 +if [ ! -s "$TOUCH_COMMAND" ]; then
 +        log "Prüfen, ob das Programm '$TOUCH_COMMAND' vorhanden ist.........................[FEHLER]"
 +        sendmail ERROR
 +        movelog
 +        exit 11
 +else
 +        log "Prüfen, ob das Programm '$TOUCH_COMMAND' vorhanden ist.........................[  OK  ]"
 +fi
 +
 +if [ ! -s "$RM_COMMAND" ]; then
 +        log "Prüfen, ob das Programm '$RM_COMMAND' vorhanden ist............................[FEHLER]"
 +        sendmail ERROR
 +        movelog
 +        exit 12
 +else
 +        log "Prüfen, ob das Programm '$RM_COMMAND' vorhanden ist............................[  OK  ]"
 +fi
 +
 +if [ ! -s "$CAT_COMMAND" ]; then
 +        log "Prüfen, ob das Programm '$CAT_COMMAND' vorhanden ist..........................[FEHLER]"
 +        sendmail ERROR
 +        movelog
 +        exit 13
 +else
 +        log "Prüfen, ob das Programm '$CAT_COMMAND' vorhanden ist...........................[  OK  ]"
 +fi
 +
 +if [ ! -s "$DATE_COMMAND" ]; then
 +        log "Prüfen, ob das Programm '$DATE_COMMAND' vorhanden ist...........................[FEHLER]"
 +        sendmail ERROR
 +        movelog
 +        exit 14
 +else
 +        log "Prüfen, ob das Programm '$DATE_COMMAND' vorhanden ist..........................[  OK  ]"
 +fi
 +
 +if [ ! -s "$MKDIR_COMMAND" ]; then
 +        log "Prüfen, ob das Programm '$MKDIR_COMMAND' vorhanden ist..........................[FEHLER]"
 +        sendmail ERROR
 +        movelog
 +        exit 15
 +else
 +        log "Prüfen, ob das Programm '$MKDIR_COMMAND' vorhanden ist.........................[  OK  ]"
 +fi
 +
 +if [ ! -s "$PROG_SENDMAIL" ]; then
 +        log "Prüfen, ob das Programm '$PROG_SENDMAIL' vorhanden ist.................[FEHLER]"
 +        sendmail ERROR
 +        movelog
 +        exit 16
 +else
 +        log "Prüfen, ob das Programm '$PROG_SENDMAIL' vorhanden ist.................[  OK  ]"
 +fi
 +
 +if [ ! -e "$FILE_LOCK" ]; then
 +        log "Prüfen, ob das Programm nicht bereits oder noch läuft......................[  OK  ]"
 +
 +        $TOUCH_COMMAND $FILE_LOCK
 +else
 +        log "Prüfen, ob das Programm nicht bereits oder noch läuft......................[FEHLER]"
 +        log ""
 +        log "FEHLER: Das Script läuft bereits bzw. immer noch, oder die LOCK-Datei"
 +        log "existiert noch von einem früheren Programmaufruf!"
 +        log ""
 +        sendmail ERROR
 +        movelog
 +        exit 20
 +fi
 +
 +if [ ! -d "$DIR_TARGET" ]; then
 +        log "Prüfen, ob Zielverzeichnis existiert.......................................[FEHLER]"
 +        log ""
 +        log " INFO: Erstelle Zielverzeichnis!"
 +        log " INFO: --> "$DIR_TARGET
 +        log ""
 +
 +        $MKDIR_COMMAND -p $DIR_TARGET
 +else
 +        log "Prüfen, ob Zielverzeichnis existiert.......................................[  OK  ]"
 +fi
 +
 +if [ "$UID" -ne 0 ]; then
 +        log "Prüfen, ob das Script mit root-Rechten gestartet wurde.......................[FEHLER]"
 +        log ""
 +        sendmail ERROR
 +        movelog
 +        exit 21
 +else
 +        log "Prüfen, ob das Script mit root-Rechten gestartet wurde.....................[  OK  ]"
 +fi
 +
 +# Start dumping.
 +log ""
 +log "+-------------------------------------------------------------------------------+"
 +log "| .................... Start des Datenbank-Dumps .............................. |"
 +log "+-------------------------------------------------------------------------------+"
 +log ""
 +
 +log "$MYSQLDUMP_COMMAND -h "$DB_HOST" -u "$DB_USER" --all-databases --events > $DIR_TARGET$FILE_NAME"
 +
 +$MYSQLDUMP_COMMAND -h $DB_HOST -u $DB_USER --password=$DB_SECRET --all-databases --events > $DIR_TARGET$FILE_NAME
 +
 +if [ "$?" != 0 ]; then
 +        log ""
 +        $RM_COMMAND -f $FILE_LOCK
 +        sendmail ERROR
 +        movelog
 +        exit 99
 +else
 +        log ""
 +        log "+-------------------------------------------------------------------------------+"
 +        log "| ........................ Datenbank-Dump beendet ............................. |"
 +        log "+-------------------------------------------------------------------------------+"
 +        log ""
 +fi
 +
 +# Bis auf die letzten drei Datenbankbackups alle anderen Dateien löschen.
 +cd $DIR_TARGET/
 +(ls $DUMP_FILES -t|head -n 3;ls $DUMP_FILES )|sort|uniq -u|xargs rm
 +if [ "$?" != "0" ]; then
 +        log "alte Datenbanksicherungen aus Zielverzeichnis $DIR_TARGET gelöscht....[FEHLER]"
 +        log ""
 +        sendmail ERROR
 +        movelog
 +        exit 69
 +else
 +        log "alte Datenbanksicherungen aus Zielverzeichnis $DIR_TARGET gelöscht....[  OK  ]"
 +        log ""
 +fi
 +
 +# Finish syncing.
 +log "+-------------------------------------------------------------------------------+"
 +log "| ..................... Ende des MySQL-Datenbank-Dumps ........................ |"
 +log "+-------------------------------------------------------------------------------+"
 +log ""
 +
 +# Status eMail versenden
 +if [ $MAIL_STATUS = 'J' ]; then
 +        sendmail STATUS
 +fi
 +
 +# Temporäres Logfile permanent sichern
 +movelog
 +
 +exit 0
 +
 +</file>
 +Zum Ausführen benötigt unser script dann **X**-Rechte, die wir ihm jetzt noch verleihen.
 +   # chmod +x /root/bin/mysql_fulldump
 +
 +Anschließend tragen wir noch in der Datei **/etc/crontab** ein, daß das Script täglich um 3:20 Uhr laufen soll.
 +   # vim /etc/crontab
 +
 +<file bash /etc/crontab>SHELL=/bin/bash
 +PATH=/sbin:/bin:/usr/sbin:/usr/bin
 +MAILTO=root
 +HOME=/
 +
 +# For details see man 4 crontabs
 +
 +# Example of job definition:
 +# .---------------- minute (0 - 59)
 +# |  .------------- hour (0 - 23)
 +# |  |  .---------- day of month (1 - 31)
 +# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
 +# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
 +# |  |  |  |  |
 +# *  *  *  *  * user-name command to be executed
 +
 +# Django: 2013-05-13 täglicher MySQL-Datenbankdump
 +20  3  *  *  root /root/bin/mysql_fulldump 1>/dev/null 2>&1
 +
 +</file>
 +==== phpMyAdmin ====
 +Zur komfortablen Administration unseres [[centos:mysql|mySQL-Datenbankserver unter CentOS 6.0]] greifen wir auf das PHP-Projekt [[http://phpmyadmin.sourceforge.net/|phpMyAdmin]] zurück. Im Kapitel [[centos:phpmyadmin_c6|phpMyAdmin Installation unter CentOS 6.x]] ist die Installation und Konfiguration des PHP Projektes unter CentOS 6.x beschrieben.
 +
 +
 +====== Links ======
 +  * **[[wiki:start|Zurück zu Projekte und Themenkapitel]]**
 +  * **[[http://dokuwiki.nausch.org/doku.php/|Zurück zur Startseite]]**
 +
  
  • centos/mysql.txt
  • Zuletzt geändert: 20.04.2018 09:08.
  • (Externe Bearbeitung)