欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Ubuntu MySQL远程登录_MySQL

程序员文章站 2024-02-04 14:51:52
...
Ubuntu

引言

只道成功方法,不述失败经历。折腾了3天的MySQL远程登录,终于告一段落了。。。

MySQL远程登录

远程登录命令

mysql -h ip(or ipv6) -u username -p userpassword

如果把-h ip省略,MySQL将默认本地登录,即

mysql -u username -p userpassword

等价于

mysql -h localhost -u username -p userpassword

初次远程登录登录情形如下:

Ubuntu MySQL远程登录_MySQL

出现错误,无法连接远程的MySQL数据库!

查看一下用户是否允许远程登录MySQL

Ubuntu MySQL远程登录_MySQL

(其中%、127.0.0.1和localhost表明对应用户可以本地登录MySQL,linux是一个自定义的host,解析的ip为127.0.0.1,::1是一个ipv6的地址,指向本机)

由图可知jinqiang用户可以远程登录MySQL,那为什么依然不能远程登录呢?

问题出在MySQL的配置文件上

~cat /etc/mysql/my.cnf | grep "^[^#]" -n19:[client]20:port		= 330621:socket		= /var/run/mysqld/mysqld.sock27:[mysqld_safe]28:socket		= /var/run/mysqld/mysqld.sock29:nice		= 031:[mysqld]35:user		= mysql36:pid-file	= /var/run/mysqld/mysqld.pid37:socket		= /var/run/mysqld/mysqld.sock38:port		= 330639:basedir		= /usr40:datadir		= /var/lib/mysql41:tmpdir		= /tmp42:lc-messages-dir	= /usr/share/mysql43:skip-external-locking47:bind-address		= 127.0.0.151:key_buffer		= 16M52:max_allowed_packet	= 16M53:thread_stack		= 192K54:thread_cache_size = 857:myisam-recover = BACKUP64:query_cache_limit	= 1M65:query_cache_size= 16M77:log_error = /var/log/mysql/error.log89:expire_logs_days	= 1090:max_binlog_size = 100M112:[mysqldump]113:quick114:quote-names115:max_allowed_packet	= 16M117:[mysql]120:[isamchk]121:key_buffer		= 16M127:!includedir /etc/mysql/conf.d/~

打开/etc/mysql/my.cnf

sudo vi /etc/mysql/my.cnf

将上图
47:bind-address = 127.0.0.1
注释掉
#bind-address = 127.0.0.1
或者改成
bind-address = 192.168.81.93
然后重启MySQL

sudo service mysql restart

再次尝试远程登录

Ubuntu MySQL远程登录_MySQL

登录成功。

C#远程连接MySQL

关键代码(环境为windows)

private void buttonTest_Click(object sender, EventArgs e)		{			string constr = "host=192.168.81.93;user=jinqiang;password=123456;database=test;";			MySqlConnection mycon = new MySqlConnection(constr);			mycon.Open();			MySqlCommand mycmd = new MySqlCommand("insert into employee(id,name,password,email) values(1,'jinqiang','123456789','hellojinqiang@gmail.com')", mycon);			if (mycmd.ExecuteNonQuery() > 0)			{				labelTest.Text = "数据插入成功!";			}			else			{				labelTest.Text = "数据插入失败!";			}			mycon.Close(); 		}

PS:
C#连接MySql测试代码下载:http://yunpan.cn/QN7dHwqYPV8i4 (提取码:b3a1)
除此之外,你需要安装MySQL Connector Net:

http://dev.mysql.com/downloads/connector/net/

查看结果

Ubuntu MySQL远程登录_MySQL

(完)