MySQL Series 3 Basics

Tutorial Series

MySQL series: Basic concepts of MySQL relational database
MariaDB-server installation of MySQL series
MySQL Series II Multi-Instance Configuration
MySQL Series 4 SQL Syntax
MySQL series five views, stored functions, stored procedures, triggers
MySQL series 6 users and authorization
MySQL Series 7 MySQL Storage Engine
MySQL Series 8 MySQL Server Variables
MySQL series 9 MySQL query cache and index
MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
MySQL Series 11 Logging
MySQL Series 12 Backup and Recovery
MySQL Series 13 MySQL Replication
MySQL Series 14 MySQL High Availability Implementation
MySQL series 15 MySQL common configuration and performance stress test

1. Introduction to MySQL

MySQL was originally an open source relational database management system. Its original developer was the Swedish company MySQL AB, which was acquired by Sun in 2008. In 2009, Oracle acquired Sun and MySQL became an Oracle product.

However, after being acquired by Oracle, Oracle significantly raised the price of the commercial version of MySQL, and Oracle no longer supported the development of another free software project, OpenSolaris. As a result, the free software community was concerned about whether Oracle would continue to support the MySQL Community Edition (the only free version of MySQL). MySQL founder Michael Widenius established a branch project MariaDB based on MySQL. Some open source software that originally used MySQL has gradually turned to MariaDB or other databases.

2. Development History of MySQL

By Monty Michael Widenius

• 1996: MySQL 1.0 was released, initially for Solaris and later for Linux
• 1999: Monty founded MySQL AB in Sweden
• 2003: MySQL 5.0 was released, adding views, stored procedures and other features
• 2008: Acquired by Sun
• 2009: Oracle acquires Sun
• 2009: Monty founded MariaDB

Version evolution:

• MySQL: 5.1 --> 5.5 --> 5.6 --> 5.7
• MariaDB: 5.5 --> 10.0 --> 10.1 --> 10.2 --> 10.3

3. Basic use of MariaDB

MariaDB aims to be fully compatible with MySQL, including API and command line, making it an easy replacement for MySQL. In terms of storage engine, XtraDB (code-named Aria) is used to replace MySQL's InnoDB starting from version 10.0.9.

​ MySQL was developed by Michael Widenius, the founder of MySQL. He had previously sold his company MySQL AB to SUN for $1 billion. After that, as SUN was acquired by Oracle, the ownership of MySQL also fell into the hands of Oracle. MariaDB is named after Michael Widenius' daughter Maria.

​ Plug-in storage engine: also known as "table type", the storage manager has multiple implementation versions, and the functions and features may be slightly different; users can flexibly choose according to their needs. Starting from MySQL5.5.5, the innoDB engine is the default engine of MYSQL. MyISAM ==> Aria, InnoDB ==> XtraDB

1. Basic installation and configuration

Installation: yum install mariadb-server

Main configuration file: Check from top to bottom. If there are duplicate configuration parameters, the parameters in the following configuration file will take effect and overwrite the searched ones.

/etc/my.cnf
/etc/mysql/my.cnf
/etc/sysconfig/my.cnf
/usr/local/etc/my.cnf
~/.my.cnf

​ View the default configuration: /usr/libexec/mysqld --print-defaults

The default database library file storage directory is: /var/lib/mysql/

Socket file: /var/lib/mysql/mysql.sock

PID file: /var/run/mariadb/mariadb.pid

Log file: /var/log/mariadb/mariadb.log

Start the service: # systemctl start mariadb.service

2. Client command: mysql

Options:

-u root specifies the user
-p password specifies the password
-h host specifies the connection host
-A disable completion
-P port specifies the port, the default is 3306
-S socket file path default: /var/lib/mysql/mysql.sock
-D databasename specifies the default database
-C Enable compression
-e SQL_CMD exits after execution
-V View version
--print-defaults View default settings

Batch Mode

mysql < /path/somefile.sql Import the sql script into the database for execution

Interactive Mode

Client Commands

• \d|delimiter Set the statement terminator
• \c|clear End the statement early
• \r|connect Reconnect to the database
• \g Send the statement directly to the server for execution
• \G Send the statement directly to the server for execution; the result is displayed vertically
• .|source Read the script file to create a database
• ! COMMAND executes shell command
• \W Display a warning message after the statement is executed
• \w No warning message is displayed after the statement is executed
• \s Get the current system status
• use database_name specifies the database for the operation
• prompt \u@[\D] \r:\m:\s-> Change the prompt. If you need to change it permanently, add prompt="(\u@\h) [\d]> under [mysql] in my.cnf
• \q Exit

Server command: You need to add a command terminator after the command. The default terminator is a semicolon ";"

• help KEYWORD Get help for server commands
• select version(); View the database version
• show variables; Displays the environment variables of the current database work

3. Other client tools

• mysqldump: A backup tool that sends query requests to mysqld based on the mysql protocol and converts all the retrieved data into write operation statements such as insert and saves them in text files
• mysqladmin: manage mysqld based on mysql protocol
• mysqlimport: Data import tool
• myisamchk: Check MyISAM library
• myisampack: Pack MyISAM tables
• mysqld_safe: Service Binary
• mysqld_multi: Create multiple instances tool
  

4. Security enhancement script mysql_secure_installation

This script can help us do some basic security enhancement

[root@centos7 mysql]# mysql_secure_installation
Enter current password for root (enter for none): #Press Enter directly if the password is empty Set root password? [Y/n] Y #Set root password New password: ******
Re-enter new password: ******
Remove anonymous users? [Y/n] Y #Delete anonymous accounts Disallow root login remotely? [Y/n] n #Disable remote connection Remove test database and access to it? [Y/n] Y #Delete test library Reload privilege tables now? [Y/n] Y #Reread the authorization table to make its configuration effective [root@centos7 mysql]# mysql -uroot -p'your_password' #Connect to database command

This is the end of this article on the third part of the MySQL series. For more information about MySQL basics, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!