Creating MySQL Backups With AutoMySQLBackup

AutoMySQLBackup is a shell script that lets you take daily, weekly and monthly backups of your MySQL databases using mysqldump. It can back up multiple databases, compress the backups, back up remote databases, and email the logs.

Read more on Howtoforge.com

How To Back Up MySQL Databases Without Interrupting MySQL

This article describes how you can back up MySQL databases without interrupting the MySQL service. Normally, when you want to create a MySQL backup, you either have to stop MySQL or issue a read lock on your MySQL tables in order to get a correct backup; if you don’t do it this way, you can end up with an inconsistent backup. To get consistent backups without interrupting MySQL, I use a little trick: I replicate my MySQL database to a second MySQL server, and on the second MySQL server I use a cron job that creates regular backups of the replicated database.

Read mor on Howtoforge.com

Advertisement

Repair MySQL master-master replication

ntroduction
Let assume we have two webservers that have databases in replication, web1.example.com and web2.example.com

In this example IP are the following, please change them according to your config :

web1.example.com : 192.168.0.104
web2.example.com : 192.168.0.105

From my experience, two problem can occur with a master-master replication setup :

1)Replication is out of sync for whatever reason (see chapter 1 to 6)
2)Replication is not working because an error happened in MySQL such as duplicate keys, error 1062 (see chapter 7 to 10)

Read more on blogama.org

Miscellaneous SQL Server Optimization Tips

By Alexander Chigrik

Here are seventeen little known tips you can use to ensure your SQL Server environment is performing in the most efficient manner possible.

1. Try to perform backups at the local hard disk first, and then copy backup file(s) to the tape later.

When you perform a backup, some SQL Server commands cannot be made, for example: during a backup you cannot run ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options, you cannot shrink a database, you cannot run a CREATE INDEX statement, you cannot make SELECT INTO, bulk load and so on. So, to improve backup performance, you can perform backups on the local hard disk first, and then copy backup file(s) to the tape later.

2. Use nonlogged bulk copy whenever possible.

The nonlogged bulk copy is much faster than the logged one, but to use it you must provide all the following conditions:

1. The database option ‘select into/bulkcopy’ is set to true.
2. The target table is not being replicated.
3. The TABLOCK hint is specified.
4. The target table has no indexes or, if the table does have indexes, it is empty when the bulk copy starts.

Read more on DatabaseJournal.com

SQL Server 2000 Optimization Tips

For the very large databases, use distributed partitioned views to partition tables horizontally across multiple servers.
This is a new SQL Server 2000 feature, which is available when using SQL Server 2000 Enterprise Edition only. Due to distributed partitioned views, SQL Server 2000 now on the first place in the TPC-C tests.

*****

Use indexed views to increase the speed of your queries.
The result set of the indexed view is persist in the database and indexed for fast access. Because indexed views depend on base tables, you should create indexed views with SCHEMABINDING option to prevent the table or column modification that would invalidate the view. Furthermore, using views instead of heavy-duty queries can reduce network traffic and can be used to facilitate permission management.

*****
Read more on MSSQLCity.com

SQL Server 7.0, 2000 and 2005 Clustering Resources

By : Brad McGehee Mar 15, 2007

As important as SQL Server clustering is to those companies that implement it, trying to find good information on how to implement and administer it is another story. And this is why I put this article together.

If you are seriously considering implementing SQL Server 2000 or 2005 under Windows 2000 or Windows 2003; then I highly recommend that you review the following resources before you begin. SQL Server clustering has many gotchas, and the best way to breeze past them is to know about them before you begin.

Recommended Book Resources

For whatever reason, there have been very few books written on Windows server clustering or SQL Server clustering. And the few that have been written are somewhat dated. Below are the names of some books you might want to consider. But before you buy any of them, be sure to check them out at Amazon.com first to be sure that it covers what you are looking for. To be honest with you, none of these books really are great, but they are all that are currently available.

For Windows clustering, consider the following books:

• Tuning Microsoft Server Clusters: Guaranteeing High Availability for Business Networks by Robert W. Buchanan and Robert Buchanan
• MCSE Training Kit: Microsoft Windows 2000 Advanced Server Clustering Services by Microsoft Corporation (out of print)
• Windows 2000 Cluster Server Guidebook: A Guide to Creating and Managing a Cluster (2nd Edition) by Dave Libertone (out of print)
• Windows 2000 Clustering and Load Balancing Handbook by Joseph M. Lamb
• Clustering Windows Server: A Road Map for Enterprise Solutions (covers Windows 2000) by Gary Mauler and Milt Beebe
• Introduction to Microsoft Windows NT Cluster Server: Programming and Administration by Raj Rajagopal
• Windows Server 2003 Clustering and Load Balancing by Robert Shimonski

For SQL Server clustering, consider the following books:

• Microsoft SQL Server 2000 High Availability by Allan Hirt, Cathan Cook, and Kimberley Tripp
• SQL Server 2000 High Availability Volume 2: Deployment by Microsoft Corporation
• MCTS Self-Paced Training Kit (Exam 70-431): Microsoft SQL Server 2005 Implementation and Maintenance by Solid Quality Learning and Laura Sackerman
• MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide: Exam 70-431 by Joseph L. Jorden and Dandy Weyn

Read more at www.sql-server-performance.com

An Introduction to SQL Server Clustering

By : Brad McGehee Apr 03, 2002

If your mission-critical SQL Server should experience a motherboard failure, how long will it be down? One hour, four hours, a day, or longer? How much will this cost your business in lost sales or productivity? And perhaps even more important to you, what will it do to your stress level?

