mysql 主从同步工具_mysql主从同步工具maatkit安装

最新推荐文章于 2024-05-05 09:41:23 发布

weixin_39983051

最新推荐文章于 2024-05-05 09:41:23 发布

阅读量359

点赞数

文章标签： mysql 主从同步工具

本文链接：https://blog.csdn.net/weixin_39983051/article/details/113228554

版权

Maatkit是非常不错的mysql管理工具，网络评价非常不错。包含以下主要工具：

1、mk-table-checksum 检查主从表是否一致的有效工具

2、mk-table-sync 有效保证表一致的工具，不必重载从表而能够保证一致。

3、mk-visual-explain exlpain解释工具

4、mk-heartbeat 主从同步的监视工具，能够给出从落后于主多少

5、mk-parallel-dump 多线程的mysqldump工具

6、mk-parallel-restore 多线程的表回复工具

7、mk-query-profiler 查询检测分析工具

8、mk-deadlock-logger 死锁的记录工具，支持innodb

9、mk-duplicate-key-checker key侦测工具

10、mk-show-grants 权限管理显示工具

11、mk-slave-restart slave的检测和重启工具

12、mk-slave-delay slave delay replication 的工具

13、mk-slave-prefetch

This tool implements Paul Tuckfield’s famous “oracle” algorithm to read ahead of the slave SQL thread in the relay logs, rewriting queries as SELECT and executing them to warm the slave’s caches. This can help an I/O-bound slave SQL thread run faster under some conditions, because it doesn’t have to wait for as much I/O to complete. 这个看起来非常不错。

maatkit 安装

安装在perl环境下安装

先检查perl环境是否存在。

perl -v

1.mysql安装在此省略。

2.安装DBI 下载地址：http://ftp.cuhk.edu.hk/pub/packages/perl/CPAN/authors/id/T/TI/TIMB/

[root@localhost ~]# wget http://ftp.cuhk.edu.hk/pub/packages/perl/CPAN/authors/id/T/TI/TIMB/DBI-1.616.tar.gz

[root@localhost ~]# tar zxvf DBI-1.616.tar.gz

[root@localhost ~]# cd DBI-1.616

[root@localhost ~]# perl Makefile.PL

*** Your LANG environment variable is set to 'en_US.UTF-8'

*** This may cause problems for some perl installations.

*** If you get test failures, please try again with LANG unset.

*** If that then works, please email dbi-dev@perl.org with details

*** including the output of 'perl -V'

*** You are using a perl configured with threading enabled.

*** You should be aware that using multiple threads is

*** not recommended for production environments.

Your perl was compiled with gcc (version 4.1.2 20080704 (Red Hat 4.1.2-46)), okay.

Creating test wrappers for DBD::Gofer:

....

Warning: prerequisite ExtUtils::MakeMaker 6.48 not found. We have 6.30.

Warning: prerequisite Test::Simple 0.90 not found. We have 0.62.

I see you're using perl 5.008008 on x86_64-linux-thread-multi, okay.

Remember to actually *read* the README file!

Use 'make' to build the software (dmake or nmake on Windows).

Then 'make test' to execute self tests.

Then 'make install' to install the DBI and then delete this working

directory before unpacking and building any DBD::* drivers.

Writing Makefile for DBI

[root@localhost DBI-1.616]# make

/usr/bin/perl "-MExtUtils::Command" -e mkpath blib/lib/DBI

rm -f blib/lib/DBI/Changes.pm

cp Changes blib/lib/DBI/Changes.pm

cp Driver_xst.h blib/arch/auto/DBI/Driver_xst.h

cp lib/DBD/Proxy.pm blib/lib/DBD/Proxy.pm

cp lib/DBI/Gofer/Response.pm blib/lib/DBI/Gofer/Response.pm

cp lib/DBI/Util/_accessor.pm blib/lib/DBI/Util/_accessor.pm

cp lib/DBI/Gofer/Transport/Base.pm blib/lib/DBI/Gofer/Transport/Base.pm

....

Manifying blib/man3/Win32::DBIODBC.3pm

Manifying blib/man3/DBI::DBD::SqlEngine.3pm

Manifying blib/man3/DBI::PurePerl.3pm

Manifying blib/man3/DBI::ProfileData.3pm

[root@localhost DBI-1.616]# make install

Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/.packlist

Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod

3 安装data-showtable包下载地址：http://search.cpan.org/

