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)