Mariadb 10.3序列对象的导出导入、查看及备份恢复问题咨询

Hey there! Let's tackle your questions about MariaDB 10.3+ sequence objects—since XtraBackup treats them as tables (which breaks their functionality post-restore), using proper export/import methods is key. Here's how to handle each task:

MariaDB stores sequence metadata in the information_schema.SEQUENCES table (available starting 10.3). Run this query to list all sequences across your databases, along with their core properties:

SELECT 
  SEQUENCE_SCHEMA AS database_name,
  SEQUENCE_NAME AS sequence_name,
  START_VALUE,
  MIN_VALUE,
  MAX_VALUE,
  INCREMENT,
  CACHE_SIZE,
  CURRENT_VALUE
FROM information_schema.SEQUENCES;

To filter for a specific database, add a WHERE clause:

WHERE SEQUENCE_SCHEMA = 'your_target_database';

You can use mysqldump to export sequences specifically, avoiding the table-like backup that XtraBackup creates. Here are two common scenarios:

2.1 Export sequences from a single database #

Run this one-liner to dump only the sequences in your target DB:

mysqldump -u your_username -p --databases your_target_db --tables $(mysql -u your_username -p -N -e "SELECT SEQUENCE_NAME FROM information_schema.SEQUENCES WHERE SEQUENCE_SCHEMA='your_target_db'") > single_db_sequences.sql

The subquery fetches all sequence names in the DB, and mysqldump exports only those objects as valid CREATE SEQUENCE statements.

2.2 Export sequences from all databases #

If you need sequences across all databases, use a simple shell script to loop through each DB with sequences:

#!/bin/bash
DB_USER="your_username"
DB_PASS="your_password"

# Get all databases that contain sequences
TARGET_DBS=$(mysql -u$DB_USER -p$DB_PASS -N -e "SELECT DISTINCT SEQUENCE_SCHEMA FROM information_schema.SEQUENCES")

# Export sequences from each DB to a combined file
for DB in $TARGET_DBS; do
  SEQUENCES=$(mysql -u$DB_USER -p$DB_PASS -N -e "SELECT SEQUENCE_NAME FROM information_schema.SEQUENCES WHERE SEQUENCE_SCHEMA='$DB'")
  mysqldump -u$DB_USER -p$DB_PASS --databases $DB --tables $SEQUENCES >> all_sequences_dump.sql
done

Make the script executable (chmod +x export_sequences.sh) and run it—this will create a single SQL file with all your sequences.

If you need to preserve the current value of sequences (not just their definition), follow these steps:

3.1 Export sequences with current values #

1. First, export the sequence definitions (as before):

mysqldump -u your_username -p --databases your_target_db --tables $(mysql -u your_username -p -N -e "SELECT SEQUENCE_NAME FROM information_schema.SEQUENCES WHERE SEQUENCE_SCHEMA='your_target_db'") > sequence_definitions.sql

2. Then, generate SQL to reset sequences to their current values:
   Run this query in MariaDB and save the output to a file (e.g., sequence_current_values.sql):

SELECT 
  CONCAT('ALTER SEQUENCE ', SEQUENCE_SCHEMA, '.', SEQUENCE_NAME, ' RESTART WITH ', CURRENT_VALUE, ';') AS reset_sql
FROM information_schema.SEQUENCES
WHERE SEQUENCE_SCHEMA = 'your_target_db';

3.2 Import sequences to a new/restore environment #

1. First, if you're recovering from an XtraBackup restore, delete the "fake tables" that were created from sequences (double-check these are indeed sequences, not real tables!):

DROP TABLE IF EXISTS your_target_db.sequence_name1, your_target_db.sequence_name2;

2. Import the sequence definitions:

mysql -u your_username -p your_target_db < sequence_definitions.sql

3. Apply the current value resets to match the original state:

mysql -u your_username -p your_target_db < sequence_current_values.sql

This ensures your sequences work as expected post-import, unlike the broken table-like objects from XtraBackup.

内容的提问来源于stack exchange，提问作者Roc King