2-14 MySQL preliminary understanding, and CentOS6.8 environment, source way to install MySQL

What is a database: The warehouse where the data resides
rdbms--> (relational database Management system) relational databases management systems
DBMS---> Database management systems (Managerment System)

Database classification:
1. Relational database
2. Non-relational database
Common relational database:
Oracle,mysql,mariadb,sql Server,access,postgresql,db2,informix,sybase
PostgreSQL free Object-relational database server (database management system), with very active development team (hacker support)
Common non-relational databases:
Memcache,redis,mongodb,cassandra
Other infrequently used non-relational databases:
HBase, Memachedb, BerkeleyDB, Tokyo Cabinet

##################################################
##################################################
##################################################
Mysql

MySQL is a relational database management system,
Developed by the Swedish MySQL AB company, currently owned by Oracle Corporation

Characteristics:
1. mysql performance is excellent, service is stable, and few abnormal outages occur.
2. mysql open source free, no copyright restrictions, autonomy and low cost of use
3. Product coupling, MySQL supports multiple operating systems, supports multi-language development, especially PHP
4. The accumulation of technology, a large and active community,
5. The software is small in size, easy to install and easy to maintain
url:http://www.mysql.com/

MySQL adopts dual authorization policy
Community Edition and Commercial edition
Also subdivided into ALPHA,BETA,RC,GA versions
GA for the official version

Community Edition, Enterprise Edition difference--->
1. Price, Community edition free, Enterprise Edition high Fee
2. Technology, Enterprise Edition with MySQL Enterprise-Class Server, system monitoring tools, after rigorous testing and certification
The Community edition isn't that strict.
3. Service, Community Edition does not provide technical support, Enterprise Edition conversely
4. Agreement, Commercial Edition does not support GPL protocol

Product line:
1.5.0.XX-5.1.XX Series
This product line continues to improve the user experience and performance,
While adding new features, it is the continuation of MySQL's early products,
such as the rhel6 comes with MySQL
2.5.4.XX-5.7.XX Series
MySQL community and third-party companies Percona integrated,
Absorbing new algorithms and storage engines
3.6.0.XX-7.X.XX Series
Mainly in order to better promote the MySQL cluster version,
Provides performance and stability for the cluster version,
This version is less company, because it's too late.

MySQL Installation:
1:rpm/yum Installation
#使用二进制方式安装, developers for different operating systems and environments wearing pieces of RPM package, by downloading the direct installation
RPM-IVH *.rpm
Yum-y Install Mysql-server
# MySQL Login program for client
# Mysql-server Database Server

2: Source Compilation installation
Tar---> cmake---> Make---> Make install

3: Batch deployment, src.rpm through spec files
# not studied.

Source code compilation Install MySQL 5.5.32
# for non-version source code compilation method is different
# no need to use CMake for MySQL 5.1 series
#./configure---Make-install
1. Install the compilation tool CMake
Yum-y Install CMake gcc gcc-c++
# CMake depends on C language needs to install C compiler tool under Linux gcc gcc-g++/c++

2. Download MySQL
# Go to official website to download
wget http://cdn.mysql.com/archives/mysql-5.5/mysql-5.5.32.tar.gz
Unzip to the specified directory
TAR-ZXF mysql-5.5.32.tar.gz-c/USR/LOCAL/SRC
3. Since the settlement
Yum-y Install CMake ncurses-devel #用于终端操作的开发包
4. Create a MySQL user
Useradd-u 8001-m-s/sbin/nologin MySQL
#-U 8001 specify UID,-m do not create host directory,-s/sbin/nologin prohibit login
5. Create a Database storage directory
Mkdir/data # Easy to Mount,
# Store database data files with System sub-disks or partitions to avoid IO impact
6. Compiling
Cmake-dcmake_install_prefix=/usr/local/mysql-dmysql_unix_addr=/tmp/mysql.sock-ddefault_charset=utf8-ddefault_ Collation=utf8_general_ci-dmysql_extra_charsets=all-dwith_myisam_storage_engine=1-dwith_innobase_storage_ Engine=1-dwiht_memory_storage_engine=1-dwith_readline=1-denabled_local_infile=1-dmysql_datadir=/data-dmysql_ User=mysql
Parameter explanation:
-dcmake_install_prefix=/usr/local/mysql #指定安装目录为/usr/local/mysql
-dmysql_unix_addr=/tmp/mysql.sock #指定套接字文件为mysql. Sock communication file
-ddefault_charset=utf8 #指定字符集为utf8
-ddefault_collation=utf8_general_ci #指定字符校对
-dmysql_extra_charsets=all #扩展字符支持 Default All
-dwith_storage_storage_engine=1 #开启存储storage引擎支持
# STORAGE=MYISAM,INNOBASE,MEMORY,CVS, etc.
-dwith_readline=1 #开启READLINE模式
-dwith_local_infile=1 #启动加载本地数据引擎
-dmysql_datadir=/data #指定数据存储目录
-dmysql_use=mysql #指定运行用户
Extended:
-dwith_partition_storage_engine=1 #支持分区表
-dinstall_libdir=dir_name #指定库文件安装目录
-dsysconfdir=dir_name #指定默认配置文件存放路径
# Default configuration file is My.cnf
7. Installation
Make-j 4 && make install #-J 4 Specify 4 threads to complete this operation, do not specify a default of 1
8. Authorized Users
Chown-r Mysql:mysql/usr/local/mysql
Chown-r Mysql:mysql/data
chmod 1777/tmp
9. Create a configuration file
Cp/usr/local/mysql/support-files/my-large.cnf/etc/my.cnf
10. Set the environment variable #为了以后便于敲命令 without a path
Echo ' Path=/usr/local/mysql/bin: $PATH ' >>/etc/profile
SOURCE $! # $! = Last parameter of previous instruction
11. Create a startup script
Copy Startup scripts
Cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
Add Script executable permissions
chmod + x $!
Set the script MySQL installation directory and data directory
Sed-i "S%^basedir=%basedir=/usr/local/mysql%g"/etc/init.d/mysqld
Sed-i "S%^datadir=%datadir=/data%g"/etc/init.d/mysqld
Add Script Chkconfig
Chkconfig--add mysqld
Add Boot Boot
Chkconfig mysqld on
12. Initializing the database
Before you run the database, you need to initialize the data
chmod +x/usr/local/mysql/scripts/mysql_install_db
/usr/local/mysql/scripts/mysql_install_db--defaults-file=/etc/my.cnf
--basedir=/usr/local/mysql--datadir=/data--user=mysql

