Tutorial Planet

1. T ake dump of the master DB to whom you want to replicate . Plz note that if you are using mysqldump command then It should be like “mysqldump -u$USER -p$PASSWORD $DATABASENAME  –master-data>$FILETOSTORETHEDUMP” .DONOT FORGET TO INCLUDE “–master-data” otherwise replication won’t happen.

2.Transfer the dump file to the the slave server and give permission to the mysql to access this file.

3. Comment all the crontab job or all existing tasks which is using mysql on slave.

4. Open konsole on slave

5. mysql -u$USER -p$PASSWORD (open mysql on slave)

6. stop slave; (run this command into mysql  prompt)

7. Open the my.cnf file on slave(usually location is: /etc/my.cnf)

8. add the required DB /DB-tables (e.g. replicate-wild-do-table=$DATABASENAME.%)

9. Change/Add the Master-credentials

like : master-host=Master-IP-Address
master-user=$MASTER_MYSQL_USER_ID
master-password=$MASTER_MYSQL_PASSWORD

10. go to the Mysql Prompt on Slave again

11. start slave;

12. show slave status\G;(check what is the master-IP showing. It should be the old one)

13. reset slave;

14. again open new terminal on slave.

15. /etc/init.d/mysql restart

16. Go to again mysql prompt.

17. Drop the database which you want to replacate here but aleardy exists.

18. Create database $DATABASENAME;

19. stop slave;

19. source the dump file now using source command

20. start slave; (now your replication should work )

21. come out of mysql prompt and enable all the commented and stop jobs which depend over  mysql on slave node.

Njoy the replication now

Though following instructions are given for REHL4 but It should work for any REHL-version . Just you need to check compatible REHL-packages

1.   first login with your credentials on the machine, on which you intend to perform Mysql upgrade.

Then type  the following command:
$ su
(If you don’t have super user credentials, please contact administration).

2. Get  Newer version of MySql  packages  to your home:
http://dev.mysql.com/downloads/  e.g MySQL5.1.
# ll -h /$MYSQL_HOME/MySQL5.y
e.g.
-rw-r–r–  1 user root 7.0M Feb  5 04:04 MySQL-client-community-5.y.rhel4.i386.rpm

-rw-r–r–  1 user root 7.7M Feb  5 04:04 MySQL-devel-community-5.y.rhel4.i386.rpm

-rw-r–r–  1 user root  18M Feb  5 04:04 MySQL-server-community-5.y.rhel4.i386.rpm

-rw-r–r–  1 user root 1.7M Feb  5 04:04 MySQL-shared-community-5.y.rhel4.i386.rpm

-rw-r–r–  1 user root 5.0M Feb  5 04:04 MySQL-shared-compat-5.1.30-0.rhel4.i386.rpm

-rw-r–r–  1 user root  12M Feb  5 04:04 MySQL-test-community-5.1.30-0.rhel4.i386.rpm

(Verify if they are correct)

Note: Currently I am taking simple example to upgrade Mysql 5.0.x to 5.1.x. You just focus on type of abover mentioned six files. They should be there.

3. Ensure that all services attached to your mysql are not active for the given machine.

4. Ensure that the port on which the service is running is blocked by firewall.

5. # rpm -qa | grep -i mysql
MySQL-server-standard-5.x.rhel4

MySQL-client-standard-5.x.rhel4

MySQL-shared-compat-5.x.rhel4

MySQL-devel-standard-5.x.rhel4

mysql-connector-odbc-3.51.14-0 (Please neglect the java connector driver)

(Following are the MySql 5.x packages. We must uninstall all of the above packages  except  java connector driver)

6. Shutdown MySql instances running:

#/etc/init.d/mysql stop

(do a ps -ef | grep mysql to check if  instances are running on other(non default) port.  If they are shut them down ).

Also, DONOT do a “kill -9 <mysql-pid>“  to stop any of the instance.

7. Remove packages :

(DONOT remove  mysql-connector)

#rpm -e –nodeps MySQL-devel-standard-5.x.rhel4

#rpm -e –nodeps MySQL-shared-compat-5.x.rhel4

#rpm -e –nodeps MySQL-client-standard-5.x.rhel4

#rpm -e –nodeps MySQL-server-standard-5.x.rhel4

8. Install new mysql packages:

#cd /root/MySQL5.y

#rpm -ivh  MySQL-client-community-5.y.rhel4.i386.rpm
#rpm -ivh MySQL-server-community-5.y.rhel4.i386.rpm
#rpm -ivh MySQL-shared-compat-5.y.rhel4.i386.rpm
#rpm -ivh MySQL-devel-community-5.y.rhel4.i386.rpm

Generally,  no problem is encountered while package addition.
However, at a times, readline package incompatibility might make package installation to fail.

Run the following command:

# rpm -qa | grep -i readline
e.g.
readline-4.3-11.99_2.el4.at

readline-devel-4.3-11.99_2.el4.at

Ensure that you have correct version of readline package installed.

9.  login to mysql prompt :

1. #mysql -u#USER -p#PASSWORD

then type following command (If you have any replication on)

mysql>  check slave status \G
Ideally, you must  NOT SEE any error in the list.
Also, verify that  replication is running by checking if “Relay_Log_Pos: ” value is changing, each time  you hit the command “mysql>check slave status \G”

10.  Start services which were shutdown.

11. Unblock the port, if applicable.
Verify if  the system is stable, if the service is has started serving requests.

12. Enable  all the services which run over mysql .

As we know the correct mysql update statement is like: update table_name set X=x, Y=y,…;

but suppose someone by mistake puts “and” operator instead of “,”  i.e:
update table_name set  X=x and Y=y; => X and Y get initialized with their previously assigned/default values(seems quite random assignments)  not with their current assigned values in the query.

Surprisingly mysql doesn’t throw error in such case.