Blog

mysql绑定多个ip地址
my.cnf中有选项bind-address=127.0.0.1，是说mysql server监听的是本地发来的请求，如果是任意主机都可以请求，则写为0.0.0.0，但是这样又不太安全。监听某ip，指定此ip地址即可，但是要保证mysql的user中有允许此ip访问，否则不能对数据库操作。那么是否可以在配置里只规定几个ip呢？

简单直接回答：不可能

请参考：http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_bind-address

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. The default address is 0.0.0.0. To specify an address explicitly, use the –bind-address=addr option at server startup, where addr is an IPv4 address or a host name. If addr is a host name, the server resolves the name to an IPv4 address and binds to that address. The server treats different types of addresses as follows:

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
If the address is a “regular” IPv4 address (such as 127.0.0.1), the server accepts TCP/IP connections only for that particular IPv4 address.

但是有此需求，就会到访问控制，那么使用防火墙iptables可实现此效果

mysql-server为192.168.1.3，只允许192.168.1.4, 192.168.1.5, 192.168.1.6来访问3306端口

在my.cnf中
```
bind-address = 0.0.0.0
```
在访问3306端口的主机中，只允许192.168.1.4-6，其他ip一律DROP掉
```
/sbin/iptables -A INPUT -p tcp -s 192.168.1.4 --dport 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp -s 192.168.1.5 --dport 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp -s 192.168.1.6 --dport 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP
```
或
```
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 192.168.1.4 -j DROP
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 192.168.1.5 -j DROP
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 192.168.1.6 -j DROP
```
保存防火墙规则
```
service iptables save
```
查看INPUT链包含3306的规则
```
echo -e "target prot opt source destination\n$(iptables -L INPUT -n | grep 3306)"
```
这样就实现了mysql只允许指定ip访问。

参考：

http://www.cyberciti.biz/faq/unix-linux-mysqld-server-bind-to-more-than-one-ip-address/
2013 年 7 月 1 日
mysql主从复制
大致思路：设置master端，用于复制的账户，锁表导入数据库，在slave中导入数据库，设置slave端

mysql主从复制是一种比较靠谱的备份方式，这里用最简单的，一个master，一个slave

12.34.56.789- Master Database

12.23.34.456- Slave Database

前提条件：

主从数据库大版本要一样，可以用mysql -V查看，我这里用的是mysql Ver 14.14 Distrib 5.5.31

设置master端

配置文件vim /etc/mysql/my.cnf，mysql的bind-address只能绑一个特定的ip或0.0.0.0，我现在用的是0.0.0.0，即
```
bind-address            = 0.0.0.0
```
在 [mysqld]段下为server-id设置一个独立的编号
```
server-id               = 1
```
主从复制需要binary的log日志，将log_bin这一行取消注释
```
log_bin                 = /var/log/mysql/mysql-bin.log
```
设置要复制的数据库jpuyydb
```
binlog_do_db            = jpuyydb
```
master的配置文件设置完了，重启mysql
```
/etc/init.d/mysql restart
```
接下来在mysql中操作，mysql -u root -p，添加一个复制用的账户slave_user密码是password，这里的权限是可以复制所有数据库
```
GRANT select, replication client, replication slave *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
```
刷新权限
```
FLUSH PRIVILEGES;
```
接下来的操作需要按步骤操作，将jpuyydb锁表，防止有其他操作影响当前状态
```
use jpuyydb;
flush tables with read lock;
```
接下来查看master状态，并将下面的值file和position记录下来
```
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | jpuyydb | test |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
```
如果在当前的界面有一些操作的话，数据库的表会自动解锁，所以新开一个窗口，将数据库备份下来，
```
mysqldump -u root -p --opt jpuyydb > jpuyydb.sql
```
然后在原窗口解锁，退出，master端就设置好了
```
UNLOCK TABLES;
QUIT;
```
在slave中创建并导入数据库，

