How To Create and Manage Databases in MySQL and MariaDB

How To Create and Manage Databases in MySQL and MariaDB

CeleriumMind-Database


 

What are MySQL and MariaDB?

MySQL and MariaDB are relational database management systems that implement forms of the SQL querying language and are some of the most popular open source databases.

In this quick guide I will cover how to create a database using these tools. This is a fundamental skill needed to manage your data in a SQL environment. I will also go over several other examples of how to work with theses databases.

For the purposes of this guide, I will be using a CentOS 7 x64 minimal installation. However, everything should translate directly to other distributions just fine.

How to Create a Database in MySQL and MariaDB

To begin, sign into MySQL or MariaDB with the following command:

mysql -u root -p

Enter the administrator password you set up during installation. You will be given a MySQL/MariaDB prompt.

We can now create a database by typing the following command:

CREATE DATABASE new_database;
Query OK, 1 row affected (0.00 sec)

To avoid errors in the event that the database name we’ve chosen already exists, use the following command:

CREATE DATABASE IF NOT EXISTS new_database;
Query OK, 1 row affected, 1 warning (0.01 sec)

The warning indicates that the database already existed and no new database was created.

If we leave the “IF NOT EXISTS” option off, and the database already exists, we will receive the following error:

ERROR 1007 (HY000): Can't create database 'other_database'; database exists

How to View Databases in MySQL and MariaDB

To view a list of the current databases that you have created, use the following command:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new_database       |
| other_database     |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

The “information_schema”, “performance_schema”, and “mysql” databases are set up by default in most cases and should be left alone unless you know what you are doing.

How to Change Databases in MySQL and MariaDB

Any operations performed without explicitly specifying a database will be performed on the currently selected database.

Find out which database is currently selected with the following command:

SELECT database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.01 sec)

We have received a result of “null”. This means that no database is currently selected.

To select a database to use for subsequent operations, use the following command:

USE new_database;
Database changed

We can see that the database has been selected by re-issuing the command we ran previously:

SELECT database();
+--------------+
| database()   |
+--------------+
| new_database |
+--------------+
1 row in set (0.00 sec)

How to Delete a Database in MySQL and MariaDB

To delete a database in MySQL or MariaDB, use the following command:

DROP DATABASE new_database;
Query OK, 0 rows affected (0.00 sec)

This operation cannot be reversed! Make certain you wish to delete before pressing enter!

If this command is executed on a database that does not exist, the following error message will be given:

DROP DATABASE new_database;
ERROR 1008 (HY000): Can't drop database 'new_database'; database doesn't exist

To prevent this error, and ensure that the command executes successfully regardless of if the database exists, call it with the following syntax:

DROP DATABASE IF EXISTS new_database;
Query OK, 0 rows affected, 1 warning (0.00 sec)

The warning indicates that the database did not exist, but the command executes successfully anyways.

Conclusion

You now have the basic skills necessary to manage databases using MySQL and MariaDB. There are many things to learn, but you now have a good starting point to manage your databases. I myself am still new to databases and am learning new things constantly.  I hope this quick how to guide helps you start your journey into databases and the potential they have.

Creating Your Back Up Plan: Backing Up 101

DataBackup_Banner


Because data is the heart of the enterprise, it’s crucial for you to protect it. And to protect your organization’s data, you need to implement a data backup and recovery plan. Backing up files can protect against accidental loss of user data, database corruption, hardware failures, and even natural disasters. It’s your job as an administrator to make sure that backups are performed and that backup tapes are stored in a secure location.

Creating a Backup and Recovery Plan:

Data backup is an insurance plan. Important files are accidentally deleted all the time. Mission-critical data can become corrupt. Natural disasters can leave your office in ruin. With a solid backup and recovery plan, you can recover from any of these. Without one, you’re left with nothing to fall back on.

Figuring Out a Backup Plan

