MySQL Database learning "first" database installation

First, the basic part

1. What is a database?

Previously learned, data to be permanently saved, such as user registration of user information, are stored in the file, and the file can only exist on a certain machine.

If we do not consider the efficiency of reading data from a file, and assuming that all of our programs are running on a single machine, there is no problem accessing the data with the file

However, the problem to be considered is that the execution efficiency of the program depends on the hardware that carries it, and the performance of a machine machine is limited, and the vertical scale is limited by the current hardware level. So we can only increase the overall performance of our system through horizontal scaling, which requires us to distribute the various components of the program across multiple machines to execute.

It is important to note that although we distribute the various components of the program to each machine, the components remain a whole, with the implication that the data for all components is shared. But the components on each machine can only operate the native files, which leads to inconsistent data.

So we thought about separating the data from the application: storing the file in a machine and then accessing the files on the machine through the network, that is, sharing the files on this machine, sharing means competition, the data is unsafe, and the lock processing is required ....

In order to remotely access and process files on this shared machine, we have to write an extra piece of code for our program to accomplish the following functions:

#1. Remote Connection # # # # # # # Open File # # # # Read and write (locking) #4. close File

We write any program need to have this code, so we extract this code, written as a specialized processing software, this is the origin of MySQL and other database management software, but MySQL is not only solve the problem of data sharing, query efficiency, security and a series of problems, in short, Frees programmers from data management and focuses on writing their own program logic.

2. Database overview

? What is Data? (Data)

Data: A symbolic record describing a transaction, a symbol that describes a thing can be either a number or a text, a picture, an image, a sound, a language, etc., data from a variety of representations, which can be digitized and stored in the computer

To describe a thing in a computer, you need to extract the typical characteristics of this thing, to form a record, which is equivalent to a line of content in a file, such as:

1  Haiyan  female 20 Gansu  

A simple record does not make any sense, if we divide by commas, we define the meaning of each field in turn.

ID   name    sex     age   adderess   major   #字段1   Haiyan  female      Gansu     Computer Department   #记录

If this is said to understand a person's information, let a person look at the clear

? What is a database? (DataBase, abbreviated as DB)

The database, as the name implies, is the warehouse where the data is stored, except that the warehouse is on a computer storage device.

The data in the database is organized, described and stored according to a certain data model, with small redundancy, high data independence and extensibility, and can be shared for various users.

What is a database management system (DBMS Management)

After understanding the concept of data and DB, how to organize and store it scientifically, how to efficiently acquire and maintain data becomes the key

This is the use of a system software---database management system

such as MySQL, Oracle, SQLite, Access, MS SQL Server

MySQL is mainly used for large-scale portals, such as Sogou, Sina, etc., its main advantage is open source code, because the open source of this database is free, he is now the Oracle company's products.
Oracle is mainly used in banks, railways, airports, etc. The database is powerful and the software is expensive. It is also the product of Oracle Corporation.
SQL Server is Microsoft's products, mainly used in large and medium-sized enterprises, such as Lenovo, founder and so on.

The database is plainly a folder
Database management system is a software
Database server: A machine that provides data exclusively to the outside

? database server, database management system, table and record relationship (key understanding)

Table: student,scholl,class_list (i.e. file)

Database: Lzsh_info (that is, folder)

Database management system: such as MySQL (is a software)

Database server: One computer (high memory requirements)

Summarize:

Database Server---Run---> Database management software

Database management software---> Management---> Database

Database---Organization---> Tables, folders---organization---> Files

Table---holds---> Multiple records, that is, the file---stored---> Multi-line content

? The development process of database management technology (understanding)

1. Human Management Phase

2. File System phase

3. Data System phase

3.mysql Introduction

MySQL is a relational database management system, MySQL most popular relational database management system, in the WEB application MySQL is the best RDBMS (relational database Management system, relational databases management systems) One of the application software.

What is MySQL?

MySQL is a socekt server
Client software
MySQL comes with
Python module

Other types of databases?

Sqllite,db2,oracle,access,sql Server,mysql
There are two main types of:
Relationship type: Above this lump, note: SQL statement Universal
Non-relational: Mongodb,redis,memcache

4. Download and install

Linux version

#二进制rpm包安装 Yum Install mysql-server mysql

#源码安装mysql1. Unzip the tar package CD/SoftwareTar-XZVF mysql-5.6. +-LINUX-GLIBC2.5-x86_64.Tar. GZMVmysql-5.6. +-LINUX-GLIBC2.5-x86_64 mysql-5.6. +2. Add users and Groups Groupadd Mysqluseradd-R-g MySQL MySQLChown-R Mysql:mysql mysql-5.6. +3. Install the databasesuMYSQLCD MySQL-5.6. +/scripts./mysql_install_db--user=mysql--basedir=/software/mysql-5.6. +--datadir=/software/mysql-5.6. +/Data4. configuration file CD/software/mysql-5.6. +/support-FilesCPmy-default.cnf/etc/my.cnfCPmysql.server/etc/init.d/Mysqlvim/etc/init.d/mysql #若mysql的安装目录是/usr/local/MySQL, you can omit this step to modify the two change values in a file Basedir=/software/mysql-5.6. +DataDir=/software/mysql-5.6. +/Data5. Configure environment Variables Vim/etc/Profileexport mysql_home="/software/mysql-5.6.21"Export PATH="$PATH: $MYSQL _home/bin"Source/etc/ Profile6add self-starting service Chkconfig--add mysqlchkconfig mysql on7. Start Mysqlservice mysql start8log in to MySQL and change passwords and configure remote access Mysqladmin-U root Password'Your_password'#修改root用户密码mysql-U Root-p #登录mysql, need to enter password MySQL>grant all privileges on * * to'Root'@'%'Identified by'Your_password'With GRANT OPTION; #允许root用户远程访问mysql>flush privileges; #刷新权限

