溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
服務器：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并實現遠程連接的操作方法就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持億速云。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
