My docker essays 15: MySQL automatically creates databases at startup

Background and Analysis

When the MySQL container starts up, it automatically creates some necessary databases, such as MySQL, which is the official default. However, in practice, we also need to let MySQL create our own custom database automatically. This paper explores the application occasion.

The general practice is to start the container and mount the data directory, use MySQL client to connect to the server, and then manually input sql statements to create (or import. sql files), of course, can also be created directly in the container (method ibid.). Because the data directory is mounted, it can be persisted. However, these methods are cumbersome and inconvenient in deploying databases, especially in the initial testing stage, they often build environments from scratch.

If we can automatically execute our own. sql files when the MySQL container starts, we don't need to be so troublesome. In fact, the startup script of the MySQL container has considered this situation. Build the script docker-entrypoint.sh through MySQL mirror (see Here
The following statements are found:

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

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
}

As you can see from the above statement, the script will need to execute files (such as. sh or. sql) in the / docker-entrypoint-initdb.d directory, then docker-entrypoint.sh will be executed automatically. Understanding the principle, the solution is very simple.

II. Solutions

2.1 Dockerfile

The contents are as follows:

FROM mysql:5.7
COPY sql/*.sql /docker-entrypoint-initdb.d/

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

2.2 Prepare sql files

The following is the. SQL file for testing, named test.sql, which reads as follows:

-- Establish db_test data base
create database `db_test` default character set utf8 collate utf8_general_ci;
 
use db_test;
 
-- Create user tables
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 data
INSERT INTO `user` (`id`, `email`, `first_name`, `last_name`, `username`)
VALUES(0,'li@latelee.org','Late','Lee','latelee');

The main content is to create user tables and insert test data.

2.3 Creating Mirrors

docker build -t mysqltest .

Create the image name mysqltest, and note the last dot. to indicate the use of the Docker file in the current directory.

2.4 Container Start

The docker-compose.yml file reads as follows:

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

Start with the following commands:

docker-compose up -d

Verify database tables:

$ docker exec -it mysql bash
bash-4.2# mysql -uroot -p123456
mysql> use db_test;
mysql> select * from user;
+----+----------------+------------+-----------+----------+
| id | email          | first_name | last_name | username |
+----+----------------+------------+-----------+----------+
|  0 | li@latelee.org | Late       | Lee       | latelee  |
+----+----------------+------------+-----------+----------+
1 row in set (0.00 sec)

Use the following statement to modify the mailbox address:

update user set email='cst@cststudio.com.cn' where username='latelee';

After exiting the container, stop the container:

docker-compose down

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

3. Practical guidance

sql scripts are created only at the first startup (or when there is no database), and they are created and will not be overwritten. Otherwise, the mechanism will not be formally applied in the project.

In the. sql script, you can also create accounts and passwords so that you don't need to specify environment variables in the docker-compose file. It has played a protective role.

If you want to migrate data completely, you need to migrate the data directory (mysql_data directory in this article) with the docker-compose.yml file.

Keywords: Big Data MySQL SQL Docker Database

Added by rekha on Mon, 29 Jul 2019 17:14:18 +0300

Programming VIP