It takes time to create and implement a backup and recovery plan. You’ll need to figure out what data needs to be backed up, how often the data should be backed up, and more. To help you create a plan, consider the following:

  • How important is the data on your systems? The importance of data can go a long way in helping you determine if you need to back it up—as well as when and how it should be backed up. For critical data, such as a database, you’ll want to have redundant backup sets that extend back for several backup periods. For less important data, such as daily user files, you won’t need such an elaborate backup plan, but you’ll need to back up the data regularly and ensure that the data can be recovered easily.
  • What type of information does the data contain? Data that doesn’t seem important to you may be very important to someone else. Thus, the type of information the data contains can help you determine if you need to back up the data—as well as when and how the data should be backed up.
  • How often does the data change? The frequency of change can affect your decision on how often the data should be backed up. For example, data that changes daily should be backed up daily.
  • How quickly do you need to recover the data? Time is an important factor in creating a backup plan. For critical systems, you may need to get back online swiftly. To do this, you may need to alter your backup plan.
  • Do you have the equipment to perform backups? You must have backup hardware to perform backups. To perform timely backups, you may need several backup devices and several sets of backup media. Backup hardware includes tape drives, optical drives, and removable disk drives. Generally, tape drives are less expensive but slower than other types of drives.
  • Who will be responsible for the backup and recovery plan? Ideally, someone should be a primary contact for the organization’s backup and recovery plan. This person may also be responsible for performing the actual backup and recovery of data.
  • What is the best time to schedule backups? Scheduling backups when system use is as low as possible will speed the backup process. However, you can’t always schedule backups for off-peak hours. So you’ll need to carefully plan when key system data is backed up.
  • Do you need to store backups off-site? Storing copies of backup tapes off-site is essential to recovering your systems in the case of a natural disaster. In your off-site storage location, you should also include copies of the software you may need to install to reestablish operational systems.
The Basic Types of Backup

There are many techniques for backing up files. The techniques you use will depend on the type of data you’re backing up, how convenient you want the recovery process to be, and more.

If you view the properties of a file or directory in Windows Explorer, you’ll note an attribute called Archive. This attribute often is used to determine whether a file or directory should be backed up. If the attribute is on, the file or directory may need to be backed up. The basic types of backups you can perform include

  • Normal/full backups All files that have been selected are backed up, regardless of the setting of the archive attribute. When a file is backed up, the archive attribute is cleared. If the file is later modified, this attribute is set, which indicates that the file needs to be backed up.
  • Copy backups All files that have been selected are backed up, regardless of the setting of the archive attribute. Unlike a normal backup, the archive attribute on files isn’t modified. This allows you to perform other types of backups on the files at a later date.
  • Differential backups Designed to create backup copies of files that have changed since the last normal backup. The presence of the archive attribute indicates that the file has been modified and only files with this attribute are backed up. However, the archive attribute on files isn’t modified. This allows you to perform other types of backups on the files at a later date.
  • Incremental backups Designed to create backups of files that have changed since the most recent normal or incremental backup. The presence of the archive attribute indicates that the file has been modified and only files with this attribute are backed up. When a file is backed up, the archive attribute is cleared. If the file is later modified, this attribute is set, which indicates that the file needs to be backed up.
  • Daily backups Designed to back up files using the modification date on the file itself. If a file has been modified on the same day as the backup, the file will be backed up. This technique doesn’t change the archive attributes of files.

In your backup plan you’ll probably want to perform full backups on a weekly basis and supplement this with daily, differential, or incremental backups. You may also want to create an extended backup set for monthly and quarterly backups that includes additional files that aren’t being backed up regularly.

Tip You’ll often find that weeks or months can go by before anyone notices that a file or data source is missing. This doesn’t mean the file isn’t important. Although some types of data aren’t used often, they’re still needed. So don’t forget that you may also want to create extra sets of backups for monthly or quarterly periods, or both, to ensure that you can recover historical data over time.

Differential and Incremental Backups

The difference between differential and incremental backups is extremely important. To understand the distinction between them, examine table below. As it shows, with differential backups you back up all the files that have changed since the last full backup (which means that the size of the differential backup grows over time). With incremental backups, you only back up files that have changed since the most recent full or incremental backup (which means the size of the incremental backup is usually much smaller than a full backup).

Incremental and Differential Backup Techniques:

Day of Week Weekly Full Backup with Daily Differential Backup Weekly Full Backup with Daily Incremental Backup
Sunday A full backup is performed. A full backup is performed.
Monday A differential backup contains all changes since Sunday. An incremental backup contains changes since Sunday.
Tuesday A differential backup contains all changes since Sunday. An incremental backup contains changes since Monday.
Wednesday A differential backup contains all changes since Sunday. An incremental backup contains changes since Tuesday.
Thursday A differential backup contains all changes since Sunday. An incremental backup contains changes since Wednesday.
Friday A differential backup contains all changes since Sunday. An incremental backup contains changes since Thursday.
Saturday A differential backup contains all changes since Sunday. An incremental backup contains changes since Friday.

Once you determine what data you’re going to back up and how often, you can select backup devices and media that support these choices. These are covered in the next section.

Selecting Backup Devices and Media

