What to do if the server_id of the slaves gets lost after restarting Mariadb?

Question

linux

I create a slave from the master like this

for SLAVE_HOST in "${SLAVE_HOSTS[@]}"
do
  echo "SLAVE: $SLAVE_HOST"
  echo "  - Create server_id"
  SERVERID=`date '+%s'`
  ssh $SLAVE_HOST "echo server_id=$SERVERID >> /etc/mysql/my.cnf"
  mysql -h $SLAVE_HOST "-P$SPORT" "-u$REPLICA_USER" "-p$REPLICA_PASS" $DB <<-EOSQL &
    SET GLOBAL server_id=$SERVERID;
  EOSQL
  echo "  - Creating database copy"
  mysql -h $SLAVE_HOST "-P$SPORT" "-u$REPLICA_USER" "-p$REPLICA_PASS" -e "DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB;"
  scp $DUMP_FILE $SLAVE_HOST:$DUMP_FILE >/dev/null
  mysql -h $SLAVE_HOST "-P$SPORT" "-u$REPLICA_USER" "-p$REPLICA_PASS" $DB < $DUMP_FILE

  echo "  - Setting up slave replication"
  mysql -h $SLAVE_HOST "-P$SPORT" "-u$REPLICA_USER" "-p$REPLICA_PASS" $DB <<-EOSQL &
    STOP SLAVE;
    CHANGE MASTER TO MASTER_HOST='0.0.0.0',
    MASTER_PORT=$MPORT,
    MASTER_USER='$REPLICA_USER',
    MASTER_PASSWORD='$REPLICA_PASS',
    MASTER_LOG_FILE='$LOG_FILE',
    MASTER_LOG_POS=$LOG_POS;
    START SLAVE;
  EOSQL
  # Wait for slave to get started and have the correct status
  sleep 2
  # Check if replication status is OK
  SLAVE_OK=$(mysql -h $SLAVE_HOST "-P$SPORT" "-u$REPLICA_USER" "-p$REPLICA_PASS" -e "SHOW SLAVE STATUS\G;" | grep 'Waiting for master')
  if [ -z "$SLAVE_OK" ]; then
    echo "  - Error ! Wrong slave IO state."
  else
    echo "  - Slave IO state OK"
  fi
done

Everything perfectly flies to the end of my.cnf on the slave writes an entry like
server_id=1601962227

However, when the slave is restarted, the id drops to one, breaking replication and at least crack, there is no serve_id=1 anywhere else in the mariadb configs.
Debian 10 OS

Please help

Answer 1

In general, I decided it myself, it didn't work because my file ended like this:
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
[mysqldump]
quick
quote-names
max_allowed_packet = 512M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completion
[ isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!include /etc/mysql/mariadb.cnf
server_id=1601962227
And you need the server id to be strictly in the section
[mysqld]
Those
[mysqld]
All sorts of rubbish on a hundred thousand rows
server_id=1601962227
Then everything works fine

Answer 2

Look at the logs.
server_id=1 I think by default, meaning the server then ignores your ID.
It seems to be no more than the maximum, but try to choose a smaller one just in case https://mariadb.com/docs/reference/mdb/system-vari...