Details
-
Bug
-
Status: In Progress (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.6.19, 11.4.5
Description
Tested on: 10.6.19-15 Enterprise (consistent), 10.6.19 Community (consistent), 11.4.5 Community (consistent)
- You will need 3 instances to make this occur: Master (db1) / Slave (db2) / Slave (db3)
- The backup is taken on the SLAVE that is running, but after restore on the other slave, replication is connected to master.
- If inserts on the master are slowed down to one per second, this failure does not occur and checksum table shows that tables are perfect copies.
- If the table is ENGINE=INNODB the failure will not occur.
On master, create a table:
Drop schema if exists Rpa; Create schema Rpa; Use Rpa;
|
DROP TABLE IF EXISTS `aria_table`;
|
CREATE TABLE `aria_table` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`message` longtext NOT NULL, `createdOn` datetime NOT NULL DEFAULT current_timestamp(),
|
`processId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_svax` (`processId`)
|
) ENGINE=ARIA AUTO_INCREMENT=7295 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
|
On master, run a bash script that quickly inserts into the aria_table:
#!/bin/bash
|
touch t.txt
|
mariadb -Ae "truncate table Rpa.aria_table;"
|
ii=0
|
while [ -f t.txt ]; do
|
ii=$(( $ii + 1 ))
|
mariadb -Ae "use Rpa; INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000));"
|
echo $ii
|
done
|
echo "finished"
|
Verify that db2 is a slave of db1 and that the slave is running.
On db3, stop server, destroy the data:
systemctl stop mariadb
|
datadir=/var/lib/mysql
|
logdir=/var/log/mysql
|
rm -fr $datadir/* $logdir/*
|
On db2, use mariabackup to stream a backup into the datadir of db3:
datadir=/var/lib/mysql
|
replica=192.168.8.113
|
mariabackup --user=root --backup --stream=xbstream | ssh -o StrictHostKeyChecking=NO root@"$replica" -t "cd $datadir; mbstream -x"
|
On db3, set the replication to db1
mhost=db1.edw.ee
|
datadir=/var/lib/mysql
|
logdir=/var/log/mysql
|
mport=3306
|
mpw=password
|
muser=repl
|
|
|
# Test the connectivity from this replica node to the primary:
|
mariadb -h$mhost -u$muser -p$mpw -ABNe "select now(); select @@hostname;"
|
|
|
cd $datadir
|
|
|
gtid=$(cat $datadir/*_binlog_info | tail -1 | awk '{print $3}')
|
echo $gtid $mhost $mport $muser $mpw
|
|
|
mariabackup --prepare --target-dir=$datadir
|
|
|
chown -R mysql:mysql $datadir
|
chown -R mysql:mysql $logdir
|
|
|
systemctl start mariadb;
|
|
|
mariadb -ABNe "stop slave; reset slave; set global gtid_slave_pos='$gtid'; change master to master_host='$mhost', master_port=$mport, master_user='$muser', master_password='$mpw', master_use_gtid=slave_pos; start slave;"
|
|
|
mariadb -Ae "show replica status\G"
|
ERROR OCCURS!
Last_SQL_Error: Error 'Duplicate entry '9699' for key 'PRIMARY'' on query. Default database: 'Rpa'. Query: 'INSERT INTO aria_table ( message, createdOn, processId) VALUES ( 'Lorem ipsum dolor sit amet, et pharetra nulla tincidunt.', now(), round(rand()*10000000))'
|