Many tools are available for backing up data. Some are fast and expensive. Others are slow but very reliable. The backup solution that’s right for your organization depends on many factors, including

  • Capacity The amount of data that you need to back up on a routine basis. Can the backup hardware support the required load given your time and resource constraints?
  • Reliability The reliability of the backup hardware and media. Can you afford to sacrifice reliability to meet budget or time needs?
  • Extensibility The extensibility of the backup solution. Will this solution meet your needs as the organization grows?
  • Speed The speed with which data can be backed up and recovered. Can you afford to sacrifice speed to reduce costs?
  • Cost The cost of the backup solution. Does it fit into your budget?
Common Backup Solutions

Capacity, reliability, extensibility, speed, and cost are the issues driving your backup plan. If you understand how these issues affect your organization, you’ll be on track to select an appropriate backup solution. Some of the most commonly used backup solutions include

  • Tape drives Tape drives are the most common backup devices. Tape drives use magnetic tape cartridges to store data. Magnetic tapes are relatively inexpensive but aren’t highly reliable. Tapes can break or stretch. They can also lose information over time. The average capacity of tape cartridges ranges from 100 MB to 2 GB. Compared with other backup solutions, tape drives are fairly slow. Still, the selling point is the low-cost.
  • Digital audio tape (DAT) drives DAT drives are quickly replacing standard tape drives as the preferred backup devices. DAT drives use 4 mm and 8 mm tapes to store data. DAT drives and tapes are more expensive than standard tape drives and tapes, but they offer more speed and capacity. DAT drives that use 4 mm tapes can typically record over 30 MB per minute and have capacities of up to 16 GB. DAT drives that use 8 mm tapes can typically record more than 10 MB per minute and have capacities of up to 36 GB (with compression).
  • Auto-loader tape systems Auto-loader tape systems use a magazine of tapes to create extended backup volumes capable of meeting the high-capacity needs of the enterprise. With an auto-loader system, tapes within the magazine are automatically changed as needed during the backup or recovery process. Most auto-loader tape systems use DAT tapes. The typical system uses magazines with between 4 and 12 tapes. The main drawback to these systems is the high cost.
  • Magnetic optical drives Magnetic optical drives combine magnetic tape technology with optical lasers to create a more reliable backup solution than DAT. Magnetic optical drives use 3.5-inch and 5.25-inch disks that look similar to floppies but are much thicker. Typically, magnetic optical disks have capacities of between 1 GB and 4 GB.
  • Tape jukeboxes Tape jukeboxes are similar to auto-loader tape systems. Jukeboxes use magnetic optical disks rather than DAT tapes to offer high-capacity solutions. These systems load and unload disks stored internally for backup and recovery operations. Their key drawback is the high cost.
  • Removable disks Removable disks, such as Iomega Jaz, are increasingly being used as backup devices. Removable disks offer good speed and ease of use for a single drive or single system backup. However, the disk drives and the removable disks tend to be more expensive than standard tape or DAT drive solutions.
  • Disk drives Disk drives provide the fastest way to back up and restore files. With disk drives, you can often accomplish in minutes what takes a tape drive hours. So when business needs mandate a speedy recovery, nothing beats a disk drive. The drawbacks to disk drives, however, are relatively high costs and less extensibility.

Before you can use a backup device, you must install it. When you install backup devices other than standard tape and DAT drives, you need to tell the operating system about the controller card and drivers that the backup device uses. For detailed information on installing devices and drivers, see the section of Chapter 2 entitled “Managing Hardware Devices and Drivers.”

Buying and Using Tapes

Selecting a backup device is an important step toward implementing a backup and recovery plan. But you also need to purchase the tapes or disks, or both, that will allow you to implement your plan. The number of tapes you need depends on how much data you’ll be backing up, how often you’ll be backing up the data, and how long you’ll need to keep additional data sets.

The typical way to use backup tapes is to set up a rotation schedule whereby you rotate through two or more sets of tapes. The idea is that you can increase tape longevity by reducing tape usage and at the same time reduce the number of tapes you need to ensure that you have historic data on hand when necessary.

One of the most common tape rotation schedules is the 10-tape rotation. With this rotation schedule, you use 10 tapes divided into two sets of 5 (one for each weekday). As shown in the table below, the first set of tapes is used one week and the second set of tapes is used the next week. On Fridays, full backups are scheduled. On Mondays through Thursdays, incremental backups are scheduled. If you add a third set of tapes, you can rotate one of the tape sets to an off-site storage location on a weekly basis.

Using Incremental Backups:

