ubuntu server配置mysql并实现远程连接的操作方法
服务器：ubuntuserver16.04LSS
客户机：ubuntu16.04LTS
服务器配置
服务器安装mysql
#eric@userverin~[14:00:31] $sudoaptinstallmysql-serverinstallmysql-clientlibmysqlclient-dev
检查是否成功SETPASSWORDFOR‘pig'@'%'=PASSWORD(“123456”);
#eric@userverin~[14:10:55] $sudonetstat-tap|grepmysql tcp00localhost:mysql*:*LISTEN5287/mysqld
修改远程连接配置文件
#eric@userverin~[14:16:26] $sudovim/etc/mysql/mysql.conf.d/mysqld.cnf #注释掉bind-address=127.0.0.1 #bind-address=127.0.0.1
设置服务器数据库字符为utf-8
#eric@userverin~[14:16:26] $sudovim/etc/mysql/mysql.conf.d/mysqld.cnf #在[mysqld]中添加：character-set-server=utf8 [mysqld] # #*BasicSettings # 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@userverin~[14:21:26] $mysql-uroot-p Enterpassword: WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis4 Serverversion:5.7.20-0ubuntu0.16.04.1(Ubuntu) Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>showvariableslike'%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/| +--------------------------+----------------------------+ 8rowsinset(0.00sec)
新建远程登录用户并授权
mysql>createuser'eric'@'%'identifiedby'lyd2017'; QueryOK,0rowsaffected(0.01sec) mysql>grantallon*.*to'eric'@'%';--所有权限 QueryOK,0rowsaffected(0.00sec)
关于授权：
命令：GRANTprivilegesONdatabasename.tablenameTO'username'@'host'
说明：privileges-用户的操作权限，如select，insert,update等，如果要授予所有权则使用all
如果要授予该用户对所有数据库和表的操作权限则用*表示，如*.*
例如：
GRANTSELECT,INSERTONmysql.tablesTO'eric'@'%'; GRANTALLON*.*TO'eric'@'%';
但是用这些命令授权的用户不能再给其他用户授权，如果要让该用户有权限，则使用
GRANTprivilegesONdatabasename.tablenameTO'username'@'host'WITHGRANTOPTION;
重启服务器
#eric@userverin~[14:35:49] $/etc/init.d/mysqlrestart [....]Restartingmysql(viasystemctl):mysql.service====AUTHENTICATINGFORorg.freedesktop.systemd1.manage-units=== Authenticationisrequiredtorestart'mysql.service'. Authenticatingas:eric,,,(eric) Password: ====AUTHENTICATIONCOMPLETE===
客户端
安装mysql客户端
#eric@rayin~[14:32:12]C:127 $sudoaptinstallmysql-client [sudo]passwordforeric: Readingpackagelists...Done
连接mysql服务器
#eric@rayin~[14:37:13]C:1 $mysql-h192.168.122.58-ueric-p# Enterpassword: WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis15 Serverversion:5.7.20-0ubuntu0.16.04.1(Ubuntu) Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>
关于直接用root用户连接报错问题
#eric@rayin~[14:35:22]C:1
$mysql-h192.168.122.58-uroot-p
Enterpassword:
ERROR1045(28000):Accessdeniedforuser'root'@'192.168.122.1'(usingpassword:YES)
#如果刚开始，直接用root用户登录，则会报错，可修改root密码解决该问题
mysql>SETPASSWORDFOR'root'@'%'=PASSWORD("123456");
以上这篇ubuntuserver配置mysql并实现远程连接的操作方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。