---json
{
    "aliases": [
        { "path": ":docs:system:mysql:howto" }
    ],
    "page_id": "78sbwubp3pa8i48p716cq"
}
---
====== How to start and use the HowTo MySQL database in Tabulify ======



===== About =====
This howto shows you how to:
  * start an [[:docs:system:mysql:mysql|mysql database]] with the <icon name="logos:mysql" />  ''mysql'' howto [[:docs:service:docker|docker service]]
  * and use the  ''@mysql'' [[docs:connection:howtos|default howto mysql connection]] in tabul commands.


===== Steps =====

<unit display=none>
Should be in a stop step
<code bash>
tabul service stop mysql
</code>
</unit>

==== Docker Installation ====

[[:docs:service:howtos|howto docker services]] are an easy way to start locally a database instance.

Because [[:docs:service:howtos|howto services]] are [[:docs:service:docker|docker services]], you should have [[https://docs.docker.com/engine/install/|docker installed]] on your computer.

==== Check the service ====

By default, the [[docs:conf:tabul.yml|user configuration vault]] is created with the [[:docs:service:howtos|howto docker services]].

You can check if the mysql service is present and see its [[:docs:service:service#attributes|attributes]] with:
  * the [[:docs:tabul:service:info|tabul service info]] command
  * or the [[docs:conf:tabul.yml|configuration vault]]
<tabs>
    <panel>
       <label>Tabul Service Info</label>
<unit>
<code bash>
tabul service info mysql
</code>
<console>
Information about the service (mysql)
Attribute     Value                                                      Description
-----------   --------------------------------------------------------   --------------------------
COMMAND                                                                  The command
ENVIRONMENT   {MYSQL_DATABASE=howto, MYSQL_ROOT_PASSWORD=my-secret-pw}   The environment attributes
IMAGE         mysql:5.7.37                                               The image
IS_STARTED    false                                                      The status of the service
NAME          mysql                                                      The name of the service
ORIGIN        CONF                                                       The origin of the system
PORTS         3306:3306                                                  The ports
TYPE          docker                                                     The type of the system
VOLUMES                                                                  The volumes
</console>
</unit>
   </panel>
   <panel selected>
       <label>Tabul Yaml Vault </label>
<unit>
<code bash>
tabul data print --type text .tabul/.tabul.yml@home | yq '.spec.services.mysql'
</code>
<console yaml>
environment:
  MYSQL_DATABASE: howto
  MYSQL_ROOT_PASSWORD: my-secret-pw
image: mysql:5.7.37
ports:
  - 3306:3306
type: docker
</console>
</unit>
   </panel>
</tabs>

If the service is not present, move or delete your [[docs:conf:tabul.yml|user configuration vault file]]

==== Start the mysql service ====

To start the docker service, you can use
  * the [[:docs:tabul:service:start_command|tabul service start]] command
  * or the ''docker command''

This service is based on the [[https://hub.docker.com/_/mysql|Official MySql Docker image]].
<tabs>
    <panel selected>
       <label>Tabulify Service</label>
<unit>
<code bash>
tabul service start mysql
</code>
<console>
1 service was started
Name    Type
-----   ------
mysql   docker
</console>
</unit>
   </panel>
   <panel>
       <label>Docker Bash</label>
       You can use start the local database with the following [[docs:common:bash|Bash]] command :
<code bash>
docker run \
    -e MYSQL_DATABASE=howto \
    -e MYSQL_ROOT_PASSWORD=my-secret-pw \
    -p 3306:3306 \
    -d \
    --name mysql \
    mysql:5.7.37
</code>
</panel>
<panel>
   <label>Docker CMD / Powershell</label>
  * [[docs:common:shell|CMD/Powershell]]
<code bash>
docker run ^
    -e MYSQL_DATABASE=howto ^
    -e MYSQL_ROOT_PASSWORD=my-secret-pw ^
    -p 3306:3306 ^
    -d ^
    --name mysql ^
    mysql:5.7.37
</code>
   </panel>
</tabs>

==== Check that you can ping/connect to the service with the connection ====

<unit>
<code bash>
tabul connection ping mysql
</code>
<console>
The connection (mysql) has been pinged successfully
</console>
</unit>



==== Connection Info ====

By default, the [[docs:conf:tabul.yml|user configuration vault]] is created with the [[:docs:service:howtos|howto docker services]].

<tabs>
    <panel>
       <label>Tabul Connection Info</label>
You can check the service with the [[:docs:tabul:service:info|tabul service info]] command
<unit>
<code bash>
tabul connection info mysql
</code>
<console>
Information about the connection (mysql)
Attribute                    Value                               Description
--------------------------   ---------------------------------   --------------------------------------------------------------------
BOOLEAN_DATA_TYPE            Native                              Boolean data type used to store boolean values
BUILDER_CACHE_ENABLED        true                                Enable or disable the builder cache
CHAR_DEFAULT_PRECISION       1                                   Default CHAR precision
COMMENT                      The default mysql data store        A connection description
DATABASE_MAJOR_VERSION                                           The major version number of the database
DATABASE_MINOR_VERSION                                           The minor version number of the database
DATABASE_PRODUCT_NAME                                            The name of the database
DATABASE_PRODUCT_VERSION                                         The version of the database
DATE_DATA_TYPE               NATIVE                              Date data type used to store date values
DRIVER                       com.mysql.cj.jdbc.Driver            The jdbc driver class
DRIVER_NAME                                                      The driver name
DRIVER_VERSION                                                   The driver version
HOST                                                             Host Server
JDBC_MAJOR_VERSION                                               The major version number of JDBC
JDBC_MINOR_VERSION                                               The minor version number of JDBC
LOGIN_STATEMENTS                                                 Statements that runs after a connection has been established
MAX_CONCURRENT_THREAD                                            The maximum number of threads that can be created against the system
MAX_NAME_IN_PATH                                                 The maximum number of names in a path
NAME                         mysql                               The name of the connection
NAME_QUOTING_DISABLED_CASE   UPPERCASE                           The case to apply when quoting is disabled
NAME_QUOTING_ENABLED         true                                Enable quoting of names
NATIVES                                                          Native Driver attributes (jdbc properties, ...)
NCHAR_DEFAULT_PRECISION      1                                   Default NCHAR precision
NVARCHAR_DEFAULT_PRECISION   0                                   Default NVARCHAR precision
ORIGIN                       CONF                                The origin of the connection
PASSWORD                     my-secret-pw                        The user password
PORT                         3306                                Host Port
SUPPORT_BATCH_UPDATES                                            If the system supports batch SQL updates
SUPPORT_NAMED_PARAMETERS                                         If the system supports named parameters in the SQL statement
TIMESTAMP_DATA_TYPE          NATIVE                              Timestamp data type used to store timestamp values
TIME_DATA_TYPE               NATIVE                              Time format data type to store time values
URI                          jdbc:mysql://localhost:3306/howto   The uri of the connection
USER                         root                                The user name
VARCHAR_DEFAULT_PRECISION    2000                                Default VARCHAR precision
WORKING_PATH                 howto.                              The working path (Schema for database, directory for file system)
</console>
</unit>
   </panel>
   <panel selected>
       <label>Tabul Yaml Vault </label>
You can also check the connection attributes with the [[docs:conf:tabul.yml|configuration vault]]
<unit>
<code bash>
tabul data print --type text .tabul/.tabul.yml@home | yq '.spec.connections.mysql'
</code>
<console yaml>
comment: The default mysql data store
driver: com.mysql.cj.jdbc.Driver
password: my-secret-pw
uri: jdbc:mysql://localhost:3306/howto
user: root
varchar-default-precision: 2000
</console>
</unit>
   </panel>
</tabs>

If the connection is not present, move or delete your [[docs:conf:tabul.yml|user configuration vault file]]


==== Use the mysql connection ====


You then can use the ''@mysql'' connection in place of any other connection

Example:
  * replacing the ''@sqlite'' connection by the ''@mysql''
<unit display="none">
Drop all
<code bash>
tabul data drop --no-strict-selection --force *@mysql
</code>
</unit>
<unit>
<code bash>
# with sqlite
# tabul data upsert characters.csv@howto @sqlite
# with mysql
tabul data upsert characters.csv@howto @mysql
</code>
</unit>
<unit>
<code bash>
# with sqlite
# tabul data list  *@sqlite
# with mysql
tabul data list  *@mysql
</code>
<console>
path         media_type
----------   ----------
characters   table
</console>
</unit>
In particularly, you can follow:
  * the [[:howto:getting_started:getting_started|getting started guide]]
  * and all SQL howto ([[:howto:sql_table:sql_table|Sql Table]], Sql View, ...)



<iterator>
    <data>select from pages where path like ':howto:sql_table%'  or path like ':howto:sql_view:%' order by title asc limit 10</data>

    <carrousel element-width="250px">
        <fragment>
            <card clickable>
            <page-image path="$path" ratio="21:9" zoom="-6"/>
            <heading h5> $title </heading>

            ${description | head(200, "...") } [[$path| ]]
            </card>
        </fragment>
    </carrousel>
\\
</iterator>

==== Stop the mysql service ====

Once, you don't need the mysql service, you can stop it:
  * with the [[:docs:tabul:service:stop|tabul service stop]] command
  * or with Docker


<tabs>
    <panel selected>
       <label>Tabul Service Stop</label>
<unit>
<code bash>
tabul service stop mysql
</code>
<console>
1 service was stopped
Name    Type
-----   ------
mysql   docker
</console>
</unit>
   </panel>
   <panel>
       <label>Docker</label>
You can also stop it with Docker
<code bash>
docker stop mysql
</code>

   </panel>
</tabs>