Day of Week Tape Set 1 Tape Set 2
Friday Full backup on Tape 5 Full backup on Tape 5
Monday Incremental backup on Tape 1 Incremental backup on Tape 1
Tuesday Incremental backup on Tape 2 Incremental backup on Tape 2
Wednesday Incremental backup on Tape 3 Incremental backup on Tape 3
Thursday Incremental backup on Tape 4 Incremental backup on Tape 4

Tip The 10-tape rotation schedule is designed for the 9 to 5 workers of the world. If you’re in a 24 x 7 environment, you’ll definitely want extra tapes for Saturday and Sunday. In this case, use a 14-tape rotation with two sets of 7 tapes. On Sundays, schedule full backups. On Mondays through Saturdays, schedule incremental backups.

 

Seagate Backup Plus For Mac

seagate_backup_plusSeagate Backup Plus for Mac and Windows


 

The Backup Plus portable drive for Mac from Seagate is the simple way to protect and share your entire digital life—without getting in the way of the rest of your life.

  • Mac OS and Time-Machine® ready out of the box
  • Automatically save photos from your social networks
  • Share photos and video to social networks with a click
  • Easily increase transfer speeds by upgrading to Thunderbolt™ technology
  • compatible with Windows using our HFS+ Driver

 

Intro:

For this article I will be going over the Seagate Backup Plus for Mac.  This is small compact HDD with a terabyte of storage (930Gb usable) that you can take almost anywhere. And with USB 3.0 speeds you can transfer and store your data on this drive with lighting speed.

What’s in the Box!

Inside the box you will find your typical accessories.

  • Seagate Backup Plus drive
  • Seagate Dashboard pre-loaded on drive (And some other worthless data files)
  • 18-inch USB 3.0 cable
  • Quick start guide
  • 2-year limited warranty

Does it work with Mac?

Sure….I dont have a Mac, I run Windows for most desktops and Red hat Linux for all my servers. Its built for Mac so there really should be any issue with it being plug and play with a Mac.

Does it work with Windows?

Yes it does but you will need to download the HFS+ driver from Seagate in order to use the external HDD. You can download the driver here. Once you download this driver you will need to launch the installer and restart your computer.  Ya you need to restart your computer to have an external HDD driver work.  There is no plug and play with this HDD for windows.  Well let me clarify that there is plug and play after you run through the initial setup steps.

First Use, requires some first steps:

Once you install the HFS+ driver and restart you computer the Seagate Backup Plus will show up on your system as a usable drive.  Though when I clicked into this drive I was a little horrified by the amount of junk and worthless material in the drive. There was a damaged install for the Backup Dashboard and a undamaged version of it as well. Guess Seagate didn’t feel the need to remove this before shipping it out to consumers.  Also Seagate, I understand the idea of putting in videos showing users how to work with the backup dashboard, but my god do the videos have to be such poor quality!  320x180x is barely readable and not to mention that the videos as is take up almost 400mb of space of the driver due to the fact that each video is in 8 different languages.

Though I can’t really complain too much as its my standard operating procedure to just factory wipe devices that come straight from the factory and Seagate has the right to promote their product. But Seagate if you ever read this please take another look at the amount and type of files you put onto your external drives.

So I just went ahead and formated the entire drive to wipe out and clean the bloated junk that I was never going to use.  Also if I ever wanted to get them back its as easy as going to their website and downloading them again.

Dashboard:

The dashboard itself is very easy to use and has some interesting features that can be useful for some people. Take a look that the pictures below for installing and using the dashboard to back up your important files.

Speed Transfers:

Speed transfers can vary according to the type and format of the file  you are transferring. For example one 4.0Gb DVD movie transferred at 40Mbs while a 4.0Gb folder with pictures and documents in it transferred around 25Mbs.  Now this does not mean that the drive is bad or slow.  This just means a computer can transfer one file faster than it can with a 1000 images.

Overview:

All in all I like this external drive.  Now with that being said I wouldn’t say this is the best external drive in the world and the bloated features that come with the Seagate Backup Plus are not something to be mess around with and more or less with just give you hassle in the long run. Its best just to wipe the drive and start with a truly new and ready to use external drive. Though once you format the drive its will work prefect for storing files and keeping important information safe.

Thank you


logo3


 

Windows Photo Viewer shows pictures with yellow tint

Windows_logo-4Fixing Windows Photo Viewer when pictures/background have a yellow tint

