Skip to main content
Version: Next

MySQL

MySQL is MySQL-a high reliable database management system, used for Relational Databases . MySQL can cost-effectively help you deliver high performance, scalable database applications.

ui

Prepare​

When referring to this document to use MySQL, please read and ensure the following points:

  • Login to Websoft9 Console and find or install MySQL:

    • Go to My Apps listing applications
    • Go to App Store installing target application

  • This application is installed by Websoft9 console.

  • The purpose of this application complies with the GPL-2.0 open source license agreement.

  • Configure the domain name or server security group opens external network ports for application access.

Getting started​

Initial setup​

  1. When completing the installation of MySQL in the Websoft9 Console, get the applicaiton's Overview and Access information from My Apps

Testing Availability​

  1. Access the CLI of the MySQL container and log in to test availability 
    mysql -u root -p
  2. A successful login will take you to the MySQL CLI

Graphical tools​

The Websoft9 App Store provides phpMyAdmin and CloudBeaver for managing MySQL, without opening an external network.

Configuration options​

  • Configuration file directory (mounted): /etc/mysql/conf.d
  • Initialization script directory (mounted): /docker-entrypoint-initdb.d
  • Port: 3306
  • Master-slave replication (√): DDL and DML operations are replicated to the slave repository using binary logs, supporting one master and multiple slaves
  • Database hostname: Container name
  • External network port: user-defined selection during installation
  • Connectors and APIs
  • CLI tools:
    • mysql
    • mysqladmin
    • mysqldump
    • mysqlhotcopy
    • mysqlcheck
    • mysqlshow
    • mysqlimport
    • mysqlbinlog
    • myisampack

Administer​

Setting up Binary Log​

Binary Log is not enabled by default in MySQL. Modify the relevant entries in the MySQL Configuration File.

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

Setting up MySQL remote access​

Access the CLI of the MySQL container and set up remote access:

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

Changing the password.​

To change the password, execute the following command:

mysqladmin -u username -p old password password 'new password'

Resetting the Password​

To reset the password through a temporary container

  1. Stop the current MySQL container and run a new MySQL container sharing storage with the old container.
  2. Change the password in the new container and then delete the new container.
  3. Resume the original MySQL container

Backup (export)​

  • It is recommended to use a visualization tool such as phpMyAdmin to Export the database (SQL format is recommended)

  • Developers can use the mysqldump tool for exporting, which is more efficient and versatile:

    mysqldump -uroot -p databasename>databasename.sql

Recover (import)​

  1. Log in to phpMyAdmin, select Import at the top, and follow the wizard to start the import.

  2. Database character set incompatibility may occur during the import process, requiring manual intervention.

Migration​

Migrating from MySQL to MySQL is usually accomplished quickly by importing and exporting data.

However, migrations from other DBMS to MySQL are best handled using a migration tool such as MySQL Workbench: Database Migration

Audit​

  1. install the Maria Audit Plugin plugin
  2. Set the audit log path

Troubleshooting​

How to analyze database logs?​

MariaDB logs include files that record daily operations and error messages, categorized as follows:

  • Binary log (Binlog): Log all database operations for recovery.
  • Error log: For diagnosing problems
  • Slow Query Log: Records inefficiently executed query statements.
  • Generic Query Log: Records all query operations

From a troubleshooting perspective, focus on error logs and slow query logs.

Importing a database reports an error?​

Check the script to ensure it includes a database creation script.

Database service cannot start?​

Common reasons for MySQL startup failure include:

  • Insufficient disk space (binary log file size is growing too quickly)
  • Deadlocks
  • MySQL configuration file errors

Use the following commands to troubleshoot:

# View disk space
df -lh

# Check memory usage
free -lh

# View database logs
docker logs container_name

Logs causing low disk space?​

  • Clean up logs manually
  • Consider commenting out #log_bin=mysql-bin in the configuration file to turn off binary logs

Data files exceeding upper limit?​

When a single file exceeds the upper limit, it can prevent the database from starting. In such cases, add disk space or specify a new data file path:

innodb_data_file_path= /data/mysql/data1:2000M;/data2/mysql/data2:2000M:autoextend

MySQL container cannot be accessed remotely?​

Possible reasons include:

  1. Incorrect port mapping, resulting in no network access for the container.
  2. The container does not have remote access privileges enabled
  3. Special setting requirements for MySQL 8.0.

mysqladmin Command reports an error ?​

Error: “Access denied; you need the SUPER privilege for this operation”
Reason: The mysqlamdin command requires SUPER privilege, which is not available to normal users by default.

Cannot delete all tables?​

Some tables cannot be deleted due to Foreign Key Constraints between tables.

Deadlock in database?​

Deadlocks are typically caused by application design issues, particularly with transaction operations.

To confirm the cause of a deadlock, use the following command:

MariaDB [(none)]> show innodb status \G;