在mysql中创建数据库
```
create database if not exists jpuyydb default charset utf8 collate utf8_general_ci;
```
导入数据库
```
mysql -u root -p jpuyydb < jpuyydb.sql
```
设置slave端，vim /etc/mysql/my.cnf，需要修改的如下
```
server-id               = 100
```
添加一条relay-log
```
relay-log               = /var/log/mysql/mysql-relay-bin.log
```
```
log_bin                 = /var/log/mysql/mysql-bin.log
```
```
binlog_do_db            = jpuyydb
```
修改好之后退出，重启mysql，之后进入mysql控制界面，运行
```
CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;
```
现在slave设置好了，启动slave
```
START SLAVE;
```
查看slave的状态，\G将输出以适合阅读的方式显示出来
```
SHOW SLAVE STATUS\G
```
如果下面两条都是Yes表示成功
```
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
```
Trouble Shooting:

如果一直是连接状态，需要做如下排查，

在slave端尝试连接master
```
mysql -u slave_user -p -P 3306 -h 12.34.56.789
```
在master端查看3306的连接情况，应该有一条类似于下面的ESTABLISHED的记录
```
lsof -i tcp:3306
```
```
mysqld  10310 mysql   15u  IPv4 7636301      0t0  TCP ip-10-128-.internal:mysql->112.65.13.1s:55781 (ESTABLISHED)
```
在slave端如果一直是connecting状态，则尝试如下操作
```
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
```
在slave端出现如下错误时

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

解决方法一：

进入到/var/lib/mysql/删掉

master.info
relay-log.info

解决方法二：
```
mysql> flush slave;

Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
```
参考：

https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-in-mysql
2013 年 7 月 1 日

rsync使用方法

服务器1：192.168.1.1

服务器2：192.168.1.2

要求：

将192.168.1.1的abc目录同步到192.168.1.2上的/tmp/temp下

rsync -a [email protected]:/home/bak/abc /tmp/temp/

如果端口为非22，则加上 -e 'ssh -p 537', –progress显示进度

rsync -a --progress -e 'ssh -p 537' [email protected]:/data/jpuyy.com /home/bak/

rsync增量备份

增量硬链备份，对比bk1，如果有增量则备份到bk2中

rsync -au myadmin bk1
rsync -au --link-dest=/root/bk1 myadmin bk2

实际增量备份脚本

#!/bin/bash

cur_date=`date +%F`
rsync="/usr/bin/rsync"
args="-au --bwlimit=30000 --copy-links"
user='root'
local_dir='/data/all_web_backup/abc.com/'

IFS=' '
while read project ip path
do
# rsync loop , project one by one
mkdir -p $local_dir/$project
last_rsync=`ls $local_dir/$project | grep -P "\d{4}-\d{2}-\d{2}" | sort | tail -1`
if [ -d "$local_dir/$project/$last_rsync" ]; then
$rsync $args -e 'ssh -p 22' --link-dest=$local_dir/$project/$last_rsync $user@$ip:$path $local_dir/$project/$cur_date
else
$rsync $args -e 'ssh -p 22' $user@$ip:$path $local_dir/$project/$cur_date
fi
done < backup_list

backup_list的内容需要如下格式

 bbs 192.168.1.5 /home/www/abc/bbs

rsync的时候同步到远端并exclude .git目录

rsync -r --exclude=.git . /var/www/html/jpuyy.com

控制传输带宽

rsync --bwlimit=kb/second source dest

2013 年 6 月 28 日

使用iperf测试网络的性能

准备工作：

安装epel源

rpm -ivh http://mirrors.sohu.com/fedora-epel/6/x86_64/epel-release-6-8.noarch.rpm

更新本地cache安装iperf

yum makecache -y
yum install iperf -y

测试工作：

192.168.0.244为server端，192.168.0.236为client端

在server端和client端可以查看到传输的情况，还可以通过ifstat, iptraf查看网卡的流量

TCP测试

server(0.244)

iperf -s -i 1

-s 服务器模式

-i 报告显示间隔秒数

client(0.236)

iperf -t 20 -i 1 -c 192.168.0.244

-t 测试用时的秒数

