MySQL Database article initial database

Key points of knowledge:

　　I. Overview of the database

Second, MySQL installation and basic management

1?? Database overview

　1. What is data?

The symbolic records describing things are called data, and the symbols that describe things can be either numbers or text, pictures, images, sounds,

Language, data from a variety of manifestations, they can be digitized and stored in the computer.

　2. What is a database, or DB, for short?

The database is the warehouse where the data is stored, except that the warehouse is on the computer storage device, and the data is stored in a certain format.

A database is a data that is stored in a computer, organized, and shared over a long period of time.

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

3. 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.

4. Summary:

1. Database server: computer running database management software 2, database management software: MySQL, Oracle, DB2, SQL Server3, Library: Folder 4, table: File 5, A series of typical features, such as: cc,male,216, data: Symbols describing the characteristics of things MySQL: relational database management software, developed by the Swedish MySQL company, currently belongs to Oracle Corporation.

Database server-: Running database management software

Database management software: Management-Database

Database: A folder for organizing files/tables

Table: A file that is used to hold multiple lines of content/Multiple records

2?? MySQL Installation and basic management

1. What is MySQL?

MySQL is a socket-based C/s architecture software client software MySQL comes with: such as MySQL command, mysqldump command and other Python modules: such as Pymysql

2. Database management software Classification

There are two main categories: relational: Like Sqllite,db2,oracle,access,sql server,mysql, Note: SQL statement general non-relational: Mongodb,redis,memcache can be simply understood as:    Relational database requires a table structure    non-relational database is key-value stored, no table structure

3. Download and install

3.1 Linux Download and install

# binary RPM Package installation yum-y install mysql-server MySQL

3.2 Windows Download and install

#1. Download: MySQL Community Server 5.7.16http://dev.mysql.com/downloads/mysql/#2. DecompressionIf you want MySQL to be installed in the specified directory, then move the extracted folder to the specified directory, such as: C:\mysql-5.7.16-Winx64#3. Add Environment Variables"Right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the Second content box find a row of the variable named path, double-click"-"Append the MySQL Bin directory path to the variable value, split"#4. InitializeMysqld--initialize-insecure#5. Start the MySQL serviceMysqld#start the MySQL service#6. Start the MySQL client and connect to the MySQL serviceMysql-u root-p#connect to MySQL server

The problem has not been solved completely, because the current terminal will be stuck when the MySQL server is executed "MYSQD", then the setup can solve the problem and the MySQL service will be made into Windows service.

 note:-- Before install, the absolute path of the command must be started with MySQL  #   Create a Windows service for MySQL, execute this command at the terminal:   c:\ Mysql-5.7.16-winx64\bin\mysqld   "-- Install  #   Remove the MySQL Windows service and execute this command at the terminal:   " c:\mysql-5.7.16-winx64\bin\mysqld   "--remove when you start and shut down the MySQL service after registering as a service Just execute the following command:  #   start MySQL service  net start mysql  #   turn off MySQL service  net stop MySQL 

4. mysql Startup and view

View under 4.1 Linux platform

#systemctl start mariadb #启动#systemctl enable mariadb #设置开机自启动Created symlink from/etc/systemd/system/multi-user.target.wants/mariadb.service to/usr/lib/systemd/system/Mariadb.service.#PS aux |grep mysqld |grep-v grep #查看进程, mysqld_safe for startup MySQL script file, internal invoke mysqld commandMySQL 3329 0.0 0.0 113252 1592? Ss 16:19 0:00/bin/sh/usr/bin/mysqld_safe--basedir=/Usrmysql3488 0.0 2.3 839276 90380? Sl 16:19 0:00/usr/libexec/mysqld--basedir=/usr--datadir=/var/lib/mysql--plugin-dir=/usr/lib64/mysql/plugin--log- Error=/var/log/mariadb/mariadb.log--pid-file=/var/run/mariadb/mariadb.pid--socket=/var/lib/mysql/Mysql.sock#netstat-an |grep 3306 #查看端口TCP 0 0 0.0.0.0:3306 0.0.0.0:*LISTEN#ll-d/var/lib/mysql #权限不对, startup unsuccessful, note user and groupDrwxr-xr-x 5 mysql mysql 4096 Jul 16:28/var/lib/mysql

