MySQL database from getting started to combat (iii)

Part I: Supplementary
(1) The previous chapter adds:
MySQL at boot time:
1. Start the daemon daemon (mysqld) and generate worker threads (IO, W, R)
2. Pre-allocated memory structure for MySQL processing data use
What is an instance?
MySQL background process + thread + pre-allocated memory structure.
MySQL database management system: Instance + data composition
(2) MySQL boot mode:
Mysql.server---->mysqld_safe---->mysqld
Connecting strings through a network
Mysql-uroot-poldboy123-h 10.0.0.52
Through a socket file
Mysql-uroot-poldboy123-s/application/mysql/tmp/mysql.sock
(3) MySQL shutdown mode:
mysqladmin-uroot-poldboy123 shutdown
/etc/init.d/mysqld stop
(4) MySQL connection management mysql command
-U user Name
-p password
-H MYSQL,IP Address
-S sockets
-P Port
-e non-interactive access to the database and execution of commands
(5) error log (very important)
"Hostname.err", which is stored by default in the data directory, is used in conjunction with the Log Analysis tool
Common Error Collation: perror command to view
http://oldboy.blog.51cto.com/2561410/1728380
(6) mysqld Boot configuration selection order:
1. Pre-compilation (Cmake)
2, at the start of the command line directly specified (this priority is the highest)
./mysqld_safe--log-error=/var/log/mysql.log--socket=/tmp/mysql.sock--port=3310 &
(7) What can/etc/my.cnf affect?
Mysqld Start: Mysqld,mysqld_safe,
[Server] Module
Affect client link Mysql,mysqladmin,mysqldump
[Client] Module
(2) Configuration case
[Server]
Basedir=/application/mysql
Datadir=/application/mysql/data
Socket=/tmp/mysql.sock
Log-error=/var/log/mysql.log
Log_bin=/data/mysql/mysql-bin
Binlog_format=row
Skip_name_resolve=1
server_id=3306
[Client]
Socket=/tmp/mysql.sock

