ubuntu server Configuration of mysql and Implementation of Remote Connection Operation Method

Server: ubuntu server 16.04 LSS

Client: ubuntu 16.04 LTS

Server configuration

Server Installation mysql

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

Check for success 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  

Modify the remote connection profile

# eric @ userver in ~ [14:16:26] 
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 
# Comment out  bind-address   = 127.0.0.1
#bind-address   = 127.0.0.1

Set the server database character to utf-8

# eric @ userver in ~ [14:16:26] 
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 
# In [mysqld]  Add: 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 # New additions 

# Login mysql View Character 

# 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)

Create a new remote login user and authorize it

mysql> create user 'eric'@'%' identified by 'lyd2017';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to 'eric'@'%';-- All permissions 
Query OK, 0 rows affected (0.00 sec)

With regard to authorization:

Command: GRANT privileges ON databasename. tablename TO 'username' @ 'host'

Description: privileges-User's operation rights, such as select, insert, update, etc. If you want to grant ownership, use all

If you want to grant the user permission to operate on all databases and tables, it is indicated by *, such as *.*

For example:

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

However, users authorized with these commands can no longer authorize other users. If you want this user to have permission, use the

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

Restart the server

# 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 ===

Client

Installing the mysql client

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

Connect to the mysql server

# 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>

On the problem of reporting errors by directly using root user connection

# eric @ userver in ~ [14:10:55] 
$ sudo netstat -tap | grep mysql
tcp 0 0 localhost:mysql *:* LISTEN 5287/mysqld  

0