Common mysql operations

Common mysql operations

1. Forget the root password.
Edit the mysql main configuration file my. cnf adds the skip-grant parameter in the [mysqld] field and restarts the database service. In this way, you can enter the database without authorizing mysql-uroot and change the user password to use mysql; update user set password = password ('your password') where user = 'root'; flush privileges; finally Modify/etc/my. remove skip-grant from cnf and restart mysql.

2. skip-innodb we can add this parameter without using the innodb engine.

3. Configure slow query logs
# Log_slow_queries =/path/to/slow_queries
# Long_query_time = 1

4. Common mysql operations
View All databases of show databases;
View the table use db; show tables of a database;
View the desc tb field of the table;
View the table creation statement show create table tb;
Select user ();
Select database ();
Create database db1;
Create table t1 ('id' int (4), 'name' char (40 ));
View the database version select version ();
View the mysql status show status;
Modify the mysql parameter show variables like 'max _ connect % '; set global max_connect_errors = 1000;
View the mysql queue show processlist;
Create a common user and grant all on *. * to user1 identified by '20140901 ';
Grant all on db1. * to 'user2' @ '10. 0.2.100 'identified by '123 ';
Grant all on db1. * to 'user3' @ '%' identified by '000000'; 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;
Clear the table truncate table db1.t1;
Drop table db1.t1;
Drop database db1;
Repair table tb1 [use frm];

5. mysql backup and recovery
Back up mysqldump-uroot-p db> 1. SQL
Restore mysql-uroot-p db <1. SQL
Back up only one table mysqldump-uroot-p db tb1> 2. SQL
Specify the character set mysqldump-uroot-p -- default-character-set = utf8 db> 1. SQL during Backup
Recovery also specifies the character set mysql-uroot-p -- default-character-set = utf8 db <1. SQL

Extended knowledge:
Comparison http://www.pureweber.com/article/myisam-vs-innodb/ between myisam and innodb Engines
One mysql server starts multiple port 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/... 4259542599%e7%a8%8b.pdf
What is a transaction? What are the features of transactions? 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 batch change tables
Pattern http://lihuipeng.blog.51cto.com/3064864/833017 for binlog of mysql binary logs
Mysql restores data http://www.centoscn.com/mysql/2015/0204/4630.html for a specified period of time based on binlog
Mysql character set adjustment http://xjsunjie.blog.51cto.com/999372/1355013