当前位置 主页 > 服务器问题 > Linux/apache问题 > 最大化 缩小

    ubuntu server配置mysql并实现远程连接的操作方法

    栏目:Linux/apache问题 时间:2019-10-19 21:38

    服务器:ubuntu server 16.04 LSS

    客户机:ubuntu 16.04 LTS

    服务器配置

    服务器安装mysql

    # eric @ userver in ~ [14:00:31] 
    $ sudo apt install mysql-server install mysql-client libmysqlclient-dev
    

    检查是否成功SET PASSWORD FOR ‘pig'@'%' = PASSWORD(“123456”);

    # eric @ userver in ~ [14:10:55] 
    $ sudo netstat -tap | grep mysql
    tcp 0 0 localhost:mysql *:* LISTEN 5287/mysqld  
    

    修改远程连接配置文件

    # eric @ userver in ~ [14:16:26] 
    $ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 
    #注释掉 bind-address   = 127.0.0.1
    #bind-address   = 127.0.0.1

    设置服务器数据库字符为utf-8

    # eric @ userver in ~ [14:16:26] 
    $ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 
    #在[mysqld] 中添加:character-set-server=utf8
    [mysqld]
    #
    # * Basic Settings
    #
    user   = mysql
    pid-file  = /var/run/mysqld/mysqld.pid
    socket   = /var/run/mysqld/mysqld.sock
    port   = 3306
    basedir   = /usr
    datadir   = /var/lib/mysql
    tmpdir   = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    character-set-server=utf8 #新增加
    
    #登录mysql查看字符
    
    # eric @ userver in ~ [14:21:26]
    $ mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.20-0ubuntu0.16.04.1 (Ubuntu)
    Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    mysql> show variables like '%char%';
    +--------------------------+----------------------------+
    | Variable_name   | Value      |
    +--------------------------+----------------------------+
    | character_set_client  | utf8      |
    | character_set_connection | utf8      |
    | character_set_database | utf8      |
    | character_set_filesystem | binary      |
    | character_set_results | utf8      |
    | character_set_server  | utf8      |
    | character_set_system  | utf8      |
    | character_sets_dir  | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.00 sec)
    

    新建远程登录用户并授权

    mysql> create user 'eric'@'%' identified by 'lyd2017';
    Query OK, 0 rows affected (0.01 sec)
    mysql> grant all on *.* to 'eric'@'%';--所有权限
    Query OK, 0 rows affected (0.00 sec)
    

    关于授权:

    命令:GRANT privileges ON databasename.tablename TO 'username'@'host'

    说明:privileges-用户的操作权限,如select,insert,update 等,如果要授予所有权则使用all

    如果要授予该用户对所有数据库和表的操作权限则用* 表示,如 *.*

    例如:

    GRANT SELECT, INSERT ON mysql.tables TO 'eric'@'%';
    GRANT ALL ON *.* TO 'eric'@'%';