[toc]

官方网站

https://launchpad.net/mysql-proxy

下载解压

[shell]wget http://launchpad.net/mysql-proxy/0.8/0.8.0/+download/mysql-proxy-0.8.0.tar.gz
tar xvf mysql-proxy-0.8.0.tar.gz[/shell]

安装编译工具

[shell]apt-get install gcc pkg-config[/shell]
gcc:The GNU C compiler c语言编译器
pkg-config:manage compile and link flags for libraries http://blog.csdn.net/yuntongsf/archive/2009/04/21/4097393.aspx

安装支持库

[shell]apt-get install libevent-dev libglib2.0-dev liblua5.1-0-dev libmysqlclient-dev[/shell]

编译安装

[shell]cd mysql-proxy-0.8.0
./configure
make
make install[/shell]

刷新缓存库路径

[shell]ldconfig -v[/shell]

ldconfig命令的用途,主要是在默认搜寻目录(/lib 和/usr/lib)以及动态库配置文件/etc/ld.so.conf内所列的目录下,搜索出可共享的动态链接库(格式如前介绍,lib*.so*), 进而创建出动态装入程序(ld.so)所需的连接和缓存文件.缓存文件默认为/etc/ld.so.cache,此文件保存已排好序的动态链接库名字列表.
ldconfig通常在系统启动时运行,而当用户安装了一个新的动态链接库时,就需要手工运行这个命令.

配置脚本

启动脚本

/etc/init.d/mysql-proxy
[shell]
#! /bin/sh

### BEGIN INIT INFO
# Provides: mysql-proxy
# Required-Start: $syslog
# Required-Stop: $syslog
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: MySQL Proxy
### END INIT INFO

PATH=/bin:/usr/bin:/sbin:/usr/sbin
LUA_PATH="/usr/share/mysql-proxy/?.lua"

DAEMON="/usr/sbin/mysql-proxy"
PIDFILE="/var/run/mysql-proxy.pid"

test -f $DAEMON || exit 0

test ! -r /etc/default/mysql-proxy || . /etc/default/mysql-proxy

if ! $ENABLED ; then exit 0 ; fi

. /lib/lsb/init-functions

case "$1" in
start)
log_begin_msg "Starting MySQL Proxy daemon…"
start-stop-daemon –start –quiet –pidfile $PIDFILE –make-pidfile –name mysql-proxy –startas $DAEMON -b — $OPTIONS
log_end_msg $?
;;
stop)
log_begin_msg "Stopping MySQL Proxy daemon…"
start-stop-daemon –stop –quiet –retry 3 –exec $DAEMON –pidfile $PIDFILE
log_end_msg $?
;;
status)
log_begin_msg "Checking MySQL Proxy daemon… "
status_of_proc -p $PIDFILE /usr/sbin/mysql-proxy mysql-proxy && exit 0 || exit $?
;;
restart|force-reload)
log_begin_msg "Reloading MySQL Proxy daemon…"
start-stop-daemon –stop –quiet –retry 3 –exec $DAEMON –pidfile $PIDFILE
start-stop-daemon –start –quiet –pidfile $PIDFILE –make-pidfile –name mysql-proxy –startas $DAEMON -b — $OPTIONS
log_end_msg $?
;;
*)
log_success_msg "Usage: $0 {start|stop|status|restart|force-reload}"
exit 1
esac

exit 0[/shell]

启动设置

/etc/default/mysql-proxy

[shell]ENABLED="true"

OPTIONS="

–proxy-backend-addresses=127.0.0.1:3306 \

–proxy-read-only-backend-addresses=61.164.186.241:3306 \

–proxy-read-only-backend-addresses=61.164.186.246:3306 \

–proxy-lua-script=/usr/lib/mysql-proxy/lua/rw-splitting.lua \

–log-level=info \

–log-file=/var/log/mysql/mysql-proxy.log

–keepalive

"[/shell]

执行权限

[shell]chmod a+x /etc/init.d/mysql-proxy[/shell]

开机自启动

[shell]apt-get install rcconf
rcconf[/shell]

运行rcconf会列出所有/etc/init.d/目录下的启动脚本:

  1. 使用[空格]键选择启动项,变成[*]
  2. 按[TAB]键,选中[OK]

测试连接

[shell]mysql -uroot -poP05_1ep -hlocalhost –port=4040[/shell]

很多同学在做分页的时候可能会使用select count(*) as total from ….,然后再limit 。。。这种的方式来做,

其实这样就会造成了两次查询,事实上一条sql语句就可以了,使用SQL_CALC_FOUND_ROWS();
如代码:
[php]
mysql_connect(‘192.168.1.81′,’boss’,’55_1NK6TR’);

mysql_select_db(‘web_td’);

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM user LIMIT 0, 10";
$rs = mysql_query($sql);

$total = mysql_query(‘SELECT FOUND_ROWS()’);
var_dump(mysql_result($total, 0));

$total = mysql_query(‘SELECT COUNT(*) FROM user’);
var_dump(mysql_result($total, 0));
[/php]
这样一次查询就能得到分页所要的总数,自然简单效率又更高了。

[shell]mysql_convert_table_format –help

Conversion of a MySQL tables to other storage engines

