Inhaltsverzeichnis
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
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.
Diskussion