"ERROR 1049 (42000): Unknown database mydatabasename" when importing sql data into mysql/mariadb

Mysqldump is a tool frequently used in creating a backup of a mariadb or mysql database. To use this tool is pretty straight forward, just run below command:

$ mysqldump -u root -p mydatabasename > mydatabasename.sql 

The above command is fine, and we can always restore the data from the sql file into a database provided we have the database already in place, using below command:

$ mysql -u root -p mydatabasename <  mydatabasename.sql

A problem appears when we are transferring the sql file to another server which does not have the database already created. If we try to import the sql file, without the database already existed, we will get below error:

ERROR 1049 (42000): Unknown database mydatabasename

We can prevent this by adding an option to our mysqldump command. The option is "--databases" or in short "-B". To test it out, we can use below commands (dump the db, drop the db, and import back the db from the sql file):

$ mysqldump -u root -p --databases mydatabasename > mydatabasename.sql

$ mysqladmin -u root -p drop mydatabasename

$ mysql -u root -p < mydatabasename.sql     

This time, you would not get the above error, since the "--databases" option will add "CREATE DATABASE" query into the sql file, and that query will create the database if the database is not already exist.

 

Transferring a proxmox VM to a different proxmox installation (backup and restore)

 To backup a vm, you can use vzdump command. The command just need a vm ID to start the backup process.

Get the VMID

# qm list

Start the backup process. You can choose a compression option, between no compression, gzip or lzo.

# vzdump --compress gzip 655

The command will generate 2 files, a data file and a log file. Transfer the data file to the other proxmox installation, using whatever methods. I just use scp.

# scp vzdump-qemu-655-2020_10_10-13_51_13.vma mynewproxmox.ip.add.ress:

Once transferred, restore the file into a VM, by providing the VMID. This command will restore the dump file into a VM with VMID 300.

# qmrestore ~/vzdump-qemu-655-2020_10_10-13_51_13.vma 300

Oh, and all this can be done via GUI, but for this tutorial, I will just cover the command line part :).

Enjoy.

Backup RHEV/RHV/ovirt disk image to external drive

Login to RHV/RHEV/ovirt administrator interface

Search for your VM, lets say I want to search for windows VM

Right click on the VM name, and choose 'Create Snapshot'

Give a proper name, and tick "Save Memory" if you want to save current state of memory as well. Please be warned, that by choosing "Save Memory", the VM will be paused while the snapshooting is in progress. 

If you check the Snapshots tab, the status of the snapshot will be Locked, and the VM will be paused

Once completed, the status will be changed to Ok

Now, open a terminal inside the rhev machine, and search for the disk image snapshot by running below command:

# virsh -r dumpxml windows | grep "source file"

Your newly created snapshot is on the last line of the above command. Copy the file location, and put it into your external drive

# cp /rhev/data-center/00000002-0002-0002-0002-00000000035d/747b7d84-68d5-4436-98f5-baeec68381e1/images/8af6c761-94ac-4adb-bf33-f009e3dd3dc7/../8af6c761-94ac-4adb-bf33-f009e3dd3dc7/../8af6c761-94ac-4adb-bf33-f009e3dd3dc7/../8af6c761-94ac-4adb-bf33-f009e3dd3dc7/../8af6c761-94ac-4adb-bf33-f009e3dd3dc7/e42c8e85-9566-4953-b881-70c7ce97de0b /mnt/my_external_drive/windows-snapshot-20181219.raw

If you want a different format, use qemu-img command to change it. For example, you require this image to be vmware compatible

# qemu-img -f raw -O vmdk /mnt/my_external_drive/windows-snapshot-20181219.raw /mnt/my_external_drive/windows-snapshot-20181219.vmdk

Rsync to a different ssh port

In order to use rsync with different ssh post, you can use -e flag, and supply ssh command with port option. For example, I want to transfer a file called /root/book.txt from a server called project.local with sshd running on port 55522 to local partition /mnt, I can use this command:

$ rsync -avz --progress -e "ssh -p 55522" root@project.local:/root/book.txt /mnt

where:
-a for archive mode
-v for verbose
-z to compress data during transfer
--progress for showing progress of the copy
-e to specify the remote shell to use, in this case to use ssh to port 55522

Set up automated mysql/mariadb backup

We are going to use mysqldump for this automated backup exercise.

Create a read only user in mysql to do the backup, in this case, backupuser

# mysql -uroot -p

mysql> grant lock tables, select on *.* to 'backupuser'@'%' identified by 'password_of_your_choice';

Test doing mysqldump using the new user

# mysqldump -ubackupuser -p --all-databases > /tmp/mysql_all_db.sql

