To skip or not to skip replication errors

Replication in MySQL is the standard way high availablity is achieved. Ineveitable, replication will break because of data inconsistency problems between the master and replicas.

Deciding what to do or what not to do when these errors happen is typically the DBA job. Sometimes, the error is not important to the application or the data and can be skipped. I onced worked with an application that would failover to a secondary master when it reached maxed connections and this would always result in the data inconsisties. The primary and secondary master were using master-master replication but the application would only write to one master at a time. The old primary master would not get removed from the cluster when the switchover happened and there would always be data differnces because of the way the application wrote data. For that particular application, I wrote a script that was monitoring replication and would skip the error and restart. It would do this all day long until the work day was over and we could schedule a planned swithcover and clean up the data.


Instead of writing a custom script to do this, MySQL has this option built in. There is an entry in the my.cnf called "slave-skip-errors" which can be used to automatically skip specific errors. For example, if you have data on the master that for whatever reason doesn't exist on the slave or vice versa, such as an insert that succeeded on the master but failed on the slave because that row already existed, or a DELETE on the master that succeeded but fails on the slave, those error numbers can be automatically skipped.

There is some risk involved here because the real problem that needs to be addressed is why is there missing data between master and slave? The root problem of the data inconsistiences really ought to be addressed.

I have one client that uses this in all of their my.cnf configs:

slave-skip-errors = 1032,1062

Some servers, this client also skips error 1452 and 1594.

I've also sometimes used this error number because it appears so many times:
1114

This site has a some great information on troubleshooting replication errors:

http://www.fromdual.com/replication-troubleshooting-classic-vs-gtid

Here is a related question and answer on stackexchange:

http://dba.stackexchange.com/questions/130366/which-error-codes-are-safe-for-slave-skip-errors-with-mysql-innodb

ERROR 1794 (HY000) at line 1: Slave is not configured or failed to initialize properly.

ERROR 1794 (HY000) at line 1: Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

I see this error when I am adding a new MySQL 5.6 slave to a MySQL 5.5 master. Before I get the error, I've used Xtrabackup to backup the MySQL 5.5 and restored onto the MySQL5.6 server without running mysql_upgrade on the MySQL 5.6.

The MySQL error log will have message like this before doing anything:

2016-10-27 12:31:28 24365 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
2016-10-27 12:31:28 24365 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
2016-10-27 12:31:28 24365 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
2016-10-27 12:31:28 24365 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure

I will drop the performance_schema like this:

mysql> drop database if exists performance_schema;
Query OK, 17 rows affected (0.08 sec)

Then run mysql_upgrade like this:

mysql_upgrade -uUSERNAME -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
<db_name>.<table_name>                         OK

OK

Then you can add it into the cluster:

STOP SLAVE;

RESET SLAVE;

CHANGE MASTER TO MASTER_HOST='<sever_name>',MASTER_USER='<replication_user>', MASTER_PASSWORD='<replication_passwor>', MASTER_LOG_FILE='mysqld-bin.000006', MASTER_LOG_POS=67549518;

START SLAVE;

show slave status \G 

Query to find orphaned priv on databases/tables/views/columns that have been dropped

MySQL will not remove permissions for databases, tables, views and columns even after those objects are removed.

However, MySQL will remove permissions for functions and procedures when those are moved. The following queries will find orphaned permissions that still remain even after dropping databases, tables, views and columns. I added in a check for functions and procedures even though it should not be needed. These queries will skip any databases that have wildcard characters (% or _).


SET @username = '%';
SELECT -- the list of privileges for a database
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
md.host `host`,
md.user `user`,
md.db `Database`,
'-' as `Tables and Views`,
'-' as `Tables and Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
TRIM(TRAILING ',' FROM(RTRIM(CONCAT(
IF(md.Select_priv = 'Y', 'Select, ', ''),
IF(md.Insert_priv = 'Y', 'Insert, ', ''),
IF(md.Update_priv = 'Y', 'Update, ', ''),
IF(md.Delete_priv = 'Y', 'Delete, ', ''),
IF(md.Create_priv = 'Y', 'Create, ', ''),
IF(md.Drop_priv = 'Y', 'Drop, ', ''),
IF(md.Grant_priv = 'Y', 'Grant, ', ''),
IF(md.References_priv = 'Y', 'References, ', ''),
IF(md.Index_priv = 'Y', 'Index, ', ''),
IF(md.Alter_priv = 'Y', 'Alter, ', ''),
IF(md.Create_tmp_table_priv = 'Y', 'CREATE TEMPORARY TABLES, ', ''),
IF(md.Lock_tables_priv = 'Y', 'LOCK TABLES, ', ''),
IF(md.Create_view_priv = 'Y', 'CREATE VIEW, ', ''),
IF(md.Show_view_priv = 'Y', 'SHOW VIEW, ', ''),
IF(md.Create_routine_priv = 'Y', 'CREATE ROUTINE, ', ''),
IF(md.Alter_routine_priv = 'Y', 'ALTER ROUTINE, ', ''),
IF(md.Execute_priv = 'Y', 'Execute, ', ''),
IF(md.Event_priv = 'Y', 'Event, ', ''),
IF(md.Trigger_priv = 'Y', 'Trigger, ', '')
))))  AS `Privileges`
FROM
mysql.db md
WHERE user LIKE CONCAT('%', @username)
AND md.db NOT LIKE '%\%%'
AND md.db NOT LIKE '%\_%'
AND md.user <> ''
AND md.db NOT IN
(SELECT
DISTINCT
s.SCHEMA_NAME `schemas`
FROM
information_schema.SCHEMATA AS s
)
UNION
SELECT -- the list of privileges for tables
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mt.host `host`,
mt.user `user`,
'-' as `Database`,
CONCAT(mt.Db, '.', mt.Table_name) `Tables and Views`,
'-' as `Tables and View Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mt
WHERE
user LIKE CONCAT('%', @username)
AND
CONCAT(mt.db,'.',mt.Table_name) NOT IN
(SELECT
DISTINCT CONCAT(t.table_schema,'.',t.table_name) as `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)
UNION
SELECT -- the list of privileges for table columns
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mtc.host `host`,
mtc.user `user`,
'-' as `Database`,
'-' as `Tables`,
CONCAT(mtc.Db, '.', mtc.Table_name, '.', mtc.Column_name) `Tables and View Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mtc.Column_priv, ',', ', ') AS `Privileges`
FROM
mysql.columns_priv mtc
WHERE
user LIKE CONCAT('%', @username)
AND mtc.Table_name NOT IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)
UNION
SELECT -- the list of privileges for procedures
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mp.host `host`,
mp.user `user`,
'-' as `Database`,
'-' as `Tables`,
'-' as `Tables Columns`,
CONCAT(mp.Db, '.', mp.Routine_name) `Procedures`,
'-' as `Functions`,
REPLACE(mp.Proc_priv, ',', ', ') AS `Privileges`
FROM
mysql.procs_priv mp
WHERE
mp.Routine_type = 'PROCEDURE'
AND user LIKE CONCAT('%', @username)
AND mp.Routine_name NOT IN
(SELECT
DISTINCT
p.name `proc`
FROM
mysql.proc AS p
WHERE type='PROCEDURE'
)
UNION
SELECT -- the list of privileges for functions
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mf.host `host`,
mf.user `user`,
'-' as `Database`,
'-' as `Tables`,
'-' as `Tables Columns`,
'-' as `Procedures`,
CONCAT(mf.Db, '.', mf.Routine_name) `Functions`,
REPLACE(mf.Proc_priv, ',', ', ') AS `Privileges`
FROM
mysql.procs_priv mf
WHERE
mf.Routine_type = 'FUNCTION'
AND user LIKE CONCAT('%', @username)
AND mf.Routine_name NOT IN
(SELECT
DISTINCT
p.name `func`
FROM
mysql.proc AS p
WHERE type='FUNCTION'
)
;

Useful queries to find data sizes in MySQL

Get the table size for all MyIASM tables and all INNODB tables, separated by data usage and index usage:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

Get the data size and index size and table size for a specific table in a specific database (need to change the schema and table name):

SET @DB_NAME = 'my_db';

SET @TABLE_NAME = 'my_table';

SELECT 

table_schema AS "Database",  

table_name AS "Table",   

ROUND(((data_length) / 1024 / 1024), 2) "Data in MB",  

ROUND(((index_length) / 1024 / 1024), 2) "Index in MB",   

ROUND(((data_length + index_length) / 1024 / 1024), 2) "Total in MB" 

FROM information_schema.TABLES WHERE table_schema = @DB_NAME AND table_name = @TABLE_NAME;

Get the data size for each schema on a MySQL server (may want to change from GB to MB by removing one of the 1024 when dividing the sum depending on the sizes of the schemas):

SELECT   table_schema AS "Database", 

         ROUND((SUM(index_length)/1024/1024/1024),2) AS "Index in GB",

         ROUND((SUM(data_length)/1024/1024/1024),2) AS "Data in GB",

         ROUND((SUM(data_length+index_length)/1024/1024/1024),2) AS "Total in GB"

FROM     information_schema.tables

WHERE table_schema NOT IN ('performance_schema','sys','information_schema', 'mysql')

GROUP BY table_schema 

ORDER BY 2 DESC;

Size of all tables that are InnoDB (running this can be very slow on systems with lots of tables)

SELECT CONCAT(db,'.',tb) as 'db_name.table_name' ,CONCAT(ROUND( tbsz/POWER(1024,IF(pw<0,0,IF(pw>4,4,pw))),3),' ', SUBSTR(' KMGT',IF(pw<0,0,IF(pw>4,4,pw))+1,1)) table_size FROM (SELECT data_length+index_length tbsz,table_schema db,table_name tb FROM information_schema.tables WHERE engine='InnoDB' AND table_schema NOT IN ('performance_schema','sys','information_schema', 'mysql')) A, (SELECT 3 pw) B;

Get the size of all tables in a specific schema with largest table first (need to change the schema name):

SET @DB_NAME = 'my_db';

SELECT table_schema as "Database", table_name AS "Table", 

ROUND(((index_length) / 1024 / 1024 / 1024), 2) "Index in GB",

ROUND(((data_length) / 1024 / 1024 / 1024), 2) "Data in GB",

ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) "Total in GB" 

FROM information_schema.TABLES WHERE table_schema = @DB_NAME ORDER BY (data_length + index_length) DESC;