mysql-utilities⼯具介绍
(2).使⽤配置⽂件参数
[root@node5 ~]# vim /etc/myf
[www]
host=10.172.78.203
user=wuhan
password=system
port=3306
[root@node5 ~]# mysqlserverinfo  --server=/etc/myf[www] --format=vertical
# Source on 10.172.78.203: ... connected.
*************************1. row *************************
server: 10.172.78.203:3306
config_file: /etc/myf, /data/mysql-5.6.28/myf
binary_log: binlog.000003
binary_log_pos: 120
relay_log:mysql连接工具
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/myf, /data/mysql-5.6.28/myf
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
#
# 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
#
# 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
# Copying data for
#...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
# Copying data for
# Connecting to the current server as master
#...done.
[root@node5 data]#