JuangaCovas.info

La página personal de Juan Gabriel Covas

Herramientas de usuario

Herramientas del sitio


linux:howtos:mariadb-104-mariabackup-partial-backup

Diferencias

Muestra las diferencias entre dos versiones de la página.

Enlace a la vista de comparación

Ambos lados, revisión anterior Revisión previa
linux:howtos:mariadb-104-mariabackup-partial-backup [16/03/2021 04:17]
Juanga Covas
linux:howtos:mariadb-104-mariabackup-partial-backup [09/09/2021 06:07] (actual)
Juanga Covas
Línea 17: Línea 17:
      
 Getting a list of InnoDB tables on a given "dbname", with the commands to "FORCE" (fix them) Getting a list of InnoDB tables on a given "dbname", with the commands to "FORCE" (fix them)
-  mysql --batch --silent --skip-column-names -e "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' LOCK=EXCLUSIVE, FORCE;') FROM information_schema.tables WHERE table_schema = 'dbame' AND engine = 'InnoDB'"+  mysql --batch --silent --skip-column-names -e 
 +  "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' LOCK=EXCLUSIVE, FORCE;') FROM information_schema.tables 
 +  WHERE table_schema = 'dbame' AND engine = 'InnoDB'"
  
 Passing the command list to mysql: Passing the command list to mysql:
-  mysql --batch --silent --skip-column-names -e "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' LOCK=EXCLUSIVE, FORCE;') FROM information_schema.tables WHERE table_schema = 'dbame' AND engine = 'InnoDB'" | mysql+  mysql --batch --silent --skip-column-names -e 
 +  "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' LOCK=EXCLUSIVE, FORCE;') FROM information_schema.tables 
 +  WHERE table_schema = 'dbame' AND engine = 'InnoDB'" | mysql
  
 Problem is that future ALTERs (while the bug is not solved), will again render the schema bugged for the "IMPORT TABLESPACE", so you can: Problem is that future ALTERs (while the bug is not solved), will again render the schema bugged for the "IMPORT TABLESPACE", so you can:
   * Enforce the use of '', ALGORITHM=copy'' on each dangerous ALTER statement.   * Enforce the use of '', ALGORITHM=copy'' on each dangerous ALTER statement.
   * or ''SET SESSION alter_algorithm=copy'' (see [[https://mariadb.com/kb/en/library/server-system-variables/#alter_algorithm|alter_algorithm]] system variable) before an ALTER that could cause this "schema mismatch" issue with MariaDB 10.4   * or ''SET SESSION alter_algorithm=copy'' (see [[https://mariadb.com/kb/en/library/server-system-variables/#alter_algorithm|alter_algorithm]] system variable) before an ALTER that could cause this "schema mismatch" issue with MariaDB 10.4
-  * or just setting ''alter_algorithm=copy'' on a [Mariadb-10.4] section of your ''server.cnf'' configuration (check that it survives a mariadb restart using ''SELECT @@alter_algorithm;''+  * or just setting ''alter_algorithm=copy'' on your ''server.cnf'' configuration (check that it survives a mariadb restart using ''SELECT @@alter_algorithm;'' 
 +  * <wrap tip>2020 - Also you can use</wrap> ''innodb_instant_alter_column_allowed=add_last'' in server config. as seen [[https://jira.mariadb.org/browse/MDEV-20590?focusedCommentId=148275&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-148275|here]] since an INSTANT ''add_last'' column works, the problematic ALTER changes are column ''drop'' and ''reorder'' (reorder being applied when adding a column which will not be the last). This new server variable is only valid starting with MariaDB 10.4.14, 10.5.3, and later versions
  
 The COPY algorithm is going to slow things down when doing ALTER on big tables, but you know... The COPY algorithm is going to slow things down when doing ALTER on big tables, but you know...
linux/howtos/mariadb-104-mariabackup-partial-backup.txt · Última modificación: 09/09/2021 06:07 por Juanga Covas