MYSQL DATABASE日常运维命令

最新推荐文章于 2023-09-16 08:00:00 发布

独行侠谁也不带

最新推荐文章于 2023-09-16 08:00:00 发布

阅读量281

点赞数

分类专栏： MYSQL运维文章标签： mysql 运维数据库

本文链接：https://blog.csdn.net/xielong8866/article/details/127649393

版权

MYSQL运维专栏收录该内容

6 篇文章 0 订阅

订阅专栏

介绍一些基本的mysql数据库日常运维命令

查看某个数据库中表和索引的总大小

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test2              |
+--------------------+
4 rows in set (0.00 sec)

将数据库名代入到语句之中，我们要查看test数据库中的所有表的大小，数据库名不需要大写。

mysql> select TABLE_NAME, \
    -> concat(truncate(data_length/1024/1024/1024, 2), 'GB') as data_size, \
    -> concat(truncate(index_length /1024/1024/1024, 2), 'GB') as index_size, \
    -> truncate(data_length/1024/1024/1024, 2)+  truncate(index_length /1024/1024/1024, 2) \
    -> from information_schema.tables where TABLE_SCHEMA = 'test'  order by data_length desc;
+------------+-----------+------------+-------------------------------------------------------------------------------------+
| TABLE_NAME | data_size | index_size | truncate(data_length/1024/1024/1024, 2)+  truncate(index_length /1024/1024/1024, 2) |
+------------+-----------+------------+-------------------------------------------------------------------------------------+
| xl         | 0.00GB    | 0.00GB     |                                                                                0.00 |
+------------+-----------+------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

检查所有数据库的使用总空间以及总索引空间，二者之和为数据库大小：

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;
+--------------------+-----------+------------+
| TABLE_SCHEMA       | data_size | index_size |
+--------------------+-----------+------------+
| test2              | 0.00 MB   | 0.00MB     |
| test               | 0.00 MB   | 0.00MB     |
| mysql              | 0.53 MB   | 0.08MB     |
| information_schema | 0.00 MB   | 0.00MB     |
+--------------------+-----------+------------+
4 rows in set (0.00 sec)

查看mysql数据库的所有数据文件存放路径：

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

[root@otherdb ~]# cd /var/lib/mysql/

[root@otherdb mysql]# ls *
ibdata1  ib_logfile0  ib_logfile1  mysql.sock

mysql:
columns_priv.frm  func.MYD           help_keyword.MYI   ndb_binlog_index.frm  procs_priv.MYD   tables_priv.MYI            time_zone_transition.frm
columns_priv.MYD  func.MYI           help_relation.frm  ndb_binlog_index.MYD  procs_priv.MYI   time_zone.frm              time_zone_transition.MYD
columns_priv.MYI  general_log.CSM    help_relation.MYD  ndb_binlog_index.MYI  servers.frm      time_zone_leap_second.frm  time_zone_transition.MYI
db.frm            general_log.CSV    help_relation.MYI  plugin.frm            servers.MYD      time_zone_leap_second.MYD  time_zone_transition_type.frm
db.MYD            general_log.frm    help_topic.frm     plugin.MYD            servers.MYI      time_zone_leap_second.MYI  time_zone_transition_type.MYD
db.MYI            help_category.frm  help_topic.MYD     plugin.MYI            slow_log.CSM     time_zone.MYD              time_zone_transition_type.MYI
event.frm         help_category.MYD  help_topic.MYI     proc.frm              slow_log.CSV     time_zone.MYI              user.frm
event.MYD         help_category.MYI  host.frm           proc.MYD              slow_log.frm     time_zone_name.frm         user.MYD
event.MYI         help_keyword.frm   host.MYD           proc.MYI              tables_priv.frm  time_zone_name.MYD         user.MYI
func.frm          help_keyword.MYD   host.MYI           procs_priv.frm        tables_priv.MYD  time_zone_name.MYI

test:
xl.frm  xl.MYD  xl.MYI

test2:
db.opt  test.frm  test.MYD  test.MYI

查看mysql数据库的错误日志路径：

mysql> show variables where variable_name = 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql错误日志文件内容大致如下：

[root@otherdb log]# tail -100 mysqld.log
221031 11:35:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
221031 11:35:42  InnoDB: Initializing buffer pool, size = 8.0M
221031 11:35:42  InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
221031 11:35:42  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
221031 11:35:42  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
221031 11:35:42  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
221031 11:35:42  InnoDB: Started; log sequence number 0 0
221031 11:35:42 [Note] Event Scheduler: Loaded 0 events
221031 11:35:42 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.71'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
221031 13:49:35 [Note] /usr/libexec/mysqld: Normal shutdown

221031 13:49:35 [Note] Event Scheduler: Purging the queue. 0 events
221031 13:49:35  InnoDB: Starting shutdown...
221031 13:49:35  InnoDB: Shutdown completed; log sequence number 0 44233
221031 13:49:35 [Note] /usr/libexec/mysqld: Shutdown complete

221031 13:49:35 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
221031 13:49:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
221031 13:49:41  InnoDB: Initializing buffer pool, size = 8.0M
221031 13:49:41  InnoDB: Completed initialization of buffer pool
221031 13:49:41  InnoDB: Started; log sequence number 0 44233
221031 13:49:41 [Note] Event Scheduler: Loaded 0 events
221031 13:49:41 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.71'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
221101  7:40:15 [Note] /usr/libexec/mysqld: Normal shutdown

221101  7:40:15 [Note] Event Scheduler: Purging the queue. 0 events
221101  7:40:15  InnoDB: Starting shutdown...
221101  7:40:16  InnoDB: Shutdown completed; log sequence number 0 44233
221101  7:40:16 [Note] /usr/libexec/mysqld: Shutdown complete

221101 07:40:16 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
221101 22:02:10 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
221101 22:02:10  InnoDB: Initializing buffer pool, size = 8.0M
221101 22:02:10  InnoDB: Completed initialization of buffer pool
221101 22:02:10  InnoDB: Started; log sequence number 0 44233
221101 22:02:10 [Note] Event Scheduler: Loaded 0 events
221101 22:02:10 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.71'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution