针对不同的备份有不同的方式
txt
~~~~~~~~~~~~~~~~~~~~~
mysqlimport的速度
mysqlimport支持多线程::
mysqlimport –debug-info –use-threads=2 –local -uroot -pxxxx logs `find . -name “*.txt”`
单个sql
~~~~~~~~~~~~~~~~~~~~~~~
有一个开源项目：tbdba-restore-mysqldump.pl
地址：https://github.com/orczhou/dba-tool/blob/master/tbdba-restore-mysqldump.pl
客服反馈后台无法查询，原因大概知道，是因为MySQL的事务产生了死锁，以往都不知道是哪个事务锁住了，只能很粗暴地重启MySQL
最近查找到一个方法，不用重启MySQL，记录如下
登录到MySQL，来看下有哪些MySQL事务
可以看到一个时间持续了比较久的事务，现在时间是13点了，而这个事务的开始时间是2012-11-09 12:15:14，显然是不正常的，我们看这个事务对应的mysql的线程ID（trx_mysql_thread_id）是82230715，就是这个事务导致的
13:01:48pm> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
+———–+———–+———————+———————–+——————+————+———————+———–+———————+——————-+——————-+——————+———————–+—————–+——————-+————————-+———————+——————-+————————+—————————-+—————————+—————————+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+———–+———–+———————+———————–+——————+————+———————+———–+———————+——————-+——————-+——————+———————–+—————–+——————-+————————-+———————+——————-+————————+—————————-+—————————+—————————+
| 31868CED0 | RUNNING | 2012-11-09 12:15:14 | NULL | NULL | 2 | 82230715 | NULL | NULL | 0 | 0 | 2 | 376 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 |
+———–+———–+———————+———————–+——————+————+———————+———–+———————+——————-+——————-+——————+———————–+—————–+——————-+————————-+———————+——————-+————————+—————————-+—————————+—————————+
我们登录到mysql把它kill掉
13:01:55pm> kill 82230715;
这样，就可以查询了
其他
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
有时候nginx，apache，mysql，php编译完了想看看编译参数可以用以下方法
nginx编译参数：
#/usr/local/nginx/sbin/nginx -V
nginx version: nginx/0.6.32
built by gcc 4.1.2 20071124 (Red Hat 4.1.2-42)
configure arguments: –user=www –group=www –prefix=/usr/local/nginx/ –with-http_stub_status_module –with-openssl=/usr/local/openssl
apache编译参数：
# cat /usr/local/apache2/build/config.nice
#! /bin/sh
#
# Created by configure
“./configure” \
“–prefix=/usr/local/apache2” \
“–with-included-apr” \
“–enable-so” \
“–enable-deflate=shared” \
“–enable-expires=shared” \
“–enable-rewrite=shared” \
“–enable-static-support” \
“–disable-userdir” \
“$@”
php编译参数：
# /usr/local/php/bin/php -i |grep configure
Configure Command => ‘./configure’ ‘–prefix=/usr/local/php’ ‘–with-apxs2=/usr/local/apache2/bin/apxs’ ‘–with-config-file-path=/usr/local/php/etc’ ‘–with-mysql=/usr/local/mysql’ ‘–with-libxml-dir=/usr/local/libxml2/bin’ ‘–with-gd=/usr/local/gd2’ ‘–with-jpeg-dir’ ‘–with-png-dir’ ‘–with-bz2’ ‘–with-xmlrpc’ ‘–with-freetype-dir’ ‘–with-zlib-dir’
mysql编译参数：
# cat “/usr/local/mysql/bin/mysqlbug”|grep configure
# This is set by configure
CONFIGURE_LINE=”./configure ‘–prefix=/usr/local/mysql’ ‘–localstatedir=/var/lib/mysql’ ‘–with-comment=Source’ ‘–with-server-suffix=-H863’ ‘–with-mysqld-user=mysql’ ‘–without-debug’ ‘–with-big-tables’ ‘–with-charset=gbk’ ‘–with-collation=gbk_chinese_ci’ ‘–with-extra-charsets=all’ ‘–with-pthread’ ‘–enable-static’ ‘–enable-thread-safe-client’ ‘–with-client-ldflags=-all-static’ ‘–with-mysqld-ldflags=-all-static’ ‘–enable-assembler’ ‘–without-isam’ ‘–without-innodb’ ‘–without-ndb-debug'”
转载自：http://hi.baidu.com/xi4oyu/blog/item/8a0e1ed020e81adb562c8452.html
1、配置环境
yum -y install rpm-build
2、下载源代码rpm包
mkdir /root/download
cd /root/download
wget http://www.percona.com/redir/downloads/Percona-Server-5.1/Percona-Server-5.1.58-12.9/source/Percona-Server-51-5.1.58-rel12.9.271.rhel6.src.rpm
3、安装打包MySQL时依赖的包
yum -y install perl readline-devel gcc-c++ ncurses-devel zlib-devel libtool automake autoconf time ccache bison
4、安装percona的源代码
rpm -ivh Percona-Server-51-5.1.58-rel12.9.271.rhel6.src.rpm
安装了2个文件，它们的位置是
/root/rpmbuild/SOURCES/Percona-Server-5.1.58.tar.gz
/root/rpmbuild/SPECS/percona-server.spec
5、修改percona-server.spec
加入我们自己的编译参数
vi percona-server.spec
将
# The –enable-assembler simply does nothing on systems that does not
# support assembler speedups.
和
%if %{YASSL_BUILD}
之间的一段修改成这样
# The –enable-assembler simply does nothing on systems that does not
# support assembler speedups.
sh -c “CFLAGS=\”$CFLAGS\” \
CXXFLAGS=\”$CXXFLAGS\” \
AM_CPPFLAGS=\”$AM_CPPFLAGS\” \
LDFLAGS=\”$LDFLAGS\” \
./configure \
$* \
–with-comment=MC-DB \
–with-server-suffix=-MC-DB-Server \
–with-charset=utf8 \
–with-plugins=blackhole,csv,innodb_plugin \
–enable-assembler \
–enable-local-infile \
–with-mysqld-user=%{mysqld_user} \
–with-unix-socket-path=/tmp/mysql.sock \
–with-pic \
-prefix=/usr \
–with-extra-charsets=gbk,gb2312,utf8 \
–without-debug \
–with-pthread \
–enable-thread-safe-client \
–with-client-ldflags=-all-static \
–without-ndb-debug \
%if %{YASSL_BUILD}
保存退出
6、定义在打包过程中不进行测试
export MYSQL_RPMBUILD_TEST=”no”
7、开始打包
cd /root/rpmbuild/SPECS/
rpmbuild -bb percona-server.spec
等待一段时间就可以看到编译好的软件包
/root/rpmbuild/RPMS/x86_64
Percona-Server-client-51-5.1.58-rel12.9.rhel6.x86_64.rpm
Percona-Server-devel-51-5.1.58-rel12.9.rhel6.x86_64.rpm
Percona-Server-server-51-5.1.58-rel12.9.rhel6.x86_64.rpm
Percona-Server-shared-51-5.1.58-rel12.9.rhel6.x86_64.rpm
Percona-Server-test-51-5.1.58-rel12.9.rhel6.x86_64.rpm
7、安装顺序
先安装这个
Percona-Server-shared-51-5.1.58-rel12.9.rhel6.x86_64.rpm
再安装其他软件包
8、已知问题：
无法打包为一个包
无法将所有问题安装到一个目录，例如我们之前一直安装的目录/usr/local/mysql
9、题外话
一般MySQL源代码包都有包含打包所需要的spec文件，一般存放在support-files/下
DB_HOST=”localhost”
DB_PORT=3306
DB_USER=”root”
DB_PASS=””
maxtime=10
sql=”SHOW PROCESSLIST”
sss=$(/usr/bin/mysqladmin processlist|sed -e “s/\s//g”|awk -F’|’ ‘{print $2,$7,substr($9,1,6)}’|awk ‘{if($2>'”$maxtime”‘ && $3==”SELECT”){print $1}}’)
for pid in $(echo “$sss”); do
/usr/bin/mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS -e “kill $pid”
done
echo “$sss”
date
转载自：http://home.phpchina.com/space.php?uid=24549&do=blog&id=158758