Linux operation and Architecture Road-mysql

First, database management system

1. RDBMS relational database features

① Two-dimensional table

② Typical products Oracle traditional enterprise, MySQL is the Internet Enterprise products

③ data access via SQL

④ the greatest features, data security is strong (ACID)

2. NoSQL non-relational database features

① is not a negative relational database, it complements the relational database.

② Typical Products: Redis persistent cache, MongoDB, Memcached

③ management does not apply to SQL management, but instead uses some special APIs or data interfaces

Second, MySQL installation method

1, yum installation is convenient, installation speed is fast, can not be customized

2, Binary: Inconvenient installation, decompression can be used, can not be customized

3. Compile and install: customizable, slow to install

Before 5.5:./configure make && make install

After 5.5: CMake gmake

4, customized RPM package, first compile and then make RPM and then make the Yum Warehouse, then yum installation

Simple installation, fast, customizable, long-time customization, comparison replication, large enterprises will choose to customize the rpm

Third, the actual combat compilation installation MySQL-5.6.36

1. Environment

[Email protected] mysql-5.6. $]# cat/etc/redhat-Release CentOS release6.9(Final) [[email protected] MySQL-5.6. $]# uname-R2.6. +-696. El6.x86_64[[email protected] MySQL-5.6. $]#/etc/init.d/iptables Statusiptables:firewall isNot running. [[email protected] MySQL-5.6. $]# getenforce disabled[[email protected] MySQL-5.6. $]# hostname-I172.19.5.54 172.16.1.54

2. Installation

mkdir/server/tools/-PCD/server/tools/wget-Q http://mirrors.sohu.com/mysql/mysql-5.6/mysql-5.6.36.tar.gzTar XF mysql-5.6. $. TAR.GZCD MySQL-5.6. $CMake.-dcmake_install_prefix=/application/mysql-5.6. $ -dmysql_datadir=/application/mysql-5.6. $/Data-dmysql_unix_addr=/application/mysql-5.6. $/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 Installln-s/application/mysql-5.6. $//application/MYSQL\CP/server/tools/mysql-5.6. $/support-files/my*.cnf/etc/my.cnf/application/mysql/scripts/mysql_install_db--basedir=/application/mysql/--datadir=/application/mysql/data-- User=MYSQLCP/server/tools/mysql-5.6. $/support-files/mysql.server/etc/init.d/Mysqldchmod the/etc/init.d/mysqldchkconfig mysqld onchkconfig--List Mysqldmkdir/application/mysql/Tmpchown-R mysql.mysql/application/mysql/Echo'path=/application/mysql/bin/: $PATH'>>/etc/Profilesource/etc/ Profile/etc/init.d/mysqld start

The role of CMake

① custom Software Installation path ② custom MySQL source program and command script bin/mysqld (binary) key daemon source bin/mysqlbin/mysqld_safescripts/mysql.serverbin/ mysqldumpbin/mysqladminsupports-file/mysql_install_db

3. Clean up the database and unwanted users

drop database Test;drop user'Root'@':: 1';d ROP User'Root'@'DB';d ROP User"'@'DB';d ROP User"'@'localhost'; MySQL>show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema |+--------------------+3Rowsinch Set(0.00sec) MySQL>SelectUser,host fromMysql.user;+------+-----------+| user | Host |+------+-----------+| Root |127.0.0.1|| Root | localhost |+------+-----------+2Rowsinch Set(0.00Sec

4, the installation encountered error

Question 1:

error! The server quit without updating PID file (/application/mysql-5.6.36/data/db.pid).

Solve:

Chown-r mysql.mysql/application/mysql-5.6. /

Question 2:

Database startup will prompt that/application/tmp/mysql.sock cannot be found because 5.6. Version 36 does not automatically create the TMP directory

Solve:

Mkdir/application/mysql/tmp

Other FAQs

① permissions. Chown-r mysql.mysql②killall Mysqld③ reinitialization of the data. ④ has been running for 1 years, out of the question (illegal (power off) shutdown or illegal database, such as kill-9)

Iv. MySQL basic command use

Command	Description
show databases;	The query displays all the database information
Create Database Oldboy;	Create a new database
Drop database Oldboy;	Delete the existing database
Use MySQL;	Represents the choice of using a database equivalent to a CD entering a database
Show tables;	View table information in the database
Select Database ();	Represents the ability to view the current database, similar to the PWD command
Select User ();	View the user who is currently logged in to the database, similar to the WhoAmI command
SELECT * from User\g;	View all the information in the user table, and the vertical line displays
Select User,host from user;	View the specified information in the user table and display it in rows
Select User,host from Mysql.user;	View the directory where you can log in to the MySQL database, and where you can manage the MySQL database
Drop user ' user ' @ ' host ';	Delete User
Flush privileges;	Refresh Permissions
Grant all on. * to [e-mail protected] ' host ' identified by ' oldboy123 ';	Create user
Grant all on. * to [e-mail protected] ' localhost ' identified by ' oldboy123 ';	Create user (user name contains uppercase letters)
mysqladmin-u root password ' 123456 ';	Set a password for a user
mysqladmin-uroot-p123456 Password 654321	Change the password, if you know the old password
Delete from Mysql.user where user= "root" and host= "MySQL";	In case of host name capitalization, special characters cannot be deleted using drop

Linux operation and Architecture Road-mysql