mySQL-Datenbankserver unter CentOS 6.x einrichten
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:
- Datenbankpasswort des MySQL-Datenbankuser root setzen
- Anonyme Benutzerkonten löschen
- Deaktivieren der Remote-Zugriffsmöglichkeit für den MySQL-Datenbankuser root
- Löschen der nicht benötigten Testdatenbank test
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven'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.
- laufenden mySQL-Daemon stoppen:
# service mysqld stop
- mySQL im sicheren Modus starten:
# mysqld_safe --skip-grant-tables --user=root &
- 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>
- 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>
- 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>
- Verbindung zum mySQL-Daemon beenden:
mysql> quit
- 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
- 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.
127.0.0.1