Rewrite MySQL / MariaDB Database Dump Create View Statements For Current User
Hi!
If you would like to import a database dump file created by MySQL's or MariaDB's mysqldump executable, but it contains statements to create views, the import process may abort with an error when it comes to creating views.
When creating a view, MySQL wants to know who created it, and for that it needs a username and the host. If the user who is executing the import does not have sufficient privileges or the original user referenced in the dump does not exist (for example when importing the dump into a fresh database for a migration), this leads to an error.
Usually when I import a dump, I don't care so much about the "SQL Security Definer", so I just want to set it to the importing user.
You can generate a new, modified SQL dump file very easily with the following shell command:
$ sed -r 's#^(/\*!50013 DEFINER=).+?( SQL SECURITY DEFINER \*/)$#\1CURRENT_USER\2#' input_file.sql > output_file.sql
This command simply scans through the entire dump, looking for the statement created by mysqldump which triggers the database to create the view if it doesn't exist already. It then sets the user information to CURRENT_USER which refers to the user that is currently executing the import.
Please note that because the search pattern is so specific, it will probably require some modification in the future, depending on the version of mysqldump you're using and if / how they change this particular statement. On the upside the chance that it will accidentally modify something it shouldn't is pretty low.
I hope this is helpful to you!
Thanks for reading!
How To Migrate Your Live Windows System To VirtualBox
Hey!
Ever regret that Sun didn't include something like a migration assistant for your live Windows system in VirtualBox?
My method concentrates on these basic steps:
- Create a backup of your whole system partition
- Create a new VirtualBox Hard Disk
- Install Windows onto the VirtualBox Hard Disk to create a function MBR (Master Boot Record)
- Apply the backup onto your VirtualBox Hard Disk
Done!
What you need:
- Sun VirtualBox
- Acronis TrueImage Home 2009 (or an equivalent backup program)
- File-to-ISO converter (like a CD/DVD burning program, e.g. Ahead Nero or Alcohol 120%)
- The Windows installation disc
Process:
- Start Acronis TrueImage and create a backup of your whole system partition. For version 2009 doesn't mean System State but My Computer => Disk 1 => C:. Leave Back up sector-by-sector unchecked. Adjust the other settings to your liking and start the backup process.
- Go to Tools => Create Bootable Rescue Media and create the image as an ISO file.
- Use your File-to-ISO converter or burning software to convert your .tbi backup file to an ISO that you will be able to mount it as a CD/DVD later on. (Reason being that I had problems selecting the right partition to be replaced by the backup when I had two partitions mounted to the virtual machine - the other one included the backup file. Also you won't be able to install the Guest Additions, so you can't mount folders over the VirtualBox shared folders.)
- Start VirtualBox and create a new Machine. Choose your current OS and name it as you like.
- During the process create a new Boot Hard Disk and give it the same size as your current system partition (maybe less if you know the size will be enough).
- Adjust the settings of your new machine and start it.
- Mount the Windows installation disc and install Windows. This is so that a proper MBR (Master Boot Record) is created because we will need one in order to boot from the final system.
- After the Windows installation (as soon as you see the Windows Start Menu and stuff), mount the Acronis Bootable Rescue Media ISO as a CD/DVD and boot from it.
- Select Acronis True Image Home (Full Version) and you'll see the TrueImage program like you did in Windows.
- Dismount the current CD/DVD image.
- Mount your the ISO with your backup file.
- Click on Manage and Restore => Browse for backup... and open the CD/DVD Drive which should have the backup image file right there. If it doesn't, try unmounting and remounting it again.
- Right-click on the backup in the list and select Restore.
- Select the hard disk (partition) you just created and continue. Make sure that the Restore MBR (Master Boot Recover) / Track 0 checkbox is unchecked.
- Let it run through.
- Now reboot your machine.
- Congratulations! You should have a working copy of your live system in VirtualBox!
I guess there are a couple of kinks here and there because I didn't actually do it like that (I installed Windows on the virtual machine before I attempted to restore via Acronis Bootable Rescue Media ... yeah, silly me 😀 ) and it was about 3 days ago, so I'm writing off the top of my head.
Please give me feedback if it worked! (And also if it didn't!) 🙂
In the case of Windows XP (and probably Vista and 7) because of the major change of hardware (actual drivers replaced by virtual drivers) you will be required to re-activate your Windows copy. XP gives you a maximum of 3 days for that.
Just thought I'd mention it for clarity's sake 🙂
Good luck and have fun!