If everything is fine, and the mysql_all_db.sql is created successfully, proceed on giving the backupuser passwordless access to mysql. Add below lines to /root/.my.cnf

[mysqldump]

user = backupuser

host = localhost

password = "password_of_your_choice"

Test whether you can still do mysqldump without the -u and -p options

# mysqldump --all-databases > /tmp/mysql_all_db.sql

If everything is running fine, now is the time to add to crontab

# crontab -e

Put below lines to run the mysqldump command at 11 PM every Saturday, and the filename will be datestamped as well

* 23 * * * Sat /usr/bin/mysqldump --all-databases > /backup/mysql_all_db-$(date +%Y-%m-%d).sql

Using tar on the fly to efficiently transfer file over ssh (wondertar)

Have you been in the situation where you want to transfer a big file, and decided to tar it before transferring but being limited by the disk space available on the machine?

Well, worry no more as I will show you how you can do a tar on the fly while ssh'ing, to overcome that limitation.

Method 1:

ssh foo@machine-to-keep-the-data "tar czpf - /data/to/be/transferred" | tar xzpf - -C /the/data/new/place

What this command will do is to create a tar file (tar czpf), and untar it at the other side of the ssh (tar xvpf) command, where c is for create tar, z is to use gzip, p is for preserving permission, f is for file which is to be zipped, - is for stdin or stdout and x is for extract


Method 2:

tar cpf - /data/to/be/transferred | ssh foo@machine-to-keep-the-data tar xpf - -C /the/data/new/place" 

This command will tar the file, and untar it at the other end, same as above, but just different command arrangement


Method 3 (this is useful if you want to tar it. and keep it that way on the other end, without untarring it):

tar czf - -C /data/to/be/transferred | ssh foo@machine-to-keep-the-data "cat - > /the/data/new/place/backup.tar.gz"

Method 4 (add pv to the middle of the pipes to monitor the transfer speed):

sudo apt-get install pv; ssh foo@machine-to-keep-the-data "tar czpf - /data/to/be/transferred" | pv | tar xzpf - -C /the/data/new/place

That's all, hope you will find these useful.

Backup and restore mysql databases

To backup(dump) single mysql database to a file:

mysqldump -u foo -p foodb > foodb.sql

where -u is for username, -p is for mysqldump to ask for password, foodb is the name of the database and foodb.sql is the file to dump the database.


To backup(dump) all databases to a file:

mysqldump -u foo -p --all-databases > alldb.sql

where -u is for username, -p is for mysqldump to ask for password, --all-databases is to tell mysqldump to dump all databases available and alldb.sql is the dumpfile name.


To restore back mysql database from dumpfile:


1. From terminal

mysql -u foo -p foodb < foodb.sql

-u and -p are similar to above, foodb is the name of the database and foodb.sql is the name of the dumpfile.


2. From inside mysql console where you have to access mysql console first:

mysql> source foodb.sql
or
mysql> \. foodb.sql

where foodb.sql is the dumpfile

Rebuild corrupted dbmail account

Symptoms of corrupted dbmail account are slow to open account and if manage to open account, it will take forever just to open the inbox. Here are the steps to rebuild back the account using joe's email account

1. You have to backup the the user's inbox. Use command "dbmail-export" to export mailbox to mbox file. For better management, export the mailbox to home folder.
# cd /home
# dbmail-export -u joe

2. A few mbox files will be generated depending on how many folders you have in your email. The mboxs will be placed in a folder named with your username, in this case, joe
#ls /home

3. Clear up the username mailbox using "dbmail-users". -e is for clearing mailbox
# dbmail-users -e joe

4. Delete the user. -d is for deleting user
# dbmail-users -d joe

5. Create back user. -a is for add user, -w for password, -p for password type and -s for aliases
# dbmail-users -a joe -w joe123456 -p md5 -s joe@example.com

6. Download dbmail tarball from here. Extract and you can see a script named mailbox2dbmail in contrib folder. Use that script to migrate back the mbox files to dbmail. For easy usage, copy the mailbox2dbmail script to /usr/local/bin. -u is for username, -t for type of input, -m for input filename and -b is for the name you want the mailbox to appear in your email
# cd /home/joe
# mailbox2dbmail -u joe -t mbox -m INBOX.mbox -b INBOX

7. Go to your joe's webmail and you can see the INBOX is already there. Repeat step 6 for all your mailboxes

Installing backuppc in centos 5

I have been given the task of setting up one backuppc server, and below are the steps on how I did it :)

Server setup

1. Download the backuppc 3.1.0 srpm from dev.centos.org
   
