MySQL Configuration and User Management

This content is a key note in my DevOps development roadmap. It has been efficiently summarized by artificial intelligence and serves as a valuable memory aid in case of forgetfulness.

Creating a User:
- To create a new user in MySQL, use the following command:
```
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
```
- Here, 'username' is the name of the user, 'host' specifies where the user can connect from, and 'password' is the password assigned to this user.
Granting Privileges:
- To grant specific privileges to a user on a particular table:
```
GRANT INSERT ON database.table TO 'username'@'host';
```
- This command allows 'username' to perform INSERT operations on 'database.table'.
- To grant all privileges to a user on all databases and tables:
```
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' WITH GRANT OPTION;
```
- 'WITH GRANT OPTION' allows this user to grant these privileges to other users.
Importance of the Host:
- The host ('host') is crucial as it determines where the user can connect from. If you specify a specific host other than 'localhost', the user will only be able to connect from that host.
Initializing the MariaDB Database on Arch-based Linux Distributions:
- After installing MariaDB, use the following command to initialize the database:
```
mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
```
- This step prepares the data directory for MariaDB.
Restarting the MariaDB Service:
- After configuring or modifying settings, restart MariaDB to apply the changes:
```
systemctl restart mariadb.service
```
- This restart ensures that all new configurations are taken into account.

Network Monitoring

Network monitoring is crucial for maintaining security, optimizing performance, and troubleshooting issues in any system. In Ubuntu, several command-line tools are available for this purpose, each offering unique features and insights.

1. Netstat: A Classic Tool

Netstat is a traditional and versatile tool for network analysis. Although not installed by default on newer Ubuntu versions, it can be easily added via the net-tools package (sudo apt install net-tools). Use it to check active connections, routing tables, and port usage:

sudo netstat -tulnp

2. SS: The Modern Alternative

Replacing netstat, ss is a modern tool for monitoring sockets. It's faster and displays more detailed information:

sudo ss -tulnp

3. Lsof: For Open Files and Ports

Lsof, standing for 'list open files', is a powerful utility that also shows open network ports. It provides a detailed view of which processes are using which ports:

sudo lsof -i -P -n | grep LISTEN

4. Nmap: The Network Mapper

Primarily used for network discovery and security auditing, nmap is a robust tool for local system scans. It's particularly useful for identifying open ports and the services running on them:

sudo nmap -sT -O localhost

Why Monitor Ports and Services?

Monitoring ports and services is critical for several reasons:

Security: Identifying open ports can help pinpoint potential security vulnerabilities.
Performance Optimization: Understanding what services are running aids in resource management.
Troubleshooting: It's easier to resolve network issues when you know the state of your ports and services.

Tips for Effective Monitoring

Regularly review your running services and open ports.
Use a combination of tools for a comprehensive view.
Always ensure necessary permissions (like sudo) for complete information.

In conclusion managing and understanding running services and ports is a vital skill in system administration. Tools like netstat, ss, lsof, and nmap provide powerful ways to monitor and troubleshoot your network on Ubuntu. By regularly checking these services and ports, you can ensure that your system runs securely and efficiently.

Change `mariadb server settings`

To update the bind settings in the /etc/mysql/mariadb.conf.d/50-server.cnf file for MariaDB to allow connections from any IPv4 address, you need to modify the bind-address directive. Here's how you can do it:

Open the Configuration File: You will need to open the 50-server.cnf file with a text editor. Since this is a system file, you will need administrative privileges to edit it. You can use nano, a command-line text editor, for this purpose:
```
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
```
Modify the Bind Address: In the file, look for the line that starts with bind-address. It might be set to 127.0.0.1 (localhost) by default. You need to change it to 0.0.0.0 to allow connections from any IPv4 address.

It should look like this:
```
bind-address = 0.0.0.0
```
If the bind-address line is not there, you can add it under the [mysqld] section.
Save and Exit: After making the change, save the file and exit the editor. If you're using nano, you can do this by pressing CTRL + X, then Y to confirm, and Enter to save.
Restart MariaDB Service: For the changes to take effect, you need to restart the MariaDB service. You can do this with the following command:
```
sudo systemctl restart mariadb.service
```
Verify the Changes: After restarting the service, you can verify that the bind address has been updated successfully. You can use the netstat command to check that MariaDB is listening on all interfaces:
```
netstat -tulnp | grep mysql
```
Look for a line that shows mysql listening on 0.0.0.0:3306 or a similar entry.
Security Consideration: Be aware that setting the bind-address to 0.0.0.0 will allow connections from any IP address. This can expose your database to the internet if not properly secured. Ensure that you have robust firewalls and access controls in place to protect your database.

By following these steps, you should be able to update the bind settings in MariaDB to accept connections from any IPv4 address. Always remember to secure your database server when making it accessible over a network.