mysqlfailover

星期日,2013-03-17 at 上午 9:43

mysqlfailover监控主备复制情况及故障转移，只支持5.6版本，要用到gtid_mode为ON

$ ./mysqlfailover --help
Usage: mysqlfailover --master=root@localhost --discover-slaves-login=root --candidates=root@host123:3306,root@host456:3306 
 
mysqlfailover - automatic replication health monitoring and failover
 
Options:
  --version             show program's version number and exit
  --help                
  --candidates=CANDIDATES
                        connection information for candidate slave servers for
                        failover in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>]. Valid only with
                        failover command. List multiple slaves in comma-
                        separated list.
  --discover-slaves-login=DISCOVER
                        at startup, query master for all registered slaves and
                        use the user name and password specified to connect.
                        Supply the user and password in the form
                        <user>[:<password>] or <login-path>. For example,
                        --discover-slaves-login=joe:secret will use 'joe' as
                        the user and 'secret' as the password for each
                        discovered slave.
  --exec-after=EXEC_AFTER
                        name of script to execute after failover or switchover
  --exec-before=EXEC_BEFORE
                        name of script to execute before failover or
                        switchover
  --log=LOG_FILE        specify a log file to use for logging messages
  --log-age=LOG_AGE     specify maximum age of log entries in days. Entries
                        older than this will be purged on startup. Default = 7
                        days.
  --master=MASTER       connection information for master server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>]
  --max-position=MAX_POSITION
                        Used to detect slave delay. The maximum difference
                        between the master's log position and the slave's
                        reported read position of the master. A value greater
                        than this means the slave is too far behind the
                        master. Default is 0.
  --ping=PING           Number of ping attempts for detecting downed server.
  --seconds-behind=MAX_DELAY
                        Used to detect slave delay. The maximum number of
                        seconds behind the master permitted before slave is
                        considered behind the master. Default is 0.
  --slaves=SLAVES       connection information for slave servers in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>]. List multiple slaves
                        in comma-separated list.
  --timeout=TIMEOUT     Maximum timeout in seconds to wait for each
                        replication command to complete. For example, timeout
                        for slave waiting to catch up to master. Default = 3.
  -i INTERVAL, --interval=INTERVAL
                        interval in seconds for polling the master for failure
                        and reporting health. Default = 15 seconds. Lowest
                        value is 5 seconds.
  -f FAILOVER_MODE, --failover-mode=FAILOVER_MODE
                        action to take when the master fails. 'auto' =
                        automatically fail to best slave, 'elect' = fail to
                        candidate list or if no candidate meets criteria fail,
                        'fail' = take no action and stop when master fails.
                        Default = 'auto'.
  --exec-fail-check=EXEC_FAIL
                        name of script to execute on each interval to invoke
                        failover
  --force               override the registration check on master for multiple
                        instances of the console monitoring the same master.
  --exec-post-failover=EXEC_POST_FAIL
                        name of script to execute after failover is complete
                        and the utility has refreshed the health report.
  --rediscover          Rediscover slaves on interval. Allows console to
                        detect when slaves have been removed or added.
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  --rpl-user=RPL_USER   the user and password for the replication user
                        requirement, in the form: <user>[:<password>] or
                        <login-path>. E.g. rpl:passwd - By default = none

注意1：需要权限还是有点大的，最后把all权限都给slave，当然这里为省事

$ ./mysqlfailover --master=slave:slave@xx.xx.xx.44:7038 --discover-slaves-login=slave:slave --log=log.txt
# Discovering slaves for master at xx.xx.xx.44:7038
WARNING: There are slaves that have not been registered with --report-host or --report-port.
ERROR: Query failed. 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
ERROR: Query failed. 1142: CREATE command denied to user 'slave'@'xx.xx.xx.xx' for table 'failover_console'
ERROR: Query failed. 1142: SELECT command denied to user 'slave'@'xx.xx.xx.xx' for table 'failover_console'
ERROR: Query failed. 1142: INSERT command denied to user 'slave'@'xx.xx.xx.xx' for table 'failover_console'

