Tag Archives: MySQL
Using PowerShell to backup your MySQL databases
A few months ago we had to find a way to backup our MySQL databases, both remotely and locally. Instead of trying to find a software that did something similar I decided to write a few lines of code that solved our problem. Why spend time and money evaluating software when can create your own solution to the problem with little to no cost, right ?
The snippet below is fairly simple. The $databases variable holds an array of all the databases you want to dump. You will need to specify the database name, the path you wish to save the backup and the backup filename. I took the liberty of commenting the code in order to make it a bit more clear.
Please keep in mind that this solution uses mysqldump. This executable is usually located under C:\Program Files\MySQL\MySQL Server 5.6\bin, assuming you have MySQL server installed. In addition to mysqldump, since all our Windows servers we have WinRAR installed, this snippet uses WinRAR in order to compress the database files. You can always modify the code either skip the compression or to use .NET’s ZipFile class or 7zip.
This solution still needs some tweaking. For example, creating a temporary file with the passwords and passing it as an argument to mysqldump to avoid the “Specifying a password on the command line should be considered insecure” message, as well as some code cleanup and error catching. Until then, you are free to modify this example to better suit your needs.
If you have any questions feel free to post them in the comment section below.
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 | function ZipAndDeleteFile([string] $file, [string] $saveLocation) { $command = [string]::Format("`"{0}`" a -ep -df `"$saveLocation`" `"$file`"", $winrarPath); iex "& $command"; } function GetCurrentDateTime { return Get-Date -Format yyyyMMdd_HHmmss; } Class DatabaseEntry { [string] $DatabaseName; [string] $DatabaseBackupPath; [string] $DatabaseFilename; DatabaseEntry([string] $databaseName, $databaseBackupPath, $databaseFilename) { $this.DatabaseName = $databaseName; $this.DatabaseBackupPath = $databaseBackupPath; $this.DatabaseFilename = $databaseFilename; } } $debugMode = $false; # Show errors from mysqldump $winrarPath = "C:\Program Files\WinRAR\WinRAR.exe"; # Path to WinRAR $logFilePath = "C:\Database Backups\"; # Folder where the log file will be created in $logFileName = "backup_log.log"; # Filename of the log file $logFileFullPath = [io.path]::combine($logFilePath, $logFileName); # The full path of the log file (ignore this, it will get populated from the 2 variables above) $mysqlDumpLocation = "C:\MySQL\mysqldump.exe"; # Path to the mysqldump executable. This is required in order to be able to dump the databases. <# The path where the database files will be stored. This is simply here to speed up the assigning of paths for the DatabaseEntry objects. You can set an entirely different path for each database. #> $databaseBackupLocation = "C:\Database Backups\MySQL"; $databaseIp = "<Your MySQL server IP>"; # Database IP or Hostname $databaseUsername = "<MySQL User>"; # Database username (it should have access to all the databases you need to backup) $databasePassword = "<MySQL password for user>"; # The password of the user <# Here is where you specify which databases you want to backup. The DatabaseEntry object takes three arguments: 1) The database name, this should match the name of the database you have in your MySQL server 2) The path where you want the database to be stored (without the filename) 3) The filename of backup file without the extension (a timestamp will be added automatically so you do need to worry about it) #> $databases = New-Object System.Collections.ArrayList; [void]$databases.Add([DatabaseEntry]::New("database1", [io.path]::combine($databaseBackupLocation, "database1_foldername"), "database1")); [void]$databases.Add([DatabaseEntry]::New("database2", [io.path]::combine($databaseBackupLocation, "database2_foldername"), "database2")); [void]$databases.Add([DatabaseEntry]::New("database3", [io.path]::combine($databaseBackupLocation, "database3_foldername"), "database3")); md -Force $logFilePath | Out-Null # Create the path for the log file Out-File $logFileFullPath -InputObject "Starting backup operation" -Append; # Iterate and process all the database entries foreach ($database in $databases.GetEnumerator()) { try { Out-File $logFileFullPath -InputObject ([string]::Format("Backing up {0}...",$database.DatabaseFilename)) -Append; $date = GetCurrentDateTime; md -Force $database.DatabaseBackupPath | Out-Null $saveFilePath = [string]::format("{0}\{1}_{2}.sql", $database.DatabaseBackupPath, $database.DatabaseFilename, $date); $command = [string]::format("`"{0}`" -u {1} -p{2} -h {3} --quick --default-character-set=utf8 --routines --events `"{4}`" > `"{5}`"", $mysqlDumpLocation, $databaseUsername, $databasePassword, $databaseIp, $database.DatabaseName, $saveFilePath); $mysqlDumpError; Invoke-Expression "& $command" -ErrorVariable mysqlDumpError; # Execute mysqldump with the required parameters for each database # If debug mode is on then you will see the errors mysqldump generates in the log file. if ($debugMode -eq $true) { Out-File $logFileFullPath -InputObject $mysqlDumpError -Append; } $zipFileLocation = [string]::format("{0}\{1}_{2}.zip", $database.DatabaseBackupPath, $database.DatabaseFilename, $date); ZipAndDeleteFile $saveFilePath $zipFileLocation; # Zip the file and delete the file that was zipped. $logEntry = [string]::Format("[{0}] Successfully backed up {1}", $date, $database.DatabaseName); Out-File $logFileFullPath -InputObject $logEntry -Append; } catch [Exception] { $exceptionMessage = $_.Exception.Message; $logEntry = [string]::Format("[{0}] Failed to backup up {1}. Reason: {2}", $date, $database.DatabaseName, $exceptionMessage); Out-File $logFileFullPath -InputObject $logEntry -Append; } } Out-File $logFileFullPath -InputObject "Backup operation completed" -Append; |
Posted in PowerShell.
Tagged database, MySQL, PowerShell
Create a MySQL table dynamically in C#
Sometimes you might need to create database tables dynamically, based on new users, products etc. The snippet below allows you to do exactly that, create a new table in your database dynamically, straight from your code.
This example is heavily based on How to connect to a MySQL database and retrieve data in C# as the same concept of connecting to the database is required.
The first thing you need to do, as mentioned in the article linked above, is that you will need to add a reference to MySQL.Data in your project. After you have done that, you can modify the snippet below to create the table with the columns you want based on your requirements.
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 | string createTableQuery = string.Format(@"CREATE TABLE `{0}` ( `sid` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(120) NOT NULL DEFAULT '', `title` varchar(120) NOT NULL DEFAULT '', `description` text NOT NULL, `optionscode` text NOT NULL, `value` text NOT NULL, `disporder` smallint(5) unsigned NOT NULL DEFAULT '0', `gid` smallint(5) unsigned NOT NULL DEFAULT '0', `isdefault` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`sid`), KEY `gid` (`gid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;", "YourTableName"); string serverIp = "<Server IP or Hostname>"; string username = "<DB username>"; string password = "<DB password>"; string databaseName = "<DB name>"; string dbConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};", serverIp, username, password, databaseName); var conn = new MySql.Data.MySqlClient.MySqlConnection(dbConnectionString); conn.Open(); var cmd = new MySql.Data.MySqlClient.MySqlCommand(createTableQuery, conn); cmd.ExecuteNonQuery(); |
If you are unsure as to which database engine to use, please take a look at https://dev.mysql.com/doc/refman/5.0/en/storage-engines.html as every database engine has different type of limitations, so it is better to choose the one that better suits your needs.
How to connect to a MySQL database and retrieve data in C#
Connecting to a MySQL database is fairly simple and straight forward. The first thing you will need to do is to add a reference to MySQL.Data in your project.
This can be done in a few ways:
- Right click on references in your project -> click Manage NuGet packages -> Search and install MySQL.Data from Oracle
- Open Package Manager Console from Tools -> NuGet Package Manager -> Paste in the console the following line:
Install-Package MySql.Data - If you have installed the .NET Connector from MySQL you can navigate to where you have installed the connector and add a reference to the MySQL.Data.dll file
After you have added a reference to MySQL.Data you can tailor the snippet below to suit your requirements.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | string serverIp = "<Server IP or Hostname>"; string username = "<DB username>"; string password = "<DB password>"; string databaseName = "<DB name>"; string dbConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};", serverIp, username, password, databaseName); string query = "SELECT * FROM YourTable"; var conn = new MySql.Data.MySqlClient.MySqlConnection(dbConnectionString); conn.Open(); var cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn); var reader = cmd.ExecuteReader(); while (reader.Read()) { var someValue = reader["SomeColumnName"]; // Do something with someValue } |
If you are interested in other type of databases you can check my Microsoft Access Database and SQLite database tutorials.
Top 5 Most Popular Databases 2014
While parsing the data for Top 10 Most Popular Programming Languages 2014 I decided to make another chart that would illustrate which are the top 5 most popular databases in 2014.
As I have mentioned in the post above as well, the chart below is nowhere near 100% accurate as there are a lot of variations for each tag.
For my findings I used Stack Overflow’s database to query the posts that were created in 2014, then split and shorted the tags based on how many times they appeared in the posts. After parsing and processing 9307 unique tags with a total of 97386 occurrences, these are the results.
Raw Data
| Tag Name | Tag Count |
|---|---|
| MySQL | 54894 |
| SQL-Server | 25164 |
| MongoDB | 7605 |
| SQLite | 5090 |
| PostgreSQL | 4633 |
Posted in Tech News.
Tagged database, MongoDB, MySQL, PostgreSQL, SQL-Server, SQLite
