Tuesday, August 13, 2019

.::: Repair Table & Database MySQL / MariaDB :::.


1. Repair table (sample graph_templates_graph ;)

use databasename
repair table tablename

mysql> use cacti;
Database changed
mysql>
mysql> repair table graph_templates_graph ;
+-----------------------------+--------+----------+----------+
| Table                       | Op     | Msg_type | Msg_text |
+-----------------------------+--------+----------+----------+
| cacti.graph_templates_graph | repair | status   | OK       |
+-----------------------------+--------+----------+----------+
1 row in set (0.06 sec)

mysql>

2. Repair all table database

All Database
mysqlcheck -u root -p --all-databases -a #analyze
mysqlcheck -u root -p --all-databases -r #repair
mysqlcheck -u root -p --all-databases -o #optimize
mysqlcheck -u root -p --auto-repair --check --all-databases

Sample Database (cacti)
mysqlcheck -u root -p cacti -a #analyze
mysqlcheck -u root -p cacti -r #repair
mysqlcheck -u root -p cacti -o #optimize
mysqlcheck -u root -p --auto-repair --check cacti


[root@server ~]# mysqlcheck -u root -p cacti -a #analyze ;
Enter password:
cacti.cdef                                         OK
cacti.cdef_items                                   OK
cacti.colors                                       OK
cacti.data_input                                   OK
cacti.data_input_data                              OK
cacti.data_input_fields                            OK
cacti.data_local                                   OK
cacti.data_template                                OK
cacti.data_template_data                           OK
cacti.data_template_data_rra                       OK
cacti.data_template_rrd                            OK
cacti.graph_local                                  OK
cacti.graph_template_input                         OK
cacti.graph_template_input_defs                    OK
cacti.graph_templates                              OK
cacti.graph_templates_gprint                       OK
cacti.graph_templates_graph                        Table is already up to date
cacti.graph_templates_item                         OK
cacti.graph_tree                                   OK
cacti.graph_tree_items                             OK
cacti.host                                         OK
cacti.host_graph                                   OK
cacti.host_snmp_cache                              OK
cacti.host_snmp_query                              OK
cacti.host_template                                OK
cacti.host_template_graph                          OK
cacti.host_template_snmp_query                     OK
cacti.manage_admin_link                            OK
cacti.manage_alerts                                Table is already up to date
cacti.manage_groups                                OK
cacti.manage_host                                  Table is already up to date
cacti.manage_poller_output                         Table is already up to date
cacti.manage_process                               Table is already up to date
cacti.manage_services                              Table is already up to date
cacti.manage_sites                                 Table is already up to date
cacti.manage_tcp                                   Table is already up to date
cacti.manage_templates                             OK
cacti.manage_uptime_method                         Table is already up to date
cacti.plugin_config                                OK
cacti.plugin_db_changes                            OK
cacti.plugin_docs                                  Table is already up to date
cacti.plugin_hooks                                 OK
cacti.plugin_mikrotik_processes
note     : The storage engine for the table doesn't support analyze
cacti.plugin_mikrotik_processor                    Table is already up to date
cacti.plugin_mikrotik_storage                      Table is already up to date
cacti.plugin_mikrotik_system                       Table is already up to date
cacti.plugin_mikrotik_trees                        Table is already up to date
cacti.plugin_mikrotik_users                        Table is already up to date
cacti.plugin_realms                                OK
cacti.poller                                       Table is already up to date
cacti.poller_command                               OK
cacti.poller_item                                  OK
cacti.poller_output                                OK
cacti.poller_output_rt                             Table is already up to date
cacti.poller_reindex                               OK
cacti.poller_time                                  OK
cacti.rra                                          OK
cacti.rra_cf                                       OK
cacti.settings                                     OK
cacti.settings_graphs                              Table is already up to date
cacti.settings_tree                                Table is already up to date
cacti.snmp_query                                   OK
cacti.snmp_query_graph                             OK
cacti.snmp_query_graph_rrd                         OK
cacti.snmp_query_graph_rrd_sv                      OK
cacti.snmp_query_graph_sv                          OK
cacti.user_auth                                    OK
cacti.user_auth_perms                              Table is already up to date
cacti.user_auth_realm                              OK
cacti.user_log                                     OK
cacti.version                                      OK
cacti.weathermap_auth                              Table is already up to date
cacti.weathermap_data                              Table is already up to date
cacti.weathermap_groups                            OK
cacti.weathermap_maps                              Table is already up to date
cacti.weathermap_settings                          Table is already up to date
[root@server ~]#

