Complete Guide to Configuring MariaDB External Access

🎯 Summary

To enable external access to MariaDB, you need two steps: modifying bind-address settings and configuring user permissions.

Quick Solution

1. Comment out bind-address

# Edit my.cnf file
sudo nano /etc/mysql/my.cnf
# or
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Comment out this line
#bind-address = 127.0.0.1

2. Grant external access permissions

-- Execute after connecting to MariaDB
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

3. Restart service

sudo systemctl restart mariadb

📚 Detailed Explanation

Background and Necessity

MariaDB by default only allows local connections (127.0.0.1) for security reasons. To connect from remote servers or other applications, you must configure the database to allow external access.

Step-by-Step Configuration Process

1. Locate Configuration Files

Ubuntu/Debian Systems

# Main configuration file locations
/etc/mysql/my.cnf
/etc/mysql/mariadb.conf.d/50-server.cnf

CentOS/RHEL Systems

# Main configuration file locations
/etc/my.cnf
/etc/my.cnf.d/server.cnf

2. Modify bind-address Settings

# Backup configuration file
sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

# Edit configuration file
sudo nano /etc/mysql/my.cnf

Before Change

[mysqld]
bind-address = 127.0.0.1

After Change

[mysqld]
#bind-address = 127.0.0.1
# Or to allow specific IPs only
#bind-address = 0.0.0.0

3. Configure User Permissions

-- Connect to MariaDB
mysql -u root -p

-- Allow access from all IPs
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

-- Allow access from specific IP only
CREATE USER 'myuser'@'192.168.1.100' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'192.168.1.100';
FLUSH PRIVILEGES;

4. Firewall Configuration

Ubuntu (UFW)

sudo ufw allow 3306/tcp

CentOS (firewalld)

sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

Practical Use Cases

Web Application Connection

// Node.js example
const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'your-server-ip',
  user: 'myuser',
  password: 'mypassword',
  database: 'mydatabase'
});

connection.connect((err) => {
  if (err) {
    console.error('Connection failed:', err);
    return;
  }
  console.log('MariaDB connection successful!');
});

External Tool Connection Settings

Host: your-server-ip
Port: 3306
Username: myuser
Password: mypassword
Database: mydatabase

Security Considerations

1. Allow Specific IPs Only

-- Allow specific IP range only
GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.%' IDENTIFIED BY 'password';

2. Set Strong Passwords

-- Use complex passwords
CREATE USER 'user'@'%' IDENTIFIED BY 'StrongP@ssw0rd!2023';

3. Principle of Least Privilege

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'user'@'%';

Troubleshooting

Items to Check When Connection Fails

1. Check Port

netstat -tulpn | grep 3306

2. Check Service Status

sudo systemctl status mariadb

3. Check Logs

sudo tail -f /var/log/mysql/error.log

Common Error Solutions

“Access denied” Error

-- Recheck user permissions
SELECT user, host FROM mysql.user WHERE user = 'myuser';
SHOW GRANTS FOR 'myuser'@'%';

“Can’t connect to server” Error

# Check firewall status
sudo ufw status
# or
sudo firewall-cmd --list-all

Conclusion

Configuring MariaDB external access can be easily resolved by commenting out the bind-address and setting user permissions. However, for security purposes, it’s important to allow only specific IPs and apply the principle of least privilege.

Consider implementing SSL connections and advanced security options as your next steps.