MARIADB First Knowledge

MYSQLI. Database Fundamentals 1. Legacy File System Management deficiencies

writing applications is inconvenient;
Data redundancy is unavoidable;
application dependencies;
Concurrent access to files is not supported;
weak connection between data;
It is difficult to represent data by user view;
No stage safety control function.

2. Advantages of the database management system

A collection of interrelated data;
less data redundancy;
Procedures and data are independent of each other;
Ensure the safety and reliability of the data;
To ensure the correctness of the data to the fullest extent;
Data can be used concurrently and be consistent at the same time.

3. Database management System

A database is a collection of data that is stored on a storage medium in a certain form.
DBMS is the system software that manages database, it realizes various functions of database system. Is the core of the database system
DBA: Responsible for database planning, design, coordination, maintenance and management work
Applications refer to database-based applications;

stand-alone architecture;
Large host/terminal architecture;
Master-Slave distributed (c/s); Mysql,orical
Distributed architecture;

4. Relational Data Key/value

Database
Relationship: A relationship is a two-dimensional table. and meet the following properties:
The row and column order in the table is not important
Row row: Each row in the table, also known as a record
Column columns: Each column in a table, called a property, field
Primary KEY (Primary key): A field used to uniquely determine a record
Domain domains: The value range of a property, such as, gender can only be ' male ' and ' female ' two values
Foreign key (Foreign key): a one-to-many relationship between tables
Unique key (Uniq key): Can be null,
non-relational database : NO SQL (not only SQL)
mencached Redis Mogodb
RDBMS:
Mysql:mysql, MariaDB, Percona Server
PostgreSQL: referred to as Pgsql,enterprisedb
Oracle:
MSSQL:
DB2:
transaction tansaction: Multiple operations are treated as a whole
ACID:
A: atomicity
C: Consistency
I: Isolation
D: Persistence
The transaction is not revoked and the data is formed: dirty

5. Three elements of data

Data:

? One is the object related to data type, content and nature, such as domain, attribute and relation in relational model;
The other is the object associated with the data, which expresses the structure of the data records and fields from the data organization layer.

operation of the data :

? Extract: Extracts the content of interest in the data collection. SELECT
Data update: Changes the data in the database. INSERT, DELETE, UPDATE

constraints on data :
is a set of integrity rules:

? Entity (row) Integrity entities Integrity
field (column) integrity domain Integrity
Referential integrity referential Integrity

6. Normalization Analysis of databases

rdmbs Design paradigm Basic Concept ---physical layer

? When designing the relational database, we should design a reasonable relational database to conform to different specifications, and these different norm requirements are called different paradigms, and the higher paradigm is the sub-norm, the smaller the database redundancy is.

There are currently six paradigms for relational databases:---logic layer

? The first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the Badesco paradigm (BCNF), the fourth normal form (4NF) and the fifth normal (5NF, also known as the perfect paradigm). The paradigm that satisfies the minimum requirements is the first paradigm (1NF). The second paradigm (2NF) is further satisfied on the basis of the first paradigm, and the rest of the paradigms are referred to by analogy. In general, the database only needs to meet the third normal form (3NF)
Paradigm
1NF: No duplicate columns, each column is an indivisible base data item, and there cannot be multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties. Remove a field of the same type, which is a column with no duplicates
Description: The first paradigm (1NF) is the basic requirement of the relational model, and the database that does not satisfy the first normal form (1NF) is not a relational database;

? 2NF: The property is completely dependent on the primary key, and the second paradigm must first satisfy the first paradigm, requiring each row in the table to be divided by a unique region. Usually a table with a column to store each instance unique identity PK, non-PK fields need to have a direct correlation with the entire PK;

? 3NF: The attribute does not depend on other non-principal attributes, and the second normal form must be satisfied before the third paradigm. The third paradigm requires that a database table does not contain non-primary keyword information already contained in other tables, and that non-PK fields cannot have dependencies;
For performance, some databases do not satisfy the normal paradigm and increase the redundancy of the database.

7.MYSQL concept

Historical development
1979: TCX Company Monty Widenius,unireg
1996: Released Mysql1.0,solaris version, Linux version
1999: MySQL AB Company, Sweden
2003: MySQL 5.0 version, providing features such as views, stored procedures, etc.
2008: Sun Acquisition
2009: Oracle acquires Sun
2009: Monty established MARIADB

Two. Installing MARIADB

Yum installs the MARIADB, the system CD with 5.5

]#yum install mariadb-server -y -q]#rpm -q --scripts mysql-server #安装前脚本]#ll /var/lib/mysql/

