Shulou(Shulou.com)06/01 Report--

MySQL database system

MySQL features:

Multiple processes

Multi-user

High performance

High reliability

Ease of use

MySQL AB-- > SUN-- > Oracle

MySQL-- > MariaDB

Relational database RDBMS:

Business: Oracle,Sybase,Infomix,SQL Server,DB2

Open source: MySQL,PostgreSQL,pgsql,EnterpriseDB

Non-relational database NoSQL:

MongoDB,Redis,HBase,memcached

MySQL:

Community Edtion Community Edition

Enterprise Edtion Enterprise Edition

Package format:

.rpm

.exe

.bin (binary)

Source program

RPM package installation method:

Yum-y install mysql-server mysql

Service mysqld start

Netstat-ntpl | grep: 3306

MySQL source code installation + LVM logical volume

Create logical Volume / dev/mysqlvg/mysqllv

Create a partition, change the type to 8e and save exit

[root@RHEL6-Server1 ~] # fdisk-l / dev/sdb

Device Boot Start End Blocks Id System

/ dev/sdb1 1 10443 83883366 8e Linux LVM

[root@RHEL6-Server1 ~] # partprobe / dev/sdb

[root@RHEL6-Server1 ~] # pvcreate / dev/sdb1

[root@RHEL6-Server1 ~] # vgcreate mysqlvg / dev/sdb1

[root@RHEL6-Server1] # lvcreate-L 60G-n mysqllv mysqlvg

[root@RHEL6-Server1 ~] # mkfs.ext4 / dev/mysqlvg/mysqllv

[root@RHEL6-Server1 ~] # mkdir / usr/local/mysql/data-p

[root@RHEL6-Server1 ~] # mount / dev/mysqlvg/mysqllv / usr/local/mysql/data

Configure automatic mount

[root@RHEL6-Server1 ~] # vim / etc/fstab

/ dev/mysqlvg/mysqllv / usr/local/mysql/data ext4 defaults 0 0

[root@RHEL6-Server1] # mount-a

=

1) install Mysql database

[root@RHEL6-Server1] # rpm-e mysql-server mysql

[root@RHEL6-Server1 ~] # yum-y install ncurses-devel

Install the configuration tool cmake

[root@RHEL6-Server1] # tar xf cmake-2.8.12.tar.gz-C / usr/src/

[root@RHEL6-Server1 ~] # cd / usr/src/cmake-2.8.12/

[root@RHEL6-Server1 cmake-2.8.12] # / configure & & gmake & & gmake install

[root@RHEL6-Server1 ~] # groupadd mysql

[root@RHEL6-Server1] # useradd-M-s / sbin/nologin-g mysql mysql

[root@RHEL6-Server1] # tar xf mysql-5.7.2-m12.tar.gz-C / usr/src/

[root@RHEL6-Server1 ~] # cd / usr/src/mysql-5.7.2-m12/

[root@RHEL6-Server1 mysql-5.7.2-m12] # cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DWITH_EXTRA_CHARSETS=all-DSYSCONFDIR=/etc & & make & & make install

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql / / installation directory of the main program

-DDEFAULT_CHARSET=utf8 / / default character set is utf8

-DDEFAULT_COLLATION=utf8_general_ci / / default character set proofreading rules

-DWITH_EXTRA_CHARSETS=all / / install all character sets

-DSYSCONFDIR=/etc / / configuration file storage directory

[root@RHEL6-Server1 mysql-5.7.2-m12] # make & & make install

2) Optimization and adjustment

[root@RHEL6-Server1 mysql-5.7.2-m12] # cp support-files/my-default.cnf / etc/my.cnf

[root@RHEL6-Server1 mysql-5.7.2-m12] # cp support-files/mysql.server / etc/init.d/mysqld

[root@RHEL6-Server1 mysql-5.7.2-m12] # chmod + x / etc/init.d/mysqld

[root@RHEL6-Server1 mysql-5.7.2-m12] # chkconfig-- add mysqld

[root@RHEL6-Server1 mysql-5.7.2-m12] # chkconfig-- list mysqld

Mysqld 0: off 1: off 2: enable 3: enable 4: enable 5: enable 6: close

[root@RHEL6-Server1 mysql-5.7.2-m12] # echo "PATH=$PATH:/usr/local/mysql/bin" > > / etc/profile

[root@RHEL6-Server1 mysql-5.7.2-m12] #. / etc/profile = source / etc/profile

3) initialize the database

[root@RHEL6-Server1 mysql-5.7.2-m12] # chown-R mysql:mysql / usr/local/mysql/

[root@RHEL6-Server1 mysql-5.7.2-m12] # / usr/local/mysql/scripts/mysql_install_db-basedir=/usr/local/mysql/-datadir=/usr/local/mysql/data-user=mysql

-- basedir=/usr/local/mysql/ specify the installation directory (product directory)

-- datadir=/usr/local/mysql/data / / specify the data directory

-- user=mysql / / specify user identity

4) start the mysql service

[root@RHEL6-Server1 mysql-5.7.2-m12] # service mysqld start

Starting MySQL. [OK]

[root@RHEL6-Server1 mysql-5.7.2-m12] # mysqladmin-u root password '123456' / / set to your own password

[root@RHEL6-Server1 mysql-5.7.2-m12] # mysql- u root-p123456

Introduction to my.cnf configuration file, templates provided under mysql decompression path support-files/

Huge amount of data in my-huge.cnf

My-innodb-heavy-4G.cnf innodb engine

Large amount of data in my-large.cnf

