Chapter 1 Database Classification

1. Relational database
There are many two-dimensional tables in the database, one table, two tables, three tables, one hundred tables and so on. There may be associations between multiple tables.

2. Non-relational databases
Includes multiple database services (multi-instance) 3306/3307/3307.
Each database service instance contains multiple libraries.
Each library contains multiple tables.
Warehouse No. 1: Table 1, Table 2.
Warehouse 2: Table 3, Table 4.
Each table contains multiple rows of data.

3. Database products
MySQL and Oracle databases, both of which belong to Oracle
oracle large traditional enterprises
MySQL Internet Enterprise
access Small (Microsoft)

4. Categorizing different versions of databases

Chapter 2 Relational Database

Features: SQL Structured Query Language manages relational databases!

1. Two-dimensional tables
A relational database actually uses a two-dimensional table when storing data (almost the same as the tables in word and Excel)

2. Market Occupancy
MySQL and Oracle databases account for a large share of the market, while MySQL databases are most commonly used in Internet scenarios.

3. SQL Structured Query Language
It accesses and manages the data of relational database through SQL structured query language.

4. High security consistency
Relational database is very strong in maintaining data security and data consistency, and it follows ACID theory.

Chapter 3 Non-relational databases

NoSQL database is not only sql, not just SQL.
Based on oil head: only focus on high performance, high concurrency.
Memcached pure memory software. Rsdis memory can also be persisted
Managing NoSQL is no longer a structured query language for SQL
Data representation: It's not a two-dimensional table, nor is management using SQL statements.
Non-relational database (NOSQL) is just a summary:

1. Supplementary to the database
NOSQL database is not a negation of relational database, but an important supplement to relational database.

2. Focus on high performance and high concurrency
NOSQL database is created for flexibility, high performance and high concurrency, neglecting the functions that affect high performance and high concurrency.

3. Typical product Redis
In the field of NOSQL database, the most typical products today are Redis (persistent cache), Mongodb, memcached (pure memory), etc.

4. No Standard Query Language
NoSQL database does not have a standard query language (SQL), and it always uses REST-style data interface or query API.

Chapter 4 Principles of MySQL Workflow

Chapter 5 MySQL Installation Steps

1. yum/rpm cannot be customized
2. 5.0 product configure, make, make install
3. 5.6 Products. / cmake, make, make install
4. Binary, decompression can be
5. Compile and install the source code to make rpm package, and then install it in yum warehouse*

1. Source installation package download

wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.37.tar.gz

2. Installing dependency packages

yum install ncurses-devel libaio-devel gcc-c++ -y 

3. cmake installation

yum install  -y cmake

4. Adding Users

useradd -s /sbin/nologin -M mysql

5. Unzip installation package

tar xf mysql-5.6.37.tar.gz
cd mysql-5.6.37/

6. cmake configuration

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.37 \
-DMYSQL_DATADIR=/application/mysql-5.6.37/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.37/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0

#For more information, refer to the official MySQL 5.6 Of cmake Compilation parameters http://dev.mysql.com/doc/refman/5.6/en/source-#configuration-options.html

7. Compiling and compiling installation

make && make install

8. Create soft links

ln -s  /application/mysql-5.6.37/ /application/mysql

9. Copy configuration files

cp support-files/my*.cnf /etc/my.cnf

10. Initialize the database

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql

11. Change Owner Group Authorization

chown -R mysql.mysql /application/mysql/

12. Configure startup scripts

cp support-files/mysql.server /etc/init.d/mysqld

13. Change permission 700

chmod 700 /etc/init.d/mysqld

14. Add boot-up and self-start

chkconfig mysqld on
chkconfig --list mysqld

15. Start MySQL

sed -i '/socket/a socket = /application/mysql-5.6.37/data/mysql.sock' /etc/my.cnf
/etc/init.d/mysqld start

[root@mysql-51 mysql]# ss -lntup|egrep 3306
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=24588,fd=10))

16. Setting MySQL environment variables

echo 'PATH=/application/mysql/bin/:$PATH' >>/etc/profile
tail -1 /etc/profile
source /etc/profile
echo $PATH
mysql -S /application/mysql-5.6.37/data/mysql.sock

17. root User Sets Password

mysqladmin -uroot password '123456' -S /application/mysql/data/mysql.sock
mysql -uroot -p123456 -S /application/mysql/data/mysql.sock

Chapter 6 MySQL Multi-Instance Installation

1. Stop the single instance first

/etc/init.d/mysqld stop

2. Upload data-5.6-new-book.zip multi-instance package

