MySQL Database Import And Export Operations

Take Backup With Data

Open Command Prompt (cmd)
# mysqldump -u dbusername -p db_name > /tmp/db_fullbackup.sql

Take Only Metadata Backup


# mysqldump -u dbusername -p –no-data db_name > /tmp/db_backupmetadata.sql
or
# mysqldump -u dbusername -p -d db_name > /tmp/db_backupmetadata.sql

Restore Database Metadata From Backup


# mysql -u dbusername -p db_name < /tmp/db_backupmetadata.sql

Restore Database From Backup


# mysql -u dbusername -p db_name < /tmp/db_fullbackup.sql

MySQL Frequently Using Commands

MySQL is one of the most using database system. Almost all web sites are using MySQL database. I wrote most using commands of MySQL database.I will share various commands using examples for MySQL database .



Connect MySQL


[root@testdb ~]# mysql
[root@testdb ~]# mysql -h hostip
[root@testdb ~]# mysql -h hostip -u username
[root@testdb ~]# mysql dbname -u username
[root@testdb ~]# mysql dbname -u username -P portnumber

List, Add, Drop MySQL Database


List databases

mysql> show databases;

Add database

mysql> create database testdb

Drop database

mysql> drop database testdb

List, Add, Drop, Change and Grant User


mysql> use mysql
mysql> create user facar;
mysql> drop user facar;
mysql> update user set password=PASSWORD(“newpassword”) where user=’facar’;
mysql> grant all privileges on testdb.* to facar;
mysql> grant all privileges on *.* to facar;
mysql> revoke all privileges on testdb.* from facar;
mysql> revoke all privileges on *.* from facar;
mysql> flush privileges;
mysql> grant usage on testdb.* to facar identified by ‘password’;
mysql> grant usage on *.* to facar identified by ‘password’;
mysql> revoke usage on testdb.* from facar
mysql> revoke usage on *.* from facar
mysql> flush privileges;

Information Queries of MySQL Database


mysql> help;

mysql> use testdb;
mysql> show tables;
mysql> desc tablename;

mysql> connect mysql;
mysql> select user();

mysql> show variables;
mysql> show variables where variable_name = ‘Port’;

[root@testdb ~]# mysqladmin –help
[root@testdb ~]# mysqladmin –version
[root@testdb ~]# mysqladmin ping
[root@testdb ~]# mysqladmin variables

Linux: Find And Delete Files Greater Than Given Size

If you want to delete files which are greater than given size on Linux, you can use below examples by changing as your need.

Delete Files


Delete files which are size more than 2 MB. and type is jgp.

[root@testserver ~]# find /tmp/ -type f -size +2M -name “*.jpg” -exec rm -rf {} \;

Delete files which are size more than 2 MB low than 5 MB. and type is jpg.

[root@testserver ~]# find /tmp/ -type f -size +2M -size -5M -name “*.jpg” -exec rm -rf {} \;

Delete files which are size low than 10 MB. and type is zip.

[root@testserver ~]# find /tmp/ -type f -size -10M -name “*.zip” -exec rm -rf {} \;

MySQL Function: GROUP_CONCAT

Don’t know why I never thought to google for something like this before. There probably aren’t too many good uses for it, but it saved me a bunch of time today so I thought I’d share:

GROUP_CONCAT

Running a query like the one below, would return all the values from my sub-select as a comma delimited list so this…

SELECT products.namename,
        GROUP_CONCAT(options.option) AS options
FROM products
     INNER JOIN options
     ON          options.productID = products.productID

Could return something like this:

NAME	OPTIONS
t-shirt	green, blue, red, small, medium, large
hat	cowboy

MySQL Capitalize Function

I wrote a little MySQL function to capitalize the first letter of every word in a string. I thought I’d share since I wasn’t able to google for it.

CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN
 DECLARE len INT;
 DECLARE i INT;

 SET len   = CHAR_LENGTH(input);
 SET input = LOWER(input);
 SET i = 0;

 WHILE (i < len) DO
  IF (MID(input,i,1) = ' ' OR i = 0) THEN
   IF (i < len) THEN
    SET input = CONCAT(
     LEFT(input,i),
     UPPER(MID(input,i + 1,1)),
     RIGHT(input,len - i - 1)
    );
   END IF;
  END IF;
  SET i = i + 1;
 END WHILE;

 RETURN input;
END;

• 