2. Rebuild the srpm using rpmbuild. If the command is not there in your pc, install it first using 'yum install rpm-build'
   
• # rpmbuild --rebuild backuppc-3.1.0-1.el5.centos.src.rpm
  
3. Get your rpm file at /usr/src/redhat/RPMS/i386 and install it. Install all the perl module needed using yum
   
• # yum install perl-Compress-Zlib perl-Archive-Zip perl-File-RsyncP perl-XML-RSS httpd
  
• # rpm -Uvh backuppc-3.1.0-1%{dist}.i386.rpm
4. User backuppc will be created upon installation. Change apache user to backuppc.
• # vi /etc/httpd/conf/httpd.conf
• Change 'User apache' to 'User backuppc'
• Save
  
5. Edit file /etc/httpd/conf.d/backuppc.conf
• # vi /etc/httpd/conf.d/backuppc.conf
• change 'Allow from 127.0.0.1' to 'Allow from all'
• Save
  
6. Create password for cgi-bin admin user
   
• # htpasswd -c /var/lib/backuppc/passwd/htpasswd admin
7. Edit backuppc config file
• # vi /etc/BackupPC/config.pl
• Find and change accordingly
  
• $Conf{ServerHost} = 'localhost';
• $Conf{SplitPath} = '/usr/bin/split';
  $Conf{CatPath} = '/bin/cat';
  $Conf{GzipPath} = '/bin/gzip';
  $Conf{Bzip2Path} = '/usr/bin/bzip2';
• $Conf{BackupPCUser} = 'backuppc';
• $Conf{TopDir} = '/var/lib/backuppc';
  $Conf{ConfDir} = '/etc/BackupPC';
  $Conf{LogDir} = '/var/log/BackupPC';
  $Conf{InstallDir} = '/usr';
  $Conf{CgiDir} = '/usr/share/backuppc/cgi-bin';
  
• $Conf{ServerInitdPath} = '/etc/init.d/backuppc';
  $Conf{ServerInitdStartCmd} = '$sshPath -q -x -l root $serverHost$serverInitdPath start';
  
• $Conf{SshPath} = '/usr/bin/ssh';
  
• $Conf{NmbLookupPath} = '/usr/bin/nmblookup';
  
• $Conf{PingPath} = '/bin/ping';
• $Conf{CgiAdminUsers} = 'admin';
• Save
  
8. Grant passwordless sudo for user backuppc to run /bin/gtar and /bin/tar
   
• # visudo
• Add these entries
  
• Defaults !lecture # to disable lecture
  
• backuppc ALL=NOPASSWD:/bin/gtar,/bin/tar # enable user backuppc to run /bin/tar and /bin/gtar without authentication.
• Comment this entry
• #Defaults requiretty
  
• Save
  
9. Restart apache and backuppc service
• # /etc/init.d/http restart
• # /etc/init.d/backuppc restart
10. Open your browser and point it to 'http://backuppc_server_ip/backuppc' and you should see the backuppc web interface
    
11. After this, you have to do almost all the configuration through the web interface. To test, you can run localhost backup first. You have to create the host, fill up all the setting and you are ready to go. Record the host and ip in /etc/hosts.
    

Client setup

1. Create new user
• # useradd backupuser
• # passwd backupuser
2. Grant passwordless sudo for user backupuser
• # visudo
• Add these entries
  
• Defaults !lecture # to disable lecture
  
• backupuser ALL=NOPASSWD:/bin/gtar,/bin/tar # enable user backuppc to run /bin/tar and /bin/gtar without authentication.
• Comment this entry
• #Defaults requiretty
  
• Save
  
3. From the server using backuppc user, create ssh public key
• # su -s /bin/bash backuppc
  
• $ mkdir .ssh
• $ chown backuppc.backuppc .ssh
• $ chmod 700 .ssh
• $ ssh-keygen -t rsa
• $ ssh-copy-id -i .ssh/id_rsa.pub backupuser@client
4. To make sure that the 3rd step is a success, try to ssh to backupuser@client using backuppc user from the server. If no password is asked, then you are ready.
• # su -s /bin/bash backuppc
• $ ssh backupuser@client
5. You can start entering the client to the list of host and start backing up :)

Update: Precompiled rpm can be downloaded here

Accessing data on xen lvm guest image

Accessing xen guest image is very easy if the image is not lvm partitioned. But the main problem arise when the image is of lvm format and normal mount command cannot be used. Here I will show both the way. The first is when ext filesystem is used, and the second is when lvm is used.

To mount xen guest image (without lvm)

1. check the partition on the image
# fdisk -lu
The result will be something like this:



