Have you tried putting single quotes around your exec? What you’re running into is the host is interpreting the variables before passing them in, and only your container has those variables in the environment.
docker-compose exec 'mysql mysqldump --single-transaction -hlocalhost -uroot -p$MYSQL_ROOT_PASSWORD $DB_NAME' > /tmp/mysqldump.sql (move the quotes to after the sql file if you intended to dump inside your container)