My-medium.cnf testing uses

Small amount of data in my-small.cnf

MySQL database storage engine:

Myisam

Innodb

The order in which multiple profiles are applied, with the latter overwriting the previous one

/ etc/my.cnf-- > / etc/mysql/my.cnf-- > $BASEDIR/my.cnf-- > ~ / .my.cnf

Netstat-anpt | grep mysqld

Tcp 3306 port

MySQL is a Cpact S structure, and client tools are required to connect. Mysql under Linux (mysql software package is required if installing software through rpm format)

Connect and log in to the MySQL operating environment

Mysql-u specifies the user name

-p specify password

-h specify the host

-P designated port

[root@RHEL6-Server1 ~] # mysql-u root default root is Mysql user and empty password

Set the password of the database user

Mysqladmin-u root password '123456'

Check out those databases.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema | compatibility is guaranteed. The information stored in memory during mysql operation is empty after shutdown. |

| | mysql | the main one |

| | test | used for testing |

View data table information in the database

Mysql > USE mysql; / / switch database

Mysql > show tables; / / check which tables are available

Display the structure of the data table (fields)

DESCRIBE [database name.] Table name

Mysql > DESCRIBE user

Mysql > DESCRIBE mysql.user

Create a new database

CREATE DATABASE database name

Mysql > CREATE DATABASE auth

Create a new datasheet

CREATE TABLE table name (field definition …)

Mysql > USE auth

Mysql > CREATE TABLE users (user_name CHAR (16) NOT NULL, user_passwd CHAR (48) DEFAULT', PRIMARY KEY (user_name))

Delete the specified data table

DROP TABLE [database name.] Table name

Mysql > DROP TABLE auth.users

Delete the specified database

DROP DATABASE database name

Mysql > DROP DATABASE auth

Insert a new data record into the data table

INSERT INTO table name (field 1, field 2, …) VALUES (the value of field 1, the value of field 2,...)

Mysql > use auth

Mysql > INSERT INTO users (user_name,user_passwd) VALUES ('zhangsan', PASSWORD (' 123456'))

Mysql > INSERT INTO users VALUES ('lisi', PASSWORD (' 654321'))

Mysql > select * from auth.users

Find qualified data records from the data table

SELECT field name 1, field name 2... FROM table name WHERE conditional expression

Mysql > SELECT user_name,user_passwd FROM auth.users where user_name= 'zhangsan'

Modify and update data records in the data table

UPDATE table name SET field name 1 = value 1 [, field name 2 = value 2] WHERE conditional expression

Mysql > UPDATE auth.users SET user_passwd=PASSWORD ('') WHERE user_name='lisi'

Mysql > select * from auth.users

Mysql > UPDATE mysql.user SET password=PASSWORD ('123456') WHERE user='root'

Mysql > FLUSH PRIVILEGES; refresh the authorization table and let the mysql process reread the authorization table

Deletes the specified data record in the data table

DELETE FROM table name WHERE conditional expression

Mysql > SELECT user,host,password FROM mysql.user WHERE user=''

Mysql > DELETE FROM mysql.user WHERE user=''

Mysql > DELETE FROM auth.users WHERE user_name='lisi'

Mysql > SELECT * FROM auth.users

Set user permissions (create a new user when the user does not exist)

GRANT permission list ON database name. Table name TO username @ source address [IDENTIFIED BY 'password']

Mysql > grant all on. To 'root'@'192.168.6.131' IDENTIFIED BY' 123456'

Mysql > GRANT select ON auth. TO 'xiaoqi'@'localhost' IDENTIFIED BY' 123456'

[root@www] # mysql-u xiaoqi-p

……

Mysql > SELECT FROM auth.users

+-+

| | user_name | user_passwd |

+-+

| | zhangsan | 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT FROM mysql.user

View the user's permissions

SHOW GRANTS FOR user name @ domain name or IP

Mysql > SHOW GRANTS FOR 'xiaoqi'@'RHEL6-Server1'

Mysql > SHOW GRANTS FOR 'dbuser'@'192.168.4.19'

Revoke the permissions of a user

REVOKE permission list ON database name. Table name FROM user name @ domain name or IP

Mysql > REVOKE all ON auth.* FROM 'xiaoqi'@'localhost'

Backup and recovery of Database

Method 1: you can directly back up the directory / var/local/mysql/var

Method 2: use the dedicated backup tool mysqldump

Backup operation

Mysqldump-u username-p [password] [option] [database name] [table name] > / backup path / backup file name

Common options:

-- all-databases

-- opt

[root@www ~] # mysqldump-u root-p mysql user > mysql-user.sql

Enter password:

[root@www] # mysqldump-u root-p-- database auth > auth.sql

Enter password:

[root@www] # mysqldump-u root-p-opt-- all-databases > all-data.sql

Enter password:

Restore operation

Mysql-u root-p [database name]

< /备份路径/备份文件名 [root@www ~]# mysql -u root -p test < mysql-user.sql Enter password: MySQL通配符： _:任意单个字符 %：任意长度的任意字符 用于显示广泛的服务器状态信息 show status; 显示创建特定数据库或表 help create database； help create tables； 显示授权用户的安全权限 show grants； 显示服务器错误或警告信息 show errors； show warnings； 显示当前连接用户 mysql>

Select user ()

Show current time

Mysql > select now ()

Show current user and time

Mysql > select CURRENT_USER (), CURRENT_TIMESTAMP

Mysql > select user (), now ()

Show current database

Mysql > select database ()

Show server status

Mysql > status

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.