Part Four MySQL related

1. Forget the root password
Http://www.lishiming.net/thread-252-1-1.html

Go to MySQL
which MySQL
/usr/local/mysql/bin/mysql You can open MySQL like this before you set a password

Login/usr/local/mysql/bin/mysql-uroot-p123456

If you forget the root password or other user password, do not worry, just follow the steps below.
1. Edit the MySQL Master profile my.cnf
Vim/etc/my.cnf
Add a parameter under the [Mysqld] field
Skip-grant does not require authorization
2. Restart the database service
Service mysqld Restart
3. This allows access to the database without authorization.
Mysql-uroot

4. Modify the corresponding user password
Use MySQL;
Update user set Password=password (' Aminglinux ') where user= ' root ';

Flush privileges;
5. Modify/ETC/MY.CNF remove skip-grant, restart MySQL service
Restart
/etc/init.d/mysqld restart

Login/usr/local/mysql/bin/mysql-uroot-paminglinux

See User table
Use MySQL;
SELECT * from User\g;

=============
2.
Skip-name-resolve (prohibit resolving domain names)
Skip-innodb (is the MySQL engine)

VIM/ETC/MY.CNF in the configuration with the above two, and then in/data/mysql a few files will disappear

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

To view the configuration file, the following file is found by default
Vim/etc/init.d/mysqld go in and find CONF=/ETC/MY.CNF.

4. InnoDB and MyISAM common MySQL engine, summarize
Http://www.lishiming.net/thread-97-1-1.html

5. mysql Configuration tuning
Http://www.lishiming.net/thread-5758-1-1.html

6. MySQL Common operation
Mysql-uroot-paminglinux
or Mysql-uroot-h127.0.0.1-paminglinux.
Mysql-uroot-h192.168.11.190-p3306-paminglinux

Mysql-uroot-s/tmp/mysql.sock-paminglinux can use sock to communicate, can only be used in the local

See which libraries show databases;
command System LS called
View the table of a library use DB; Show tables;
View the field structure of a table desc TB;
View Build Table statement Show create TABLE tb\g; \g show a little more regular.

Which user is currently Select users ();
Current Library Select database ();
Creating a library Create database db1; MYSQL-UROOT-PAMINGLINUX-E "CREATE Database Discuz2"
CREATE TABLE T1 (' id ' int (4), ' name ' char (40));
INSERT into T1 values (1, ' aming ');
INSERT INTO T1 (' id ') values (2); Insert a single field
SELECT * from T1;

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; ===============

Grant all on * * to ' root ' @ ' 10.0.2.100 ' identified by ' 123456 '; *. * represents all libraries and tables authorized

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 ';
Flush privileges; Refresh the disk
Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' 231222 ';

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.% ';

Updated update db1.t1 set name= ' AAA ' where id=1;
Delete table field Delete from T1 where id=3;
Clears the table's data truncate tables DB1.T1;
Delete tables drop table db1.t1;
Delete database drop databases db1;
Fix tables Repair table tb1 [use frm];
Show Processlist; See which show full processlist the tasks are;
Show variables like '%timeout% ';

In the configuration file, both must be used at the same time from the
Interactive_timeout = 10
wait_timeout=10

If you change the MySQL configuration file my.conf will need to restart MySQL, now do not need to restart the method can be effective (not the global, the global need to write to the configuration file inside)
Set global interactive_timeout = 10;

Put the restart of the service into the system under the environment/etc/rc.local, you can start automatically effective =========
/usr/local/apache2/bin/apachectl start

View a table of post content
SELECT * from Pre_forum_post\g;

Search posts
SELECT * from Pre_forum_post where subject like '%sjs% ';

7. mysql Backup and recovery
Backing up the database Mysqldump-uroot-paminglinux db >1.sql
Recover Mysql-uroot-paminglinux DB <1.sql

MYSQL-UROOT-PAMINGLINUX-E "CREATE Database Discuz2"

Specify character set Mysqldump-uroot-paminglinux--default-character-set=utf8 db >1.sql when backing up
Back up table structure only mysqldump-uroot-paminglinux--default-character-set=utf8-d db >1.sql
Restores the specified character set Mysql-uroot-paminglinux--default-character-set=utf8 db < 1.sql
Back up only one table Mysqldump-uroot-paminglinux db tb1 > 2.sql

8. A MySQL server starts multiple ports
Http://www.lishiming.net/thread-63-1-1.html

Http://www.aminglinux.com/bbs/forum.php?mod=viewthread&tid=6509&highlight=phpmyadmin

Installing the Mysqlamin Management platform

Part Four MySQL related