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

Sort out information about MySQL and MariaDB (installation and deployment, database operations, SQL statements)

Friday, August 9, 2019

15:10

1. MySQL: (practical environment: CentOS6.5 installs MySQL5.6)

Quote the original address:

Https://www.cnblogs.com/renjidong/p/7047396.html

Linux CentOS6.5 yum install mysql 5.6

1. For a newly opened CVM, you need to check whether the system comes with mysql installed.

# yum list installed | grep mysql

two。 If you find a system that comes with mysql, do so decisively

# yum-y remove mysql-libs.x86_64

3. Feel free to execute it in the directory where you store the files. Explain here that because the yum source server of this mysql is abroad, the download speed will be relatively slow. Fortunately, the mysql5.6 is only 79m, while mysql5.7 has 182m, so this is the reason why I do not want to install mysql5.7.

# wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

4. Then execute this sentence to explain that this rpm is not an installation file for mysql, but only two yum source files. After execution, there are more mysql-community-source.repo and mysql-community.repo in the / etc/yum.repos.d/ directory.

# rpm-ivh mysql-community-release-el6-5.noarch.rpm

5. At this point, you can use the yum repolist mysql command to see if you already have an mysql installable file

# yum repolist all | grep mysql

6. Install the mysql server command (all the way yes):

# yum install mysql-community-server

7. After successful installation

# service mysqld start

8. Since the password of the root user of mysql is empty by default when mysql is installed, we need to log in in time with the root user of mysql (enter key for the first time, no need to enter the password) and change the password

# mysql-u root

# use mysql

# update user set password=PASSWORD ("enter root user password here") where User='root'

# flush privileges

9. Check whether mysql is self-starting, and set the self-start command to be turned on.

# chkconfig-- list | grep mysqld

# chkconfig mysqld on

10.mysql security settings (the system will ask you a few questions all the way, but you can't understand the translation after copying, basically all the way yes):

# mysql_secure_installation

2. MariaDB: (CentOS7.x test environment)

1. Install MariaDB

# yum-y group install mariadb mariadb-client

2. Boot and start MariaDB

# systemctl enable mariadb

3. View MariaDB status

# systemctl status mariadb

# netstat-lant | grep 3306

4. Initialize mariadb

# mysql_secure_installation

1) enter root (mariaDB) password: (enter directly by default)

2) whether to set the root password? [YPao]

3) remove anonymous account? [YPao]

4) root is not allowed to log in remotely? [YBO]

5) remove test database and access? [YBO]

6) reload the privilege table? [YPao]

5. Confirm whether root can log in.

# mysel-u root

ERROR 1045 (28000): Access denied for user

'root'@'localhost' (using password: NO)

Verify that the test database has been removed

Mysql-u root-p $PassWord

Verify that root cannot log in locally from remote

# mysql-u root-p $PassWord-h mariadb_server

6. Warning log location of MariaDB

/ var/log/mariadb/mariadb.log

You can use tail-f to view logs dynamically

Common SQL instructions for database operations:

Database class operation: (the operation object is the database)

1. Display the local database

# mysql-u root-p $PassWord

> show databases

2. Create a database

> create database $DatabaseName

> show databases

3. Use the specified database

> use $DatabaseName

View the tables in the database

> show tables

View the information in the specified table

> describe customers

Displays a wide range of server status information

> show status

Displays the MariaDB statement that creates the specified database (table)

> show create database (table)

Displays the security permissions granted to the user

> show grants

Display server error or warning message

> show errors (warnings)

List of show statements

> help show

4. Database backup and recovery

Backup

> mysqldump-u root-p $DataBaseName >. / $DataBaseName.dump

> musqldump-u root-p-all-databases >. / all.dump

Restore

> mysql-u root-p $DataBaseName

< ./$DataBaseName.dump >

Mysql-u root-p

< ./all.dump 5、数据库检查与维护 ANALYZE TABLE用来检查表的键是否正确: >

Analyze table $TableName

CHECK TABLE is used to check various problems with the table:

> check table $TableName

REPAIR TABLE is used to repair tables:

> repair table $TableName

Second, user type operation: (the operation object is the user)

1. Create a new database user account (create)

> create user $UserName $PassWord

2. Delete database user (drop)

> drop user $UserName

3. Rename the database user name (rename)

> rename user $OldUserName to $NewUserName

4. Query the list of all database users (query the user column from the user table "user" and the column name is "user")

> use mysql

> select user form user

5. View the user's permissions (show)

> show grants for $UserName

6. Authorize users to access the database (grant)

> grant select on $DataBaseName.* to $UserName

Multiple authorizations (multiple grant can be merged)

Grant select insert on $DataBaseName.* to $UserName

7. Revoke the authority (revoke)

> revoke select on $DataBaseName. * from $UserName

Authority

Authority

Description

ALL

All permissions except grant option

ALTER

Use alter table

ALTER ROUTINE

Using alter procedure and drop procedure

CREATE

Use create table

CREATE TEMPORAY

TABLE

Use create temporary table

CREATE ROUTING

Use create procedure

CREATE USER

Use create user,drop user,rename user and

Revoke,privileges

CREATE VIEW

Use create view

DELETE

Use delete

DROP

Use drop table

EXECUTE

Using call and stored procedures

FILE

Using select into outfile and load data infile

GRANT OPTION

Using grant and revoke

INDEX

Using create index and drop index

INSERT

Use insert

LOCK TABLES

Use lock tables

PROCESS

Use show full processlist

RELORD

Use flush

RELICATION CLIENT

Access the local server

RELICATION SLAVE

There is replication dependent use

SELECT

Use select

SHOW DATABASES

Use show databases

SHOW VIEW

Use show create view

SHUTDOWN

Use mysqladmin shutdomn

SUPER

Using change master,kill,logs,purge,master and set

Globle, which also allows mysqladmin to test login

UPDATE

Use Update

USAGE

No access permission

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.