Skip to main content

MySQL/MariaDB Getting Started

MySQL is a popular open source database. Whether you are a fast growing web property, technology ISV or large enterprise, MySQL can cost-effectively help you deliver high performance, scalable database applications. It is intend to provide a high reliable database management system. MariaDB is one of the most popular database servers. Made by the original developers of MySQL. Guaranteed to stay open source.

MariaDB is one of the most popular database servers in the world. It’s made by the original developers of MySQL and guaranteed to stay open source. Notable users include Wikipedia, WordPress.com and Google. MariaDB is developed as open source software and as a relational database it provides an SQL interface for accessing data. The latest versions of MariaDB also include GIS and JSON features.

If you have installed Websoft9 MySQL/MariaDB, the following steps is for your quick start

Preparation

  1. Get the Internet IP of your Server on Cloud
  2. Check your Inbound of Security Group Rule of Cloud Console to ensure the TCP:3306,9090 is allowed
  3. Complete Five steps for Domain if you want to use Domain for Jenkins
  4. Get default username and password of Jenkins

MySQL/MariaDB Initialization

Steps for you

  1. Use the SSH to connect Server, and run these command below to view the installation information and running status

    cd /data/apps/mysql && sudo docker compose ls
  2. You can ge the message from SSH " STATUS: running(1) " when MySQL is running

  3. Using SSH to connect MySQL Server, run the following command

    #assume the root password is `7j5vc0EttEUDtC!`
    docker exec -it mariadb mysql -uroot -p7j5vc0EttEUDtC!
  4. You can find the MySQL version from the message

  5. Login MySQL with phpMyAdmin

Having trouble?

Below is for you to solve problem, and you can contact Websoft9 Support or refer to Troubleshoot + FAQ to get more.

MySQL/MariaDB QuickStart

MySQL/MariaDB Setup

Modify and reset MySQL password

Modify password: Change the current password to another password
Reset password: Have forgotten your password, need to get an initial password

Modify password

You can modify password by MySQL GUI or commands:

  1. Use phpMyAdmin(Recommend)

  1. Use Commands
docker exec -it mysql mysqladmin -u root -p oldpassword password 'newpassword' 

Reset password

You can reset your MySQL password by the following two solution:

  1. Use SSH to connect MySQL Server, access into the mysql container
    docker exec -it mysql bash
  2. Run the following command
    wget -N https://raw.githubusercontent.com/websoft9dev/role_mysql/master/tools/reset_password.sh; bash reset_password.sh

Set Remote Connection

Most of the time, if you want to use GUI tools to manage MySQL/MariaDB on you local computer, you shoud open the remote connection of MySQL/MariaDB. 

Database is high security application, you need two steps for remote connection settings at least:

  1. Enable the TCP:3306 port

  2. Open MySQL remote connection

    • phpMyAdmin Quick set (recommended)

    • Use commands

      # Log in MySQL
      docker exec -i mysql mysql -u root -p

      # Enable Remote
      mysql> use mysql;
      mysql> update user set host = '%' where user = 'root';

      # Restart MySQL
      sudo docker restart mysql

Modify MySQL Data Directory

The data directory for MySQL is set to /data/mysql by default. If you want to modify MySQL Data Directory, following are the steps for you:

  1. Edit MySQL compose file

    version: '3.8'
    services:
    mariadb:
    image: $APP_REPO:$APP_VERSION
    container_name: $APP_NAME
    restart: always
    environment:
    - MARIADB_ROOT_PASSWORD=$APP_PASSWORD
    ports:
    - $APP_PORT:3306
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
    - ./src/newdata:/var/lib/mysql
  2. Recreate the MySQL container, date derectory is ok

    cd /data/apps/mysql && sudo docker compose up -d

Set The Binary Log

