remote access mysql

Because of some ulterior motives, I decided to moniter my weight changes. I value the sense of ceremony, so I planned to write an executable file to recorde my bmi index. These data can be monitered by me and others who I send this file to, but only I have acess to write and delete data. This blog post shows a way to enable remote acess of mysql under win10.

Step 0: mysql Environent

Open permisson which database can be remote acess.And create a new user for remote acess considering security condition.
code0:

use mysql;

code1:

w0: update user set host="%“ where user="root";
    #all dbs open, % means any ip. Of course you can also specify an ip
    #this code permits you to remote access mysql as root user.For security,I suggest you connect it as normal user. 
w1: create user "user_name"@"%" identified by "user_passwd";
    #create a speciall user
    grant all privileges on  *.* to "user_name"@"%";
    #grant authority to user
    #the first * is name of db,the last is name of table.
ps:
    delete from user where user="xxxx" and host ="xxx";                         #delete user
    select host,user from user;                                                 #view user information
    update user set  authentication_string ="xxxx" where user="xxx" and host="xx";
    set password for "user"@"ip" password=("口令")
    #modify user passwd
    update user set user ="new_name" where user ="old_name";                     #modify user name

code2:

flush privileges

Step 1: win10 Enviroment

code0:

show variables like "port"; #the default port is 3306,or you can see details in my.ini

step1:
Open port 3306 in the firewall and check if your ip can be ping.

Step 2: python program

Third-party library:
　　python2: mysqldb
　　python3: pymysql
　　
Additional short description:

#0.Connect mysql BMItrace
   import pymysql
   conn = pymysql.connect("127.0.0.1","user",getpass.getpass('请输入mysql服务器密码:'),"db")
#1.Get action cursor
   cursor=conn.cursor()
#2.execute sql code
   cursor.execute("sql code")
#3.Disconnect
   conn.close()

Pack python program into executable file by pyinstaller.
Now you can invite others to exercise or supervise your exercise.