美文网首页Kali Linux数据库我用 Linux
本地远程连接腾讯云服务器Ubuntu的MySQL

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

作者: Superbsco | 来源:发表于2017-12-02 11:43 被阅读368次

    更多的可以参考我的博客,也在陆续更新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

    相关文章

      网友评论

      • Phliexlee:我打开/etc/mysql/mysql.conf.d/mysqld.cnf后不能修改是为什么呢?
        Superbsco:@Phliexlee 没有权限,chomd -R 777 *给权限

      本文标题:本地远程连接腾讯云服务器Ubuntu的MySQL

      本文链接:https://www.haomeiwen.com/subject/lihnbxtx.html