Part II: MySQL actual operation
(1) MySQL user management
————————————————————————————————————
User's role:
(1) Login
(2) Managing database objects (objects such as libraries, tables, etc.)
user's definition:
(1) Username (2) host domain: Who can access MySQL
' 10.0.0.200 '----> only allow 200 addresses to access my MySQL
' 10.0.0.% '-----allows all machines in this segment to access me
"10.0.0.5% '----" allows 50-59
'% '----> Allow everyone
To create a user:
Create user [email protected] ' 10.0.0.% ' identified by ' 123 '; ----Create
Select User,host,password from Mysql.user; ----View User tables
drop user [email protected] ' 10.0.0.% '; ----Delete a user
————————————————————————————————————
(2) Rights Management
————————————————————————————————————
1. Role of authority
What the User object does
[Email protected] ' 10.0.0.% ' Library, table add, delete, change, check
2. Permission Settings
Grant permission on object (range) to user identified by ' password ';
Grant All on.to [e-mail protected] ' 10.0.0.% ' identified by ' 123 ';
3. Description of the object:
object: A scope definition in a GRANT statement
OLDBOY.T1 Single Table level
Oldboy.Single-Library level
. * Full Library level
4. Permission Description:
Think about it if in test., set the Select,insert,update,delete
With OLDBOY.T1 set Select, the user's final permission on the T1 table should be what.
What should I do when I reclaim my privileges?
Grant select on test.to [e-mail protected] ' 10.0.0.% ' identified by ' 123 ';
Grant all on Test.t1 to [e-mail protected] ' 10.0.0.% ' identified by ' 123 ';
Oldboy user Last Test.t1 permission is all
Conclusion: If permissions are set at the library level and at the table level, permissions are superimposed on table operations.
To tell you the same, do not set permissions on multiple levels for the same user.
5. Final summary
Grant Insert,select, UPDATE, DELETE, CREATE, DROP on Oldboy.To [e- Mail protected] ' 10.0.0.% ' identified by ' 123 ';---set permissions
Revoke Insert,select, UPDATE, DELETE, CREATE, DROP on Oldboy.From [email protected] ' 10.0.0.% '; ----Permission Reclamation
Show grants for [email protected] ' 10.0.0.% ';
Select User,host,password from Mysql.user; ---view users
————————————————————————————————————
(3) MySQL forgot password
————————————————————————————————————
1. Stop the database
2. Stop the authorization function and stop the remote network login
Mysqld_safe--skip-grant-table--skip-networking &
3. Change the root password in the user table
Update Mysql.user set Password=password (' 123 ') where user= ' root ' and host= ' localhost ';
4. Restart the database
/etc/init.d/mysqld restart
Additional notes:
5.7 The password field is no longer password, replaced in order to Authentication_strings
Update Mysql.user set Authentication_strings=password (' 123 ') where user= ' root ' and host= ' localhost ';
——————————————————————————————————————
Help-----Useful
\c
\g
Source restore Backup, execute SQL script
System Execution OS commands
Use World
Status
Example:mysql> help create database;
———————————————————————————————————————
(3) Libraries, tables, columns, row operations-----DDL: Object Definition language in the database (libraries, tables)
———————————————————————————————————————
Library Operations:
Create schema Zabbix character set UTF8; ---building a library
Show CREATE Database Zabbix; ---See the Library
DROP DATABASE Zabbix---Delete library
ALTER DATABASE Zabbix CharSet UTF8MB4; ---modifying library properties
Show CREATE Database Zabbix;
column operations:
CREATE TABLE stu (ID int,name varchar (), age int, gender int); ---create a table
Show CREATE TABLE Stu; ----View Table content information
Desc Stu; ---view table structure
drop table Stu; ---delete a table
ALTER TABLE Stu Rename to student; To rename a table----
1. Add a column at the end of the table
ALTER TABLE student Add addr varchar (20);
2. Add a column to the head of the table
ALTER TABLE student add stu_id int first;
3. Add a column after the name column
ALTER TABLE student add QQ int after name;
4, add tel_num after age, add email in the last line
ALTER TABLE student add Tel_num int after age,add email varchar (20);
5. Delete a column
ALTER TABLE student Drop ID
6. Modify the column name
ALTER TABLE student change name stu_name varchar (20);
7. Modifying column data types
ALTER TABLE student Modify gender varchar (20);
8. Replace Delete with update, pseudo-delete.
ALTER TABLE STUDENT_0 add state int default 1; ---the last column to add a new column
Update student_0 set state=0 where stu_name= ' zhang3 '; ---modification
SelectFrom student_0 where state=1;---view by condition
9. Added:
Create an empty table with the same table structure as the student table.
CREATE table student_0 like Stundet;
Create a backup table with the same T1
CREATE TABLE t1_1 as selectfrom T1;
——————————————————————————————————————
DCL: Database Control Language (permission grant revoke)
——————————————————————————————————————
Grant Insert,select, UPDATE, DELETE, CREATE, DROP on Oldboy.To [e- Mail protected] ' 10.0.0.% ' identified by ' 123 ';
Revoke Insert,select, UPDATE, DELETE, CREATE, DROP on Oldboy.From [email protected] ' 10.0.0.% ';
——————————————————————————————————-————
DML: Data line Operation language (increment, delete, change)
——————————————————————————————————————
What is the operation?
Data rows in a table, general "Transaction" statements
Insert data:
Insert student VALUES (1, ' zhang3 ', 123,20,110, ' Male ', ' bj ', ' [email protected] '); ---insert a row
INSERT into student values (1, ' zhang3 ', 123,20,110, ' Male ', ' bj ', ' [email protected] ');
Specify columns for insertion
INSERT into student (STU_ID,STU_NAME,QQ) VALUES (2, ' li4 ', 456);
Insert multiple rows of data at once (MultiRow at the same time, higher efficiency, recommended, but not):
INSERT into student values (1, ' zhang3 ', 123,20,110, ' Male ', ' bj ', ' [email protected] '), (5, ' zz ', 12322,202,1102, ' female ', ' BJ ', ' [email protected] ';
Inserting student data into STUDENT_0
INSERT INTO STUDENT_0 Selectfrom student;
To modify the data:
Update student set stu_name= ' Wang5 ' where stu_id=5;
Delete data
Delete from student where stu_name= ' zhang3 ';
————————————
TRUNCATE TABLE student;
Usually when you need to delete the whole big table, in order to speed up
For example drop table oss_base; -----"1000w Line
First:
TRUNCATE TABLE oss_base;
After
drop table oss_base;
————————————
Replace Delete pseudo-delete with update.
ALTER TABLE STUDENT_0 add state int default 1;
Update student_0 set state=0 where stu_name= ' zhang3 ';
Selectfrom Student_0 where state=1;
——————————————————————————————————
DQL: Data row Query Language (select Show)
——————————————————————————————————

MySQL database from getting started to combat (iii)