Usage of MySQL

Root password for MySQL
/usr/local/mysql/bin/mysqladmin-uroot password ' [password] ' Set password
/usr/local/mysql/bin/mysqladmin-uroot-p ' [Original password] ' password ' [New password] ' Set password
If you do not know the password, reset the password:
Vi/etc/my.cnf adding skip-grant in [mysqld] module means ignoring authorization to log in directly
Restart MySQL service/etc/init.d/mysqld restart
Mysql-uroot
Use MySQL;
Update user set Password=password (' 123456 ') where user= ' root '

mysql-u[user name]-p[password]-h[host IP]-p[port number]
mysql-u[user name]-p[password]-s[socket file] Only suitable for local connections

2. MySQL commands (plus semicolons required)
   Query library show databases;
   Switch the library use MySQL; MySQL is the library name
   View the table in the library show tables;
   View the fields in the table desc tb_name;
   View Build Table statement Show create TABLE tb_name\g;
   View the current user Select User ();
   View the database currently in use Select Databsase ();
   Creating a library Create database db1;
   CREATE table use DB1; CREATE TABLE T1 ( id int (4), name char (40));
   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;
   View queue show Processlist; Show full processlist;

MySQL User management
Grant all on . to ' user1 ' identified by ' [Password] '; Grant all permissions to User1 in all libraries
Grant Select,update,insert on DB1. To ' user2 ' @ ' 192.168.86.1 ' identified by ' [Password] ';
Grant all on DB1. To ' user3 ' @ '% ' of ' identified by ' [Password] ';
Show grants for [email protected]; View authorization information for a specified user

Common statements
Select COUNT (*) from Mysql.user; View the number of database rows (MySQL is the library name, user is the table name)
SELECT * from Mysql.db; Show Table Contents
Select db from Mysql.db;
Select Db,user from Mysql.db; Displays the contents of two fields (separated by commas)
SELECT * from mysql.db where host like ' 192.168.% ';
INSERT into DB1.T1 values (1, ' abc '); Insert Content
Update db1.t1 set name= ' AAA ' where id=1; Update
TRUNCATE TABLE db1.t1; Emptying the contents of a table
drop table db1.t1; Delete a table
Drop database db1; Delete a library

Backing up and recovering data
Backup library mysqldump-uroot-p123456 mysql >/tmp/mysql.sql
Recovery library mysql-uroot-p123456 MySQL </tmp/mysql.sql
Backup table mysqldump-uroot-p123456 mysql user >/tmp/user.sql
Restore table mysql-uroot-p123456 MySQL </tmp/user.sql restore only the library name is required.
Back up all libraries mysqldump-uroot-p-A >/tmp/123.sql
Back up table structure only mysqldump-uroot-p123456-d mysql >/tmp/mysql.sql

Usage of MySQL