MySQL Zugriff über das Netzwerk schlägt immer fehl

[binary trust]

Hi,

ich verwende Debian Sarge und mysqld in der version 4.0.23.
ein netstat -ltn zeigt mir an das mysql unter 0.0.0.0:3306 eingentlich laufen sollte. wenn ich nun aber ein telnet 10.2.3.135 3306 kommt ständig die meldung:

telnet 10.2.3.135 3306
Trying 10.2.3.135...
Connected to 10.2.3.135.
Escape character is '^]'.
CHost '10.2.3.133' is not allowed to connect to this MySQL serverConnection closed by foreign host.

skip_networking ist auskommetiert.
in der neuen my.cnf hab ich die bind_address auf die ip angepasst, klappt dennoch nicht.

woran kann das ganze denn liegen ?

[Tomek]

Hat der User in der MySQL-Datenbank entsprechende Rechte, um sich auch von extern verbinden zu dürfen?
Dazu bitte lesen: http://dev.mysql.com/doc/mysql/de/privileges.html

Und: Wenn du MySQL an die externe Netzwerkadresse bindest, kann vermutlich keine Verbindung von localhost zum lokalen MySQL-Server hergestellt werden. Ich würde skip-networking und bind-address aus der my.cnf entfernen bzw. auskommentieren.

[binary trust]

Ok, ich hab die Einträge für skip_networking und bind_address auskommentiert sowie denn datenbankzugriff auf % gesetzt. dannach hat das verbinden mittels phpmyadmin von einen fremden host geklappt.

der eigentliche grund wofür ich die kommunikation überhaupt für mysql freischalten will / muss ist der das ich meine benutzer mittels smtp_auth bei postfix sowie courier mit der mysql datenbank abfragen möchte (einfachere verwaltung). problem ist das postfix in einen jail (chroot) läuft der nicht auf denn mysql socket zugreifen kann. also muss ich eine connection mittels tcp herstellen. das nun die frage was ich in host eintragen muss, wäre hier ein 127.0.0.1:3306 genügent oder muss es die ip am netzwerkdevice sein?

im prinzip müsste folgende mysql konfiguration ausreichend sein:
user: user postfix, passwort test, host localhost
db: datenbank: provider, user: postfix, host: localhost
ausreichen da mysql und postfix auf der selben maschine laufen.

die genaue fehlermeldung lautet:
b 17 10:40:04 localhost postfix/cleanup[2856]: warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

was ich allerdings nicht verstehe warum diese über denn socket connecten möchte? zugriffe sind alle auf 127.0.0.1:3306 eingestellt ...


was mich allerdins etwas verwundert ist das bei root standartmäßig 2 einträge drin sind, einmal für host localhost und einmal denn hostnamen mail.
muss ich das für alle anderen benutzer und datenbankzugriffe auch so einstellen?

gruß
binary trustix (gequälter wannabe mailadmin)

[marce]

bei php ist's so, dass wenn man auf eine MySQL zugreifen will, die auf localhost oder 127.0.0.1 liegt er automatisch über den socket geht. Vielleicht liegt der Fall bei postfix ähnlich...

[binary trust]

hab das ganze nun auf die ip abgeändert, bring aber alles nichts.
ich post am besten mal meine kompleten konfigurationsdateien, nicht das sich hier nur ein dummer rechtschreibfehler eingeschlichen hat:

ich bin nach folgender Anleitung vorgegangen:
http://workaround.org/articles/ispmail-sarge/

/etc/mysql/my.cnf:


[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
log = /var/log/mysql/mysql.log
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
query_cache_limit = 1048576
query_cache_size = 26214400
query_cache_type = 1

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]

[isamchk]
key_buffer = 16M



/etc/mysql/my.cnf:


