Home

Search this site:
 

Horshack’s
very
own site

 

This is Bavaria, Germany!
You will find links to Perl,
bootdisks for system administration,
a lot of Linux,
Windows administration
and some personal belongings.
Mysql

MySQL

Home of MySQL is http://www.mysql.com where you can also find a manual with hints (books, tutorials, where to get drivers, examples). The manual is great, you can buy a printed version from http://www.nusphere.com. VERY useful is phpMyAdmin, an administration tool of MySQL in your webbrowser: rename tables, columns, create and delete data, tables, users... EASY to install.

This helps me to convert my Access97-databases into mysql: exportSQL version 2.0 from http://www.cynergi.net/exportsql/

Administrating MySQL

Configuration

Binding mysqld to a special ip-address:
[mysqld]
bind-address=127.0.0.1

Commands

Show rights for user: mysqlaccess richard booksdatabase
Creating a User having rights only for one database:
mysql> create database nagios;
mysql> use mysql;
mysql> grant all on nagios.* to nagiosadmin@192.168.1.12 identified by 'apassword';
mysql> grant all on nagios.* to everywhere_admin@'%' identified by 'apassword2';
mysql> flush privileges;
mysql> quit

lindt:~# mysql -unagios -pechojuhei nagios
mysql>  
Show how a table is built exactly:
mysql> show fields from tabeinrichtungen;
+--------------------+----------+------+-----+----------+----------------+
| Field              | Type     | Null | Key | Default  | Extra          |
+--------------------+----------+------+-----+----------+----------------+
| ID                 | int(11)  |      | MUL | 0        | auto_increment |
| NameEinrichtung    | char(90) | YES  |     | NULL     |                |
| Strasse            | char(50) | YES  |     | NULL     |                |
| PLZ                | int(11)  |      |     | 90513    |                |
| Ort                | char(50) |      |     | Zirndorf |                |
| TelefonEinrichtung | char(50) | YES  |     | NULL     |                |
| FaxEinrichtung     | char(50) | YES  |     | NULL     |                |
| EmailEinrichtung   | char(50) | YES  |     | NULL     |                |
| Homepage           | char(50) | YES  |     | NULL     |                |
| Aktivitaet_ID      | int(11)  |      | MUL | 0        |                |
+--------------------+----------+------+-----+----------+----------------+
10 rows in set (0.00 sec)

Perl and MySQL

How to suppress errormessages in Perl:
$sql = "insert into dupes (nr,name) values (1,'lilli')";
# error will happen here (dupe):
$cur = $dbh->prepare($sql);
# do not print error into my programs output!
# i do print my error output on my own.
$cur->{'PrintError'}=0;
if (!$cur->execute) {
    print "My Error: " . $cur->errstr . "\n";
}
$cur->finish;

Mysql on Mac OS X

There is a package to simply install MySQL 4.021 on a Mac at http://www.serverlogistics.com/mysql.php. Included is a Controlpanel to stop and start the MySQL-server.

SQL-queries to cheat from

Records from the last 21 days only.

select
    date_format(wann,'%Y-%m-%d') as date_only,
    date_format(wann,'%T') as time_only,
    weekday(wann) as weekdaynumber,
    dayname(wann) as dayname
from statistics
where wann > (now() - interval 21 day)
    and customer='findus'
order by wann desc

+------------+-----------+---------------+---------+
| date_only  | time_only | weekdaynumber | dayname |
+------------+-----------+---------------+---------+
| 2008-09-23 | 20:52:28  |             1 | Tuesday |
+------------+-----------+---------------+---------+








Impressum