注意2：依赖的参数还是比较多的 gtid_mode(主备) master-info-repository(主) report-host(备) report-port(备)

$ ./mysqlfailover --master=slave:slave@xx.xx.xx.44:7038 --discover-slaves-login=slave:slave --log=log.txt
# Discovering slaves for master at xx.xx.xx.44:7038
Multiple instances of failover console found for master xx.xx.xx.44:7038.
If this is an error, restart the console with --force. 
Failover mode changed to 'FAIL' for this instance. 
Console will start in 10 seconds..........starting Console.
# Checking privileges.
ERROR: Failover requires --master-info-repository=TABLE for all slaves.

eg1:查看主备健康情况

$ ./mysqlfailover --master=slave:slave@xx.xx.xx.44:7038 --discover-slaves-login=slave:slave --log=log.txt -i 5
# Discovering slaves for master at xx.xx.xx.44:7038
Multiple instances of failover console found for master xx.xx.xx.44:7038.
If this is an error, restart the console with --force. 
Failover mode changed to 'FAIL' for this instance. 
Console will start in 10 seconds..........starting Console.
# Checking privileges.
 
MySQL Replication Failover Utility
Failover Mode = fail     Next Interval = Sun Mar 17 17:27:31 2013
 
NOTICE: Failover mode changed to fail due to another
        instance of the console running against master.
 
Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB  
mysql-bin.000014  326                                       
 
GTID Executed Set
1BCC5C6A-814B-11E2-88A0-00188B77085E:1
 
Replication Health Status
+----------------+-------+---------+--------+------------+---------+
| host           | port  | role    | state  | gtid_mode  | health  |
+----------------+-------+---------+--------+------------+---------+
| xx.xx.xx.44   | 7038  | MASTER  | UP     | ON         | OK      |
| xx.xx.xx.11  | 7039  | SLAVE   | UP     | ON         | OK      |
+----------------+-------+---------+--------+------------+---------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries
 
MySQL Replication Failover Utility
Failover Mode = fail     Next Interval = Sun Mar 17 17:27:31 2013
 
NOTICE: Failover mode changed to fail due to another
        instance of the console running against master.
 
Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB  
mysql-bin.000014  326                                       
 
GTID Executed Set
1BCC5C6A-814B-11E2-88A0-00188B77085E:1
 
Replication Health Status
+----------------+-------+---------+--------+------------+---------+
| host           | port  | role    | state  | gtid_mode  | health  |
+----------------+-------+---------+--------+------------+---------+
| xx.xx.xx.44   | 7038  | MASTER  | UP     | ON         | OK      |
| xx.xx.xx.11  | 7039  | SLAVE   | UP     | ON         | OK      |
+----------------+-------+---------+--------+------------+---------+
 
Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries

按L键效果：

MySQL Replication Failover Utility
Failover Mode = fail     Next Interval = Sun Mar 17 17:27:31 2013
 
NOTICE: Failover mode changed to fail due to another
        instance of the console running against master.
 
Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB  
mysql-bin.000014  326                                       
 
GTID Executed Set
1BCC5C6A-814B-11E2-88A0-00188B77085E:1
 
