MySQL Backup and Recovery with Command-Line

the mysqldump client is a backup program which can be used to dump a database or a collection of databases for backup or transfer to another sql server. the dump typically contains sql statements to create the table, populate it, or both.

backup an entire database

mysqldump -u username -p db_name > backup.sql

-p, --password
-u, --user=username

backup a table

mysqldump db_name table > backup.sql

dump several databases with one command

mysqldump --databse db_name1 db_name2 .. > databases.sql

dump all databases

mysqldump --all-databases > all_databases.sql



you need to create the databases manually if your target mysql server has no database with same name before recovery

mysql> create database db_name;

restore

mysql -u username -p < backup.sql


Set up Lighttpd, PHP, PhpMyAdmin, MySQL on Arch Linux

1. install packages

pacman -S lighttpd php phpmyadmin mysql

2. this script starts mysql daemon and also take care of the basic configuration as adding system users and creating log files

/etc/rc.d/mysqld start

3. force both mysql client and server to use utf8 encoding though not all kinds of clients read the setting

vi /etc/my.cnf
--

..
[client]
default-character-set = utf8
..
[mysqld]
default-character-set = utf8
..

4. it may be necessary to add a user and group for http. this user seems to need to have permissions to write to the /var/log/lighttpd

groupadd http
adduser http
chown -R http /var/log/lighttpd

5. lighttpd handles php through fastcgi so we have to enable it in /etc/lighttpd/lighttpd.conf

server.modules = (
..
"mod_fastcgi",
..
..
fastcgi.server = ( ".php" =>
( "localhost" =>
(
"socket" => "/var/run/lighttpd/php-fastcgi.socket",
"bin-path" => "/usr/bin/php-cgi"
)
)
)

6. for php and phpmyadmin, edit /etc/php/php.ini as follows

..
cgi.fix_pathinfo=1
..
extension=mysql.so
..

6. sample phpmyadmin configuration

vi /srv/http/phpMyAdmin/config.inc.php

--
<?php
/* Servers configuration */
$i = 0;

/* First server */
$i++;
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['auth_type'] = 'http';
$cfg['Servers'][$i]['verbose'] = 'name_of_server';

/* End of servers configuration */

?>

7. start your web server

/etc/rc.d/lighttpd start

then enter the url, http://localhost/phpMyAdmin

Unify Encoding of MySQL Clients on Ubuntu

it solves encoding of phpmyadmin and mysql cli aren't same.

sudo vi /etc/mysql/my.cnf
--

[client]
default-character-set = utf8


Use ulogd to log iptables records into mysql db with web interface, nulog

1. download and install ulogd and ulogd-mysql TESTING rpms from http://rpm.razorsedge.org/centos-5/RE-test/repodata/index.html
2. mysql -u root -p

mysql> create database ulog;

3. vi /etc/ulogd.conf

..
plugin="/usr/lib/ulogd/ulogd_MYSQL.so" #uncomment it for use
..
[MYSQL]
table="ulog"
pass=""
user="root"
db="ulog"
host="localhost"
..

4. download and install NuLog from http://software.inl.fr/trac/trac.cgi/wiki/EdenWall/NuLog

wget http://software.inl.fr/releases/Nulog/nulog-1.2.14.tar.gz
tar zxvf nulog-1.2.14.tar.gz
cd nulog-1.2.14
make install

5. create tables from the script

# the two commands are totally same
cat /root/nulog-1.2.14/scripts/ulogd.mysqldump | mysql -u root -p ulog
mysql -u root -p ulog < /root/nulog-1.2.14/scripts/ulogd.mysqldump

6. /etc/init.d/ulogd start
7. cp /usr/share/edenwall-web/nulog/include/config.template.php /usr/share/edenwall-web/nulog/include/config.php
7. vi /usr/share/edenwall-web/nulog/include/config.php

..
if (!isset($lang))
$lang="en";
..
# database Host
$db_host="localhost";
# database name
$db_ulog="ulog";
# database user
$db_user="root";
# database password
$db_pwd="";
..



* create a password file for basic http authentication
htpasswd -c /var/www/apache.passwd username
* cp nulog_apache.conf /etc/httpd/conf.d/nulog.conf
* vi /etc/httpd/conf.d/nulog.conf

Alias /nulog/ "/usr/share/edenwall-web/nulog/" # path
..
# Authentification
# Create file /path/to/some/file with htpasswd
AuthType Basic
AuthName "Nulog access"
AuthUserFile /var/www/apache.passwd
Require user username


Install the web application, phpMyAdmin to mange MySQL via browser on CentOS

1. yum install phpmyadmin
2. vi /etc/httpd/conf.d/phpmyadmin.conf

<Directory "/usr/share/phpmyadmin">
Order Deny,Allow
# Deny from all
Allow from all # If you want to connect it from anywhere
</Directory>
..
..

3. vi /usr/share/phpmyadmin/config.inc.php

/*
* This is needed for cookie based authentication to encrypt password in
* cookie
*/
$cfg['blowfish_secret'] = 'RANDOMTEXT'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';