-c 客户端模式，后面接要连接的服务器

服务端显示：

------------------------------------------------------------
Server listening on TCP port 5001
TCP window size: 32.0 KByte (default)
------------------------------------------------------------
[ 4] local 192.168.0.244 port 5001 connected with 192.168.0.236 port 54921
[ ID] Interval Transfer Bandwidth
[ 4] 0.0- 1.0 sec 112 MBytes 938 Mbits/sec
[ 4] 1.0- 2.0 sec 112 MBytes 942 Mbits/sec
[ 4] 2.0- 3.0 sec 112 MBytes 942 Mbits/sec
[ 4] 3.0- 4.0 sec 112 MBytes 941 Mbits/sec
[ 4] 4.0- 5.0 sec 112 MBytes 942 Mbits/sec
[ 4] 5.0- 6.0 sec 112 MBytes 942 Mbits/sec
[ 4] 6.0- 7.0 sec 112 MBytes 942 Mbits/sec
[ 4] 7.0- 8.0 sec 112 MBytes 941 Mbits/sec
[ 4] 8.0- 9.0 sec 112 MBytes 942 Mbits/sec
[ 4] 9.0-10.0 sec 112 MBytes 942 Mbits/sec
[ 4] 10.0-11.0 sec 112 MBytes 942 Mbits/sec
[ 4] 11.0-12.0 sec 112 MBytes 941 Mbits/sec
[ 4] 12.0-13.0 sec 112 MBytes 942 Mbits/sec
[ 4] 13.0-14.0 sec 112 MBytes 942 Mbits/sec
[ 4] 14.0-15.0 sec 112 MBytes 942 Mbits/sec
[ 4] 15.0-16.0 sec 112 MBytes 941 Mbits/sec
[ 4] 16.0-17.0 sec 112 MBytes 941 Mbits/sec
[ 4] 17.0-18.0 sec 112 MBytes 941 Mbits/sec
[ 4] 18.0-19.0 sec 112 MBytes 942 Mbits/sec
[ 4] 19.0-20.0 sec 112 MBytes 942 Mbits/sec
[ 4] 0.0-20.0 sec 2.20 GBytes 941 Mbits/sec

客户端显示：

------------------------------------------------------------
Client connecting to 192.168.0.244, TCP port 5001
TCP window size: 23.2 KByte (default)
------------------------------------------------------------
[ 3] local 192.168.0.236 port 54921 connected with 192.168.0.244 port 5001
[ ID] Interval Transfer Bandwidth
[ 3] 0.0- 1.0 sec 115 MBytes 965 Mbits/sec
[ 3] 1.0- 2.0 sec 112 MBytes 937 Mbits/sec
[ 3] 2.0- 3.0 sec 113 MBytes 946 Mbits/sec
[ 3] 3.0- 4.0 sec 113 MBytes 946 Mbits/sec
[ 3] 4.0- 5.0 sec 112 MBytes 935 Mbits/sec
[ 3] 5.0- 6.0 sec 113 MBytes 946 Mbits/sec
[ 3] 6.0- 7.0 sec 112 MBytes 935 Mbits/sec
[ 3] 7.0- 8.0 sec 113 MBytes 946 Mbits/sec
[ 3] 8.0- 9.0 sec 113 MBytes 946 Mbits/sec
[ 3] 9.0-10.0 sec 111 MBytes 934 Mbits/sec
[ 3] 10.0-11.0 sec 113 MBytes 946 Mbits/sec
[ 3] 11.0-12.0 sec 111 MBytes 934 Mbits/sec
[ 3] 12.0-13.0 sec 113 MBytes 945 Mbits/sec
[ 3] 13.0-14.0 sec 113 MBytes 945 Mbits/sec
[ 3] 14.0-15.0 sec 113 MBytes 946 Mbits/sec
[ 3] 15.0-16.0 sec 111 MBytes 931 Mbits/sec
[ 3] 16.0-17.0 sec 113 MBytes 948 Mbits/sec
[ 3] 17.0-18.0 sec 111 MBytes 934 Mbits/sec
[ 3] 18.0-19.0 sec 113 MBytes 945 Mbits/sec
[ 3] 19.0-20.0 sec 113 MBytes 948 Mbits/sec
[ 3] 0.0-20.0 sec 2.20 GBytes 942 Mbits/sec

