Tag Archives: MySQL

Clean historylist in SAM with MySQL

show size of all databases

mysql> SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

+----------------------+--------------+
| Database name        | Size (MB)    |
+----------------------+--------------+
| information_schema   |   0.00781250 | 
| mysql                |   0.61204815 |
| project1_development | 281.59375000 |
| project1_test        |   7.29687500 |
| project2_development |   2.65625000 |
| project2_test        |   0.15625000 |
+----------------------+--------------+

 

show database sizes and export summary file

mysql SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema INTO OUTFILE '/home/majestaetix/__0_WORK/___upload_zumbi/db_size.txt'

 

Delete played track before defined date and optimize the historylist table

use giardino1; 
DELETE FROM `historylist` WHERE `date_played` < '2014-12-31 23:59:59';
OPTIMIZE TABLE `historylist`;

 

How to do Painless MySQL Server Backups with AutoMySQLBackup

AutoMySQLBackup is a clever script that takes the pain out of setting up automated incremental daily, weekly, and monthly backups of MySQL databases.

wget http://heanet.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz
tar zxvf automysqlbackup-v3.0_rc6.tar.gz
./install.sh

 

Quelle: How to do Painless MySQL Server Backups with AutoMySQLBackup | Linux.com

Centos Basic Installation 

# ------------------------------------------------------
## Network installation Path
# ------------------------------------------------------
#http://mirror.switch.ch/ftp/mirror/centos/6.4/os/x86_64/
#http://mirror.ovh.net/ftp.centos.org/6.4/os/x86_64/
# Bei OpenVZ installation Host als Gateway eingeben !!!

# ------------------------------------------------------
## Firewall deaktivation
# ------------------------------------------------------
# disable IP tables temporär
/etc/init.d/iptables stop
# permanent if server is in the LAN
chkconfig iptables off

# ------------------------------------------------------
## SELINUX
# ------------------------------------------------------
# if you like to disable SeLINUX
# temporär :
setenforce 0
# disable permanent on Servers Running in the LAN
vi /etc/selinux/config
SELINUX=disabled

# permanent :
sed --in-place=.BAK 's:SELINUX=[a-z]*:SELINUX=disabled:g' /etc/selinux/config
setenforce 0

# enable the ftp rules for ftp homes
#/usr/sbin/setsebool -P ftp_home_dir 1

# ------------------------------------------------------
## Add additional Repo's
# ------------------------------------------------------
# sehr gute erklärung : http://dokuwiki.nausch.org/doku.php/centos:epel6
# http://wiki.centos.org/AdditionalResources/Repositories

# RPMForge - This repository is a collaboration of Dag (who also maintains an individual archive) and other packagers. The archive provides over 10,000 packages for CentOS, including mplayer, xmms-mp3 and other popular media tools. You can read the installation instructions at Installing RPMForge. This repository is considered by many in the community to be stable and safe.

# http://wiki.centos.org/AdditionalResources/Repositories/RPMForge#head-f0c3ecee3dbb407e4eed79a56ec0ae92d1398e01
# i686 http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.i686.rpm
# x86_64 http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm

cd /tmp
wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
rpm --import http://apt.sw.be/RPM-GPG-KEY.dag.txt
# Verify the package you have downloaded
rpm -K rpmforge-release-0.5.3-1.el6.rf.*.rpm
# Install the package
rpm -i rpmforge-release-0.5.3-1.el6.rf.*.rpm

http://dokuwiki.nausch.org/doku.php/centos:epel6

# ------------------------------------------------------
# CentOS / RHEL / Scientific Linux 6 Enable & Install EPEL Repo
# ------------------------------------------------------
for 32bit : wget http://mirror.de.leaseweb.net/epel/6/i386/epel-release-6-8.noarch.rpm
for 64bit : wget http://mirror.de.leaseweb.net/epel/6/x86_64/epel-release-6-8.noarch.rpm

rpm -ivh epel-release-6-8.noarch.rpm

# ------------------------------------------------------
# Add Basic user
# ------------------------------------------------------
# add user/group zumbi
groupadd zumbi
useradd -gzumbi -Gzumbi -s/bin/bash -d/home/zumbi -m zumbi
# activate the user zumbi
# password :
passwd zumbi

