What is MySQL
MySQL is an open-source relational database management system (RDBMS). Its name is a blend of “My”, the name of prime supporter Michael Widenius’ little girl, and “SQL”, the abbreviation for Structured Query Language. Mastering MySQL is a must for programmers. Especially when associated with web programming that (almost) all use MySQL as a database.
The MySQL improvement extend has made its source code accessible under the terms of the GNU General Public License, and under an assortment of restrictive understandings. MySQL was possessed and supported by a solitary revenue-driven firm, the Swedish Organization MySQL AB, now claimed by Oracle Corporation ( For exclusive use, a few paid releases are accessible and offer extra usefulness. ). During the acquisition of Sun Microsystems by Oracle, some of the senior engineers who were working on the development of MySQL felt that there is a conflict of interest between MySQL and Oracle’s commercial database - Oracle Database Server. As a result, these engineers created a fork of MySQL code base and started their own organization. This is how MariaDB was born. MariaDB is free under the GNU GPL. It is outstanding for being driven by the first designers of MySQL, who forked it because of worries over its procurement by Oracle. Contributors are required to impart their copyright to the MariaDB Foundation. MariaDB means to keep up high similarity with MySQL, guaranteeing a “drop-in” supplanting capacity with library twofold equivalency and correct coordinating with MySQL APIs and commands.
MariaDB offers more and better storage engines. NoSQL support, provided by Cassandra, allows you to run SQL and NoSQL in a single database system. MariaDB also supports TokuDB, which can handle big data for large organizations and corporate users. MySQL’s usual (and slow) database engines MyISAM and InnoDB are replaced in MariaDB by Aria and XtraDB respectively. Aria offers better caching, which makes a difference when it comes to disk-intensive operations. Temporary tables also use Aria, which speeds up complex queries, such as those involving GROUP BY and DISTINCT. Percona’s XtraDB gets rid of all of the InnoDB problems with slow performance and stability, especially in high load environments.
Create a User & DB
Basic CRUD Operations on MySQL
Deploy MySQL-DB On Docker Container
Automate with Ansible
To follow these steps everytime when you create a new VM instance is a very time consuming and boring stuff. Here I use to automate the setup with ansible.
- Install MySQL
- Setup MySQL
- Enable and Start the MySQL Systemd Service
- Setup Configs
- Setup on Custom Dir
- Remote Access
Create a Users and Databases
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
# Note: Here, I use preconfigured ansible role to setup mysql database. Created By GeerlingGuy ( https://github.com/geerlingguy/ansible-role-mysql ). You can reference this and create your own role to setup mysql. # STEP - 1 : Install Ansible # STEP - 2 : Download geerlingguy's - ansible-role-postgresql $ ansible-galaxy install geerlingguy.mysql # STEP - 3 : Setup Ansible Env # Note: Before proceding, please add a password-less communication between your client and server # Create a Dir and Create these files . |____mysql.yaml |____hosts |____ansible.cfg
1 2 3 4 5 6 7 8 9 10
# ansible.cfg --- [defaults] inventory = hosts remote_user = ubuntu private_key_file = /home/user/.ssh/id_rsa host_key_checking = False deprecation_warnings = False interpreter_python = /usr/bin/python3 ---
1 2 3 4 5 6 7 8 9 10 11
# hosts --- [all:vars] ansible_user = ubuntu ansible_become = yes ansible_become_method = sudo [server] server1 ansible_host=10.0.1.11 ansible_port=22 server2 ansible_host=10.0.1.12 ansible_port=22 ---
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
# mysql.yaml --- - name: Install MySQL hosts: server become: yes roles: - geerlingguy.mysql vars: mysql_root_home: /root mysql_root_username: root mysql_root_password: 'password' mysql_root_password_update: true mysql_user_home: /home/ubuntu mysql_user_name: ubuntu mysql_user_password: 'password' mysql_enabled_on_startup: true mysql_datadir: /var/lib/mysql mysql_port: "3306" mysql_bind_address: "0.0.0.0" mysql_databases: - name: "example_db" encoding: "utf8mb4" collation: "utf8mb4_0900_ai_ci" mysql_users: - name: backops host: "%" password: "password" encrypted: no priv: "*.*:ALL" state: present --- # Note: This is a test env, That's why i put password's in plain text ( Please use ansible_vault or any secret manager to encrypt you secrets ) # You can also use mariadb, Add these packages in vars - --- mysql_packages: - mariadb-client - mariadb-server - python-mysqldb ---
1 2 3 4 5
# Run a playbook $ ansible-playbook -i hosts mysql.yaml # BOOM: This playbook will install and setup mysql and create a backops user with SUPERUSER privilege and also creates a DB ( example_db ). # Now, whenever you setup a new server machine. You only need to run this playbook. You can also setup this to automate mysql docker container.
How to Backup & Restore MySQL-DB
Author Akash Rajvanshi
LastMod Sunday, January 31, 2021
License Akash Rajvanshi