MySQL Common operations

1. Forget the root password
Edit MySQL Master profile my.cnf in the [Mysqld] field to add parameters 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 user 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 the table of a library 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 = 1000;
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 contrast http://www.pureweber.com/article/myisam-vs-innodb/
A MySQL server starts multiple ports http://www.lishiming.net/thread-63-1-1.html
SQL Statement Tutorial http://blog.51cto.com/zt/206
SQL Tutorial PDF document http://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 engine http://c.biancheng.net/cpp/html/1465.html
Engine http://www.361way.com/change-mysql-engine/1729.html for bulk change tables
MySQL binary log binlog mode http://lihuipeng.blog.51cto.com/3064864/833017
MySQL restores data for a specified time period based on Binlog http://www.centoscn.com/mysql/2015/0204/4630.html
MySQL Character set tuning http://xjsunjie.blog.51cto.com/999372/1355013

MySQL Common operations