[root@server ~]# mysqlcheck -u root -p cacti -r #repair;
Enter password:
cacti.cdef                                         OK
cacti.cdef_items                                   OK
cacti.colors                                       OK
cacti.data_input                                   OK
cacti.data_input_data                              OK
cacti.data_input_fields                            OK
cacti.data_local                                   OK
cacti.data_template                                OK
cacti.data_template_data                           OK
cacti.data_template_data_rra                       OK
cacti.data_template_rrd                            OK
cacti.graph_local                                  OK
cacti.graph_template_input                         OK
cacti.graph_template_input_defs                    OK
cacti.graph_templates                              OK
cacti.graph_templates_gprint                       OK
cacti.graph_templates_graph                        OK
cacti.graph_templates_item                         OK
cacti.graph_tree                                   OK
cacti.graph_tree_items                             OK
cacti.host                                         OK
cacti.host_graph                                   OK
cacti.host_snmp_cache                              OK
cacti.host_snmp_query                              OK
cacti.host_template                                OK
cacti.host_template_graph                          OK
cacti.host_template_snmp_query                     OK
cacti.manage_admin_link                            OK
cacti.manage_alerts                                OK
cacti.manage_groups                                OK
cacti.manage_host                                  OK
cacti.manage_poller_output                         OK
cacti.manage_process                               OK
cacti.manage_services                              OK
cacti.manage_sites                                 OK
cacti.manage_tcp                                   OK
cacti.manage_templates                             OK
cacti.manage_uptime_method                         OK
cacti.plugin_config                                OK
cacti.plugin_db_changes                            OK
cacti.plugin_docs                                  OK
cacti.plugin_hooks                                 OK
cacti.plugin_mikrotik_processes
note     : The storage engine for the table doesn't support repair
cacti.plugin_mikrotik_processor                    OK
cacti.plugin_mikrotik_storage                      OK
cacti.plugin_mikrotik_system                       OK
cacti.plugin_mikrotik_trees                        OK
cacti.plugin_mikrotik_users                        OK
cacti.plugin_realms                                OK
cacti.poller                                       OK
cacti.poller_command                               OK
cacti.poller_item                                  OK
cacti.poller_output                                OK
cacti.poller_output_rt                             OK
cacti.poller_reindex                               OK
cacti.poller_time                                  OK
cacti.rra                                          OK
cacti.rra_cf                                       OK
cacti.settings                                     OK
cacti.settings_graphs                              OK
cacti.settings_tree                                OK
cacti.snmp_query                                   OK
cacti.snmp_query_graph                             OK
cacti.snmp_query_graph_rrd                         OK
cacti.snmp_query_graph_rrd_sv                      OK
cacti.snmp_query_graph_sv                          OK
cacti.user_auth                                    OK
cacti.user_auth_perms                              OK
cacti.user_auth_realm                              OK
cacti.user_log                                     OK
cacti.version                                      OK
cacti.weathermap_auth                              OK
cacti.weathermap_data                              OK
cacti.weathermap_groups                            OK
cacti.weathermap_maps                              OK
cacti.weathermap_settings                          OK
[root@server ~]#

[root@server ~]# mysqlcheck -u root -p cacti -o #optimize ;
Enter password:
cacti.cdef                                         OK
cacti.cdef_items                                   OK
cacti.colors                                       OK
cacti.data_input                                   OK
cacti.data_input_data                              OK
cacti.data_input_fields                            OK
cacti.data_local                                   OK
cacti.data_template                                OK
cacti.data_template_data                           OK
cacti.data_template_data_rra                       OK
cacti.data_template_rrd                            OK
cacti.graph_local                                  OK
cacti.graph_template_input                         OK
cacti.graph_template_input_defs                    OK
cacti.graph_templates                              OK
cacti.graph_templates_gprint                       OK
cacti.graph_templates_graph                        OK
cacti.graph_templates_item                         OK
cacti.graph_tree                                   OK
cacti.graph_tree_items                             OK
cacti.host                                         OK
cacti.host_graph                                   OK
cacti.host_snmp_cache                              OK
cacti.host_snmp_query                              OK
cacti.host_template                                OK
cacti.host_template_graph                          OK
cacti.host_template_snmp_query                     OK
cacti.manage_admin_link                            OK
cacti.manage_alerts                                OK
cacti.manage_groups                                OK
cacti.manage_host                                  OK
cacti.manage_poller_output                         OK
cacti.manage_process                               OK
cacti.manage_services                              OK
cacti.manage_sites                                 OK
cacti.manage_tcp                                   OK
cacti.manage_templates                             OK
cacti.manage_uptime_method                         OK
cacti.plugin_config                                OK
cacti.plugin_db_changes                            OK
cacti.plugin_docs                                  OK
cacti.plugin_hooks                                 OK
cacti.plugin_mikrotik_processes
note     : The storage engine for the table doesn't support optimize
cacti.plugin_mikrotik_processor                    OK
cacti.plugin_mikrotik_storage                      OK
cacti.plugin_mikrotik_system                       OK
cacti.plugin_mikrotik_trees                        OK
cacti.plugin_mikrotik_users                        OK
cacti.plugin_realms                                OK
cacti.poller                                       OK
cacti.poller_command                               OK
cacti.poller_item                                  OK
cacti.poller_output                                OK
cacti.poller_output_rt                             OK
cacti.poller_reindex                               OK
cacti.poller_time                                  OK
cacti.rra                                          OK
cacti.rra_cf                                       OK
cacti.settings                                     OK
cacti.settings_graphs                              OK
cacti.settings_tree                                OK
cacti.snmp_query                                   OK
cacti.snmp_query_graph                             OK
cacti.snmp_query_graph_rrd                         OK
cacti.snmp_query_graph_rrd_sv                      OK
cacti.snmp_query_graph_sv                          OK
cacti.user_auth                                    OK
cacti.user_auth_perms                              OK
cacti.user_auth_realm                              OK
cacti.user_log                                     OK
cacti.version                                      OK
cacti.weathermap_auth                              OK
cacti.weathermap_data                              OK
cacti.weathermap_groups                            OK
cacti.weathermap_maps                              OK
cacti.weathermap_settings                          OK
[root@server ~]#

No comments:

Post a Comment

Popular Posts