Ubuntu18.04 安装MySQL；Ubuntu root密码修改；navicat 连接远程数据库；

一 下载安装 MySQL

下载 MySQL,创建文件存放路径：mkdir software/data/mysql

wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb

sudo apt-get update

sudo apt-get install mysql-server

baicun@ubuntu1:~/software/data/mysql$ sudo apt-get install mysql-server
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
  libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
  libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
  libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7
  mysql-server-core-5.7
0 upgraded, 21 newly installed, 0 to remove and 59 not upgraded.
Need to get 21.0 MB of archives.
After this operation, 162 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://archive.ubuntu.com/ubuntu bionic/main amd64 mysql-common all 5.8+1.0.4 [7,308 B]
Get:2 http://archive.ubuntu.com/ubuntu bionic/main amd64 libaio1 amd64 0.3.110-5 [6,448 B]
Get:3 http://archive.ubuntu.com/ubuntu bionic-security/main amd64 mysql-client-core-5.7 amd64 5.7.23-0ubuntu0.18.04.1 [6,985 kB]
Get:3 http://archive.ubuntu.com/ubuntu bionic-security/main amd64 mysql-client-core-5.7 amd64 5.7.23-0ubuntu0.18.04.1 [6,985 kB]                                                         
Get:4 http://archive.ubuntu.com/ubuntu bionic-security/main amd64 mysql-client-5.7 amd64 5.7.23-0ubuntu0.18.04.1 [2,312 kB]                                                              
Get:5 http://archive.ubuntu.com/ubuntu bionic-security/main amd64 mysql-server-core-5.7 amd64 5.7.23-0ubuntu0.18.04.1 [7,777 kB]                                                         
Ign:5 http://archive.ubuntu.com/ubuntu bionic-security/main amd64 mysql-server-core-5.7 amd64 5.7.23-0ubuntu0.18.04.1                                                                    
Get:6 http://archive.ubuntu.com/ubuntu bionic/main amd64 libevent-core-2.1-6 amd64 2.1.8-stable-4build1 [85.9 kB]
Get:7 http://archive.ubuntu.com/ubuntu bionic-security/main amd64 mysql-server-5.7 amd64 5.7.23-0ubuntu0.18.04.1 [3,194 kB]                                                              
Get:8 http://archive.ubuntu.com/ubuntu bionic/main amd64 libhtml-tagset-perl all 3.20-3 [12.1 kB]                                                                                        
Get:9 http://archive.ubuntu.com/ubuntu bionic/main amd64 liburi-perl all 1.73-1 [77.2 kB]                                                                                                
Get:10 http://archive.ubuntu.com/ubuntu bionic/main amd64 libhtml-parser-perl amd64 3.72-3build1 [85.9 kB]                                                                               
Get:11 http://archive.ubuntu.com/ubuntu bionic/main amd64 libcgi-pm-perl all 4.38-1 [185 kB]                                                                                             
Get:12 http://archive.ubuntu.com/ubuntu bionic/main amd64 libfcgi-perl amd64 0.78-2build1 [32.8 kB]                                                                                      
Get:13 http://archive.ubuntu.com/ubuntu bionic/main amd64 libcgi-fast-perl all 1:2.13-1 [9,940 B]                                                                                        
Get:14 http://archive.ubuntu.com/ubuntu bionic/main amd64 libencode-locale-perl all 1.05-1 [12.3 kB]                                                                                     
Get:15 http://archive.ubuntu.com/ubuntu bionic/main amd64 libhtml-template-perl all 2.97-1 [59.0 kB]                                                                                     
Get:16 http://archive.ubuntu.com/ubuntu bionic/main amd64 libtimedate-perl all 2.3000-2 [37.5 kB]                                                                                        
Get:17 http://archive.ubuntu.com/ubuntu bionic/main amd64 libhttp-date-perl all 6.02-1 [10.4 kB]                                                                                         
Get:18 http://archive.ubuntu.com/ubuntu bionic/main amd64 libio-html-perl all 1.001-1 [14.9 kB]                                                                                          
Get:19 http://archive.ubuntu.com/ubuntu bionic/main amd64 liblwp-mediatypes-perl all 6.02-1 [21.7 kB]                                                                                    
Get:20 http://archive.ubuntu.com/ubuntu bionic/main amd64 libhttp-message-perl all 6.14-1 [72.1 kB]                                                                                      
Get:21 http://archive.ubuntu.com/ubuntu bionic-security/main amd64 mysql-server all 5.7.23-0ubuntu0.18.04.1 [9,940 B]                                                                    
Get:5 http://archive.ubuntu.com/ubuntu bionic-security/main amd64 mysql-server-core-5.7 amd64 5.7.23-0ubuntu0.18.04.1 [7,777 kB]                                                         
Fetched 9,061 kB in 8min 11s (18.4 kB/s)                                                                                                                                                 
Preconfiguring packages ...
Selecting previously unselected package mysql-common.
(Reading database ... 102189 files and directories currently installed.)
Preparing to unpack .../0-mysql-common_5.8+1.0.4_all.deb ...
Unpacking mysql-common (5.8+1.0.4) ...
Selecting previously unselected package libaio1:amd64.
Preparing to unpack .../1-libaio1_0.3.110-5_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-5) ...
Selecting previously unselected package mysql-client-core-5.7.
Preparing to unpack .../2-mysql-client-core-5.7_5.7.23-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-client-core-5.7 (5.7.23-0ubuntu0.18.04.1) ...
Selecting previously unselected package mysql-client-5.7.
Preparing to unpack .../3-mysql-client-5.7_5.7.23-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-client-5.7 (5.7.23-0ubuntu0.18.04.1) ...
Selecting previously unselected package mysql-server-core-5.7.
Preparing to unpack .../4-mysql-server-core-5.7_5.7.23-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-server-core-5.7 (5.7.23-0ubuntu0.18.04.1) ...
Selecting previously unselected package libevent-core-2.1-6:amd64.
Preparing to unpack .../5-libevent-core-2.1-6_2.1.8-stable-4build1_amd64.deb ...
Unpacking libevent-core-2.1-6:amd64 (2.1.8-stable-4build1) ...
Setting up mysql-common (5.8+1.0.4) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Selecting previously unselected package mysql-server-5.7.
(Reading database ... 102357 files and directories currently installed.)
Preparing to unpack .../00-mysql-server-5.7_5.7.23-0ubuntu0.18.04.1_amd64.deb ...
Unpacking mysql-server-5.7 (5.7.23-0ubuntu0.18.04.1) ...
Selecting previously unselected package libhtml-tagset-perl.
Preparing to unpack .../01-libhtml-tagset-perl_3.20-3_all.deb ...
Unpacking libhtml-tagset-perl (3.20-3) ...
Selecting previously unselected package liburi-perl.
Preparing to unpack .../02-liburi-perl_1.73-1_all.deb ...
Unpacking liburi-perl (1.73-1) ...
Selecting previously unselected package libhtml-parser-perl.
Preparing to unpack .../03-libhtml-parser-perl_3.72-3build1_amd64.deb ...
Unpacking libhtml-parser-perl (3.72-3build1) ...
Selecting previously unselected package libcgi-pm-perl.
Preparing to unpack .../04-libcgi-pm-perl_4.38-1_all.deb ...
Unpacking libcgi-pm-perl (4.38-1) ...
Selecting previously unselected package libfcgi-perl.
Preparing to unpack .../05-libfcgi-perl_0.78-2build1_amd64.deb ...
Unpacking libfcgi-perl (0.78-2build1) ...
Selecting previously unselected package libcgi-fast-perl.
Preparing to unpack .../06-libcgi-fast-perl_1%3a2.13-1_all.deb ...
Unpacking libcgi-fast-perl (1:2.13-1) ...
Selecting previously unselected package libencode-locale-perl.
Preparing to unpack .../07-libencode-locale-perl_1.05-1_all.deb ...
Unpacking libencode-locale-perl (1.05-1) ...
Selecting previously unselected package libhtml-template-perl.
Preparing to unpack .../08-libhtml-template-perl_2.97-1_all.deb ...
Unpacking libhtml-template-perl (2.97-1) ...
Selecting previously unselected package libtimedate-perl.
Preparing to unpack .../09-libtimedate-perl_2.3000-2_all.deb ...
Unpacking libtimedate-perl (2.3000-2) ...
Selecting previously unselected package libhttp-date-perl.
Preparing to unpack .../10-libhttp-date-perl_6.02-1_all.deb ...
Unpacking libhttp-date-perl (6.02-1) ...
Selecting previously unselected package libio-html-perl.
Preparing to unpack .../11-libio-html-perl_1.001-1_all.deb ...
Unpacking libio-html-perl (1.001-1) ...
Selecting previously unselected package liblwp-mediatypes-perl.
Preparing to unpack .../12-liblwp-mediatypes-perl_6.02-1_all.deb ...
Unpacking liblwp-mediatypes-perl (6.02-1) ...
Selecting previously unselected package libhttp-message-perl.
Preparing to unpack .../13-libhttp-message-perl_6.14-1_all.deb ...
Unpacking libhttp-message-perl (6.14-1) ...
Selecting previously unselected package mysql-server.
Preparing to unpack .../14-mysql-server_5.7.23-0ubuntu0.18.04.1_all.deb ...
Unpacking mysql-server (5.7.23-0ubuntu0.18.04.1) ...
Setting up libhtml-tagset-perl (3.20-3) ...
Setting up libevent-core-2.1-6:amd64 (2.1.8-stable-4build1) ...
Processing triggers for ureadahead (0.100.0-20) ...
Setting up libencode-locale-perl (1.05-1) ...
Setting up libtimedate-perl (2.3000-2) ...
Setting up libio-html-perl (1.001-1) ...
Setting up liblwp-mediatypes-perl (6.02-1) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...
Setting up libaio1:amd64 (0.3.110-5) ...
Setting up liburi-perl (1.73-1) ...
Processing triggers for systemd (237-3ubuntu10.3) ...
Setting up libhtml-parser-perl (3.72-3build1) ...
Setting up libcgi-pm-perl (4.38-1) ...
Processing triggers for man-db (2.8.3-2) ...
Setting up mysql-client-core-5.7 (5.7.23-0ubuntu0.18.04.1) ...
Setting up libfcgi-perl (0.78-2build1) ...
Setting up libhttp-date-perl (6.02-1) ...
Setting up libhtml-template-perl (2.97-1) ...
Setting up mysql-server-core-5.7 (5.7.23-0ubuntu0.18.04.1) ...
Setting up libcgi-fast-perl (1:2.13-1) ...
Setting up libhttp-message-perl (6.14-1) ...
Setting up mysql-client-5.7 (5.7.23-0ubuntu0.18.04.1) ...
Setting up mysql-server-5.7 (5.7.23-0ubuntu0.18.04.1) ...
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Renaming removed key_buffer and myisam-recover options (if present)
Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.
Setting up mysql-server (5.7.23-0ubuntu0.18.04.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...
Processing triggers for ureadahead (0.100.0-20) ...
Processing triggers for systemd (237-3ubuntu10.3) ...

安装更新MySQL服务以后，启动MySQL服务：

service mysql start

# 重启
service mysql restart

baicun@ubuntu1:~/software/data/mysql$ service mysql start
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to start 'mysql.service'.
Authenticating as: root (baicun)
Password: 
==== AUTHENTICATION COMPLETE ===

查看服务是否启动：

sudo netstat -tap | grep mysql

baicun@ubuntu1:~/software/data/mysql$ sudo netstat -tap | grep mysql 
[sudo] password for baicun: 
tcp        0      0 localhost.localdo:mysql 0.0.0.0:*               LISTEN      3910/mysqld

还可以通过查看状态信息确认： Active(running) 表示正在运行。

baicun@ubuntu1:~/software/data/mysql$ service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Sun 2018-09-23 14:24:21 UTC; 30min ago
  Process: 3908 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
  Process: 3886 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 3910 (mysqld)
    Tasks: 28 (limit: 1084)
   CGroup: /system.slice/mysql.service
           └─3910 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Sep 23 14:24:20 ubuntu1 systemd[1]: Starting MySQL Community Server...
Sep 23 14:24:21 ubuntu1 systemd[1]: Started MySQL Community Server.
baicun@ubuntu1:~/software/data/mysql$ 

连接数据库：

sudo mysql -u root -p

注意：mysql 5.7以后，该条命令需要加上 sudo

否则提示：ERROR 1698 (28000): Access denied for user 'root'@'localhost'

# 查看当前的数据库

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

二 navicat 连接远程数据库

继续百度，结果有人说首先要关闭防火墙，并且要看看3306端口是否可用，，，

baicun@ubuntu1:~/software/data/mysql$ sudo ufw status   # 开启防火墙
Status: active                                          # active ：开启
baicun@ubuntu1:~/software/data/mysql$ sudo ufw status   # 查看防火墙状态
Status: active
baicun@ubuntu1:~/software/data/mysql$ sudo ufw disable  # 关闭防火墙
Firewall stopped and disabled on system startup
baicun@ubuntu1:~/software/data/mysql$ sudo ufw status   
Status: inactive                                        # inactive ：关闭
baicun@ubuntu1:~/software/data/mysql$ 

# 查看 3306 端口使用情况（如果3306前ip是127.0.0.1，说明，3306端口只能本机ip使用）
baicun@ubuntu1:/etc/mysql/mysql.conf.d$ netstat -an|grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN  

怎么办呢？找到mysql配置文件看一看

cd /etc/mysql/mysql.conf.d
more mysqld.cnf

# ********************************摘取部分代码******************************
[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
# ********************************摘取部分代码******************************

端口默认绑定本机ip:127.0.0.1,修改配置文件 vi  mysqld.cnf

# 修改前
bind-address          = 127.0.0.1
# 修改后
# bind-address          = 127.0.0.1

呃呃，只能高权修改配置文件，好吧，切换到root

# 安装ubuntu系统时，并没有提示设置初始密码，使用如下命令设置
baicun@ubuntu1:/etc/mysql/mysql.conf.d$ sudo passwd   
# 想要设置的 root 用户的密码
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
baicun@ubuntu1:/etc/mysql/mysql.conf.d$ su
Password: 
root@ubuntu1:/etc/mysql/mysql.conf.d# vi mysqld.cnf
# 注意 $ 表示一般用户；# 表示root用户

修改保存以后：查看是否修改正确。

注意：修改完成以后，一定要重启MySQL服务，否则配置文件不生效。

然后进入mysql，对远程用户进行赋权，

# 下面俩个'root'，第一个是用户，@'%'表示任意主机，第二个是密码
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)

navicat 做连接测试：

终于搞定！

三 其他命令

# 关闭 MySQL 服务
service mysql stop 
# 退出数据库
> quit 或是 exit