lvm备份mysql及slave配置

lvm备份mysql有些限制,具体可以访问http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/

1、逻辑卷及快照的创建

查看pv空间情况:

[root@dg3 ~]# pvscan
  PV /dev/sdb1   VG mysqldata    lvm2 [8.00 GB / 188.00 MB free]
  PV /dev/sda2   VG VolGroup00   lvm2 [19.88 GB / 0    free]
  Total: 2 [27.87 GB] / IN USE: 2 [27.87 GB] / IN no VG: 0 [0   ]

对所有表要读锁,为做一致性快照

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 ROWS affected (0.07 sec)

创建188M的lv:

[root@dg3 ~]# lvcreate -L188M -s -n dbbackup /dev/mysqldata/lvdata
  Logical volume "dbbackup" created

记录slave reply的位置,并解锁:

mysql> SHOW master STATUS;
+------------------+----------+--------------+------------------+
| File             | POSITION | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |  1244591 |              |                  |
+------------------+----------+--------------+------------------+
1 ROW IN SET (0.01 sec)
 
mysql> UNLOCK TABLES;
Query OK, 0 ROWS affected (0.00 sec)

pv空间使用完了:

  [root@dg3 ~]# pvscan
  PV /dev/sdb1   VG mysqldata    lvm2 [8.00 GB / 0    free]
  PV /dev/sda2   VG VolGroup00   lvm2 [19.88 GB / 0    free]
  Total: 2 [27.87 GB] / IN USE: 2 [27.87 GB] / IN no VG: 0 [0   ]

查看lv使用情况:

[root@dg3 ~]# lvscan
  ACTIVE   Original '/dev/mysqldata/lvdata' [7.81 GB] inherit
  ACTIVE   Snapshot '/dev/mysqldata/dbbackup' [188.00 MB] inherit
  ACTIVE            '/dev/VolGroup00/LogVol00' [15.94 GB] inherit
  ACTIVE            '/dev/VolGroup00/LogVol01' [3.94 GB] inherit

创建lv挂载目录,并挂载创建的目录:

[root@dg3 ~]# mkdir /mnt/backup
[root@dg3 ~]# mount /dev/mysqldata/dbbackup /mnt/backup
[root@dg3 ~]# cd /mnt/backup

可以看到快照创建成功:

[root@dg3 backup]# ls
mysql
[root@dg3 backup]# mkdir /var/backup
[root@dg3 backup]# cp -R mysql /var/backup

2、快照删除

[root@dg3 mnt]# umount /mnt/backup
[root@dg3 mnt]# lvscan
  ACTIVE   Original '/dev/mysqldata/lvdata' [7.81 GB] inherit
  ACTIVE   Snapshot '/dev/mysqldata/dbbackup' [188.00 MB] inherit
  ACTIVE            '/dev/VolGroup00/LogVol00' [15.94 GB] inherit
  ACTIVE            '/dev/VolGroup00/LogVol01' [3.94 GB] inherit
[root@dg3 mnt]# lvremove /dev/mysqldata/dbbackup
Do you really want TO remove active logical volume dbbackup? [y/n]: y
  Logical volume "dbbackup" successfully removed

3、配置slave
把mysql文件夹传到要恢复的机器上,启动mysql

[root@dg4 bin]# ./mysqld_safe --user=mysql &
mysql> CHANGE master TO master_host='192.168.201.14', master_user='repl', master_password='repl', 
master_log_file='mysql-bin.000001', master_log_pos=1244591;
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> START slave;
Query OK, 0 ROWS affected (0.00 sec)

参考文献:http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/

mysql5.5.13源码安装

mysql的5.5.13安装和以前版本还是些区别,我把安装步骤及遇到的问题罗列一下:

从5.5开始使用cmake(CMake是一套跨平台的工程构建工具)
下载:http://www.cmake.org/
Autotools和cmake选项对应关系:http://forge.mysql.com/wiki/Autotools_to_CMake_Transition_Guide

一、

[root@dg3 mysql-5.5.13]#groupadd mysql
[root@dg3 mysql-5.5.13]#useradd -r -g mysql mysql
[root@dg3 mysql-5.5.13]# cmake . \
> -DCMAKE_INSTALL_PREFIX=/mnt/u01/mysql \
> -DMYSQL_DATADIR=/mnt/u01/mysql/DATA \
> -DDEFAULT_CHARSET=gbk \
> -DDEFAULT_COLLATION=gbk_chinese_ci \
> -DWITH_INNOBASE_STORAGE_ENGINE=1

二、

make

三、

make install

四、

[root@dg3 mysql]# chown -R mysql .
[root@dg3 mysql]# chgrp -R mysql .

五、

[root@dg3 mysql]# ./scripts/mysql_install_db \
> --user=mysql \
> --basedir=/mnt/u01/mysql \
> --datadir=/mnt/u01/mysql/data \
> --no-defaults
Installing MySQL system TABLES...
OK
Filling help TABLES...
OK

六、安全考虑

[root@dg3 mysql]# chown -R root .
[root@dg3 mysql]# chown -R mysql DATA

七、

[root@dg3 mysql]# cp support-files/my-medium.cnf /etc/my.cnf

八、

[root@dg3 bin]# ./mysqld_safe --user=mysql &
[1] 25900
[root@dg3 bin]# 110624 21:10:05 mysqld_safe Logging TO '/mnt/u01/mysql/data/dg3.localdomain.err'.
110624 21:10:05 mysqld_safe Starting mysqld daemon WITH DATABASES FROM /mnt/u01/mysql/DATA
 
