find tables of a particular storage engine in mysql

If you have a database with some tables using MyISAM and some using InnoDB, you probably want to find out which tables are using InnoDB. 




SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name' AND ENGINE = 'InnoDB';




It is also easy to find out a specific table's storage engine:




SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME= 'table_name';

MySql stored procedure commands

1. To show all stored procedures of a database:

SHOW PROCEDURE STATUS where DB = 'databasename';

2. Dump(export) all stored procedures of a database

mysqldump  -uuser -ppassword  --routines databasename > outputfile.sql

mysql run query from command line

I've seen many times that a developer login mysql just to run a single/simple query. This is usually what he would do:
mysql -uuser -ppassword db;
mysql> select * from users;

Instead, we can run a mysql query from command line by using option -e, --execute=name. it means execute command and quit. For example:

mysql -uuser -ppassword db -e "select * from users";

or

mysql -uuser -ppassword -e "select * from db.users";

Usually when we want to install some open source PHP software, we need to create the database first. We can create a mysql database from command line as well:

mysql -uuser -ppassword -e "create database db";

mysql innodb row level locking

We know that MySql InnoDB engine provides row level locking, while MyISAM can only do table level lock. But, InnoDB's row level locking mechanism may not be as what you expect. InnoDB doesn't really lock rows of data, instead, it set locks on on every index record that is scanned in the processing of the SQL statement. This means, only when your SQL statement is using index to query data, InnoDB will use row level lock. Otherwise, InnoDB will use table level lock. If we don't pay attention to this, we may end up with lots of lock conflicts in our application.

Let's do some simple tests.

mysql> create table no_index_lock_test(id int,name varchar(10)) engine=innodb;

Query OK, 0 rows affected (0.15 sec)

mysql> insert into no_index_lock_test values(1,'henry'),(2,'alice'),(3,'bob'),(4,'jack');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

Let's start two sessions

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from no_index_lock_test where id = 1 ; +------+------+ | id   | name | +------+------+ | 1    | 'henry' | +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from no_index_lock_test where id = 2 ; +------+------+ | id   | name | +------+------+ | 2    | 'alice'  | +------+------+ 1 row in set (0.00 sec)
mysql> select * from no_index_lock_test where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 'henry'    | +------+------+ 1 row in set (0.00 sec)
	mysql> select * from no_index_lock_test where id = 2 for update; Will keep waiting...

This example show us InnoDB can only use table level locking if no index is available. In session 1, it looks like we only set a lock on one row (where id=1 for update). But we don't have index on id column, InnoDB is actually locking the whole table, so in session two, when we try to set a lock to a different row(where id=2 for update), we have to wait until the lock in session 1 is released.

Now, let's add index to our id column.

mysql> create table index_lock_test(id int,name varchar(10)) engine=innodb;

Query OK, 0 rows affected (0.15 sec)

mysql> alter table index_lock_test add index id(id);

Query OK, 4 rows affected (0.24 sec)

mysql> insert into index_lock_test values(1,'henry'),(2,'alice'),(3,'bob'),(4,'jack');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from index_lock_test where id = 1 ; +------+------+ | id   | name | +------+------+ | 1    | 'henry' | +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from index_lock_test where id = 2 ; +------+------+ | id   | name | +------+------+ | 2    | 'alice'  | +------+------+ 1 row in set (0.00 sec)
mysql> select * from index_lock_test where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 'henry'    | +------+------+ 1 row in set (0.00 sec)
	mysql> select * from index_lock_test where id = 2 for update; +------+------+ | id   | name | +------+------+ | 2    | 'alice'  | +------+------+ 1 row in set (0.00 sec)

This time, InnoDB is using row level lock.

mysql replace text in field

Task: Assume that we have a table called articles. This table has a column/field called title. We have a lot of titles like "ubuntu administration tips xxx". One day we found that all these tips actually apply to all linxu systems, so we want to change all the titles from "ubuntu administration tips xxx" to "linux administration tips xxx"

Here is how we can do it quickly:

UPDATE articles SET title = REPLACE(title, 'ubuntu', 'linux');

mysql event scheduler

Since Mysql 5.1.6, it supports a feature: event. Using this feature, we can make mysql run schedule tasks that we used to do in cron.

First, we activate the the event_scheduler

