pycharm连接云端mysql
在阿里云上安装了一个mysql,打算用windows系统上面装的pycharm来操作
首先,右端有个database,点开它,点开加号
这里,general填的是mysql上面设置的密码,端口不用改了,都是3306,host也不用改,user写root,如果test connection是灰色的,点安装driver,pycharm会自己下载安装的
这里,SSH打钩,proxy host写服务器的地址,user写使用的root或者你的子用户,password是root或者子用户登录上云的密码
最后点test connection就完成了,如果防火墙申请权限,点允许
接下来是使用pycharm来操作mysql
点右上角的笔那里,打开控制台
现在我要创建一个新的数据库shop
点绿色的运行,看到下面终端显示已经创建了一个叫做shop的数据库
打开ssh窗口看看
百度经验里面的mysql和excel的互相转换,先存着https://jingyan.baidu.com/article/c910274bc36006cd361d2dfb.html
下面开始不要看了,我尝试了下面的方法都没有成功,最后想着开放数据库很不安全,就装了本地的数据库
----------------------------------------------------------------------------------------------我是一条分割线----------------------------------------------------------------------------------------------------------------
load data infile 'C:\\Users\\Desktop\\test.csv' into table `paper` fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n';
[2019-04-08 09:55:03] [08S01] Communications link failure [2019-04-08 09:55:03] The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. [2019-04-08 09:55:03] java.net.ConnectException: Connection refused: connect
1.数据库授权
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'stenci/12' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.07 sec)
2.防火墙开放3306窗口
[root@izm5e47qpndp7jfqjxvf13z etc]# firewall-cmd --zone=public --add-port=3306/tcp --permanent FirewallD is not running [root@izm5e47qpndp7jfqjxvf13z etc]# telnet 192.168.1.211 3306 bash: telnet: command not found #查看防火墙状态,发现是没有开启的(dead) [root@izm5e47qpndp7jfqjxvf13z etc]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) #开启防火墙 [root@izm5e47qpndp7jfqjxvf13z etc]# systemctl start firewalld #查看防火墙状态,已经开启 [root@izm5e47qpndp7jfqjxvf13z etc]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: active (running) since Mon 2019-04-08 10:57:52 CST; 29s ago Docs: man:firewalld(1) Main PID: 2061 (firewalld) Tasks: 2 Memory: 24.2M CGroup: /system.slice/firewalld.service └─2061 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid #开启防火墙3306窗口,success [root@izm5e47qpndp7jfqjxvf13z etc]# firewall-cmd --permanent --zone=public --add-port=3306/tcp success #重启防火墙服务 [root@izm5e47qpndp7jfqjxvf13z etc]# systemctl restart firewalld.service
https://www.cnblogs.com/kevingrace/p/5799210.html
https://blog.csdn.net/ldj0816/article/details/80270084
关闭默认firewall部署iptables
最后没有看到#SELINUX=enforcing所以没有注释
[root@izm5e47qpndp7jfqjxvf13z network-scripts]# yum install iptables-services Loaded plugins: fastestmirror Determining fastest mirrors base | 3.6 kB 00:00:00 docker-ce-stable | 3.5 kB 00:00:00 epel | 4.7 kB 00:00:00 extras | 3.4 kB 00:00:00 mysql-connectors-community | 2.5 kB 00:00:00 mysql-tools-community | 2.5 kB 00:00:00 mysql57-community | 2.5 kB 00:00:00 updates | 3.4 kB 00:00:00 (1/4): extras/7/x86_64/primary_db | 187 kB 00:00:00 (2/4): epel/x86_64/updateinfo | 990 kB 00:00:00 (3/4): updates/7/x86_64/primary_db | 3.4 MB 00:00:01 (4/4): epel/x86_64/primary_db | 6.6 MB 00:00:01 Resolving Dependencies --> Running transaction check ---> Package iptables-services.x86_64 0:1.4.21-28.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================================== Package Arch Version Repository Size ================================================================================================================================================================== Installing: iptables-services x86_64 1.4.21-28.el7 base 52 k Transaction Summary ================================================================================================================================================================== Install 1 Package Total download size: 52 k Installed size: 26 k Is this ok [y/d/N]: y Downloading packages: iptables-services-1.4.21-28.el7.x86_64.rpm | 52 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : iptables-services-1.4.21-28.el7.x86_64 1/1 Verifying : iptables-services-1.4.21-28.el7.x86_64 1/1 Installed: iptables-services.x86_64 0:1.4.21-28.el7 Complete!
首先考虑是不是3306没有开启,但是打开my.cnf后没有发现 bind-address = 127.0.0.1 这么一行,所以也不能改为 bind-address = 0.0.0.0
然后又考虑mysql驱动是不是有问题,把版本改为最早的5.1.35