I recently saw a really good deal on NewEgg for some Dell S2340M monitors and I just could resist.  After hooking up the monitors everything look amazing on them until I started opening images up in Window Photo Viewer.  Everything had an orange/yellow hugh in Windows Photo Viewer.  At first I didn’t think anything of it, thinking maybe I just messed around with some background setting or something like that. Then I was looking at an image that had quite a bit of white in the image yet even the image seemed yellow.  So I did a quick Google search and the first thing I see is a Microsoft KB article talking about just this issue. Not even a minute later I applied the fix and everything was back to normal.

Fixing The Problem

To fix this problem, you have to remove the incorrect color profile. To do this, follow these steps:

  1. Click Start, type Color Management in the Start Search box, and then press ENTER.
  2. In the Color Management dialog box, click to select the Use my settings for this device check box.
  3. In the Profiles associated with this device list, click the color profile that you want to remove, and then click Remove.
    Note If you receive a warning message, click Yes.
  4. Close the Color Management dialog box and then restart the computer to apply the setting.

 

Increase FSRM Search Limitations


Increase FSRM Search Limitations


windows-server-2012-logo

Overview:

By default FSRM has some search limitations varying from 10 to 1000 files on a system.  I needed to remedy this issue so that I could search a huge file structure and view all the files it contained rather than the first 10 or 1000. So to start off let’s get a general understanding of some the of search limitations that FSRM has set by default.

ReportLimits1

Useful information especially if your new to FSRM and are trying to decipher some of the cloudy errors it outputs. Keep in mind as well all these search options above can be change at will.

Default Values for FSRM:

ReportLimits2

How to Change Search Limitations:

To start off you will first need to open PowerShell as an administrator.

ReportLimits4.jpg-550x0

Next run the following command to increase the max files per property value.  This command will enable a report to contain up to 1 million files.

Set-FsrmSetting –ReportLimitMaxFilesPerPropertyValue 1000000 –PassThru

After running this command PowerShell will output a summary of the current FSRM parameter values.ReportLimits5.jpg-550x0

Parameter Name translations:

Reporting Limit Constant Name

Parameter Name

FsrmReportLimit_MaxFiles ReportLimitMaxFile
FsrmReportLimit_MaxFileGroups ReportLimitMaxFileGroup
FsrmReportLimit_MaxOwners ReportLimitMaxOwner
FsrmReportLimit_MaxFilesPerFileGroup ReportLimitMaxFilesPerFileGroup
FsrmReportLimit_MaxFilesPerOwner ReportLimitMaxFilesPerOwner
FsrmReportLimit_MaxFilesPerDuplGroup ReportLimitMaxFilesPerDuplicateGroup
FsrmReportLimit_MaxDuplicateGroups ReportLimitMaxDuplicateGroup
FsrmReportLimit_MaxQuotas ReportLimitMaxQuota
FsrmReportLimit_MaxFileScreenEvents ReportLimitMaxFilesScreenEvent
FsrmReportLimit_MaxPropertyValues ReportLimitMaxPropertyValue
FsrmReportLimit_MaxFilesPerPropertyValue ReportLimitMaxFilesPerPropertyValue

Replace the parameter name with the one you wish to change, and what will happen is that the FSRM API is being called from PowerShell cmdlets which in turn provides a new value

Issue the new command in the PowerShell command window to match the structure:

Set-FsrmSetting – –PassThru

SUMMARY

Once you have executed a command successfully, you should see a new summary of active FSRM Reporting Constants displayed with your new value associated to the parameter you specified.   If you got an error message instead, it’s likely you misspelled some part of the command or parameters, supplied an invalid value, or do not have the appropriate privileges to execute FSRM commands. So I hope this quick overview on how to increase FSRM storage search limitations was helpful.

Thank you

CeleriumMind


cropped-logo3.png


Enable Last Accessed Time for NTFS Files


Enable Last Accessed Time for NTFS Files


NTFSlogo

Overview:

While recently working with FSRM reporting tools on a new server 2012 instance I received an error when running a FSRM report with the option to search for “Least Recently Accessed File” against a directory structure. To be a little more specific I received this error when opening the “Dhtml” FSRM report log.

So after doing a quick Google searching I was able to come across a technet article about this exact same issue.

FSRM Error

This File Server is currently configured not to track last access time for files, therefore the last access time might be inaccurate for some or all files on the NTFS volumes.To fix this issue you need to enable some registry settings

FSRMError_1

 

To fix this issue to open “regedit” and navigate to

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem

Find the “NtfsDisableLastAccessUpdate” key and change the D-word from 1 to 0 to enable.

FSRMRegEdit

 

And really its as simple as that, once you change over this registry setting that error you saw every time you opened a least accessed file report from FSRM will be gone.


cropped-logo3.png