mysql-utilities工具介绍

最新推荐文章于 2024-08-25 10:43:37 发布

cny0120

最新推荐文章于 2024-08-25 10:43:37 发布

阅读量292

点赞数

文章标签：数据库 python 开发工具

下载地址:http://pan.baidu.com/s/1gfkdMWN

1.安装mysql utilities工具
[root@node5 ~]# tar xvf mysql-utilities-1.6.4.tar.gz
[root@node5 ~]# cd mysql-utilities-1.6.4
[root@node5 mysql-utilities-1.6.4]# python ./setup.py build
[root@node5 mysql-utilities-1.6.4]# python ./setup.py install

2.查看mysql的配置参数(mysql_config_editor和mysqlserverinfo命令的使用)
(1).创建参数文件
[root@node5 ~]# mysql_config_editor set --login-path=1.txt --host=10.172.78.203 --password --user=wuhan --port=3306
Enter password:
[root@node5 ~]# mysql_config_editor print --login-path=1.txt
[1.txt]
user = wuhan
password = *****
host = 10.172.78.203
port = 3306
[root@node5 ~]# mysqlserverinfo --server=1.txt --format=vertical
# Source on 10.172.78.203: ... connected.
************************* 1. row *************************
server: 10.172.78.203:3306
config_file: /etc/my.cnf, /data/mysql-5.6.28/my.cnf
binary_log: binlog.000002
binary_log_pos: 1238
relay_log:
relay_log_pos:
version: 5.6.28-log
datadir: /data/mysql-5.6.28/data/
basedir: /data/mysql-5.6.28
plugin_dir: /data/mysql-5.6.28/lib/plugin/
general_log: OFF
general_log_file:
general_log_file_size:
log_error: /data/mysql-5.6.28/data/mysqld.log
log_error_file_size: 54668 bytes
slow_query_log: OFF
slow_query_log_file:
slow_query_log_file_size:
1 row.
#...done.
[root@node5 ~]#

(2).使用配置文件参数
[root@node5 ~]# vim /etc/my.cnf
[www]
host=10.172.78.203
user=wuhan
password=system
port=3306
[root@node5 ~]# mysqlserverinfo --server=/etc/my.cnf[www] --format=vertical
# Source on 10.172.78.203: ... connected.
*************************1. row *************************
server: 10.172.78.203:3306
config_file: /etc/my.cnf, /data/mysql-5.6.28/my.cnf
binary_log: binlog.000003
binary_log_pos: 120
relay_log:
relay_log_pos:
version: 5.6.28-log
datadir: /data/mysql-5.6.28/data/
basedir: /data/mysql-5.6.28
plugin_dir: /data/mysql-5.6.28/lib/plugin/
general_log: OFF
general_log_file:
general_log_file_size:
log_error: /data/mysql-5.6.28/data/mysqld.log
log_error_file_size: 64845 bytes
slow_query_log: OFF
slow_query_log_file:
slow_query_log_file_size:
1 row.
#...done.
[root@node5 ~]#

(3).命令行显示
[root@node5 ~]# mysqlserverinfo --server=wuhan:system@10.172.78.203:3306 --format=vertical
WARNING: Using a password on the command line interface can be insecure.
# Source on 10.172.78.203: ... connected.
************************* 1. row *************************
server: 10.172.78.203:3306
config_file: /etc/my.cnf, /data/mysql-5.6.28/my.cnf
binary_log: binlog.000003
binary_log_pos: 120
relay_log:
relay_log_pos:
version: 5.6.28-log
datadir: /data/mysql-5.6.28/data/
basedir: /data/mysql-5.6.28
plugin_dir: /data/mysql-5.6.28/lib/plugin/
general_log: OFF
general_log_file:
general_log_file_size:
log_error: /data/mysql-5.6.28/data/mysqld.log
log_error_file_size: 64957 bytes
slow_query_log: OFF
slow_query_log_file:
slow_query_log_file_size:
1 row.
#...done.
[root@node5 ~]#

2.清空多余的二进制日志文件(mysqlbinlogpurge)
--binlog=BINLOG --清除指定的二进制之前的文件
--master=MASTER --主数据库
--slaves=SLAVES --从数据库(如果有多个从库用分号隔开)

[root@node5 data]# mysqlbinlogpurge --master=root:system@localhost:3306 --slave=root:system@10.90.18.74:3306 -vv
# Checking user permission to purge binary logs...
#
# Master active binlog file: binlog.000007
# Checking slave: 10.90.18.74@3306
# I/O thread is currently reading: binlog.000007
# File position of the I/O thread: 309
# Master binlog file with last event executed by the SQL thread: binlog.000007
# I/O thread running: Yes
# SQL thread running: Yes
# Binlog file available: binlog.000007
# Latest binlog file replicated by all slaves: binlog.000006
# Latest not active binlog file: binlog.000006
# No binlog files can be purged.
# Binlog file available: binlog.000007
[root@node5 data]#

3.新建一个新的二进制日志文件(mysqlbinlogrotate)

[root@node5 data]# mysqlbinlogrotate --server=root:system@localhost:3306 -vv
# Checking user permission to rotate binary logs...
#
# Active binlog file: 'binlog.000008' (size: 120 bytes)'
# The binlog file has been rotated.
# New active binlog file: 'binlog.000009'
[root@node5 data]# mysqlbinlogrotate --server=root:system@localhost:3306 --min-size=1073741824 -vv --指定二进制日志进行切割

4.比较两个服务器的库是否一样
[root@node5 data]# mysqldbcompare --server1=root:system@localhost:3306 --server2=root:system@10.90.18.74:3306 wuhan --changes-for=server2 --difftype=context
--server1=SERVER1 --其中一台mysql服务器
--server2=SERVER2     --另一台mysql服务器
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on 10.90.18.74: ... connected.
# Checking databases wuhan on server1 and wuhan on server2
#
#   Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE t     pass FAIL ERROR: Row counts are not the same among `wuhan`.`t` and `wuhan`.`t`.
#
[root@node5 data]#

5.复制数据库(mysqldbcopy)
(1).本机拷贝数据库
[root@node5 data]# mysqldbcopy --source=root:system@localhost:3306 --destination=root:system@localhost:3306 tt:t --将本地的tt数据库复制一份为t数据库
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database tt renamed as t
# Copying TABLE tt.tt
# Copying data for TABLE tt.tt
#...done.
[root@node5 data]#

(2).从主库拷贝数据到从库
[root@node5 data]# mysqldbcopy --source=root:system@localhost:3306 --destination=qwer:system@10.90.18.74:3306 tt --rpl=master --rpl-user=root
--source=root:system@localhost:3306 --主库的用户名和密码
--destination=qwer:system@10.90.18.74:3306 --从库的用户名和密码,用户名必须使用with grant option权限,tt是主库的数据库名
--rpl=master --主库到从库
--rpl-user=root --主库的用户名
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Destination on 10.90.18.74: ... connected.
# Copying database tt
# Copying TABLE tt.tt
# Copying data for TABLE tt.tt
# Connecting to the current server as master
#...done.
[root@node5 data]#