I. Background and analysis

When the MySQL container starts, it automatically creates the necessary databases, such as MySQL, which is the official default. In practice, however, we also want MySQL to automatically create our own custom database. This paper explores this application.

If you want to start the container and mount the data directory, use the MySQL client to connect to the server, and then manually create (or import. SQL file), or directly create the container (same method as above). Because the data directory is mounted, it can be persisted. However, these methods are cumbersome and inconvenient in the deployment of the database, especially in the initial testing phase, which will frequently build the environment from scratch.

If we could automatically execute our own.sql files when the MySQL container is started, we wouldn’t need to bother, but the MySQL container startup script already takes this into account. MySQL image build script docker-entrypoint.sh (see here)

for f in /docker-entrypoint-initdb.d/*; do
    process_init_file "$f" "${mysql[@]}"
done
Copy the code

The process_init_file function is defined as follows:

process_init_file() {
    local f="$1"; shift
    local mysql=( "$@" )

    case "$f" in
        *.sh)     echo "$0: running $f"; . "$f" ;;
        *.sql)    echo "$0: running $f"; "${mysql[@]}" < "$f"; echo ;;
        *.sql.gz) echo "$0: running $f"; gunzip -c "$f" | "${mysql[@]}"; echo ;;
        *)        echo "$0: ignoring $f" ;;
    esac
    echo
}
Copy the code

/docker-entrypoint-initdb.d /docker-entrypoint-initdb.d /docker-entrypoint-initdb.d Once you understand the principle, the solution is simple.

Second, solutions

2.1 Dockerfile

As follows:

FROM mysql:5.7 COPY SQL /*. SQL /docker-entrypoint-initdb.d/Copy the code

For convenience, all.SQL files are placed in the same SQL directory as Dockerfile.

2.2 Preparing SQL Files

SQL file named test.sql, containing the following contents:

Create database 'db_test' default character set utF8 collate UTf8_general_CI; use db_test; DROP TABLE IF EXISTS 'user'; CREATE TABLE `user` ( `id` bigint(20) NOT NULL, `email` varchar(255) DEFAULT NULL, `first_name` varchar(255) DEFAULT NULL, `last_name` varchar(255) DEFAULT NULL, `username` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO 'user' (' id ', 'email', 'first_name', 'last_name', `username`) VALUES(0,'[email protected]','Late','Lee','latelee');Copy the code

The main content is to create the User table and insert the data for the test.

2.3 Creating a Mirror

docker build -t mysqltest .
Copy the code

Create the image named mysqltest, note the dot at the end, which means using the Dockerfile of the current directory.

2.4 Container Startup

Docker-comemess. yml file contains the following contents:

version: '2'
services:
  database: 
    image: mysqltest
    #image: singula/mysql
    container_name: mysql
    ports:
      - "3406:3306"
    volumes:
      - ./mysql_data:/var/lib/mysql
      - /home:/home
    environment:
      MYSQL_ROOT_PASSWORD: 123456
Copy the code

Start with the following command:

docker-compose up -d
Copy the code

Verify database table:

$docker exec -- it mysql -- bash-4.2# mysql -- uroot -- p123456 mysql> use db_test; mysql> select * from user; +----+----------------+------------+-----------+----------+ | id | email | first_name | last_name | username | +----+----------------+------------+-----------+----------+ | 0 | [email protected] | Late | Lee | latelee | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Use the following statement to change the email address:

update user set email='[email protected]' where username='latelee';
Copy the code

After exiting the container, stop the container:

docker-compose down
Copy the code

Repeat the above command to start and view the data. You can see that the email address is up to date, thus verifying that the data can be stored permanently.

Iii. Practical guidance

.sql scripts are only created when the database is first started (or when there is no database).

In the. SQL script, you can also create accounts and passwords without specifying environment variables in the docker-compose file. It has a protective effect.

To migrate the data completely, the data directory (the mysql_data directory in this article) needs to be migrated along with the docker-comemater.yml file.