SET GLOBAL event_scheduler = 1;

Now, we can create an event

use test;

create table event_test (value datetime);

create event update_event on schedule every 1 second 

do

insert into event_test values(now());

Let 's wait for seconds and check the result:

select * from event_test;

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

| value               |

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

| 2011-05-17 11:56:14 |

| 2011-05-17 11:56:15 |

| 2011-05-17 11:56:16 |

| 2011-05-17 11:56:17 |

| 2011-05-17 11:56:18 |

| 2011-05-17 11:56:19 |

| 2011-05-17 11:56:20 |

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

three handy mysql table operation

1. replicate a table & its data

create table new_table as select * from old_table

However, keep in mind that the new_table is myisam! So if your old_table is innodb, the new_table won't contain the key constraints

2. copy a table structure only (without its data)

create table new_table like old_table

3.rename a table

rename table old_table to new_table

how to use left join in mysql

Let's setup two tables for our experiment.

create table student(id int primary key auto_increment, name varchar(255));

insert into student (name) values('henry'),('jack'),('alice');

create table class_a(id int primary key auto_increment, student_id int, registered_datetime datetime);

insert into class_a(student_id, registered_datetime) values(1,now());

We use a simple left join to see the result:

select s.*,c.id as class_id from student s left join class_a c on s.id = c.student_id;
+----+-------+----------+
| id | name  | class_id |
+----+-------+----------+
|  1 | henry |        1 | 
|  2 | jack  |     NULL |
|  3 | alice |     NULL |
+----+-------+----------+

We can see that left join returns all rows from the left table(student) even the student may not have a match in the right table(class_a). In this case, any c

So thing are quite obvious now. If we only want to get the students that are not registered in class_a, we can simply add a filter for left join:

select s.*,c.id as class_id from student s left join class_a c on s.id = c.student_id where c.id is null;
+----+-------+----------+
| id | name  | class_id |
+----+-------+----------+
|  2 | jack  |     NULL |
|  3 | alice |     NULL |
+----+-------+----------+

Magento Hierarchical Category Database Design

First of all, there are two ways to manage hierarchical data in MySql(or other relational database). One is Adjacency List Model and the other is The Nested Set Model. They both have pros and cons. Again, i recommend you read this article http://dev.mysql.com/tech-resources/articles/hierarchical-data.html first before you start to work on hierarchy related issue.

Magento uses Adjacency List Model for some reasons, which i don't know what they exactly are. The problem of Adjacency List Model is traversing hierarchy tree is very difficult and low efficient. Usually you have to recursively query database to find out the depth/level of a node.

To address the problem of Adjacency List Model, Magento's category table has three extra columns:level, path, children_count. As the names suggest, 'level' stores the level of a node, 'path' saves the path from the root node to this node and 'children_count' records the number of children the node has. If you are interested in what they really look like in Magento's database, you can simply download and install Magento.

As you can imagine, with the three extra columns, traversing hierarchy tree becomes extremely easy.

You may also find out that in this way, the difficult part goes to manipulating: adding/deleting/moving a node. For each of these operations, you have to update those three extra columns for the nodes involved in your operation.

However, there is no perfect solution to hierarchy data(even in Nested Set Model, manipulation is very hard) because relational database is not designed to handle hierarchical data, not like XML. It is a trade-off. We either sacrifice one side for the other side. And i think traversing hierarchy is obviously the most critical part.

MYSQL主从数据库同步备份

首先两台机器：
a: 192.168.0.1
b: 192.168.0.2


打开a机器的my.cnf/my.ini设置

首先要保证要同步的数据库内容完全一致

在[mysqld]下添加以下内容
server-id=1

log-bin=/var/db/mysql/master.log

binlog-do-db=要同步的数据库名称 //不写就同步全部。
#binlog-ignore-db = mysql //忽略的数据库

增加一个backup的用户

GRANT FILE,REPLICATION SLAVE,REPLICATION CLIENT,SUPER ON *.* TO backup@'192.168.0.2' IDENTIFIED by 'backuppassword';
这个权限表示，这个backup账号只能由从备份机192.168.0.2访问只能用来进行备份操作


