docker mysql 重新设置binlog

【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/my.cnf
• /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