+-- client-character-set.cnf
+-- docker-compose.yml
+-- docker-entrypoint-initdb.d
    +-- 01_shows_status.sh  // docker-entry_point-initdb.d実行時の文字コード確認用
    +-- 02_mysql_native_password.sql   // native_passwordパスワード追加
    +-- 03_create_db.sql    // cp932でDBを作り直す
    +-- 04_user_data.sql    // cp932文字コードで保存

docker-compose.yml

services:
  mysql:
    image: mysql:8.4.2
    container_name: mysql
    command: --mysql-native-password=ON --character-set-server=cp932 --collation-server=cp932_japanese_ci
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: testdb
    volumes:
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
      - ./client-character-set.cnf:/var/lib/mysql/.my.cnf
      - ./client-character-set.cnf:/root/.my.cnf

client-character-set.cnf

[client]
default-character-set=cp932

01_shows_status.sh

echo $HOME
mysql -uroot -ppassword -e 'status'

02_mysql_native_password.sql

ALTER USER `root`@`%` IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY 'password';

03_create_db.sql

DROP DATABASE testdb;
CREATE DATABASE testdb CHARACTER SET cp932 COLLATE cp932_japanese_ci;

04_user_data.sql　(CP932)

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET cp932 COLLATE cp932_japanese_ci,
    email VARCHAR(100) CHARACTER SET cp932 COLLATE cp932_japanese_ci
);
INSERT INTO user (name, email) VALUES ('山田太郎', 'taro@example.com');
INSERT INTO user (name, email) VALUES ('鈴木一郎', 'ichiro@example.com');

$ docker --version
Docker version 27.1.1, build 6312585
$ docker compose up -d
$ docker compose exec mysql mysql -uroot -ppassword -e 'status'
Server charcterset:    cp932
Db     charcterset:    cp932
Client charcterset:    cp932
Conn   charcterset:    cp932
$ docker compose exec mysql mysql -uroot -ppassword testdb -e 'select * from user' | iconv -f cp932
id     name      email
1      山田太郎   taro@example.com
2      鈴木一郎   ichiro@example.com

ポイント

MYSQL_DATABASEに指定していたtestdbを一度Dropし、CREATE DATABASEすることで文字コードを指定のものとして再作成
docker-entrypoint-initdb.dで実行される際に読み込むmy.cnfは/var/lib/mysql/.my.cnf
完了後のmysql cliで読み込まれるmy.cnfは/root/.my.cnfになる