远程连接linux下mysql数据库

[桾楉叁杄]

linux 下连接mysql数据库报错
找不到此连接，错误代码10065
linux是在虚拟机下建立的
虚拟机和宿主机可以互相ping通
但是用宿主机上的Navicat 8.0 进行连接虚拟机里的mysql时 连接不上
虚拟机里的mysql在虚拟机时可以正常使用
为什么呢？
网上说在etc下my.conf下更改bind-address
但是在我的my.conf下压根就没有bind-address 这项
这样的话如何搞

[steptodream]

1.首先你要确认的是你的mysql是否只在本地ip127.0.0.1上监听
lsof -i:3306
在确认一遍你的my.cnf里是否有bind-address = 127.0.0.1 如果有注释掉 重启mysql
2.你需要在mysql里添加允许远程访问的用户
grant all privileges on DBName.* to username@ip identified by "password";
3.你需要在防火墙里允许来自远程ip访问3306的连接
/sbin/iptables -I INPUT -s ip -p tcp -m tcp --dport 3306 -j ACCEPT
4.如果还不行 暂时关闭selinux试试
setenforce 0

[桾楉叁杄]

[Quote=引用 1 楼 steptodream 的回复:]
1.首先你要确认的是你的mysql是否只在本地ip127.0.0.1上监听
lsof -i:3306
在确认一遍你的my.cnf里是否有bind-address = 127.0.0.1 如果有注释掉 重启mysql
2.你需要在mysql里添加允许远程访问的用户
grant all privileges on DBName.* to username@ip identified by "p……
[/Quote]

都试了 还是不行 然后phpmyadmin装在了虚拟机下的linux里后 当在linux下使用127.0.0.1/phpmyadmin访问的时候也会报错

Existing configuration file (./config.inc.php) is not readable.

大概是权限不够 但是我设置权限了 chmod 660 /var/www/phpMyAdmin/config.inc.php

不知道因为什么

[steptodream]

原来你在弄phpmyadmin。

大概是权限不够 但是我设置权限了 chmod 660 /var/www/phpMyAdmin/config.inc.php
---------------
光这样还不不够 需要apache用户对该文件有写权限 如果config.inc.php文件的属主和组都是root 那么即便是 chmod 660了 apache用户一样没权限
如果你的apache用户就是apache 那么请执行
#chown -R root.apache /var/www/phpMyAdmin

如果还没解决清参照我安装phpMyAdmin的步骤http://blog.csdn.net/steptodream/archive/2010/09/09/5874314.aspx

[桾楉叁杄]

[Quote=引用 4 楼 steptodream 的回复:]
光这样还不不够 需要apache用户对该文件有写权限 如果config.inc.php文件的属主和组都是root 那么即便是 chmod 660了 apache用户一样没权限
如果你的apache……
[/Quote]

我执行#chown -R root.apache /var/www/phpMyAdmin
这句的时候大概意思是告诉我没有root.apache这个组 然后就跳过去了 我装apache的时候应该是没有另开组 这样的话怎么弄？

[steptodream]

[Quote=引用 5 楼 tilts5945 的回复:]
这句的时候大概意思是告诉我没有root.apache这个组 然后就跳过去了 我装apache的时候应该是没有另开组 这样的话怎么弄？
[/Quote]
既然跳过去了 就表示没成功 出问题很正常 那么你就应该首先想到这个问题！

找你的httpd.conf文件 看看下面两句
User daemon
Group daemon

如果是和上面的一样 那么就执行
#chown -R root.daemon /var/www/phpMyAdmin

我还是推荐你按我教程上的方法 创建一个apache专用用户和组

[桾楉叁杄]

[Quote=引用 6 楼 steptodream 的回复:]
引用 5 楼 tilts5945 的回复:
[/Quote]

疯了 还是不好使 还是权限不足的意思 如果我先把windows下导出的.sql文件 导入到linux里的mysql数据库的话 在那个.sql里有存储过程 这样的话 不用客户端一类的东西应该怎么弄呢？

[steptodream]

mysql -uuser -ppasswd dbname < backup.sql

[桾楉叁杄]

[Quote=引用 8 楼 steptodream 的回复:]
mysql -uuser -ppasswd dbname < backup.sql
[/Quote]

比如说 我的用户名是root 密码是123456 要导入的库名为test sql脚本为test.sql

那么应该写成 # mysql -u root -p 123456 test<test.sql
对么？

这么写没导进来

[steptodream]

你先得创建一个test数据库
然后 mysql -uroot -p123456 test<test.sql

没导入成功 你怎么总是不看错误时什么呢？ 根据错误 就能解决问题。

[桾楉叁杄]

[Quote=引用 10 楼 steptodream 的回复:]
你先得创建一个test数据库
然后 mysql -uroot -p123456 test<test.sql

没导入成功 你怎么总是不看错误时什么呢？ 根据错误 就能解决问题。
[/Quote]

我有这个库 库里有表 没提示错误 只不过 存储过程导入后查询一个都没有

[steptodream]

你确认你导出的存储过程没问题吗 照这个上面的提示确认一下内容
http://blog.sina.com.cn/s/blog_5a1a52870100a850.html

[goodmrning]

[Quote=引用 1 楼 steptodream 的回复:]
1.首先你要确认的是你的mysql是否只在本地ip127.0.0.1上监听
lsof -i:3306
在确认一遍你的my.cnf里是否有bind-address = 127.0.0.1 如果有注释掉 重启mysql
2.你需要在mysql里添加允许远程访问的用户
grant all privileges on DBName.* to username@ip identified by "p……
[/Quote]

哪里都能见到主席啊!

[桾楉叁杄]

