MySQL 从命令行访问mysql远程数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15872543/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access mysql remote database from command line
提问by user1986299
I have a server with Rackspace. I want to access the database from my local machine command line.
我有一台带有 Rackspace 的服务器。我想从我的本地机器命令行访问数据库。
I tried like:
我试过：
mysql -u username -h my.application.com -ppassword
But it gives an error:
但它给出了一个错误：
ERROR 2003 (HY000):
Can't connect to MySQL server on 'my.application.com' (10061)
错误 2003 (HY000)：
无法连接到“my.application.com”上的 MySQL 服务器 (10061)
What causes this error and how can I connect to the remote database?
导致此错误的原因是什么，如何连接到远程数据库？
回答by Venkat M
To directly login to a remote mysql console, use the below command:
要直接登录到远程 mysql 控制台，请使用以下命令：
mysql -u {username} -p'{password}' \
-h {remote server ip or name} -P {port} \
-D {DB name}
For example
例如
mysql -u root -p'root' \
-h 127.0.0.1 -P 3306 \
-D local
no space after -pas specified in the documentation
文档中指定的-p 后没有空格
It will take you to the mysql console directly by switching to the mentioned database.
它将通过切换到提到的数据库直接将您带到 mysql 控制台。
31-07-2019 - edited syntax
31-07-2019 - 编辑语法
回答by alfasin
edit my.cnf file:
编辑 my.cnf 文件：
vi /etc/my.cnf:
vi /etc/my.cnf：
make sure that:
确保：
bind-address=YOUR-SERVER-IP
bind-address=YOUR-SERVER-IP
and if you have the line:
如果你有这条线：
skip-networking
skip-networking
make sure to comment it:
确保评论它：
#skip-networking
#skip-networking
don't forget to restart:
不要忘记重新启动：
/etc/init.d/mysqld restart
/etc/init.d/mysqld restart
回答by Kedar Jangir
simply put this on terminal at ubuntu:
只需将其放在 ubuntu 的终端上：
mysql -u username -h host -p
Now hit enter
现在按回车
terminal will ask you password, enter the password and you are into database server
终端会问你密码，输入密码，你就进入了数据库服务器
回答by rajarajan2809
Try this command mysql -uuser -hhostname -PPORT -ppassword.
试试这个命令mysql -uuser -hhostname -PPORT -ppassword。
I faced a similar situation and later when mysql port for host was entered with the command, it was solved.
我遇到了类似的情况，后来用命令输入主机的mysql端口时，它就解决了。
回答by KayV
For Mac, use the following command:
对于 Mac，请使用以下命令：
mysql -u app -h hostaddress -P port -D dbname -p
and then enter the password when prompted.
然后在提示时输入密码。
回答by Cornea Valentin
If you want to not use ssh tunnel, in my.cnfor mysqld.cnfyou must change 127.0.0.1with your local ip address (192.168.1.100) in order to have access over the Lan. example bellow:
如果您不想使用 ssh 隧道，则在my.cnf或mysqld.cnf 中，您必须将127.0.0.1更改为您的本地 IP 地址（192.168.1.100）才能通过 LAN 访问。示例如下：
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Search for bind-addressin my.cnfor mysqld.cnf
在my.cnf或mysqld.cnf 中搜索bind-address
bind-address = 127.0.0.1
and change 127.0.0.1to 192.168.1.100( local ip address )
并将127.0.0.1更改为192.168.1.100（本地 IP 地址）
bind-address = 192.168.1.100
To apply the change you made, must restart mysql server using next command.
要应用您所做的更改，必须使用下一个命令重新启动 mysql 服务器。
sudo /etc/init.d/mysql restart
Modify user rootfor lan acces ( run the query's bellow in remote server that you want to have access )
修改用户root以获取 lan 访问权限（在您想要访问的远程服务器中运行查询的波纹管）
[email protected]:~$ mysql -u root -p
..
..
CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
If you want to have access only from specific ip address , change 'root'@'%'to 'root'@'( ip address or hostname)'
如果您只想从特定的 ip 地址访问，请将'root'@'%'更改为'root'@'(ip address or hostname)'
CREATE USER 'root'@'192.168.1.100' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Then you can connect:
然后你可以连接：
nobus@xray:~$ mysql -h 192.168.1.100 -u root -p
tested on ubuntu 18.04 server
在 ubuntu 18.04 服务器上测试
回答by Cornea Valentin
- try
telnet 3306. If it doesn't open connection, either there is a firewall setting or the server isn't listening (or doesn't work). - run
netstat -anon server to see if server is up. It's possible that you don't allow remote connections.
See http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
- 试试
telnet 3306。如果它没有打开连接，要么有防火墙设置，要么服务器没有监听（或不工作）。 netstat -an在服务器上运行以查看服务器是否已启动。您可能不允许远程连接。
见http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
回答by realPK
I assume you have MySQL installed on your machine. Execute the command below after filling missing details:
我假设你的机器上安装了 MySQL。填写缺失的详细信息后执行以下命令：
mysql -uUSERNAME -pPASSWORD -hHOSTNAME -P3306
回答by Amirol Ahmad
Must check whether incoming access to port 3306 is block or not by the firewall.
必须检查对端口 3306 的传入访问是否被防火墙阻止。
回答by Manish Goswami
There is simple command.
有简单的命令。
mysql -h {hostip} -P {port} -u {username} -p {database}
Example
例子
mysql -h 192.16.16.2 -P 45012 -u rockbook -p rockbookdb
