欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-02 08:33:21
服务器:ubuntu server 16.04 lss 客户机:ubuntu 16.04 lts 服务器配置 服务器安装mysql # eric @ use...

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

但是用这些命令授权的用户不能再给其他用户授权,如果要让该用户有权限,则使用

grant privileges on databasename.tablename to 'username'@'host' with grant option;

重启服务器

# eric @ userver in ~ [14:35:49] 
$ /etc/init.d/mysql restart 
[....] restarting mysql (via systemctl): mysql.service==== authenticating for org.freedesktop.systemd1.manage-units ===
authentication is required to restart 'mysql.service'.
authenticating as: eric,,, (eric)
password: 
==== authentication complete ===

客户端

安装mysql客户端

# eric @ ray in ~ [14:32:12] c:127
$ sudo apt install mysql-client
[sudo] password for eric: 
reading package lists... done

连接mysql服务器

# eric @ ray in ~ [14:37:13] c:1
$ mysql -h 192.168.122.58 -u eric -p #
enter password: 
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 15
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>

关于直接用root用户连接报错问题

# eric @ ray in ~ [14:35:22] c:1
$ mysql -h 192.168.122.58 -u root -p
enter password: 
error 1045 (28000): access denied for user 'root'@'192.168.122.1' (using password: yes)

#如果刚开始,直接用root用户登录,则会报错,可修改root密码解决该问题
mysql>set password for 'root'@'%' = password("123456"); 

以上这篇ubuntu server配置mysql并实现远程连接的操作方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。