# ------------------------------------------------------
## ssh configuration
# ------------------------------------------------------
# backup original configuration file
cp -a /etc/ssh/sshd_config /etc/ssh/sshd_config_original
# add line in sshd_config
echo "AllowUsers zumbi" >> /etc/ssh/sshd_config
echo "PermitRootLogin no" >> /etc/ssh/sshd_config
# Change the default Port
sed -i 's/#Port 22/Port 65535/g' /etc/ssh/sshd_config
service sshd restart
# Install the openssh-client for doing scp to the centos server
yum install openssh-clients

# ------------------------------------------------------
## ssh key auth configuration
# ------------------------------------------------------
#check howtoforge
# http://wiki.centos.org/HowTos/Network/SecuringSSH
mkdir ~/.ssh
chmod 700 ~/.ssh
vi ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
vi /etc/ssh/sshd_config
[...]
Protocol 2
PasswordAuthentication no
UsePAM no
[...]

service sshd restart

# ------------------------------------------------------
## Logrotate
# ------------------------------------------------------

Enable compress in the config file
/etc/logrotate.conf

# ------------------------------------------------------
## FTP Server configuration
# ------------------------------------------------------
yum install -y vsftpd
# backup original configuration file
cp -a /etc/vsftpd/vsftpd.conf /etc/vsftpd/vsftpd.conf_original
cp -a /etc/vsftpd/user_list /etc/vsftpd/user_list_original
cp -a /etc/vsftpd/chroot_list /etc/vsftpd/chroot_list_original

# remove original configuration files
rm -rf /etc/vsftpd/vsftpd.conf
rm -rf /etc/vsftpd/user_list
rm -rf /etc/vsftpd/chroot_list

# wget the reference files
cd /etc/vsftpd/
wget https://www.dropbox.com/s/nzwkqiwsgqv2rit/chroot_list
wget https://www.dropbox.com/s/etef4jaskcd50wd/user_list
wget https://www.dropbox.com/s/lxl86h46y4tjv5z/vsftpd.conf

# make the the FTP Server is starting at boot time
chkconfig vsftpd on
service vsftpd start

# ------------------------------------------------------
## ACPID installation > for VM's important
# ------------------------------------------------------
yum install acpid
service acpid start
chkconfig acpid on

# ------------------------------------------------------
## Mail configuration
# ------------------------------------------------------
# Test mail versand via watchdog script

# ------------------------------------------------------
## Kernel Tuning
# ------------------------------------------------------
# kernel parameter linux tuning
http://people.redhat.com/alikins/system_tuning.html

# ------------------------------------------------------
## Webmin installation
# ------------------------------------------------------
touch /etc/yum.repos.d/webmin.repo
echo "[Webmin]" >> /etc/yum.repos.d/webmin.repo
echo "name=Webmin Distribution Neutral" >> /etc/yum.repos.d/webmin.repo
echo "#baseurl=http://download.webmin.com/download/yum" >> /etc/yum.repos.d/webmin.repo
echo "mirrorlist=http://download.webmin.com/download/yum/mirrorlist" >> /etc/yum.repos.d/webmin.repo
echo "enabled=1" >> /etc/yum.repos.d/webmin.repo
cd /tmp
wget http://www.webmin.com/jcameron-key.asc
rpm --import jcameron-key.asc
yum install webmin

# ------------------------------------------------------
## UPTIMED instllation
# ------------------------------------------------------
# 64bit >> ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/GeneBean/CentOS_CentOS-6/x86_64/uptimed-0.3.17-24.1.x86_64.rpm
# 32bit >> http://rpm.pbone.net/index.php3/stat/4/idpl/21652127/dir/centos_6/com/uptimed-0.3.17-24.1.i386.rpm.html
cd /tmp
wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/GeneBean/CentOS_CentOS-6/x86_64/uptimed-0.3.17-24.1.x86_64.rpm
rpm -i uptimed-0.3.17-24.1.x86_64.rpm

# ------------------------------------------------------
## glances installation
# ------------------------------------------------------
# refer first to CentOS / RHEL / Scientific Linux 6 Enable & Install EPEL Repo
yum -y install glances
#start application with 
glances

# ------------------------------------------------------
## CACTI installation
# ------------------------------------------------------
# http://blog.network-outsourcing.de/cacti-howtos/cacti/installing-cacti/
# http://forums.cacti.net/viewtopic.php?t=36937
# http://pkgs.repoforge.org/cacti/
# http://pkgs.repoforge.org/cacti/cacti-0.8.8a-1.el6.rf.noarch.rpm

# ------------------------------------------------------
## DSTAT installation
# ------------------------------------------------------
# http://pkgs.repoforge.org/dstat/dstat-0.7.2-1.el6.rfx.noarch.rpm

