performance_schema table global_variables. The 5.7 list has 505 items, the 8.0 list has 620. We need a user-friendly display of these differences.
Percona has a tool that does it in Linux. Can we get it done for any operating system using just MySQL? Yes ....
1. Setup variables and parameters
2. Import the performance_schema.global_variables table from one of the servers to the other
3. Run a full outer join on the two lists, excluding optimizer_switch and sql_mode which are themselves lists that will need to be broken out into separate displays
4. Run full outer joins on optimizer_switch and sql_mode to list those items
1. Setup
If the MySQL server where you'll be doing this doesn't yet have a utility database for such jobs and tables, create it. Ours is namedsystem, so we issue ...
create schema if not exists system; alter schema system character set utf8mb4; use system; set names 'utf8mb4';We'll use the names of the local and remote servers to label columns in the difference lists, for now call them
local and remote ...
set @local_srvr='local', @remote_srvr='remote';We have two ways of getting a remote server's global variables onto a local server so we can run comparison queries---create a local Federated
global_variables table linked to the global_variables table in the remote server's performance_schema, or import the global_variabes table from the remote server's performance_schema via a dump. Either way, we'll be querying two tables ...performance_schema.global_variablesfor local global variables- a
global_variablestable in the current database, imported from or linked to the remote server
set @local_tbl='performance_schema.global_variables',
@remote_tbl='global_variables';
And we'll need a small stored procedure to run prepared queries ...
drop procedure if exists pquery; delimiter go create procedure pquery( psql text ) begin set @sql = psql; prepare stmt from @sql; execute stmt; drop prepare stmt; end; go delimiter ;
2. Import global_variables from the remote server
If the Federated storage engine is enabled on one of the servers, it offers the simplest way to get the two configuration lists into one place for querying. Ifshow engines shows it's not enabled, edit my.cnf/ini to add this line in the [mysqld] section of the file ...
[mysqld] ... federated ...... restart the server, then check that
show engines now says it's enabled.
To use the Federated engine, CREATE a SERVER, then reference it in SQL that creates the desired Federated table. CREATE SERVER accepts only literal values; the server name must not be quoted, but user, host and password values must. You'll need to enter the actual name of the remote MySQL server as the HOST value, and of course your values for USR and PWD ...
drop server if exists HOST;
create server HOST
foreign data wrapper mysql
options ( user 'USR', host 'HOST', password 'PWD',
database 'performance_schema'
);
drop table if exists global_variables;
create table global_variables (
variable_name varchar(64) not null,
variable_value varchar(1024) default null,
primary key(variable_name)
) engine=federated
default charset='utf8mb4' collate='utf8mb4_unicode_520_ci'
connection='HOST/global_variables';
select count(*) from global_variables;
Charset utf8mb4 and collation utf8mb4_unicode_520_ci help avoid collation errors because both 5.7 and 8.0 support them, but if your systems depend on different charsets & collations, you'll need to find a charset and collation that both your servers understand. If the count result isn't 505 from a 5.7 server or 620 from an 8.0 server, something's not right---you'll need to find the error.
If the Federated engine can't be enabled on one of your servers, dump performance_schema.global_variables from one server to the other, e.g., ...
mysqldump -uUSR -pPWD -hHOST performance_schema global_variables... again substituting your values for
USR, PWD, HOST, then import the dump to the database of the server where you'll be running the config comparison.
3. Compare global_variables
Here is a stored procedure to display differences inglobal_variables, skipping optimizer_switch, sql_mode ...
drop procedure if exists compare_server_vars;
delimiter go
create procedure compare_server_vars(
plocal_tbl varchar(128), plocal_srvr varchar(32),
premote_tbl varchar(128), premote_srvr varchar(32)
)
begin
-- AVOID MYSQL TEMP TABLE MULTI--REFERENCE LIMITATION
drop temporary table if exists r1,r2;
set @sql = concat(
"create temporary table r1 ",
"select variable_name,a.variable_value as aval,b.variable_value as bval from ",
plocal_tbl, " a left join ", premote_tbl, " b using(variable_name) ",
"where variable_name not in('optimizer_switch','sql_mode')"
);
call pquery(@sql);
set @sql = replace( @sql, "r1", "r2" );
set @sql = replace( @sql, "left join", "right join" );
call pquery(@sql);
set @sql =
concat( "select variable_name, aval as ",
plocal_srvr,"_value, bval as ",premote_srvr,"_value ",
"from (select * from r1 union select * from r2 ) x ",
"where aval is null or bval is null or aval<>bval order by 1"
);
call pquery( @sql );
end;
go
delimiter ;
To show all value pairs, not just the differences, comment out the WHERE clause.
4a. Compare optimizer_switch settings
A procedure to fetch and displayoptimizer_switch settings from the local and remote servers:
drop procedure if exists show_optimizer_switches;
delimiter go
create procedure show_optimizer_switches(
plocal_srvr varchar(128), premote_srvr varchar(128)
)
begin
-- COLLECT DATA IN 2 TEMP TABLES
drop temporary table if exists os1, os2;
create temporary table os1(
switch varchar(64) character set utf8mb4 collate utf8mb4_unicode_520_ci
);
create temporary table os2 like os1;
set @s = (
select replace( variable_value, ",", "'),('" )
from performance_schema.global_variables
where variable_name='optimizer_switch'
);
set @s = concat( "('", @s, "')" );
call pquery( concat( "insert into os1 values", @s ) );
set @s = (
select replace( variable_value, ",", "'),('" )
from global_variables
where variable_name='optimizer_switch'
);
set @s = concat( "('", @s, "')" );
call pquery( concat( "insert into os2 values", @s ) );
-- NEED TEMP TABLES B/O MYSQL LIMITATION THAT
-- QUERIES MAY REFERENCE TEMP TABLES ONLY ONCE
drop temporary tables if exists osleft,osright;
create temporary table osleft
select os1.switch as os1_switch, os2.switch as os2_switch
from os1
left join os2 on
left(os1.switch,locate('=',os1.switch)-1) =
left(os2.switch,locate('=',os2.switch)-1);
create temporary table osright
select os1.switch as os1_switch, os2.switch as os2_switch
from os1
right join os2 on
left(os1.switch,locate('=',os1.switch)-1) =
left(os2.switch,locate('=',os2.switch)-1);
-- FORM AND RUN THE QUERY
set @sql = concat( "
select
os1_switch as '", plocal_srvr, " optimizer_switches',
os2_switch as '", premote_srvr, " optimizer_switches'
from (
select os1_switch, os2_switch from osleft
union
select os1_switch, os2_switch from osright
order by ifnull(os1_switch,concat('zzz',os2_switch)),os2_switch
) as x"
);
call pquery( @sql );
end;
go
delimiter ;
4b. Compare sql_modes
A procedure to fetch and displaysql_mode settings from the local and remote servers ...
drop procedure if exists show_sql_modes;
delimiter go
create procedure show_sql_modes( plocal_srvr varchar(128), premote_srvr varchar(128) )
begin
drop temporary table if exists sqm1, sqm2;
create temporary table sqm1(
item varchar(64) character set utf8mb4 collate utf8mb4_general_ci );
create temporary table sqm2 like sqm1;
set @s = ( select variable_value
from performance_schema.global_variables
where variable_name='sql_mode'
);
set @s = replace( @s, ",", "'),('" );
set @s = concat( "('", @s, "')" );
call pquery( concat( "insert into sqm1 values", @s ) );
set @s = ( select variable_value
from global_variables
where variable_name='sql_mode'
);
set @s = replace( @s, ",", "'),('" );
set @s = concat( "('", @s, "')" );
call pquery( concat( "insert into sqm2 values", @s ) );
drop temporary table if exists r1,r2;
create temporary table r1 charset utf8mb4 collate utf8mb4_general_ci
select sqm1.item as item1, sqm2.item as item2
from sqm1 left join sqm2 using(item);
create temporary table r2 charset utf8mb4 collate utf8mb4_general_ci
select sqm1.item as item1, sqm2.item as item2
from sqm1 right join sqm2 using(item);
set @sql =
"select item1 as 'sql_mode1', item2 as 'sql_mode2'
from (
select item1,item2 from r1
union
select item1,item2 from r2
) x
order by item1,item2";
set @sql = replace( @sql, 'sql_mode1' collate utf8mb4_unicode_520_ci,
concat(plocal_srvr collate utf8mb4_unicode_520_ci, ' sql mode')
);
set @sql = replace( @sql, 'sql_mode2' collate utf8mb4_unicode_520_ci,
concat(premote_srvr collate utf8mb4_unicode_520_ci,' sql mode')
);
call pquery( @sql );
end;
go
delimiter ;
Full Script
The script has three sections:SETUP, SPROCS, EXECUTE. To keep the federated
For example if you ran the script to compare the local server with srvr1, then with srvr2, copying the Federated tables respectively to srvr1_vars and srvr2_vars tables, you can now compare srvr1 and srvr2 variables by passing those table and server names directly to the comparison functions directly ...
call compare_server_vars( 'srvr1_vars', 'srvr1', 'srvr2_vars', 'srvr2' ); call show_optimizer_switches( 'srvr1', 'srvr2' ); call show_sql_modes( 'srvr1', 'srvr2' );The script ...
/*
1. SETUP
*/
create schema if not exists system;
alter schema system character set utf8mb4;
use system;
set names 'utf8mb4';
-- CUSTOMISE PARAM VALUES
set @local_srvr='local',
@remote_srvr='remote',
@local_tbl='performance_schema.global_variables',
@remote_tbl='global_variables';
/*
IMPORT PERFORMANCE_SCHEMA FROM REMOTE SERVER.
BEFORE EXECUTING THIS, ENABLE FEDERTED ENGINE IN MY.CNF/INI
AND FILL IN VALUES FOR HOST, USR, PWD
*/
drop server if exists HOST;
create server HOST
foreign data wrapper mysql
options ( user 'USR', host 'HOST', password 'PWD',
database 'performance_schema'
);
drop table if exists global_variables;
create table global_variables (
variable_name varchar(64) not null,
variable_value varchar(1024) default null,
primary key(variable_name)
) engine=federated
default charset='utf8mb4' collate='utf8mb4_unicode_520_ci'
connection='HOST/global_variables';
-- IF COUNT <> 505 FOR 5.7 OR 620 FOR 8.0, AN ERROR HAS OCCURRED
select count(*) from global_variables;
-- iF FEDERATED NOT AVAILABLE, EXECUTE ...
-- mysqldump -uUSR -pPWD -hHOST performance_schema global_variables
/*
2. SPROCS
*/
-- SPROC TO RUN PREPARED QUERY
drop procedure if exists pquery;
delimiter go
create procedure pquery( psql text )
begin
set @sql = psql;
prepare stmt from @sql;
execute stmt;
drop prepare stmt;
end;
go
delimiter ;
-- SPROC TO COMPARE GLOBAL VARS
drop procedure if exists compare_server_vars;
delimiter go
create procedure compare_server_vars(
plocal_tbl varchar(128), plocal_srvr varchar(32),
premote_tbl varchar(128), premote_srvr varchar(32)
)
begin
drop temporary table if exists r1,r2;
set @sql = concat(
"create temporary table r1 ",
"select variable_name,a.variable_value as aval,b.variable_value as bval from ",
plocal_tbl, " a left join ", premote_tbl, " b using(variable_name) ",
"where variable_name not in('optimizer_switch','sql_mode')"
);
call pquery(@sql);
set @sql = replace( @sql, "r1", "r2" );
set @sql = replace( @sql, "left join", "right join" );
call pquery(@sql);
set @sql =
concat( "select variable_name, aval as ",
plocal_srvr,"_value, bval as ",premote_srvr,"_value ",
"from (select * from r1 union select * from r2 ) x ",
"where aval is null or bval is null or aval<>bval order by 1"
);
call pquery( @sql );
end;
go
delimiter ;
-- SPROC TO COMPARE OPTIMIZER_SWITCH SETTINGS
drop procedure if exists show_optimizer_switches;
delimiter go
create procedure show_optimizer_switches(
plocal_srvr varchar(128), premote_srvr varchar(128)
)
begin
-- COLLECT DATA IN 2 TEMP TABLES
drop temporary table if exists os1, os2;
create temporary table os1(
switch varchar(64) character set utf8mb4 collate utf8mb4_unicode_520_ci
);
create temporary table os2 like os1;
set @s = (
select replace( variable_value, ",", "'),('" )
from performance_schema.global_variables
where variable_name='optimizer_switch'
);
set @s = concat( "('", @s, "')" );
call pquery( concat( "insert into os1 values", @s ) );
set @s = (
select replace( variable_value, ",", "'),('" )
from global_variables
where variable_name='optimizer_switch'
);
set @s = concat( "('", @s, "')" );
call pquery( concat( "insert into os2 values", @s ) );
-- NEED TEMP TABLES B/O MYSQL LIMITATION THAT
-- QUERIES MAY REFERENCE TEMP TABLES ONLY ONCE
drop temporary tables if exists osleft,osright;
create temporary table osleft
select os1.switch as os1_switch, os2.switch as os2_switch
from os1
left join os2 on
left(os1.switch,locate('=',os1.switch)-1) =
left(os2.switch,locate('=',os2.switch)-1);
create temporary table osright
select os1.switch as os1_switch, os2.switch as os2_switch
from os1
right join os2 on
left(os1.switch,locate('=',os1.switch)-1) =
left(os2.switch,locate('=',os2.switch)-1);
-- FORM AND RUN THE QUERY
set @sql = concat( "
select
os1_switch as '", plocal_srvr, " optimizer_switches',
os2_switch as '", premote_srvr, " optimizer_switches'
from (
select os1_switch, os2_switch from osleft
union
select os1_switch, os2_switch from osright
order by ifnull(os1_switch,concat('zzz',os2_switch)),os2_switch
) as x"
);
call pquery( @sql );
end;
go
delimiter ;
-- SPROC TO COMPARE SQL_MODES
drop procedure if exists show_sql_modes;
delimiter go
create procedure show_sql_modes( plocal_srvr varchar(128), premote_srvr varchar(128) )
begin
drop temporary table if exists sqm1, sqm2;
create temporary table sqm1(
item varchar(64) character set utf8mb4 collate utf8mb4_general_ci
);
create temporary table sqm2 like sqm1;
set @s = ( select variable_value
from performance_schema.global_variables
where variable_name='sql_mode'
);
set @s = replace( @s, ",", "'),('" );
set @s = concat( "('", @s, "')" );
call pquery( concat( "insert into sqm1 values", @s ) );
set @s = ( select variable_value from global_variables where variable_name='sql_mode' );
set @s = replace( @s, ",", "'),('" );
set @s = concat( "('", @s, "')" );
call pquery( concat( "insert into sqm2 values", @s ) );
drop table if exists r1,r2;
create temporary table r1 charset utf8mb4 collate utf8mb4_general_ci
select sqm1.item as item1, sqm2.item as item2
from sqm1 left join sqm2 using(item);
create temporary table r2 charset utf8mb4 collate utf8mb4_general_ci
select sqm1.item as item1, sqm2.item as item2
from sqm1 right join sqm2 using(item);
set @sql =
"select item1 as 'sql_mode1', item2 as 'sql_mode2'
from (
select item1,item2 from r1
union
select item1,item2 from r2
) x
order by item1,item2";
set @sql = replace( @sql, 'sql_mode1' collate utf8mb4_unicode_520_ci,
concat(plocal_srvr collate utf8mb4_unicode_520_ci, ' sql mode')
);
set @sql = replace( @sql, 'sql_mode2' collate utf8mb4_unicode_520_ci,
concat(premote_srvr collate utf8mb4_unicode_520_ci,' sql mode')
);
call pquery( @sql );
end;
go
delimiter ;
/*
3. EXECUTE
*/
call compare_server_vars( @local_tbl, @local_srvr, @remote_tbl, @remote_srvr );
call show_optimizer_switches( @local_srvr, @remote_srvr );
call show_sql_modes( @local_srvr, @remote_srvr );
Last updated 19 Nov 2024