Source Code Compilation Installation mariadb-10.2.15

] #yum Groupinstall "Development Tools" #yum install ncurse-devel openssl-devel-y] #yum install-y CMake] #useradd-R-d/d Ata/mysqldb-s/sbin/nologin MySQL] #mkdir/DATA/MYSQLDB-PV] #chown mysql.mysql/data/mysqldb] #chmod 770/data/mysqldb]# TAR-XF mariadb-10.2.15.tar.gz] #cd mariadb-10.2.15/] #cmake-dcmake_install_prefix=/app/mysql-dmysql_datadir=/data/ mysqldb/-dsysconfdir=/etc-dmysql_user=mysql-dwith_innobase_storage_engine=1-dwith_archive_storage_engine=1- Dwith_blackhole_storage_engine=1-dwith_partition_storage_engine=1-dwithout_mroonga_storage_engine=1-dwith_ Debug=0-dwith_readline=1-dwith_ssl=system-dwith_zlib=system-dwith_libwrap=0-denabled_local_infile=1-dmysql_ UNIX_ADDR=/APP/MYSQL/MYSQL.SOCK-DDEFAULT_CHARSET=UTF8-DDEFAULT_COLLATION=UTF8_GENERAL_CI] #make-j 3 && Make install && for i in {1..5};d o echo-e "\a";d One] #cd/app/mysql] #scripts/mysql_install_db--datadir=/data/my Sqldb--user=mysql--basedir=/app/mysql] #cp/app/mysql/support-files/my-hUGE.CNF/ETC/MY.CNF] #vim/etc/my.cnf[mysqld]datadir =/data/mysqldb] #setfacl-R-M u:mysql:rwx/app/mysql] #cp/app/mys QL/SUPPORT-FILES/MYSQL.SERVER/ETC/INIT.D/MYSQLD] #chkconfig--add mysqld] #service mysqld start

centos7.4 Binary Installation mariadb-10.2.15

] #useradd-R-d/data/mysqldb-s/sbin/nologin MySQL] #tar xvf mariadb-10.2.15-linux-x86_64.tar.gz-c/usr/local] #cd/usr/ Local] #ln-s mariadb-10.2.15-linux-x86_64/mysql] #chown-R root:root/usr/local/mysql/#修改文件的所属组] #ls/usr/local/mysql/ Bin] #echo Path=/usr/local/mysql/bin: $PATH >/etc/profile.d/mysql.sh] #echo '---' >/sys/class/scsi_host/host2/ Scan] #lsblk] #pvcreate/dev/sdb] #vgcreate vg0/dev/sdb] #lvcreate-n lv_mysql-l 100%free vg0] #mkfs. xfs/dev/vg0/lv_mysql   ] #vim/etc/fstab #增加自动挂载] #mount-a] #mkdir/DATA/MYSQLDB-PV] #chown mysql.mysql/data/mysqldb] #chmod 770/data/mysqldb #改变权限, safer] #cd/usr/local/mysql/] #setfacl-R-M u:mysql:rwx/usr/local/mysql/] #scripts/mysql_install_db--datadir=/ DATA/MYSQLDB--user=mysqlinstalling mariadb/mysql system tables in '/data/mysqldb ' ...  OK ] #ll/data/mysqldb/#生成数据库文件] #cp/etc/my.cnf/etc/my.cnf.bak] #cp/usr/local/mysql/support-files/my-huge.cnf/etc/ MY.CNF] #vim/etc/my.cnf [Client]socket=/tmp/mysql.sock[mysqld]datadir=/data/mysQldbsocket=/tmp/mysql.sock] #cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld] #chkconfig--add mysqld]# Service mysqld startstarting mysqld (via Systemctl): [OK]] #mysql_secure_installatio N #安全加护

Start the service

]#systemctl start mariadb]#mysqlMariaDB [(none)]>drop database mysqlclone; #删除数据库MariaDB [(none)]>use mysql;MariaDB [mysql]> statusMariaDB [mysql]> create database testdb;Query OK, 1 row affected (0.00 sec)MariaDB [mysql]> \! ls /var/lib/mysqlMariaDB [mysql]> select user,password,host from user;]#mysql_secure_installation     #设置mysql密码]#mysql -uroot -p              #输入密码MariaDB [mysql]> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema |+--------------------+MariaDB [mysql]> select user,host,password from user;MariaDB [mysql]> select * from user\G;MariaDB [mysql]>quit

Interactive commands

Input redirection can be accepted. Can source

]#mysql -uroot -proot < test.sqlMariaDB [(none)]> source test.sql

Client commands

The

can be used without adding ";" Closing sentence

Note that all text commands must is first on line and end with '; '? (\?) Synonym for "help". Clear (\c) clear the current input statement.connect (\ r) reconnect to the server.       Optional arguments is DB and Host.delimiter (\d) Set statement Delimiter.edit (\e) Edit command with $EDITOR. Ego (\g) Send command to MySQL server, display result Vertically.exit (\q) exit MySQL. Same as Quit.go (\g) Send command to MySQL server.help (\h) Display this help.nopager (\ n) Disable Pager, PR int to stdout.notee (\ t) Don ' t write into Outfile.pager (\p) Set Pager [To_pager]. Print the query results via Pager.print (\p) print current command.prompt (\ r) Change your MySQL prompt.quit ( \q) Quit Mysql.rehash (\#) Rebuild completion Hash.source (\.) Execute an SQL script file. Takes a file name as an argument.status (\s) Get status information from the Server.system (\!) Execute a system shell command.tee (\ t) Set outfile [To_ouTfile]. Append everything into given outfile.use (\u) use another database. Takes database name as Argument.charset (\c) Switch to another charset. Might is needed for processing binlog with Multi-Byte charsets.warnings (\w) Show warnings after every statement.nowarnin G (\w) Don ' t show warnings after every statement.

