Docker Assignments #9: Docker, MySQL startup script and Backup

Docker containers are ephemeral i.e. any container data or configuration will be lost if the container is deleted or corrupted. However,  Docker Volume or bind-mount provide technique to persist container data. Later, we can spin-up new container with existing data.

1. Create a folder structure as per above image in the host machine
2. add following content into startscript.sql
3. create database hr;

   use hr;

   create table employee(id int, name varchar(10));

   insert INTO employee VALUES(1,'rama');
4. pull mysql:5.7 docker image into host machine
5. Write docker run command by setting following options

• enable detached mode
• set container name as "testmysql"
• map host port number to 3500
• set MYSQL_ROOT_PASSWORD as “mysql123”
• do bind-mount from 'node-mysql-initscript/scripts' to container '/docker-entrypoint-initdb.d'
• do bind-mount from 'node-mysql-initscript/data' to container '/var/lib/mysql'

      6. Check testmysql container up and running

       7. Use docker exec and enter testmysql  container mysql cli client with root password

       8. Check “hr” database and “Employee” table with data

       9. Insert sample data into "employee" table

       10. stop and delete "testmysql" container

       11. run new mysql container named "testmysqldata" by doing bind-mount from 'node-mysql-               initscript/data' to container '/var/lib/mysql'

      12. Check testmysqldata container up and running

       13. Use docker exec and enter 'testmysqldata'  container mysql cli client with root password

       14. Check “hr” database and “Employee” table with data

       15. stop and delete "testmysqldata" container

       16. remove 'data' folder under 'node-mysql-initscript' in the host machine

       17. Create docker volume named as 'testmysqlvolume' and create

              docker run command by setting following options

• enable detached mode
• set container name as "testmysql"
• map host port number to 3500
• set MYSQL_ROOT_PASSWORD as “mysql123”
• do bind-mount from 'node-mysql-initscript/scripts' to container '/docker-entrypoint-initdb.d'
• do volume mount from 'testmysqlvolume' to container '/var/lib/mysql'

      18. Check testmysql container up and running

       19. Use docker exec and enter testmysql  container mysql cli client with root password

       20. Check “hr” database and “Employee” table with data

       21. Insert sample data into "employee" table

       22. stop and delete "testmysql" container

       23. run new mysql container named "testmysqlvolumedata" by doing volume mount from 'node-mysql-               initscript/data' to container '/var/lib/mysql'

      24. Check testmysqlvolumedata container up and running

       25. Use docker exec and enter 'testmysqlvolumedata '  container mysql cli client with root password

       26. Check “hr” database and “Employee” table with data

Step 4:

     > docker pull mysql:5.7   

Step 5:

> docker run -d \ 

-p 3500:3306 \

    -e MYSQL_ROOT_PASSWORD=mysql123 \

    --name testmysql \

    --mount type=bind,source=/c/Users/rama/node-mysql-initscript/scripts,target=/docker-entrypoint-initdb.d \

    --mount type=bind,source=/c/Users/rama/node-mysql-initscript/data,target=/var/lib/mysql \      

    mysql:5.7

• -d --> used to run the container in detached mode
• -p --> map host port 3500 to container port 3306
• -e --> set container ENV variables
• --name --> set container name
• .sh or .sql scripts will be executed after container started by doing mount to /docker-entrypoint-initdb.d folder
• we can take backup of mysql container data by doing mount to /var/lib/mysql folder

Step 6: 

     > docker ps

Step 7: 

     > docker exec -it testmysql mysql -u root -p

Step 8: 

     mysql> show databases;

     mysql> use hr;

     mysql> select * from employee;

     mysql> insert into employee values(2,'krishna');

     mysql> select * from employee;

Step 10: 

     > docker rm -f testmysql

Step 11:    

> docker run -d \

  -p 3500:3306 \

  -e MYSQL_ROOT_PASSWORD=mysql123 \

  --name testmysqldata \   

  --mount type=bind,source=/c/Users/rama/node-mysql-initscript/data,target=/var/lib/mysql \      

  mysql:5.7

Step 12: 

     > docker ps

Step 13: 

     > docker exec -it testmysqldata mysql -u root -p

Step 14: 

     mysql> show databases;

     mysql> use hr;

     mysql> select * from employee;

     

Step 15: 

     > docker rm -f testmysqldata

Step 17: 

     > docker volume create testmysqlvolume

     > docker volume ls  

> docker run -d \

   -p 3500:3306 \

   -e MYSQL_ROOT_PASSWORD=mysql123 \

   --name testmysql \       

   --mount type=bind,source=testmysqlvolume,target=/var/lib/mysql \ 

   --mount type=bind,source=/c/Users/rama/node-mysql-initscript/scripts,target=/docker-entrypoint-initdb.d \  

   mysql:5.7

    

Step 19: 

     > docker exec -it testmysql mysql -u root -p

Step 20: 

     mysql> show databases;

     mysql> use hr;

     mysql> select * from employee;

   

 Happy Coding :)