[root@localhost ~]# wget http://search.cpan.org/CPAN/authors/id/A/AK/AKSTE/Data-ShowTable-3.3.tar.gz

[root@localhost ~]# tar zxvf Data-ShowTable-3.3.tar.gz

[root@localhost ~]# cd Data-ShowTable-3.3

[root@localhost Data-ShowTable-3.3]# perl Makefile.PL

Checking if your kit is complete...

Looks good

Writing Makefile for Data::ShowTable

[root@localhost Data-ShowTable-3.3]# make

cp ShowTable.pm blib/lib/Data/ShowTable.pm

cp showtable blib/script/showtable

/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/showtable

Manifying blib/man1/showtable.1

Manifying blib/man3/Data::ShowTable.3pm

*** ERROR: unterminated Iat line 724 in file ShowTable.pm

make的时候报错了：

*** ERROR: unterminated Iat line 724 in file ShowTable.pm

编辑一下ShowTable.pm 找到724行，修改

I 重新make

[root@localhost Data-ShowTable-3.3]# make

cp ShowTable.pm blib/lib/Data/ShowTable.pm

Manifying blib/man3/Data::ShowTable.3pm

[root@localhost Data-ShowTable-3.3]# make install

Installing /usr/lib/perl5/site_perl/5.8.8/Data/ShowTable.pm

Installing /usr/share/man/man1/showtable.1

Installing /usr/share/man/man3/Data::ShowTable.3pm

Installing /usr/bin/showtable

Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Data/ShowTable/.packlist

Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod

4.安装msql-mysql-modules下载地址：http://search.cpan.org/

[root@localhost ~]# wget http://search.cpan.org/CPAN/authors/id/J/JW/JWIED/Msql-Mysql-modules-1.2219.tar.gz

[root@localhost ~]# tar zxvf Msql-Mysql-modules-1.2219.tar.gz

[root@localhost ~]# cd Msql-Mysql-modules-1.2219

[root@localhost Msql-Mysql-modules-1.2219]# perl Makefile.PL

Which drivers do you want to install?

1) MySQL only

2) mSQL only (either of mSQL 1 or mSQL 2)

3) MySQL and mSQL (either of mSQL 1 or mSQL 2)

4) mSQL 1 and mSQL 2

5) MySQL, mSQL 1 and mSQL 2

Enter the appropriate number: [3] 1

Do you want to install the MysqlPerl emulation? You might keep your old

Mysql module (to be distinguished from DBD::mysql!) if you are concerned

about compatibility to existing applications! [n] n

Where is your MySQL installed? Please tell me the directory that

contains the subdir 'include'. [/usr/local/mysql]

Which database should I use for testing the MySQL drivers? [test]

On which host is database test running (hostname, ip address

or host:port) [localhost]

User name for connecting to database test? [undef] root

Password for connecting to database test? [undef] rootpassword

Creating files for MySQL ....................

WARNING: PL_FILES takes a hash reference not a array reference.

Please inform the author.

Checking if your kit is complete...

Looks good

Note (probably harmless): No library found for -lgz

