Ubuntu server configures mysql and implements remote connection, using tumysql

Ubuntu server configures mysql and implements remote connection, using tumysql

Server: ubuntu server 16.04 LSS

Client: ubuntu 16.04 LTS

Server Configuration

Install mysql on the server

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

Check whether the set password for 'pig' @ '%' = PASSWORD ("123456") is successful ");

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

Modify the remote connection configuration file

# Eric @ userver in ~ [14:16:26] $ sudo vim/etc/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. conf. d/mysqld. cnf # add character-set-server = utf8 [mysqld] # * Basic Settings # user = mysqlpid-file =/var/run/mysqld in [mysqld. pidsocket =/var/run/mysqld. sockport = 3306 basedir =/usrdatadir =/var/lib/mysqltmpdir =/tmplc-messages-dir =/usr/share/mysqlskip-external-lockingcharacter-set-server = utf8 # New add # logon to mysql to view characters # 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 4 Server version: 5.7.20-0ubuntu0. 16.04.1 (Ubuntu) Copyright (c) 2000,201 7, 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 % '; + bytes + | Variable_name | Value | + bytes + | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | bytes | binary | utf8 | | character_set_server | utf8 | character_set_system | utf8 | character_sets_dir |/usr/share/mysql/charsets/| + ---------------------- + rows + 8 rows in set (0.00 sec)

Create remote login user and authorize

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)

About authorization:

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

Note: privileges-operation permissions of users, such as select, insert, and update. If you want to grant ownership, use all

If you want to grant the user the operation permission on all databases and tables, use *, for example *.*

For example:

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

However, users authorized by these commands cannot be authorized to other users. To grant permissions to this user, use

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

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

An error is reported when the root user is directly connected.

# 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) # If you log on directly with the root user at the beginning, an error is returned, you can modify the root PASSWORD to solve this problem. mysql> set password for 'root' @ '%' = PASSWORD ("123456 ");

The above ubuntu server configuration mysql and remote connection operation method is a small series to share with you all of the content, hope to give you a reference, but also hope you can support a lot of homes.