Borndba

Recently i ventured into MYSQL. I did a installation and replication setup of mysql. Though i don’t entirely understand each steps, i believe it gave me a good start to this db. SQLSERVER and DB2 always on the card.

Uninstalling Any Previous MySQL Installation
============================================

1. Check the release:

On the server as root user:

# rpm -qa | grep -i mysql

2. Remove software
# rpm -e libdbi-dbd-mysql
# rpm -e MySQL-python
# rpm -e mod_auth_mysql
# rpm -e php-mysql
# rpm -e mysql-bench
# rpm -e mysql-server
# rpm -e freeradius-mysql
# rpm -e mysql-devel
# rpm -e mysql perl-DBD-MySQL MyODBC qt-MySQL

3. Remove user: mysql

# userdel mysql

Create MySQL User Account:
==========================

# cd /usr/local
# groupadd mysql
# useradd -c "MySQL Software Owner" -g mysql mysql
# passwd mysql

INstall Binaries:
=================

1. cp /tmp/mysql-standard-4.1.22-pc-linux-gnu-i686.tar.gz /data/.
2. cd /data (/usr/local)
3. gunzip mysql-standard-4.1.22-pc-linux-gnu-i686.tar.gz
4. tar xvf mysql-standard-4.1.22-pc-linux-gnu-i686.tar
5. mv mysql-standard-4.1.22-pc-linux-gnu-i686 mysql_4.1.22
6 cd /usr/local
7 ln -s /data/mysql_4.1.22 mysql
6. cd mysql

As root user
============

1. cd /usr/local/mysql
2. scripts/mysql_install_db --user=mysql

3.
[root@Primary mysql]# pwd
/usr/local/mysql
[root@Primary mysql]# chown -R root .
[root@Primary mysql]# chown -R mysql data
[root@Primary mysql]# chgrp -R mysql .

4. cd /usr/local/mysql/bin
5. ./mysqld_safe --user=mysql & (STARTS DB)
6. ./mysqladmin ping
7. ./mysqladmin version
8 ./mysqladmin -u root shutdown

Set Passwd (NOT REQUIRED)
===========================
cd /usr/local/mysql
./bin/mysqladmin -u root password 'mysql123'
./bin/mysqladmin -u root -h Primary.shans.net password 'mysql123'

===

[root@Primary bin]# ./mysqladmin version
./mysqladmin Ver 8.41 Distrib 4.1.22, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.1.22-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 30 sec

Threads: 1 Questions: 2 Slow queries: 0 Opens: 11 Flush tables: 1
Open tables: 5 Queries per second avg: 0.067

./mysqladmin version
./mysqladmin Ver 8.41 Distrib 4.1.22, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.1.22-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 23 sec

Threads: 1 Questions: 2 Slow queries: 0 Opens: 11 Flush tables: 1
Open tables: 5 Queries per second avg: 0.087

====

REPLICATION SETUP:

================================================================================
PRIMARY
================================================================================

First Create Replication DB
===========================

mysql> create database repldb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| repldb |
| test |
+----------+
3 rows in set (0.00 sec)

Create Table
============

mysql> use repldb;

CREATE TABLE Test_Table
(Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Test_Name VARCHAR(30),
Test_Date DATETIME,
Test_Giver VARCHAR(30));

INSERT INTO Test_Table
(Test_ID, Test_Name, Test_Date, Test_Giver)
VALUES
(NULL, 'Test','2000-01-01','Pramod');

mysql> describe Test_Table;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| Test_ID | int(11) | | PRI | NULL | auto_increment |
| Test_Name | varchar(30) | YES | | NULL | |
| Test_Date | datetime | YES | | NULL | |
| Test_Giver | varchar(30) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

mysql> select * from repldb.Test_Table;
+---------+-----------+---------------------+------------+
| Test_ID | Test_Name | Test_Date | Test_Giver |
+---------+-----------+---------------------+------------+
| 1 | Test | 2000-01-01 00:00:00 | Pramod |
+---------+-----------+---------------------+------------+
1 row in set (0.00 sec)

INSERT INTO Test_Table
(Test_ID, Test_Name, Test_Date, Test_Giver)
VALUES
(NULL, 'Test','2007-01-10','Nallaiya');

CREATE USER
===========

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'
IDENTIFIED BY 'repl123';

FLUSH PRIVILEGES;

mysql> select * from mysql.user;

mysql> select * from mysql.user where user='repl_user'G
*************************** 1. row ***************************
Host: %
User: repl_user
Password: *A9CF6959A2DAECF248BA652C29231C5B78B13FF7
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: Y
Repl_client_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
1 row in set (0.00 sec)

mysql>

Shutdown DB
========================

# cd /usr/local/mysql/bin
[root@Primary bin]# ./mysqladmin -u root shutdown
[root@Primary bin]# ps -ef | grep mysql
root 12951 31519 0 22:59 pts/0 00:00:00 grep mysql
[root@Primary bin]#

Edit COnfig FIle
========================
cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf
chown mysql:mysql /etc/my.cnf

Add/Edit below parameters:
-------------------------
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=repldb
server-id=1

chmod 777 /var/log/mysql

LOck Tables
============

mysql>use repldb;
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 79 | repldb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Take Dump FIle
==============

./mysqldump -u root -- repldb > /tmp/repldb.sql

UNLOck Tables
=============
mysql>use repldb;
mysql>unlock tables;
mysql>quit;

================================================================================
SLAVE
================================================================================

Create DB
=========

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.01 sec)

mysql> create database repldb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| repldb |
| test |
+----------+
3 rows in set (0.00 sec)

SCP DUmp FIle From Primary
===========================
[root@Primary bin]# scp /tmp/repldb.sql Secondary:/tmp/.
root@Secondary.shans.net's password:
repldb.sql

DUmp file into DB
=================
# cd /usr/local/mysql/bin
#./mysql -u root repldb < /tmp/repldb.sql

SHutdown DB
============
# ./mysqladmin -u root shutdown

COpy my.cnf from Primary to /etc/my.cnf
=======================================

Make below changes:

server-id=2
master-host=192.168.13.118
master-user=repl_user
master-password=repl123
master-connect-retry=60
replicate-do-db=repldb

# chown mysql:mysql /etc/my.cnf

Start MYSQL
==========

#./mysql

mysql> slave stop;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.13.118'
-> , MASTER_USER='repl_user'
-> , MASTER_PASSWORD='repl123'
-> , MASTER_LOG_FILE='mysql-bin.000001'
-> , MASTER_LOG_POS=79;
Query OK, 0 rows affected (0.01 sec)

MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> quit