[Quote=引用 12 楼 steptodream 的回复:]
你确认你导出的存储过程没问题吗 照这个上面的提示确认一下内容
http://blog.sina.com.cn/s/blog_5a1a52870100a850.html
[/Quote]

使用以上方法后给出的结果是

mysql Ver 14.12 Distrib 5.0.87, for pc-linux-gnu (i686) using EditLine wrapper
Copyright (C) 2000-2008 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
--auto-rehash Enable automatic rehashing. One doesn't need to use
'rehash' to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
--disable-auto-rehash.
-A, --no-auto-rehash
No automatic rehashing. One has to use 'rehash' to get
table and field completion. This gives a quicker start of
mysql and disables rehashing on reconnect. WARNING:
options deprecated; use --disable-auto-rehash instead.
-B, --batch Don't use history file. Disable interactive behavior.
(Enables --silent)
--character-sets-dir=name
Directory where character sets are.
--default-character-set=name
Set the default character set.
-c, --comments Preserve comments. Send comments to the server. The
default is --skip-comments (discard comments), enable
with --comments
-C, --compress Use compression in server/client protocol.
-#, --debug[=#] This is a non-debug version. Catch this and exit
-D, --database=name Database to use.
--delimiter=name Delimiter to be used.
-e, --execute=name Execute command and quit. (Disables --force and history
file)
-E, --vertical Print the output of a query (rows) vertically.
-f, --force Continue even if we get an sql error.
-G, --named-commands
Enable named commands. Named commands mean this program's
internal commands; see mysql> help . When enabled, the
named commands can be used from any line of the query,
otherwise only from the first line, before an enter.
Disable with --disable-named-commands. This option is
disabled by default.
-g, --no-named-commands
Named commands are disabled. Use \* form only, or use
named commands only in the beginning of a line ending
with a semicolon (;) Since version 10.9 the client now
starts with this option ENABLED by default! Disable with
'-G'. Long format commands still work from the first
line. WARNING: option deprecated; use
--disable-named-commands instead.
-i, --ignore-spaces Ignore space after function names.
--local-infile Enable/disable LOAD DATA LOCAL INFILE.
-b, --no-beep Turn off beep on error.
-h, --host=name Connect to host.
-H, --html Produce HTML output.
-X, --xml Produce XML output
--line-numbers Write line numbers for errors.
-L, --skip-line-numbers
Don't write line number for errors. WARNING: -L is
deprecated, use long version of this option instead.
-n, --unbuffered Flush buffer after each query.
--column-names Write column names in results.
-N, --skip-column-names
Don't write column names in results. WARNING: -N is
deprecated, use long version of this options instead.
-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.
--sigint-ignore Ignore SIGINT (CTRL-C)
-o, --one-database Only update the default database. This is useful for
skipping updates to other database in the update log.
--pager[=name] Pager to use to display results. If you don't supply an
option the default pager is taken from your ENV variable
PAGER. Valid pagers are less, more, cat [> filename],
etc. See interactive help (\h) also. This option does not
work in batch mode. Disable with --disable-pager. This
option is disabled by default.
--no-pager Disable pager and print to stdout. See interactive help
(\h) also. WARNING: option deprecated; use
--disable-pager instead.
-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).
--prompt=name Set the mysql prompt to this value.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --quick Don't cache result, print it row by row. This may slow
down the server if the output is suspended. Doesn't use
history file.
-r, --raw Write fields without conversion. Used with --batch.
--reconnect Reconnect if the connection is lost. Disable with
--disable-reconnect. This option is enabled by default.
-s, --silent Be more silent. Print results with a tab as separator,
each row on new line.
-S, --socket=name Socket file to use for connection.
-t, --table Output in table format.
-T, --debug-info Print some debug info at exit.
--tee=name Append everything into outfile. See interactive help (\h)
also. Does not work in batch mode. Disable with
--disable-tee. This option is disabled by default.
--no-tee Disable outfile. See interactive help (\h) also. WARNING:
option deprecated; use --disable-tee instead
-u, --user=name User for login if not current user.
-U, --safe-updates Only allow UPDATE and DELETE that uses keys.
-U, --i-am-a-dummy Synonym for option --safe-updates, -U.
-v, --verbose Write more. (-v -v -v gives the table output format).
-V, --version Output version information and exit.
-w, --wait Wait and retry if connection is down.
--connect_timeout=# Number of seconds before connection timeout.
--max_allowed_packet=#
Max packet length to send to, or receive from server
--net_buffer_length=#
Buffer for TCP/IP and socket communication
--select_limit=# Automatic limit for SELECT when using --safe-updates
--max_join_size=# Automatic limit for rows in a join when using
--safe-updates
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol
--show-warnings Show warnings after every statement.

Default options are read from the following files in the given order:
/etc/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
The following groups are read: mysql 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 options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
auto-rehash FALSE
character-sets-dir (No default value)
default-character-set latin1
comments FALSE
compress FALSE
database (No default value)
delimiter ;
vertical FALSE
force FALSE
named-commands FALSE
ignore-spaces FALSE
local-infile FALSE
no-beep FALSE
host (No default value)
html FALSE
xml FALSE
line-numbers TRUE
unbuffered FALSE
column-names TRUE
sigint-ignore FALSE
port 3306
prompt mysql>
quick FALSE
raw FALSE
reconnect FALSE
socket /tmp/mysql.sock
table FALSE
debug-info FALSE
user root
safe-updates FALSE
i-am-a-dummy FALSE
connect_timeout 0
max_allowed_packet 16777216
net_buffer_length 16384
select_limit 1000
max_join_size 1000000
secure-auth FALSE
show-warnings FALSE

但是还是没导入进去