UDP 测试

server(0.244): iperf -u -s -i 1

client(0.236): iperf -t 20 -i 1 -u -b 1000M -c 192.168.0.244

-u 使用udp协议

-b 后面接每秒带宽发送量(udp下适用)

服务器显示

------------------------------------------------------------
Server listening on UDP port 5001
Receiving 1470 byte datagrams
UDP buffer size: 224 KByte (default)
------------------------------------------------------------
[ 3] local 192.168.0.244 port 5001 connected with 192.168.0.236 port 34489
[ ID] Interval Transfer Bandwidth Jitter Lost/Total Datagrams
[ 3] 0.0- 1.0 sec 128 KBytes 1.05 Mbits/sec 0.013 ms 0/ 89 (0%)
[ 3] 1.0- 2.0 sec 128 KBytes 1.05 Mbits/sec 0.010 ms 0/ 89 (0%)
[ 3] 2.0- 3.0 sec 128 KBytes 1.05 Mbits/sec 0.015 ms 0/ 89 (0%)
[ 3] 3.0- 4.0 sec 128 KBytes 1.05 Mbits/sec 0.012 ms 0/ 89 (0%)
[ 3] 4.0- 5.0 sec 128 KBytes 1.05 Mbits/sec 0.008 ms 0/ 89 (0%)
[ 3] 5.0- 6.0 sec 129 KBytes 1.06 Mbits/sec 0.008 ms 0/ 90 (0%)
[ 3] 6.0- 7.0 sec 128 KBytes 1.05 Mbits/sec 0.009 ms 0/ 89 (0%)
[ 3] 7.0- 8.0 sec 128 KBytes 1.05 Mbits/sec 0.019 ms 0/ 89 (0%)
[ 3] 8.0- 9.0 sec 128 KBytes 1.05 Mbits/sec 0.011 ms 0/ 89 (0%)
[ 3] 9.0-10.0 sec 128 KBytes 1.05 Mbits/sec 0.009 ms 0/ 89 (0%)
[ 3] 10.0-11.0 sec 128 KBytes 1.05 Mbits/sec 0.014 ms 0/ 89 (0%)
[ 3] 11.0-12.0 sec 129 KBytes 1.06 Mbits/sec 0.014 ms 0/ 90 (0%)
[ 3] 12.0-13.0 sec 128 KBytes 1.05 Mbits/sec 0.009 ms 0/ 89 (0%)
[ 3] 13.0-14.0 sec 128 KBytes 1.05 Mbits/sec 0.017 ms 0/ 89 (0%)
[ 3] 14.0-15.0 sec 128 KBytes 1.05 Mbits/sec 0.016 ms 0/ 89 (0%)
[ 3] 15.0-16.0 sec 128 KBytes 1.05 Mbits/sec 0.011 ms 0/ 89 (0%)
[ 3] 16.0-17.0 sec 128 KBytes 1.05 Mbits/sec 0.012 ms 0/ 89 (0%)
[ 3] 17.0-18.0 sec 129 KBytes 1.06 Mbits/sec 0.017 ms 0/ 90 (0%)
[ 3] 18.0-19.0 sec 128 KBytes 1.05 Mbits/sec 0.018 ms 0/ 89 (0%)
[ 3] 19.0-20.0 sec 128 KBytes 1.05 Mbits/sec 0.012 ms 0/ 89 (0%)
[ 3] 0.0-20.0 sec 2.50 MBytes 1.05 Mbits/sec 0.013 ms 0/ 1785 (0%)

客户端显示

