Jianghu Panel - Database
12006In this lesson, we will primarily learn how to manage the server database using the "MySQL" interface of the Jianghu Panel.
1. Starting or Stopping MySQL
On the left side of the Jianghu Panel, click on "MySQL" to open the MySQL management interface.
Here, you can view the running status of the MySQL database and can stop, start, or restart the MySQL server.
2. Understanding the MySQL Plugin Management Interface
- Auto-start: Set MySQL to start automatically on boot. This is generally enabled by default.
- Configuration File: Most configurations in the MySQL plugin of the panel can be modified through the interface, and direct modification of the configuration file is also supported. It is recommended that users familiar with MySQL configurations use this option.
- Storage Location: The default storage location for the MySQL plugin is /www/server/mysql-apt/data. It is advisable to use the default configuration.
- Port: The default port for the MySQL plugin is 33067. After modifying the port, if you need to connect to MySQL from Navicat on your local computer, you must open the corresponding port in the panel's "Security" and the server's "Firewall" settings.
- Current Status, Performance Optimization: The MySQL plugin provides features to view the current status of MySQL and modify parameter configurations to optimize performance. For detailed explanations, refer to 304 Advanced Training - MySQL Optimization Guide.
- Logs, Slow Logs: The MySQL plugin supports querying logs and slow query logs. These logs can help administrators promptly identify and resolve database issues.
- Management List: See the next section for details.
3. Database Management List
In the MySQL management interface, click on "Management List" to see all databases on the server. In this interface, we can manage the root user of the database, add databases, or perform operations such as backing up, configuring permissions, changing passwords, and deleting individual databases.
If this list is empty, you can click the "Get from Server" button to obtain the latest databases from the MySQL server.
4. Database Users, Passwords, and Permissions
The MySQL plugin supports creating multiple users and databases, allowing different permissions to be set for different users. Generally, only administrators can create and delete users and databases; other users can only access the databases they own.
You can configure the corresponding permissions through the MySQL plugin - Management List in the panel.
Managing the root User and Password
In the MySQL "Management List," you can click on "root password" to view or modify the password of the root user.
Users and Passwords for Individual Databases
In addition to the root user, each database can also have corresponding users. Typically, these types of users only have permission to access data within their own databases and do not have permissions for other databases.
You can select a database in the MySQL plugin - Management List in the panel to modify permissions and change passwords.
JianghuJS Database User Permission Guidelines
When deploying JianghuJS projects, we generally require that each project only use the corresponding database's user and password to connect to the database, and the root user should not be used unless necessary. The benefit of this practice is to avoid unauthorized database access and enhance application security.
If a JianghuJS project needs to read and write data from other databases, the following principles can be followed:
- In the project database, create views for the tables that need to be read from the target database.
- Reading Data: You can directly read data from the views.
- Writing Data: If you need to insert, update, or delete data in the target database through the views, you can grant the project database's user the relevant permissions for the target database. This way, data can be written.
5. Data Backup
Regularly backing up databases is a necessary measure to ensure data security. This can help you quickly recover data in cases of accidental deletion, hard drive failure, hacker attacks, etc.
The Jianghu Panel provides database backup functionality, allowing you to easily back up and restore databases.
Manual Backup: On the "Database" page, select the database to back up, click the "Not Backed Up" button, then select the data options to back up in the pop-up window and click the "Backup" button.
You can also use the Jianghu Panel's Xtrabackup plugin to back up the database content in either full or incremental mode. For detailed information, see:
6. Database Logs
Database logs are a mechanism for recording database operations and statuses. They can be used for troubleshooting, performance analysis, and data recovery. Database logs can record operations such as writing, updating, and deleting, and can log the time, user, execution results, and other information for each operation. In the Jianghu Panel, you can view the log information of the database to understand the operation status and conditions of the database.
After entering the Jianghu Panel, click on the "MySQL" menu on the left sidebar, then "Logs," to view the database log files.
7. Database Security
In addition to regular backups, attention must also be paid to database security, such as setting database passwords, controlling user access permissions, and restricting remote access.
- Setting Database Passwords: A database password must be set when creating a database, and the password should be sufficiently complex to enhance protection.
- Controlling User Access Permissions: Define reasonable user permissions in the MySQL server to ensure that only qualified users are authorized to access specific databases, and specify allowed database operations while preventing all unauthorized personnel from accessing data.
- Restricting Remote Access: By setting firewall rules for the database, restrict access to the database to only hosts with specific IP addresses, thereby enhancing the security of database access.
8. Using Navicat For MySQL to Manage Data
- Ensure that the server has the MySQL port open (obtain it from the MySQL plugin - Port). Generally, two places need to be confirmed: one is whether it is open in the server's panel - Security, and the other is that it also needs to be open in the firewall settings of the server provider.
- Ensure that the corresponding data tables have open access permissions: click on the MySQL plugin - Management List.
- For external access to a single database: click on the permissions of the database that needs to be opened and set the access permission to "Everyone" (recommended to only open permissions for a single table).
- For external access to the root account: click the "ROOT Permissions" button above and set the access permission to "Everyone" (not recommended).
- Install and start Navicat For MySQL, and connect to the server database using Server IP:33067.
Assignment
Based on the content of this article, complete the following assignments:
- Try to change the root password of the database.
- Use the panel to create a database named "jianghu."
- Connect to the database using Navicat For MySQL, add a student table, and insert a record into the student table.
- Back up the "jianghu" database.