Mysql in Case of

Mysql in Case of

2012-05-19. Category & Tags: Soft&Skills Soft&Skills

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:

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 [INJ]

Using an abstraction layer: #

1. mysqli

2. ADODB

3. ODBC

4. PDO

Using applications & frameworks: #

CakePHP: $this->MyModel->query

6. WordPress: global $wpdb;

Backup and Restore [BAK] #

**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

3. for each ($lamp as $component) { git clone xxxx; && ./configure && make && make install; }

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.  
_

Reason: (see REF).

Solution:

mysql stop && mysqld_safe --skip-grant-tables;  
mysql_upgrade; kill ... ; mysql start;

REF: http://bugs.mysql.com/bug.php?id=58969

Garbled 乱码 #

Problem check [GARa] #

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

Solution (config in my.ini) [GARb] #

[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

Reason: there is no enough memory.

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