------------------------------------------------------------
Client connecting to 192.168.0.244, UDP port 5001
Sending 1470 byte datagrams
UDP buffer size: 224 KByte (default)
------------------------------------------------------------
[ 3] local 192.168.0.236 port 34489 connected with 192.168.0.244 port 5001
[ ID] Interval Transfer Bandwidth
[ 3] 0.0- 1.0 sec 129 KBytes 1.06 Mbits/sec
[ 3] 1.0- 2.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 2.0- 3.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 3.0- 4.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 4.0- 5.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 5.0- 6.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 6.0- 7.0 sec 129 KBytes 1.06 Mbits/sec
[ 3] 7.0- 8.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 8.0- 9.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 9.0-10.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 10.0-11.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 11.0-12.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 12.0-13.0 sec 129 KBytes 1.06 Mbits/sec
[ 3] 13.0-14.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 14.0-15.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 15.0-16.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 16.0-17.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 17.0-18.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 18.0-19.0 sec 129 KBytes 1.06 Mbits/sec
[ 3] 19.0-20.0 sec 128 KBytes 1.05 Mbits/sec
[ 3] 0.0-20.0 sec 2.50 MBytes 1.05 Mbits/sec
[ 3] Sent 1785 datagrams
[ 3] Server Report:
[ 3] 0.0-20.0 sec 2.50 MBytes 1.05 Mbits/sec 0.013 ms 0/ 1785 (0%)

2013 年 6 月 28 日

sublime的使用
ubuntu下的安装方法：
```
add-apt-repository ppa:webupd8team/sublime-text-2
apt-get update
apt-get install sublime-text
```
ctrl+tab可以快速切换标签

ctrl+w关闭当前的标签

ctrl+r快速跳转到文档中相关函数

ctrl+p加#可以在当前文件中进行搜索

ctrl+g加上数字跳到对应的行

ctrl+`调出console

使用vim模式：
按下 Shift + Command + P 调出命令面板，输入 settings user 调出 Preferences：Settings – User，这时添加：
```
{
 "ignored_packages": []
}
```
2013 年 6 月 28 日
proxmox虚拟化环境-pve
proxmox VE 是一个非常棒的同时支持openvz和kvm虚拟化环境平台，简称PVE，基于debian制作。

下载好镜像之后，可以用u盘安装，http://pve.proxmox.com/wiki/Install_from_USB_Stick

在linux下使用dd命令制作一个可以安装用的u盘：
dd if=pve-cd.iso of=/dev/XYZ bs=1M
安装过程，略

打开https://ip:8006，使用安装时设置的用户名和密码登陆。

可以创建的有VM和CT，VM即kvm虚拟机，kvm可以装windows,linux；CT即ConTainer(容器)，也就是openvz虚拟机，不能装windows。

制作CentOS的模板

http://wiki.openvz.org/Creating_a_CentOS_5.0_Template

http://pve.proxmox.com/wiki/Main_Page

对于 kvm 虚拟化的资源 210 是里面的 id ，复制一台新机器 211
```
qm clone 210 211 -full 1 -format qcow2 -name ready-to-use.jpuyy.com -storage data
```
kvm 从宿主机切换虚拟机使用 terminal

在宿主机配置文件中编辑，kvm 虚拟机为 2549

/etc/pve/qemu-server/2549.conf

添加一行
```
serial0: socket
```
虚拟机为 centos6
```
 vim /boot/grub/grub.conf
```
在 kernel 一行中追加
```
console=tty0 console=ttyS0
```
示例
```
kernel /vmlinuz-2.6.32-504.el6.x86_64 ro root=UUID=dbc46481-4b4c-420f-9b32-8e8ac82a4979 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=auto  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM rhgb quiet console=tty0 console=ttyS0
```
这些做完，
```
qm stop 2549
qm start 2549
```
重启机器后
```
qm terminal 2549
```
即可从宿主机进入虚拟机 terminal.

参考：

https://pve.proxmox.com/wiki/Serial_Terminal

https://pve.proxmox.com/wiki/Qm_manual
2013 年 6 月 27 日

Blog

mysql绑定多个ip地址

mysql主从复制

rsync使用方法

使用iperf测试网络的性能

sublime的使用

proxmox虚拟化环境-pve