Skip to content

Automate common scripts with MySQL without writting any command on the MySQL CLI.

License

Notifications You must be signed in to change notification settings

plusiv/mysql-scriptor

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

36 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

MySQL Scriptor Container

Table of Contents

About

This tool helps to automate common scripts with MySQL Containers without writting any command on the MySQL CLI.

Supported tasks for the moment are:

  • User creation (with grant privileges) and dropping.
  • Database creating and dropping.
  • Multiple .sql scripts execution.
  • Connection testing.

This tool is also util for automation and CI/CD procceses since it allows run scripts on MySQL database in a faster and secure way, without connecting directly to it.

Getting Started

To use this tool you must pull the image from Docker Hub, and run the container in the same network as your database container, additionally you must pass the environments variables that describes what you want to achieve.

docker run jorgmassih/mysql-scriptor-container \
    -e MYSQL_HOST='<your-database-host>' \
    -e MYSQL_PORT='<your-database-port>' \
    -e MYSQL_USER='<your-database-user>' \
    -e MYSQL_PASSWORD='<your-database-password>' \
    -e ACTION_DATABASE='create' \
    -e ACTION_DATABASE_NAME='testdb' \
    -e ACTION_USER='create' \
    -e ACTION_USER_NAME='test-user' \
    -e ACTION_USER_PASSWORD='P@ssw0rd' \
    -e ACTION_USER_ALLOWED_HOSTS='%' \
    -e ACTION_USER_GRANT_ALL='yes'
    --network <my-database-network>

The previous command will attempt to create a database called testdb and also a user called test-user identified by the password P@ssw0rd allowed to be connected from everywhere (since was set with %) and with all grants to that databse.

You can also write that long line in a docker-compose file for an elegant solution.

---
version: '3'
services:
  msc: 
    image: jorgmassih/mysql-scriptor-container 
    container_name: mysql-client
    environment:
      - MYSQL_HOST=database
      - MYSQL_PORT=3306
      - MYSQL_USER=root
      - MYSQL_PASSWORD=root
      - ACTION_DATABASE=create
      - ACTION_DATABASE_NAME=testdb
      - ACTION_USER=create
      - ACTION_USER_NAME=test-user
      - ACTION_USER_PASSWORD=P@ssw0rd
      - ACTION_USER_ALLOWED_HOSTS=%
      - ACTION_USER_GRANT_ALL=yes
    depends_on: 
      - mysql
    networks:
      - database

  mysql:
    image: mysql:5.7
    container_name: dummy-mysql
    environment:
      - MYSQL_ROOT_PASSWORD=root
    networks:
      - database

  adminer:
    image: adminer
    depends_on: 
      - mysql
    ports:
      - 8080:8080
    networks:
      - database

networks:
  database:

Important Note: make sure the user you are using has Privileges to Grant other users. In the above example, the user root were used.

Environmental Variables

Available Environmental Variables at the moment will be listed below.

MySQL connection variables

Variable Name Options Default Description
MYSQL_USER Any root MySQL connection user.
MYSQL_PASSWORD Any N/A MySQL connection password.
MYSQL_HOST Any mysql MySQL connection host.
MYSQL_PORT Any number 3306 MySQL connection port.
MYSQL_PROTOCOL tcp,socket,pipe,memory tcp MySQL connecto protocol.
MYSQL_CONNECTION_TIMEOUT Any number 5 MySQL connection timeout.

Actions variables

Variable Name Options Default Description
ACTION_USER create,drop create Creates or Drops user.
ACTION_USER_NAME any N/A Name of user to create or drop.
ACTION_USER_PASSWORD any N/A User password (only with create action)
ACTION_USER_ALLOWED_HOSTS any ip direcction localhost Specify which hosts will be available to use the user.
ACTION_USER_AUTH_PLUGIN mysql_native_password mysql_native_password Authentication plugin for connection.
ACTION_USER_GRANT_ALL_ON_DB yes,no no Grants all privileges for selected database by ACTION_DATABASE_NAME
ACTION_DATABASE create,drop create Creates or Drops database.
ACTION_DATABASE_NAME any N/A Name of database to create or drop.

Others variables

Variable Name Options Default Description
TEST_CONNECTION_ONLY yes,no no Exits after connection testing.

Avanced usage

Using a configuration file

You can specify the MySQL configuration in a file and create a bind volume to the path /etc/mysql/my.cnf in the container. This configuration file must have to follow the MySQL documentation for my.cnf file but just with the [client] group.

# my-config.cnf
[client]
host=database
user=jorgmassih
password=root
port=3306
protocol=tcp

And inside of the docker-compose.yml:

---
version: '3'
services:
  msc: 
    image: jorgmassih/mysql-scriptor-container 
    container_name: mysql-client
    environment:
      - ACTION_DATABASE=create
      - ACTION_DATABASE_NAME=testdb
      - ACTION_USER=create
      - ACTION_USER_NAME=test-user
      - ACTION_USER_PASSWORD=P@ssw0rd
      - ACTION_USER_ALLOWED_HOSTS=%
      - ACTION_USER_GRANT_ALL=yes
    volumes: 
      - /path/to/my-config.cnf:/etc/mysql/my.cnf
    networks:
      - mysql-database-network

Keeping a file ready with your connection parameters allows you to run the container without keeping your credentials in your CI/CD process.

If you want a more secure approach while using it manually, you can remove the credentias from your configuration file and set to yes the environmental variable PROMPT_CREDENTIALS, and then you will be prompted for connection user and password.

⛏️ Built Using

✍️ Authors

🤝 Contributing

I'm open to contributions! If you are interested in collaborating, you can reach out to me via the info on my bio.