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

# eric @ userver in ~ [14:00:31] $ sudo apt install mysql-server install mysql-client libmysqlclient-dev

# eric @ userver in ~ [14:10:55] $ sudo netstat -tap | grep mysqltcp 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

# eric @ userver in ~ [14:16:26] $ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf #在[mysqld] 中添加：character-set-server=utf8[mysqld]## * Basic Settings#user   = mysqlpid-file  = /var/run/mysqld/mysqld.pidsocket   = /var/run/mysqld/mysqld.sockport   = 3306basedir   = /usrdatadir   = /var/lib/mysqltmpdir   = /tmplc-messages-dir = /usr/share/mysqlskip-external-lockingcharacter-set-server=utf8 #新增加#登录mysql查看字符# eric @ userver in ~ [14:21:26]$ mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 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 ===

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

# 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 15Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql>

# eric @ ray in ~ [14:35:22] C:1$ mysql -h 192.168.122.58 -u root -pEnter 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");