在Ubuntu上安装MySQL

安装mysql并启动

1.升级软件包
root@my-server:~# sudo apt-get update
2.执行安装命令
root@my-server:~# sudo apt-get install mysql-server
3.启动数据库
root@my-server:~# sudo service mysql start
4.查看数据库状态
root@my-server:~# sudo service mysql status
5.设置开启启动
root@my-server:~# sudo systemctl enable mysql

设置mysql登录权限

1.登录数据库
root@my-server:~# mysql -u root -p
新安装mysql第一次登录不需要密码，直接回车便可登录
2.设置root密码

mysql> use mysql;
 
mysql> select host, user, authentication_string from user;
 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'XXXXX';
 
mysql> quit

root@my-server:~# mysql -u root -p your_password

3.设置远程登录

mysql> use mysql;
 
mysql> update user set host = '%' where user = 'root' and host = 'localhost';

4.修改配置文件
编辑mysql配置文件注释掉bind-address、mysqlx-bind-address配置

root@my-server:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf

5.重启

root@my-server:~# service mysql restart

6.防火墙开放3306
root@my-server:~# sudo ufw allow 3306/tcp

创建用户并授权

1.新建mysql用户

mysql> create user 'user_demo'@'%' identified with mysql_native_password by 'your_password';
 
mysql> flush privileges;

2.新建数据库以及授权

mysql> create database my_demo_database;
 # 授权my_demo_database给user_demo用户
mysql> grant all privileges on my_demo_database.* to 'user_demo'@'%';
 # 授权所有数据库给user_demo用户
mysql> grant all privileges on *.* TO 'user_demo'@'%';
  
mysql> flush privileges;

修改MySQL的数据路径

1.查看当前的数据路径

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

2.创建新的路径并赋权
root@my-server:~# mkdir -p /data/mysql
root@my-server:/data/mysql# cp -R /var/lib/mysql/* /data/mysql
root@my-server:/data/mysql# chmod -R 777 /data
root@my-server:/data/mysql# chown -R mysql:mysql /data/mysql
3.关闭数据库服务
root@my-server:/data/mysql# service mysql stop
4.修改mysql配置文件
root@my-server:/data/mysql# vim /etc/mysql/mysql.conf.d/mysqld.cnf
datadir = /data/mysql

5.修改安全保护文件

root@my-server:/data/mysql# vim /etc/apparmor.d/usr.sbin.mysqld

#Allow data dir access
/data/mysql/ r,
/data/mysql/** rwk,

6.mysql访问控制文件
root@my-server:/data/mysql# vim /etc/apparmor.d/abstractions/mysql

/data/mysql{,d}/mysql{,d}.sock rw,

7.重新加载 AppArmor 配置
root@my-server:~# sudo systemctl reload apparmor
8.重启服务
root@my-server:~# sudo service mysql restart