000006be

mysql-utilities⼯具介绍

(2).使⽤配置⽂件参数

[root@node5~]#vim/etc/

[www]

host=10.172.78.203

user=wuhan

password=system

port=3306

[root@node5~]#mysqlserverinfo--server=/etc/[www]--format=vertical

#Sourceon10.172.78.203:...connected.

**************************************************

server:10.172.78.203:3306

config_file:/etc/,/data/mysql-5.6.28/

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/

log_error_file_size:64845bytes

slow_query_log:OFF

slow_query_log_file:

slow_query_log_file_size:

1row.

#...done.

[root@node5~]

#(3).命令⾏显⽰

[root@node5~]#mysqlserverinfo--server=wuhan:system@10.172.78.203:3306--format=vertical

WARNING:Usingapasswordonthecommandlineinterfacecanbeinsecure.

#Sourceon10.172.78.203:...connected.

**************************************************

server:10.172.78.203:3306

config_file:/etc/,/data/mysql-5.6.28/

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/

log_error_file_size:64957bytes

slow_query_log:OFF

slow_query_log_file:

slow_query_log_file_size:

1row.

#...done.

[root@node5~]

#2.清空多余的⼆进制⽇志⽂件(mysqlbinlogpurge)

--binlog=BINLOG--清除指定的⼆进制之前的⽂件

--master=MASTER--主数据库

--slaves=SLAVES--从数据库(如果有多个从库⽤分号隔开)

[root@node5data]#mysqlbinlogpurge--master=root:system@localhost:3306--slave=root:system@10.90.18.74:3306-vv

#Checkinguserpermissiontopurgebinarylogs...

##Masteractivebinlogfile:binlog.000007

#Checkingslave:10.90.18.74@3306

#I/Othreadiscurrentlyreading:binlog.000007

#FilepositionoftheI/Othread:309

#MasterbinlogfilewithlasteventexecutedbytheSQLthread:binlog.000007

#I/Othreadrunning:Yes

#SQLthreadrunning:Yes

#Binlogfileavailable:binlog.000007

#Latestbinlogfilereplicatedbyallslaves:binlog.000006

#Latestnotactivebinlogfile:binlog.000006

#Nobinlogfilescanbepurged.

#Binlogfileavailable:binlog.000007

[root@node5data]

#3.新建⼀个新的⼆进制⽇志⽂件(mysqlbinlogrotate)

[root@node5data]#mysqlbinlogrotate--server=root:system@localhost:3306-vv

#Checkinguserpermissiontorotatebinarylogs...

##Activebinlogfile:'binlog.000008'(size:120bytes)'

#Thebinlogfilehasbeenrotated.

#Newactivebinlogfile:'binlog.000009'

[root@node5data]#mysqlbinlogrotate--server=root:system@localhost:3306--min-size=1073741824-vv--指定⼆进制⽇志进⾏切割

4.⽐较两个服务器的库是否⼀样

[root@node5data]#mysqldbcompare--server1=root:system@localhost:3306--server2=root:system@10.90.18.74:3306wuhan--changes-

for=server2--difftype=context

--server1=SERVER1--其中⼀台mysql服务器

--server2=SERVER2--另⼀台mysql服务器

#WARNING:Usingapasswordonthecommandlineinterfacecanbeinsecure.

#server1onlocalhost:...connected.

#server2on10.90.18.74:...connected.

#Checkingdatabaseswuhanonserver1andwuhanonserver2

##DefnRowData

#TypeObjectNameDiffCountCheck

#-------------------------------------------------------------------------

#TABLEtpassFAILERROR:Rowcountsarenotthesameamong`wuhan`.`t`and`wuhan`.`t`.

#[root@node5data]

#5.复制数据库(mysqldbcopy)

(1).本机拷贝数据库

[root@node5data]#mysqldbcopy--source=root:system@localhost:3306--destination=root:system@localhost:3306tt:t--将本地的tt数据库

复制⼀份为t数据库

WARNING:Usingapasswordonthecommandlineinterfacecanbeinsecure.

#Sourceonlocalhost:...connected.

#Destinationonlocalhost:...connected.

#Copyingdatabasettrenamedast

#

#

#...done.

[root@node5data]

#(2).从主库拷贝数据到从库

[root@node5data]#mysqldbcopy--source=root:system@localhost:3306--destination=qwer:system@10.90.18.74:3306tt--rpl=master--rpl-

user=root

--source=root:system@localhost:3306--主库的⽤户名和密码

--destination=qwer:system@10.90.18.74:3306--从库的⽤户名和密码,⽤户名必须使⽤withgrantoption权限,tt是主库的数据库名

--rpl=master--主库到从库

--rpl-user=root--主库的⽤户名

WARNING:Usingapasswordonthecommandlineinterfacecanbeinsecure.

#Sourceonlocalhost:...connected.

#Destinationon10.90.18.74:...connected.

#Copyingdatabasett

#

#

#Connectingtothecurrentserverasmaster

#...done.

[root@node5data]#