MySQL database common basic command application sharing 02

Note: This article is from the linux O & M training of Old Boys-MySQL database training course series
1) video tutorial directory
1. MySQL service operation .................................... ........................................ 3
1.1 start and close MySQL ................................... .......................... 3
1.1.1 single-instance method .................................... ........................ 3
1.1.2 multi-instance method .................................... ........................ 4
1.2 log on to MySQL .................................... ............................ 4
1.2.1 single-instance method .................................... ........................ 4
1.2.2 multi-instance method .................................... ........................ 4
1.3 after logging on to MySQL .................................... ............................... 4
1.4 make good use of MySQL help ................................... ........................ 5
1.5 exit MySQL method .................................... ............................. 7
1.6 set and modify the mysql root User Password ................................ ...... 7
1.6.1 set the password .................................... ..................... 7
1.6.2 root password change method 1 .................................. ............... 8
1.6.3 root password change method 2 .................................. ............... 8
1.6.4 modify the root password method 3 .................................. ............... 8
1.7 How to retrieve the lost mysql root User Password ............................... 8
1.7.1 how to start a single instance ................................... .................. 8
1.7.2 how to start multiple instances ................................... .................. 8
1.7.3 how to change the password ................................... .................. 8
1.7.4 restart and use the new password to log on .................................. ............... 9
2 database operations ..................................... ........................................ .... 9
2.1 create a database ..................................... ................................. 9
2.2 display the database ..................................... ................................. 11
2.3 Delete the database ..................................... ................................ 12
2.4 connect to the database ..................................... ................................ 12
2.5 view the database currently connected .................................. ................. 13
2.6 table information contained in the current database ................................. ............... 14
2.7 Delete excessive system accounts ................................... ....................... 14
2.8 create a user and grant permissions ................................ ................ 15
2.8.1 view grant help .................................... ............... 15
2.8.1 create and grant .................................. ...... 16
2.8.2 direct grant method .................................... ................... 18
2.8.3 obtain all privileges permissions through the experiment... 19
2.8.4 suggestions for authorizing users in the production environment ................................. .... 20
2.9 view mysql users ................................... ........................ 23
3 Table operations ..................................... ........................................ ......... 23
3.1 prepare ...................................... ........................................ .. 23
3.2 Create a table ..................................... ........................................... 24
3.2.1 create a test table .................................... .............. 24
3.2.2 UTF8 format table structure statement in the production environment
3.2.2.1 mysql field type .................................... ......... 25
3.2.2.2 create several joined tables :.................................. ..... 26
3.2.2.3 create an association table ................................... ........ 26
3.2.2.4 create a table for an sns product.
3.3 check table structure and SQL statement for table Creation ................................ .................... 27
3.3.1 view the table structure ..................................... ..................... 27
3.3.2 view the table creation statement ................................... .................... 28
3.3.3 create an index for a field ................................... ................ 28
3.4 insert data ..................................... .................................... 31
3.5 query ...................................... ......................................... 34
3.5.1 query all rows .................................... ........................ 34
3.5.2 query the first few rows of data .................................. ................... 34
3.5.3 multi-Table query .................................... ........................... 37
3.5.4 use explain to query the index used by the select statement ......
3.6 modify the data in the table .................................... ............................. 38
3.7 delete data in the table .................................... ............................. 39
3.8 add and delete fields in the table (dba operation ).............................. ............ 41
3.9 change the table name .................................... .................................... 43
3.10 delete a table ..................................... ..................................... 45
3.11 mysql field type .................................... ......................... 45
4. backup and recovery of the MySQL database ................................. ....................... 45
4.1 preparations: first, create a table to fill in the data ................................... ...... 45
4.2 The Problem of filling in Chinese data with garbled characters ................................ ............ 46
4.3 MySQL Character Set introduction .................................... ....................... 50
4.4 what is set names gbk doing ?................................ ............. 52
4.5 back up a single database .................................... ........................... 57
4.6 what is mysqldump doing ?.................................. .................. 59
4.7 back up multiple databases ................................... ................................... 61
4.8 back up a single table .................................... .................................. 61
4.9 back up multiple tables ................................... ................................... 62
4.10 back up the database structure (excluding data )............................... ......... 63
5. Restore the database ..................................... ........................................... 64
5.1 source command recovery .................................... ......................... 64
5.2 mysql Command recovery (standard )................................. ................... 70
6. mysql uses the-e parameter to implement non-Interactive dialog .............................. ................. 72
6.1 restore mysql Data .................................... .......................... 72
6.2 check the mysql status .................................... .......................... 73
7. mysql production environment case ................................... ................................. 76
7.1 mysql database batch insert data shell script implementation ......
7.2 do not log on to the database and execute the mysql command ................................ .. 77

2) Video practice (better full-screen viewing)

 

 For related video content, please pay more attention to the blog updates of old boys.
Finally, we welcome everyone to discuss learning and teaching, how to make the learner gain something, and how to maximize the inheritance of knowledge from the teacher.

This article from the "old boy linux O & M" blog, please be sure to keep this source http://oldboy.blog.51cto.com/2561410/1130667