In mysql5.6, ce_schemaconfigures the 、setup.txt file, which is used to back up the performance_schema configuration. This script generates an SQL statement to restore the setup table in performance_schema. -- Backup setup_instrumentsselectconcat (updateperformance_schema.setup_instrumentssete
MySQL 5.6 backs up and restores the performance_schema configuration file, which is used to back up the performance_schema configuration. This script generates an SQL statement to restore the setup table in performance_schema. -- Backup setup_instrumentsselect concat ('Update performance_schema.setup_instruments set e
Back up and restore performance_schema configuration in MySQL 5.6
The one-stop setup.txt file is used to back up the configuration of performance_schema. This script generates an SQL statement to restore the setup table in performance_schema.
-- Backup setup_instrumentsselect concat ('Update performance_schema.setup_instruments set enabled = ''', enabled, ''' where name = ''', name ,''';') from performance_schema.setup_instruments where enabled = 'no'; -- backup setup_consumersselect concat ('Update replica set enabled = ''', enabled, ''' where name = ''', name, '''; ') from performance_schema.setup_consumers where enabled = 'no'; -- commitselect 'commit ;';
Run the setup.txt file to back up the cece_schema configuration.
mysql -h localhost -P 3306 -u root -pmysql -D test -s < d:\atmp\backup_perf_setup.sql > d:\ATMP\restore_perf_setup.sql
3. Enable all setup configurations for monitoring.
update performance_schema.setup_instruments set enabled='YES' where enabled='NO';update performance_schema.setup_consumers set enabled='YES' where enabled='NO';commit;
4. After the monitoring is complete, restore the cece_schema configuration.
D:\software\mysql\mysql-5.6.14-win32\bin>mysql -h localhost -P 3306 -u root -pmysql -D test -s < d:\ATMP\restore_perf_setup.sql
5. Clear the monitoring table records of performance_schema.
SELECT CONCAT('truncate table performance_schema.',table_name,';')FROM information_schema.TABLESWHERE table_schema='performance_schema'AND table_name NOT LIKE 'setup%'AND table_name NOT LIKE '%instances'AND table_name NOT LIKE '%attrs'AND table_name NOT IN ('performance_timers', 'threads');