2. Mount using offset option
# mount -o loop,offset=106929152 /path/to/image /mnt
where 106929152=208846*512, 208846 is the start of the partition. Using this way, you only mount the second partition and not the whole image

3. You can now access your image at /mnt

To mount xen guest image (with lvm)

1. Check the partition on the image
# fdisk -lu /path/to/image

2. You have to install kpartx to handle lvm partiton
# yum install kpartx

3. Run kpartx on the image
# kpartx -av /path/to/image

4. Run vgscan to scan volume group available
# vgscan

5. Run vgchange to activate the volume group in the image
# vgchange -ay VolGroup00

6. Use lvs to see what is the name of your low volume group
# lvs

7. Mount the low volume group
# mount /dev/VolGroup00/LogVol01 /mnt

8. You can access your lvm image at the mounted directory which is /mnt

9. To unmount it, a few commands have to be executed (umount for unmounting, vgchange -an to deactivate volume group, kpartx -d to delete device map and losetup -d to delete loop device used)
# umount /mnt/
# vgchange -an VolGroup00
# kpartx -d /path/to/image
# losetup -d /dev/loop0

Hope this will be useful

Backup using tar and ssh

Doing backup is important. You do not want to store your backup at the same machine where the data is stored. It is to avoid data loss when the machine broke down. To do this job, you can use tar paired with ssh to archive your important data and transfer it through network to another machine. Below are the steps:

1. Make sure the backup machine is installed with ssh server and the service is running
# yum install openssh-server
# service sshd start

2. Go to the folder where you want to backup. Use tar to archive and send it though network to backup machine
# tar -cvjf - /path/to/backup | ssh user@backupmachine "cat > /home/backup.tar.bz2"

3. Finish. Congatulations, your backup file is now safely kept in the backup machine

Mounting LVM Disk using Ubuntu livecd

Mounting is an easy process to do, provided the filesystem type you are using is supported. What happen when you have an LVM formatted disk, and you need to mount it because the disk cannot be booted and a hell lot of valuable data kept inside?? Do not worry, because the solution is here.......

1. Get a live cd, for example, Ubuntu. For this article, I use Ubuntu 6.06 (I cannot find any latest version of ubuntu at my place)

2. Boot using the live cd. Search for these tools: lvm2. If the cd do not have it, install it.
# apt-get install lvm2

3. To make sure the harddisk is recognised, you can use fdisk
# fdisk -lu

4. Once installed, run pvscan to scan all disks for physical volume. this to make sure your LVM harddisk is detected by Ubuntu
# pvscan
PV /dev/sda2 VG VolGroup00 lvm2 [74.41 GB / 32.00 MB free]
Total: 1 [74.41 GB] / in use: 1 [74.41 GB] / in no VG: 0 [0 ]


5. After that run vgscan to scan disks for volume groups.
# vgscan
Reading all physical volumes. This may take a while...
Found volume group "VolGroup00" using metadata type lvm2


6. Activate all volume groups available.
# vgchange -a y
2 logical volume(s) in volume group "VolGroup00" now active

7. Run lvscan to scan all disks for logical volume. You can see partitions inside the hard disk now active.
# lvscan
ACTIVE '/dev/VolGroup00/LogVol00' [72.44 GB] inherit
ACTIVE '/dev/VolGroup00/LogVol01' [1.94 GB] inherit

8. Mount the partition to any directory you want, usually to /mnt
# mount /dev/VolGroup00/LogVol00 /mnt

9. You can access the partition in the /mnt directory and can backup your data

System backup using rsnapshot

Rsnapshot is a filesystem snapshot utility that we can use for free to backup our system. To use this tool, user must have perl, ssh and rsync installed in their machine. Rsnapshot is an ideal tool for backup because it saves a lot of hard disk space. This can be done because the space needed is roughly the size of one full backup plus a copy of each additional file that is changed. The installation is also simple as shown below:

1. install rsnapshot
   
• -> yum install rcsnapshot
2. after installation, do some setting in /etc/rsnapshot.conf . Some of the important settings are as below:
   
• snapshot_root - change it to your preferred directory
• cmd_cp - for linux user, uncomment this option
• backup script
• backup user@servername:/folder/to/backed-up destination/in/snapshot_root
  
• interval - set this to set the frequency of snapshot to be taken. Please note that it must be arranged from smaller unit(hourly) on top to bigger one. For example:
  
• interval daily 7 - this means snapshot will be taken 7 times a week
• interval weekly 4 - this means snapshot will be taken 4 times a month
3. Test the validation of the config file
   
• -> rsnapshot configtest
4. Test run your command
• -> rsnapshot -t daily
5. To backup, just run the below command(for daily)
   
• -> rsnapshot daily