Inhaltsverzeichnis

mySQL-Datenbankserver unter CentOS 6.x einrichten

mySQL Logo
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:

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
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

mysql

 # rpm -qil mysql
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

perl-DBD-MySQL

 # rpm -qil perl-DBD-MySQL
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

perl-DBI

 # rpm -qil perl-DBI
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

Konfiguration

my.cnf

Die Konfiguration unseres Datenbankservers erfolgt über die Konfigurationsdatei /etc/my.cnf, die uns bei der Installation bereits mitgeliefert wurde.

/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

Weitere Beispiele finden sich im Verzeichnis /usr/share/doc/mysql-server-5.1.52/.

 # ll /usr/share/doc/mysql-server-5.1.52/
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

Bei Bedarf, wie z.B. der Angabe eines spezifischen Datenbankverzeichnisses, tragen wir unsere individuelle Konfiguration in der my.cnf nach.

[mysqld]
# Django : 2011-10-25 spezifisches Datenbankverzeichnis angegeben
# default: datadir=/var/lib/mysql
datadir=/var/lib/mysql/data
...

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 
-rwxr-xr-x 1 root root 5509 Jun 25 07:51 /etc/init.d/mysqld
 # service mysqld start
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  ]

Der Start unseres Servers wir uns in der Logdatei des mySQl-Daemons entsprechend vermerkt:

 # less /var/log/mysqld.log
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

Gemäß unserer Konfiguration wurden im unseren data-Verzeichnis die ersten Datenbankfile angelegt.

 # ll /var/lib/mysql/data/
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

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:

  1. Datenbankpasswort des MySQL-Datenbankuser root setzen
  2. Anonyme Benutzerkonten löschen
  3. Deaktivieren der Remote-Zugriffsmöglichkeit für den MySQL-Datenbankuser root
  4. Löschen der nicht benötigten Testdatenbank test

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven'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!

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
/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
}

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
 ...
# Django : 2011-10-25 mySQL-Zugriff freigeschaltet
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

Anschließend aktivieren wir die Änderungen an unserem Paketfilter, indem wir den Daemon durchstarten.

 # service iptables restart
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Setting chains to policy ACCEPT: filter nat      [  OK  ]
iptables: Unloading modules:                               [  OK  ]
iptables: Applying firewall rules:                         [  OK  ]

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
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

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
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

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.

  1. laufenden mySQL-Daemon stoppen:
     # service mysqld stop
  2. mySQL im sicheren Modus starten:
     # mysqld_safe --skip-grant-tables --user=root & 
  3. Datenbank-Verbindung aufbauen:
     # mysql -u root
    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> 
  4. mySQL Datenbank auswählen:
    mysql> use mysql;
    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> 
  5. Passwort des users root neu setzen:
    mysql> update user set password=password ("d3r_desß3n_N@mEn_n1chT_GeN@nNT_w3rd3n_dArf!") where user="root";
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> 
  6. Verbindung zum mySQL-Daemon beenden:
    mysql> quit
  7. mySQL stoppen:
     # ps aux | grep mysql
    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
     # kill 5703
  8. mySQL-Daemon wieder „normal“ starten:
     # service mysqld start

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
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

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

phpMyAdmin

Zur komfortablen Administration unseres mySQL-Datenbankserver unter CentOS 6.0 greifen wir auf das PHP-Projekt phpMyAdmin zurück. Im Kapitel phpMyAdmin Installation unter CentOS 6.x ist die Installation und Konfiguration des PHP Projektes unter CentOS 6.x beschrieben.

Links