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
浙公网安备 33010602011771号