You must reset your password using the ALTER USER statement before executing this statement.

1:set PASSWORD = PASSWORD ('your new PASSWORD'root'@ ' localhost '  3:flush privileges;

4.2 windows platform to view in service

  initial state , admin root, password blank, default to only allow password from native login localhost [[email protected]  ~]#   Span style= "COLOR: #008000" > mysqladmin-uroot password "123" Sets the initial password because the original password is empty, so-p can be used without the  [[email protected] ~]#   mysqladmin-uroot-p "123" password "456" Modify my SQL password, because there is already a password, you must enter the original password to set the new password   command format: [[email protected]  ~]#   mysql-h172.31.0.2-uroot-p456  [[email  Protected] ~]#   mysql-uroot-p  [[Email  protected] ~]#   MySQL log on as root user native , the password is empty   

5. Crack Password

5.1 Linux platform, two ways to crack the password

Method One: Delete the authorization library MySQL, re-initialize

[[Email protected] ~] # rm-rf/var/lib/mysql/mysql #所有授权信息全部丢失!!! [[email protected] ~]#  systemctl restart mariadb[[email protected] ~]#  MySQL 

Method Two: Skip the authorization library when you start

[[Email protected] ~]#vim/etc/my.cnf #mysql主配置文件[Mysqld]skip-grant-Table[[email protected]~]#systemctl Restart mariadb[[Email protected] ~]#MySQLMariaDB [(none)]> update Mysql.user set Password=password ("123") Where user="Root"  andhost="localhost"; MariaDB [(none)]>flush Privileges; MariaDB [(none)]>\q[[email protected]~]##打开/etc/my.cnf Remove the skip-grant-table and restart[[Email protected] ~]#systemctl Restart mariadb[[Email protected] ~]#mysql-u root-p123 #以新密码登录

5.2 Windows platform, version 5.7 MySQL, two ways to crack passwords:

Way One:

 #  1 close MySQL  #  2 executed in cmd: mysqld--skip-grant-tables  #  3 executed in cmd: MySQL  #  4 executes the following sql:  update mysql.user set Authentication_string=password (" ) where user =  " root   " ;flush privileges;  #  5 tskill mysqld #或taskkill-F/pid 7832  #  6 restart MySQL   

Way two:

#1. Turn off MySQL, you can kill it with Tskill mysqld#2. In the extract directory, create a new MySQL configuration file My.ini#3. My.ini content, specifying[Mysqld]skip-grant-Tables#4. Start Mysqld#5. Enter MySQL login directly in cmd, then operateUpdate Mysql.user Set Authentication_string=password ("') Where user='Root and host='localhost';flush Privileges;#6. Note The skip-grant-tables in My.ini, and then start MYQSLD, then you can log in with the new password

6. Unified character encoding

Note: Comments in the configuration file can be in Chinese, but the configuration item cannot appear in Chinese

Global Configuration Resolution

#under MySQL's decompression directory, create a new My.ini, and then configure#1. When executing the MYSQLD command, the following configuration will take effect when the MySQL service starts[Mysqld];skip-grant-Tablesport=3306Character_set_server=Utf8default-storage-engine=innodbinnodb_file_per_table=1#Extracted directorybasedir=e:\mysql-5.7.19-Winx64#Data DirectoryDatadir=e:\my_data#when Mysqld--initialize, the initial data will be stored in the directory specified here, after initialization, when starting MySQL, will go to this directory to find data#2. For global configuration of client commands, the following configuration takes effect when the MySQL client command executes[Client]port=3306default-character-set=Utf8user=Rootpassword=123#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;d efault-character-set=Utf8user=Egonpassword=4573#!! If there is no [MySQL], the user's configuration when executing the MySQL command is based on [client]

View Code

Unified character encoding

#1. Modify the configuration file[Mysqld]default-character-set=UTF8 [Client]default-character-set=UTF8 [Mysql]default-character-set=UTF8#mysql5.5 Above: Modification method has changed[Mysqld]character-set-server=utf8collation-server=Utf8_general_ci[client]default-character-set=Utf8[mysql]default-character-set=UTF8#2. Restart the service#3. View the results of the changes:\sshow variables like'%char%'

MySQL Database article initial database