Use Navicat for MySQL to connect to MySQL server

use Navicat for MySQL to connect to MySQL server

A solution to the problem that occurs when you use Navicat for MySQL to connect a MySQL server installed on a virtual machine on Ubuntu on Windows.

Navicat is a powerful MySQL database and development tool that can be used in any MySQL version of 3.21 or more, and supports most of the latest MySQL features, including triggers, stored procedures, functions, events, views, management users, and more.

Navicat MySQL is available for three platforms-Microsoft Windows, Mac OS x and Linux. It allows the user to connect to the local/remote MySQL Server, and provides useful tools such as data/fabric synchronization, import/export, backup, and reporting to assist in the process of managing the data.

If you have installed MySQL server, if you have not installed MySQL server first

1. Log in to the MySQL server, create a remote user, and give the appropriate permissions

First step: Log in to the MySQL server and create a new user.

Why do we use new users to manage this?

In the MySQL installation, the default is the root user, but the root user's default connection host is localhost or 127.0.0.1, which restricts the root user to use as a local connection; In general, it is also safer and more convenient to create new accounts.

1.1 log in to MySQL service

Mysql-u root-p

Enter after entering the password, enter after entering, enter after the terminal command line becomes mysql>

Note: After entering the MySQL interface, the command must be terminated, or the command will not come back after execution.

1.2 View Database

Display the database in MySQL

show databases;

Display all databases in MySQL and view data from the user table

1.3 then view current database account information

#接着查看当前数据库账户信息, this information is in the user table

1.4 Create a user and give permissions

According to observation, these users can only be connected by local. What if I need to make an external IP connection?

You can see that the user host display is only available locally. Now, in order for us to connect to the server database remotely, we need to create a new database account with remote connection privileges, using the following command:

#创建一个afu的用户, and give permission.

Grant all privileges on * * to [email protected] '% ' identified by ' 123456 ' with GRANT option;

Grant is the authorization command, where Afu is the user name that we connect with, "123456″ is the connection password, and the"% "general character after the user name indicates that the host operation is allowed.

# Refresh Database account permissions:

Flush privileges;

#刷新权限之后, re-query.

Select User,host from user;

1.5 Deleting a user

Delete from user where user= ' username ';

1.6 Exit Database command

#退出数据库命令

Quit

1.7 Restarting the MySQL database server

#重启mysql数据库服务器

Service MySQL Start

2. Modify the MySQL configuration file to allow the MySQL server to connect remotely 2.1 First step: Log in to Ubuntu server and view the server's IP

#查看服务器的ip

We get the IP inside Ubuntu: 192.168.1.13. Note that this IP will be assigned differently depending on the network. Users need to read them on their own command line.

2.2 Step Two: Start Navicat for MySQL and fill in the remote connection data.

The navicat that issued Windows

Click "Connect" pop-up window, follow the prompts to fill in the connection information

Confirm after entering the registration code

After obtaining the IP and fill it out, click on the connection when the error will be found. The reason is that the MySQL server has not been set up to allow external connections.

Connection test

Connection Failure Reference Map

2.3 Step three: Log in to the Ubuntu server and modify the configuration file. 2.3.1 MySQL server configuration file and view files in this directory

#进入到mysql服务器配置文件处并且查看该目录下的文件

cd/etc/mysql/mysql.conf.d/

2.3.2 Edit mysqld.cnf file

Change to the root account when changing, otherwise can not change

Switch to root account command: sudo-s, exit to normal account command: Exit

#编辑mysqld. cnf file

Vim/etc/mysql/mysql.conf.d/mysqld.cnf

There is a line in the command bind-address= 127.0.0.1, it is another step to restrict the database address access. So now we're going to go into the config file and comment out this line of command.

2.3.3 Restarting the database

This step must not be less, otherwise it will not take effect

Service MySQL Restart

2.3.4 Re-check the database

#重新查看一下数据库

Mysql-u root-p;

show databases;

Use MySQL;

Select User,host from user;

2.3.5 The effect of reconnection

After the connection.

• After the connection is successful, the current database is displayed under the connection name
• You can edit this database by double-clicking the selected database
• Next time you enter this software, you can complete the connection and edit the operation by double-clicking

2.3.6 New Database

• Right-click on the name of the connection, select "New Database", pop-up window, and follow the prompts to fill in

After clicking OK

• Right-click on the database and select "Delete Database" to complete the delete operation

Use Navicat for MySQL to connect to MySQL server