Spring boot Spring data and mysql docker container:
https://www.youtube.com/watch?v=vO7MNTXxbZ0
docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:5.7.22 (select from docker images with Kitematic)
Run docker container:
Initializing a fresh instance When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.
The Docker documentation is a good starting point for understanding the different storage options and variations, and there are multiple blogs and forum postings that discuss and give advice in this area. We will simply show the basic procedure here for the latter option above:
Create a data directory on a suitable volume on your host system, e.g. /my/own/datadir. Start your mysql container like this:
$ docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
The -v /my/own/datadir:/var/lib/mysql part of the command mounts the /my/own/datadir directory from the underlying host system as /var/lib/mysql inside the container, where MySQL by default will write its data files.
see : https://hub.docker.com/_/mysql/ (docker mysql documentation for aditional configuration)
docker inspect mysql-docker (show docker info like ip )
docker run --name mysql-local -p 3388:3306 -v $PATH_DOCKER:/var/lib/mysql -v $PATH_DOCKER/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=root -d mysql:5.5.57
Connect to mysql inside a docker container:
docker exec -it mysql-local bash --> (run bash inside mysql docker container) a
Connect as root user (mysql):
mysql -u root -p - ->>>Connect to mysql database as root with password
- Show all databases :
show databases;
+-------------------------------------+
| Database |
+-------------------------------------+
| information_schema |
| batchdb | |
| mysql |
| performance_schema |
+-------------------------------------+
- select a database:
use mysql;
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed
- Show all tables
show tables;
+---------------------------------+
| Tables_in_mymdb |
+---------------------------------+
| User |
| Account |
- show table definition
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
- user and host grant permission
SELECT user,host FROM mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
- create database and user (spring example)
**% value give access from any ip. Change to localhost if you need database only local access
mysql> create database db_example; -- Create the new database
mysql> create user 'springuser'@'%' identified by 'springuser'; -- Creates the user with password springuser
mysql> grant all on db_example.* to 'springuser'@'%'; -- Gives all the privileges to the new user on the newly created database
** In intellij only show one schema: **************************************
Right-click on connection -> Properties -> Schemas and Tables tab -> select all the checkboxes in available databases tab -> click OK
Mysql workbenck will show all schemmas
- Bonus track: create a view:
CREATE VIEW MI_VIEW1 SELECT * FROM xxxx JOIN yyyy ap on xxxx.application = ap.id
WHERE (ap.id =15558)
Now it can be used:
select * from database.MI_VIEW1 where xxxxx
Granting user/view:
GRANT SELECT ON database.MI_VIEW1 TO 'someuser'@'somehost'; (grant and user to be able to only use this view)
- If we use latest docker mysql image Mysql workbench throws Authentication plugin 'caching_sha2_password' cannot be loaded.
You need to connect to mysql with root and change password (mysql_native_password is a must with mysql workbench)
docker exec -it mysql-2 bash
mysql -u root -p
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; (use old mysql_native_password)
- MySQL recommends using indexes for a variety of reasons including elimination of rows between conditions: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
This makes your datetime column/Varchar columns an excellent candidate for an index if you are going to be using it in conditions frequently in queries. If your only condition is BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) and you have no other index in the condition, MySQL will have to do a full table scan on every query.
https://dev.mysql.com/doc/refman/8.0/en/getting-information.html