Python-100-Days-English/Day36-45/36.relational-databases-and-mysql-overview.md at main · tamnd/Python-100-Days-English

Relational Databases and MySQL Overview

Relational Database Overview

Data persistence means saving data to a storage medium that can keep data for a long time, so the data will not be lost even if the power is cut off.
The history of database development includes network databases, hierarchical databases, relational databases, NoSQL databases, and NewSQL databases.

In 1970, IBM researcher E. F. Codd published the paper A Relational Model of Data for Large Shared Data Banks in Communications of the ACM. In that paper he proposed the relational model, laying the theoretical foundation of relational databases. Later, Codd published several more articles on normalization theory and the twelve rules for evaluating relational systems, further grounding relational databases in mathematical theory.
Characteristics of relational databases.
- Theoretical foundation: relational algebra (set theory, first-order predicates, relational operations).
- Concrete form: use two-dimensional tables (with rows and columns) to organize data.
- Programming language: Structured Query Language (SQL).
  - DDL: Data Definition Language
  - DML: Data Manipulation Language
  - DCL: Data Control Language
  - TCL: Transaction Control Language
ER model (Entity-Relationship Model) and conceptual model diagram.

The ER model, whose full name is Entity-Relationship Model, was proposed by the Chinese-American computer scientist Peter Chen. It is a high-level way to describe a conceptual data model, as shown in the picture below.
- entity: rectangle
- attribute: ellipse
- relationship: diamond
- cardinality: 1:1 (one-to-one) / 1:N (one-to-many) / M:N (many-to-many)
In actual project development, we can use database modeling tools, such as PowerDesigner, to draw a conceptual data model. Then we set the target database system, convert the conceptual model into a physical model, and finally generate the SQL for creating two-dimensional tables. Many tools can export SQL directly or generate data tables according to the physical model we design and the target database we set.
Relational database products.
- Oracle: currently the most widely used database-management system in the world. As a general-purpose database system, it has complete data-management capabilities. As a relational database, it is a full-featured product. As a distributed database, it supports distributed processing. In newer versions, Oracle has also introduced a multitenant architecture, which makes it easier to deploy and manage database clouds.
- DB2: a relational database product developed by IBM, primarily running on Unix, including IBM's own AIX, Linux, and Windows Server. DB2 has a long history and is regarded as one of the earliest SQL-based database products. It also has strong business-intelligence capabilities.
- SQL Server: a relational database product developed and promoted by Microsoft. It was originally suitable mainly for data management in small and medium-sized businesses, but in recent years its usage has expanded, and some large enterprises and even multinational companies have begun to build their own data-management systems on top of it.
- MySQL: MySQL is open source, and anyone can download it under the GPL, General Public License, and modify it to suit their own needs. MySQL has attracted attention because of its speed, reliability, and adaptability.
- PostgreSQL: an open-source relational database product distributed under the BSD license.

MySQL Introduction

MySQL was originally developed by the Swedish company MySQL AB as an open-source relational database management system. That company was acquired by Sun Microsystems in 2008. In 2009, Oracle acquired Sun Microsystems, so MySQL is now also an Oracle product.

Because MySQL had high performance, low cost, and good reliability, it became the most popular open-source database in the past, so it was widely used in the development of small and medium-sized websites. As MySQL kept becoming more mature, it was gradually used in more large-scale websites and applications. Wikipedia, Google, Facebook, Baidu, Taobao, Tencent, Sina, Qunar, and many others all used MySQL to provide data persistence services.

After Oracle acquired Sun Microsystems, it greatly raised the price of MySQL Commercial Edition, and Oracle no longer supported the development of another free software project, OpenSolaris. Because of this, the free software community became worried about whether Oracle would continue to support MySQL Community Edition, the only free version among MySQL's releases. So MySQL founder Michael Widenius created the MariaDB branch based on MySQL, and named it after his daughter. Many companies that originally used MySQL, such as Wikipedia, have already finished moving from MySQL to MariaDB.

Installing MySQL

Windows Environment

Download the MySQL Community Server installer through the download link provided by the official website. It is recommended that everyone download the offline version of MySQL Installer.
Run Installer and follow the steps below to install it.
- choose custom installation
- choose the components that need to be installed
- if dependencies are missing, install them first
- get ready to start installation
- installation finished
- get ready to run the configuration wizard
Run the configuration wizard after installation.
- configure the server type and network
- configure the authentication method (the way to protect the password)
- configure users and roles
- configure the Windows service name and whether it starts automatically when the computer starts
- configure logs
- configure advanced options
- apply the configuration
You can start or stop MySQL in the Windows "Services" window.
Configure the PATH environment variable, so that you can use MySQL command-line tools in the Command Prompt window.
- open the Windows "System" window and click "Advanced system settings"
- in the "Advanced" tab of "System Properties", click the "Environment Variables" button
- modify the PATH environment variable, and add the path of the bin folder under the MySQL installation path into the PATH environment variable
- after configuration is finished, you can try using MySQL command-line tools in the Command Prompt

