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 <span style="text-decoration: underline;">PRIVILEGES</span> ON *.* TO &lt;userName&gt;@localhost WITH GRANT OPTION;

Revoke:

REVOKE INSERT ON *.* FROM &lt;userName&gt;@localhost; REVOKE ALL PRIVILEGES, GRANT OPTION FROM &lt;userName&gt;@localhost;

 

Reset the password: #

SET PASSWORD FOR &lt;userName&gt;@localhost = PASSWORD('&lt;newPassWd&gt;');

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: #

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

6. WordPress: global $wpdb;

 

Backup and Restore [BAK] #

**backup:** # mysqldump -u root database_name -p &gt; dumpfilename.sql or: mysqldump -u dump_user database_name -p 'pass' <span style="text-decoration: underline;">**--routines**</span> --single-transaction --default-character-set=utf8 &gt; dumpfile.sql (http://code.openark.org/blog/mysql/parameters-to-use-on-mysqldump) **restore:**# mysql -u root database_name -p password &lt; 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

  1. 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 &amp;&amp; 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