Being a SQL Server DBA can be demanding and stressful, especially as the success of your company is often a function of your SQL Server’s uptime. While we, as DBAs, have some control over the uptime of our SQL Servers, we don’t have full control. There is not much we can do if a motherboard fails on a server, other than be prepared.

As you may already be aware, there is one way to help boost your SQL Server’s uptime, and that is by clustering SQL Servers. This way, should one SQL Server fail in the cluster, another clustered server will automatically take over, keeping downtime to minutes, instead of hours or more.

The purpose of this article is to introduce you to SQL Server clustering, along with its pros and cons. If you are considering clustering SQL Server to help reduce potential downtime, this article is a good place to start.

Read more at www.sql-server-performance.com

How To Set Up A Load-Balanced MySQL Cluster With MySQL 5.1

Version 1.0
Author: Peter Okupski <okupski [at] widzew [dot] net>
Last edited 06/02/2008

This tutorial is based on Falko Timme’s tutorial for MySQL Cluster 5.0. It shows how to configure a MySQL 5.1 cluster with five nodes: 1 x management, 2 x storage nodes and 2 x balancer nodes. This cluster is load-balanced by an Ultra Monkey package which provides heartbeat (for checking if the other node is still alive) and ldirectord (to split up the requests to the nodes of the MySQL cluster).

In this document I use Debian Etch 4.0 for all nodes. Therefore the setup might differ a bit for other distributions. The two data nodes were x64 to use all of the 8GB RAM. Servers were compiled from source so you should be able to make it running on any platform. The MySQL version I use in this setup is 5.1.24-rc. It’s a release candidate, but I wanted to use 5.1 to take advantage of Memory-Disk Based tables.

Beginning with MySQL 5.1.6, it is possible to store the non-indexed columns of NDB tables on disk, rather than in RAM as with previous versions of MySQL Cluster.

This howto is just a scratch to make it running, for many of you I am suggesting to read some off docs from MySQL page to be prepared to how manage the whole cluster and always know whats going on.

This document comes without warranty of any kind! Bare in mind you need to make tests and prepare your databases before using it in production mode.

Read more at Howtoforge.com

How To Repair MySQL Replication

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>

If you have set up MySQL replication, you probably know this problem: sometimes there are invalid MySQL queries which cause the replication to not work anymore. In this short guide I explain how you can repair the replication on the MySQL slave without the need to set it up from scratch again.

I do not issue any guarantee that this will work for you!

Read more at Howtoforge.com

Building an Inexpensive Oracle RAC 11g Release 1 on Linux – (CentOS 5.1 / iSCSI)

Contents

1. Overview
2. Oracle RAC 11g Overview
3. Shared-Storage Overview
4. iSCSI Technology
5. Hardware and Costs
6. Install the Linux Operating System
7. Install Required Linux Packages for Oracle RAC
8. Network Configuration
9. Install Openfiler
10. Configure iSCSI Volumes using Openfiler
11. Configure iSCSI Volumes on Oracle RAC Nodes
12. Create “oracle” User and Directories
13. Configure the Linux Servers for Oracle
14. Configure RAC Nodes for Remote Access using SSH
15. All Startup Commands for Both Oracle RAC Nodes
16. Install and Configure Oracle Cluster File System (OCFS2)
17. Install and Configure Automatic Storage Management (ASMLib 2.0)
18. Download Oracle RAC 11g Software
19. Pre-Installation Tasks for Oracle Clusterware 11g
20. Install Oracle Clusterware 11g
21. Install Oracle Database 11g
22. Install Oracle Database 11g Examples (formerly Companion)
23. Create TNS Listener Process
24. Create the Oracle Cluster Database
25. Post-Installation Tasks – (Optional)
26. Verify TNS Networking Files
27. Create / Alter Tablespaces
28. Verify the RAC Cluster and Database Configuration
29. Starting / Stopping the Cluster
30. Troubleshooting
31. Conclusion
32. Acknowledgements
33. About the Author

---

Overview

One of the most efficient ways to become familiar with Oracle Real Application Cluster (RAC) 11g technology is to have access to an actual Oracle RAC 11g cluster. In learning this new technology, you will soon start to realize the benefits Oracle RAC 11g has to offer like fault tolerance, new levels of security, load balancing, and the ease of upgrading capacity. The problem though is the price of the hardware required for a typical production RAC configuration. A small two node cluster, for example, could run anywhere from US$10,000 to well over US$20,000. This would not even include the heart of a production RAC environment, the shared storage. In most cases, this would be a Storage Area Network (SAN), which generally start at US$10,000.

For those who simply want to become familiar with Oracle RAC 11g, this article provides a low cost alternative to configure an Oracle RAC 11g system using commercial off the shelf components and downloadable software for educational purposes. The estimated cost for this configuration could be anywhere from US$2,000 to US$2,500. The system will consist of a dual node cluster (each with a single processor), both running Linux (CentOS 5.1), Oracle 11g Release 1, OCFS2, and ASMLib 2.0. All shared disk storage for Oracle RAC will be based on iSCSI using a Network Storage Server; namely Openfiler Release 2.2 (respin 2).

Powered by rPath Linux, Openfiler is a free browser-based network storage management utility that delivers file-based Network Attached Storage (NAS) and block-based Storage Area Networking (SAN) in a single framework. Openfiler supports CIFS, NFS, HTTP/DAV, FTP, however, we will only be making use of its iSCSI capabilities to implement an inexpensive SAN for the shared storage components required by Oracle RAC 11g. A 500GB internal hard drive will be connected to the network storage server (sometimes referred to in this article as the Openfiler server) through a SATA PCI Card. The Openfiler server will be configured to use this disk for iSCSI based storage and will be used in our Oracle RAC 11g configuration to store the shared files required by Oracle Clusterware as well as all Oracle ASM volumes.

Read more at www.idevelopment.info