一.安装MYSQL
查找MySQL镜像：
docker search mysql
下载MySQL镜像
docker pull mysql:latest
运行MySQL容器:
docker run -d --name mysql8 --restart=unless-stopped -p 3306:3306 -e MYSQL_ROOT_PASSWORD=密码-e MYSQL_USER=用户 -e MYSQL_PASSWORD=用户密码 -e MYSQL_DATABASE=db -v /data/mysql8/data:/var/lib/mysql -v /data/mysql8/conf/my.cnf:/etc/mysql/conf.d/my.cnf:ro -v /data/mysql8/logs:/var/log/mysql -v /data/mysql8/backup:/backup mysql:latest --lower-case-table-names=1
参数说明
-p 3306:3306：将容器的3306端口映射到主机的3306端口
--restart=unless-stopped 服务器重启后自启
-v 数据/配置/日志/备份全部持久化
--lower-case-table-names=1 Windows→Linux 迁移常用，必须在初始化前指定，后期改不了
-e MYSQL_ROOT_PASSWORD=密码：初始化root用户的密码
--lower_case_table_names=1 设置表名参数名等忽略大小写
记得定期把 /data/mysql8/backup 目录异地同步
二. MySQL远程访问
查看容器
docker ps -a
进入容器
docker exec -it <容器名或容器ID> bash
进入mysql
root@7e8060b03125:/# mysql -u root -p # 登录mysql服务器
# 在这里输入mysql密码:XiaoMeng666
mysql> show databases; # 查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.09 sec)
mysql> select host, user,plugin,authentication_string from mysql.user;
+-----------+---------------+-----------------------+-------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+---------------+-----------------------+-------------------------------------------+
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | mysql_native_password | *50208BAA1E29F372145D89CC187279F75FBEC2CC |
+-----------+---------------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
# 备注：host为 % 表示不限制ip
# localhost表示本机使用
# plugin 非mysql_native_password则需要修改密码
也有可能需要
mysql> update mysql.user set host='%' where user='root'; # 更新 root的 远程登录为所有
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host, user,plugin,authentication_string from mysql.user;
+-----------+---------------+-----------------------+-------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+---------------+-----------------------+-------------------------------------------+
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | mysql_native_password | *50208BAA1E29F372145D89CC187279F75FBEC2CC |
+-----------+---------------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
# 备注：host为 % 表示不限制ip localhost表示本机使用 plugin非mysql_native_password 则需要修改密码
mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.03 sec)
但是还报错了,报错内容不一样了:
1045 Access denied for user 'root'@'192.168.31.43' (usingpassword:YES)
解决方法如下:
mysql> grant all privileges on *.* to root@'%' identified by '123456'; # 给用户授权
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
刷新授权
flush privileges;
退出
exit退出mysql
exit
exit退出容器的bash
exit
max_allowed_packet
写入MySQL报错超出 max_allowed_packet 的问题。
MySQL会根据配置文件会限制server接受的数据包的大小。如果写入大数据时，因为默认的配置太小，插入和更新操作会因为 max_allowed_packet 参数限制，而导致失败。
查看当前配置：
mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)
也可以用select查看：
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 4194304 |
+----------------------+
1 row in set (0.00 sec)
mysql>
max_allowed_packet 如果不设置，默认值在不同的 MySQL 版本表现不同，有的版本默认1M，有的版本默认4M。
修改方法1(配置文件持久化修改)：
vim /opt/mysql/conf/my.cnf
[mysqld]
max_allowed_packet = 100M
注意：修改配置文件以后，需要重启mysql服务才能生效。
参考:https://blog.csdn.net/weixin_39456915/article/details/105230544
