MySQL Backup and Restore Commands for Database Administration

How to Backup MySQL Database?

To take a backup of MySQL database or databases, the database must exist in the database server and you must have access to it. The format of the command would be.

# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

The parameters of the said command as follows.

1. [username] : A valid MySQL username.
2. [password] : A valid MySQL password for the user.
3. [database_name] : A valid Database name you want to take backup.
4. [dump_file.sql] : The name of backup dump file you want to generate.

How to Backup a Single MySQL Database?

To take a backup of single database, use the command as follows. The command will dump database [rsyslog] structure with data on to a single dump file called rsyslog.sql.

# mysqldump -u root -ptecmint rsyslog > rsyslog.sql

How to Backup Multiple MySQL Databases?

If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [rsyslog, syslog] structure and data in to a single file called rsyslog_syslog.sql.

# mysqldump -u root -ptecmint --databases rsyslog syslog > rsyslog_syslog.sql

How to Backup All MySQL Databases?

If you want to take backup of all databases, then use the following command with option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql.

# mysqldump -u root -ptecmint --all-databases > all-databases.sql

How to Backup MySQL Database Structure Only?

If you only want the backup of database structure without data, then use the option –no-data in the command. The below command exports database [rsyslog] Structure into a filersyslog_structure.sql.

# mysqldump -u root -ptecmint -–no-data rsyslog > rsyslog_structure.sql

How to Backup MySQL Database Data Only?

To backup database Data only without structure, then use the option –no-create-info with the command. This command takes the database [rsyslog] Data  into a file rsyslog_data.sql.

# mysqldump -u root -ptecmint --no-create-db --no-create-info rsyslog > rsyslog_data.sql

How to Backup Single Table of Database?

With the below command you can take backup of single table or certain tables of your database. For example, the following command only take backup of wp_posts table from the databasewordpress.

# mysqldump -u root -ptecmint wordpress wp_posts > wordpress_posts.sql

How to Backup Multiple Tables of Database?

If you want to take backup of multiple or certain tables from the database, then separate each table with space.

# mysqldump -u root -ptecmint wordpress wp_posts wp_comments > wordpress_posts_comments.sql

How to Backup Remote MySQL Database

The below command takes the backup of remote server [172.16.25.126] database [gallery] into a local server.

# mysqldump -h 172.16.25.126 -u root -ptecmint gallery > gallery.sql

How to Restore MySQL Database?

In the above tutorial we have seen the how to take the backup of databases, tables, structures and data only, now we will see how to restore them using following format.

# # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

How to Restore Single MySQL Database

To restore a database, you must create an empty database on the target machine and restore the database using msyql command. For example the following command will restore thersyslog.sql file to the rsyslog database.

# mysql -u root -ptecmint rsyslog < rsyslog.sql

If you want to restore a database that already exist on targeted machine, then you will need to use the mysqlimport command.

# mysqlimport -u root -ptecmint rsyslog < rsyslog.sql

In the same way you can also restore database tables, structures and data. If you liked this article, then do share it with your friends.

High Secure data Encryption Decryption in php

// Define a 32-byte (64 character) hexadecimal encryption key
// Note: The same encryption key used to encrypt the data must be used to decrypt the data
define('ENCRYPTION_KEY', 'd0a7e7997b6d5fcd55f4b5c32611b87cd923e88837b63bf2941ef819dc8ca282');

// Encrypt Function
function mc_encrypt($encrypt, $key){
$encrypt = serialize($encrypt);
$iv = mcrypt_create_iv(mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_CBC), MCRYPT_RAND);
$key = pack('H*', $key);
$mac = hash_hmac('sha256', $encrypt, substr(bin2hex($key), -32));
$passcrypt = mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $key, $encrypt.$mac, MCRYPT_MODE_CBC, $iv);
$encoded = base64_encode($passcrypt).'|'.base64_encode($iv);
return $encoded;
}

// Decrypt Function
function mc_decrypt($decrypt, $key){
$decrypt = explode('|', $decrypt);
$decoded = base64_decode($decrypt[0]);
$iv = base64_decode($decrypt[1]);
if(strlen($iv)!==mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_CBC)){ return false; }
$key = pack('H*', $key);
$decrypted = trim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $key, $decoded, MCRYPT_MODE_CBC, $iv));
$mac = substr($decrypted, -64);
$decrypted = substr($decrypted, 0, -64);
$calcmac = hash_hmac('sha256', $decrypted, substr(bin2hex($key), -32));
if($calcmac!==$mac){ return false; }
$decrypted = unserialize($decrypted);
return $decrypted;
}