Log File
+-------------------------+-------------------------------------------------------------------------------+
| Date                    | Entry                                                                         |
+-------------------------+-------------------------------------------------------------------------------+
| 2013-03-17 12:13:21 PM  | INFO Discovering slaves for master at localhost:3306                          |
| 2013-03-17 12:13:21 PM  | INFO Registering instance on master.                                          |
| 2013-03-17 12:13:21 PM  | CRITICAL Topology must support global transaction ids and have GTID_MODE=ON.  |
| 2013-03-17 12:13:21 PM  | INFO Unregistering instance on master.                                        |
| 2013-03-17 12:13:21 PM  | INFO Failover console stopped.                                                |
| 2013-03-17 12:13:21 PM  | CRITICAL Topology must support global transaction ids and have GTID_MODE=ON.  |
| 2013-03-17 12:14:12 PM  | INFO Discovering slaves for master at localhost:3306                          |
| 2013-03-17 12:14:12 PM  | INFO Discovering slave at xxxxxx.hostname:3406                               |
| 2013-03-17 12:14:12 PM  | INFO Found slave: xxxxxx.hostname:3406                                       |
| 2013-03-17 12:14:12 PM  | INFO Discovering slave at xxxxxx.hostname:3506                               |
| 2013-03-17 12:14:12 PM  | INFO Found slave: xxxxxx.hostname:3506                                       |
| 2013-03-17 12:14:12 PM  | INFO Registering instance on master.                                          |
| 2013-03-17 12:14:12 PM  | CRITICAL Topology must support global transaction ids and have GTID_MODE=ON.  |
| 2013-03-17 12:14:12 PM  | INFO Unregistering instance on master.                                        |
| 2013-03-17 12:14:12 PM  | INFO Failover console stopped.                                                |
| 2013-03-17 12:14:12 PM  | CRITICAL Topology must support global transaction ids and have GTID_MODE=ON.  |
+-------------------------+-------------------------------------------------------------------------------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs L-log entries Up|Down-scroll

eg2:演示failover，当44挂掉时，11会升级为主库，继续提供服务

[Mysql@xx.hostname ~]
$mysqladmin -uroot shutdown -S /tmp/mysql.sock 
 
Failover starting in 'auto' mode...
# Candidate slave xx.xx.xx.11:7039 will become the new master.
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# Discovering slaves for master at xx.xx.xx.11:7039
 
Failover console will restart in 5 seconds.
 
MySQL Replication Failover Utility
Failover Mode = fail     Next Interval = Sun Mar 17 17:30:03 2013
 
NOTICE: Failover mode changed to fail due to another
        instance of the console running against master.
 
Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB  
mysql-bin.000025  191                                       
 
GTID Executed Set
1BCC5C6A-814B-11E2-88A0-00188B77085E:1
 
Log File
+----------------+-------+---------+--------+------------+---------+
| host           | port  | role    | state  | gtid_mode  | health  |
+----------------+-------+---------+--------+------------+---------+
| xx.xx.xx.11    | 7039  | MASTER  | UP     | ON         | OK      |
+----------------+-------+---------+--------+------------+---------+
By admin | Posted in 数据库 | Comments (2)

mysqlindexcheck

星期日,2013-03-17 at 上午 7:28

mysqlindexcheck是来检查库中是否存在冗余索引，并生成删除冗余索引sql，支持BTRR、HASH、SPATIAL、FULLTEXT

$ ./mysqlindexcheck --help
Usage: mysqlindexcheck --server=user:pass@host:port:socket db1.table1 db2 db3.table2
 
mysqlindexcheck - check for duplicate or redundant indexes
 
Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  -d, --show-drops      display DROP statements for dropping indexes
  -i, --show-indexes    display indexes for each table
  -s, --skip            skip tables that do not exist
  -f FORMAT, --format=FORMAT
                        display the list of indexes per table in either sql,
                        grid (default), tab, csv, or vertical format
  --stats               show index performance statistics
  --best=BEST           limit index statistics to the best N indexes
  --worst=WORST         limit index statistics to the worst N indexes
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
 
root@luoxuan 02:50:35>show create table test_index \G;
*************************** 1. row ***************************
       Table: test_index
