-
Notifications
You must be signed in to change notification settings - Fork 43
/
Copy pathinnodb_myrocks_backup_tests.sh
executable file
·2938 lines (2281 loc) · 155 KB
/
innodb_myrocks_backup_tests.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#!/bin/bash
########################################################################
# Created By Manish Chawla, Percona LLC #
# This script tests backup for innodb and myrocks tables #
# Assumption: PS and PXB are already installed as tarballs #
# Usage: #
# 1. Set paths in this script: #
# xtrabackup_dir, backup_dir, mysqldir, datadir, qascripts, logdir, #
# vault_config, cloud_config #
# 2. Set config variables in the script for #
# sysbench, stream, encryption key, kmip, kms #
# 3. For usage run the script as: ./innodb_myrocks_backup_tests.sh #
# 4. Logs are available in: logdir #
########################################################################
# Set script variables
export xtrabackup_dir="$HOME/pxb-9.1/bld_9.1/install/bin"
export mysqldir="$HOME/mysql-9.1/bld_9.1/install"
export backup_dir="$HOME/dbbackup_$(date +"%d_%m_%Y")"
export datadir="${mysqldir}/data"
export qascripts="$HOME/percona-qa"
export logdir="$HOME/backuplogs"
export cloud_config="$HOME/aws.cnf" # Only required for cloud backup tests
export PATH="$PATH:$xtrabackup_dir"
rocksdb="disabled" # Set this to disabled for PXB2.4 and MySQL versions
server_type="PS" # Default server PS
install_type="tarball" # Set value to tarball/package
LOCK_DDL=on # LOCK_DDL Accepted values: on, reduced
mysql_start_timeout=60
# Set sysbench variables
num_tables=10
table_size=1000
random_type=uniform
# Set stream and encryption key
backup_stream="backup.xbstream"
encrypt_key="mHU3Zs5sRcSB7zBAJP1BInPP5lgShKly"
backup_tar="backup.tar"
# Set user for backup
backup_user="root"
# Set Kmip configuration
kmip_server_address="0.0.0.0"
kmip_server_port=5696
kmip_client_ca="/home/manish.chawla/.local/etc/pykmip/client_certificate_john_smith.pem"
kmip_client_key="/home/manish.chawla/.local/etc/pykmip/client_key_john_smith.pem"
kmip_server_ca="/home/manish.chawla/.local/etc/pykmip/server_certificate.pem"
# For kms tests set the values of KMS_REGION, KMS_KEYID, KMS_AUTH_KEY, KMS_SECRET_KEY in the shell and then run the tests
kms_region="${KMS_REGION:-us-east-1}" # Set KMS_REGION to change default value us-east-1
kms_id="${KMS_KEYID:-}"
kms_auth_key="${KMS_AUTH_KEY:-}"
kms_secret_key="${KMS_SECRET_KEY:-}"
# Start vault server
start_vault_server(){
echo "Setting up vault server"
if [ ! -d $HOME/vault ]; then
mkdir $HOME/vault
fi
rm -rf $HOME/vault/*
# Kill any previously running vault server
killall vault > /dev/null 2>&1
$qascripts/vault_test_setup.sh --workdir=$HOME/vault --use-ssl > /dev/null 2>&1
vault_config="$HOME/vault/keyring_vault_ps.cnf"
vault_url=$(grep 'vault_url' "$vault_config" | awk -F '=' '{print $2}' | tr -d '[:space:]')
secret_mount_point=$(grep 'secret_mount_point' "$vault_config" | awk -F '=' '{print $2}' | tr -d '[:space:]')
token=$(grep 'token' "$vault_config" | awk -F '=' '{print $2}' | tr -d '[:space:]')
vault_ca=$(grep 'vault_ca' "$vault_config" | awk -F '=' '{print $2}' | tr -d '[:space:]')
}
# Below function is a hack-ish way to find out if the server type is PS or MS
find_server_type() {
# Grep for the server variable --innodb-sys-tablespace-encrypt which is available only in Percona Server
$mysqldir/bin/mysqld --help --verbose | grep -q "innodb-sys-tablespace-encrypt"
if [ $? -eq 0 ]; then
server_type="PS"
else
server_type="MS"
fi
}
normalize_version(){
local major=0
local minor=0
local patch=0
# Only parses purely numeric version numbers, 1.2.3
# Everything after the first three values are ignored
if [[ $1 =~ ^([0-9]+)\.([0-9]+)\.?([0-9]*)([\.0-9])*$ ]]; then
major=${BASH_REMATCH[1]}
minor=${BASH_REMATCH[2]}
patch=${BASH_REMATCH[3]}
fi
printf %02d%02d%02d $major $minor $patch
}
VER=$($mysqldir/bin/mysqld --version | awk -F 'Ver ' '{print $2}' | grep -oe '[0-9]\.[0-9][\.0-9]*' | head -n1)
PXB_VER=$($xtrabackup_dir/xtrabackup --no-defaults --version 2>&1 | awk -F 'version' '{print $2}' | grep -oe '[0-9]\.[0-9][\.0-9]*' | head -n1)
VERSION=$(normalize_version $VER)
PXB_VERSION=$(normalize_version $PXB_VER)
#set -o pipefail
start_server() {
# This function starts the server
ADD_OPTIONS=$1
{ for pid in $(pgrep mysqld); do kill -9 $pid && wait $pid 2>/dev/null; done; } 2>/dev/null
rr $mysqldir/bin/mysqld --no-defaults --basedir=$mysqldir --datadir=$datadir $MYSQLD_OPTIONS $ADD_OPTIONS --port=21000 --socket=$mysqldir/socket.sock --plugin-dir=$mysqldir/lib/plugin --max-connections=1024 --log-error=$datadir/error.log --general-log --log-error-verbosity=3 --core-file > /dev/null 2>&1 &
MPID="$!"
for X in $(seq 0 ${mysql_start_timeout}); do
sleep 1
if ${mysqldir}/bin/mysqladmin -uroot -S${mysqldir}/socket.sock ping > /dev/null 2>&1; then
echo "..Server started successfully"
break
fi
if [ $X -eq ${mysql_start_timeout} ]; then
echo "ERR: Database could not be started. Please check error logs: ${mysqldir}/data/error.log"
exit 1
fi
done
}
initialize_db() {
# This function initializes and starts mysql database
local MYSQLD_OPTIONS="$1"
if [ ! -d ${logdir} ]; then
mkdir ${logdir}
fi
if [ -d $datadir ]; then
rm -rf $datadir
fi
echo "=>Creating data directory"
$mysqldir/bin/mysqld --no-defaults --datadir=$datadir --initialize-insecure > $mysqldir/mysql_install_db.log 2>&1
echo "..Data directory created"
echo "=>Starting MySQL server"
start_server
echo "Creating innodb data in database"
which sysbench >/dev/null 2>&1
if [ "$?" -ne 0 ]; then
echo "ERR: Sysbench not found, data could not be created"
exit 1
fi
$mysqldir/bin/mysql -uroot -S$mysqldir/socket.sock -e"CREATE DATABASE test"
if [[ "${MYSQLD_OPTIONS}" != *"keyring"* ]]; then
# Create tables without encryption
sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --table-size=${table_size} --mysql-db=test --mysql-user=root --threads=100 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock --rand-type=${random_type} prepare
if [ "${rocksdb}" = "enabled" ]; then
echo "Creating rocksdb data in database"
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE DATABASE IF NOT EXISTS test_rocksdb;"
sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --table-size=${table_size} --mysql-db=test_rocksdb --mysql-user=root --threads=100 --db-driver=mysql --mysql-storage-engine=ROCKSDB --mysql-socket=${mysqldir}/socket.sock --rand-type=${random_type} prepare
fi
else
# Create encrypted tables: changed the oltp_common.lua script to include mysql-table-options="Encryption='Y'"
echo "Creating encrypted tables in innodb"
sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --table-size=${table_size} --mysql-db=test --mysql-user=root --threads=100 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock --mysql-table-options="Encryption='Y'" --rand-type=${random_type} prepare >/dev/null 2>&1
if [ "$?" -ne 0 ]; then
for ((i=1; i<=${num_tables}; i++)); do
echo "Creating the table sbtest$i..."
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE test.sbtest$i (id int(11) NOT NULL AUTO_INCREMENT, k int(11) NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y';"
done
echo "Adding data in tables..."
sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --mysql-db=test --mysql-user=root --threads=50 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock --time=30 --rand-type=${random_type} run >/dev/null 2>&1
fi
fi
}
process_backup() {
# This function extracts a streamed backup, decrypts and uncompresses it
local BK_TYPE="$1"
local BK_PARAMS="$2"
local EXT_DIR="$3"
if [[ "${BK_TYPE}" = "stream" ]]; then
if [ -z "${backup_dir}/${backup_stream}" ]; then
echo "ERR: The backup stream file was not created in ${backup_dir}/${backup_stream}. Please check the backup logs in ${logdir} for errors."
exit 1
else
echo "Extract the backup from the stream file at ${backup_dir}/${backup_stream}"
${xtrabackup_dir}/xbstream --directory=${EXT_DIR} --extract --verbose < ${backup_dir}/${backup_stream} 2>>${logdir}/extract_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Extract of backup failed. Please check the log at: ${logdir}/extract_backup_${log_date}_log"
exit 1
else
echo "Backup was successfully extracted. Logs available at: ${logdir}/extract_backup_${log_date}_log"
#rm -r ${backup_dir}/${backup_stream}
fi
fi
fi
if [[ "${BK_PARAMS}" = *"--encrypt-key"* ]]; then
echo "Decrypting the backup files at ${EXT_DIR}"
${xtrabackup_dir}/xtrabackup --decrypt=AES256 --encrypt-key=${encrypt_key} --target-dir=${EXT_DIR} --parallel=10 2>>${logdir}/decrypt_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Decrypt of backup failed. Please check the log at: ${logdir}/decrypt_backup_${log_date}_log"
exit 1
else
echo "Backup was successfully decrypted. Logs available at: ${logdir}/decrypt_backup_${log_date}_log"
fi
fi
if [[ "${BK_PARAMS}" = *"--compress"* ]]; then
if ! which qpress 2>&1>/dev/null; then
echo "ERR: The qpress package is not installed. It is required to decompress the backup."
exit 1
fi
echo "Decompressing the backup files at ${EXT_DIR}"
#${xtrabackup_dir}/xtrabackup --decompress --remove-original --parallel=100 --target-dir=${EXT_DIR} 2>>${logdir}/decompress_backup_${log_date}_log
${xtrabackup_dir}/xtrabackup --decompress --parallel=10 --target-dir=${EXT_DIR} 2>>${logdir}/decompress_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Decompress of backup failed. Please check the log at: ${logdir}/decompress_backup_${log_date}_log"
exit 1
else
echo "Backup was successfully decompressed. Logs available at: ${logdir}/decompress_backup_${log_date}_log"
fi
fi
}
incremental_backup() {
# This function takes the incremental backup
local BACKUP_PARAMS="$1 --lock-ddl=$LOCK_DDL"
local PREPARE_PARAMS="$2"
local RESTORE_PARAMS="$3"
local MYSQLD_OPTIONS="$4"
local BACKUP_TYPE="$5"
local CLOUD_PARAMS="$6"
log_date=$(date +"%d_%m_%Y_%M")
if [ -d ${backup_dir} ]; then
rm -r ${backup_dir}
fi
mkdir -p ${backup_dir}/full
if [ ! -d ${logdir} ]; then
mkdir ${logdir}
fi
case "${BACKUP_TYPE}" in
'cloud')
echo "=>Taking full backup and uploading it"
rr ${xtrabackup_dir}/xtrabackup --no-defaults --user=${backup_user} --password='' --backup --extra-lsndir=${backup_dir} --target-dir=${backup_dir}/full -S ${mysqldir}/socket.sock --datadir=${datadir} ${BACKUP_PARAMS} --stream=xbstream 2>${logdir}/full_backup_${log_date}_log | ${xtrabackup_dir}/xbcloud ${CLOUD_PARAMS} put full_backup_${log_date} 2>${logdir}/upload_full_backup_${log_date}_log
;;
'stream')
echo "=>Taking full backup and creating a stream file"
rr ${xtrabackup_dir}/xtrabackup --no-defaults --user=${backup_user} --password='' --backup --target-dir=${backup_dir}/full -S ${mysqldir}/socket.sock --datadir=${datadir} ${BACKUP_PARAMS} --stream=xbstream --parallel=10 > ${backup_dir}/${backup_stream} 2>${logdir}/full_backup_${log_date}_log
;;
'tar')
echo "=>Taking full backup and creating a tar file"
# Note: The --stream=tar option does not support --parallel option
rr ${xtrabackup_dir}/xtrabackup --no-defaults --user=${backup_user} --password='' --backup --target-dir=${backup_dir}/full -S ${mysqldir}/socket.sock --datadir=${datadir} ${BACKUP_PARAMS} --stream=tar > ${backup_dir}/${backup_tar} 2>${logdir}/full_backup_${log_date}_log
;;
*)
echo "=>Taking full backup"
rr ${xtrabackup_dir}/xtrabackup --no-defaults --user=${backup_user} --password='' --backup --target-dir=${backup_dir}/full -S ${mysqldir}/socket.sock --datadir=${datadir} ${BACKUP_PARAMS} --register-redo-log-consumer 2>${logdir}/full_backup_${log_date}_log
;;
esac
if [ "$?" -ne 0 ]; then
grep -e "PXB will not be able to make a consistent backup" -e "PXB will not be able to take a consistent backup" "${logdir}"/full_backup_"${log_date}"_log
if [ "$?" -ne 0 ]; then
echo "ERR: Full Backup failed. Please check the log at: ${logdir}/full_backup_${log_date}_log"
exit 1
else
return # Backup could not be completed due to DDL
fi
else
echo "..Full backup was successfully created at: ${backup_dir}/full. Logs available at: ${logdir}/full_backup_${log_date}_log"
fi
if [ "${BACKUP_TYPE}" = "tar" ]; then
if [ -z "${backup_dir}/${backup_tar}" ]; then
echo "ERR: The backup tar file was not created in ${backup_dir}/${backup_tar}. Please check the backup logs in ${logdir} for errors."
exit 1
else
echo "Extract the backup from the tar file at ${backup_dir}/${backup_tar}"
tar -xvf ${backup_dir}/${backup_tar} -C ${backup_dir}/full >${logdir}/extract_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Extract of backup failed using tar. Please check the log at: ${logdir}/extract_backup_${log_date}_log"
exit 1
else
echo "Backup was successfully extracted. Logs available at: ${logdir}/extract_backup_${log_date}_log"
fi
fi
fi
if [ "${BACKUP_TYPE}" = "cloud" ]; then
echo "=>Downloading full backup"
${xtrabackup_dir}/xbcloud ${CLOUD_PARAMS} get full_backup_${log_date} 2>${logdir}/download_full_backup_${log_date}_log | ${xtrabackup_dir}/xbstream -xv -C ${backup_dir}/full 2>${logdir}/download_stream_full_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Download of Full Backup failed. Please check the log at: ${logdir}/download_full_backup_${log_date}_log and ${logdir}/download_stream_full_backup_${log_date}_log"
exit 1
else
echo "..Full backup was successfully downloaded at: ${backup_dir}/full"
fi
fi
# Call function to process backup for streaming, encryption and compression
process_backup "${BACKUP_TYPE}" "${BACKUP_PARAMS}" "${backup_dir}/full"
echo "=>Adding data in database"
# Innodb data
sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --mysql-db=test --mysql-user=root --threads=50 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock --time=20 --rand-type=${random_type} run >/dev/null 2>&1 &
# Rocksdb data
if [ "${rocksdb}" = "enabled" ]; then
sysbench /usr/share/sysbench/oltp_insert.lua --tables=${num_tables} --mysql-db=test_rocksdb --mysql-user=root --threads=50 --db-driver=mysql --mysql-storage-engine=ROCKSDB --mysql-socket=${mysqldir}/socket.sock --time=20 --rand-type=${random_type} run >/dev/null 2>&1 &
fi
sleep 10
case "${BACKUP_TYPE}" in
'cloud')
echo "=>Taking incremental backup and uploading it"
${xtrabackup_dir}/xtrabackup --no-defaults --user=${backup_user} --password='' --backup --target-dir=${backup_dir}/inc --incremental-basedir=${backup_dir} -S ${mysqldir}/socket.sock --datadir=${datadir} ${BACKUP_PARAMS} --stream=xbstream 2>${logdir}/inc_backup_${log_date}_log | ${xtrabackup_dir}/xbcloud ${CLOUD_PARAMS} put inc_backup_${log_date} 2>${logdir}/upload_inc_backup_${log_date}_log
;;
'stream')
echo "=>Taking incremental backup and creating a stream file"
${xtrabackup_dir}/xtrabackup --no-defaults --user=${backup_user} --password='' --backup --target-dir=${backup_dir}/inc --incremental-basedir=${backup_dir}/full -S ${mysqldir}/socket.sock --datadir=${datadir} ${BACKUP_PARAMS} --stream=xbstream --parallel=10 > ${backup_dir}/${backup_stream} 2>${logdir}/inc_backup_${log_date}_log
;;
# Note: The --stream=tar option is not supported for incremental backup in PXB2.4
*)
echo "=>Taking incremental backup"
rr ${xtrabackup_dir}/xtrabackup --no-defaults --user=${backup_user} --password='' --backup --target-dir=${backup_dir}/inc --incremental-basedir=${backup_dir}/full -S ${mysqldir}/socket.sock --datadir=${datadir} ${BACKUP_PARAMS} --register-redo-log-consumer 2>${logdir}/inc_backup_${log_date}_log
;;
esac
if [ "$?" -ne 0 ]; then
grep -e "PXB will not be able to make a consistent backup" -e "PXB will not be able to take a consistent backup" "${logdir}"/inc_backup_"${log_date}"_log
if [ "$?" -ne 0 ]; then
echo "ERR: Incremental Backup failed. Please check the log at: ${logdir}/inc_backup_${log_date}_log"
exit 1
else
return # Backup could not be completed due to DDL
fi
else
echo "..Inc backup was successfully created at: ${backup_dir}/inc. Logs available at: ${logdir}/inc_backup_${log_date}_log"
fi
if [ "${BACKUP_TYPE}" = "cloud" ]; then
echo "=>Downloading incremental backup"
${xtrabackup_dir}/xbcloud ${CLOUD_PARAMS} get inc_backup_${log_date} 2>${logdir}/download_inc_backup_${log_date}_log | ${xtrabackup_dir}/xbstream -xv -C ${backup_dir}/inc 2>${logdir}/download_stream_inc_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Download of Inc Backup failed. Please check the log at: ${logdir}/download_inc_backup_${log_date}_log and ${logdir}/download_stream_inc_backup_${log_date}_log"
exit 1
else
echo "..Incremental backup was successfully downloaded at: ${backup_dir}/inc"
fi
fi
if [ "${BACKUP_TYPE}" = "cloud" ]; then
echo "=>Deleting full backup"
${xtrabackup_dir}/xbcloud ${CLOUD_PARAMS} delete full_backup_${log_date} 2>${logdir}/delete_full_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Delete of Full Backup failed. Please check the log at: ${logdir}/delete_full_backup_${log_date}_log"
else
echo "..Full backup was successfully deleted from the cloud"
fi
echo "=>Deleting incremental backup"
${xtrabackup_dir}/xbcloud ${CLOUD_PARAMS} delete inc_backup_${log_date} 2>${logdir}/delete_inc_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Delete of Inc Backup failed. Please check the log at: ${logdir}/delete_inc_backup_${log_date}_log"
else
echo "..Incremental backup was successfully deleted from the cloud"
fi
fi
# Call function to process backup for streaming, encryption and compression
process_backup "${BACKUP_TYPE}" "${BACKUP_PARAMS}" "${backup_dir}/inc"
# Save the backup before prepare
if [ -d $HOME/dbbackup_save ]; then
rm -r $HOME/dbbackup_save
fi
cp -r ${backup_dir} $HOME/dbbackup_save
echo "=>Preparing full backup"
rr ${xtrabackup_dir}/xtrabackup --no-defaults --user=root --password='' --prepare --apply-log-only --target_dir=${backup_dir}/full ${PREPARE_PARAMS} 2>${logdir}/prepare_full_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Prepare of full backup failed. Please check the log at: ${logdir}/prepare_full_backup_${log_date}_log"
exit 1
else
echo "..Prepare of full backup was successful. Logs available at: ${logdir}/prepare_full_backup_${log_date}_log"
fi
echo "=>Preparing incremental backup"
rr ${xtrabackup_dir}/xtrabackup --no-defaults --user=root --password='' --prepare --target_dir=${backup_dir}/full --incremental-dir=${backup_dir}/inc ${PREPARE_PARAMS} 2>${logdir}/prepare_inc_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Prepare of incremental backup failed. Please check the log at: ${logdir}/prepare_inc_backup_${log_date}_log"
exit 1
else
echo "..Prepare of incremental backup was successful. Logs available at: ${logdir}/prepare_inc_backup_${log_date}_log"
fi
echo "=>Restart mysql server"
start_server
echo "Collecting current data of all tables"
# Get record count and checksum for each table in test database
for ((i=1; i<=${num_tables}; i++)); do
rc_innodb_orig[$i]=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "SELECT COUNT(*) FROM test.sbtest$i;")
chk_innodb_orig[$i]=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "CHECKSUM TABLE test.sbtest$i;"|awk '{print $2}')
done
# Get record count and checksum of each table in test_rocksdb database
if [[ "${rocksdb}" = "enabled" ]] && [[ "${MYSQLD_OPTIONS}" != *"keyring"* ]]; then
for ((i=1; i<=${num_tables}; i++)); do
rc_myrocks_orig[$i]=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "SELECT COUNT(*) FROM test_rocksdb.sbtest$i;")
chk_myrocks_orig[$i]=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "CHECKSUM TABLE test_rocksdb.sbtest$i;"|awk '{print $2}')
done
fi
echo "Stopping mysql server and moving data directory"
${mysqldir}/bin/mysqladmin -uroot -S${mysqldir}/socket.sock shutdown
if [ -d ${mysqldir}/data_orig_$(date +"%d_%m_%Y") ]; then
rm -r ${mysqldir}/data_orig_$(date +"%d_%m_%Y")
fi
mv ${mysqldir}/data ${mysqldir}/data_orig_$(date +"%d_%m_%Y")
if [[ "${BACKUP_PARAMS}" = *"--transition-key"* ]] && [[ "${MYSQLD_OPTIONS}" != *"keyring_vault"* ]]; then
echo "Moving keyring file from ${mysqldir} dir"
if [ -f "${keyring_file}" ]; then
mv "${keyring_file}" "${keyring_file}"_orig
fi
#mv ${mysqldir}/keyring ${mysqldir}/keyring_orig
fi
if [ -d "${mysqldir}"/binlog ]; then
mv "${mysqldir}"/binlog/* ${mysqldir}/data_orig_$(date +"%d_%m_%Y")
rm -r "${mysqldir}"/binlog
fi
echo "=>Restoring full backup"
${xtrabackup_dir}/xtrabackup --no-defaults --user=root --password='' --copy-back --target-dir=${backup_dir}/full --datadir=${datadir} ${RESTORE_PARAMS} 2>${logdir}/res_backup_${log_date}_log
if [ "$?" -ne 0 ]; then
echo "ERR: Restore of full backup failed. Please check the log at: ${logdir}/res_backup_${log_date}_log"
exit 1
else
echo "..Restore of full backup was successful. Logs available at: ${logdir}/res_backup_${log_date}_log"
fi
# Copy server certificates from original data dir
cp -pr ${mysqldir}/data_orig_$(date +"%d_%m_%Y")/*.pem ${mysqldir}/data/
echo "=>Restarting the mysql server"
start_server
# Binlog can't be applied if binlog is encrypted or skipped
if [[ "${MYSQLD_OPTIONS}" != *"binlog-encryption"* ]] && [[ "${MYSQLD_OPTIONS}" != *"--encrypt-binlog"* ]] && [[ "${MYSQLD_OPTIONS}" != *"skip-log-bin"* ]]; then
echo "Check xtrabackup for binlog position"
xb_binlog_file=$(cat ${backup_dir}/full/xtrabackup_binlog_info|awk '{print $1}'|head -1)
xb_binlog_pos=$(cat ${backup_dir}/full/xtrabackup_binlog_info|awk '{print $2}'|head -1)
echo "Xtrabackup binlog position: $xb_binlog_file, $xb_binlog_pos"
echo "Applying binlog to restored data starting from $xb_binlog_file, $xb_binlog_pos"
${mysqldir}/bin/mysqlbinlog ${mysqldir}/data_orig_$(date +"%d_%m_%Y")/$xb_binlog_file --start-position=$xb_binlog_pos | ${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock
if [ "$?" -ne 0 ]; then
echo "ERR: The binlog could not be applied to the restored data"
fi
sleep 5
fi
echo "Checking restored data"
echo "Check the table status"
check_err=0
if [[ "${rocksdb}" = "enabled" ]] && [[ "${MYSQLD_OPTIONS}" != *"keyring"* ]]; then
database_list="test test_rocksdb"
else
database_list="test"
fi
for ((i=1; i<=${num_tables}; i++)); do
for database in ${database_list}; do
if ! table_status=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "CHECK TABLE $database.sbtest$i"|cut -f4-); then
echo "ERR: CHECK TABLE $database.sbtest$i query failed"
# Check if database went down
if ! ${mysqldir}/bin/mysqladmin ping --user=root --socket=${mysqldir}/socket.sock >/dev/null 2>&1; then
echo "ERR: The database has gone down due to corruption in table $database.sbtest$i"
exit 1
fi
check_err=1
fi
if [[ "$table_status" != "OK" ]]; then
echo "ERR: CHECK TABLE $database.sbtest$i query displayed the table status as '$table_status'"
check_err=1
exit 1
fi
done
done
# Check if database went down
if ! ${mysqldir}/bin/mysqladmin ping --user=root --socket=${mysqldir}/socket.sock >/dev/null 2>&1; then
echo "ERR: The database has gone down due to corruption, the restore was unsuccessful"
exit 1
fi
if [[ "$check_err" -eq 0 ]]; then
echo "All innodb and myrocks tables status: OK"
else
echo "After restore, some tables may be corrupt, check table status is not OK"
fi
# Record count and checksum can't be checked if binlog encryption is enabled and binlogs are not applied
if [[ "${MYSQLD_OPTIONS}" != *"binlog-encryption"* ]] && [[ "${MYSQLD_OPTIONS}" != *"--encrypt-binlog"* ]] && [[ "${MYSQLD_OPTIONS}" != *"skip-log-bin"* ]]; then
echo "Check the record count of tables in databases: ${database_list}"
# Get record count for each table in databases test and test_rocksdb
rc_err=0
checksum_err=0
for ((i=1; i<=${num_tables}; i++)); do
rc_innodb_res[$i]=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "SELECT COUNT(*) FROM test.sbtest$i;")
if [[ "${rc_innodb_orig[$i]}" -ne "${rc_innodb_res[$i]}" ]]; then
echo "ERR: The record count of test.sbtest$i changed after restore. Record count in original data: ${rc_innodb_orig[$i]}. Record count in restored data: ${rc_innodb_res[$i]}."
rc_err=1
fi
if [[ "${rocksdb}" = "enabled" ]] && [[ "${MYSQLD_OPTIONS}" != *"keyring"* ]]; then
rc_myrocks_res[$i]=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "SELECT COUNT(*) FROM test_rocksdb.sbtest$i;")
if [[ "${rc_myrocks_orig[$i]}" -ne "${rc_myrocks_res[$i]}" ]]; then
echo "ERR: The record count of test_rocksdb.sbtest$i changed after restore. Record count in original data: ${rc_myrocks_orig[$i]}. Record count in restored data: ${rc_myrocks_res[$i]}."
rc_err=1
fi
fi
done
if [[ "$rc_err" -eq 0 ]]; then
echo "Match record count of tables in databases ${database_list} with original data: Pass"
fi
echo "Check the checksum of each table in databases: ${database_list}"
# Get checksum of each table in databases test and test_rocksdb
for ((i=1; i<=${num_tables}; i++)); do
chk_innodb_res[$i]=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "CHECKSUM TABLE test.sbtest$i;"|awk '{print $2}')
if [[ "${chk_innodb_orig[$i]}" -ne "${chk_innodb_res[$i]}" ]]; then
echo "ERR: The checksum of test.sbtest$i changed after restore. Checksum in original data: ${chk_innodb_orig[$i]}. Checksum in restored data: ${chk_innodb_res[$i]}."
checksum_err=1;
fi
if [[ "${rocksdb}" = "enabled" ]] && [[ "${MYSQLD_OPTIONS}" != *"keyring"* ]]; then
chk_myrocks_res[$i]=$(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "CHECKSUM TABLE test_rocksdb.sbtest$i;"|awk '{print $2}')
if [[ "${chk_myrocks_orig[$i]}" -ne "${chk_myrocks_res[$i]}" ]]; then
echo "ERR: The checksum of test_rocksdb.sbtest$i changed after restore. Checksum in original data: ${chk_myrocks_orig[$i]}. Checksum in restored data: ${chk_myrocks_res[$i]}."
checksum_err=1;
fi
fi
done
if [[ "$checksum_err" -eq 0 ]]; then
echo "Match checksum of all tables in databases ${database_list} with original data: Pass"
fi
fi
echo "Check for gaps in primary sequence id of tables"
gap_found=0
#for database in test test_rocksdb; do
for database in ${database_list}; do
for ((i=1; i<=${num_tables}; i++)); do
j=1
while read line; do
if [[ "$line" != "$j" ]]; then
echo "ERR: Gap found in $database.sbtest$i. Expected sequence number for ID is: $j. Actual sequence number for ID is: $line."
gap_found=1
return
fi
let j++
done < <(${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -Bse "SELECT id FROM $database.sbtest$i ORDER BY id ASC")
done
done
if [[ "$gap_found" -eq 0 ]]; then
echo "No gaps found in primary sequence id of tables: Pass"
fi
}
change_storage_engine() {
# This function changes the storage engine of a table
echo "Change the storage engine of test.sbtest1 to MYISAM, INNODB continuously"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "alter table test.sbtest1 ENGINE=MYISAM;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "alter table test.sbtest1 ENGINE=INNODB;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Change the storage engine of test_rocksdb.sbtest1 to INNODB, ROCKSDB, MYISAM continuously"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "alter table test_rocksdb.sbtest1 ENGINE=INNODB;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "alter table test_rocksdb.sbtest1 ENGINE=ROCKSDB;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "alter table test_rocksdb.sbtest1 ENGINE=MYISAM;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "alter table test_rocksdb.sbtest1 ENGINE=ROCKSDB;" >/dev/null 2>&1
done ) &
fi
}
add_drop_index() {
# This function adds and drops an index in a table
echo "Add and drop an index in the test.sbtest1 table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE INDEX kc on test.sbtest1 (k,c);" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 ADD INDEX kc2 (k,c);" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc2 on test.sbtest1;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc on test.sbtest1;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 ADD INDEX kc (k,c), ALGORITHM=COPY, LOCK=EXCLUSIVE;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc on test.sbtest1;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Add and drop an index in the test_rocksdb.sbtest1 table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE INDEX kc on test_rocksdb.sbtest1 (k,c);" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 ADD INDEX kc2 (k,c);" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc2 on test_rocksdb.sbtest1;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc on test_rocksdb.sbtest1;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 ADD INDEX kc (k,c), ALGORITHM=COPY, LOCK=EXCLUSIVE;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX kc on test_rocksdb.sbtest1;" >/dev/null 2>&1
done ) &
fi
}
rename_index() {
# This function renames an index in a table
echo "Rename an index in the test.sbtest1 table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 RENAME INDEX k_1 TO k_2, ALGORITHM=INPLACE, LOCK=NONE;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 RENAME INDEX k_2 TO k_1, ALGORITHM=INPLACE, LOCK=NONE;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Rename an index in the test_rocksdb.sbtest1 table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 RENAME INDEX k_1 TO k_2, ALGORITHM=INPLACE, LOCK=NONE;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 RENAME INDEX k_2 TO k_1, ALGORITHM=INPLACE, LOCK=NONE;" >/dev/null 2>&1
done ) &
fi
}
add_drop_full_text_index() {
# This function adds and drops a full text index in a table
echo "Add and drop a full text index in the test.sbtest1 table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE FULLTEXT INDEX full_index on test.sbtest1 (pad);" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX full_index on test.sbtest1;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Add and drop a full text index in the test_rocksdb.sbtest1 table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE FULLTEXT INDEX full_index on test_rocksdb.sbtest1 (pad);" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX full_index on test_rocksdb.sbtest1;" >/dev/null 2>&1
done ) &
fi
}
change_index_type() {
# This function changes the index type in a table
echo "Change the index type in the test.sbtest1 table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 DROP INDEX k_1, ADD INDEX k_1(k) USING BTREE, ALGORITHM=INSTANT;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 DROP INDEX k_1, ADD INDEX k_1(k) USING HASH, ALGORITHM=INSTANT;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Change the index type in the test_rocksdb.sbtest1 table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 DROP INDEX k_1, ADD INDEX k_1(k) USING BTREE, ALGORITHM=INSTANT;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 DROP INDEX k_1, ADD INDEX k_1(k) USING HASH, ALGORITHM=INSTANT;" >/dev/null 2>&1
done ) &
fi
}
add_drop_spatial_index() {
# This function adds data to a spatial table along with add/drop index
echo "Adding data in spatial table: test.geom"
a=1; b=2
( while true; do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "INSERT INTO test.geom VALUES(POINT($a,$b));" >/dev/null 2>&1
let a++; let b++
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Add and drop a spacial index in the test.geom table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE SPATIAL INDEX spa_index on test.geom (g), ALGORITHM=INPLACE, LOCK=SHARED;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX spa_index on test.geom;" >/dev/null 2>&1
done ) &
fi
}
add_drop_tablespace() {
# This function adds a table to a tablespace and then drops the table, tablespace
echo "Add an innodb table to a tablespace and drop the table, tablespace"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE test.sbtest1copy SELECT * from test.sbtest1;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1copy TABLESPACE ts1;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP TABLE test.sbtest1copy;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP TABLESPACE ts1;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Add a rocksdb table and drop the table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE test_rocksdb.sbrcopy$i Engine=ROCKSDB SELECT * from test.sbtest1;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP TABLE test_rocksdb.sbrcopy$i;" >/dev/null 2>&1
done ) &
fi
}
change_compression() {
# This function changes the compression of a table
echo "Change the compression of an innodb table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 compression='lz4';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 compression='zlib';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 compression='';" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Change the compression of a myrocks table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 comment = 'cfname=cf1';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 comment = 'cfname=cf2';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 comment = 'cfname=cf3';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest1 comment = 'cfname=cf4';" >/dev/null 2>&1
done ) &
fi
}
change_row_format() {
# This function changes the row format of a table
echo "Change the row format of an innodb table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest2 ROW_FORMAT=COMPRESSED;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest2 ROW_FORMAT=DYNAMIC;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest2 ROW_FORMAT=COMPACT;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest2 ROW_FORMAT=REDUNDANT;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Change the row format of a myrocks table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest2 ROW_FORMAT=COMPRESSED;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest2 ROW_FORMAT=DYNAMIC;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test_rocksdb.sbtest2 ROW_FORMAT=FIXED;" >/dev/null 2>&1
done ) &
fi
}
add_data_transaction() {
# This function adds data in both innodb and myrocks table in a single transaction
echo "Create tables innodb_t for innodb data and myrocks_t for myrocks data"
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE test.innodb_t(id int(11) PRIMARY KEY AUTO_INCREMENT, k int(11), c char(120), pad char(60), KEY k_1(k), KEY kc(k,c)) ENGINE=InnoDB;"
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE test.myrocks_t(id int(11) PRIMARY KEY AUTO_INCREMENT, k int(11), c char(120), pad char(60), KEY k_1(k), KEY kc(k,c)) ENGINE=ROCKSDB;"
echo "Insert data in both innodb_t and myrocks_t tables in a single transaction"
a=1; b=11; c=101
( while true; do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "START TRANSACTION;
INSERT INTO innodb_t(k, c, pad) VALUES($a, $b, $c);
INSERT INTO myrocks_t(k, c, pad) VALUES($a, $b, $c);
COMMIT;" test
let a++; let b++; let c++
done ) &
}
update_truncate_table() {
# This function updates data in tables and then truncates it
echo "Update an innodb table and then truncate it"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "SET @@SESSION.OPTIMIZER_SWITCH='firstmatch=ON';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "UPDATE test.sbtest1 SET c='Œ„´‰?Á¨ˆØ?”’';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "OPTIMIZE TABLE test.sbtest1;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "TRUNCATE test.sbtest1;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Update a myrocks table and then truncate it"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "UPDATE test_rocksdb.sbtest2 SET c='Œ„´‰?Á¨ˆØ?”’';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "OPTIMIZE TABLE test_rocksdb.sbtest2;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "TRUNCATE test_rocksdb.sbtest2;" >/dev/null 2>&1
done ) &
fi
}
create_drop_database() {
# This function creates a database and drops it
echo "Create a database test1_innodb, add data and then drop it"
( for ((i=1; i<=3; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE DATABASE IF NOT EXISTS test1_innodb;" >/dev/null 2>&1
sysbench /usr/share/sysbench/oltp_insert.lua --tables=1 --table-size=1000 --mysql-db=test1_innodb --mysql-user=root --threads=10 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock prepare >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test1_innodb.sbtest1 ADD COLUMN b JSON AS('{\"k1\": \"value\", \"k2\": [10, 20]}');" >/dev/null 2>&1
# Create a multivalue index
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE INDEX jindex on test1_innodb.sbtest1( (CAST(b->'$.k2' AS UNSIGNED ARRAY)) );"
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP INDEX jindex on test1_innodb.sbtest1;"
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test1_innodb.sbtest1 DROP COLUMN b;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP DATABASE test1_innodb;" >/dev/null 2>&1
done ) &
if [ "${rocksdb}" = "enabled" ]; then
echo "Create a database test1_rocksdb, add data and then drop it"
( for ((i=1; i<=3; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE DATABASE IF NOT EXISTS test1_rocksdb;" >/dev/null 2>&1
sysbench /usr/share/sysbench/oltp_insert.lua --tables=1 --table-size=1000 --mysql-db=test1_rocksdb --mysql-user=root --threads=10 --db-driver=mysql --mysql-storage-engine=ROCKSDB --mysql-socket=${mysqldir}/socket.sock prepare >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test1_rocksdb.sbtest1 ADD COLUMN b VARCHAR(255) DEFAULT '{"k1": "value", "k2": [10, 20]}';" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test1_rocksdb.sbtest1 DROP COLUMN b;" >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP DATABASE test1_rocksdb;" >/dev/null 2>&1
done ) &
fi
}
create_delete_encrypted_table() {
# This function creates an encrypted table and deletes it
echo "Create an encrypted table and delete it"
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE DATABASE IF NOT EXISTS test_innodb;" >/dev/null 2>&1
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "CREATE TABLE test_innodb.sbtest1 (id int(11) NOT NULL AUTO_INCREMENT, k int(11) NOT NULL DEFAULT '0', c char(120) NOT NULL DEFAULT '', pad char(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k)) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y' COMPRESSION='lz4';" >/dev/null 2>&1
sysbench /usr/share/sysbench/oltp_insert.lua --tables=1 --mysql-db=test_innodb --mysql-user=root --threads=100 --db-driver=mysql --mysql-socket=${mysqldir}/socket.sock --time=1 run >/dev/null 2>&1
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "DROP TABLE test_innodb.sbtest1;" >/dev/null 2>&1
done ) &
}
change_encryption() {
# This function changes the encryption of a table
echo "Change the encryption of a table"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 ENCRYPTION='N';"
${mysqldir}/bin/mysql -uroot -S${mysqldir}/socket.sock -e "ALTER TABLE test.sbtest1 ENCRYPTION='Y';"
done ) &
}
compressed_column() {
# This function compresses a table column
echo "Compress a table column"
( for ((i=1; i<=10; i++)); do
# Check if database is up otherwise exit the loop
${mysqldir}/bin//mysqladmin ping --user=root --socket=${mysqldir}/socket.sock 2>/dev/null 1>&2
if [ "$?" -ne 0 ]; then
break
fi