MariaDB Guide: The Open Source MySQL Alternative

It’s time to get to grips with MariaDB, the popular open-source relational database created by the original developers of MySQL. MariaDB is designed as a drop-in replacement for MySQL but offers improved performance, new features, and a more community-driven development model. Many Linux distributions have even adopted it as their default MySQL implementation.

💻 Initial Setup and Security

After installing MariaDB, your first steps should be to secure the server and create a user. The client is typically the same as MySQL’s.

1. Connect as Root: You’ll first connect using the administrative root user.
   mysql -u root -p
2. Secure the Installation: Run the provided security script. It will prompt you to set a root password, remove anonymous users, and disable remote root login.
   mysql_secure_installation

💻 Creating Databases, Tables, and Users

Once connected, you interact with MariaDB using SQL commands. Let’s create a database, a table, and a dedicated user.

• Create a Database:
  CREATE DATABASE lxfdata;
USE lxfdata;
• Create a Table: Here we define a table to store data about Linux distros. The id column is set to AUTO_INCREMENT to act as a unique primary key.
  

  CREATE TABLE linuxes (
    id int(5) NOT NULL AUTO_INCREMENT,
    name varchar(32) DEFAULT NULL,
    easy bool DEFAULT NULL,
    PRIMARY KEY(id)
  );

  
  
• Create a User and Grant Privileges: It’s bad practice to use the root user for applications. Always create a less privileged user for specific tasks.
  

  CREATE USER 'lxfuser'@'localhost' IDENTIFIED BY 'password1';
  GRANT INSERT, SELECT on lxfdata.linuxes TO 'lxfuser'@'localhost';

  
  

💻 Basic Data Manipulation (CRUD)

With your user and table set up, you can now manage your data.

• Insert Data:
  INSERT INTO linuxes (name, easy) VALUES ('Ubuntu', 1);
• Read (Select) Data: The WHERE clause filters results, and ORDER BY sorts them.
  SELECT * FROM linuxes WHERE easy = 1 ORDER BY name;
• Update Data:
  UPDATE linuxes SET easy=0 WHERE name='Ubuntu';
• Delete Data:
  DELETE FROM linuxes WHERE name = 'Ubuntu';

More Topics

• Redis Guide: The High-Speed In-Memory Data Store
• Riak NoSQL Guide: What’s the Big Deal?
• MongoDB Guide: Build a Blog with Python and Bottle
• MongoDB Guide: An Admin’s Guide to Maximum Power
• MongoDB Guide: Using Native Drivers with Python and Ruby
• SQLite3 Guide: Getting Started with Python
• A Beginner’s Guide to Go: Explore Functions