The 13th chapter MySQL operation

13.1 Changing the root password
13.1.1 Change Password
The default is no password.
/etc/init.d/mysql start MySQL
PS aux |grep MySQL to see if it started
Mysql-uroot Report no command error found
Because the MySQL command is not in an environment variable, you can use the following methods:
Ls/usr/local/mysql/bin/mysql
1) with absolute address
/usr/local/mysql/bin/mysql-uroot
Change environment variable path, add MySQL absolute path
2) temporarily Add environment variables
Export path= $PATH:/usr/local/mysql/bin/
3) Permanent entry into force, modify Profil file, add command sentence.
Vim/etc/profile
Export path= $PATH:/usr/local/mysql/bin/
Source/etc/profile//Update a bit
Mysqladmin-uroot password ' 123456 '//Set password 123456
Hint: You show the password in the current command line, not xxx full
mysql-uroot-p123456//Login with password 123456
Mysqladmin-uroot-p ' 123456 ' password ' 567890 ' Change password is 567890

13.1.2 Password Reset
Modify VI/ETC/MY.CNF//Add a line skip-grant ignore authorization

Restart MySQL service/etc/init.d/mysqld restart
Mysql-uroot do not need a password to login
Switch the library to use MySQL after landing;
select * from user; This table holds the user/password/permissions, etc.
Select password from user; Choose your password
Use the command to change the root password to Aminglinux
Update user set Password=password (' Aminglinux ') where user= ' root ';
Modifying the configuration file ignores authorization cancellation
VI/ETC/MY.CNF//Delete the added line skip-grant Ignore authorization
/etc/init.d/mysqld Restart restart MySQL Service
Mysql-uroot-paminglinux Login with new password

13.2 Connecting MySQL
Mysql-uroot-paminglinux//default also specifies a socket connection
Mysql-uroot-paminglinux-s/tmp/mysql.sock//Specify Socket connection
mysql-uroot-paminglinux-h127.0.0.1-p3306//Specify IP port connection, default 3306
Mysql-uroot-paminglinux-e "Show Databases"//Login to list all the databases

13.3 MySQL Common commands
A library consists of a table and a table consists of fields.
Query library show databases;
Switch the library use MySQL;
View the table in the library show tables;
View the fields in the table desc user; User is the name of the table
View the Build Table statement Show create TABLE User\g user as the name of the table; \g Vertical Row Display
After adding \g, there is no need to add a semicolon. It is itself an end symbol.
After adding a semicolon, MySQL thought we had another command, and another command was empty, and we reported this error.
View the current user Select User (); Check the user name separately
mysql-uroot-paminglinux-h192.168.188.128-p3306//aming-01
mysql-uroot-paminglinux-h127.0.0.1-p3306//localhost
View the database you are currently using Select Database ();

Creating a library Create database db1; DB1 for the new library
CREATE table use DB1; CREATE TABLE T1 ( id int (4), name char (+)),//T1 table for library DB1

View current database version select version ();
View database status Show status;
View each parameter show variables; Show variables like ' max_connect% ';
Modify parameter set global max_connect_errors=1000; Temporary, to permanently modify the configuration file
View queue show Processlist; Show full processlist;

13.4 Mysql User Management
There is a root user by default.
Create User1 user, authorize all permissions password is 123456, limit login on localhost
Grant All on .To ' user1 ' @ ' localhost ' identified by ' 123456 ';
After Grant finishes, do you want to execute it? Flush privileges;
mysql-uuser1-p123456-h127.0.0.1-p3306, log in.
Mysql-uuser1-p123456 can also, because authorized localhost,localhost is for the socket
Authorization for specific permissions
Grant Select,update,insert on DB1. To ' user2 ' @ ' 192.168.188.1 ' identified by ' 123456 ';
For all IP authorizations
Grant all on DB1.To ' user3 ' @ '% ' of ' identified by ' passwd ';
View authorizations
Show grants;
You can copy content to another user or address after viewing authorized view permissions for the user
Show grants for [email protected];

13.4 MySQL Common statements
Select COUNT () from mysql.user;//function to view MySQL user table
Select from MYSQL.DB\G//view all MySQL content \g
Select db from Mysql.db; View the fields of a table
Select Db,user from Mysql.db; View two fields of a table
Select from mysql.db where host like ' 192.168.% ';//Blur View
Select from Db1.t1\g
INSERT into DB1.T1 values (1, ' abc '); Insert Field Id+name
Update db1.t1 set name= ' AAA ' where id=1; Update content with Field ID 1
Delete from db1.t1 where id=1; Delete a id=1 table
TRUNCATE TABLE db1.t1; Clears the contents of the table, but the fields still exist
drop table db1.t1; Delete a table
? drop database db1; Delete a library

13.4 Mysql Database Backup and Recovery
Back up the library to a file? Mysqldump-uroot-paminglinux mysql >/tmp/mysql.sql
MYSQL-UROOT-PAMINGLINUX-E "CREATE DATABASE Mysql2"//Create a library MYSQL2
Recovery Library Mysql-uroot-paminglinux Mysql2 </tmp/mysql.sql
Backup table mysqldump-uroot-paminglinux mysql user >/tmp/user.sql//Backup Library user table
Recovery table Mysql-uroot-paminglinux MySQL </tmp/user.sql
Back up all libraries mysqldump-uroot-p-A >/tmp/123.sql
Back up table structure only mysqldump-uroot-paminglinux-d mysql >/tmp/mysql.sql

The 13th chapter MySQL operation