Install and run the mysql instance on docker

  • 2020-06-01 11:24:40
  • OfStack

I hope to simulate the construction of micro services in the company with my own machine. I deployed the mysql instance with docker, and verified the CRUD operation with spring boot. I can also try more frameworks and components in the following study.

ps: the experimental environment is: ubuntu 14.04, 64-bit

1. Get the mysql image

Pull the mysql image from docker hub's repository

sudo docker pull mysql

Look at mirror


sudo docker images

mysql latest 18f13d72f7f0 2 weeks ago 383.4 MB


2. Run an mysql container

The command to run 1 instance of mysql is as follows:

sudo docker run --name first-mysql -p 3306:3306 -e MYSQL\_ROOT\_PASSWORD=123456 -d mysql
5b6bf6f629bfe46b4c8786b555d8db1947680138b2de1f268f310a15ced7247a

The meanings of each parameter of the above command:

run runs 1 container
--name is the name of the mirror
-p 3306:3306 means using port 3306(the second) in this container to map to the port number of the native is also 3306(the first)
-d stands for running with a daemon, where the service hangs in the background

View the current running container status:

sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES5b6bf6f629bf
mysql "docker-entrypoint.sh" 32 hours ago Up 5 hours 0.0.0.0:3306->3306/tcp first-mysql

To access the mysql database, I need to install an mysql-client on my machine.

sudo apt-get install mysql-client-core-5.6

Next, we will use the mysql command to access the server. The password is 123456,192.168.95.4 as shown just now. ip, 3306 is the port occupying the physical machine as shown just now (not the port inside docker).

mysql -h192.168.95.4 -P3306 -uroot -p123456

The results of the visit are as follows:


mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+

4 rows in set (0.00 sec)

3. Run the second mysql instance

The reason for using docker relative to a virtual machine is that it consumes very few resources and can "open up" a very large number of isolated environments, so we continue to run the second instance of mysql, using the previous image, second-mysql.

sudo docker run --name second-mysql -p 3306:3307 -e MYSQL\_ROOT\_PASSWORD=123456 -d
mysql2de4ddb5bfb9b9434af8e72368631e7f4c3f83ee354157328049d7d0
f5523661docker: Error response from daemon: driver failed programming external connectivity on endpoint second-mysql (33aa29d891a1cb540de250bcbbbe9a0a41cd98f61a4e9f129a2ad5db69da4984): Bind for 0.0.0.0:3306 failed: port is already allocated.

In order to verify that the first port number is the port number of the machine, the port 3306 is still used. Then, as shown above, an error occurred, but a container id was generated. When we modify the port, an error will be reported, because the name conflicts, that is, this failed creation will occupy the name:

sudo docker run --name second-mysql -p 3307:3306 -e MYSQL\_ROOT\_PASSWORD=123456 -d
mysqldocker: Error response from daemon: Conflict. The name "/second-mysql" is already in use by container 2de4ddb5bfb9b9434af8e72368631e7f4c3f83ee354157328049d7d0f5523661. 
You have to remove (or rename) that container to be able to reuse that name..

To verify that the second-mysql container is still in the docker process, we use the command ps-a. We can observe that first-mysql is in the state of Up 34 minutes, indicating that it has been working for 34 minutes, while second-mysql has just been created.


sudo docker ps -a

CONTAINER ID    IMAGE        COMMAND         CREATED       STATUS       PORTS          NAMES
2de4ddb5bfb9    mysql        "docker-entrypoint.sh"  About a minute ago  Created                   second-mysql
5b6bf6f629bf    mysql        "docker-entrypoint.sh"  34 minutes ago    Up 34 minutes    0.0.0.0:3306->3306/tcp  first-mysql

We delete this container with the rm command, as shown below:


maintain@maintain-dev1:~$ sudo docker rm second-mysql

maintain@maintain-dev1:~$ sudo docker ps -a

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5b6bf6f629bf mysql "docker-entrypoint.sh" 42 minutes ago Up 42 minutes 0.0.0.0:3306->3306/tcp first-mysql

Re-create the second-mysql container, using port 3307 of the physical machine:


sudo docker run --name second-mysql -p 3307:3306 -e MYSQL\_ROOT\_PASSWORD=123456 -d mysql

5404fb11f29cba07b991f34056d6b40ed0888aa905a45e637e396d071bd7f331

sudo docker ps

CONTAINER ID    IMAGE        COMMAND         CREATED       STATUS       PORTS          NAMES
5404fb11f29c    mysql        "docker-entrypoint.sh"  12 seconds ago   Up 11 seconds    0.0.0.0:3307->3306/tcp  second-mysql
5b6bf6f629bf    mysql        "docker-entrypoint.sh"  43 minutes ago   Up 43 minutes    0.0.0.0:3306->3306/tcp  first-mysql

As shown in the figure above, both instances are up and running, and to access the second container, we specify port 3307 to log on to client of mysql.


mysql -h192.168.95.4 -P3307 -uroot -p123456

Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

4. jdbc test (maven & Spring boot)

Example source: http: / / spring io/guides/gs/relational - data - access /

java spring is used to operate the database. The example of spring website is used. JdbcTemplate is used to write sql without hibernate or myibatis.

The example on the spring website USES h's 21 built-in in-memory databases, so there is no trouble of configuration files being generated as objects,

To practice yaml instead of xml, use the following mysql jdbc configuration, application.yaml.


### application.yaml
### mysql config
spring:
 datasource:
  dbcp:
   driver-class-name: com.mysql.jdbc.Driver
   url: jdbc:mysql://192.168.18.129:3306/test1
   username: root
   password: 123456

I looked for a long time to automatically inject the dependency into an JdbcTemplate object, but I didn't get it done. I just like the following one, which is DataSource, and then new, which is JdbcTemlate. Finally, I used jdbcTemplate to operate the database.


@Bean
@ConfigurationProperties(prefix = "spring.datasource.dbcp")
public DataSource mysqlSource() { 
  return DataSourceBuilder.create().build();
}
@Bean
public JdbcTemplate mysqlJdbcTemplate() { 
  return new JdbcTemplate(mysqlSource());
}

Here are some CRUD operations on the database, using jdk8 for functional programming:


JdbcTemplate jdbcTemplate = mysqlJdbcTemplate();
jdbcTemplate.execute("DROP TABLE IF EXISTS customers");
jdbcTemplate.execute("CREATE TABLE customers(" + "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");
// Split up the array of whole names into an array of first/last names
List<Object[]> splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long")
 .stream() .map(name -> name.split(" ")) .collect(Collectors.toList());
// Use a Java 8 stream to print out each tuple of the list
splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %s %s", name[0], name[1])));
// Uses JdbcTemplate's batchUpdate operation to bulk load data
jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);
log.info("Querying for customer records where first_name = 'Josh':");
jdbcTemplate.query( 
  "SELECT id, first_name, last_name FROM customers WHERE first_name = ?", 
  new Object[]{"Josh"}, (rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name")))
  .forEach(customer -> log.info(customer.toString()));

The following is verified on the mysql client:


mysql> select * from customers;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1 | John    | Woo    |
| 2 | Jeff    | Dean   |
| 3 | Josh    | Bloch   |
| 4 | Josh    | Long   |
+----+------------+-----------+

4 rows in set (0.00 sec)

5. Some potholes

maven configuration

The lamda expression of jdk8 is used, and java.version must be configured in maven


mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+

4 rows in set (0.00 sec)

0

The docker service has restarted

When the docker service is suspended, the container is suspended and not restarted. The container should be run with the parameter restart=always


Related articles: