MySQL Daily Backup and Transfer to other Server

When you have 2 MySQL server which is not running in replication or cluster, is recommended to have MySQL backup running daily. This will help you on fast restoration, reliable data backup and disaster recovery.

I have created a bash script to run daily and make sure the data is save into local disk and another copy being transferred to another server via rsync. You can use following script and change the value to suit your environment. I am using following variables:

OS: CentOS 5.6 64bit
Server1: 192.168.1.2
Server2: 192.168.1.3
Backup user: mysql_backup
Backup user password: l3tsb4ckup
Backup path: /backup

Before we use the script, is good to have a specific user to run the backup scripts. Now lets start configuring in Server1:

1. Create specific user to run the backup:

[root@centos ~] useradd mysql_backup
[root@centos ~] passwd mysql_backup

(notes: enter the password 2 times as above)

2. Assign that user to a specific location inside your server:

[root@centos ~] usermod -d /backup mysql_backup

3. Test the user environment by using su and create ssh-key so this user will have password-less connection to another server:

[root@centos ~] su - mysql_backup
-bash-3.2$ mkdir ~/.ssh/
-bash-3.2$ ssh-keygen -t dsa

(notes: just press enter for all prompts)

4. Now login to Server2 via root and repeat step 1 to step 3. Once done, continue below step in both servers to transfer the SSH key for mysql_backup user. This will allow password-less connection between each of them:

-bash-3.2$ cat ~/.ssh/id_dsa.pub | ssh mysql_backup@192.168.1.2 "cat -- >> ~/.ssh/authorized_keys"

(notes: enter mysql_backup’s password which is l3tsb4ckup)

Continue reading “MySQL Daily Backup and Transfer to other Server” »

Install MySQL Replication and Cluster using Galera

INFO: Updated version of Galera cluster installation with high-availability MySQL setup can be refer here

What is Galera? Its generic synchronous multi-master replication plugin for transactional applications.

I will not telling you about this application cause you can read and understand by go to http://www.codership.com/content/using-galera-cluster or refer to the image for Galera overview. I just show you on how to install Galera/MySQL with 1 master and 1 slave (joined node) architecture.

Please find variables that I used as below:

OS: CentOS 5.6 64bit
Server1: 192.168.1.1 (master)
Server2: 192.168.1.2 (joined node)
MySQL root password: rootpass
SST MySQL user: sst
SST MySQL password: sstpass123

Configuration in both servers are quite similar. So I will show you on how to install Galera MySQL in Server1 and then same procedure in Server2 with some minor changes. Lets configure Server1 first:

1. Install gcc44 and gcc44-c++ packages and set CC and CXX variables accordingly:

[root@centos ~] yum install gcc44 gcc44-c++ -y
export CC=gcc44
export CXX=g++44

2. Lets check what kind of MySQL applications installed in the box:

[[email protected] ~] rpm -qa | grep mysql
mysql-5.0.77-4.el5_6.6
mod_auth_mysql-3.0.0-3.2.el5_3
mysql-bench-5.0.77-4.el5_6.6
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-server-5.0.77-4.el5_6.6
mysql-test-5.0.77-4.el5_6.6
libdbi-dbd-mysql-0.8.1a-1.2.2
mysql-devel-5.0.77-4.el5_6.6
mysql-devel-5.0.77-4.el5_6.6
php53-mysql-5.3.3-1.el5_6.1
mysql-5.0.77-4.el5_6.6

3. Lets download all needed applications. Download following applications in respective site:

URL: https://launchpad.net/codership-mysql/+download
Download MySQL-server-wsrep-5.1.53-0.8.0b-x86_64.rpm = the MySQL server
URL: https://launchpad.net/galera/0.8/0.8.0
Download galera-0.8.0-x86_64.rpm = Galera library
URL: http://dev.mysql.com/downloads/mysql/5.1.html
Download the client-community and shared-community for 64bit

4. Following files should exist if you download the correct version:

[[email protected] galera] ls -1
galera-0.8.0-x86_64.rpm
MySQL-client-community-5.1.57-1.rhel5.x86_64.rpm
MySQL-server-wsrep-5.1.53-0.8.0b-x86_64.rpm
MySQL-shared-community-5.1.57-1.rhel5.x86_64.rpm

5. Lets remove currently installed mysql. Dont worry about this, because we are actually upgrading our MySQL to version 5.1:

[[email protected] galera] rpm -e --nodeps mysql.i386 mysql.x86_64 mysql-devel.i386 mysql-devel.x86_64 mysql-server mysql-bench

Continue reading “Install MySQL Replication and Cluster using Galera” »

The Best Way to Setup MySQL Replication

In this case, I have 2 servers installed with MySQL 5 and running in CentOS 5. Assuming Server1 is master server and currently host a database called db1 and Server2 is slave server and have nothing inside.

Variable used in this case:

Server1 IP=192.168.1.1
Server2 IP=192.168.1.2
Database name=db1
Replication user=replicator
Replication user password=slavepass

Now we configure Server1 first. Login to the server and do following:

1. Login to Server1 and open MySQL configuration file, /etc/my.cnf and add following lines under [mysqld] section:

[mysqld]
server-id=100
log-bin=master-bin
									

2. Restart MySQL:

service mysqld restart
									

3. Create a user account so slave can use to connect to this master server:

mysql> GRANT REPLICATION SLAVE on *.* to 'replicator'@'%' IDENTIFIED BY 'slavepass';
									

4. Dump the database so we can bring the database to the slave server:

mysqldump -u[root] -p[password] --master-data --single-transaction db1 > db1.sql
									

Continue reading “The Best Way to Setup MySQL Replication” »

Shrink MSSQL Database Transaction Logs

Have you ever encounter problem when managing MSSQL databases, the transactions logs is eating up your hard disk if you not initially configured to be limit in growth value. So, when you see this kind of problem, you actually can shrink the transaction log, and then change the limit of growth value using MSSQL Management Studio.

1. Open MSSQL Management Studio

2. Open the SQL command windows and put following lines

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
									

Replace the <DatabaseName> and <TransactionLogName> with respective value and execute the SQL. Now your transaction logs will be shrinked and you will save a lot of disk space. Do not forget to do the prevention action afterwards!