Install MySql in Ubuntu and connect to the Internet

Install MySql in Ubuntu and connect to the Internet

Pure beginner tutorial.

1. Install mysql

apt-get install mysql-server mysql-client libmysqlclient15-dev  

During the installation process, you will be prompted to set a password for the root account of the database. Enter the two passwords.

2. Enter

mysql  mysql -uroot -p 

3. Reset the mysql user root password.

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "123456";

4. Create a new database proxy

create database proxy;

5. create user test with full operation permissions on the proxy Database

grant all privileges on proxy.* to test@localhost identified by "testpwd";

6. Allow the test user to log on to MySQL from any machine

grant all privileges on proxy.* to test@"%" identified by "testpwd"; 

7. log out of mysql

exit 

8. After mysql is installed, the default listening address is 127.0.0.1 and port is 3306. You can run the following command to view the listening address and port:

netstat -ntulp

9. If you are listening for 127.0.0.1, the database cannot be connected from the Internet. In this case, you can modify the listening address to 0.0.0.0:

sudo vim /etc/mysql/my.cnf

Find bind-address = 127.0.0.1, change 127.0.0.1 to 0.0.0.0, save and exit

10. Restart mysql to make the configuration take effect.

service mysql restart 

Now, you can remotely connect to the mysql database using MySql_Front or phpmyadmin. However, you can only remotely log on to the test account you just authorized. By default, the root account does not allow remote database logon. You can use either of the following methods to allow the root account to remotely log on to the database:

(1). Change the table method.

It may be that your account is not allowed to log on remotely, but only on localhost. At this time, you only need to log in to mysql on the computer of localhost, and change the "host" entry in the "user" table in the "mysql" database to "%" from "localhost"

<pre name="code" class="sql">mysql -u root -pmysql>use mysql;mysql>update user set host = '%' where user = 'root';mysql>select host, user from user;

(2). Authorization method.

For example, if you want myuser to use mypassword to connect to the mysql server from any host:

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

If you want to allow myuser to connect to the mysql server from a host whose ip address is 192.168.1.3, and use mypassword as the password

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

If you want to prevent the root account from logging on to the mysql database remotely due to security concerns:

delete from user where user = 'root' and host = '%';select host, user from user;flush privileges;