MySQL connected to remote Ubuntu server
Install mysql
# Check whether MySQL is installed:
dpkg -l | grep mysql
# Completely uninstall mysql see https://www.cnblogs.com/mjhblog/p/10499772.html
# Enter the topic: Install MySQL:
apt install mysql-server
# Check whether mysql is installed, see the following instructions, you are installed
root@huaweiyun:~# root@huaweiyun:~# service mysql restart
root@huaweiyun:~# netstat -tap | grep mysql
tcp6 00[::]:mysql [::]:* LISTEN 1466/mysqld
tcp6 00 huaweiyun:mysql 113.45.85.95:56178 ESTABLISHED 1466/mysqld
root@huaweiyun:~# dpkg -l | grep mysql
ii mysql-client-5.75.7.28-0ubuntu0.18.04.4 amd64 MySQL database client binaries
ii mysql-client-core-5.75.7.28-0ubuntu0.18.04.4 amd64 MySQL database core client binaries
ii mysql-common 5.8+1.0.4 all MySQL database common files, e.g./etc/mysql/my.cnf
ii mysql-server 5.7.28-0ubuntu0.18.04.4 all MySQL database server(metapackage depending on the latest version)
ii mysql-server-5.75.7.28-0ubuntu0.18.04.4 amd64 MySQL database server binaries and system database setup
ii mysql-server-core-5.75.7.28-0ubuntu0.18.04.4 amd64 MySQL database server binaries
Enter MySQL
mysql -u root -p
- u indicates the user name that you choose to log in, -p indicates the user password for logging in, now there is no password in the mysql database, and press Enter directly at Enter password: to enter the mysql database [no password by default]
change Password##
# Stop MySQL
sudo /etc/init.d/mysql stop
# Create mysqld file
sudo mkdir -p /var/run/mysqld
# Assign permissions to the mysqld file
sudo chown mysql:mysql /var/run/mysqld
# Enter safe mode
sudo /usr/bin/mysqld_safe --skip-grant-tables --skip-networking &
#############
root@huaweiyun:~# 2019-11-23T12:05:41.538762Z mysqld_safe Logging to syslog.2019-11-23T12:05:41.541303Z mysqld_safe Logging to '/var/log/mysql/error.log'.2019-11-23T12:05:41.557945Z mysqld_safe A mysqld process already exists
# There is nothing here by default, then enter mysql-u -p
# Not surprisingly, you will enter mysql
# Use mysql
use mysql;
# Set new password
update user set authentication_string=PASSWORD("New password") where User='root';
# Update system cache password
update user set plugin="mysql_native_password"；
# Refresh operation permissions
flush privileges;
# enter\q Exit the mysql shell, then you can log in to your database with the new password
Set to allow remote login##
# Enter the new password to enter the database
mysql -u root -p
# Use mysql database
use mysql;
# Modify remote login permissions
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
# Refresh operation permissions
flush privileges;
# enter\q exit mysql
Change configuration file##
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# Comment below command
bind-address =127.0.0.1
# Must comment this sentence Must comment this sentence Must comment this sentence
Set the database to start automatically
systemctl enable mysql.service
Development designated port##
# Install iptables, many are already installed by default, you don’t need to install it
sudo apt-get install iptables
# Release the designated port, here I release 3306
iptables -I INPUT -p tcp --dport 3306-j ACCEPT
# Save rules
iptables-save
# Install iptables-persistent guarantees that the rules will not be cleared the next time you boot
sudo apt-get install iptables-persistent
# Set persistence rules
sudo netfilter-persistent save
sudo netfilter-persistent reload
Open the security group and set the incoming direction rule##
Here I purchased a Huawei Cloud server. Find the inbound rule of your server’s security group. You can choose to release it with one click or release 3306 quickly, and then you can use tools to connect to your database.
Security group entry rules.png
Open the cmd test and find that the connection is successful
win10 test.png
Recommended Posts