ubuntu server配置mysql并實(shí)現(xiàn)遠(yuǎn)程連接的操作方法

176次閱讀

共計(jì) 3405 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

自動(dòng)寫代碼機(jī)器人，免費(fèi)開通

服務(wù)器：ubuntu server 16.04 LSS

客戶機(jī)：ubuntu 16.04 LTS

服務(wù)器配置

服務(wù)器安裝 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

修改遠(yuǎn)程連接配置文件

# 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

設(shè)置服務(wù)器數(shù)據(jù)庫字符為 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)

新建遠(yuǎn)程登錄用戶并授權(quán)

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

關(guān)于授權(quán)：

命令：GRANT privileges ON databasename.tablename TO ‘username’@’host’

說明：privileges- 用戶的操作權(quán)限，如 select，insert,update 等，如果要授予所有權(quán)則使用 all

如果要授予該用戶對所有數(shù)據(jù)庫和表的操作權(quán)限則用 * 表示，如 *.*

例如：

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

但是用這些命令授權(quán)的用戶不能再給其他用戶授權(quán)，如果要讓該用戶有權(quán)限，則使用

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

重啟服務(wù)器

# 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 服務(wù)器

# 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

關(guān)于直接用 root 用戶連接報(bào)錯(cuò)問題

# 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 用戶登錄，則會(huì)報(bào)錯(cuò)，可修改 root 密碼解決該問題
mysql SET PASSWORD FOR 'root'@'%' = PASSWORD("123456");

以上這篇 ubuntu server 配置 mysql 并實(shí)現(xiàn)遠(yuǎn)程連接的操作方法就是丸趣 TV 小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持丸趣 TV。

向 AI 問一下細(xì)節(jié)

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！

正文完