因为要实现双向备份，所以a上还要加以下内容。 如果是单向，则不需要加。
master-host=192.168.0.2
master-user=backup
master-password=backuppassword
master-port=3306
master-connect-retry=60
replicate-do-db=要同步的数据库
#replicate-ignore-db=不同步的数据库

打开b机器的my.cnf/my.ini文件
添加或修改以下内容
server-id=2

master-host=192.168.0.1
master-user=backup
master-password=backuppassword
master-port=3306
master-connect-retry=60
replicate-do-db=要同步的数据库
#replicate-ignore-db=不同步的数据库

如果要双向同步则再加上以下内容

log-bin=/var/db/mysql/master.log
binlog-do-db=要同步的数据库名称
#binlog-ignore-db = mysql //忽略的数据库

再增加一个帐号给a
GRANT FILE,REPLICATION SLAVE,REPLICATION CLIENT,SUPER ON *.* TO backup2@'192.168.0.1' IDENTIFIED by 'backuppassword';


至此基于mysql的同步功能就做好了。
重起两边的服务器。 用show master status查看主服务器状态。 用 show slave status 查看从服务器状态。

slave stop; 停止从服务器
slave start; 启动从服务器
master stop; 停止主服务器
master start; 启动主服务器

用show processlist可以查看同步状态。
如果有什么错误打开mysql的 hostname.err 查看原因，再调用下面的修改命令：

CHANGE MASTER TO
MASTER_HOST='master_host_name',
MASTER_USER='master_user_name',
MASTER_PASSWORD='master_pass',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;

然后再重起slave;

install multiple mysql instances on windows

1.正常安装Windows版的Mysql，例如安装在d:\mysql文件夹里；

2.按照常规配置好Mysql；

3.复制备份安装好的文件夹，比如备份到另外一个文件夹，或者命名为“复件mysql";

4.运行卸载程序删除安装的MYSQL和文件夹；

5.把备份的mysql文件夹，重新恢复原来的名字，或还原到原来的位置上；

6.再次运行安装程序，安装在另外一个目录，例如：“d:\mysql2"。配置端口为3307;

现在，后面安装的第二个Mysql——mysql2肯定是可以正常运行的，下面让起初安装的第一个Mysql

服务正常运行：

在cmd命令行模式下，进入第一个Mysql的\bin文件夹。

执行mysql-nt install mysql2命令。作用是为Mysql安装一个服务，服务名是mysql2，你也可以自己改名字，比如mysql5，那么命令就是：

mysql-nt install mysql5

完毕后，在运行里运行REGEDIT，打开WINDOWS注册表编辑器，在HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\项目找到你刚才手动安装的服务，

我这里是mysql2。打开，修改下面的ImagePath数值。

你一般将会看到"D:\mysql\bin\mysqld-nt" mysql2类似的数值。

在mysql-nt"和mysql2之间加入my.ini的路径信息，格式为：

--defaults-file="d:\mysql\my.ini"

其中粗体部分替换你的正确的路径，

修改完毕后数据数值一般是类似这个样子：

"D:\mysql\bin\mysqld-nt"
--defaults-file="d:\mysql\my.ini" mysql2

确定，退出注册表编辑器。

然后就可以正常启动mysql2服务了，在CMD窗口下输入命令：

net start mysql2

一般应能正常启动了。

我的经历, 按以上配置好后, 我无法同时启动两个MYSQL服务, 经研究, 原因是

MYSQL5.1把DATA文件夹分离出来, 放在datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/", 要修改其中一个MY.INI配置, 把DATA文件夹放在另一个不同的地方, 就可以了.

Mysql Optimization - schema optimization

1. Choose optimal data type
a. smaller is better - less space on disk, in memory, in CPU cache
b. simple is good (use integer instead of ip address, for example);
c. avoid null if possible - null requires special processing, makes index, comparisons more complicated.

2.Index strategies
a. isolate the column. mysql can't use index unless the columns are isolated in query
e.g., suppose id is primary key.
select * from test where id+1 = 5; should be changed to select * from test where id = 4

b. Prefix index
e.g., if u need to index long character columns, index the first few characters
alter table test add key (long_name(5));
downside is prefix index cannot be used for order by or group by

c. covering indexex
e.g. an index containing all the data needed for a query is covering index
-index entries are smaller and faster than row size
-indexes are sorted by their values

Normalization and Denormalization
A mixture of Normalized and Denormalized