[root@dg3 bin]# mysql -uroot
Welcome TO the MySQL monitor.  Commands END WITH ; OR \g.
Your MySQL connection id IS 1
Server version: 5.5.13-log SOURCE distribution

需要注意的问题:

问题一、

[root@dg3 mysql-5.5.13]# cmake . \
> -DCMAKE_INSTALL_PREFIX=/mnt/u01/mysql \
> -DMYSQL_DATADIR=/mnt/u01/mysql/DATA \
> -DDEFAULT_CHARSET=gbk \
> -DDEFAULT_COLLATION=gbk_chinese_ci \
> -DWITH_INNOBASE_STORAGE_ENGINE=1
-- MySQL 5.5.13
Warning: Bison executable NOT found IN PATH
-- Configuring done
-- Generating done
-- Build files have been written to: /mnt/u01/mysql-5.5.13

Bison没有安装,那就安装一下!
下载地址:
http://www.icewalkers.com/Linux/Software/515090/bison.html

问题二、

[root@dg3 mysql]# ./scripts/mysql_install_db \
> --user=mysql \
> --basedir=/mnt/u01/mysql \
> --datadir=/mnt/u01/mysql/data \
> --no-defaults
Installing MySQL system TABLES...
110624 20:00:17 [ERROR] COLLATION 'latin1_swedish_ci' IS NOT valid FOR CHARACTER SET 'gbk'
110624 20:00:17 [ERROR] Aborting
 
110624 20:00:17 [Note] /mnt/u01/mysql/bin/mysqld: Shutdown complete

注意:DDEFAULT_CHARSET和DDEFAULT_COLLATION一定要对应

参考文献:
http://dev.mysql.com/doc/refman/5.5/en/installing-source-distribution.html

如何生成perl安装包

http://mathforum.org/~ken/perl_modules.html

innotop的Query segment fault

上次在公共的机器上安装了innotop,今天却跑不起来了,报错如下:

[RO] Query List (? for help) localhost, 12+23:52:22.049, 0.39 QPS, 18/1/3 con/run/cac thds, 5.1.48-log
 
When   Load  QPS   Slow   Se/In/Up/De%   QCacheHit  KCacheHit  BpsIn  BpsOut
Total  0.00  0.39  6.33k  59/1228/10/27      0.00%     72.73%  54.48   53.12
 
Cmd    ID      State  User   Host           DB      Time   Query段错误

google中找到如下信息:

I reproduced the error with :
#!/usr/bin/perl
#use Term::ReadKey qw(ReadMode ReadKey);
use Term::ReadKey;
ReadKey(10);

I fixed by upgrading to :
perl-TermReadKey-2.30-4.el5

查一下模块及版本号:

[root@xxx ~]# perl mod_ver.pl
......
Term::ReadKey --    2.14
......

mod_ver.pl脚本:

#!/usr/bin/perl    
use strict;
use ExtUtils::Installed;
 
my $inst= ExtUtils::Installed->new();    
my @modules = $inst->modules();    
 
foreach(@modules)
{
                my $ver = $inst->version($_) || "???";    
                printf("%-12s --    %s\n", $_, $ver);
}
exit 0;

重新安装Term::ReadKey,使用2.30版本,问题解决。

perl模块操作

删除模块:把*.pm文件删除,但删除不是很干净,所以不建议那么做

一、查询模块安装路径,确认*.pm文件位置(不建议做法)
1、找到模块安装路径

perl -e 'print join "\n",@INC'

2、

rm *.pm

这种方式删除后,还是能查到模块的信息。
查询已安装的模块:perl -MExtUtils::Installed -le ‘print foreach ExtUtils::Installed->new->modules’

二、脚本删除,很干净(建议使用)

#!/usr/local/bin/perl -w 
 
use ExtUtils::Packlist; 
use ExtUtils::Installed; 
 
$ARGV[0] or die "Usage: $0 Module::Name\n"; 
 
my $mod = $ARGV[0]; 
 
my $inst = ExtUtils::Installed->new(); 
 
    foreach my $item (sort($inst->files($mod))) { 
             print "removing $item\n"; 
             unlink $item; 
          } 
 
     my $packfile = $inst->packlist($mod)->packlist_file(); 
          print "removing $packfile\n"; 
          unlink $packfile;

查询模块:查询模块的方法有很多种,看各人喜好了

perl -MTime::HiRes -e 'print 0';

机器装那些模块:

perl -MExtUtils::Installed -le 'print foreach ExtUtils::Installed->new->modules'

看Perl内置安装了哪些模块:

perl -MModule::CoreList -le 'print foreach Module::CoreList::find_modules

如何知道自己装了哪些PERL的模块:

find `perl -e 'print "@INC"'` -name '*.pm' -print

查询当前使用的模块的版本

1、$perl -MTime::HiRes -e 'print $Module::VERSION;'
2、
#!/usr/bin/perl    
use strict;
use ExtUtils::Installed;
 
my $inst= ExtUtils::Installed->new();    
my @modules = $inst->modules();    
 
foreach(@modules)
{
                my $ver = $inst->version($_) || "???";    
                printf("%-12s --    %s\n", $_, $ver);
}
exit 0;

安装模块:

一、cpan
二、perl -MCPAN -e 'install 模块名'
三、
perl Makefile.PL
make
make install
四、cpanm

参考:http://www.php-oa.com/2008/11/15/perl-note.html