So running the following code...

SELECT CAP_FIRST(
 'this is totally like   @ TEST 1 right!' 
)

Returns the string "This Is Totally Like @ Test 1 Right!"

I would rather have regex'd it, but I couldn't find any sort of regex replace function in the docs.

GIT: Introduction into Git

What is Git?

Git is currently the most popular implementation of a distributed version control system.

Git originates from the Linux kernel development and was founded in 2005 by Linus Torvalds. Nowadays it is used by many popular open source projects, e.g., the Android or the Eclipse developer teams, as well as many commercial organizations.

The core of Git was originally written in the programming language C, but Git has also been re-implemented in other languages, e.g., Java, Ruby and Python.

Git repositories

A Git repository contains the history of a collection of files starting from a certain directory. The process of copying an existing Git repository via the Git tooling is called cloning. After cloning a repository the user has the complete repository with its history on his local machine. Of course, Git also supports the creation of new repositories.

If you want to delete a Git repository, you can simply delete the folder which contains the repository.

If you clone a Git repository, by default, Git assumes that you want to work in this repository as a user. Git also supports the creation of repositories targeting the usage on a server.

• bare repositories are supposed to be used on a server for sharing changes coming from different developers. Such repositories do not allow the user to modify locally files and to create new versions for the repository based on these modifications.
• non-bare repositories target the user. They allow you to create new changes through modification of files and to create new versions in the repository. This is the default type which is created if you do not specify any parameter during the clone operation.

A local non-bare Git repository is typically called local repository.

Working tree

A local repository provides at least one collection of files which originate from a certain version of the repository. This collection of files is called the working tree. It corresponds to a checkout of one version of the repository with potential changes done by the user.

The user can change the files in the working tree by modifying existing files and by creating and removing files.

A file in the working tree of a Git repository can have different states. These states are the following:

• untracked: the file is not tracked by the Git repository. This means that the file never staged nor committed.
• tracked: committed and not staged
• staged: staged to be included in the next commit
• dirty / modified: the file has changed but the change is not staged

After doing changes in the working tree, the user can add these changes to the Git repository or revert these changes.

Adding to a Git repository via staging and committing

After modifying your working tree you need to perform the following two steps to persist these changes in your local repository:

• add the selected changes to the staging area (also known as index) via the git add command
• commit the staged changes into the Git repository via the git commit command

This process is depicted in the following graphic.

The git add command stores a snapshot of the specified files in the staging area. It allows you to incrementally modify files, stage them, modify and stage them again until you are satisfied with your changes.

Some tools and Git user prefer the usage of the index instead of staging area. Both terms mean the same thing.

After adding the selected files to the staging area, you can commit these files to add them permanently to the Git repository. _ Committing_ creates a new persistent snapshot (called commit or commit object) of the staging area in the Git repository. A commit object, like all objects in Git, is immutable.

The staging area keeps track of the snapshots of the files until the staged changes are committed.

For committing the staged changes you use the git commit command.

If you commit changes to your Git repository, you create a new commit object in the Git repository. See Commit object (commit) for information about the commit object.

Synchronizing with other Git repositories (remote repositories)

Git allows the user to synchronize the local repository with other (remote) repositories.

Users with sufficient authorization can send new version in their local repository to to remote repositories via the pushoperation. They can also integrate changes from other repositories into their local repository via the fetch and pulloperation.

The concept of branches

Git supports branching which means that you can work on different versions of your collection of files. A branch allows the user to switch between these versions so that he can work on different changes independently from each other.

For example, if you want to develop a new feature, you can create a branch and make the changes in this branch. This does not affect the state of your files in other branches. For example, you can work independently on a branch called production for bugfixes and on another branch called feature_123 for implementing a new feature.

Branches in Git are local to the repository. A branch created in a local repository does not need to have a counterpart in a remote repository. Local branches can be compared with other local branches and with _remote-tracking branches. A remote-tracking branch proxies the state of a branch in another remote repository.

Git supports the combination of changes from different branches. The developer can use Git commands to combine the changes at a later point in time.

Summary of the core Git terminology

The following table provides a summary of important Git terminology discussed in this section.

Table 1. Git terminology

