CentOS installs mariadb in yum mode and binary package

centos7.3 install MARIADB by yum mode

Install by installing a package group

Yum GroupInfo mariadb mariadb-client

Yum Groupinstall mariadb mariadb-client

Start the service, the first boot time is a bit long

Systemctl Start mariadb

Systemctl Status mariadb

Systemctl Enable MARIADB

Security policy settings, setting passwords, etc.

Mysql_secure_installation

To see if TCP 3306 ports are turned on

Ss-tunlp

MARIADB configuration file

More/etc/my.cnf

Database storage Location

Ls/var/lib/mysql

Log in to Database

Mysql-uroot-p

Help and Query commands

Help

\s

Select version ();

Select User ();

show databases;

Show MySQL;

Show tables;

System Ls/root

System hostname

Quit

centos6.8 installation via binary package mariadb

Binary package:

https://downloads.mariadb.org/mariadb/5.5.57/

Create user MySQL

Useradd-r-u 306-d/app/data/-s/sbin/nologin MySQL

ID MySQL

Getent passwd MySQL

Create a home directory where the database resides and change its owner to MySQL

Mkdir/app/data

Chown MySQL. /app/data/

ll/app/data/-D

Unzip the MARIADB installation package, note that the directory must be specified/usr/local/

TAR-XF mariadb-5.5.57-linux-x86_64.tar.gz-c/usr/local/

cd/usr/local/

Create a soft link and recursively change file permissions

Ln-s Mariadb-5.5.57-linux-x86_64/mysql

Chown-r MySQL. Mysql

ll mysql/

Go to installation directory MySQL, install mariadb

Cd/usr/local/mysql

Generate database files, specify database storage directory and user

scripts/mysql_install_db--datadir=/app/data/--user=mysql

Copying a configuration file

Mkdir/etc/mysql

CP SUPPORT-FILES/MY-LARGE.CNF/ETC/MYSQL/MY.CNF

Edit the copied configuration file and add the following 3 items to the [Mysqld] service configuration

Vi/etc/mysql/my.cnf

[Mysqld]

DataDir =/app/data/

Skip_name_resolve = On

Innodb_file_per_table = On

Add the Mysqld service startup item and try to start the service

Chkconfig--add mysqld

Chkconfig--list | grep mysqld

Service mysqld Start

The error message is as follows

Can ' t create/write to file '/var/log/mysqld.log ' (errcode:13)

Create a log file, try restarting the service

Touch Mysqld.log

Chown MySQL. Mysqld.log

Service mysqld Start

Ss-tnlp

Add path path to easily execute MySQL command

cd/etc/profile.d/

VI mysql.sh

Export Path=/usr/local/mysql/bin: $PATH

. mysql.sh

Login database, first logon without password

Mysql

Exit

Modify security Policy Yynny

Mysql_secure_installation

Sign in with a password

Mysql-uroot-p

SQL statements

View the database, SQL statements are case insensitive show databases; show databases; Switch the database use mysql; the table in the Query Library show tables; query table structure desc user; all the information in the query table select *  from user; Query the specified column information in the table select host,user,password from user; if you do not specify which database to use, You need to specify the database name. Table name select host,user,password from mysql.user; Query Current user select user (); Create Database create  database mydb; Select can also display content directly, similar to Echo,as for defining field aliases, supporting direct arithmetic operations select  "UserA"  as name,1+2 as result; Delete database drop database mydb; View all supported character sets show character set; See all supported collations show collation; Get command Help Help showhelp create database view the system's own engine show engines; create a table help create table; use mydbcreate table student  (id int unsigned primary key,name  varchar ( not null,age tinyint unsigned);show tables;desc student; View Table Index Show  indexes from mydb.student; add data, the string must be added ' insert into student values (1, ' enAng ', 20); Add individual field data insert into student (id,name)  values (2, ' Wang '); query data Select * from  student; change record update student set name= ' li '  where id=2; delete record delete from  student where id=2; Empty Table truncate table mydb.student;select * from  mydb.student; sort, the default index is automatically sorted insert into student values (1, ' Zhang '); insert into student  values (2, ' Wang ', +); Insert into student values (4, ' Li '); insert into student  values (Ten, ' Zhao '), Insert into student values (5, ' song '), Select * from  student; query Sort by name column select * from student order by name; reverse descselect *  from student order by name desc; query results are displayed as aliases, the AS keyword can omit the select id  number, name   name  from student; query null value nullinsert into student (id,name)  values (8, ' Yang '); select   * from student where age is null; 

Create a new table from the old table create table test1  (Sid int,name varchar), Address varchar (100)); create table test2 select * from test1;desc test2; Ibid, import all data from old table to new table create  table student2 select * from student;select * from student2; Ibid., Import only part of the data create table student3 select id,name from student;select * from  student3; Bulk import of data into a new table insert into test2 select id,name from student; The Find Name field contains the data entry for a select * from test2 where name like  "%a%"; conditional filtering select *  from student where id in  (1,5,10);select * from student  Where id >=2 and id <=8;select * from student where is  between 2 and 8;select * from student where name !=  ' Zhang ';

Add Telnet user, the user's composition has two parts: ' User name ' @ ' host address ', the following user name [email protected] Client network segment, password redhat create telnet user create user  ' UserA ' @ ' 192.168.%.% '  identified by  ' redhat ';create user  ' UserB ' @ ' 192.168.10.% '   identified by  ' redhat '; test telnet mysql -uusera -h 192.168.10.10 -p default to normal user rights, You cannot display all databases show databases; delete users, note that the user name must be consistent with the previous creation. User name format: ' username ' @ ' host address ' drop user  ' UserB ' @ ' 192.168.10.% '; change user password set password for  ' usera ' @ ' 192.168.%.% ' =password (' CentOS '); change user password with MySQL admin command/usr/local/mysql/bin/mysqladmin -u usera -p  centos password  ' redhat ' authorization to query and delete, and allow all operations grant select on mydb.* to  ' UserA ' @ ' 192.168.%.% ';grant delect on mydb.test to  ' UserA ' @ ' 192.168.%.% ';grant  all on mydb.* to  ' UserB ' @ ' 192.168.%.% '  identified by userb;  # Create an account and authorize all of its permissions to mydb remotely via UserA user login, view the database again to see the MyDB database Show databases;use mydb;delete from test; prohibit user operation revoke delete on mydb.* from  ' UserB ' @ ' 192.168.%.% '; flush  privileges; need to exit and re-login before it takes effect

This article is from the "Rackie" blog, make sure to keep this source http://rackie386.blog.51cto.com/11279229/1952051

CentOS installs mariadb in yum mode and binary package