2bounce_notice_recipient = postmaster
access_map_reject_code = 554
address_verify_default_transport = $default_transport
address_verify_local_transport = $local_transport
address_verify_map =
address_verify_negative_cache = yes
address_verify_negative_expire_time = 3d
address_verify_negative_refresh_time = 3h
address_verify_poll_count = 3
address_verify_poll_delay = 3s
address_verify_positive_expire_time = 31d
address_verify_positive_refresh_time = 7d
address_verify_relay_transport = $relay_transport
address_verify_relayhost = $relayhost
address_verify_sender = postmaster
address_verify_service_name = verify
address_verify_transport_maps = $transport_maps
address_verify_virtual_transport = $virtual_transport
alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases, nis:mail.aliases
allow_mail_to_commands = alias, forward
allow_mail_to_files = alias, forward
allow_min_user = no
allow_percent_hack = yes
allow_untrusted_routing = no
alternate_config_directories =
always_bcc =
append_at_myorigin = yes
append_dot_mydomain = yes
application_event_drain_time = 100s
backwards_bounce_logfile_compatibility = yes
berkeley_db_create_buffer_size = 16777216
berkeley_db_read_buffer_size = 131072
best_mx_transport =
biff = yes
body_checks =
body_checks_size_limit = 51200
bounce_notice_recipient = postmaster
bounce_queue_lifetime = 5d
bounce_service_name = bounce
bounce_size_limit = 50000
broken_sasl_auth_clients = yes
canonical_maps =
cleanup_service_name = cleanup
command_directory = /usr/sbin
command_expansion_filter = 1234567890!@%-_=+:,./abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ
command_time_limit = 1000s
config_directory = /etc/postfix
content_filter =
daemon_directory = /usr/lib/postfix
daemon_timeout = 18000s
debug_peer_level = 2
debug_peer_list =
default_database_type = hash
default_delivery_slot_cost = 5
default_delivery_slot_discount = 50
default_delivery_slot_loan = 3
default_destination_concurrency_limit = 20
default_destination_recipient_limit = 50
default_extra_recipient_limit = 1000
default_minimum_delivery_slots = 3
default_privs = nobody
default_process_limit = 100
default_rbl_reply = $rbl_code Service unavailable; $rbl_class [$rbl_what] blocked using $rbl_domain${rbl_reason?; $rbl_reason}
default_recipient_limit = 10000
default_transport = smtp
default_verp_delimiters = +=
defer_code = 450
defer_service_name = defer
defer_transports =
delay_notice_recipient = postmaster
delay_warning_time = 0h
deliver_lock_attempts = 20
deliver_lock_delay = 1s
disable_dns_lookups = no
disable_mime_input_processing = no
disable_mime_output_conversion = no
disable_verp_bounces = no
disable_vrfy_command = no
dont_remove = 0
double_bounce_sender = double-bounce
duplicate_filter_limit = 1000
empty_address_recipient = MAILER-DAEMON
enable_errors_to = no
enable_original_recipient = yes
error_notice_recipient = postmaster
error_service_name = error
expand_owner_alias = no
export_environment = TZ MAIL_CONFIG
fallback_relay =
fallback_transport =
fast_flush_domains = $relay_domains
fast_flush_purge_time = 7d
fast_flush_refresh_time = 12h
fault_injection_code = 0
flush_service_name = flush
fork_attempts = 5
fork_delay = 1s
forward_expansion_filter = 1234567890!@%-_=+:,./abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ
forward_path = $home/.forward${recipient_delimiter}${extension}, $home/.forward
hash_queue_depth = 1
hash_queue_names = incoming, active, deferred, bounce, defer, flush, hold, trace
header_address_token_limit = 10240
header_checks =
header_size_limit = 102400
helpful_warnings = yes
home_mailbox =
hopcount_limit = 50
html_directory = no
ignore_mx_lookup_error = no
import_environment = MAIL_CONFIG MAIL_DEBUG MAIL_LOGTAG TZ XAUTHORITY DISPLAY
in_flow_delay = 1s
inet_interfaces = all
initial_destination_concurrency = 5
invalid_hostname_reject_code = 501
ipc_idle = 100s
ipc_timeout = 3600s
ipc_ttl = 1000s
line_length_limit = 2048
lmtp_bind_address =
lmtp_bind_address6 =
lmtp_cache_connection = yes
lmtp_connect_timeout = 0s
lmtp_data_done_timeout = 600s
lmtp_data_init_timeout = 120s
lmtp_data_xfer_timeout = 180s
lmtp_destination_concurrency_limit = $default_destination_concurrency_limit
lmtp_destination_recipient_limit = $default_destination_recipient_limit
lmtp_lhlo_timeout = 300s
lmtp_mail_timeout = 300s
lmtp_quit_timeout = 300s
lmtp_rcpt_timeout = 300s
lmtp_rset_timeout = 120s
lmtp_sasl_auth_enable = no
lmtp_sasl_password_maps =
lmtp_sasl_security_options = noplaintext, noanonymous
lmtp_send_xforward_command = no
lmtp_skip_quit_response = no
lmtp_tcp_port = 24
lmtp_xforward_timeout = 300s
local_command_shell =
local_destination_concurrency_limit = 2
local_destination_recipient_limit = 1
local_recipient_maps = proxy:unix:passwd.byname $alias_maps
local_transport = local:$myhostname
luser_relay =
mail_name = Postfix
mail_owner = postfix
mail_release_date = 20040915
mail_spool_directory = /var/mail
mail_version = 2.1.5
mailbox_command =
mailbox_command_maps =
mailbox_delivery_lock = fcntl, dotlock
mailbox_size_limit = 51200000
mailbox_transport =
mailq_path = /usr/bin/mailq
manpage_directory = /usr/share/man
maps_rbl_domains =
maps_rbl_reject_code = 554
masquerade_classes = envelope_sender, header_sender, header_recipient
masquerade_domains =
masquerade_exceptions =
max_idle = 100s
max_use = 100
maximal_backoff_time = 4000s
maximal_queue_lifetime = 5d
message_size_limit = 10240000
mime_boundary_length_limit = 2048
mime_header_checks = $header_checks
mime_nesting_limit = 100
minimal_backoff_time = 1000s
multi_recipient_bounce_reject_code = 550
mydestination = localhost
mydomain = creative.de
myhostname = mail.creative.de
mynetworks = 127.0.0.1/32 10.2.3.135/32 [::1]/128 [fe80::240:caff:fe60:84ae]/128
mynetworks_style = host
myorigin = $mydomain
nested_header_checks = $header_checks
newaliases_path = /usr/bin/newaliases
non_fqdn_reject_code = 504
notify_classes = resource, software
owner_request_special = yes
parent_domain_matches_subdomains = debug_peer_list,fast_flush_domains,mynetworks,perm it_mx_backup_networks,qmqpd_authorized_clients,rel ay_domains,smtpd_access_maps
permit_mx_backup_networks =
pickup_service_name = pickup
prepend_delivered_header = command, file, forward
process_id_directory = pid
propagate_unmatched_extensions = canonical, virtual
proxy_interfaces =
proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks
qmgr_clog_warn_time = 300s
qmgr_fudge_factor = 100
qmgr_message_active_limit = 20000
qmgr_message_recipient_limit = 20000
qmgr_message_recipient_minimum = 10
qmqpd_authorized_clients =
qmqpd_error_delay = 1s
qmqpd_timeout = 300s
queue_directory = /var/spool/postfix
queue_file_attribute_count_limit = 100
queue_minfree = 0
queue_run_delay = 1000s
queue_service_name = qmgr
rbl_reply_maps =
readme_directory = /usr/share/doc/postfix
receive_override_options =
recipient_bcc_maps =
recipient_canonical_maps =
recipient_delimiter =
reject_code = 554
relay_clientcerts =
relay_destination_concurrency_limit = $default_destination_concurrency_limit
relay_destination_recipient_limit = $default_destination_recipient_limit
relay_domains = $mydestination
relay_domains_reject_code = 554
relay_recipient_maps =
relay_transport = relay
relayhost =
relocated_maps =
require_home_directory = no
resolve_dequoted_address = yes
resolve_null_domain = no
rewrite_service_name = rewrite
sample_directory = /usr/share/doc/postfix/examples
sender_based_routing = no
sender_bcc_maps =
sender_canonical_maps =
sendmail_path = /usr/sbin/sendmail
service_throttle_time = 60s
setgid_group = postdrop
show_user_unknown_table_name = yes
showq_service_name = showq
smtp_always_send_ehlo = yes
smtp_bind_address =
smtp_bind_address6 =
smtp_connect_timeout = 30s
smtp_data_done_timeout = 600s
smtp_data_init_timeout = 120s
smtp_data_xfer_timeout = 180s
smtp_defer_if_no_mx_address_found = no
smtp_destination_concurrency_limit = $default_destination_concurrency_limit
smtp_destination_recipient_limit = $default_destination_recipient_limit
smtp_enforce_tls = no
smtp_helo_name = $myhostname
smtp_helo_timeout = 300s
smtp_host_lookup = dns
smtp_line_length_limit = 0
smtp_mail_timeout = 300s
smtp_mx_address_limit = 0
smtp_mx_session_limit = 2
smtp_never_send_ehlo = no
smtp_pix_workaround_delay_time = 10s
smtp_pix_workaround_threshold_time = 500s
smtp_quit_timeout = 300s
smtp_quote_rfc821_envelope = yes
smtp_randomize_addresses = yes
smtp_rcpt_timeout = 300s
smtp_rset_timeout = 120s
smtp_sasl_auth_enable = no
smtp_sasl_password_maps =
smtp_sasl_security_options = noplaintext, noanonymous
smtp_sasl_tls_security_options = $var_smtp_sasl_opts
smtp_sasl_tls_verified_security_options = $var_smtp_sasl_tls_opts
smtp_send_xforward_command = no
smtp_skip_5xx_greeting = yes
smtp_skip_quit_response = yes
smtp_starttls_timeout = 300s
smtp_tls_CAfile =
smtp_tls_CApath =
smtp_tls_cert_file =
smtp_tls_cipherlist =
smtp_tls_dcert_file =
smtp_tls_dkey_file = $smtp_tls_dcert_file
smtp_tls_enforce_peername = yes
smtp_tls_key_file = $smtp_tls_cert_file
smtp_tls_loglevel = 0
smtp_tls_note_starttls_offer = no
smtp_tls_per_site =
smtp_tls_scert_verifydepth = 5
smtp_tls_session_cache_database =
smtp_tls_session_cache_timeout = 3600s
smtp_use_tls = no
smtp_xforward_timeout = 300s
smtpd_authorized_verp_clients = $authorized_verp_clients
smtpd_authorized_xclient_hosts =
smtpd_authorized_xforward_hosts =
smtpd_banner = $myhostname ESMTP (fReakSys v.01)
smtpd_client_connection_count_limit = 50
smtpd_client_connection_limit_exceptions = $mynetworks
smtpd_client_connection_rate_limit = 0
smtpd_client_restrictions =
smtpd_data_restrictions =
smtpd_delay_reject = yes
smtpd_enforce_tls = no
smtpd_error_sleep_time = 1s
smtpd_etrn_restrictions =
smtpd_expansion_filter = \t\40!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~
smtpd_hard_error_limit = 20
smtpd_helo_required = no
smtpd_helo_restrictions =
smtpd_history_flush_threshold = 100
smtpd_junk_command_limit = 100
smtpd_noop_commands =
smtpd_null_access_lookup_key = <>
smtpd_policy_service_max_idle = 300s
smtpd_policy_service_max_ttl = 1000s
smtpd_policy_service_timeout = 100s
smtpd_proxy_ehlo = $myhostname
smtpd_proxy_filter =
smtpd_proxy_timeout = 100s
smtpd_recipient_limit = 1000
smtpd_recipient_overshoot_limit = 1000
smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
smtpd_reject_unlisted_recipient = yes
smtpd_reject_unlisted_sender = no
smtpd_restriction_classes =
smtpd_sasl_application_name = smtpd
smtpd_sasl_auth_enable = yes
smtpd_sasl_exceptions_networks =
smtpd_sasl_local_domain =
smtpd_sasl_security_options = noanonymous
smtpd_sasl_tls_security_options = $smtpd_sasl_security_options
smtpd_sender_login_maps =
smtpd_sender_restrictions =
smtpd_soft_error_limit = 10
smtpd_starttls_timeout = 300s
smtpd_timeout = 300s
smtpd_tls_CAfile =
smtpd_tls_CApath =
smtpd_tls_ask_ccert = no
smtpd_tls_auth_only = no
smtpd_tls_ccert_verifydepth = 5
smtpd_tls_cert_file = /etc/postfix/smtpd.cert
smtpd_tls_cipherlist =
smtpd_tls_dcert_file =
smtpd_tls_dh1024_param_file =
smtpd_tls_dh512_param_file =
smtpd_tls_dkey_file = $smtpd_tls_dcert_file
smtpd_tls_key_file = /etc/postfix/smtpd.key
smtpd_tls_loglevel = 0
smtpd_tls_received_header = no
smtpd_tls_req_ccert = no
smtpd_tls_session_cache_database =
smtpd_tls_session_cache_timeout = 3600s
smtpd_tls_wrappermode = no
smtpd_use_tls = yes
soft_bounce = no
stale_lock_time = 500s
strict_7bit_headers = no
strict_8bitmime = no
strict_8bitmime_body = no
strict_mime_encoding_domain = no
strict_rfc821_envelopes = no
sun_mailtool_compatibility = no
swap_bangpath = yes
syslog_facility = mail
syslog_name = postfix
tls_daemon_random_bytes = 32
tls_daemon_random_source =
tls_ipv6_version = 1.24
tls_random_bytes = 32
tls_random_exchange_name = ${queue_directory}/prng_exch
tls_random_prng_update_period = 60s
tls_random_reseed_period = 3600s
tls_random_source =
trace_service_name = trace
transport_maps =
transport_retry_time = 60s
trigger_timeout = 10s
undisclosed_recipients_header = To: undisclosed-recipients:;
unknown_address_reject_code = 450
unknown_client_reject_code = 450
unknown_hostname_reject_code = 450
unknown_local_recipient_reject_code = 550
unknown_relay_recipient_reject_code = 550
unknown_virtual_alias_reject_code = 550
unknown_virtual_mailbox_reject_code = 550
unverified_recipient_reject_code = 450
unverified_sender_reject_code = 450
verp_delimiter_filter = -=+
virtual_alias_domains =
virtual_alias_expansion_limit = 1000
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual_forwardings.cf mysql:/etc/postfix/mysql-virtual_email2email.cf
virtual_alias_recursion_limit = 1000
virtual_destination_concurrency_limit = $default_destination_concurrency_limit
virtual_destination_recipient_limit = $default_destination_recipient_limit
virtual_gid_maps = static:5000
virtual_mailbox_base = /home/vmail
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual_domains.cf
virtual_mailbox_limit = 51200000
virtual_mailbox_lock = fcntl
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual_mailboxes.cf
virtual_minimum_uid = 100
virtual_transport = virtual
virtual_uid_maps = static:5000


