blog.up-link.ro android bsd linux unix & open-source world

17Oct/100

UNIX Tools: mytop – MySQL Monitoring Tool

MyTop is a console-based tool for monitoring the threads and overall performance of a MySQL server. It runs on most Unix systems. MyTop is a top clone for MySQL Server.

To install MyTop under FreeBSD, enter:

make install clean -C /usr/ports/databases/mytop

To install MyTop under CentOS, Fedora, RHEL, enter:

yum install mytop

To install MyTop under Debian, Ubuntu, enter:

sudo apt-get install mytop

Print This Post Print This Post
15Aug/102

FreeBSD: How To Install Lighttpd with PHP5-FastCGI and MySQL Support in FreeBSD 8

In this article I'll show you how to install and configure Lighttpd web server on FreeBSD 8.1 with PHP5 support (through FastCGI) and MySQL support.

1. FreeBSD port tree update

Update FreeBSD ports using the following command:

portsnap fetch update

Once is done, we can proceed with the next step.

Print This Post Print This Post
17May/101

How To Install Lighttpd with PHP5 and MySQL support on CentOS 5

Lighttpd is an open-source web server optimized for speed-critical environments. It's standards-compliant, secure and flexible. In this tutorial I'll show you how to install Lighttpd on a CentOS 5.4 server with PHP5 support (through FastCGI) and MySQL support.

1. Installing MySQL 5 Server

To install MySQL run this command from the shell:

# yum install mysql mysql-server

Enable MySQL server on boot and start MySQL server:

# chkconfig --levels 235 mysqld on
# service mysqld start

Print This Post Print This Post
14Apr/100

How To Export MySQL data to CSV file

In this article I'll show you how to export MySQL database to a CSV file.

1. Export MySQL data to CSV file using a simple "SELECT" statement

If you want to export your mysql table into a csv file you need to run the following command:

# mysql -u username -ppassword database -e "SELECT * INTO OUTFILE '/tmp/filename.csv'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  LINES TERMINATED BY '\r\n' FROM table;"

username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export

You can find the exported CSV file in the /tmp directory. The name of the file is filename.csv.

2. Export MySQL data to CSV file using "sed"

If you want to export your mysql table into a csv file you need to run the following command:

Print This Post Print This Post
2Apr/100

Installing PowerDNS with MySQL backend and PowerAdmin On CentOS

PowerDNS is a MySQL-based DNS server, written in C++ and licensed under the GPL. PowerDNS can be managed through a web interface (PowerAdmin). This guide shows how to install it on CentOS 5.

1. Installing MySQL

# yum -y install mysql mysql-server

2. Enable MySQL on boot and start MySQL server

# chkconfig --levels 235 mysqld on
# service mysqld start

Make sure the MySQL server is running:

# netstat -tap | grep mysql
tcp        0      0 *:mysql                 *:*     LISTEN   28179/mysqld

3. Set password for user root

# mysqladmin -u root password your_password

Print This Post Print This Post
30Mar/100

How To Backup MySQL databases using a shell script

MySQL is one of the most popular open source database management system for the development of interactive websites.

If your server stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster.

Below is a backup script for MySQL databases (please make sure you will change MYSQL_PASS):

#!/bin/sh
# backup mysql databases shell script
# UNIX LINUX BSD
# by Adi http://blog.up-link.ro
# March 2010

DATE=$(date +%Y-%m-%d)
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)
MYSQL_USER="root"
MYSQL_PASS="password"
HOSTNAME=$(hostname)
GZIP=$(which gzip)
ARG="-u $MYSQL_USER -p$MYSQL_PASS"
DATABASES=$($MYSQL $ARG -s -e "SHOW DATABASES;")
BACKUP_PATH="/home/backup/$DATE/mysql"

! [ -d $BACKUP_PATH ] && mkdir -p $BACKUP_PATH

for DB in $DATABASES
do
BACKUP_FILE="$BACKUP_PATH/$HOSTNAME-mysql-$DB-$DATE.sql.gz"
$MYSQLDUMP $ARG $DB | $GZIP -9 > $BACKUP_FILE
done