1. UnzipTarZXVF mariadb-5.5. to-linux-x86_64.Tar. GZMVmariadb-5.5. to-linux-x86_64/usr/local/mysql//This is required so that many scripts or executable programs will directly access this directory2. Permissions Groupadd MySQL//add MySQL genus Groupuseradd-g MySQL MySQL//add MySQL user to MySQL groupChownMysql:mysql-rf/usr/local/mysql//sets the user and user group attribution for the MySQL directory. chmod+x-rf/usr/local/mysql//Grant executable permission3. Copy the configuration fileCP/usr/local/mysql/support-files/my-medium.cnf/etc/my.cnf//Copy the default MySQL configuration file to/etc directory4. Initialize/usr/local/mysql/scripts/mysql_install_db--user=mysql//Initializing the databaseCP/usr/local/mysql/support-files/mysql.server/etc/init.d/mysql//Copy the MySQL service program to the system directoryChkconfig MySQL on//add MySQL to system service and set to bootService MySQL Start//start MySQL5. Environment variable Configuration vim/etc/profile//Edit Profile To add the MySQL executable path to the system pathExport path=/usr/local/mysql/Bin: $PATH source/etc/profile//make path effective. 6. Account password Mysqladmin-U root Password'YourPassword' //set root account and passwordMysql-u root-p//log in to MySQL using the root userUse MySQL//switch to the MySQL database. SelectUser,host,password from user;//View System PermissionsDrop user"'@'localhost';//Delete an unsecured accountdrop user [email protected]':: 1';d ROP User [email protected]127.0.0.1;SelectUser,host,password from user;//Check the system permissions again to ensure that unsecured accounts are deleted. Flush privileges;//Refresh Permissions7. Some of the necessary initial configurations1) Modify the character set to UTF8VI/etc/my.cnf Add default under [client]-character-set =UTF8 add character_set_server under [mysqld]=UTF82) Add error logVI/etc/My.cnf added under [mysqld]: Log-error =/usr/local/mysql/log/error.loggeneral-log-file=/usr/local/mysql/log/Mysql.log3is set to case-insensitive , and Linux is case-sensitive by default. VI/etc/My.cnf added under [mysqld]: Lower_case_table_name=1modified restart: #service mysql restart source installation mariadb

Window version

1. Download path:http://dev.mysql.com/downloads/mysql/

1. First download the installation package, and then unzip the downloaded compressed package to the path you want to store
2. Then set the environment variables. For example, my storage is in e-drive.
Set environment variables: path--e:\mysql-5.7.19-winx64\bin;
3.mysqld--initialize-insecure Initialization (one data package is generated from initialization)
4.mysqld start MySQL
5.tasklist | findstr MySQL #查看mysql是否启动
6.MYSQL-UROOT-P Login without password
7.mysqladmin-uroot Password 123 setting password login
8.quit exit
9.windows system Kill task Tskill Mysqld

To this end, the MySQL server has been successfully installed and the client is ready to connect, and then when the MySQL operation

Note: When we start with MySQL every time, idle trouble, then we make it a system service
Just use mysqld--install.
When you enter services.msc in the search, you can see a MySQL.

After registering as a service, you only need to execute the following command when you start and close the MySQL service later:

# Start MySQL service net start mysql# shut down MySQL service net stop MySQL

What if I forget my password?

Two ways to crack passwords

Crack password One
1. Dry-up MySQL (note: In what way it will be done in what way it kills)
2. Start in cmd: Mysqld--skip-grant-tables (Skip all authorization forms)
3. Log in in cmd: MySQL
4. Enter two commands:
Update mysql.user Set authentication_string = password (' 123 ') where user = ' root ' and host = ' localhost '
Flush privileges Re-refresh permissions

5.tskill MYSQLD Kill Mission
6. Normal start MySQL
7.mysql-uroot-p123

Crack password Two (only when the database is just installed, if a lot of data that you useful already exists,
Do not use this method, you have to use the first type)
1. Create a new My.ini configuration file in MySQL's decompression directory
So write it in the My.ini config file.
There are two types of notes in the INI configuration file: # or;
[MySQL]
Skip-grant-tables (at the beginning-it's not necessary) if you start it off, and then restart it, after logging in, My.ini takes effect.
Basedir = E:\mysql-5.7.19-winx64
DataDir = D:\6_data #如果data指定要保存数据的目录, always remember to initialize MYSQLD--initialize-insecure
#完了吧原先的那个数据data干掉.

4. Under Windows, specify the configuration file for the MySQL service

#在mysql的解压目录下, create a new My.ini, and then configure # #. The following configuration takes effect when the mysqld command is executed, that is, when the MySQL service is started [mysqld];skip-grant-tablesport=3306character_set_server=utf8# extracted directory Basedir=e : \mysql-5.7.19-winx64#data directory Datadir=e:\my_data #在mysqld--initialize, the initial data is stored in the directory specified here, after initialization, when MySQL is started, Will go to this directory to find data # #. For the global configuration of the client commands, the following configuration takes effect when the MySQL client command executes [client]port=3306default-character-set=utf8user=rootpassword=123456#3. Only for MySQL this client configuration, 2 is the global configuration, and here is only for MySQL this command local configuration [MySQL];p ort=3306;default-character-set=utf8user= hiayanpassword=123456

　　

MySQL Database learning "first" database installation