# If the database was run before initialization, an error would be as follows:
[[Email protected] ~]# service mysqld start
Starting MySQL. error! The server quit without updating PID file (/data/xiaogan63.cn.pid).
#
[Email protected] ~]#/usr/local/mysql/scripts/mysql_install_db--defaults-file=/etc/my.cnf--basedir=/usr/local/ MySQL--datadir=/data--user=mysql

TAL error:could not find/bin/my_print_defaults

If you compiled from source, you need to run ' make install ' to
Copy the software into the correct location ready for operation.

If you is using a binary release, you must either is at the top
Level of the extracted archive, or pass the--BASEDIR option
Pointing to the location.
# No environment variables set

[Email protected] ~]#/usr/local/mysql/scripts/mysql_install_db--defaults-file=/etc/my.cnf--basedir=/usr/local/ MySQL--datadir=/data--user=mysql
Installing MySQL system tables ...
Ok
Filling Help Tables ...
Ok

Success!

Because the startup script is wrong.
Sed-i command incorrectly replaced string cause MYSQLD service failed to start
The error is as follows:
[[Email protected] ~]# service mysqld start
Starting MySQL. error! The server quit without updating PID file (/data/xiaogan63.cn.pid)
Re-copy the configuration file and modify the following:
Sed-i "S%^basedir=%basedir=/usr/local/mysql%g"/etc/init.d/mysqld
Sed-i "S%^datadir=%datadir=/data%g"/etc/init.d/mysqld
The replacement string is missing a symbol ^

[[email protected] ~]# grep "^basedir="/etc/init.d/mysqld-r--color
Basedir=/usr/local/mysql
[[email protected] ~]# grep "^datadir="/etc/init.d/mysqld-r--color
Datadir=/data
[Email protected] ~]#
[[email protected] ~]# grep "basedir="/etc/init.d/mysqld-r--color
# basedir=<path-to-mysql-installation-directory>
Basedir=/usr/local/mysql
Basedir=/usr/local/mysql
# *not* set inside of the--basedir= handler.)
--basedir=*) basedir= ' echo ' $arg | Sed-e ' s/^[^=]*=//'
[[email protected] ~]# grep "datadir="/etc/init.d/mysqld-r--color
Datadir=/data
Datadir=/data
Datadir= "$basedir/data"
Datadir= "$basedir/data"
--datadir=*) datadir= ' echo ' $arg | Sed-e ' s/^[^=]*=//'
$bindir/mysqld_safe--datadir= "$datadir"--pid-file= "$mysqld _pid_file_path" $other _args >/dev/null 2>&1 &
[Email protected] ~]#

13. Initialize the Security Configuration
Mysql_secure_installation #安全初始化配置

# If the MYSQLD service is not running, the error will be as follows:
[Email protected] ~]# mysql_secure_installation
Note:running all PARTS of this SCRIPT are RECOMMENDED for all MySQL
SERVERS in PRODUCTION use! Please READ each STEP carefully!
In order to log into MySQL to secure it, we'll need the current
Password for the root user. If you ' ve just installed MySQL, and
You haven ' t set the root password yet, the password would be blank,
So, should just press ENTER here.

Enter current password to root (enter for none):
ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/tmp/mysql.sock ' (2)
You need to start the service

Enter current password to root (enter for none): #直接回车即可
OK, successfully used password, moving on ...

Set root Password? [y/n] Y # set Root password
# Enter Password two times
... success!
Remove anonymous users? [y/n] Y # Remove anonymous users
... success!
Disallow Root login remotely? [y/n] Y #禁止root远程登录
... success!
Remove test database and access to it? [y/n] Y #删除测试数据库
-Dropping test database ...
... success!
-Removing privileges on test database ...
... success!
Reload privilege tables now? [y/n] Y # Reload Authorization table
... success!
All done! If you've completed all of the above steps, your MySQL
Installation should now is secure.

14. Security Optimization Small configuration:
User installation:
Mysql> select User,host from Mysql.user;
# leave only the root user's localhost and 127.0.0.1;
Mysql> Delete from mysql.user where user= ';

We can also delete all users and recreate a super administrator with the following actions:
Delete all Users
Mysql> Delete from Mysql.user;
Grant all permissions to [email protected] ' localhost '
Mysql> Grant all privileges on * * to [e-mail protected] ' localhost ' identified by
' YOURPASSWD ' with GRANT option;
Make configuration effective
mysql> flush Privileges;
View
Mysql> select User,host from Mysql.user;

2-14 MySQL preliminary understanding, and CentOS6.8 environment, source way to install MySQL