Using DBI 1.616 (for perl 5.008008 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/

Writing Makefile for DBD::mysql

Writing Makefile for Msql-Mysql-modules

[root@localhost Msql-Mysql-modules-1.2219]# make

/usr/bin/pod2text mysql/lib/DBD/mysql.pm >README

/usr/bin/perl -Iblib/arch -Iblib/lib -I/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi \

-I/usr/lib/perl5/5.8.8 -Ilib -MExtUtils::PerlPP \

-e ppp "dbd/dbimon.in" "dbimon" "lib/DBD/mysql/Install/Config.pm"

make[1]: Entering directory `/home/zengxiong/maatkit/Msql-Mysql-modules-1.2219/mysql'

cp lib/DBD/mysql.pm ../blib/lib/DBD/mysql.pm

cp lib/Mysql.pm ../blib/lib/Mysql.pm

....

mysql.xs: In function ‘XS_DBD__mysql__db__ListDBs’:

mysql.xs:250: warning: passing argument 3 of ‘mysql_dr_error’ discards qualifiers from pointer target type

mysql.xs: In function ‘XS_DBD__mysql__db__ListTables’:

mysql.xs:271: warning: passing argument 3 of ‘mysql_dr_error’ discards qualifiers from pointer target type

make[1]: *** [mysql.o] Error 1

make[1]: Leaving directory `/home/zengxiong/maatkit/Msql-Mysql-modules-1.2219/mysql'

make: *** [subdirs] Error 2

make的时候报错了：

make[1]: *** [mysql.o] Error 1

make[1]: Leaving directory `/root/Msql-Mysql-modules-1.2219/mysql'

make: *** [subdirs] Error 2

第5步在执行到make的时候会在目录下生成mysql.o文件，cp /home/maatkit/DBD-mysql-4.019/mysql.o /home/maatkit/Msql-Mysql-modules-1.2219/mysql/

后，重新make一下就不会报错了。(Msql-Mysql-modules其实只是包含DBD:Msql和DBD:mysql??)

[root@localhost Msql-Mysql-modules-1.2219]# make

make[1]: Entering directory `/home/zengxiong/maatkit2/Msql-Mysql-modules-1.2219/mysql'

Running Mkbootstrap for DBD::mysql ()

chmod 644 mysql.bs

rm -f ../blib/arch/auto/DBD/mysql/mysql.so

gcc -shared -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic dbdimp.o mysql.o -L/usr/local/mysql/lib/mysql -o ../blib/arch/auto/DBD/mysql/mysql.so \

-L/usr/local/mysql/lib/mysql -lmysqlclient -lm -lz -L/usr/lib/gcc/x86_64-redhat-linux/4.1.2 -lgcc \

chmod 755 ../blib/arch/auto/DBD/mysql/mysql.so

cp mysql.bs ../blib/arch/auto/DBD/mysql/mysql.bs

chmod 644 ../blib/arch/auto/DBD/mysql/mysql.bs

Manifying ../blib/man3/DBD::mysql.3pm

Manifying ../blib/man3/Mysql.3pm

Manifying ../blib/man3/Bundle::DBD::mysql.3pm

make[1]: Leaving directory `/root/Msql-Mysql-modules-1.2219/mysql'

cp dbimon blib/script/dbimon

/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/dbimon

Manifying blib/man1/dbimon.1

[root@localhost Msql-Mysql-modules-1.2219]# make install

make[1]: Entering directory `/root/Msql-Mysql-modules-1.2219/mysql'

make[1]: Leaving directory `/root/Msql-Mysql-modules-1.2219/mysql'

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.bs

Files found in blib/arch: installing files in blib/lib into architecture dependent library tree

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/Mysql.pm

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/Mysql/Statement.pm

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql.pm

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/Bundle/DBD/mysql.pm

Installing /usr/share/man/man1/dbimon.1

Installing /usr/share/man/man3/DBD::mysql.3pm

Installing /usr/share/man/man3/Mysql.3pm

Installing /usr/share/man/man3/Bundle::DBD::mysql.3pm

Installing /usr/bin/dbimon

Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/Msql-Mysql-modules/.packlist

Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod

[root@localhost ~]# wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.019.tar.gz

[root@localhost ~]# tar zxvf DBD-mysql-4.019.tar.gz

[root@localhost ~]# cd DBD-mysql-4.019

[root@localhost DBD-mysql-4.019]# perl Makefile.PL --mysql_config=/usr/bin/mysql_config

PLEASE NOTE:

For 'make test' to run properly, you must ensure that the database user 'root' can connect to your MySQL server and has the proper privileges that these tests require such as 'drop table', 'create table', 'drop procedure', 'create procedure' as well as others.

mysql> grant all privileges on test.* to 'root'@'localhost' identified by 's3kr1t';

You can also optionally set the user to run 'make test' with:

perl Makefile.PL --testuser=username

I will use the following settings for compiling and testing:

cflags (mysql_config) = -I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv

embedded (mysql_config) =

libs (mysql_config) = -rdynamic -L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib64 -lssl -lcrypto

mysql_config (Users choice) = /usr/bin/mysql_config

nocatchstderr (default ) = 0

nofoundrows (default ) = 0

ssl (guessed ) = 1

testdb (default ) = test

testhost (default ) =

testpassword (default ) =

testsocket (default ) =

testuser (guessed ) = root

To change these settings, see 'perl Makefile.PL --help' and

'perldoc INSTALL'.

Checking if your kit is complete...

Looks good

Unrecognized argument in LIBS ignored: '-rdynamic'

Multiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/ /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 914

Using DBI 1.616 (for perl 5.008008 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/

Writing Makefile for DBD::mysql

[root@localhost DBD-mysql-4.019]# make

cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm

cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm

cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod

cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm

....

chmod 755 blib/arch/auto/DBD/mysql/mysql.so

cp mysql.bs blib/arch/auto/DBD/mysql/mysql.bs

chmod 644 blib/arch/auto/DBD/mysql/mysql.bs

Manifying blib/man3/DBD::mysql.3pm

Manifying blib/man3/DBD::mysql::INSTALL.3pm

Manifying blib/man3/Bundle::DBD::mysql.3pm

[root@localhost DBD-mysql-4.019]# make test

PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t

t/00base....................ok 1/6

# Failed test 'use DBD::mysql;'

# in t/00base.t at line 21.

# Tried to use 'DBD::mysql'.

# Error: Can't load '/home/zengxiong/maatkit/DBD-mysql-4.020/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.16: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.

# at (eval 6) line 2

# Compilation failed in require at (eval 6) line 2.

# BEGIN failed--compilation aborted at t/00base.t line 21.

t/00base....................NOK 2FAILED--Further testing stopped: Unable to load DBD::mysql

make: *** [test_dynamic] Error 9

make test出错了，如果报错像上面一样，那么就执行

[root@localhost DBD-mysql-4.019]# cp /usr/local/mysql/lib/*so* /usr/lib

[root@localhost DBD-mysql-4.019]# cp /usr/local/mysql/lib/*so* /usr/lib64/(如果64位否则是lib)

如果上面这一步没做，则到安装maatkit的时候，在perl Makefile.PL会出现警告：prerequisite DBD::mysql 1 not found.

Checking if your kit is complete...

Looks good

Warning: prerequisite DBD::mysql 1 not found.

Writing Makefile for maatkit

[root@localhost DBD-mysql-4.019]# make test

PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t

t/00base....................ok

t/10connect.................skipped

all skipped: ERROR: Access denied for user 'root'@'localhost' (using password: NO) Can't continue test

t/20createdrop..............skipped

all skipped: ERROR: Access denied for user 'root'@'localhost' (using password: NO). Can't continue test

t/25lockunlock..............skipped

....

all skipped: ERROR: Access denied for user 'root'@'localhost' (using password: NO). Can't continue test

t/86_bug_36972..............skipped

all skipped: ERROR: DBI connect('test','root',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at t/86_bug_36972.t line 14

All tests successful, 39 tests skipped.

Files=40, Tests=6, 3 wallclock secs ( 2.08 cusr + 0.30 csys = 2.38 CPU)

[root@localhost DBD-mysql-4.019]# make

[root@localhost DBD-mysql-4.019]# make install

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so

Files found in blib/arch: installing files in blib/lib into architecture dependent library tree

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql.pm

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql/GetInfo.pm

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql/INSTALL.pod

Installing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/Bundle/DBD/mysql.pm

Installing /usr/share/man/man3/DBD::mysql::INSTALL.3pm

Installing /usr/share/man/man3/DBD::mysql.3pm

Installing /usr/share/man/man3/Bundle::DBD::mysql.3pm

Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/.packlist

Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod

[root@localhost ~]# wget http://maatkit.googlecode.com/files/maatkit-7486.tar.gz

[root@localhost ~]# tar zxvf maatkit-7486.tar.gz

[root@localhost ~]# cd maatkit-7486

[root@localhost maatkit-7486]# perl Makefile.PL

Checking if your kit is complete...

Looks good

Writing Makefile for maatkit

[root@localhost maatkit-7486]# make install

cp maatkit.pod blib/lib/maatkit.pod

cp bin/mk-slave-move blib/script/mk-slave-move

....

Installing /usr/bin/mk-config-diff

Installing /usr/bin/mk-archiver

Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/maatkit/.packlist

Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod

安装的工具如下在/usr/bin 下：mk-%

weixin_39983051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 主从同步工具_mysql主从同步工具maatkit安装

Maatkit是非常不错的mysql管理工具，网络评价非常不错。包含以下主要工具：1、mk-table-checksum 检查主从表是否一致的有效工具2、mk-table-sync 有效保证表一致的工具，不必重载从表而能够保证一致。3、mk-visual-explain exlpain解释工具4、mk-heartbeat 主从同步的监视工具，能够给出从落后于主多少5、mk-parallel-du...
复制链接

扫一扫