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

I. the installation of the database and its installation steps

1. Installation mode

Rpm package installation, yum installation, binary package installation, compilation installation

2. Installation steps (this article uses a binary package to install)

1) download the database of the corresponding version of mariadb and decompress it

Tar xf mariadb-5.5.48-linux-86_64.tar.gz-C / usr/local/

2) create soft links and create users and store data directories and authorize their directory files

Cd / usr/local

Ln-sv mariasb-5.5.48 mysql

Chown-R root.mysql mysql

Groupadd-r mysql

Useradd-r-g mysql mysql

Mkdir / mydata/data-pv

Chown-R mysql.mysql / mydata/data

3) provide configuration files and startup scripts to the database

Cp / usr/local/mysql/support-files/my-large.cnf / etc/my.cnf

Cp / usr/local/mysql/support-files/my.server / etc/init.d/mysqld

Chkconfig-add / etc/init.d/mysqld

Chkconfig mysqld on

4) add the following item to / etc/my.cnf

Datadir=/mydata/data

Skip-name-resolve=ON

Innodb-file-per-table=ON

5) initialize the database and its startup database

/ usr/local/mysql/scripts/mysql_install_db-user=mysql-datadir=/mydata/data

Service mysqld start

6) execute mysql_secure_installation command to set password and delete anonymous users

[root@centos6 ~] # mysql_secure_installation/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not foundNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the currentpassword for the root user. If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none): OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.You already have a root password set, so you can safely answer 'n'.Change the root password? [Y/n] n... Skipping.By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y... Successful normally, root should only be allowed to connect from 'localhost'. Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] n... Skipping.By default, MariaDB comes with a database named 'test' that anyone canaccess. This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] n... Skipping.Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] y... Success!Cleaning up...All done! If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB!

II. MariaDB Foundation

1. Configuration file lookup order

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

2. Mariadb's command line interactive customer server tools

Mysql [options] [database]

Common options:

-u:username, user name. Default is root.

-h:host remote host or address, default is localhost

-p:password, the user's password

-D:database_name, set the specified connection library

-e: run the commands in the mysql database directly on the command line

Example: mysql-uroot-hlocalhost-p mysql-utestuser-h20.1%.%-p mysql-uroot-p-e 'show databases'

The figure shows:

3. Mariadb database command line client command

Common help parameters are annotated as follows

[root@centos7] # mysql-uroot-pEnter password:Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 19Server version: 5.5.44-MariaDB MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > MariaDB [(none)] >\? # you can also use the help command General information about MariaDB can be found at http://mariadb.orgList of all MySQL commands:Note that all text commands must be first on line and end with';'? (\?) Synonym for `help'. # get help information clear (\ c) Clear the current input statement. # clear the input connect (\ r) Reconnect to the server for the current line. Optional arguments are db and host. # reconnect database delimiter (\ d) Set statement delimiter.edit (\ e) Edit command with $EDITOR.ego (\ G) Send command to mysql server, display result vertically. # display information exit (\ Q) Exit mysql. Same as quit. # exit the current database go (\ g) Send command to mysql server. # send commands to the database help (\ h) Display this help.nopager (\ n) Disable pager, print to stdout.notee (\ t) Don't write into outfile.pager (\ P) Set PAGER [to_pager]. Print the query results via PAGER.print (\ p) Print current command.prompt (\ R) Change your mysql prompt.quit (\ Q) Quit mysql.rehash (\ #) Rebuild completion hash.source (\.) Execute an SQL script file. Takes a file name as an argument.status (\ s) Get status information from the server.system (\!) Execute a system shell command.tee (\ T) Set outfile [to_outfile]. Append everything into given outfile.use (\ u) Use another database. Takes database name as argument.charset (\ C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.warnings (\ W) Show warnings after every statement.nowarning (\ w) Don't show warnings after every statement.For server side help, type 'help contents'MariaDB [(none)] >

4. Common data types in database

1) character type

Char,varchar,binary,varbinary,txt,blob...

2) numerical type

Int,float,double,tinyint...

3) enumerated type

Set,enum

4) time and date type

Date,time,datetime,timestamp,year

5) data type modifier

Unsigned,not null,default

3. Server orders

1, data definition language (DDL), mainly used to manage database components, such as indexes, views, users, stored procedures, the main commands are: create,alter,drop.

Operation on the library:

Create: create

Create {database | schema} [if not exists] db_name

Modify: alter

Alter {database | schema} [db_name]

Delete: drop

Drop {database | schema} [if exists] {db_name}

View supported character sets: show character set

View all supported sorting rules: show collation

The figure shows:

Operation on the table:

Create a table:

Create [temporary] table [if not exists] tbl_name

Modify the table:

Alter [online | offline] [ignore] table tbl_name

Field:

Add: add [column] col_name column_definition

Delete: drop [column] col_name

Modify: change [column] old_col_name new_col_name column_definition

Modify [column] col_name column_definition

Key:

Add: ADD [CONSTRAINT [symbol]] PRIMARY KEY

Add {primary key unique key foreign} (col1,col2...)

Delete:

Primary key: drop primary key

Foreign key: drop foreign key fk_name

Index:

Add: add {index | key} {index_name}

Delete: drop {index | key} index_name

The main examples are as follows:

Create database testdb; use testdb;create table if not exists students (id int unsigned NOT NULL primary key, name char (20) NOT NULL, age tinyint unsigned, gender enum); alter table students add class varchar (20) not null after age; alter table students change gender sex enum; alter table students modify class char (30) after sex; alter table students drop primary key; alter table students add primary key (id,name); alter table students add index class (class); alter table students drop index class

The figure shows:

Create libraries, tables, and view table structures

Add and modify field names and their types

Modify primary key and add primary key

View the table creation information and the status information of the table

2. Data manipulation language (DML), which is mainly used to manage the data in the table and to add, delete, modify and query the data.

Insert data: insert into

Insert into tbl_name [cols....] Values (val1,val2,...)

Example: insert into students (id,name) value (1 minicalren')

Insert into students values (2 grammatical rendition, 29 pyrrhenium, fugitive, fugitive)

Query data: select

Select [col1,col2,...] From tbl_name where clause

Example: select * from students where id=1

Select name,age from students where age > 11 and age

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.