服务器：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 FO
服务器：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并实现远程连接的操作方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持每日运维。
