Linux Learning-----Database mysql

Database

1. What is a database

If a project is a dynamic content database is required

2.MySQL

is a relational database management system one of the most popular database management system

MySQL is the best on Web applications and PHP is the Golden partner (LAMP Lnmp)

3. Common other database software

Oracle (Heavyweight) MS SQL Server

Software Installation method under 4.linux (Preliminary)

1. Source Package (less installation time dependent)

Pros: Open source can modify the original code

Compile and install the system for your own high stability

Cons: More error-prone installation steps

The compilation process takes a long time

Case Installation ncurses (a common terminal library) using the source code compilation installation method

Steps:

1. Pass the package to the server----------------upload to the server/usr/local/src/

2. Unzip the source package that needs to be installed

TAR-ZXVF *.tar.gz--------------------------TAR-ZXVF nurses-6.1.tar.gz

TAR-JXVF *tar.bz2

3. Switch to the source directory

Configuration (config/configure/bootstrap)----------./configure--prefix=/usr/local/ncurses

Compile (MAKE/BOOTSTRAPD)-------------------make

Install (make INSTALL/BOOTSTRPD install)----------make install

The configuration operation primarily specifies that the installation path of the software needs to depend on what the enemy specifies that no optional dependencies are required, the path to the configuration file common data storage location, etc.

Specify the path to the installation:--prefix= path

Path to depend on:--with-package name = package path

No dependencies required:--without-package name

2. Binary Package

Advantages: Package Management system simply requires a few commands to implement package installation upgrade query and Uninstall

Cons: After compiling no longer see the source code

Review RPM Instructions

Rpm-qa|grep keywords

Rpm-e key Words [--nodeps]

RPM-IVH Full Name

RPM-UVH Full Name

RPM-QF file path query where does the specified file belong? package

Case: Installing Lynx with a binary package (a plain command-line browser)

There's this package on the CD.

Lsblk to see if the disc is mounted

After you install it, view the webpage------------------------lynx–dump website address.

3.yum Installation

Advantages: Easy and quick installation

Cons: Complete loss of customization

Yum List---------------------list the currently installed and ready-to-install software

Yum Search Name---------------a package that searches for a specified quota keyword

Yum [-y] Install package name-----------installation of the specified package (-y means no more acknowledgments allowed)

Yum [-y] Update [package name]----------Update the specified package without specifying a package update all software

Yum [-y] Remove package name----------Unload the specified package

5. Install MySQL

1.MySQL Installation

Yum Install Mysql-server

2.MySQL initialization

Service mysqld Start

Netstat-thlp Viewing the database port number 3306

Mysql_secure_installation

Enter the root user password for MySQL

3.MySQL Start-up

Domain Name: Service mysqld Start

Enter MySQL mode--------------------mysql-u user name-P

and enter the password.

Exit Command:------------------------exit

4. Default directory/File location

Database storage directory:/var/lib/mysql

Configuration file:/etc/my.cnf

Basic operation of 6.MySQL

1. Noun Introduction:

Taking Excal as an example

Database: Can be regarded as the whole excal

Datasheets: Can be thought of as worksheets in excal

Row (record): Can be thought of as a row in a worksheet

Column (field): Can be thought of as a column in a worksheet

2. Library Operations:

The following command executes on the MySQL terminal command line

Show DATABASES; display all databases in the current MySQL

Create Database library name creates a DB

Drop database name to delete a data base

Use library name to switch databases

3. Table Operations

1.show tables Displays all table names in the current database (you must use the database first)

2.create Table Name

(Column name 1 data type "not NULL auto_increment",

Column Name 2 data type,

Column Name 2 data type

。。。

PRIMARY Key (primary key field name));

3.desc table name represents a data table (view table structure)

4. Drop table "If existed" name Delete data table

4. Record/Field operations

1. Adding records

Insert into table name values (value 1, value 2 ...). ）

Insert into table name (column 1, column 2 ...) ) VALUES (value 1, value 2 ....) ）；

2. Query records

Select Column Name 1, column name 2 from table name where condition; query specified field

SELECT * FROM table name where condition; query all fields

3. Update records

Update table name set column name 1= new value 1, column name 2= new value 2 .... Where condition;

Note the writing of the condition later when performing SQL operations on the row count

4. Delete operation

Delete from table name where column name = value;

5. Backup and restore

1. Backup

Full-scale backup (data + fabric): mysqldump-uroot-p123456-a > Backup file path

Specify library backup (data + structure): mysqldump-uroot-p123456 Library name > backup file path

Multiple library backups (data + fabric): mysqldump-uroot-p123456--databases db1 db2> backup file path

Case: Database is automatically backed up once per minute

1. Create a shell script (touch test19.sh---àvim test19.sh)

#!/bin/bash

Filename= "Test_" data+ '%y%m%d%h%m%s ' ". sql"

mysqldump-uroot-p123456 test>/root/$filename

2. Preparation of the mission plan (CRONTAB-E)

/root/test19.sh

2. Restore:

The Restore section is divided into MySQL command line Sourse method and System command line method

1. Restore all databases

1.mysql command line:mysql> source backup file path

2. System command line: mysql-uroot-p123456 < backup file path

2. Restore a single database (you need to specify a database)

1.mysql>use Library Name

mysql> sourse backup file path

2.MYSQL-UROOT-P123456 Library Name < backup file path

3. Restore multiple tables for a single database (Specify a database)

1.mysql>use Library Name

Mysql>source backup File path

2.MYSQL-UROOT-P123456 Library Name < backup file path

4. Restore multiple databases (backups of multiple databases in one backup file do not need to specify a database at this time)

1.mysql command line: Mysql>source backup file path

2. System command line: mysql-uroot-p123456 < backup file path

5. Setting the MySQL connection character set

Mysql>set names Utf-8;

Expand

Connect to a database using a remote terminal

Remote management tools for 1.mysql

Divided into two categories: b/S architecture browser and server such as Baidu Search application

c/S architecture clients and servers such as applications

MySQL has a typical management tool in BS: PMA (PhpMyAdmin)

There is a more typical software in CS: Navicat MySQL Workbrach

2.Navicat Use steps

1. Turn off the firewall:----Service iptables stop

2. Open navicat connection MySQL input connection name IP address port number user name password

To resolve the issue: Allow MySQL remote login

1. Enter the database

2. Execute SQL statement:-------select Host,user from user;

3. Change one of the host values to "%" to indicate that you can allow any place to log in

------Updata User Set host = "%" where host= "host name";

4. Refresh the permissions table (mysql> flush privileges) or restart MySQL

Linux Learning-----Database mysql