Create Table: CREATE TABLE `test_index` (
  `a` bigint(20) NOT NULL AUTO_INCREMENT,
  `b` bigint(20) NOT NULL,
  `c` varchar(1000) DEFAULT NULL,
  `d` bigint(20) NOT NULL,
  PRIMARY KEY (`a`),
  KEY `ind_b` (`b`),
  KEY `ind_dc` (`b`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

eg1:展示luoxuan库中的冗余索引

$ ./mysqlindexcheck --server=root@localhost luoxuan
# Source on localhost: ... connected.
# The following indexes are duplicates or redundant for table luoxuan.test_index:
#
CREATE INDEX ind_b ON luoxuan.test_index (b) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX ind_dc ON luoxuan.test_index (b, d) USING BTREE

eg2:展示luoxuan库中的冗余索引，并打印drop索引语句、表索引情况、索引统计信息

$ ./mysqlindexcheck --server=root@localhost luoxuan -d -i --stats 
# Source on localhost: ... connected.
# Showing indexes from luoxuan.test_index:
#
+-----------+-------------+----------+--------+----------+
| database  | table       | name     | type   | columns  |
+-----------+-------------+----------+--------+----------+
| luoxuan   | test_index  | PRIMARY  | BTREE  | a        |
| luoxuan   | test_index  | ind_b    | BTREE  | b        |
| luoxuan   | test_index  | ind_dc   | BTREE  | b, d     |
+-----------+-------------+----------+--------+----------+
#
# The following indexes are duplicates or redundant for table luoxuan.test_index:
#
CREATE INDEX ind_b ON luoxuan.test_index (b) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX ind_dc ON luoxuan.test_index (b, d) USING BTREE
#
# DROP statements:
#
ALTER TABLE luoxuan.test_index DROP INDEX ind_b;
#
# Showing indexes from luoxuan.test_index2:
#
+-----------+--------------+----------+--------+----------+
| database  | table        | name     | type   | columns  |
+-----------+--------------+----------+--------+----------+
| luoxuan   | test_index2  | PRIMARY  | BTREE  | a        |
| luoxuan   | test_index2  | ind_bb   | BTREE  | b        |
+-----------+--------------+----------+--------+----------+

注意：如果表中没有数据，–stats不起作用

eg3:如果表中有索引冗余，只保留效率最好一个

root@luoxuan 03:23:11>show index from test_index;
+------------+------------+----------+--------------+-------------+-----------+-------------+------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type |
+------------+------------+----------+--------------+-------------+-----------+-------------+------------+
| test_index |          0 | PRIMARY  |            1 | a           | A         |      500124 | BTREE      |
| test_index |          1 | ind_b    |            1 | b           | A         |      500124 | BTREE      |
| test_index |          1 | ind_dc   |            1 | b           | A         |      500124 | BTREE      |
| test_index |          1 | ind_dc   |            2 | d           | A         |      500124 | BTREE      |
+------------+------------+----------+--------------+-------------+-----------+-------------+------------+
4 rows in set (0.00 sec)
 
root@luoxuan 03:23:13>show index from test_index7;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+------------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Index_type |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+------------+
| test_index7 |          0 | PRIMARY   |            1 | a           | A         |      500124 | BTREE      |
| test_index7 |          1 | ind_b     |            1 | b           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bc  |            1 | b           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bc  |            2 | c           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bcd |            1 | b           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bcd |            2 | c           | A         |      500124 | BTREE      |
| test_index7 |          1 | index_bcd |            3 | d           | A         |      500124 | BTREE      |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+------------+
7 rows in set (0.01 sec)
 
$ ./mysqlindexcheck --server=root@localhost luoxuan --stats --best=1 -d
# Source on localhost: ... connected.
# The following indexes are duplicates or redundant for table luoxuan.test_index:
#
CREATE INDEX ind_b ON luoxuan.test_index (b) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX ind_dc ON luoxuan.test_index (b, d) USING BTREE
#
# DROP statements:
#
ALTER TABLE luoxuan.test_index DROP INDEX ind_b;
#
#
# Showing the top best performing indexes from luoxuan.test_index:
#
+-----------+-------------+---------+---------+-----------+--------------+--------------+------------+----------+
| database  | table       | name    | column  | sequence  | num columns  | cardinality  | est. rows  | percent  |
+-----------+-------------+---------+---------+-----------+--------------+--------------+------------+----------+
| luoxuan   | test_index  | ind_dc  | d       | 2         | 2            | 500124       | 500124     | 100.00   |
+-----------+-------------+---------+---------+-----------+--------------+--------------+------------+----------+
# The following indexes are duplicates or redundant for table luoxuan.test_index7:
#
CREATE INDEX ind_b ON luoxuan.test_index7 (b) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX index_bc ON luoxuan.test_index7 (b, c(255)) USING BTREE
#
CREATE INDEX index_bc ON luoxuan.test_index7 (b, c(255)) USING BTREE
#     may be redundant or duplicate of:
CREATE INDEX index_bcd ON luoxuan.test_index7 (b, c(255), d) USING BTREE
#
# DROP statements:
#
ALTER TABLE luoxuan.test_index7 DROP INDEX ind_b;
ALTER TABLE luoxuan.test_index7 DROP INDEX index_bc;
#
#
# Showing the top best performing indexes from luoxuan.test_index7:
#
+-----------+--------------+------------+---------+-----------+--------------+--------------+------------+----------+
| database  | table        | name       | column  | sequence  | num columns  | cardinality  | est. rows  | percent  |
+-----------+--------------+------------+---------+-----------+--------------+--------------+------------+----------+
| luoxuan   | test_index7  | index_bcd  | b       | 1         | 3            | 500124       | 500124     | 100.00   |
+-----------+--------------+------------+---------+-----------+--------------+--------------+------------+----------+
By admin | Posted in 数据库 | Comments (0)

mysqldbexport&mysqldbimport

星期日,2013-03-17 at 上午 3:16

mysqldbexport 表数据及结构导出工具，和mysqldump类似，主要在于mysqldbexport支持多种格式，可以有选择的打印主备切换的change语句。在mysqldump中，必须自己脚本中加入change语句
mysqldbimport 表数据及结构导入工具

具体参看：

http://dev.mysql.com/doc/workbench/en/mysqldbexport.html

http://dev.mysql.com/doc/workbench/en/mysqldbimport.html

By admin | Posted in 数据库 | Comments (0)

mysqldiff

星期日,2013-03-17 at 上午 3:15

mysqldiff来比较对象之间不同，有点类似mysqldbcompare

$ ./mysqldiff  --help
Usage: mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4
 
mysqldiff - compare object definitions among objects where the difference is
how db1.obj1 differs from db2.obj2
 
Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --server1=SERVER1     connection information for first server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --server2=SERVER2     connection information for second server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  --width=WIDTH         display width
  --force               do not abort when a diff test fails
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  -d DIFFTYPE, --difftype=DIFFTYPE
                        display differences in context format in one of the
                        following formats: [unified|context|differ|sql]
                        (default: unified).
  --changes-for=CHANGES_FOR
                        specify the server to show transformations to match
                        the other server. For example, to see the
                        transformation for transforming server1 to match
                        server2, use --changes-for=server1. Valid values are
                        'server1' or 'server2'. The default is 'server1'.
  --show-reverse        produce a transformation report containing the SQL
                        statements to conform the object definitions specified
                        in reverse. For example if --changes-for is set to
                        server1, also generate the transformation for server2.
                        Note: the reverse changes are annotated and marked as
                        comments.

eg1:比较两个库下面的表及表结构

$ ./mysqldiff --server1=root@localhost:3406  --server2=root@localhost:3506 luoxuan:luoxuan
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing luoxuan to luoxuan                                     [PASS]
# Comparing luoxuan.test_lx to luoxuan.test_lx                     [PASS]
Success. All objects are the same.

eg2:在server1中加个e字段，没有设置–changes-for，以server2为准

root@luoxuan 06:29:16>alter table test_lx add e int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
$ ./mysqldiff --server1=root@localhost:3406  --server2=root@localhost:3506 luoxuan:luoxuan
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing luoxuan to luoxuan                                     [PASS]
# Comparing luoxuan.test_lx to luoxuan.test_lx                     [FAIL]
# Object definitions differ. (--changes-for=server1)
#
--- luoxuan.test_lx
+++ luoxuan.test_lx
@@ -3,6 +3,5 @@
   `b` bigint(20) NOT NULL,
   `c` varchar(1000) DEFAULT NULL,
   `d` varchar(1000) DEFAULT NULL,
-  `e` int(11) DEFAULT NULL,
   PRIMARY KEY (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=gbk
Compare failed. One or more differences found.

eg3:产生一致性的表结构的sql，以server2为标准

$ ./mysqldiff --server1=root@localhost:3406  --server2=root@localhost:3506 luoxuan:luoxuan  --difftype=sql
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing luoxuan to luoxuan                                     [PASS]
# Comparing luoxuan.test_lx to luoxuan.test_lx                     [FAIL]
# Transformation for --changes-for=server1:
#
 
ALTER TABLE luoxuan.test_lx 
  DROP COLUMN e;
 
Compare failed. One or more differences found.
 
eg4:可以server1为标准，产生修改语句
$ ./mysqldiff --server1=root@localhost:3406  --server2=root@localhost:3506 luoxuan:luoxuan  --changes-for=server2 --difftype=sql
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing luoxuan to luoxuan                                     [PASS]
# Comparing luoxuan.test_lx to luoxuan.test_lx                     [FAIL]
# Transformation for --changes-for=server2:
#
 
ALTER TABLE luoxuan.test_lx 
  ADD COLUMN e int(11) NULL AFTER d;
 
Compare failed. One or more differences found.
By admin | Posted in 数据库 | Comments (0)

mysqldiskusage

星期日,2013-03-17 at 上午 3:12

mysqldiskusage可以查看binary log, slow query log, error log, general query log, relay log,InnoDB tablespaces，database的空间使用情况

eg1:查看luoxuan、xiyan库空间使用情况，如果xiyan库下面没有表的话，xiyan库是不去计算大小的

$ sudo ./mysqldiskusage --server=root@localhost:3306 luoxuan xiyan
# Source on localhost: ... connected.
# Database totals:
+----------+----------+
| db_name  |   total  |
+----------+----------+
| luoxuan  | 131,589  |
| xiyan    | 127,397  |
+----------+----------+
 
Total database disk usage = 258,986 bytes or 252.00 KB

eg2:查看所有信息

$  sudo ./mysqldiskusage --server=root@localhost:3306 -a -vv
# Source on localhost: ... connected.
# Database totals:
+---------------------+--------------+------------+-------------+----------+
| db_name             | db_dir_size  | data_size  | misc_files  |   total  |
+---------------------+--------------+------------+-------------+----------+
| luoxuan             | 115,205      | 16,384     | 115,205     | 131,589  |
| mysql               | 891,355      | 671,167    | 202,366     | 873,533  |
| performance_schema  | 154,041      | 0          | 154,041     | 154,041  |
| xiyan               | 111,013      | 16,384     | 111,013     | 127,397  |
+---------------------+--------------+------------+-------------+----------+
 
Total database disk usage = 1,286,560 bytes or 1.00 MB
 
# Log information.
+---------------+------------+
| log_name      |      size  |
+---------------+------------+
| db170075.log  | 5,258,516  |
| slow.log      |    18,756  |
| alert.log     |   168,451  |
+---------------+------------+
 
Total size of logs = 5,445,723 bytes or 5.00 MB
 
# binary log information:
Current binary log file = mysql-bin.002588
ERROR: The binary log are missing.

最后这个错误应该是mysql的bug引起的，根据log_bin去找文件，找不到文件，报错

root@(none) 07:07:53>show variables like 'log_bin'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

Setting this option causes the log_bin system variable to be set to ON (or 1), and not to the basename. This is a known issue; see Bug #19614 for more information.

By admin | Posted in 数据库 | Comments (0)