备份数据库
mysqldump 数据库名 >数据库备份名
mysqldump -A -u用户名 -p密码 数据库名>数据库备份名
mysqldump -d -A --add-drop-table -uroot -p >xxx.sql 1.导出结构不导出数据
mysqldump --opt -d 数据库名 -u root -p > xxx.sql2.导出数据不导出结构
mysqldump -t 数据库名 -uroot -p > xxx.sql 3.导出数据和表结构
mysqldump 数据库名 -uroot -p > xxx.sql4.导出特定表的结构：
mysqldump -uroot -p -B 数据库名 --table 表名 > xxx.sql 导入数据：
由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了：
mysql 数据库名 < 文件名
source /tmp/xxx.sqlmysqldump 导出数据，排除某张表：
就用 --ignore-table=dbname.tablename参数就行了。
mysqldump -uroot -ppass -h192.168.0.1 -P3306 dbname --ignore-table=dbname.dbtanles > dump.sqlMySQL查看所有数据库容量大小：
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
MySQL查看数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;查看指定数据库容量大小，如查看MySQL库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';mysql> select
-> table_schema as '数据库',
-> sum(table_rows) as '记录数',
-> sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
-> sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
-> from information_schema.tables
-> where table_schema='mysql';
+-----------+-----------+------------------+------------------+
| 数据库 | 记录数 | 数据容量(MB) | 索引容量(MB) |
+-----------+-----------+------------------+------------------+
| mysql | 3984 | 0.86 | 0.03 |
+-----------+-----------+------------------+------------------+
1 row in set (0.00 sec)
查看指定数据库各表容量大小，如查看zabbix库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='zabbix'
order by data_length desc, index_length desc;mysql> select
-> table_schema as '数据库',
-> table_name as '表名',
-> table_rows as '记录数',
-> truncate(data_length/1024/1024, 2) as '数据容量(MB)',
-> truncate(index_length/1024/1024, 2) as '索引容量(MB)'
-> from information_schema.tables
-> where table_schema='zabbix'
-> order by data_length desc, index_length desc;
+-----------+----------------------------+-----------+------------------+------------------+
| 数据库 | 表名 | 记录数 | 数据容量(MB) | 索引容量(MB) |
+-----------+----------------------------+-----------+------------------+------------------+
| zabbix | history_uint | 244869765 | 16248.54 | 7958.71 |
| zabbix | trends_uint | 96840266 | 6199.90 | 0.00 |
| zabbix | history | 18194040 | 1161.25 | 502.42 |
| zabbix | events | 3260019 | 227.51 | 1052.04 |
| zabbix | trends | 2874091 | 188.84 | 0.00 |
| zabbix | problem | 1568508 | 153.34 | 207.60 |
| zabbix | event_recovery | 1735507 | 104.51 | 120.81 |
| zabbix | history_str | 878318 | 84.10 | 30.21 |
| zabbix | items | 96265 | 55.59 | 22.03 |
| zabbix | triggers | 39069 | 19.45 | 4.45 |
| zabbix | alerts | 34101 | 16.54 | 11.06 |
| zabbix | auditlog | 70679 | 9.51 | 4.03 |
| zabbix | item_discovery | 84100 | 7.51 | 6.03 |
| zabbix | items_applications | 83968 | 6.51 | 6.03 |
| zabbix | functions | 109823 | 6.50 | 8.95 |
| zabbix | graphs_items | 52772 | 4.51 | 4.03 |
| zabbix | item_preproc | 84715 | 4.51 | 2.51 |
| zabbix | trigger_discovery | 30593 | 2.51 | 1.51 |
| zabbix | auditlog_details | 11989 | 2.51 | 0.25 |
| zabbix | images | 170 | 2.51 | 0.01 |
| zabbix | trigger_depends | 27807 | 1.51 | 2.96 |
| zabbix | graphs | 10007 | 1.51 | 2.01 |
| zabbix | graph_discovery | 8096 | 0.46 | 0.25 |
| zabbix | item_condition | 2446 | 0.20 | 0.07 |
| zabbix | host_inventory | 434 | 0.18 | 0.00 |
| zabbix | applications | 2528 | 0.17 | 0.15 |
| zabbix | hosts | 786 | 0.17 | 0.15 |
| zabbix | profiles | 1290 | 0.15 | 0.18 |
| zabbix | hosts_templates | 1730 | 0.15 | 0.15 |
| zabbix | interface | 1335 | 0.12 | 0.14 |
| zabbix | sysmaps_elements | 866 | 0.12 | 0.14 |
| zabbix | application_template | 1877 | 0.10 | 0.17 |
| zabbix | hosts_groups | 784 | 0.10 | 0.09 |
| zabbix | dservices | 717 | 0.07 | 0.06 |
| zabbix | widget_field | 543 | 0.06 | 0.09 |
| zabbix | sysmaps_links | 469 | 0.06 | 0.04 |
| zabbix | dhosts | 717 | 0.06 | 0.01 |
| zabbix | mappings | 661 | 0.06 | 0.01 |
| zabbix | sessions | 329 | 0.04 | 0.04 |
| zabbix | hostmacro | 286 | 0.04 | 0.01 |
| zabbix | sysmaps | 97 | 0.01 | 0.06 |
| zabbix | httptest | 0 | 0.01 | 0.06 |
| zabbix | scripts | 3 | 0.01 | 0.04 |
| zabbix | group_prototype | 7 | 0.01 | 0.04 |
| zabbix | acknowledges | 81 | 0.01 | 0.04 |
| zabbix | correlation | 0 | 0.01 | 0.03 |
| zabbix | slideshow_usrgrp | 0 | 0.01 | 0.03 |
| zabbix | problem_tag | 0 | 0.01 | 0.03 |
| zabbix | icon_mapping | 0 | 0.01 | 0.03 |
| zabbix | screen_usrgrp | 0 | 0.01 | 0.03 |
| zabbix | host_discovery | 2 | 0.01 | 0.03 |
| zabbix | users_groups | 14 | 0.01 | 0.03 |
| zabbix | maintenances | 4 | 0.01 | 0.03 |
| zabbix | slideshow_user | 0 | 0.01 | 0.03 |
| zabbix | sysmap_usrgrp | 38 | 0.01 | 0.03 |
| zabbix | icon_map | 0 | 0.01 | 0.03 |
| zabbix | screen_user | 0 | 0.01 | 0.03 |
| zabbix | slides | 0 | 0.01 | 0.03 |
| zabbix | sysmap_user | 0 | 0.01 | 0.03 |
| zabbix | httptestitem | 3 | 0.01 | 0.03 |
| zabbix | opcommand_hst | 0 | 0.01 | 0.03 |
| zabbix | optemplate | 0 | 0.01 | 0.03 |
| zabbix | opcommand_grp | 0 | 0.01 | 0.03 |
| zabbix | opmessage_usr | 23 | 0.01 | 0.03 |
| zabbix | drules | 2 | 0.01 | 0.03 |
| zabbix | opmessage_grp | 3 | 0.01 | 0.03 |
| zabbix | rights | 139 | 0.01 | 0.03 |
| zabbix | services_links | 0 | 0.01 | 0.03 |
| zabbix | httpstepitem | 3 | 0.01 | 0.03 |
| zabbix | item_application_prototype | 0 | 0.01 | 0.03 |
| zabbix | application_prototype | 0 | 0.01 | 0.03 |
| zabbix | sysmap_element_trigger | 0 | 0.01 | 0.03 |
| zabbix | media | 9 | 0.01 | 0.03 |
| zabbix | task | 30 | 0.01 | 0.03 |
| zabbix | service_alarms | 0 | 0.01 | 0.03 |
| zabbix | application_discovery | 0 | 0.01 | 0.03 |
| zabbix | opgroup | 0 | 0.01 | 0.03 |
| zabbix | dashboard_usrgrp | 2 | 0.01 | 0.03 |
| zabbix | maintenances_windows | 4 | 0.01 | 0.03 |
| zabbix | sysmaps_link_triggers | 0 | 0.01 | 0.03 |
| zabbix | dashboard_user | 0 | 0.01 | 0.03 |
| zabbix | actions | 13 | 0.01 | 0.03 |
| zabbix | maintenances_hosts | 25 | 0.01 | 0.03 |
| zabbix | slideshows | 0 | 0.01 | 0.03 |
| zabbix | config | 0 | 0.01 | 0.03 |
| zabbix | screens | 12 | 0.01 | 0.03 |
| zabbix | maintenances_groups | 55 | 0.01 | 0.03 |
| zabbix | conditions | 28 | 0.01 | 0.01 |
| zabbix | operations | 31 | 0.01 | 0.01 |
| zabbix | autoreg_host | 0 | 0.01 | 0.01 |
| zabbix | corr_operation | 0 | 0.01 | 0.01 |
| zabbix | opconditions | 0 | 0.01 | 0.01 |
| zabbix | users | 14 | 0.01 | 0.01 |
| zabbix | escalations | 2 | 0.01 | 0.01 |
| zabbix | graph_theme | 2 | 0.01 | 0.01 |
| zabbix | history_text | 0 | 0.01 | 0.01 |
| zabbix | sysmap_url | 0 | 0.01 | 0.01 |
| zabbix | httpstep_field | 0 | 0.01 | 0.01 |
| zabbix | httptest_field | 0 | 0.01 | 0.01 |
| zabbix | trigger_tag | 0 | 0.01 | 0.01 |
| zabbix | sysmap_shape | 30 | 0.01 | 0.01 |
| zabbix | services_times | 0 | 0.01 | 0.01 |
| zabbix | httpstep | 0 | 0.01 | 0.01 |
| zabbix | opcommand | 0 | 0.01 | 0.01 |
| zabbix | history_log | 0 | 0.01 | 0.01 |
| zabbix | sysmap_element_url | 2 | 0.01 | 0.01 |
| zabbix | globalmacro | 0 | 0.01 | 0.01 |
| zabbix | opmessage | 29 | 0.01 | 0.01 |
| zabbix | media_type | 9 | 0.01 | 0.01 |
| zabbix | dchecks | 2 | 0.01 | 0.01 |
| zabbix | services | 0 | 0.01 | 0.01 |
| zabbix | regexps | 5 | 0.01 | 0.01 |
| zabbix | interface_discovery | 0 | 0.01 | 0.01 |
| zabbix | expressions | 10 | 0.01 | 0.01 |
| zabbix | proxy_history | 0 | 0.01 | 0.01 |
| zabbix | groups | 44 | 0.01 | 0.01 |
| zabbix | corr_condition_group | 0 | 0.01 | 0.01 |
| zabbix | widget | 26 | 0.01 | 0.01 |
| zabbix | proxy_dhistory | 0 | 0.01 | 0.01 |
| zabbix | corr_condition | 0 | 0.01 | 0.01 |
| zabbix | screens_items | 50 | 0.01 | 0.01 |
| zabbix | valuemaps | 58 | 0.01 | 0.01 |
| zabbix | proxy_autoreg_host | 0 | 0.01 | 0.01 |
| zabbix | event_tag | 0 | 0.01 | 0.01 |
| zabbix | dashboard | 3 | 0.01 | 0.01 |
| zabbix | group_discovery | 0 | 0.01 | 0.01 |
| zabbix | usrgrp | 7 | 0.01 | 0.01 |
| zabbix | corr_condition_tagvalue | 0 | 0.01 | 0.00 |
| zabbix | corr_condition_tagpair | 0 | 0.01 | 0.00 |
| zabbix | task_remote_command | 0 | 0.01 | 0.00 |
| zabbix | corr_condition_tag | 0 | 0.01 | 0.00 |
| zabbix | globalvars | 0 | 0.01 | 0.00 |
| zabbix | task_close_problem | 0 | 0.01 | 0.00 |
| zabbix | housekeeper | 0 | 0.01 | 0.00 |
| zabbix | task_acknowledge | 30 | 0.01 | 0.00 |
| zabbix | opinventory | 0 | 0.01 | 0.00 |
| zabbix | dbversion | 0 | 0.01 | 0.00 |
| zabbix | timeperiods | 4 | 0.01 | 0.00 |
| zabbix | task_remote_command_result | 0 | 0.01 | 0.00 |
| zabbix | ids | 59 | 0.01 | 0.00 |
+-----------+----------------------------+-----------+------------------+------------------+
140 rows in set (0.01 sec)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