unzip data-5.6-new-book.zip
Archive:  data-5.6-new-book.zip
   creating: data/
   creating: data/3306/
  inflating: data/3306/my.cnf        
  inflating: data/3306/mysql         
   creating: data/3307/
  inflating: data/3307/my.cnf        
  inflating: data/3307/mysql  

[root@mysql-51 ~]# tree /data/
/data/
├── 3306
│   ├── my.cnf
│   └── mysql
└── 3307
    ├── my.cnf
    └── mysql

3. Configuration file distinction

vimdiff /data/3306/my.cnf /data/3307/my.cnf  #Only differences between ports
mkdir /data/{3306,3307}/data -p
chown -R mysql.mysql /data/

4. Initialization of the database

cd /application/mysql/scripts
./mysql_install_db  --defaults-file=/data/3306/my.cnf --basedir=/application/mysql/ --datadir=/data/3306/data --user=mysql
./mysql_install_db  --defaults-file=/data/3307/my.cnf --basedir=/application/mysql/ --datadir=/data/3307/data --user=mysql

/data/3306/mysql start
/data/3307/mysql start
ss -lntup|egrep 330
mysql -S /data/3306/mysql.sock 
mysql -S /data/3307/mysql.sock

[root@mysql-51 3307]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1/systemd           
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1243/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1776/master         
tcp6       0      0 :::3306                 :::*                    LISTEN      17831/mysqld        
tcp6       0      0 :::3307                 :::*                    LISTEN      19338/mysqld        
tcp6       0      0 :::111                  :::*                    LISTEN      1/systemd           
tcp6       0      0 :::22                   :::*                    LISTEN      1243/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      1776/master         

Chapter 7 Adding 3308 Database

mkdir -p /data/3308/data
\cp /data/3306/my.cnf  /data/3308/
\cp /data/3306/mysql  /data/3308/
sed -i 's/3306/3308/g' /data/3308/my.cnf 
sed -i 's/server-id = 6/server-id = 8/g' /data/3308/my.cnf 
sed -i 's/3306/3308/g' /data/3308/mysql
chown -R mysql:mysql /data/3308
chmod 700 /data/3308/mysql
cd /application/mysql/scripts
./mysql_install_db --defaults-file=/data/3308/my.cnf --datadir=/data/3308/data --basedir=/application/mysql --user=mysql
chown -R mysql:mysql /data/3308
egrep "server-id|log-bin" /data/3308/my.cnf 
/data/3308/mysql start
sleep 5
netstat -lnt|grep 3308

Chapter 8 MySQL Startup and Close

1. Single instance startup and shutdown

##<== First use MySQL's own management script
/etc    /init.d/mysqld stop

##<== The second is mysqladmin management method
mysqladmin -uroot -poldboy123 shutdown  ##The biggest obstacle to this command is the need to know the password beforehand.

##<== The third method for utilizing system process management
kill pid         ##<== Here the pid is the process number corresponding to the database service
killall mysqld   ##<== Here mysqld is the process name corresponding to the database service
pill mysqld      ##<== Here mysqld is the process name corresponding to the database service

2. Multi-instance startup and shutdown

The command to start the 3306 sample command service is / data/3306/mysql start, which is actually msqld_safe with different sample configuration file parameters. The example is as follows:
mysqld_safe --default-file=/data/my.cnf 2>&1 >/dev/null &

The stop 3306 example command is / data/3306/mysqld stop, which actually uses the mysqladmin command method. The example is as follows:
mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock shutdown

Chapter 9 MySQL login security optimization

1. Interactive

[root@mysql_52 ~]# mysql -uroot -p
Enter password:
[root@db02 ~]# HISTCONTROL=ignorespace Ignores commands that start with spaces and place them in environment variables
[root@db02 ~]#  mysql -uroot -poldboy123

2. Non-interactive

[root@mysql_52 ~]# mysql -uroot -poldboy123

3. Write configuration files

[root@mysql_52 ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
user=root
password=oldboy123
[root@mysql_52 ~]# chmod 600 /etc/my.cnf   
[root@mysql_52 ~]# ll /etc/my.cnf 
-rw------- 1 root root 1164 Jun 20 16:04 /etc/my.cnf

Chapter 10 MySQL User Password Retrieval

[root@mysql_52 ~]# mysqld_safe  --skip-grant-table --user=root &
[root@mysql_52 ~]# mysql
mysql> update mysql.user set password=password('oldboy123') where user='root' and host='localhost';
mysql> flush privileges;

Chapter 11 Notes for MySQL

• Set more complex passwords for root users.
• Delete user accounts from useless mysql libraries, leaving only root@localhost and root@127.0.0.1
• Delete the default test database
• When adding users, the authorized permissions are as small as possible, and the host range allowed to access is minimized.
• The login command line prompt does not carry a password, but returns to enter a password, using interactive.