Tag: MySQL

MySQL 主从复制基本实验

最近这一两个月业余时间基本都在看 MySQL 的书，看明白了一些，又忘了一些，哈哈，总的来说没有什么很大的收获
今天无聊了，弄个 Replication(http://dev.mysql.com/doc/refman/5.1/en/replication.html)来玩玩
MySQL 的 Replication 可以用在很多地方，比如平衡负载，备份等等
主要它复制的时候是一个异步的过程(有没有将随机读写变成顺序读写这个值得研究，对于大多数磁盘来说，顺序读写可能更快些)，可以从某些程度上来说提高性能，当然生成 Binary-log 也是有代价的，不过会换来更多的好处(在网络传输影响很小的情况下)，比如读写分离将读压力分出去了，比如多机备份

也有很多人写类似的文章和书籍，我也就记录个实践的过程

我这里实践所有的环境都是 Linux 2.6.35-28-generic #50-Ubuntu SMP Fri Mar 18 19:00:26 UTC 2011 i686 GNU/Linux，两个数据库，一个 5.1.57-log 作为 Master，一个 5.5.8 作为 Slave，都装在一台机器上，启用不同的配置和端口
目的就想在 Master 插入一条数据，在 Slave 也能看到，性能以及安全的配置都没有考虑

看官方配置说明的时候就需要弄清楚哪些是在 Master 上配的，哪些是在 Slave 上配的

因为我的 Slave 是 5.5，以前的版本，比如 5.1 中可用的

master-user   =   repl
master-password   =   repl
master-port   =   3307

这些配置文件参数在 5.5 的 Slave 就不能用了，如果你不小心配置了这些参数，MySQL 服务器将无法正常启动
看下 MySQL 日志你应该会发现类似于这样的提示(unknown variable ‘master-host=‘)

2121 110809 22:53:21 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2122 110809 22:53:21 [Note] Plugin 'FEDERATED' is disabled.
2123 InnoDB: The InnoDB memory heap is disabled
2124 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2125 InnoDB: Compressed tables use zlib 1.2.3
2126 110809 22:53:21  InnoDB: Using Linux native AIO
2127 110809 22:53:21  InnoDB: Initializing buffer pool, size = 128.0M
2128 110809 22:53:21  InnoDB: Completed initialization of buffer pool
2129 110809 22:53:21  InnoDB: highest supported file format is Barracuda.
2130 110809 22:53:21  InnoDB: 1.1.4 started; log sequence number 42505789
2131 110809 22:53:21 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'master-host=127.0.0.1'
2132 110809 22:53:21 [ERROR] Aborting
2133 
2134 110809 22:53:21  InnoDB: Starting shutdown...
2135 110809 22:53:22  InnoDB: Shutdown completed; log sequence number 42505789
2136 110809 22:53:22 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
2137 
2138 110809 22:53:22 mysqld_safe mysqld from pid file /usr/local/mysql/data/KNIGHT.pid ended

那么在 5.5 的 Slave 中我是先配置了这几个参数

replicate-do-db = replication
replicate-ignore-db = mysql
replicate-ignore-db = information_schema

有意的指定和排除了一些库参与同步
之后启动 Slave，然后通过 change master to master_host=’127.0.0.1′, master_port=3307, master_user=’repl’, master_password=’repl’, master_log_file=’master-cutoe-binlog.000001′, master_log_pos=600, master_connect_retry=10;
slave start;
来实现 Slave 的开始复制

Master 配置文件主要参数如下：

server-id = 1
log-bin = master-cutoe-binlog
binlog_format = mixed
sync_binlog = 1
innodb_flush_log_at_trx_commit = 2

Slave 配置文件主要参数如下：

server-id = 2
replicate-do-db = replication
replicate-ignore-db = mysql
replicate-ignore-db = information_schema

如果配置都对了的话，先起 Master，再起 Slave
在 Master 和 Slave 中创建好需要复制的库和表，比如
create database replication;
create table user(id int primary key auto_increment, name varchar(20), age smallint);
(让复制自动在 Slave 中创建和 Master 一样的表在这里我没有尝试，有空可以试试)
还要在 Master 中创建一个 Slave 用来访问 Master 的用户

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';  -- 为什么不能指定该用户只对单独的库具有复制权限，目前不清楚原因

然后在 Master 中 mysql> show master status;
在 Slave 中通过 change master to，slave start 开启复制
并 mysql> show slave status \G
就应该会看到 Slave_IO_State: Waiting for master to send event
这个时候再在 Master 中执行个 Insert 语句，如果没有错误的话，应该可以在 Slave 的相应表中看到刚刚你在 Master 插入的一样的数据
基本可以通过 mysql> show slave status \G 查看到复制过程中发生的所有情况，例如错误信息

如果 Slave 复制失败，你可以根据错误信息进行修正，然后执行
mysql> slave stop;
mysql> slave start;
就可以把原来应该复制过来的数据都复制过来

别忘了一个很简单的命令也是要常用的
mysql> show processlist \G

Good Luck & Good Night…

MySQL临时表用法

MySQL目前还不支持动态游标(可能这个名词用在这里不准确)，但很多时候我们需要这样的一个功能(http://bugs.mysql.com/bug.php?id=17153)
比如在存储过程中根据传入的参数动态查询出数据，然后遍历数据，这个时候我们当然想动态的构建出这个游标出来

人民大众就开始想尽办法来实现类似的功能，比如下面的创建一个临时表来完成

mysql> CREATE TEMPORARY TABLE tmp_data (uid int(11) NOT NULL) ENGINE=MEMORY DEFAULT CHARSET=utf8;

注意这里为了提高速度用的是MEMORY引擎，当然它有一些限制行的东西，比如表的大小限制，某些类型的字段不能保存
可以根据自己的情况来抉择
更多信息可以参考http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
还有一些参考意义的测评也可以借鉴，比如http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

这里还给它增加了个索引
mysql> ALTER TABLE tmp_data ADD INDEX idx_uid (uid);
mysql> SHOW INDEX FROM tmp_data;

当然用完之后记得清理表，简单的方法就是
mysql> DROP TEMPORARY TABLE tmp_data;

临时表在不同连接之间是不可见的，但是在多线程的环境中，这个还是应该值得注意

还有更多的问题值得研究，比如create和drop临时表不会产生事务提交
但直接DROP TABLE tmp_data;也会删除临时表，但是会提交前面打开事务

临时表和正式表重名机制等等

另外，其实在MySQL内部它也大量使用了临时表，当然对程序员来说都是透明的，你不用关心它什么时候用，数据是放在内存还是放在硬盘上的
想看的话可以通过explain查看

有关MySQL奇技淫巧

有关MySQL奇技淫巧(一般都是Linux下试过的，其它平台自行尝试)

0、学会查看MySQL错误日志
日志名一般为HOST_NAME.err，在里面说不定很容易找到你想要的东西

1、开启MySQL General Query Log
想查看MySQL执行了哪些SQL语句？
那么就开启这个日志吧
http://dev.mysql.com/doc/refman/5.1/en/query-log.html
http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html
可以通过启动mysqld加入相关参数或者动态开启

当然最方便的还是动态开启（不用重启数据库，貌似对MySQL版本有要求）
中文参考：http://www.taobaodba.com/html/477_动态开启mysql-general-log.html

mysql> set global log_output = file;
mysql> set global general_log_file=’/home/guohai/dev/x/topics/mysql/sql.log’;
mysql> set global general_log=on;

还有个要求就是MySQL对该日志文件有读写权限，我把它的own和group全改成跟MySQL对应的了，要不然报错

关闭只需要
mysql> set global general_log=off;

2、innotop使用
去http://code.google.com/p/innotop/下载最新版的安装包，这里用的是innotop-1.8.0
按照安装说明安装好，更详细的安装说明可以参见http://crazytoon.com/2008/05/21/mysql-how-do-you-install-innotop-to-monitor-innodb-in-real-time/
可能innotop版本不一样，自己稍微变通下
第一次运行innotop的时候会叫你输入一些配置信息，比如数据库链接信息
这个你可以在~/.innotop/innotop.conf中修改
guohai@KNIGHT:~/.innotop$ tree
.
|– innotop.conf
`– plugins

1 directory, 1 file

man innotop是永远不能少的
另外可以参见http://5iwww.blog.51cto.com/856039/570669
http://blog.csdn.net/qiuyepiaoling/archive/2010/11/29/6042379.aspx

3、mysql命令的一些有用参数选项
生产环境中手工操作数据库是个技术活
有些参数可以方便我们获取需要的数据，或者节省我们的时间
一些明显的信息提示或许可以帮助我们减少出错的概率
比如
mysql>prompt \h:\u@\d>
PROMPT set to ‘\h:\u@\d>’
localhost:root@hpm>
这样你就明显的看到，你是在哪台机器，用户是什么，操作的数据库是什么

MySQL Command Line Pager
mysql>\P more
mysql>\P less
mysql>\P

下面是别人总结出来有用的
http://www.developer.com/db/10-command-line-timesavers-for-mysql-tasks.html
http://www.ningoo.net/html/2008/mysql_cmdline_html_xml_prompt.html
自己也可以查看手册找到一些有用的信息

4、create table log_20110630 like log_template;
like子句会复制表结构，包括索引等，不包括权限

create table log_20110701 as select * from log_20110630;
复制数据，不包括索引

试玩HandlerSocket for MySQL

有这么一个场景，单台MySQL数据库服务器，最大表约500万记录，常用表100万记录，每行记录的数据量不是很大
查询还算频繁，插入修改删除数据不是那么多，多个应用同时访问这个数据库
有个应用就是查询为主，没有修改，少量的删除
查询SQL语句很简单，根据用户ID查询他下面的记录（某些字段），根据记录ID查询整条记录，两表关联查询100万的关联500万
目前应用的实现也很简单，简陋的数据库连接池，客户端发送SQL语句到SQL服务器
现在也没有遇到很大的问题，没有宕机什么的问题，但就是客户端反映慢，但是看服务器的负载都不是很高

所以老大就说太慢了，需要解决

这个慢是比较显然的啊，每次解析执行SQL语句，网络传输，数据库服务器内存较小，缓存不够

所以当然就想到了在应用层加缓存，但是在几个月之前曾经看到过HandlerSocket的简单介绍，只知道它可以加快查询，没有细看

所以突然觉得目前这个也可以改用HandlerSocket，而且更合适，向老大建议，当然建议都是木有用的，因为我做不鸟主，老大有老大的想法

所以只能自己研究看看这个东西适合不了

于是就开始下载编译安装这个东西了，步骤还算简单

具体安装参见installation.en.txt

我开始想用5.1的源码和5.5的二进制版本混合编译（目前我自己用的5.5的MySQL），天知道能不能成功（最后是不能成功的，我又用5.1的源码重新编译了一个MySQL数据库出来，然后编译HandlerSocket并把它安装到5.1中了，后来看到介绍是源码版本一定要和当前安装的数据库的版本一致，之所以需要源码是因为HandlerSocket插件用到了MySQL源码之中的几个头文件之类的吧，总之不看README是很头疼的。比如有的酒店的电梯是要用房卡刷了电梯之后才能按楼层的，README写的清清楚楚，汗）
我的编译步骤如下
$ ./autogen.sh
$ ./configure –with-mysql-source=/home/guohai/dev/src/database/mysql-5.1.57 –with-mysql-bindir=/home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686/bin –with-mysql-plugindir=/home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686/lib/mysql/plugin

$ make
$ sudo make install

mysql> install plugin handlersocket soname ‘handlersocket.so’;
Query OK, 0 rows affected (0.03 sec)

mysql> show plugins;
+—————+——–+—————-+——————+———+
| Name | Status | Type | Library | License |
+—————+——–+—————-+——————+———+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| handlersocket | ACTIVE | DAEMON | handlersocket.so | BSD |
+—————+——–+—————-+——————+———+
8 rows in set (0.00 sec)

这样插件就安装好了，接下来就是装个客户端试试看了，用的淘宝的那个

当然还要在my.cnf中增加类似参数
loose_handlersocket_port = 9998
# the port number to bind to (for read requests)
loose_handlersocket_port_wr = 9999
# the port number to bind to (for write requests)
loose_handlersocket_threads = 16
# the number of worker threads (for read requests)
loose_handlersocket_threads_wr = 1
# the number of worker threads (for write requests)
open_files_limit = 65535
# to allow handlersocket accept many concurrent
# connections, make open_files_limit as large as
# possible.

弄hs4j还是报了几个错误，什么open_table，原因是我表名没有写对，巨汗，不过这个提示也够瓜的
估计也是我没有理解这个东西

然后我又报了个错误
Exception in thread “main” com.google.code.hs4j.exception.HandlerSocketException: Connection has been closed
at com.google.code.hs4j.impl.HSClientImpl.awaitResponse(HSClientImpl.java:417)
at com.google.code.hs4j.impl.HSClientImpl.insert0(HSClientImpl.java:325)
at com.google.code.hs4j.impl.HSClientImpl.insert(HSClientImpl.java:309)
at com.google.code.hs4j.example.HS4JExample.main(HS4JExample.java:29)
这个我把keys写少了一个，所以报错了
总之都是我的问题

就这样解决几个问题之后客户端就基本可用了

然后我还试了下Python的客户端，装好Python的插件http://packages.python.org/python-handler-socket/
就可以开始玩了，照着例子也很简单

看了还算多的文章介绍以及issues，这个东西在某些特定的情况下还是可以开始用的吧，以前提的比较多的问题，什么auto_increment之类的也都解决了

觉得最关键的是就是参数怎么配置，才能让这个东西在需要的场景下发挥最大的能力

还得多看看，多试试才有更充分的把握

参考
https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL
http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html 你懂的
https://github.com/killme2008/hs4j
http://whitesock.iteye.com/blog/811339
http://rdc.taobao.com/team/jm/archives/545
http://www.cnblogs.com/inrie/archive/2011/01/28/1946572.html 系列文章
http://huoding.com/2011/04/10/62

流水帐总是很简单，再把编译MySQL的主要过程也记在这里吧，凑点字数
我用的是Ubuntu 10.10 X86，以前编译过很多软件开发包，所以如果编译的时候遇到缺什么包装上就行了
下载就不说了，下载都不会的就不要从源码编译了，我用的是mysql-5.1.57.tar.gz
编译参见INSTALL-SOURCE
2.11.2. Installing MySQL from a Standard Source Distribution

$ ./configure –prefix=/home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686 –with-unix-socket-path=/tmp/mysql51.sock –with-tcp-port=3307 –with-named-curses-libs=/lib/libncurses.so.5 –enable-assembler –with-plugins=partition,innobase –with-charset=utf8 –with-collation=utf8_general_ci –with-extra-charsets=big5,ascii,gbk,utf8,latin1 –with-big-tables –without-debug

$ make
$ make install

$ cd /home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686
$ chown -R mysql .
$ chgrp -R mysql .

我以前已经装过MySQL5.5所以这些用户以及组都是有的

guohai@KNIGHT:~/dev/a/mysql/mysql-5.1.57-linux2.6-i686$ sudo cp ‘/home/guohai/dev/src/database/mysql-5.1.57/support-files/my-medium.cnf’ ./my.cnf

$ sudo bin/mysql_install_db –user=mysql –basedir=/home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686 –datadir=/home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686/data

启动数据库
$ sudo bin/mysqld_safe –defaults-file=/home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686/my.cnf –user=mysql &
注意这个参数传递的顺序，我开始一直把–user=mysql放前面，启动的时候报错，MySQL日志记载unknown variable ‘defaults-file’
110601 23:23:28 [ERROR] /home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686/libexec/mysqld: unknown variable ‘defaults-file=/home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686/my.cnf’
110601 23:23:28 [ERROR] Aborting
110601 23:23:28 InnoDB: Starting shutdown…
110601 23:23:33 InnoDB: Shutdown completed; log sequence number 0 44233
110601 23:23:33 [Note] /home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686/libexec/mysqld: Shutdown complete

然后看了mysqld_safe下这个脚本的写法，发现它有对传入的第一个参数判断是否为defaults，于是我就把defaults-file这个参数放到第一位，就正常启动了

一切都好了，然后就是设置了
/home/guohai/dev/a/mysql/mysql-5.1.57-linux2.6-i686$ sudo bin/mysql_secure_installation
一步步来就好了，注意默认my.cnf的位置，我的就放在mysql-5.1.57安装目录下

可能还要修改下MySQL数据文件存放的路径，我最开始编译的时候没有指定，然后它帮我生成的是放在mysql-5.1.57安装目录的一个var目录下面的，而我在my.cnf中指定的是data目录
所以我就修改了一个脚本中的，把var改为data了，具体是哪个脚本忘记了

再重启下，就可以用mysql命令连接上去了，基本正常

MySQL编译简单，关键是编译出自己想要的MySQL，编译出符合自己业务高性能的MySQL就是技术活了，参数很关键
只要参数用的好，没有程序写不鸟；只要工具用的好，没有程序写不鸟；夸张了点

还有刚开始编译出来你可能找不到mysqld这个东西，我反正一开始是没有找到，以为自己编译的出错了，后来才发现在libexec下而不是在bin下面，多用用工具搜索搜索就出来了

另外也可以参见Ubuntu二进制安装MySQL 5.5
地址http://lucane.iteye.com/blog/866355

MySQL存储过程备忘

突然要弄MySQL存储过程
好久没有怎么接触过数据库存储过程了，都忘了，记在这里

create table email (id int auto_increment primary key, name text, content text, int size);
create table email_trash (id int auto_increment primary key, name text, content text, int size);

create table attachment (id int auto_increment primary key, email_id int, url text);
create table attachment_trash (id int auto_increment primary key, email_id int, url text);
delimiter //
drop procedure if exists fix_data_trash_magic //
CREATE PROCEDURE `fix_data_trash_magic`()
BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE _amount INT DEFAULT 0;
    DECLARE _size INT;
    DECLARE _id INT;
    DECLARE _newid LONG;

    DECLARE email_loop CURSOR FOR select id, size from email_trash where name = 'jemmy';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN email_loop;
        REPEAT
        FETCH email_loop INTO _id, _size;
            IF NOT done THEN
                insert into email(name, content, size) select name,content,size from email_trash where id = _id;

                select last_insert_id() into _newid;

                insert into attachment(email_id, url) select _newid,url from attachment_trash where email_id = _id;
                
                SET _amount = _size + _amount;
            END IF;
        UNTIL done END REPEAT;
    CLOSE email_loop;
END
call fix_data_trash_magic()//

另外还有常用的几个命令

mysql> show procedure status;
mysql> show create procedure procedure_name; // SHOW THE CREATING SQL OF THE SP

包含异常处理，保存点的存储过程

DELIMITER ;;
CREATE PROCEDURE ps_with_exception_etc(in_domain varchar(50), in_account varchar(32), in_passwd varchar(32), OUT out_no int, OUT out_msg varchar(50))
BEGIN
    DECLARE s_fullid varchar(100);

    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Repeated key';
        ROLLBACK TO sp_01;
    END;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Unknown error';
        ROLLBACK TO sp_01;
    END;

    -- 声明之类的都应该放在前面

    SET AUTOCOMMIT = 0;
        SELECT concat(in_account, '@', in_domain) INTO s_fullid;
        START TRANSACTION;
        SAVEPOINT sp_01;
        -- DO OO HERE
        -- DO XX HERE
        COMMIT;
    SET out_no = 1;
END
;;
DELIMITER ;

存储过程中的动态SQL
这里只是演示动态SQL的写法，要是真这么写太浪费了，直接可以写出来的SQL就不需要动态SQL

DELIMITER ;;
CREATE PROCEDURE ps_with_dynamic_sql(in_domain varchar(50), in_account varchar(32), OUT out_no int, OUT out_msg varchar(50))
BEGIN
    DECLARE fullid varchar(100);

    DECLARE DROP_USER_TABLEK varchar(500);
    DECLARE DROP_USER_MAILS varchar(500);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Unknown error';
        ROLLBACK TO sp_01;
    END;

    SET AUTOCOMMIT = 0;
    START TRANSACTION;
    SAVEPOINT sp_01;

        SET @MyQuery = concat('DELETE FROM tablek WHERE uuid = ', in_account);
        PREPARE DROP_USER_TABLEK from @MyQuery;

        SET @MyQuery = concat('DELETE FROM mails WHERE uuid = ', in_account);
        PREPARE DROP_USER_MAILS from @MyQuery;

        EXECUTE DROP_USER_TABLEK;
        EXECUTE DROP_USER_MAILS;
    COMMIT;
    SET out_no = 1;
END
;;
DELIMITER ;

还有个简单的

CREATE PROCEDURE `insert_data`(in item integer)
BEGIN
    DECLARE counter INT;
    SET counter = item;
    WHILE counter >= 1 DO
        // DO YOUR BIZ HERE
        SET counter = counter - 1;
    END WHILE;
END

另外在存储过程中声明变量，游标，处理器是有一定的顺序的，否则会报语法错误

触发器

create trigger insert_after_addr
after insert on addr
for each row
begin
set @aid=new.id;
insert into data(addr_id, type, value) values (@aid, 5, 'hello trigger');
end
show triggers//
drop trigger insert_after_addr//
alter table data modify id int(22) not null auto_increment
CREATE trigger insert_before_tablek
    before
        insert on tablek
    for each row
        begin
            set new.status = 25;
        end