Linux study notes -- mysql in ubuntu allows remote connection

Linux Study Notes & mdash; mysql in ubuntu allows remote connection abstract: mysq Linux study Notes-mysql in ubuntu allows remote connection

Abstract: In general, mysql is installed by default for security reasons. generally, it only connects the host to mysql, while other machines cannot connect to mysql remotely. In this way, if you want to remotely operate the mysql instance on another machine, the instance will be denied and the backup will be rejected. Record how mysql supports remote connection.

I. INTRODUCTION

The environment is still the previous environment, and you can test on other machines whether mysql of the host can be remotely connected. The IP address of my host is 192.168.26.200, the mysql User is root, the password is password, enter the following command, and enter the password:

Mysql-h192.168.26.200-uroot-p

Will prompt:

ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.26.200 '(192)

The problem is that mysql on 192.168.26.200 does not allow remote configuration on the host.

II. solution 2.1: view the IP address of the machine that mysql allows to connect to on the host

A) log on to mysql and enter the following command:

                mysql  –uroot  –p

B) enter the password:

                password

C) mysql-related permissions are included in the mysql database and the specified fields in the user table are viewed.

Select user, password, host from user where user = 'root '; # The following is displayed: + ------ + ----------------------------------------- + ----------- + | user | password | host | + ------ + Upper + ----------- + | root | * Lower | localhost | + ------ + Upper + ------------- it indicates that the mysql login by the root user only allows localhost connection.

2.2 all IP addresses can be connected to the root user's mysql.

A) modify mysql under the root user, that is, you can specify that some IP addresses can be connected, or you can specify that all IP addresses can be connected.

B) execute the following statement on the permission view interface:

                grant allprivileges on *.* to 'root'@'%' identified by 'password';                flushprivileges;

If you want to specify an IP connection, you can replace the above % with the specified IP address. for example, you can specify 192.168.30.253 to connect.

                grant allprivileges on *.* to 'root'@'192.168.30.253' identified by 'password';                flushprivileges;

Modifying mysql configuration file my. cnf

Vim/etc/mysql/my. cnf bind-address = 127.0.0.1 comment out the bind-address = 127.0.0.1 line, that is, modify it to: # bind-address = 127.0.0.1

2.3 Test

Try remotely logging on to another machine:

Mysql-h192.168.26.200-uroot-ppassword # displays the following information: Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 413 Server version: 5.5.35-0ubuntu0. 12.04.2 (Ubuntu) Copyright (c) 2000,201 1, Oracle and/or itsaffiliates. all rights reserved. oracle is a registered trademark of OracleCorporation and/or its affiliates. other names may be trademarksof their respective owners. type 'help; 'or' \ H' for help. type '\ c' toclear the current input statement. mysql> The logon is successful.

3. Add 3.1 and modify the mysql encoding in window:

Modify the configuration file my. ini in the mysql installation directory. Change latin1 to utf8.

3.2window

Stop:

                       net stop mysql

Start:

                       net start mysql