You can modify the [MySQL configuration] (#path) to change the binary log settings

log_bin = mysql-bin      # enable Binary log
binlog_format = mixed # Binary log format
expire_logs_days = 7 # Binary log expire time

Some times, there a lot of event for your database, then the binary log file size very rapid growth and your disk space may not enough, if there no space on disk, your MySQL Service can not start.

Suggest you change the expire_logs_day to more smaller if you binary log file size is too big

GUI

Working on a relational database using only command line tools to writing queries can be hard, luckily we found the best Free GUI tools for your MySQL work. Most uses of MySQL for Web development allow you to get around a lot of back end requirements with the use of PHP and other tools that work the database without having to do more than set up. However, as the use of SQL for Web development has increased, and the complexities, so has the availability of tools to manage your SQL database. Here are some of the best tools for working with your MySQL database.

NameDescriptionCross platformPrice
MySQL WorkbenchMySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.Windows,Linux,MacOSFree
phpMyAdminphpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL and MariaDB. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still have the ability to directly execute any SQL statement.Based on Web,one installation, use everywhereFree
NavicatNavicat for MySQL is the ideal solution for MySQL/MariaDB administration and development. It is a single application that allows you to connect to MySQL and MariaDB databases simultaneously.Windows,MacOS,IOSNot free
MySQL-FrontMySQL-Front is a Windows front end program for the MySQL database server. The database structure and data can be handled via dialogs or SQL commands. Import and Export in standard file formats is supported. The database server can be connected directly or via HTTP tunneling.WindowsFree
Sequel ProSequel Pro is a fast, easy-to-use Mac database management application for working with MySQL databases.MacOSFree
HeidiSQLHeidiSQL is a useful and reliable tool designed for web developers using the popular MariaDB or MySQL server, Microsoft SQL databases or PostgreSQL. Support for multiple languages (including Chinese)WindowsFree
DBeaver CommunityFree multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.WindowsFree
dbForge Studio for MySQL ExpressdbForge Studio for MySQL is a universal GUI tool for MySQL and MariaDB database development, management, and administration. The IDE allows you to create and execute queries, develop and debug stored routines, automate database object management, analyze table data via an intuitive interface. The MySQL client delivers data and schema comparison and synchronization tools, database reporting tools, backup options with scheduling, and much more.WindowsFree

GUI tools gives you direct access to your MySQL Databases on local and remote servers.

WEB GUI - phpMyAdmin

phpMyAdmin is a very popular MySQL database management tool. The following describes the common phpMyAdmin operations.

  1. Using local Chrome or Firefox to visit the URL http://Internet IP:9090, you will enter the  phpMyAdmin

  2. Select you language, Username is root,Password should get from the Chapter"Administrator usenames and password"

  3. Click the button "Go"

Modify the root password

  1. Login to phpMyAdmin, you can see the "Modify the password" link in dashboard page, then click it

  2. After the modification of password, exit phpMyAdmin and refresh the log in page to restart it

Add new database

  1. Login to phpMyAdmin, click "New" in the left menu to start creating a new database

  2. Fill in the suitable Database Name, select the character coding, then click "Create" button to complete this step

Add user account

The database user is separated from the database and is a "many-to-many" relationship. You can associate a user with the permissions of a database by associating

  1. Login to phpMyAdmin, Click one database on the left menu which you want to add user account for it

  2. Click "Privileges" tab, you can see the users belong to this database listed

  3. Click "Add user account", start to add new user

  4. Fill in suitable User name/Host name/Password, click "go" button to complete this step

  5. You can also go to User account->Add user account to manage users

Export/Impoft database

In phpMyAdmin, Export is to back up the database, import and restore the database.

Export

  1. Login to phpMyAdmin, select your database then click "Export" tab on the top menu

  2. Select suitable Export method,Format for you, then click the "Go" button to start export

  3. After the database backup file (.sql suffix) is generated, save it to the local computer

Import

  1. Restore the database, corresponding to the "Import" operation, refer to the following

  2. Import files should pay special attention to character set compatibility

MySQL Remote

To enable remote in phpMyAdmin, you only need to change the access mode of the root account to "any way to access", as follows:

  1. Log in phpMyAdmin, go to User account->Edit privileges of root account

  2. Select the tab Login Information, Host Name fill in "%", click "Go" button completing this setting

Manage multiple MySQL instances

phpMyAdmin supports multiple MySQL instances.

Modify the corresponding environment variable of the docker-compose file of phpMyAdmin (PMA_HOST is changed to PMA_HOSTS) to support:

version: "3.7"
services:
phpmyadmin:
image: phpmyadmin/phpmyadmin
container_name: "phpmyadmin"
environment:
- PMA_HOSTS=172.17.0.1,172.17.0.2
- PMA_PORTS=3306
- UPLOAD_LIMIT=20M

Modify import file size limit

The default file size that phpMyAdmin can import is limited, you can modify it by the following steps:

  1. Use SFTP to connect to the server, edit phpmyadmin compose file, and add a field to the environment variable - UPLOAD_LIMIT=20M

    version: "3.7"
    services:
    phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: "phpmyadmin"
    environment:
    - PMA_HOST=172.17.0.1
    - PMA_PORT=3306
    - UPLOAD_LIMIT=20M
  2. Restart phpMyAdmin

    cd /data/apps/phpmyadmin && docker-compose up -d

Reference sheet

The below items and General parameter sheet is maybe useful for you manage MySQL/MariaDB

Run docker ps, view all containers when MySQL/MariaDB is running:

CONTAINER ID   IMAGE               COMMAND                  CREATED          STATUS          PORTS                                       NAMES
4c7eb560231d phpmyadmin:latest "/docker-entrypoint.…" 10 minutes ago Up 10 minutes 0.0.0.0:9090->80/tcp, :::9090->80/tcp phpmyadmin
d5c4ba36ce50 mariadb:10.8 "docker-entrypoint.s…" 10 minutes ago Up 10 minutes 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp mariadb

Path

MySQL installation directory:  /data/apps/mysql
MySQL data directory: /data/apps/mysql/data/mysql_data
MySQL configure file: /data/apps/mysql/src/conf.d/mysql.cnf
MySQL compose file: /data/apps/mysql/docker-compose.yml

MariaDB installation directory: /data/apps/mariadb
MariaDB data directory: /data/apps/mariadb/data/mariadb_data
MariaDB configure file: /data/apps/mariadb/src/conf.d/mysql.cnf
MariaDB compose file: /data/apps/mariadb/docker-compose.yml

phpMyAdmin installation directory: /data/apps/phpmyadmin
phpMyAdmin configure file: /data/apps/phpmyadmin/config.inc.php
phpMyAdmin compose file: /data/apps/phpmyadmin/docker-compose.yml

Port

PortUseNecessity
3306MySQL/MariaDB Remote portOptional
9090HTTP Access phpMyAdmin on DockerOptional

Version

docker exec -it mysql mysql -V

Service

sudo docker start | stop | restart  phpmyadmin
sudo docker start | stop | restart mysql
sudo docker start | stop | restart mariadb

CLI

After MySQL installation, the following tools are available by default:

  • mysql
  • mysqladmin
  • mysqldump
  • mysqlhotcopy
  • mysqlcheck
  • mysqlshow
  • mysqlimport
  • mysqlbinlog
  • myisampack

API

Connectors and APIs