die einträge in denn folgenden dateien unterscheiden sich bis auf die table und select und where field nicht:

-rw-r----- 1 root postfix 137 2005-02-16 20:11 mysql-virtual_domains.cf
-rw-r----- 1 root postfix 135 2005-02-16 20:12 mysql-virtual_email2email.cf
-rw-r----- 1 root postfix 143 2005-02-16 20:12 mysql-virtual_forwardings.cf
-rw-r----- 1 root postfix 142 2005-02-16 20:12 mysql-virtual_mailboxes.cf



user = postfix
password = postfix
dbname = provider
table = domains
select_field = 'virtual'
where_field = domain
host = 10.2.3.135:3306


hier noch die /etc/postfix/sasl/smtpd.conf:

-rw-r--r-- 1 root root 275 2005-02-17 10:59 smtpd.conf


pwcheck_method: auxprop
auxprop_plugin: sql
mech_list: plain login cram-md5 digest-md5
sql_engine: mysql
sql_hostnames: 10.2.3.135:3306
sql_user: postfix
sql_passwd: postfix
sql_database: provider
sql_select: select password from users where email='%u@%r'
#sql_verbose: yes


in der /etc/courier/authdeamon wurde nur folgender eintrag geändert:



authmodulelist="authmysql"
#authmodulelist="authpam"