Linux Environment

The example below uses CentOS 7.x to show how to install MySQL 5.7.x. If you need to install another version of MySQL on another Linux system, please look up the corresponding installation tutorial yourself.

Install MySQL.

You can download the installation files from the official MySQL website. First choose the platform and version on the download page, then find the corresponding download link, directly download the archive file that contains all installation files, and after extracting it, use the package management tool to install it.
```
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar
```
If there are MariaDB-related files on the system, you need to remove them first.
```
yum list installed | grep mariadb | awk '{print $1}' | xargs yum erase -y
```
Update and install the low-level dependency libraries that may be needed.
```
yum update
yum install -y libaio libaio-devel
```
Next, follow the order below and use the RPM (Red Hat Package Manager) tool to install MySQL.
```
rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
```
You can use the command below to see the installed MySQL-related packages.
```
rpm -qa | grep mysql
```

Configure MySQL.

MySQL's configuration file is located in /etc and is named my.cnf. Its default contents are shown below.

cat /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Through the configuration file, we can modify settings such as the port used by the MySQL service, the character set, the maximum number of connections, the socket queue size, the maximum packet size, the position of log files, and log expiration time. Of course, we can also use the configuration file to tune the performance of the MySQL server and control security.

Start the MySQL service.

You can use the command below to start MySQL.
```
service mysqld start
```
In CentOS 7, the command below is more recommended.
```
systemctl start mysqld
```
After MySQL starts successfully, you can use the following command to check network port usage. MySQL uses port 3306 by default.
```
netstat -ntlp | grep mysql
```
You can also use the following command to check whether there is a process named mysqld.
```
pgrep mysqld
```
Use the MySQL client tool to connect to the server.

Command-line tool:
```
mysql -u root -p
```
Note: When starting the client, the -u option is used to specify the username. MySQL's default super administrator account is root. -p means you will enter a password. If you connect to another host instead of the local machine, you can use -h to specify the host name or IP address of the host you connect to.

If this is the first installation of MySQL, you can use the command below to find the default initial password.
```
cat /var/log/mysqld.log | grep password
```
The part after root@localhost: in the matching log line is the initial password.

After entering the client, you can use the commands below to change the superuser password for root to 123456.
```
set global validate_password_policy=0;
set global validate_password_length=6;
alter user 'root'@'localhost' identified by '123456';
```
Note: Newer MySQL versions do not allow weak passwords by default, so the commands above first change the password-validation policy and minimum password length. In reality, however, we should not use weak passwords, because they carry the risk of brute-force attacks. In recent years, attacks that steal data from databases or hijack databases for Bitcoin ransom have become increasingly common. The most important step to reduce these risks is not exposing the database server directly to the public internet. The best practice is to place the database on an internal network. At a minimum, the database port should not be opened to the public. In addition, the root account password must be protected carefully. Application systems normally should not access the database using root; instead, other accounts with only the necessary permissions should be created.

When you use the client tool again to connect to the MySQL server, you can use the new password. In actual development, to make things easier, you can also choose a graphical client tool to connect to the MySQL server, including:
- MySQL Workbench, the official tool
- Navicat for MySQL, the interface is simple and friendly

macOS

Installing MySQL on macOS is relatively simple. You only need to download the DMG installation file from the official website mentioned above and run it. When downloading, you need to choose the download link based on whether your machine uses an Intel chip or Apple's M1 chip, as shown below.

After the installation is successful, you can find MySQL in "System Preferences". In the screen shown below, you can start and stop the MySQL server, and you can also configure the path of the MySQL core files.

Basic MySQL Commands

Commands for Viewing Information

View all databases

show databases;

View all character sets

show character set;

View all collations

show collation;

View all engines

show engines;

View all binary logs

show binary logs;

View all tables in the current database

show tables;

Get Help

In the MySQL command-line tool, you can use the help command or ? to get help, as shown below.

View the help for the show command.
```
? show
```
View what help contents are available.
```
? contents
```
Get help for functions.
```
? functions
```
Get help for data types.
```
? data types
```

Other Commands

Create a new server connection or rebuild the current connection: connect / resetconnection.
Clear the current input: \c. If you type something wrong, you can use \c in time to clear the current input and start again.
Change the terminator, also called the delimiter: delimiter. The default terminator is ;. You can use this command to change it to another character. For example, to change it to $, you can use the command delimiter $.
Open the system's default editor: edit. After you finish editing, save and close it, and the command line will automatically execute what you edited.
View the server status: status.
Change the default prompt: prompt.
Execute a system command: system. You can put a system command after system and execute it. The system command can also be shortened to \!.
Execute an SQL file: source. After the source command, put the path of the SQL file.
Redirect output: tee / notee. You can redirect the output of commands to the specified file.
Switch database: use.
Show warning messages: warnings.
Exit the command line: quit or exit.

FilesExpand file tree

36.relational-databases-and-mysql-overview.md

Latest commit

History