Ubuntu 安装 MYsql 数据库

Ubuntu 版本：18.04 LTS

MYsql 版本：5.7.30

 

MySQL 安装

安装命令：sudo apt-get install mysql-server

sudo apt-get install mysql-client mysql-server

 

MySQL 卸载

sudo apt purge mysql-*

sudo rm -rf /etc/mysql/ /var/lib/mysql

sudo apt autoremove

 

MySQL 命令

一、基础命令：

-----命令方式-----
service mysql start		# 启动
service mysql stop		# 停止
service mysql restart		# 重启
service mysql status		# 状态


-----脚本方式-----
/etc/init.d/mysql start        # 启动
/etc/init.d/mysql stop         # 停止
/etc/init.d/mysql restart      # 重启
/etc/init.d/mysql status       # 状态

二、辅助命令：

1、查看mysql是否启动：ps -aux | grep mysqld

mysql      24329  0.0  0.0   4624  1912 ?        S    13:41   0:00 /bin/sh /usr/bin/mysqld_safe
mysql      24736  0.4  2.2 1424296 181628 ?      Sl   13:41   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --log-syslog=1 --log-syslog-facility=daemon --log-syslog-tag=
root       24959  0.0  0.0  14604  1064 pts/5    S+   13:41   0:00 grep --color=auto mysqld

2、查看mysql是否在监听端口：netstat -tl | grep mysql

root@Ubuntu:~# netstat -tl | grep mysql
tcp        0      0 localhost:mysql         0.0.0.0:*               LISTEN

3、查看是否启动：sudo netstat -tap | grep mysql

root@Ubuntu:~# sudo netstat -tap | grep mysql
tcp        0      0 localhost:mysql         0.0.0.0:*               LISTEN      4969/mysqld  

 

MySQL 密码操作

一、查看默认密码

安装完数据库如果不显示默认密码的话，可以到 /etc/mysql 目录下查看 debian.cnf 文件

命令：cat /etc/mysql/debian.cnf

root@Ubuntu:~# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = FfAHXpW5PmvDouJj                # 这里为默认密码
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = FfAHXpW5PmvDouJj
socket   = /var/run/mysqld/mysqld.sock

 

二、修改密码

这里是关键点，由于mysql5.7没有password字段，密码存储在authentication_string字段中

所以登录数据库，在mysql 中依次执行下面6个步骤的语句来修改密码

 

1、登录数据库：mysql -u root -p

root@Ubuntu:~# mysql -u root -p
Enter password: 

2、查看当前数据库：show databases;

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

3、进入 mysql 库：use mysql;

mysql> use mysql;        
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

4、修改 root账户密码为123qwe：update user set authentication_string=PASSWORD("123qwe") where user='root'; 

mysql> update user set authentication_string=PASSWORD("123qwe") where user='root';        
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

5、更新密码：update user set plugin="mysql_native_password";

mysql> update user set plugin="mysql_native_password";        
Query OK, 1 row affected (0.00 sec)
Rows matched: 4  Changed: 1  Warnings: 0

6、刷新数据库：flush privileges;

mysql> flush privileges;            
Query OK, 0 rows affected (0.00 sec)

mysql> 

 

MySQL 开启远程访问

1、编辑配置文件：vi /etc/mysql/mysql.conf.d/mysqld.cnf  有的配置文件路径或者是：vi /etc/mysql/my.cnf

找到配置文件中 bind-address 把 127.0.0.1 修改为 0.0.0.0 即可

root@Ubuntu:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0

修改完成后保存退出 :wq

重启 mysql数据库 service mysql restart

2、进入数据库：mysql -uroot -p

root@goorm:~# mysql -uroot -p
Enter password: 

3、输入远程授权命令：grant all privileges on *.* to 'root'@'% ' identified by '123qwe' with grant option;

mysql> grant all privileges on *.* to 'root'@'% ' identified by '123qwe' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

4、刷新权限：flush privileges;

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

MySQL 修改3306默认端口

编辑配置文件：vi /etc/mysql/mysql.conf.d/mysqld.cnf  有的配置文件路径或者是：vi /etc/mysql/my.cnf

找到配置文件中 port = 3306 修改其他端口即可

root@Ubuntu:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
port = 12345

修改完成后保存退出 :wq

重启 mysql数据库 service mysql restart