有时候我们从其他数据库部署数据到一个新的机器上面的时候,通过mysql的source 命令导入 mysqldump 导出的数据,数据量很大的时候,速度会很慢。
可以执行以下命令来提高速度

mysql> set autocommit=0; set unique_checks=0; set foreign_key_checks=0;
mysql> source /path/to/dump.sql
mysql> commit; set unique_checks=1; set foreign_key_checks=1;

charCodeAt by Lua

@(Lua JavaScript charCodeAt)

I wanted to have a function charCodeAt in Lua ,and it should works exactly like javascript
but with Lua5.1 ,UTF8 and Unicode are not supported,

1: how charCodeAt works in javascript

to show Console press F12 in Chrome( MAC:CMD+alt+J)

1
2
3
4
5
[
'你'.charCodeAt(0),
'ñ'.charCodeAt(0),
'n'.charCodeAt(0)
]

it will output [20320, 241, 110] ,it means the numeric value of Unicode , ‘你’=20320 , ‘ñ’=241, ‘n’=110.

The charCodeAt() method returns the numeric Unicode value of the character at the given index (except for unicode codepoints > 0x10000).

according to alexander-yakushev we can know how many bytes one UTF8 word takes using function utf8.charbytes
[https://github.com/alexander-yakushev/awesompd/blob/master/utf8.lua]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
function utf8.charbytes (s, i)
-- argument defaults
i = i or 1
local c = string.byte(s, i)
-- determine bytes needed for character, based on RFC 3629
if c > 0 and c <= 127 then
-- UTF8-1 byte
return 1
elseif c >= 194 and c <= 223 then
-- UTF8-2 byte
return 2
elseif c >= 224 and c <= 239 then
-- UTF8-3 byte
return 3
elseif c >= 240 and c <= 244 then
-- UTF8-4 byte
return 4
end
end

Unicode & UTF8 convert method

Unicode code range UTF-8 code example
hex code binary code char
0000 0000-0000 007F 0xxxxxxx n(alphabet)
0000 0000-0000 007F 110xxxxx 10xxxxxx ñ
0000 0080-0000 07FF 1110xxxx 10xxxxxx 10xxxxxx (most CJK)
0001 0000-0010 FFFF 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx other chars

but we should pay attention to 4 bytes UTF8[emoji], it works not that simple

special Method

javascript engine using UTF16,characters in Basic Multilingual Plane were the same with unicode, but if the characters were in Supplementary Plane it should use the formula below,usually we encounter Supplementary Plane emoji like 图片名称(4 byte UTF8 character)

1
2
3
-- formula 1
H = Math.floor((c-0x10000) / 0x400)+0xD800
L = (c - 0x10000) % 0x400 + 0xDC00

####

###code is here

###https://github.com/lilien1010/lua-bit

Feedback & Bug Report


Thank you for reading this , if you got any better idea, I’m glad to hear from you

repo

###https://github.com/lilien1010/lua-bit

some phper maybe find that the libraries in nginx-lua is not enough,
like aes mcrypt with ECB mode,
so I create this.

like encrypt with PHP:

1
mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $key,$text, MCRYPT_MODE_ECB );

encrypt with lua:

1
2
3
4
5
6
7
8
local data      =   'wait to be encrypted'
local key = '01234567890123456' --length is 16
local mc_ecb = require("resty.ecb_mcrypt")
local ecb = mc_ecb:new();
local enc_data = ecb:encrypt(key,data );
ngx.print(enc_data)
-- you must use 'ngx.print' rather then 'ngx.say'
-- while 'ngx.say' will append a '\n' at the end of string

mean while,you will need to install libmcrypt,
because the lua-aes will load the libmcrypt with FFI,
try to install libmcrypt

1
yum install libmcrypt libmcrypt-devel


Github





here is the source code project , still coding


MySql数据库高可用实用方案-MHA+KeepAlived

1:先来说一下为什么会出现这种方案,以及这个方案是怎么回事儿?

只要是个服务,就都有挂的可能,Mysql也不例外,为了让各位运维、DBA、和全栈工程师晚上能睡个安稳觉,有更多的时间和女朋友么么哒,我们希望数据库服务器在宕机的时候能自动恢复过来,最少的影响用户的体验,留下更多的奖金买杰士邦、冈本、杜蕾斯什么的。

这个时候我们的MHA(Mysql Highe Available)大显身手了,他的原理就一句话:在Mysql的主从架构下,当主库挂掉的时候,自动将备库升级为主库,同时为其他备库重新设置新的主库。

假设我们有服务器
A:192.168.1.60 (主库)
B:192.168.1.70(备库,备主[主库宕机的情况,提升B为主库])
C:192.168.1.80(备库2)
D: 192.168.1.88 (manager,MHA管理中心,安装MHA manager,在主库和备库上面都需要安装MHA node,manager管理node很好理解吧)

让B C 成为A的slave 机 就不在这里介绍了,很简单的配置

2:具体原理如下:
(1)从宕机崩溃的master保存二进制日志事件(binlog events);

(2)识别含有最新更新的slave;

(3)应用差异的中继日志(relay log)到其他的slave;

(4)应用从master保存的二进制日志事件(binlog events);

