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.
Prepare
When referring to this document to use MySQL, please read and ensure the following points:
-
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
- When completing the installation of MySQL in the Websoft9 Console, get the applicaiton's Overview and Access information from My Apps
Testing Availability
- Access the CLI of the MySQL container and log in to test availability
mysql -u root -p
- 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
- Stop the current MySQL container and run a new MySQL container sharing storage with the old container.
- Change the password in the new container and then delete the new container.
- 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)
-
Log in to phpMyAdmin, select Import at the top, and follow the wizard to start the import.
-
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
- install the Maria Audit Plugin plugin
- 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:
- Incorrect port mapping, resulting in no network access for the container.
- The container does not have remote access privileges enabled
- 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;