See this: https://severalnines.com/blog/mariadb-backups-what-is-mariabackup/
Ensure DB Is up:
mariabackup --backup --target-dir=/apps/backup/mariadb --user=root --password=P@ssw0rd
Prepare backup:
mariabackup --prepare --target-dir=/apps/backup/mariadb/
Clean up data dir /var/lib/mysql and restore:
mariabackup --copy-back --target-dir=/apps/backup/mariadb/
Chown:
chown -R mysql:mysql /var/lib/mysql/
Mas DB2 Notes
Sunday, November 3, 2024
Sunday, October 27, 2024
Move SQL Server DB to another Server using backup and restore
Refer to here:
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-restore-database?view=sql-server-ver15
[unica@sbidemo backup_20241028]$ sqlcmd -S localhost -U sa
Password:
1> SELECT Name FROM sys.Databases
2> go
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
dcc
acc_demo
(6 rows affected)
1> BACKUP DATABASE dcc TO DISK = N'/apps/data/backup_20241028/dcc.bak' WITH NOFORMAT, NOINIT, NAME = N'dcc',SKIP, NOREWIND, NOUNLOAD, STATS = 10;
2> GO
Msg 3201, Level 16, State 1, Server unicademo, Line 1
Cannot open backup device '/apps/data/backup_20241028/dcc.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server unicademo, Line 1
BACKUP DATABASE is terminating abnormally.
1> BACKUP DATABASE dcc TO DISK = N'/apps/data/backup_20241028/dcc.bak' WITH NOFORMAT, NOINIT, NAME = N'dcc',SKIP, NOREWIND, NOUNLOAD, STATS = 10;
2> GO
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 251840 pages for database 'dcc', file 'demoCombined_Data' on file 1.
Processed 1 pages for database 'dcc', file 'demoCombined_Log' on file 1.
BACKUP DATABASE successfully processed 251841 pages in 82.777 seconds (23.768 MB/sec).
1> BACKUP DATABASE dcc TO DISK = N'/apps/data/backup_20241028/acc_demo.bak' WITH NOFORMAT, NOINIT, NAME = N'acc_demo',SKIP, NOREWIND, NOUNLOAD, STATS = 10; 2> GO 10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. 100 percent processed. Processed 251840 pages for database 'dcc', file 'demoCombined_Data' on file 1. Processed 1 pages for database 'dcc', file 'demoCombined_Log' on file 1. BACKUP DATABASE successfully processed 251841 pages in 133.949 seconds (14.688 MB/sec). 1> [unica@sbidemo data]$ sqlcmd -S localhost -U sa -C Password: 1> RESTORE DATABASE dcc FROM DISK = '/apps/data/backup_20241028/dcc.bak' 2> GO Processed 251840 pages for database 'dcc', file 'demoCombined_Data' on file 1. Processed 1 pages for database 'dcc', file 'demoCombined_Log' on file 1. Converting database 'dcc' from version 904 to the current version 957. Database 'dcc' running the upgrade step from version 904 to version 905. Database 'dcc' running the upgrade step from version 905 to version 906. Database 'dcc' running the upgrade step from version 906 to version 907. Database 'dcc' running the upgrade step from version 907 to version 908. Database 'dcc' running the upgrade step from version 908 to version 909. Database 'dcc' running the upgrade step from version 909 to version 910. Database 'dcc' running the upgrade step from version 910 to version 911. Database 'dcc' running the upgrade step from version 911 to version 912. Database 'dcc' running the upgrade step from version 912 to version 913. Database 'dcc' running the upgrade step from version 913 to version 914. Database 'dcc' running the upgrade step from version 914 to version 915. Database 'dcc' running the upgrade step from version 915 to version 916. Database 'dcc' running the upgrade step from version 916 to version 917. Database 'dcc' running the upgrade step from version 917 to version 918. Database 'dcc' running the upgrade step from version 918 to version 919. Database 'dcc' running the upgrade step from version 919 to version 920. Database 'dcc' running the upgrade step from version 920 to version 921. Database 'dcc' running the upgrade step from version 921 to version 922. Database 'dcc' running the upgrade step from version 922 to version 923. Database 'dcc' running the upgrade step from version 923 to version 924. Database 'dcc' running the upgrade step from version 924 to version 925. Database 'dcc' running the upgrade step from version 925 to version 926. Database 'dcc' running the upgrade step from version 926 to version 927. Database 'dcc' running the upgrade step from version 927 to version 928. Database 'dcc' running the upgrade step from version 928 to version 929. Database 'dcc' running the upgrade step from version 929 to version 930. Database 'dcc' running the upgrade step from version 930 to version 931. Database 'dcc' running the upgrade step from version 931 to version 932. Database 'dcc' running the upgrade step from version 932 to version 933. Database 'dcc' running the upgrade step from version 933 to version 934. Database 'dcc' running the upgrade step from version 934 to version 935. Database 'dcc' running the upgrade step from version 935 to version 936. Database 'dcc' running the upgrade step from version 936 to version 937. Database 'dcc' running the upgrade step from version 937 to version 938. Database 'dcc' running the upgrade step from version 938 to version 939. Database 'dcc' running the upgrade step from version 939 to version 940. Database 'dcc' running the upgrade step from version 940 to version 941. Database 'dcc' running the upgrade step from version 941 to version 942. Database 'dcc' running the upgrade step from version 942 to version 943. Database 'dcc' running the upgrade step from version 943 to version 944. Database 'dcc' running the upgrade step from version 944 to version 945. Database 'dcc' running the upgrade step from version 945 to version 946. Database 'dcc' running the upgrade step from version 946 to version 947. Database 'dcc' running the upgrade step from version 947 to version 948. Database 'dcc' running the upgrade step from version 948 to version 949. Database 'dcc' running the upgrade step from version 949 to version 950. Database 'dcc' running the upgrade step from version 950 to version 951. Database 'dcc' running the upgrade step from version 951 to version 952. Database 'dcc' running the upgrade step from version 952 to version 953. Database 'dcc' running the upgrade step from version 953 to version 954. Database 'dcc' running the upgrade step from version 954 to version 955. Database 'dcc' running the upgrade step from version 955 to version 956. Database 'dcc' running the upgrade step from version 956 to version 957. RESTORE DATABASE successfully processed 251841 pages in 22.186 seconds (88.682 MB/sec). 1>
1> BACKUP DATABASE dcc TO DISK = N'/apps/data/backup_20241028/acc_demo.bak' WITH NOFORMAT, NOINIT, NAME = N'acc_demo',SKIP, NOREWIND, NOUNLOAD, STATS = 10; 2> GO 10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. 100 percent processed. Processed 251840 pages for database 'dcc', file 'demoCombined_Data' on file 1. Processed 1 pages for database 'dcc', file 'demoCombined_Log' on file 1. BACKUP DATABASE successfully processed 251841 pages in 133.949 seconds (14.688 MB/sec). 1> [unica@sbidemo data]$ sqlcmd -S localhost -U sa -C Password: 1> RESTORE DATABASE dcc FROM DISK = '/apps/data/backup_20241028/dcc.bak' 2> GO Processed 251840 pages for database 'dcc', file 'demoCombined_Data' on file 1. Processed 1 pages for database 'dcc', file 'demoCombined_Log' on file 1. Converting database 'dcc' from version 904 to the current version 957. Database 'dcc' running the upgrade step from version 904 to version 905. Database 'dcc' running the upgrade step from version 905 to version 906. Database 'dcc' running the upgrade step from version 906 to version 907. Database 'dcc' running the upgrade step from version 907 to version 908. Database 'dcc' running the upgrade step from version 908 to version 909. Database 'dcc' running the upgrade step from version 909 to version 910. Database 'dcc' running the upgrade step from version 910 to version 911. Database 'dcc' running the upgrade step from version 911 to version 912. Database 'dcc' running the upgrade step from version 912 to version 913. Database 'dcc' running the upgrade step from version 913 to version 914. Database 'dcc' running the upgrade step from version 914 to version 915. Database 'dcc' running the upgrade step from version 915 to version 916. Database 'dcc' running the upgrade step from version 916 to version 917. Database 'dcc' running the upgrade step from version 917 to version 918. Database 'dcc' running the upgrade step from version 918 to version 919. Database 'dcc' running the upgrade step from version 919 to version 920. Database 'dcc' running the upgrade step from version 920 to version 921. Database 'dcc' running the upgrade step from version 921 to version 922. Database 'dcc' running the upgrade step from version 922 to version 923. Database 'dcc' running the upgrade step from version 923 to version 924. Database 'dcc' running the upgrade step from version 924 to version 925. Database 'dcc' running the upgrade step from version 925 to version 926. Database 'dcc' running the upgrade step from version 926 to version 927. Database 'dcc' running the upgrade step from version 927 to version 928. Database 'dcc' running the upgrade step from version 928 to version 929. Database 'dcc' running the upgrade step from version 929 to version 930. Database 'dcc' running the upgrade step from version 930 to version 931. Database 'dcc' running the upgrade step from version 931 to version 932. Database 'dcc' running the upgrade step from version 932 to version 933. Database 'dcc' running the upgrade step from version 933 to version 934. Database 'dcc' running the upgrade step from version 934 to version 935. Database 'dcc' running the upgrade step from version 935 to version 936. Database 'dcc' running the upgrade step from version 936 to version 937. Database 'dcc' running the upgrade step from version 937 to version 938. Database 'dcc' running the upgrade step from version 938 to version 939. Database 'dcc' running the upgrade step from version 939 to version 940. Database 'dcc' running the upgrade step from version 940 to version 941. Database 'dcc' running the upgrade step from version 941 to version 942. Database 'dcc' running the upgrade step from version 942 to version 943. Database 'dcc' running the upgrade step from version 943 to version 944. Database 'dcc' running the upgrade step from version 944 to version 945. Database 'dcc' running the upgrade step from version 945 to version 946. Database 'dcc' running the upgrade step from version 946 to version 947. Database 'dcc' running the upgrade step from version 947 to version 948. Database 'dcc' running the upgrade step from version 948 to version 949. Database 'dcc' running the upgrade step from version 949 to version 950. Database 'dcc' running the upgrade step from version 950 to version 951. Database 'dcc' running the upgrade step from version 951 to version 952. Database 'dcc' running the upgrade step from version 952 to version 953. Database 'dcc' running the upgrade step from version 953 to version 954. Database 'dcc' running the upgrade step from version 954 to version 955. Database 'dcc' running the upgrade step from version 955 to version 956. Database 'dcc' running the upgrade step from version 956 to version 957. RESTORE DATABASE successfully processed 251841 pages in 22.186 seconds (88.682 MB/sec). 1>
Install Mariadb 10.6 on RHEL 9
Use this for reference: https://mariadb.com/docs/server/service-management/upgrades/community-server/release-series-cs10-6/
[root@sbidemo home]# sudo yum install curl
Updating Subscription Management repositories.
Last metadata expiration check: 0:23:50 ago on Sun 27 Oct 2024 10:12:38 PM.
Package curl-7.76.1-29.el9_4.1.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
[root@sbidemo home]# curl -LsSO https://r.mariadb.com/downloads/mariadb_repo_setup
[root@sbidemo home]# chmod +x mariadb_repo_setup
[root@sbidemo home]# sudo ./mariadb_repo_setup \
--mariadb-server-version="mariadb-10.6"
# [info] Checking for script prerequisites.
# [warning] Found existing file at /etc/yum.repos.d/mariadb.repo. Moving to /etc/yum.repos.d/mariadb.repo.old_1
# [info] MariaDB Server version 10.6 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg /home
/home
# [info] Successfully added trusted package signing keys
# [info] Cleaning package cache...
Updating Subscription Management repositories.
39 files removed
[root@sbidemo home]# sudo apt install mariadb-server mariadb-backup
sudo: apt: command not found
[root@sbidemo home]# sudo yum install MariaDB-server MariaDB-backup
Updating Subscription Management repositories.
^[MariaDB Server [=== ] --- B/s | 0 B --:-- ETMariaDB Server 103 kB/s | 597 kB 00:05
MariaDB MaxScale 989 B/s | 6.9 kB 00:07
MariaDB Tools 1.3 kB/s | 4.3 kB 00:03
packages-microsoft-com-mssql-server-2019 1.5 MB/s | 5.5 MB 00:03
Red Hat Enterprise Linux 9 for x86_64 - AppStream (RPMs) 5.2 MB/s | 42 MB 00:08
Red Hat Enterprise Linux 9 for x86_64 - BaseOS (RPMs) 4.9 MB/s | 33 MB 00:06
Dependencies resolved.
==========================================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================================
Installing:
MariaDB-backup x86_64 10.6.19-1.el9 mariadb-main 6.9 M
MariaDB-server x86_64 10.6.19-1.el9 mariadb-main 18 M
Installing dependencies:
MariaDB-client x86_64 10.6.19-1.el9 mariadb-main 8.5 M
boost-program-options x86_64 1.75.0-8.el9 rhel-9-for-x86_64-appstream-rpms 108 k
galera-4 x86_64 26.4.19-1.el9 mariadb-main 1.4 M
perl-Sys-Hostname x86_64 1.23-481.el9 rhel-9-for-x86_64-appstream-rpms 19 k
socat x86_64 1.7.4.1-5.el9_4.2 rhel-9-for-x86_64-appstream-rpms 308 k
Transaction Summary
==========================================================================================================================================================================================================
Install 7 Packages
Total download size: 35 M
Installed size: 232 M
Is this ok [y/N]: y
Is this ok [y/N]: y
Downloading Packages:
(1/7): MariaDB-client-10.6.19-1.el9.x86_64.rpm 1.7 MB/s | 8.5 MB 00:05
(2/7): MariaDB-backup-10.6.19-1.el9.x86_64.rpm 1.1 MB/s | 6.9 MB 00:06
(3/7): MariaDB-server-10.6.19-1.el9.x86_64.rpm 2.1 MB/s | 18 MB 00:08
(4/7): boost-program-options-1.75.0-8.el9.x86_64.rpm 44 kB/s | 108 kB 00:02
(5/7): galera-4-26.4.19-1.el9.x86_64.rpm 351 kB/s | 1.4 MB 00:03
(6/7): perl-Sys-Hostname-1.23-481.el9.x86_64.rpm 22 kB/s | 19 kB 00:00
(7/7): socat-1.7.4.1-5.el9_4.2.x86_64.rpm 251 kB/s | 308 kB 00:01
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.5 MB/s | 35 MB 00:10
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : socat-1.7.4.1-5.el9_4.2.x86_64 1/7
Installing : perl-Sys-Hostname-1.23-481.el9.x86_64 2/7
Installing : MariaDB-client-10.6.19-1.el9.x86_64 3/7
Installing : boost-program-options-1.75.0-8.el9.x86_64 4/7
Running scriptlet: galera-4-26.4.19-1.el9.x86_64 5/7
Installing : galera-4-26.4.19-1.el9.x86_64 5/7
Running scriptlet: galera-4-26.4.19-1.el9.x86_64 5/7
Running scriptlet: MariaDB-server-10.6.19-1.el9.x86_64 6/7
usermod: no changes
Installing : MariaDB-server-10.6.19-1.el9.x86_64 6/7
Running scriptlet: MariaDB-server-10.6.19-1.el9.x86_64 6/7
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo
See the MariaDB Knowledgebase at https://mariadb.com/kb
Please report any problems at https://mariadb.org/jira
The latest information about MariaDB is available at https://mariadb.org/.
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
Installing : MariaDB-backup-10.6.19-1.el9.x86_64 7/7
Running scriptlet: MariaDB-server-10.6.19-1.el9.x86_64 7/7
Running scriptlet: MariaDB-backup-10.6.19-1.el9.x86_64 7/7
Verifying : MariaDB-backup-10.6.19-1.el9.x86_64 1/7
Verifying : MariaDB-client-10.6.19-1.el9.x86_64 2/7
Verifying : MariaDB-server-10.6.19-1.el9.x86_64 3/7
Verifying : galera-4-26.4.19-1.el9.x86_64 4/7
Verifying : boost-program-options-1.75.0-8.el9.x86_64 5/7
Verifying : perl-Sys-Hostname-1.23-481.el9.x86_64 6/7
Verifying : socat-1.7.4.1-5.el9_4.2.x86_64 7/7
Installed products updated.
Installed:
MariaDB-backup-10.6.19-1.el9.x86_64 MariaDB-client-10.6.19-1.el9.x86_64 MariaDB-server-10.6.19-1.el9.x86_64 boost-program-options-1.75.0-8.el9.x86_64 galera-4-26.4.19-1.el9.x86_64
perl-Sys-Hostname-1.23-481.el9.x86_64 socat-1.7.4.1-5.el9_4.2.x86_64
Complete!
Sunday, July 14, 2024
Alter column name in mariadb
ALTER TABLE customer
CHANGE COLUMN `CUST_ID` -- old name; notice optional backticks
CustomerID -- new name
decimal(15,0); -- must include all the datatype info
Export and Import Table in Mariadb
mariadb-dump -p dmdb customer > customer.sql
mariadb -p uirtdb < customer.sql
Wednesday, March 13, 2024
Error starting mongodb
[unica@unicademo mongodb]$ sudo systemctl status mongod
● mongod.service - MongoDB Database Server
Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)
Active: failed (Result: core-dump) since Thu 2024-03-14 14:49:28 +08; 12s ago
Docs: https://docs.mongodb.org/manual
Process: 176103 ExecStart=/usr/bin/mongod $OPTIONS (code=dumped, signal=ILL)
Main PID: 176103 (code=dumped, signal=ILL)
Mar 14 14:49:20 unicademo.localdomain systemd[1]: Started MongoDB Database Server.
Mar 14 14:49:28 unicademo.localdomain systemd[1]: mongod.service: Main process exited, code=dumped, status=4/ILL
Mar 14 14:49:28 unicademo.localdomain systemd[1]: mongod.service: Failed with result 'core-dump'.
The reason is :https://superuser.com/questions/1814515/mongodb-error-failed-with-result-core-dump
The "signal=ILL" part of the systemctl output means that MongoDB failed to start because of an illegal instruction.
This is caused by MongoDB 5 and later requiring AVX to run.
You can check if your CPU supports AVX by doing:
cat /proc/cpuinfo | grep avx
If you get any kind of output your CPU should support AVX, if not its either too old or if you are running Linux inside a VM and your hypervisor isn't passing through your physical CPU flags.
For a list of CPUs that support AVX see: https://en.wikipedia.org/wiki/Advanced_Vector_Extensions (basically CPUs started supporting AVX at around 2011)
If your physical CPU is indeed too old your options are:
Downgrade to MongoDB 4 which still runs without AVX but will be end of life next year
Compile your own version of MongoDB without AVX or look around for community made version without AVX (there are some unoffical containers out there for example)
If you are using virtualization make sure your hypervisor isn't hiding your physical CPU flags for compability or other reasons
If your hardware is indeed too old to support AVX upgrade to a newer CPU
Subscribe to:
Posts (Atom)