Usage: /usr/bin/mysql_convert_table_format database [table[ table …]]
If no tables has been specifed, all tables in the database will be converted.
You can also use wildcards, ie "my%"

The following options are available:

-f, –force
Continue even if there is some error.

-?, –help
Shows this help

-e, –engine=ENGINE
Converts tables to the given storage engine (Default: INNODB)

-h, –host=HOST
Host name where the database server is located. (Default: localhost)

-p, –password=PASSWORD
Password for the current user.

-P, –port=PORT
TCP/IP port to connect to if host is not "localhost".

-S, –socket=SOCKET
Socket to connect with.

-u, –user=USER
User name to log into the SQL server.

-v, –verbose
This is a test specific option that is only used when debugging a test.
Print more information about what is going on.

-V, –version
Shows the version of this program.[/shell]

例如,把test数据库里面所有表都转换为INNODB引擎:

[shell]

sudo vim /usr/bin/mysql_convert_table_format

GetOptions(
– "e|engine|type=s" => \$opt_type,
+ "e|engine|type=s" => \$opt_engine, // 变量名错误,改$opt_type->$opt_engine

[/shell]

[shell]
mysql_convert_table_format –host=localhost –user=root –password=oP05_1ep –engine=innodb test
[/shell]

[toc]

服务配置

/etc/mysql/my.cnf

主服务器Master

IP:61.164.186.242

[shell]
#[必须]启用二进制日志
log_bin = mysql-bin
#[必须]服务器唯一id,一般取ip最后两位
server_id = 242
#[可选]每次提交事务时,同步二进制日志内容到磁盘上,即使服务器崩溃也会把事件写入日志
sync_binlog = 1
[/shell]

重启生效

[shell]/etc/init.d/mysql restart[/shell]

从服务器Slave

IP:61.164.186.241/246

[shell]
#[必须]服务器唯一id,一般取ip最后两位
server_id = 246
#[可选]启用二进制日志
log_bin = mysql-bin
#[可选]定义中继日志的位置和文件名
relay_log = mysql-relay-bin
#[可选]使从服务器把复制的事件记录到自己的二进制日志中
log_slave_updates = 1
#[可选]只读
read_only = 1
#[可选]跳过1062主键冲突错误 <span>Duplicate entry ‘1234’ for key ‘PRIMARY'</span>
slave-skip-errors = 1062
[/shell]

同步数据

将master数据库导出为master.sql

[shell]mysqldump –single-transaction –all-databases –master-data=1 -hlocalhost -uroot -poP05_1ep > master.sql[/shell]

复制master.sql到slave服务器

[shell]gzip -c master.sql | ssh root@61.164.186.241 "gunzip -c – >master.sql"[/shell]

分别到slave服务器上执行导入

[shell]mysql -uroot -poP05_1ep < master.sql[/shell]

由于备份导入的是整个数据库,所以主从数据库的数据完全一致,包括mysql用户名密码都是一致的。

同步/etc/mysql/debian.cnf 中的管理密码;否则会导致/etc/init.d/mysql  restart失败!系统找不到启动后的mysql进程。

[shell]scp /etc/mysql/debian.cnf root@61.164.186.241:/etc/mysql/debian.cnf[/shell]

重启生效

[shell]/etc/init.d/mysql restart[/shell]

把上面的所有命令用管道一次执行,哈哈^_^,直接将本服务器数据同步到另外一台服务器!!!

[shell]mysqldump –single-transaction –all-databases –master-data=1 -hlocalhost -uroot -poP05_1ep | gzip -c – | ssh root@61.164.186.241 "gunzip -c – | mysql -uroot -poP05_1ep"[/shell]

启动复制

登录slave

[shell]mysql -uroot -poP05_1ep[/shell]

设置master

[shell]change master to master_host=’61.164.186.242′,master_user=’root’,master_password=’oP05_1ep’; [/shell]

开始复制

[shell]start slave;[/shell]

查看状态

[shell]show slave status\G[/shell]

[text]
Slave_IO_State: Waiting for master to send event
Master_Host: 61.164.186.242 #设置的master服务器
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 100764106 #同步读取二进制日志的位置,大于等于>=Exec_Master_Log_Pos
Relay_Log_File: mysqld-relay-bin.000010
Relay_Log_Pos: 100764251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 100764106 #同步执行二进制日志的位置,小于等于<=Read_Master_Log_Pos
Relay_Log_Space: 100764450
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #从服务器数据比主服务器延迟多少秒?一般是0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:[/text]

问题

  1. 修改mysql配置,导致mysql无法启动?
    答:请启用mysql错误日志,log_error  = “/var/log/mysql/mysql.err”,检查错误日志内容。
  2. [ERROR] /usr/sbin/mysqld: Table ‘./web_td/trick_rates’ is marked as crashed and should be repaired?
    答:编辑配置,在[mysqld]段内加入,myisam-recover = BACKUP,FORCE,强制修复表
  3. start slave失败,提示relay_log错误?
    答:直接注释掉:relay_log = mysql-relay-bin
  4. gzip -c -,gunzip -c -是什么意思?
    答:一般来说命令是 gzip -c /file, – 是标准输入stdin;“gzip -c -”的意思是:读取标准输入(-),gzip后,输出到标准输出(-c)