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

服务器：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'@'%';