Kali Linux数据库我用 Linux

本地远程连接腾讯云服务器Ubuntu的MySQL

2017-12-02  本文已影响368人  Superbsco

更多的可以参考我的博客,也在陆续更新ing
http://www.hspweb.cn/

1.安装MySQL

1.1、安装MySQL(若已安装直接跳到步骤2)

sudo apt-get install mysql-server

1.2、安装完成后登陆mysql

mysql -u root -p

1.3、登陆后查看版本

select version();

image

1.4、到此一切正常。

2.配置MySQL

2.1、用Navicat登陆MySQL。

(腾讯云Ubuntu 16.04为例)

2.2、修改/etc/mysql/mysql.conf.d/mysqld.cnf

vim /etc/mysql/mysql.conf.d/mysqld.cnf

2.3、将bind-address = 127.0.0.1更改为bind-address = 0.0.0.0

2.4、保存退出

2.5、登陆MySQL

 //先输入密码登陆
mysql -root -p
//然后选择数据库
mysql>use mysql;
//选择root的账户host改为%,上面2.3中已改地址,这一步不确定是否必要
mysql> update user set host='%' where user='root';
//授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '填写root的密码' WITH GRANT OPTION;
//更新权限
FLUSH PRIVILEGES;
//查询数据库用户
mysql>SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
//退出mysql重启mysql
/etc/init.d/mysql restart
image

2.6、若想添加新用户,不用root

//创建 test123用户,设置密码为 123456
CREATE USER test123 IDENTIFIED BY '123456';
//授权
GRANT ALL PRIVILEGES ON *.* TO 'test123'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
//更新权限
FLUSH PRIVILEGES;
//退出mysql重启mysql
/etc/init.d/mysql restart

2.7、腾讯云开放连接权限,我这里是默认开放所有端口,因为方便并且没什么重要东西就无所谓,建议只开放22,3306端口。

image

3.Navicat远程连接

image

3.1、远程连接mysql后,尝试用sql语句插入带自增主键属性的表,在略过主键插入时,虽然成功插入数据,但是会提示: [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_s
解决办法:

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

4.创建Java或JavaWeb项目访问数据库

4.1、下载mysql的jar包,点击此跳转百度云下载

4.2、附上Java连接代码(改IP账户密码就行)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class connectDB {

    public static void main(String[] args) {
        //声明Connection对象
        Connection con;
        //驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        //URL指向要访问的数据库名mydata
        String url = "jdbc:mysql://填写你的腾讯云IP:3306/mysql";
        //MySQL配置时的用户名
        String user = "root";
        //MySQL配置时的密码
        String password = "填写你的密码";
        //遍历查询结果集
        try {
            //加载驱动程序
            Class.forName(driver);
            //1.getConnection()方法,连接MySQL数据库!!
            con = DriverManager.getConnection(url,user,password);
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            //2.创建statement类对象,用来执行SQL语句!!
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "select * from user";
            //3.ResultSet类,用来存放获取的结果集!!
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("-----------------");
            System.out.println("执行结果如下所示:");  
            System.out.println("-----------------");  

            String job = null;
            String id = null;
            while(rs.next()){
                //获取stuname这列数据
                job = rs.getString("user");

                //输出结果
                System.out.println(job);
            }
            rs.close();
            con.close();
        } catch(ClassNotFoundException e) {   
            //数据库驱动类异常处理
            System.out.println("Sorry,can`t find the Driver!");   
            e.printStackTrace();   
            } catch(SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();  
            }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            System.out.println("数据库数据成功获取!!");
        }
    }

}

运行截图

image

4.3、附上JavaWeb连接代码,写在servlet一样(改IP账户密码就行)

<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Statement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
    <%
    //声明Connection对象
    Connection con;
    //驱动程序名
    String driver = "com.mysql.jdbc.Driver";
    //URL指向要访问的数据库名mydata
    String url = "jdbc:mysql://填写你腾讯云的IP/mysql";
    //MySQL配置时的用户名
    String user = "root";
    //MySQL配置时的密码
    String password = "填写你的密码";
    //遍历查询结果集
    try {
        //加载驱动程序
        Class.forName(driver);
        //1.getConnection()方法,连接MySQL数据库!!
        con = DriverManager.getConnection(url,user,password);
        if(!con.isClosed())
            out.print("Succeeded connecting to the Database!<br>");
        //2.创建statement类对象,用来执行SQL语句!!
        Statement statement = con.createStatement();
        //要执行的SQL语句
        String sql = "select * from user";
        //3.ResultSet类,用来存放获取的结果集!!
        ResultSet rs = statement.executeQuery(sql);
        out.print("-----------------<br>");
        out.print("执行结果如下所示:<br>");  
        out.print("-----------------<br>");  

        String job = null;
        String id = null;
        while(rs.next()){
            //获取stuname这列数据
            job = rs.getString("user");

            //输出结果
            out.print(job+"<br>");
        }
        rs.close();
        con.close();
    } catch(ClassNotFoundException e) {   
        //数据库驱动类异常处理
        out.print("Sorry,can`t find the Driver!<br>");   
        e.printStackTrace();   
        } catch(SQLException e) {
        //数据库连接失败异常处理
        e.printStackTrace();  
        }catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
    }finally{
        out.print("数据库数据成功获取!!");
    }
    %>
</body>
</html>

运行截图

image
上一篇下一篇

猜你喜欢

热点阅读