Mysql in Case of

Add / Create a User:

CREATE USER <userName>@localhost IDENTIFIED BY "<newPassWd>";
CREATE DATABASE <newDbName> DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON <newDbName>.* TO <userName>@localhost;

Super PRIVILEGES:

GRANT ALL PRIVILEGES ON . TO <userName>@localhost WITH GRANT OPTION;

REVOKE INSERT ON . FROM <userName>@localhost;

    REVOKE ALL PRIVILEGES, GRANT OPTION FROM <userName>@localhost;

Reset the password:

SET PASSWORD FOR <userName>@localhost = PASSWORD('<newPassWd>');

reset root password: http://goo.gl/jGXR6

Injection

mysql injection protection methods

Using an abstraction layer:

1. mysqli

2. ADODB

3. ODBC

4. PDO

Using applications & frameworks:

1. CakePHP: $this->MyModel->query

6. WordPress: global $wpdb;

Backup and Restore

**backup:** # mysqldump -u root database_name -p > dumpfilename.sql

or:
   mysqldump -u dump_user database_name -p 'pass' **--routines** --single-transaction --default-character-set=utf8 > dumpfile.sql
 (http://code.openark.org/blog/mysql/parameters-to-use-on-mysqldump)
**restore:**# mysql -u root database_name -p password < dumpfilename.sql

Manual:

Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.

Use of --compact is the same as specifying --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.

To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact).

Myisam VS. Innodb

                                             My ISAM   InnoDB
Full text Search                                Yes     
Transactions                                    Yes
Frequent select queries                         Yes     
Frequent insert,update,delete                           Yes
Row Locking (multi processing on single table)          Yes
Relational base design                                  Yes

http://stackoverflow.com/questions/20148/myisam-versus-innodb

Performance Test/Benchmark 数据库的性能测试

https://groups.google.com/forum/?fromgroups=#!topic/shlug/hT4tpIvlE8M

Memcached

http://crazyant.net/2012/08/30/使用php连接、操纵memcached的原理和教程/

http://hi.baidu.com/khzmylyasdhjmwr/item/c9eb1988a42c935927ebd9c5

http://blog.csdn.net/adparking/article/details/6179315

Several Ways to Set Up LAMP Environment

1\. sudo apt-get install lamp-server^

2\. sudo apt-get install apache2 mysql-server php5
   sudo apt-get install libapache2-mod-php5 php-mysql phpmyadmin

or:

   sudo yum install httpd mysql-server php php-mysql ; 

       mysql_secure_installation

Updating From MySQL 5.1 to MySQL 5.5

Got errors in log:

_Native table 'performance_schema'.xxx has the wrong structure_ , and :

_Cannot load from mysql.proc. The table is probably corrupted.

    _

Solution:

mysql stop && mysqld_safe --skip-grant-tables;

    mysql_upgrade; kill ... ; mysql start;

Garbled 乱码

Problem check

SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

Solution (config in my.ini)

[client] 
port = 3306 
#socket = mysql  ## has problem when connect via socket
#default-character-set=utf8
#collation-server=utf8_general_ci

[mysqld] 
port = 3306 
#socket = mysql 
init_connect='SET collation_connection = utf8_general_ci' 
init_connect='SET NAMES utf8'
character-set-server = utf8
#default-character-set=utf8 
#collation-server = utf8_general_ci 

[mysql] 
#default-character-set=utf8  

PS: when using default-character-set, log got:

[ERROR] unknown variable 'default-character-set=utf8'

InnoDB: mmap(xxxxxxxx bytes) failed; errno 12

When:

Initializing buffer pool, size = xxx M

Solution: kill apache, start mysql, start apache.

REF: http://dba.stackexchange.com/questions/25165/intermittent-mysql-crashes-with-error-fatal-error-cannot-allocate-memory-for-t

Reference

[INJ] http://bobby-tables.com/php.html

[BAK] http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/

[GARa] http://www.itxx.com.cn/data/mysql/data_1969.html

[GARb] http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html ; http://forums.mysql.com/read.php?103,28072,28072#msg-28072