Download the script from here.

Print This Post Print This Post
24Mar/100

How To Reset MySQL Password

1. Stop MySQL server

# /usr/local/etc/rc.d/mysql-server stop

2. Start MySQL server  with skip grant table mode

# mysqld_safe –skip-grant-tables &
Output:
[1] 5187
# Starting mysqld daemon with databases from /var/db/mysql

3. Login as user root without password

# mysql -u root -p

Print This Post Print This Post
24Mar/100

Apache HTTP Server + PHP 5 + MySQL Installation in FreeBSD

1. Install Apache HTTP Server
Go to apache22 port:
# cd /usr/ports/www/apache22
and type:
# make config

Now you can choose the options from the menu.
# make install clean

To enable apache on boot, add the following line to /etc/rc.conf
apache22_enable="YES"

To start apache http server, type
# /usr/local/etc/rc.d/apache22 start

2. Install PHP 5
Go to php port:
# cd /usr/ports/lang/php5
and type:

Print This Post Print This Post
3Mar/100

MySQL Server Installation in FreeBSD using ports

This guide describes how to install MySQL, a very popular relational database, in FreeBSD.

Please make sure the ports collection is up to date. If not, run the following command:
# portsnap fetch update

Then navigate to the ports folder for MySQL:
# cd /usr/ports/databases/mysql51-server
and run:
# make install clean
This command will download required files from the internet and will install MySQL server.

Once the install is finished, we install the database
# mysql_install_db --user=mysql
and we set up the directory permissions
# chown -R mysql /var/db/mysql/
# chgrp -R mysql /var/db/mysql/

Make sure MySQL server start automatically whenever FreeBSD comes up after reboot.
FreeBSD provide this facility via system configuration information. This file lists which services should be started up at system initial boot time.
The file contains the global system configuration information referenced by the start up scripts and is stored in /etc/rc.conf.

Configure FreeBSD to start MySQL at start up
Open /etc/rc.conf file using a text editor ( I use vi but you can use your preferred editor )
# vi /etc/rc.conf
and append following line which will enable MySQL server automatically after each reboot:
mysql_enable="YES"
Save file and exit to shell prompt.

Starting MySQL server
To Start MySQL server under FreeBSD type following command (use script):
# /usr/local/etc/rc.d/mysql-server.sh start

Note: This will work only if you have mysql_enable="YES" in rc.conf. If you don't, use the following command:
# /usr/local/etc/rc.d/mysql-server.sh forcestart

MySQL Password
By default, MySQL super user account has no password. So it’s important to assign the administrator account password. To change the root password, enter the following command:
mysqladmin -u root password newpass

Replace newpass with your own desired password.

Optionally, copy either my-huge.cnf, my-large.cnf, my-medim.cnf or my-small.cnf  (depending on the usage and utilization of your MySQL server) as my.cnf to /etc/ or /var/db/mysql which will enable you to tweak and change the configuration of server-specific MySQL server options by editing the file.

Print This Post Print This Post
12Feb/102

How To Recover the root Password in FreeBSD

The password cannot be recovered. The following procedure will allow you to change the root password.

Do the following:

- when the following boot message appears

Hit [ENTER] to boot immediately, or any other key for command prompt.
Booting [kernel] in 10 seconds...

hit a key, just pick one EXCEPT the ENTER key. You'll get a prompt like:

disk1s1a:>

- type the following commands:

disk1s1a:>unload all
disk1s1a:>load kernel
disk1s1a:>boot -s

The boot process will now start, just wait until it asks you for a shell. Just hit
ENTER and 'sh' will be used as shell.

If you type 'mount' you will see that only your root partition ( / ) is mounted, you will have to mount  'usr' partition as well.

#mount /dev/ad0s1f /usr

Now you have to mount root partition as read-write enabled. Use the following command:

#mount -u /

The root partition should now be mounted read-write. Now you can use 'passwd' program to
change the root password.

#passwd

That's all, reboot the system and login with the new password.

Print This Post Print This Post