und zu guter letzt die courier
/etc/courier/authmysqlrc



MYSQL_SERVER 10.2.3.135
MYSQL_USERNAME postfix
MYSQL_PASSWORD postfix
MYSQL_PORT 3306
MYSQL_OPT 0
MYSQL_DATABASE provider
MYSQL_USER_TABLE users
MYSQL_CLEAR_PWFIELD clear
MYSQL_UID_FIELD 5000
MYSQL_GID_FIELD 5000
MYSQL_LOGIN_FIELD email
MYSQL_HOME_FIELD "/home/vmail"
MYSQL_MAILDIR_FIELD concat(email,'/')


Hab mich bislang Schritt für Schritt an die oben genannte Anleitung gehalten, aber ihrgendwie gibt es einfach Probleme mit der Connection zum MySQL Server.

[binary trust]

hab das problem gefunden,
hier die info für alle leidenden admins:

ich hab es so hinbekommen indem ich ein hardlink des mysql sockets in denn chroot von postfix gelegt habe:

ln /var/run/mysqld/mysqld.sock /var/sppol/postfix/var/run/mysqld/mysqld.sock
(OS: Debian Sarge)


Somit ist zumindest dieses weitere Problem gelöst, weitere werden folgen ... :)

[d@tenmaulwurf]

und was ist, wenn beides auf verschiedenen Partitionen liegt? *schnüff*

dann sind hardlinks leider nicht mehr möglich :x