1.拉取镜像:
docker pull mariadb:10.5.10-focal
2.默认配置文件启动,-p 0.0.0.0:3308:3306 指定外部地址可以全部访问,端口3308
docker run -p 0.0.0.0:3308:3306 --name lyr-mariadb -e MARIADB_ROOT_PASSWORD=123456 -d mariadb:10.5.10-focal
3.默认启动成功就可以远程用root访问了
4.docker-compose安装
version: "3.9"
services:
mariadb:
image: mariadb:10.5.10-focal
container_name: demo_mariadb
restart: always
environment:
MARIADB_ROOT_PASSWORD: 123456
TZ: "Asia/Shanghai"
command:
[
"--character-set-server=utf8mb4",
"--collation-server=utf8mb4_unicode_ci",
"--default-time-zone=Asia/Shanghai"
]
ports:
- 3309:3306
volumes:
- /data/compose/mariadb/data:/var/lib/mysql
- /data/compose/mariadb/conf:/etc/mysql/conf.d
- /data/compose/mariadb/log:/var/log/mysql
若时区报错:则把时区改为+8:00
5.配置文件中开启二进制日志,docker.cnf文件,该文件未/etc/mysql/conf.d下的文件,自定义配置时将其复制到/data/compose/mariadb/conf下,到时候会自动覆盖原来的
[mysqld]
skip-host-cache
skip-name-resolve
log-bin = mysql-bin #开启二进制日志
server-id=123 #服务id,不可重复
查看时区及时间和是否开启二进制日志:
show variables like '%time_zone%';
SELECT NOW()
#on就是开启,off就是关闭
show VARIABLES like "log_bin"
#刷新二进制日志,刷新后以新的二进制进行记录
flush logs;
#查看二进制文件
show binary logs
#或
show master logs
#查看当前使用的二进制日志
show master status
#查看log_bin日志模式,默认STATEMENT
show global variables like '%binlog_format%';
模式指定开启参数如下:
#binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
#临时修改
SET GLOBAL binlog_format = 'ROW';
更多查看:MySQL binlog三种模式
下面开始添加用户和授权
添加用户:
#查询数据库的用户
SELECT host,user,password from user;
#添加用户
CREATE USER 'lyrroot'@'%' IDENTIFIED BY '123456';
#刷新
flush privileges;
#授权,授与所有数据库权限,授权时重新赋值密码会覆盖之前创建的密码,密码将改为123123
grant all privileges on *.* to lyrroot@"%" identified by '123123'
#授权时也可不指定密码,将使用原来创建用户的密码
grant all privileges on *.* to lyrroot@"%";
注意:创建用户和授权,需要使用root账户
授权某个ip段可以访问
#创建用户
CREATE USER 'test2'@'192.168.1.%' IDENTIFIED BY '123456';
#授权
grant all privileges on *.* to test2@"192.168.1.%";
创建只读testDB数据库账号
#创建用户
CREATE USER 'test3'@'%' IDENTIFIED BY '123456';
#授权
grant select on testDB.* to test3@"%";
删除用户
Delete FROM user Where User='test3' and Host='%';
修改密码
set password for test2@"192.168.1.%" = password('1234');