Lamp (quad) MySQL operation

1. Forget the root password
edit the MySQL master profile my.cnf the [mysqld] field to add the parameter skip-grant, restart the database service, so that you can enter the database without authorization mysql-uroot, modify the corresponding user password use MySQL; update us  ER set Password=password (' Your password ') where user= ' root '; flush privileges; Last modified/etc/my.cnf remove skip-grant, restart MySQL service

2. Skip-innodb We can add this parameter without using the InnoDB engine.

3. Configure Slow query log
#log_slow_queries =/path/to/slow_queries
#long_query_time = 1

4. MySQL Common operation
See which libraries show databases;
View a library's table use DB; show tables;
View the table's fields desc TB;
View the Build Table statement show create TABLE TB;
which user is currently Select users ();
Current Library Select Database ();
Creating a library Create database db1;
CREATE TABLE T1 (' id ' int (4), ' name ' char (40));
View Database version select version ();
view MySQL status show status;
modify MySQL parameters show variables like ' max_connect% '; set global max_connect_errors = +;
view MySQL queue show processlist;
create a regular user and authorize grant all on *. User1 identified by ' 123456 ';
Grant all on db1.* to ' user2 ' @ ' 10.0.2.100 ' identified by ' 111222 ';
Grant all on db1.* to ' user3 ' @ '% ' identified by ' 231222 '; insert into TB1 (id,name) VALUES (1, ' aming ');
Change Password UPDATE mysql.user SET password=password ("Newpwd") WHERE user= ' username ';
Query Select COUNT (*) from Mysql.user, select * FROM Mysql.db, select * from mysql.db where host like ' 10.0.% ';
Insert Update db1.t1 set name= ' AAA ' where id=1;
Empty tables truncate TABLE db1.t1;
Delete tables drop table db1.t1;
Delete database drop databases db1;
Fix tables Repair table tb1 [use frm];

5. mysql Backup and recovery
backup Mysqldump-uroot-p db >1.sql
recover Mysql-uroot-p db <1.sql
back up only one table Mysqldump-uroot-p db tb1 > 2.sql
Specify character set mysqldump-uroot-p--default-character-set=utf8 db >1.sql when backing up
Restore also specifies the character set mysql-uroot-p--default-character-set=utf8 db < 1.sql

Extended Knowledge:
MyISAM and InnoDB engine comparisonhttp://www.pureweber.com/article/myisam-vs-innodb/
a MySQL server starts multiple portsHttp://www.lishiming.net/thread-63-1-1.html
SQL statements Tutorialhttp://blog.51cto.com/zt/206 
SQL Tutorial PDF documenthttp://class.ccshu.net/00864091/...%95%99%e7%a8%8b.pdf
What is a transaction?  What are the characteristics of a transaction? http://blog.csdn.net/yenange/article/details/7556094
MySQL Common engineHttp://c.biancheng.net/cpp/html/1465.html
engine for bulk change tablesHttp://www.361way.com/change-mysql-engine/1729.html
mysql binary log binlog modehttp://lihuipeng.blog.51cto.com/3064864/833017
MySQL restores data for a specified time period based on BinlogHttp://www.centoscn.com/mysql/2015/0204/4630.html
MySQL Character set adjustmenthttp://xjsunjie.blog.51cto.com/999372/1355013
backing up the InnoDB engine's database using XtrabackupHttp://www.aminglinux.com/bbs/thread-956-1-1.html
Innobackupex backup Xtrabackup Incremental backupHttp://www.aminglinux.com/bbs/thread-1012-1-1.html

Lamp (quad) MySQL operation