How to backup MySQL in diferent formats

The best way to have mysql backed up in different formats is to use PhpMyAdmin, you need to go to a database and the tab Export.

You can setup a Quick backup in different formats such as:

  • CodeGen
  • CSV
  • CSV for Excel
  • Microsoft Word 2000
  • JSON
  • Latex
  • MediaWiki Table
  • Open Document Spreadsheet
  • Open Document Text
  • PDF
  • PHP Array
  • SQL
  • Texty! text
  • XML
  • YAML

The best way to backup and be sure you can restore it fully functional is to use SQL backup, also you can backup your store procedures, trigger, useras and any other particular configuration of the database.

 

Screenshot for PhpMyAdmin:

13 09_51

Simple PHP Code Igniter class to show the error log

Sometimes we need to acces the php log, but is not available on the document root, so yo need to access by console or create a symlink to some part inside the public folder of your website. Also you need to add some credentials in order to protect your logs. This is a simple and safer way to access your php error log, you can add this controller inside your protected controllers using the php session or maybe a cookie generated by you with some encrypted data.

So here is the controller class to show the php log really easy for code igniter.

<?php
/**
 * Php Log class
 *
 * Display the php log
 *
 * @category	Log
 * @author		Nicolás Bistolfi
 * @link		https://dl.dropboxusercontent.com/u/20596750/code/php/log.php
 */
class Log extends Controller{

	private $logPath; //path to the php log
	/**
	*	Class constructor
	*/
	function __construct(){
		parent::Controller();
		$this->logPath = ini_get('error_log');
	 }

	/**
	 * index: Shows the php error log
	 * @access public
	 */
	public function index(){
		if (@is_file($this->logPath)) {
			echo nl2br(@file_get_contents($this->logPath));
		} else {
			echo 'The log cannot be found in the specified route '.$this->logPath;
		}
		exit;
	}

	/**
	 * delete: Deletes the php error log
	 * @access public
	 */
	public function delete(){
		if (@is_file($this->logPath)) {
			if (@unlink($this->logPath)) {
				echo 'PHP Error Log deleted';
			} else {
				echo 'There has been an error trying to delete the PHP Error log '.$this->logPath;
			}
		} else {
			echo 'The log cannot be found in the specified route  '.$this->logPath.'.';
		}
		exit;
	}
}
?>

 

You can download the code by clicking here

How to backup MySQL database from the command line

Backup your MySQL database to an sql file with your command line. Using native mysql client and tools you can perform all the backup and restore operations you need.

First you need to have installed the command line tools, in this case mysqldump.

$ mysqldump --opt -u [uname] -p [dbname] > [backupfile.sql]
  • [uname] Your database username
  • [pass] The password for your database (note there is no space between -p and the password)
  • [dbname] The name of your database
  • [backupfile.sql] The filename for your database backup
  • [–opt] The mysqldump option

Example if you have a database called wordpress_blog you only have to do this:

$ mysqldump --opt -u root -p wordpress_blog > wordpress_blog_bk.sql

If you want to backup all the databases in only one sql file you can use –all-databases option

$ mysqldump -u root -p --all-databases > alldb_backup.sql