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

The following content mainly brings MySQL and MariaDB(installation deployment, database operation,SQL statement) diary arrangement to everyone. The knowledge mentioned here is slightly different from books. It is summarized by professional technicians in the process of contacting users. It has certain experience sharing value and hopes to bring help to readers.

Clean up about MySQL and MariaDB(install deployment, database operations,SQL statements)

Friday, 9 August 2019

15:10

I. MySQL:(Practical operation environment:CentOS6.5 install MySQL 5.6)

Linux CentOS 6.5 yum install mysql 5.6

1. For a newly opened Cloud Virtual Machine, check whether mysql is installed on the system.

# yum list installed | grep mysql

2. If you find that there is a system with mysql, do it decisively

# yum -y remove mysql-libs.x86_64

3. Just execute it in the directory where you store files. Explain here. Because the yum source server of mysql is abroad, the download speed will be slower. Fortunately, mysql 5.6 is only 79M large, while mysql 5.7 has 182M. So this is why I don't want to install mysql 5.7.

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

4. Then execute this sentence, explain, this rpm is not mysql installation file, just two yum source files, after execution, in/etc/yum.repos.d/directory more mysql-community-source.repo and mysql-community.repo

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

5. At this time, you can use yum repolist mysql to check if there is already a mysql installable file.

#yum repolist all | grep mysql

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

# yum install mysql-community-server

7. after successful installation

# service mysqld start

8. Since mysql has just been installed, the password of mysql root user is empty by default, so we need to log in as mysql root user in time (enter key for the first time, do not enter password) and modify the password.

# mysql -u root

# use mysql;

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

# flush privileges;

9. Check whether mysql is self-starting and set the enable self-starting command

# chkconfig --list | grep mysqld

# chkconfig mysqld on

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

# mysql_secure_installation

II. MariaDB:(CentOS7.x test environment)

1. Install MariaDB

#yum -y group install mariadb mariadb-client

2. Start MariaDB

#systemctl enable mariadb

3. Check MariaDB status

#systemctl status mariadb

#netstat -lant | grep 3306

4. Initialize mariadb

#mysql_secure_installation

1)Enter root(mariaDB) password:(default to enter)

2)Set root password? [Y/n]

3)Remove anonymous accounts? [Y/n]

4)Do not allow root remote login? [Y/n]

5)Remove test database and access? [Y/n]

6)Reload privilege tables? [Y/n]

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 from remote to local

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

6. MariaDB warning log location

/var/log/mariadb/mariadb.log

You can use tail -f to view logs dynamically

SQL commands for database operations:

I. Database class operation:(operation object is database)

1. Display local database

#mysql -u root -p $PassWord

>show databases;

2. Create a database

>create database $DatabaseName;

>show databases;

3. Use of designated databases

>use $DatabaseName;

View tables in the database

>show tables;

View information in a specified table

>describe customers;

Displays extensive server status information

>show status

Shows MariaDB statements that create the specified database (table)

>show create database(table)

Displays security permissions granted to users

>show grants

Display server error or warning messages

>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

recovery

>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 tables for various problems:

>check table $TableName;

REPAIR TABLE is used to repair tables:

>repair table $TableName;

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

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", column name is "user")

>use mysql;

>select user form user;

5. View user permissions (show)

>show grants for $UserName;

6. Grant database access to users

>grant select on $DataBaseName.* to $UserName;

Multiple grants (multiple grants can be combined)

grant select insert on $DataBaseName.* to $UserName;

7. Revoke permissions

>revoke select on $DataBaseName .* from $UserName;

permissions

permissions

description

ALL

All permissions except grant option

ALTER

using the alter TABLE

ALTER ROUTINE

Alter procedure and drop procedure

CREATE

use the Create table

CREATE TEMPORAY

TABLE

Create temporary table

CREATE ROUTING

Use the create procedure

CREATE USER

Use create user,drop user,rename user and

revoke,privileges

CREATE VIEW

Use Create View

DELETE

use the delete

DROP

use the drop table

EXECUTE

Using call and stored procedures

FILE

Use select into outfile and load data in file

GRANT OPTION

Use grant and revoke

INDEX

Use create index and drop index

INSERT

use the insert

LOCK TABLES

Use of lock tables

PROCESS

Use show full processlist

RELORD

Use of flush

RELICATION CLIENT

Access local server

RELICATION SLAVE

There are copies of subordinate use

SELECT

using a select

SHOW DATABASES

Using show databases

SHOW VIEW

Use Show Create View

SHUTDOWN

Use mysqladmin shutdom

SUPER

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

global, also allows mysqladmin test logins

UPDATE

use the Update

USAGE

not have access

For the above about MySQL and MariaDB(installation deployment, database operations,SQL statements) diary finishing, if you still need to know more can continue to pay attention to our industry push new, if you need to get professional answers, you can contact the official website pre-sales after-sales, I hope this article can bring you a certain knowledge update.

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.