docker 创建postgresql服务 作者: minqike 时间: 2024-06-16 分类: Docker 评论 ```shell mkdir /opt/docker/postgre15 docker run --name my_postgres \ -v /opt/docker/postgre15:/var/lib/postgresql/data \ -e POSTGRES_PASSWORD=Root123Root123 \ -p 5432:5432 \ -d postgres:15.3 ``` # 修改时区 show time zone; select * from pg_timezone_names; ## 修改配置文件/opt/docker/postgre15/postgresql.conf ``` log_timezone = 'PRC' timezone = 'PRC' ``` ```sql select now(); select datname,pg_encoding_to_char(encoding) as encoding,datcollate,datctype from pg_database; ``` ### transform_null_equals 可以设置 transform_null_equals 把 = null 翻译成 is null 避免踩坑
Docker 创建mysql服务 作者: minqike 时间: 2024-06-16 分类: Docker 评论 ## Mysql 5.7 ```shell docker pull mysql:5.7 ##拉取镜像 mkdir /opt/soft/data/mysql ##独立存放mysql的数据库位置 # mysql启动镜像 docker run --name mysql57 \ -p 3306:3306 \ -v /opt/soft/data/mysql:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ -d mysql:5.7 \ --lower_case_table_names=1 \ --character-set-server=utf8mb4 \ --collation-server=utf8mb4_unicode_ci \ --explicit_defaults_for_timestamp=true ``` ## Mysql 8 ```shell mkdir -p /opt/soft/data/mysql8/data -- 拉取 docker pull mysql:8 -- 创建 docker run -it --name=mysql8 \ -v /opt/soft/data/mysql8/data:/usr/mysql/data \ -p 3308:3306 \ -e MYSQL_ROOT_PASSWORD=root \ -d mysql:8 \ --lower_case_table_names=1 \ --character-set-server=utf8mb4 \ --collation-server=utf8mb4_unicode_ci \ --explicit_defaults_for_timestamp=true -- 进入docker docker exec -it mysql8 /bin/bash mysql -u root -proot -- 修改用户加密方式为mysql_native_password -- mysql8默认为caching_sha2_password, sqlyog13不支持 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; flush privileges; ``` ### 登录 ```shell # 登录的时候，用密码登录的话 -p 要加上， mysql -u root -p ``` ## 修改密码策略 ```shell # 查看 SHOW VARIABLES LIKE 'validate_password%'; # 修改指定密码的验证强度等级 set global validate_password.policy=LOW; # 修改指定密码长度 set global validate_password.length=4; ``` ※ 关于 Mysql 8.0.19 Homebrew 安装版本密码策略相关参数： ```bash validate_password.dictionary_file #指定密码验证的文件路径； validate_password.length #固定密码的总长度，默认为8； validate_password.mixed_case_count #整个密码中至少要包含大/小写字母的总个数； validate_password.number_count #整个密码中至少要包含阿拉伯数字的个数； validate_password.policy #指定密码的强度验证等级，默认为 MEDIUM； validate_password.special_char_count #整个密码中至少要包含特殊字符的个数； ``` ※ 指定密码的强度验证等级validate_password.policy 取值： ```bash 0/LOW #只验证长度； 1/MEDIUM #验证长度、数字、大小写、特殊字符； 2/STRONG #验证长度、数字、大小写、特殊字符、字典文件； ``` ## 其他命令 ```sql -- 创建其他用户 CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; GRANT ALL PRIVILEGES ON *.* TO 'minqike'@'%'; flush privileges; -- 查看用户权限,和密码加密方式 select user, host, plugin from mysql.user; ``` ```shell ## 重启 docker 服务 sudo systemctl daemon-reload sudo systemctl restart docker ` ## docker 开机启动 systemctl enable docker ``` ## docker 搭建mysql8 主从 * 目录结构 ```sql mysql_sync - master - datadir my.cnf - slave - datadir my.cnf ``` * master/my.cnf ```sql [mysqld] # 主数据库端ID号 server_id = 101 # 开启二进制日志 log-bin = mysql-bin # 不需要复制的数据库名 binlog-ignore-db = mysql binlog_cache_size=1M # 二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除 expire_logs_days = 7 # 将函数复制到slave log_bin_trust_function_creators = 1 binlog_format=mixed replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schema lower_case_table_names=1 character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci explicit_defaults_for_timestamp=true ``` * slave/my.cnf ``` [mysqld] server_id = 102 log-bin = mysql-bin binlog-ignore-db = mysql binlog_cache_size=1M expire_logs_days = 7 log_bin_trust_function_creators = 1 binlog_format=mixed read-only=1 replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schema lower_case_table_names=1 character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci explicit_defaults_for_timestamp=true [client] socket=/var/run/mysqld/mysqld.sock !includedir /etc/mysql/conf.d/ ``` ### 创建网络 ```sql docker network create mysql-network ``` ### 启动主节点master ```sql docker run -d --name mysql-master --network mysql-network \ -p 3306:3306 \ -v ./master/datadir:/var/lib/mysql \ -v ./master/my.cnf:/etc/mysql/my.cnf \ -e MYSQL_ROOT_PASSWORD=root \ -e MYSQL_REPLICATION_USER=root \ -e MYSQL_REPLICATION_PASSWORD=root mysql:8.0.33 ## 可以参看mysql-master的ip, 可以不用ip docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql-master ``` ### 启动从节点 ```sql docker run -d --name mysql-slave --network mysql-network \ -p 3307:3306 \ -v ./slave/datadir:/var/lib/mysql \ -v ./slave/my.cnf:/etc/mysql/my.cnf \ -e MYSQL_ROOT_PASSWORD=root \ -e MYSQL_REPLICATION_USER=root \ -e MYSQL_REPLICATION_PASSWORD=root mysql:8.0.33 ``` ### (可选)创建同步用户, 上面的docker启动的时候已经设置了MYSQL_REPLICATION_USER, 如果没有配置, 需要做下面的命令 ```sql # 进入主节点 docker exec -it mysql-slave mysql -uroot -proot # 创建用户,该用户是用来从从节点链接到主节点是的用户, 用来同步 CREATE USER 'root'@'%'; GRANT REPLICATION SLAVE ON *.* TO 'root'@'%'; FLUSH PRIVILEGES; # 显示主节点状态 mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 157 | | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ``` ### 配置从节点 ```sql # 进入从节点 docker exec -it mysql-slave mysql -uroot -proot #(可选,排错后才需要做) reset slave; # MASTER_USER 就是 链接master的同步用户, MASTER_LOG_FILE和MASTER_LOG_POS 为主节点中的 File和Position CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='root', MASTER_PASSWORD='root', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157; # 开始同步, 并查看同步状态: start slave; show slave status\G # 停止同步 stop slave; ``` ``` mysql> CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='root', MASTER_PASSWORD='root', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157; > Query OK, 0 rows affected, 8 warnings (0.02 sec) > > mysql> start slave; > Query OK, 0 rows affected, 1 warning (0.02 sec) > > mysql> show slave status\G > *************************** 1. row *************************** > Slave_IO_State: Waiting for source to send event > Master_Host: mysql-master > Master_User: root > Master_Port: 3306 > Connect_Retry: 60 > Master_Log_File: mysql-bin.000003 > Read_Master_Log_Pos: 157 > Relay_Log_File: 4a7dbfefb061-relay-bin.000002 > Relay_Log_Pos: 326 > Relay_Master_Log_File: mysql-bin.000003 > Slave_IO_Running: Yes > Slave_SQL_Running: Yes > Replicate_Do_DB: > Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema > Replicate_Do_Table: > Replicate_Ignore_Table: > Replicate_Wild_Do_Table: > Replicate_Wild_Ignore_Table: > Last_Errno: 0 > Last_Error: > Skip_Counter: 0 > Exec_Master_Log_Pos: 157 > Relay_Log_Space: 543 ```