(5)提升一个slave为新的master;

(6)使其他的slave连接新的master进行复制;

3:MHA的安装,因为MHA的功能是通过perl脚本来实现的,所以我们要安装perl环境

A: 让A B C D四台机子互相无密码登陆

查看A B C D 主机 /root/.ssh/ 下面有没有 id_rsa.pub 文件

如果没有从新生成 ssh-keygen -t rsa -b 2048

然后通过scp命令拷贝到其他三台机子,如下

[root@192.168.1.88 ~]# scp id_rsa.pub root@192.168.1.60:/root/.ssh/ //复制到主机60

[root@192.168.1.60 ~]# cat id_rsa.pub >> authorized_keys

安装必要的perl库,

[root@192.168.1.88 ~]# yum install cpan perl

[root@192.168.1.88 ~]# yum -y install perl-MIME-Lite perl-Params-Validate perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

[root@192.168.1.88 ~]# wget ftp://ftp.muug.mb.ca/mirror/centos/6.5/os/x86_64/Packages/compat-db43-4.3.29-15.el6.x86_64.rpm
[root@192.168.1.88 ~]# wget http://downloads.naulinux.ru/pub/NauLinux/6x/i386/sites/School/RPMS/perl-Log-Dispatch-2.27-1.el6.noarch.rpm

[root@192.168.1.88 ~]# wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
[root@192.168.1.88 ~]# wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
[root@192.168.1.88 ~]# wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
[root@192.168.1.88 ~]# wget http://mirror.centos.org/centos/6/os/x86_64/Packages/perl-Time-HiRes-1.9721-136.el6.x86_64.rpm

[root@192.168.1.88 ~]# rpm -ivh perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm perl-Log-Dispatch-2.27-1.el6.noarch.rpm perl-Mail-Sender-0.8.16-3.el6.noarch.rpm perl-Mail-Sendmail-0.79-12.el6.noarch.rpm perl-Time-HiRes-1.9721-136.el6.x86_64.rpm

再安装MHA manager(在192.168.1.88上)

[root@192.168.1.88 ~]# wget https://downloads.mariadb.com/files/MHA/mha4mysql-manager-0.55-0.el6.noarch.rpm

[root@192.168.1.88 ~]# rpm -ivh mha4mysql-manager-0.55-0.el6.noarch.rpm

安装过程中会出现一些缺失的库,自己再去找对应的库就好了;

再在从库安装MHA node(在192.168.1.50、192.168.1.60、192.168.1.70,A、B、C上)

[root@A-B-C ~]#wget https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm

[root@A-B-C~]#rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm

(缺失perl库的话,按第一步来装好)

配置Manager

shell> mkdir -p /masterha/app1 //创建app目录,用来存放临时数据文件,
shell> mkdir /etc/masterha //创建目录,配置文件目录
shell> vi /etc/masterha/app1.cnf //创建配置文件
[server default]
user=root //linux用于管理mysql用戶名
password=123456 //linux用于管理mysql密码
manager_workdir=/masterha/app1
manager_log=/masterha/app1/manager.log
remote_workdir=/masterha/app1
ssh_user=root //ssh免密钥登录的帐号名
repl_user=slaver //mysql复制帐号,用来在主从机之间同步二进制日志等
repl_password=slaver //mysql密码
ping_interval=1 //ping间隔,用来检测master是否正常

port=3306
[server2]
hostname=192.168.1.70

[server3]
hostname=192.168.1.80

port=3306

设置relay log的清除方式(在每个slave节点上):

[root@192.168.0.80 ~]# mysql -e ‘set global relay_log_purge=0’
[root@192.168.0.70 ~]# mysql -e ‘set global relay_log_purge=0’

2、masterha_check_ssh工具验证ssh信任登录是否成功

[root@192.168.0.88 ~]# masterha_check_ssh –conf=/etc/masterha/app1.cnf

注意:用ssh-keygen实现4台主机之间相互免密钥登录决定这一步是否成功。

Wed Apr 23 22:10:01 2014 - [debug] ok.
Wed Apr 23 22:10:01 2014 - [info] All SSH connection tests passed successfully.

3、masterha_check_repl工具验证mysql复制是否成功

[root@192.168.0.88 ~]# masterha_check_repl –conf=/etc/masterha/app1.cnf

注意:上一篇文章中的master–slaver是否成功决定这一步是否成功。或是MHA文件配置的用户账号有关。

port=3306

candidate_master=1

master_binlog_dir=/data/mysql

[server1]
hostname=192.168.1.60

Wed Apr 23 22:10:56 2014 - [info] Checking replication health on 192.168.1.232..
Wed Apr 23 22:10:56 2014 - [info] ok.
Wed Apr 23 22:10:56 2014 - [warning] master_ip_failover_script is not defined.
Wed Apr 23 22:10:56 2014 - [warning] shutdown_script is not defined.
Wed Apr 23 22:10:56 2014 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK

在出库上面停止mysql,模拟宕机的情况

[root@192.168.0.60 ~]#service mysql stop

我们可以在manager 机器上面看到输出的日志

[root@192.168.0.88 ~]tail -f /masterha/app1/manager.log