【Ubuntu】安装常用软件包-mysql
我的几个服务是部署在docker的同一个网络里，这样相互访问就可以通过docker容器的名字访问，比如容器A访问容器B，就可以http://B:8080/xxx 这样访问，不用关心ip是多少。
所以mysql前面文章给安装到主机里，感觉有点坑自己啊，这里卸载重来一下，这次安到docker里
主机安装mysql
sudo apt install mysql-server-8.0
安装完之后就自己启动了
ubuntu@VM-4-13-ubuntu:~$ service mysql status
● mysql.service - MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
Active: active (running) since Tue 2024-09-10 13:45:19 CST; 1 day 1h ago
Process: 2098809 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 2098818 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 1969)
Memory: 372.7M (peak: 379.7M)
CPU: 3min 28.439s
CGroup: /system.slice/mysql.service
└─2098818 /usr/sbin/mysqld
然后sudo mysql登陆进去,这里不需要密码
ubuntu@VM-4-13-ubuntu:~$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.39-0ubuntu0.24.04.2 (Ubuntu)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
我猜测原因是，linux当前用户是 root，mysql的root没密码，所以直接登录成功(因为不用root登录时候提示ubuntu这个用户不对)
ubuntu@VM-4-13-ubuntu:~$ mysql
ERROR 1045 (28000): Access denied for user 'ubuntu'@'localhost' (using password: NO)
改了root密码后，用sudo 也提示无法访问了
ubuntu@VM-4-13-ubuntu:~$ sudo mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
然后再回来说下改root密码流程，就2步 thisUpassword替换为你自己的密码
1.ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'thisUpassword';
2.FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'thisUpassword';
Query OK, 0 rows affected (0.02 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
然后exit退出，再使用客户端登录，这里就不用sudo了，直接指定用户名和密码就可以了
ubuntu@VM-4-13-ubuntu:~$ mysql -uroot -p
Enter password:
正常登录，搞定。如果你想新建其他用户的话，命令如下：
mysql> create user 'test'@'localhost' identified with mysql_native_password by '12345678';
mysql> grant all on *.* to 'test'@'localhost' with grant option;
mysql> flush privileges;
上面的localhost表示只能本地连接，如果你要远程连接的话改成 %
create user 'test'@'%' identified with mysql_native_password by '12345678';
如果已经创建好了，就更新一下属性
use mysql
update user set host='%' where user='test';
主要是这个表,改完记得刷新权限flush privileges;
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | test |
| localhost | root |
+-----------+------------------+
然后改一个配置 /etc/mysql/mysql.conf.d/mysqld.cnf 里面的这两行改成0.0.0.0，这个就不手把手教了
ubuntu@VM-4-13-ubuntu:/etc/mysql/mysql.conf.d$ cat /etc/mysql/mysql.conf.d/mysqld.cnf |grep "bind"
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
改完重启一下MySQL ，service mysql restart
然后就是防火墙了，云服务器一般会带，开放一下3306。
应该就可以了，然后附一个官方文档
主机卸载mysql
卸载比较简单，命令如下
sudo service mysql stop
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean
docker安装mysql8.0.24
1 先搞几个目录出来挂载
sudo mkdir -p /opt/docker/mysql/data
sudo mkdir -p /opt/docker/mysql/conf
sudo mkdir -p /opt/docker/mysql/logs
sudo mkdir -p /opt/docker/mysql/mysql-files
2 创建配置文件my.cnf
ubuntu@VM-4-13-ubuntu:~$ cd /opt/docker/mysql/
conf/ data/ logs/ mysql-files/
ubuntu@VM-4-13-ubuntu:~$ cd /opt/docker/mysql/conf/
ubuntu@VM-4-13-ubuntu:/opt/docker/mysql/conf$ ls
my.cnf
内容如下：
[mysql]
#设置服务器默认字符集为utf8mb4
default-character-set=utf8mb4
[mysqld]
#配置服务器的服务号，具备日后需要集群做准备
server-id = 1
#开启MySQL数据库的二进制日志，用于记录用户对数据库的操作SQL语句，具备日后需要集群做准备
log-bin=mysql-bin
#设置清理超过30天的日志，以免日志堆积造过多成服务器内存爆满
expire_logs_days=3
#允许最大的连接数
max_connections=16384
# 禁用符号链接以防止各种安全风险
symbolic-links=0
# 设置东八区时区
default-time_zone = '+8:00'
#wait_timeout参数值，由默认的8小时，修改为30分钟。
wait_timeout=1800
#修改back_log参数值:由默认的50修改为500 每个线程256K 1.6W线程2G左右
back_log=500
character_set_server=utf8mb4
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect=SET NAMES utf8mb4
3 启动docker容器
具体功能是：挂了就重启，挂载了4个目录，设置了密码(密码好像没什么用)
docker run -p 3306:3306 --name mysql --restart always -v /opt/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /opt/docker/mysql/logs:/logs -v /opt/docker/mysql/data:/var/lib/mysql -v /opt/docker/mysql/mysql-files:/var/lib/mysql-files -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.24
然后登陆进容器配置一下外部访问
docker exec -it mysql bash
然后登陆 mysql -uroot 直接回车就登陆成功了，所以说上面密码好像没什么用
然后就和前面说的配置用户密码和主机一样了，这里不重复了