# ------------------------------------------------------
## phpmyadmin Installation
# ------------------------------------------------------
# http://dokuwiki.nausch.org/doku.php/centos:phpmyadmin_c6

# ------------------------------------------------------
## NFS installation
# ------------------------------------------------------
# http://www.howtoforge.com/setting-up-an-nfs-server-and-client-on-centos-6.3

yum install nfs-utils nfs-utils-lib
chkconfig --levels 235 nfs on
/etc/init.d/nfs start

# Nobody user and group issue
# http://whacked.net/2006/07/26/nfsv4nfs-mapid-nobody-domain/
# You can tweak this setup by editing the: /etc/idmapd.conf
# Domain =  dmd2.local

# ------------------------------------------------------
## SAMBA Installation
# ------------------------------------------------------
# http://rbgeek.wordpress.com/2012/05/25/how-to-install-samba-server-on-centos-6/

# ------------------------------------------------------
## install 3ware 3dm2 website and CLI
# ------------------------------------------------------
#Download software from http://www.lsi.com/channel/support/pages/downloads.aspx?k=*
wget http://www.lsi.com/downloads/Public/SATA/SATA%20Common%20Files/3DM2_CLI-linux_10.2.2.1_9.5.5.1.zip
unzip 3DM2_CLI-linux_10.2.2.1_9.5.5.1.zip
./install.sh -i
# When you are finished with your installation you should see /usr/sbin/3dm2 running 3 times.
# To connect go to https://domainname:888

# ------------------------------------------------------
## Disable IPv6
# ------------------------------------------------------
# http://wiki.centos.org/FAQ/CentOS6#head-d47139912868bcb9d754441ecb6a8a10d41781df
vi /etc/sysctl.conf
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
service ip6tables stop
chkconfig ip6tables off
chkconfig shorewall on
reboot
---------------------------------------
this procedure causes issues with shorewall and ksplice.
vi /etc/sysconfig/network
# NETWORKING_IPV6=no

vi /etc/modprobe.d/modprobe.conf
# install ipv6 /bin/true

service ip6tables stop
chkconfig ip6tables off

reboot

# ------------------------------------------------------
## MySql installation
# ------------------------------------------------------
# http://dokuwiki.nausch.org/doku.php/centos:mysql

yum install mysql-server -y
service mysqld start
/usr/bin/mysql_secure_installation

vi /etc/logrotate.d/mysql

/var/log/mysqld.log {
rotate 4
weekly
compress
notifempty
size 5M
missingok
create 0640 mysql mysql
sharedscripts
postrotate
/bin/kill -HUP `cat /var/run/mysqld/mysqld.pid 2> /dev/null` 2> /dev/null || true
endscript
}

chkconfig mysqld on

# check config files hier : mysql-server-5.1.61
# To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system
# PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
# To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h dbix01 password 'new-password'

# Alternatively you can run:
/usr/bin/mysql_secure_installation

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

# ------------------------------------------------------
## SNMP Installation
# ------------------------------------------------------
# http://www.it-slav.net/blogs/2008/11/11/install-and-configure-snmp-on-rhel-or-centos/
# http://paulsuela.wordpress.com/2010/06/30/snmpd-conf-for-centos/
# http://lifeofageekadmin.com/configure-snmp-for-rhel-6/
# http://www.linuxhomenetworking.com/wiki/index.php/Quick_HOWTO_:_Ch22_:_Monitoring_Server_Performance

yum install –y net-snmp-utils net-snmp net-snmp-libs

cd /etc/snmp/
cp -a /etc/snmp/snmpd.conf /etc/snmp/snmpd.conf_original
rm /etc/snmp/snmpd.conf
wget https://www.dropbox.com/s/yjqze30ibcgxadq/snmpd.conf

service snmpd start
chkconfig snmpd on
## Test the snmpd
snmpwalk -v 1 -c public localhost IP-MIB::ipAdEntIfIndex

# ------------------------------------------------------
## Shorewall Installation
# ------------------------------------------------------
# http://www.howtoforge.com/how-to-set-up-shorewall-firewall-on-centos-5.1
# http://sugizo.wordpress.com/2011/07/08/centos-install-and-configure-shorewall/

cp /usr/share/doc/shorewall*/Samples/two-interfaces/{interfaces,policy,masq,routestopped,rules,zones} /etc/shorewall/
# definiere die Standardports im rule file
65535
10000
21
80
8080
53 >  DNS/ACCEPT

## modify the basic files
network
zones
rules
interfaces
policy
masq
routestopped