2, mysql installation and startup

1. How to install mysql

1.RPM, Yum: easy to install, fast to install, unable to customize
 2. Binary: it does not need to be installed, can be used after decompression, and cannot be customized
 3. Compile and install: customizable, slow to install.
Before 5.5:. / configure make make install
 After 5.5: cmakegmake
 4. Compile first, then make rpm, make Yum library, and then install yum.
Simple, fast, customizable, relatively complex production time is long
 Enterprises choose installation mode
 Small and medium-sized enterprises: all of the above methods are OK. Operation and maintenance prefer to compile, and dba prefer to choose binary.
Large enterprises: 4

2. mysql installation

First, MySQL5.6.36 Preparation before installation
(1)Clone a template machine (using centos6)，Snapshot after clone
(2)IP 10.0.0.52 host name db02
(3)iptables   selinux (Off)
(4)Download good 5.6.36
(5)Install dependency package
     yum  install  -y  ncurses-devel libaio-devel
(6)install cmake
	yum install cmake –y
(7)Create user
    useradd -s /sbin/nologin -M mysql
	id mysql
//2, MySQL download and installation
(0)To create a software download directory:
mkdir -p /server/tools
cd /server/tools/
(1)Download and upload to/server/tools
https://www.mysql.com/downloads/
//Community MySQL Community Server
(2)Unzip:
cd /server/tools
tar xf mysql-5.6.36.tar.gz
(3)Installation:
cd mysql-5.6.36
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.36 \
-DMYSQL_DATADIR=/application/mysql-5.6.36/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.36/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

make && make install

//3, Configure and start
(1)Making a soft connection:
[root@centos6-kvm3 mysql-5.6.36]# ln -s /application/mysql-5.6.36/ /application/mysql
(2)Copy profile to/etc:
[root@centos6-kvm3 support-files]# cp my-default.cnf /etc/my.cnf 
(3)Initialize database:
[root@centos6-kvm3 support-files]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql
(4)Create key directories and set permissions:
[root@centos6-kvm3 support-files]# mkdir -p /application/mysql/tmp
[root@centos6-kvm3 support-files]# chown -R mysql.mysql /application/mysql/
(5)Copy startup script to/etc/init.d/mysqld
[root@centos6-kvm3 support-files]# cp mysql.server /etc/init.d/mysqld 
(6)Start database
[root@centos6-kvm3 support-files]# /etc/init.d/mysqld start
[root@centos6-kvm3 support-files]# ps -ef | grep mysql
[root@centos6-kvm3 support-files]# netstat -lntp | grep 3306
(7)Configure environment variables
[root@centos6-kvm3 support-files]# echo 'PATH=/application/mysql/bin/:$PATH'>>/etc/profile
[root@centos6-kvm3 support-files]# tail /etc/profile
[root@centos6-kvm3 support-files]# source /etc/profile
[root@centos6-kvm3 support-files]# echo $PATH
/application/mysql/bin/:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[root@centos6-kvm3 support-files]# mysql
mysql> select user,host,password from mysql.user;

3. How client programs connect to MySQL

Connect strings over the network
mysql -uroot -poldboy123 -h 10.0.0.200
 Through socket file
mysql -uroot -poldboy123 -S /tmp/mysql.sock

4. SQL layer processing of mysql:

SQL:
	1. Received "SQL" sent by connection layer
	2. A special module can judge SQL syntax and semantics (SQL statement types: DDL, DCL, DML)
	3. Send different types of statements to special processing interfaces (parsers)
	4. Parsers, parsing SQL into execution plans
	5. Optimizer, will choose "it" the best execution plan to hand over to the executor
	6. Executor, execute execution plan, get the method of how to get data from "disk"
	7. The specialized thread will get the data method and send it to the lower layer (storage engine layer) for further processing.
	8. Verify authorization. The current user has permission to operate on the library or table object.
	9. Query cache.
	10. Record the modification log binlog.
	
mysql table storage
 1. Data file
   Page (16k by default): the smallest unit of mysql database storage
   Area: composed of consecutive multiple pages
   Segment: a table (partition table) is a segment that contains multiple extents

5. mysql common commands

mysql Login command
-u   user name
-p   Password
//example: 
mysql -uroot -poldboy123
-h ip
//example:
[root@centos6-kvm3 support-files]# mysql -uroot -poldboy123 -h 127.0.0.1
[root@centos6-kvm3 support-files]# mysql -uroot -poldboy123 -h localhost
mysql -uroot -poldboy123 -h 10.0.0.52
-P 3308
//example:

mysql -uroot -poldboy123 -h 10.0.0.52 -P 3308
[root@centos6-kvm3 support-files]# mysql -uroot -poldboy123 -P 3306
-S /tmp/mysql.sock
//example:
[root@centos6-kvm3 support-files]# mysql -uroot -poldboy123 -S /application/mysql/tmp/mysql.sock 
mysql -uroot -poldboy123 -S /application/mysql/tmp/mysql.sock
-e "show variables like 'server_id';"
//example:
mysql -uroot -poldboy123  -e "show variables like 'server_id';"
//Change Password:
[root@centos6-kvm3 support-files]# mysqladmin -uroot -p password oldboy123
Enter password: 

6. Clean up infrequently used databases

select user,host from mysql.user;
drop user ''@'db02';
drop user ''@'localhost';
drop user 'root'@'db02';
drop user 'root'@'::1';
select user,host from mysql.user;
drop database test;
show databases;