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

服务器：ubuntu server 16.04 LSS

客户机：ubuntu 16.04 LTS

服务器配置

服务器安装mysql

1 2	`# 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

如果要授予该用户对所有数据库和表的操作权限则用* 表示，如 *.*

例如：

1 2	`GRANT SELECT, INSERT ON mysql.tables TO 'eric'@'%';` `GRANT ALL ON . TO 'eric'@'%';`

但是用这些命令授权的用户不能再给其他用户授权，如果要让该用户有权限，则使用

1	`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");