"CentOS" Lamp related 4

Mysql
TAB completion not supported
. mysql_history Command History

Login with socket: mysql-uroot-p123456,mysql-uroot-p123456-s/var/lib/mysq/mysq.sock Specify socket File connection
Log in as TCP/IP: mysql-uroot-p123456-h127.0.0.1-p3306 (Specify host and port number)

1. Forget the root password
Edit the Master profile my.cnf add the parameter skip-grant under the [Mysqld] field and restart the database service so that you can enter the database without authorization Mysqld-uroot
Modify the corresponding user password use MySQL; (Update user table) update user set Password=password (' Your password ') where user= ' root '; flush privileges;
Last modified/ETC/MY.CNF remove skip grant and restart MySQL service

2.skip-innodb We can add this parameter does not work with InnoDB engine

3. Configure slow query log (what is slow query)
#log_slow_queries =/path/to/slow_queries
#long_query_time =1

In contrast to MyISAM and InnoDB, the probability of an interview being questioned is as high as 90%.

4.MySQL Common operations
See which libraries show databases;
View a table for a library use db;show table;
View the table's fields desc TB;
View the Build Table statement show create TABLE TB; Show create table tb\g format;
which user (s) are 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;
View MySQL parameters show variables like ' max_connect% ';
Modify MySQL parameter set global max_connect_errors=1000; (will change after reboot, unless modified in my.cnf)
View MySQL queue show processlist;/show full processlist (equivalent to PS inside Linux)
Create a regular user and authorize grant all on * * (What library what table means) to User1 identified by ' 123456 '
Grant all on db1.* to ' user2 ' @ ' 10.0.2.100 ' (source IP) identified by ' 111222 ';
Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' 23122 '; Insert into TB1 (id,name) VALUES (1, ' aming ');
Change Password UPDATE mysql.user SET password=passwird ("newpasswd") WHERE user= ' username ';
Query select COUNT (*) from Mysql.user; SELECT * FROM Mysql.db;select * from mysql.db host like ' 10.0.% ';
Insert Update db1.t1 set name= ' AAA ' where id=1;
Emptying Tables truncate TABLE TAB1
drop table Db1,t1
Delete database drop databases db1;
Hotfix table Repair Table Tb1 [use frm];use_frm is used to repair with the frm file

5.MySQL Backup and Repair
Backup Mysqldump-uroot-p db > 1.sql
Just back up the statement that created the table mysqldump-uroot-p-D db >/tmp/post.sql
Fix Mysql-uroot-p DB <1.sq;
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
Fix also specifies the character set mysql-uroot-p--default-character-set=utf8 db <1.sql

Expand your knowledge

"CentOS" Lamp related 4