modifying environment PS1 environment variables

1.shell environment variables; 2. command-line Options change mariadb_ps1;3./etc/my.cnf.d/mysql-clients.cnf file modification

MariaDB [(none)]> prompt \[email protected][\D]--->PROMPT set to ‘\[email protected][\D]--->‘[email protected][Mon Jun  4 23:08:30 2018]--->]#vim /etc/profile.d/mysql.shexport MYSQL_PS1="(\[email protected]\h) [\d]> "]#. /etc/profile.d/mysql.sh]#mysql --prompt="(\[email protected]\h) [\d]> "]#vim /etc/my.cnf.d/mysql-clients.cnf [mysql]prompt=(\\[email protected]\\h) [\\d]>\\_

MySQL Client options

mysql客户端可用选项：-A, --no-auto-rehash 禁止补全-u, --user=  用户名,默认为root-h, --host=  服务器主机,默认为localhost-p, --passowrd=  用户密码,建议使用-p,默认为空密码-P, --port=  服务器端口-S, --socket= 指定连接socket文件路径-D, --database= 指定默认数据库-C, --compress  启用压缩-e "SQL" 执行SQL命令-V, --version 显示版本-v --verbose 显示详细信息--print-defaults 获取程序默认使用的配置]#mysql -e "show databases;"+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               |+--------------------+

MARIADB Multi-instance implementation

] #yum install mariadb-server-y] #mkdir/mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data}-PV] #chown-R mysql.mysql/ mysqldb/] #mysql_install_db--datadir=/mysqldb/3306/data--user=mysql] #mysql_install_db--datadir=/mysqldb/3307/ Data--user=mysql] #mysql_install_db--datadir=/mysqldb/3308/data--user=mysql] #vim/etc/my.cnf[mysqld]port= 3306datadir=/mysqldb/3306/datasocketdir=/mysqldb/3306/socket/mysql.sock[mysqld_safe]log-error=/mysqldb/3306/ Log/mariadb.logpid-file=/mysqldb/3306/pid/mariadb.pid] #cp/etc/my.cnf/mysqldb/3306/etc/] #cp/etc/my.cnf/mysqldb/ 3307/ETC/] #cp/etc/my.cnf/mysqldb/3308/etc/] #vim/mysqldb/3307/etc/my.cnf] #vim/mysqldb/3308/etc/my.cnf:%s/3306/ 3307/G:%S/3306/3308/G] #vim/mysqldb/3306/mysqld #编辑启动脚本 #!/bin/bashport=3306mysql_user= "root" mysql_pwd= "" Cmd_path = "/app/mysql/bin" mysql_basedir= "/mysqldb" mysql_sock= "${mysql_basedir}/${port}/socket/mysql.sock" Function_start _mysql () {if [!-e "$mysql _sock"];then printf "starting mysql...\n" ${cmd_path}/mysqLd_safe--defaults-file=${mysql_basedir}/${port}/etc/my.cnf &>/dev/null & Else printf "MySQL is Runn       ing...\n "Exit Fi}function_stop_mysql () {if [!-e" $mysql _sock "];then printf" MySQL is stopped...\n " Exit Else printf "stoping mysql...\n" ${cmd_path}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-S ${mysql _sock} shutdown fi}function_restart_mysql () {printf "restarting mysql...\n" Function_stop_mysql sleep 2 func Tion_start_mysql}case $ instart) function_start_mysql;; stop) Function_stop_mysql;; restart) Function_restart_mysql;; *) printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n" Esac] #chmod 700/mysqldb/{ 3306,3307,3308}/mysqld]#/mysqldb/3306/mysqld start]# ss-ntlstate recv-q send-q Local address:port Peer Add      Ress:port LISTEN 0 *:3307 *:* LISTEN 0   50 *:3308              *:* LISTEN 0 *:111 *:* L                     Isten 0 5 192.168.122.1:53 *:* LISTEN 0 128                  *:22 *:* LISTEN 0 127.0.0.1:631 *:*                    LISTEN 0 *:3306 *:* LISTEN 0 128                  ::: 111:::* LISTEN 0 128::: 22 :::* LISTEN 0:: 1:631:::*] #mysql-S/MYSQLDB/3306/S OCKET/MYSQL.SOCKMARIADB [(None)]> show variables like '%port% '; +-------------------------------------+-------+| variable_name | Value |+-------------------------------------+-------+| Extra_port | 0 | | Innodb_imporT_table_from_xtrabackup | 0 | | Innodb_support_xa | On | | Large_files_support | On | | Port | 3306 | | Progress_report_time | 5 | |       Report_host | ||       Report_password | || Report_port | 3306 | |       Report_user | |+-------------------------------------+-------+10 rows in Set (0.00 sec) MariaDB [(None)]> Select User,host, Password from Mysql.user; MariaDB [(none)]> update mysql.user set Password=password ("CentOS") where user= "root";    MariaDB [(None)]> flush privileges; #使密码生效MariaDB [(None)]> \s #等价status

Mariadb First Knowledge