Term	Definition
Branch	A branch is a named pointer to a commit. Selecting a branch in Git terminology is called to checkout a branch. If you are working in a certain branch, the creation of a new commit advances this pointer to the newly created commit. Each commit knows their parents (predecessors). Successors are retrieved by traversing the commit graph starting from branches or other refs, symbolic references (for example: HEAD) or explicit commit objects. This way a branch defines its own line of descendants in the overall version graph formed by all commits in the repository. You can create a new branch from an existing one and change the code independently from other branches. One of the branches is the default (typically named _master ). The default branch is the one for which a local branch is automatically created when cloning the repository.
Commit	When you commit your changes into a repository this creates a new commit object in the Git repository. This commit object uniquely identifies a new revision of the content of the repository. This revision can be retrieved later, for example, if you want to see the source code of an older version. Each commit object contains the author and the committer. This makes it possible to identify who did the change. The author and committer might be different people. The author did the change and the committer applied the change to the Git repository. This is common for contributions to open source projects.
HEAD	HEAD is a symbolic reference most often pointing to the currently checked out branch. Sometimes the HEAD points directly to a commit object, this is called detached HEAD mode. In that state creation of a commit will not move any branch. If you switch branches, the HEAD pointer points to the branch pointer which in turn points to a commit. If you checkout a specific commit, the HEAD points to this commit directly.
Index	Index is an alternative term for the staging area.
Repository	A repository contains the history, the different versions over time and all different branches and tags. In Git each copy of the repository is a complete repository. If the repository is not a bare repository, it allows you to checkout revisions into your working tree and to capture changes by creating new commits. Bare repositories are only changed by transporting changes from other repositories. This description uses the term repository to talk about a non-bare repository. If it talks about a bare repository, this is explicitly mentioned.
Revision	Represents a version of the source code. Git implements revisions as commit objects (or short commits ). These are identified by an SHA-1 hash.
Staging area	The staging area is the place to store changes in the working tree before the commit. The staging area contains a snapshot of the changes in the working tree (changed or new files) relevant to create the next commit and stores their mode (file type, executable bit).
Tag	A tag points to a commit which uniquely identifies a version of the Git repository. With a tag, you can have a named point to which you can always revert to. You can revert to any point in a Git repository, but tags make it easier. The benefit of tags is to mark the repository for a specific reason, e.g., with a release. Branches and tags are named pointers, the difference is that branches move when a new commit is created while tags always point to the same commit. Tags can have a timestamp and a message associated with them.
URL	A URL in Git determines the location of the repository. Git distinguishes between fetchurlfor getting new data from other repositories and pushurl for pushing data to another repository.
Working tree	The working tree contains the set of working files for the repository. You can modify the content and commit the changes as new commits to the repository.

GIT: Introduction into version control systems

What is a version control system?

A version control system (VCS) allows you to track the history of a collection of files. It supports creating different versions of this collection. Each version captures a snapshot of the files at a certain point in time and the VCS allows you to switch between these versions. These versions are stored in a specific place, typically called a repository.

You may, for example, revert the collection of files to a state from 2 days ago. Or you may switch between versions of your files for experimental features. The process of creating different versions (snapshots) in the repository is depicted in the following graphic. Please note that this picture fits primarily to Git. Other version control systems like Concurrent Versions System (CVS) don’t create snapshots of the files but store file deltas.

VCS are typically used to track changes in text files. These text files can for example be source code for a programming language, HTML or configuration files. Of course, version control systems are not limited to text files, they can also handle other types of files. For example, you may use a VCS to track the different versions of a png file.

Localized and centralized version control systems

A localized version control system keeps local copies of the files. This approach can be as simple as creating a manual copy of the relevant files.

A centralized version control system provides a server software component which stores and manages the different versions of the files. A developer can copy (checkout) a certain version from the central sever onto their individual computer.

Both approaches have the drawback that they have one single point of failure. In a localized version control systems it is the individual computer and in a centralized version control systems it is the server machine. Both system makes it also harder to work in parallel on different features.

Distributed version control systems

In a distributed version control system each user has a complete local copy of a repository on his individual computer. The user can copy an existing repository. This copying process is typically called cloning and the resulting repository can be referred to as a clone.

Every clone contains the full history of the collection of files and a cloned repository has the same functionality as the original repository.

Every repository can exchange versions of the files with other repositories by transporting these changes. This is typically done via a repository running on a server which is, unlike the local machine of a developer, always online. Typically, there is a central server for keeping a repository but each cloned repository is a full copy of this repository. The decision which of the copies is considered to be the central server repository is pure convention.