2015-05-08lamp part fourth MySQL operation

Course Outline:
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

MySQL Remote backup and recovery

telnet to MySQL

116.211.105.59 Authorize first

Mysql-uroot-paming

Mysql> Grant All on * * to ' root ' @ ' 58.53.94.11 ' identified by ' 123aaa ';

Mysql> Select User ();

+----------------+
| User () |
+----------------+
| [email protected] |
+----------------+
1 row in Set (0.00 sec)

Mysql> Select Database ();

Mysql> Select Database ();

+------------+

| Database () |

+------------+

| NULL |

+------------+

1 row in Set (0.00 sec)

Mysql> Grant all on discuz.* to ' user1 ' @ ' 192.168.10.* ' identified by ' 123aaa ';

Discuz

User1

123aaa

mysql> flush Privileges;

Mysql> show Processlist;

58.53.94.11 Login

[[email protected] ~] # mysql -uroot -h116.211.105.59-p3306-p123aaa

[email protected] /]# mysqldump--host 111.47.123.72-uroot-paminglinux discuz >/data/discuz.sql
Mysqldump:got error:1130:host ' 111.47.123.67 ' isn't allowed to connect to this MySQL server when trying to connect
[email protected] /]# mysqldump--host 111.47.123.72-uroot-paminglinux discuz >/data/discuz.sql
[email protected] /]# cd/data
[email protected] data]# ll
Total 2308
-rw-r--r--1 root root 2361322 3 22:27 discuz.sql

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

[email protected] ~]# Find/-name mysqldump
/usr/local/mysql/bin/mysqldump

[email protected] mysql]# mysqldump-uroot-paming discuz >/data/discuz.sql/1.sql

Linux MySQL erase password

1. Log in as root: Mysql-u root-p
2, Mysql>use MySQL;
3. Mysql>update user set password= ' where user= ' root ';

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
Use Xtrabackup to back up the InnoDB engine's database http://www.aminglinux.com/bbs/thread-956-1-1.html
Innobackupex backup Xtrabackup Incremental backup http://www.aminglinux.com/bbs/thread-1012-1-1.html

2015-05-08lamp part fourth MySQL operation