echo '<h1>Rijndael 256-bit CBC Encryption Function</h1>';

$data = 'swargamm';
$encrypted_data = mc_encrypt($data, ENCRYPTION_KEY);
echo '<h2>Example #1: String Data</h2>';
echo 'Data to be Encrypted: ' . $data . '<br/>';
echo 'Encrypted Data: ' . $encrypted_data . '<br/>';
echo 'Decrypted Data: ' . mc_decrypt($encrypted_data, ENCRYPTION_KEY) . '</br>';

$data = array(1, 5, 8, 9, 22, 10, 61);
$encrypted_data = mc_encrypt($data, ENCRYPTION_KEY);
echo '<h2>Example #2: Non-String Data</h2>';
echo 'Data to be Encrypted: <pre>';
print_r($data);
echo '</pre><br/>';
echo 'Encrypted Data: ' . $encrypted_data . '<br/>';
echo 'Decrypted Data: <pre>';
print_r(mc_decrypt($encrypted_data, ENCRYPTION_KEY));
echo '</pre>';

HTTP response codes

HTTP Response Codes indicate whether a specific HTTP requests has been successfully completed. Responses are grouped in five classes: informational responses, successful responses, redirections, client errors, and servers errors.

The following table lists them all, with their respective meanings:

Status code	Status text	Description	HTTP version
Informational responses
100	Continue	This interim response indicates that everything so far is OK and that the client should continue with the request or ignore it if it is already finished.	HTTP/1.1 only
101	Switching Protocol	This code is sent in response to an Upgrade: request header by the client, and indicates that the protocol the server is switching too. It was introduced to allow migration to an incompatible protocol version, and is not in common use.	HTTP/1.1 only
Successful responses
200	OK	The request has succeeded. The meaning of a success varies depending on the HTTP method: GET: The resource has been fetched and is transmitted in the message body. HEAD: The entity headers are in the message body. POST: The resource describing the result of the action is transmitted in the message body. TRACE: The message body contains the request message as received by the server	HTTP/0.9 and later
201	Created	The request has succeeded and a new resource has been created as a result of it. This is typically the response sent after a PUT request.	HTTP/0.9 and later
202	Accepted	The request has been received but not yet acted upon. It is non-committal, meaning that there is no way in HTTP to later send an asynchronous response indicating the outcome of processing the request. It is intended for cases where another process or server handles the request, or for batch processing.	HTTP/0.9 and later
203	Non-Authoritative Information	This response code means returned meta-information set is not exact set as available from the origin server, but collected from a local or a third party copy. Except this condition, 200 OK response should be preferred instead of this response.	HTTP/0.9 and 1.1
204	No Content	There is no content to send for this request, but the headers may be useful. The user-agent may update its cached headers for this resource with the new ones.	HTTP/0.9 and later
205	Reset Content	This response code is sent after accomplishing request to tell user agent reset document view which sent this request.	HTTP/1.1 only
206	Partial Content	This response code is used because of range header sent by the client to separate download into multiple streams.	HTTP/1.1 only
Redirection messages
300	Multiple Choice	The request has more than one possible responses. User-agent or user should choose one of them. There is no standardized way to choose one of the responses.	HTTP/1.0 and later
301	Moved Permanently	This response code means that URI of requested resource has been changed. Probably, new URI would be given in the response.	HTTP/0.9 and later
302	Found	This response code means that URI of requested resource has been changed temporarily. New changes in the URI might be made in the future. Therefore, this same URI should be used by the client in future requests.	HTTP/0.9 and later
303	See Other	Server sent this response to directing client to get requested resource to another URI with an GET request.	HTTP/0.9 and 1.1
304	Not Modified	This is used for caching purposes. It is telling to client that response has not been modified. So, client can continue to use same cached version of response.	HTTP/0.9 and later
305	Use Proxy	This means requested response must be accessed by a proxy. This response code is not largely supported because security reasons.	HTTP/1.1 only
306	unused	This response code is no longer used, it is just reserved currently. It was used in a previous version of the HTTP 1.1 specification.	HTTP/1.1 only
307	Temporary Redirect	Server sent this response to directing client to get requested resource to another URI with same method that used prior request. This has the same semantic than the 302 Found HTTP response code, with the exception that the user agent must not change the HTTP method used: if aPOST was used in the first request, a POST must be used in the second request.	HTTP/1.1 only
308	Permanent Redirect	This means that the resource is now permanently located at another URI, specified by the Location: HTTP Response header. This has the same semantics as the 301 Moved Permanently HTTP response code, with the exception that the user agent must not change the HTTP method used: if a POST was used in the first request, a POST must be used in the second request. Note: This is an experimental response code whose specification is currently in draft form.	draft-reschke-http-status-308
Client error responses
400	Bad Request	This response means that server could not understand the request due to invalid syntax.	HTTP/0.9 and later
401	Unauthorized	Authentication is needed to get requested response. This is similar to 403, but in this case, authentication is possible.	HTTP/0.9 and later
402	Payment Required	This response code is reserved for future use. Initial aim for creating this code was using it for digital payment systems however this is not used currently.	HTTP/0.9 and 1.1
403	Forbidden	Client does not have access rights to the content so server is rejecting to give proper response.	HTTP/0.9 and later
404	Not Found	Server can not find requested resource. This response code probably is most famous one due to its frequency to occur in web.	HTTP/0.9 and later
405	Method Not Allowed	The request method is known by the server but has been disabled and cannot be used. The two mandatory methods, GET and HEAD, must never be disabled and should not return this error code.	HTTP/1.1 only
406	Not Acceptable	This response is sent when the web server, after performing server-driven content negotiation, doesn't find any content following the criteria given by the user agent.	HTTP/1.1 only
407	Proxy Authentication Required	This is similar to 401 but authentication is needed to be done by a proxy.	HTTP/1.1 only
408	Request Timeout	This response is sent on an idle connection by some servers, even without any previous request by the client. It means that the server would like to shut down this unused connection. This response is used much more since some browsers, like Chrome or IE9, use HTTP pre-connection mechanisms to speed up surfing (see bug 634278, which tracks the future implementation of such a mechanism in Firefox). Also note that some servers merely shut down the connection without sending this message.	HTTP/1.1 only
409	Conflict	This response would be sent when a request conflict with current state of server.	HTTP/1.1 only
410	Gone	This response would be sent when requested content has been deleted from server.	HTTP/1.1 only
411	Length Required	Server rejected the request because the Content-Length header field is not defined and the server requires it.	HTTP/1.1 only
412	Precondition Failed	The client has indicated preconditions in its headers which the server does not meet.	HTTP/1.1 only
413	Request Entity Too Large	Request entity is larger than limits defined by server; the server might close the connection or return an Retry-After header field.	HTTP/1.1 only
414	Request-URI Too Long	The URI requested by the client is too long for the server to handle.	HTTP/1.1 only
415	Unsupported Media Type	The media format of the requested data is not supported by the server, so the server is rejecting the request.	HTTP/1.1 only
416	Requested Range Not Satisfiable	The range specified by the Range header field in the request can't be fulfilled; it's possible that the range is outside the size of the target URI's data.	HTTP/1.1 only
417	Expectation Failed	This response code means the expectation indicated by the Expect request header field can't be met by the server.	HTTP/1.1 only
Server error responses
500	Internal Server Error	The server has encountered a situation it doesn't know how to handle.	HTTP/0.9 and later
501	Not Implemented	The request method is not supported by the server and cannot be handled. The only methods that servers are required to support (and therefore that must not return this code) are GET and HEAD.	HTTP/0.9 and later
502	Bad Gateway	This error response means that the server, while working as a gateway to get a response needed to handle the request, got an invalid response.	HTTP/0.9 and later
503	Service Unavailable	The server is not ready to handle the request. Common causes are a server that is down for maintenance or that is overloaded. Note that together with this response, a user-friendly page explaining the problem should be sent. This responses should be used for temporary conditions and the Retry-After: HTTP header should, if possible, contain the estimated time before the recovery of the service. The webmaster must also take care about the caching-related headers that are sent along with this response, as these temporary condition responses should usually not be cached.	HTTP/0.9 and later
504	Gateway Timeout	This error response is given when the server is acting as a gateway and cannot get a response in time.	HTTP/1.1 only
505	HTTP Version Not Supported	The HTTP version used in the request is not supported by the server.	HTTP/1.1 only