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.
log in to MySQL service
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.
Display the database in MySQL
Display all databases in MySQL and view data from the user table
then view current database account information
#接着查看当前数据库账户信息, this information is in the user table
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:
Select User,host from user;
1.5 Deleting a user
Delete from user where user= ' username ';
Exit Database command
Restarting the MySQL database server
Service MySQL Start
2. Modify the MySQL configuration file to allow the MySQL server to connect remotely
First step: Log in to Ubuntu server and view the server's 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.
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 Failure Reference Map
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
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
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
Select User,host from user;
2.3.5 The effect of reconnection
After the connection.
2.3.6 New Database
- 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
- 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