PlanetMysql.ru — информация о СУБД MySQL

Enable MyISAM Auto-Repair

MyISAM can be configured to check and repair its tables automatically. By enabling the MyISAM auto repair you let the server check each MyISAM table when it is opened. It checks  if the table was closed properly when it was  last used, also checks if it needs any repair, if required it repairs the table.

To enable auto check and repair,you can start the server with –myisam-recover with following options.

DEFAULT for the default checking.

BACKUP tells the server to make a backup of any table that it must change.

FORCE causes table recovery to be performed even if it would cause the loss of more than one row of data.

QUICK performs quick recovery: Tables that have no holes resulting from deletes or updates are skipped.

You can also add it to the my.cnf file as shown below

[mysqld]

myisam-recover=FORCE,BACKUP

-Thanks

PlanetMySQL Voting: Vote UP / Vote DOWN

I was configuring MySQL Cluster where the application servers use a properties file to connect to the MySQL Data/Storage node (I configured both Data and Storage nodes on same physical server).
I want the application to use a single IP address, to access the DB servers in cluster. Since if any of the nodes fail, the application servers should still be able to query the databases.
Hence I thought of using LVS so that multiple application servers can access the DB servers through VIP. To achieve the same I have configured LVS, thought of sharing the same.

RHEL Linux LVS setup:
=====================

Pre-Requisites:
===============

REDHAT Linux Cluster Packages
VIRTUAL IP

Configuration for LB server:
============================

Configure packet forwarding on LB server

vi /etc/sysctl.conf

net.ipv4.ip_forward = 1        # change to 1,

Install piranha package from redhat cluster group

Once installed, configure the password:

# piranha-passwd

for autostart the LVS services, below are the commands

# chkconfig pulse on
# chkconfig piranha-gui on
# chkconfig httpd on

Start the http and piranha services

# service httpd start
# service piranha-gui start

Open http://localhost:3636 in a Web browser to access the Piranha Configuration Tool.
Click on the Login button and enter piranha for the Username and the administrative password you created in the Password field.

OR

Configure file to set up LVS

vi /etc/sysconfig/ha/lvs.cf        # Sample configuration file for 11.1 and 11.4

serial_no = 47
primary = 172.16.11.1
service = lvs
backup_active = 1
backup = 172.16.11.4
heartbeat = 1
heartbeat_port = 539
keepalive = 3
deadtime = 6
network = direct
debug_level = NONE
monitor_links = 0
syncdaemon = 0
virtual MySQL {
active = 1
address = 172.16.11.10 bond0:1
vip_nmask = 255.255.255.255
port = 3306
expect = “UP”
use_regex = 1
send_program = “/root/mysql_mon.sh %h”
load_monitor = none
scheduler = wlc
protocol = tcp
timeout = 5
reentry = 3
quiesce_server = 0
server Server1 {
address = 172.16.11.2
active = 1
weight = 1
}
server Server2 {
address = 172.16.11.3
active = 1
weight = 1
}
}

Restart the pulse services on the LVS Routers

# service pulse restart

Verify the LVS. Check the ipvsadm entries:

[root@ServerLB|172.16.11.1~]~ # ipvsadm

IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  172.16.11.10:mysql wlc
-> Server1  :mysql              Route   1      0          0
-> Server2  :mysql              Route   1      0          0

MySQL Real Servers configuration:
=================================

Run below commands on both the MySQL nodes

echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
echo 2 > /proc/sys/net/ipv4/conf/lo/arp_announce

Configure loopback on both the MySQL nodes

ifconfig lo:0 172.16.11.10 netmask 255.255.255.255 up

OR (Permanent changes)

vi /etc/sysconfig/network-scripts/ifcfg-lo:0

DEVICE=lo:0
IPADDR=172.16.11.10
NETMASK=255.255.255.255
NETWORK=172.16.11.0
BROADCAST=172.16.11.255
ONBOOT=yes
NAME=loopback

To bring up the IP alias the ifup command is used:
/sbin/ifup lo

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Dump Using Linux CRON Job

If you are a database administrator who would like to automate you tasks. Here is a simple and very basic task that can be automated,
MySQL Database Dumps are the very basic task every administrator does, no matter how simple it sounds, it is most useful in failure scenarios. Hence you would have to perform this task very often.

It is very likely to miss on taking dumps on daily routine, hence you can come up with an alternative to dump your databases by scheduling it to run automatically. This will let you concentrate on your other task which might need more attention.

There are several ways to dump a database, you have many utilities and tools to do so. Also many tools give you the option to schedule the dumps through a GUI.

Follow the below steps to automate your MySQL dump.

Firstly, you need create a .sh file with these entries,

>vi MySQLdump.sh

mysqldump -u root -pmysql123 –all-databases –routines| gzip > /root/MySQLDB_`date ‘+%m-%d-%Y’`.sql.gz
mysqldump -h 172.16.21.3 -u root -pmysql123 –all-databases –routines | gzip > /root/MySQLDB.3_`date ‘+%m-%d-%Y’`.sql.gz

In above two lines, I’m scheduling the cron job to dump my database on the same machine, also the second line is dumping a databsase from a remote host.
You need to provide access to the local system on the remote host to perform the task.

Secondly, create a CRON job to schedule the dumps.

>crontab -e

30 15 * * * /root/MySQLdump.sh 2>&1>> /root/MySQLdump.log

The above will dump the database every day at 15:30.

You can schedule it your way.

PlanetMySQL Voting: Vote UP / Vote DOWN

For latest developments and updates on cluster visit below.

Many limitations of previous versions addressed.

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-development-5-1-ndb-7-1.html

PlanetMySQL Voting: Vote UP / Vote DOWN

At the time of Server failure, you would want your MySQL to start at the boot.

To achieve the same follow the below steps at the prompt.

>cd /etc/rc.d/rc3.d
>ln -s /etc/rc.d/init.d/mysql S98mysql

The second line will create a symlink in the above directory. By doing this you are asking your server to run it during level 3 start up.

However you can also run the below commands manually post startup.

Using files in /etc/rc.d/init.d directly, for example:

/etc/rc.d/init.d/mysql start
/etc/rc.d/init.d/mysql stop

-Death

PlanetMySQL Voting: Vote UP / Vote DOWN