docker mysql开启 log_bin日志

【mysql5.7.x默认没有开启】docker mysql开启 log_bin日志（mysql5.7.x）

# mysql ---> 为mysql的docker容器的name，你也可以使用mysql的docker容器的id

# 开启log_bin的docker容器配置
docker exec mysql bash -c "echo 'server-id=1' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'sync_binlog=1' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'log-bin=/var/lib/mysql/mysql-bin' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'binlog_cache_size=256M' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'binlog_format=mixed' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'binlog-ignore-db=mysql,information_schema,performance_schema,sys' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'lower_case_table_names=1' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'character-set-server=utf8' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'collation-server=utf8_general_ci' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
docker exec mysql bash -c "echo 'sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' >> /etc/mysql/mysql.conf.d/mysqld.cnf"

#重启mysql容器
docker restart mysql

查看效果：

show master status;
show variables like 'sync_binlog'
show variables like 'server_id'
show variables like '%log_bin%'
show variables like 'binlog_cache_size'
show variables like 'binlog_format'
show variables like 'lower_case_table_names'
SELECT @@session.character_set_server
SELECT @@session.collation_server
SELECT @@session.sql_mode

---

【mysql8默认已经开启】 docker mysql开启 log_bin日志（mysql8.x）
建议：运行时修改配置参数！

查看效果：

show master status;
show variables like 'sync_binlog'
show variables like 'server_id'
show variables like '%log_bin%'
show variables like 'binlog_cache_size'
show variables like 'binlog_format'
show variables like 'lower_case_table_names'
SELECT @@session.character_set_server
SELECT @@session.collation_server
SELECT @@session.sql_mode

补充：安装mysql8
建议：运行时修改配置参数！

version: "3.9"
services:
 #指定服务名称mysql
 mysql:
  image: mysql:8.0.31
  #指定容器名称
  container_name: mysql8031
  #restart: always
  #指定服务运行的端口
  ports :
   - "3306:3306"
  #指定容器的环境变量
  environment:
   #数据库root密码的密码root
   - MYSQL_ROOT_PASSWORD=root
   #创建的库
   - MYSQL_DATABASE=test_demo
   #允许多IP连接数据库
   - MYSQL_ROOT_HOST=%
  #挂载数据卷
  volumes:
   - /opt/docker/mysql/conf/my.cnf:/etc/mysql
   - /opt/docker/mysql/data:/var/lib/mysql
   - /opt/docker/mysql/logs:/var/log/mysql
  command: ['--server-id=1',
              '--sync_binlog=1',
              '--log-bin=mysql8-bin',
              '--binlog-ignore-db=mysql,information_schema,performance_schema,sys',
              '--binlog_cache_size=256M',
              '--binlog_format=mixed',
              '--relay_log=mysql8-relay',
              '--lower_case_table_names=1',
              '--character-set-server=utf8mb4',
              '--collation-server=utf8mb4_general_ci',
              '--sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION']

---

主从同步参见：https://blog.csdn.net/HSJ0170/article/details/128643644