MariaDB

# Update package manager
sudo apt update
 
# Install MariaDB
sudo apt install mariadb-server mariadb-client -y
 
# Start and enable MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb
 
# Verify installation
sudo systemctl status mariadb
mysql --version

# Install MariaDB
sudo yum install mariadb-server mariadb -y
 
# Start and enable MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb
 
# Verify installation
sudo systemctl status mariadb
mysql --version

# Run security script (recommended)
sudo mysql_secure_installation
 
# This will:
# - Set root password
# - Remove anonymous users
# - Disable root login remotely
# - Remove test database

# Login without password (first time)
sudo mysql -u root
 
# Set root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
 
# Exit
exit;
 
# Now login with password
mysql -u root -p

# Start MariaDB
sudo systemctl start mariadb
 
# Stop MariaDB
sudo systemctl stop mariadb
 
# Restart MariaDB
sudo systemctl restart mariadb
 
# Check status
sudo systemctl status mariadb
 
# View logs
sudo tail -f /var/log/mysql/error.log

# Connect to MariaDB
mysql -u root -p
 
# Create database
CREATE DATABASE dbname;
 
# Create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
 
# Grant privileges
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
 
# Show databases
SHOW DATABASES;
 
# Show users
SELECT User, Host FROM mysql.user;
 
# Exit
exit;

# Edit MariaDB config
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
 
# Change bind-address
bind-address = 0.0.0.0
 
# Or comment out to listen on all interfaces
# bind-address = 127.0.0.1

mysql -u root -p
 
# Create user for remote access
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'%';
FLUSH PRIVILEGES;
 
# Or limit to specific IP
CREATE USER 'username'@'192.168.1.100' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'192.168.1.100';

# Restart MariaDB
sudo systemctl restart mariadb

# UFW (Ubuntu)
sudo ufw allow 3306
 
# Firewalld (CentOS)
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

# Backup single database
mysqldump -u root -p dbname > dbname.sql
 
# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql
 
# Restore database
mysql -u root -p dbname < dbname.sql
 
# Restore all databases
mysql -u root -p < all_databases.sql

# Main configuration
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
 
# Common settings
max_connections = 200
max_allowed_packet = 256M
innodb_buffer_pool_size = 1G
query_cache_type = 1
query_cache_size = 16M

# Connect to MariaDB
mysql -u username -p -h hostname
 
# Show current user
SELECT USER();
 
# Show current database
SELECT DATABASE();
 
# Show tables
SHOW TABLES;
 
# Show table structure
DESCRIBE tablename;
 
# Show privileges
SHOW GRANTS FOR 'username'@'localhost';
 
# Revoke privileges
REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost';
 
# Delete user
DROP USER 'username'@'localhost';

# Stop MariaDB
sudo systemctl stop mariadb
 
# Remove MariaDB
sudo apt remove mariadb-server mariadb-client -y
sudo apt autoremove -y
 
# Remove data
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql
sudo rm -rf /var/log/mysql

# Stop MariaDB
sudo systemctl stop mariadb
 
# Remove MariaDB
sudo yum remove mariadb-server mariadb -y
 
# Remove data
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/my.cnf