Replicating MySQL Server ( Fedora 13 )

IP mysql server master = 192.168.1.193
IP myslq slave = 192.168.1.176

Master Side

- create user in mysql server master for replicating
user = replicator
pass = replicate
- setup the user privilage


[root@fazries.com ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.52-log Source distribution


Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> create user replicator@192.168.1.176 identified by 'replicate';
Query OK, 0 rows affected (0.04 sec)


mysql> grant replication slave on *.* to replicator@192.168.1.176 identified by 'replicate';
Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)


mysql> quit;
Bye

Edit the /etc/my.conf, added the bold lines
[root@fazries.com ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=drupaldb
server-id=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@fazries1.com ~]# service mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

Make the log file
and see the master status

[root@fazries1.com ~]# tail -n 50 /var/log/mysql.log
110104 14:28:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/libexec/mysqld: File '/var/log/mysql/mysql-bin.index' not found (Errcode: 2)
110104 14:28:35 [ERROR] Aborting
110104 14:28:35 [Note] /usr/libexec/mysqld: Shutdown complete
110104 14:28:35 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[root@fazries1.com ~]# mkdir /var/log/mysql
[root@fazries1.com ~]# touch /var/log/mysql/mysql-bin.index
[root@fazries1.com ~]# chown -R mysql:mysql /var/log/mysql
[root@fazries1.com ~]# service mysqld start
Starting mysqld:                                           [  OK  ]

[root@fazries.com ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.52-log Source distribution


Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 | drupaldb     |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> quit;
Bye
Dump Database to mysql slave
copied to slave server
[root@fazries.com ~]# mysqldump -u root -p --opt drupaldb > drupal.sql
Enter password: 
[root@fazries1.com ~]# scp drupal.sql root@192.168.1.176:/root
root@192.168.1.176's password: 
drupal.sql                                                                               100% 6970KB 410.0KB/s   00:17    
[root@fazries1.com ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.52-log Source distribution


Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)


mysql> quit;
Bye
Slave Side
Edit the /etc/my.cnf and added the Bold lines
[root@fazries.com ~]# cat /etc/my.cnf 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=2
master-host=192.168.1.193
master-user=replicator
master-password=replicate
master-connect-retry=60
replicate-do-db=drupaldb

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Make Database for replicating in this example drupaldb
Import the drupa.sql into database
Restart mysql server
[root@fazries.com ~]# mysql -u root -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 24

Server version: 5.1.51 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database drupaldb;

Query OK, 1 row affected (0.00 sec)

mysql> quit;

Bye

[root@fazries.com ~]# mysql -u root -p drupaldb < /root/drupal.sql 

Enter password: 

[root@fazries.com ~]# service mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]
Replacing the values
[root@fazries.com ~]# mysql -u root -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.51 Source distribution


Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)


mysql>change master to master_host='192.168.1.193'
    ->, master_user='replicator'
    ->, master_password='replicate'
    ->, master_log_file='mysql-bin.000001'
    ->, master_log_pos=106;
Query OK, 0 rows affected (0.20 sec)

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

mysql> show slave status;

---------------+-------------------------------+---------------+---------------+----------------+----------------+

| Slave_IO_State                   

| Master_Host   

| Master_User 

| Master_Port 

| Connect_Retry 

| Master_Log_File  

| Read_Master_Log_Pos 

| Relay_Log_File          

| Relay_Log_Pos 

| Relay_Master_Log_File 

| Slave_IO_Running 

| Slave_SQL_Running 

| Replicate_Do_DB 

| Replicate_Ignore_DB 

| Replicate_Do_Table 

| Replicate_Ignore_Table 

| Replicate_Wild_Do_Table 

| Replicate_Wild_Ignore_Table 

| Last_Errno 

| Last_Error 

| Skip_Counter 

| Exec_Master_Log_Pos 

| Relay_Log_Space 

| Until_Condition 

| Until_Log_File 

| Until_Log_Pos 

| Master_SSL_Allowed 

| Master_SSL_CA_File 

| Master_SSL_CA_Path 

| Master_SSL_Cert 

| Master_SSL_Cipher 

| Master_SSL_Key 

| Seconds_Behind_Master 

| Master_SSL_Verify_Server_Cert 

| Last_IO_Errno 

| Last_IO_Error 

| Last_SQL_Errno 

| Last_SQL_Error |

+----------------------------------+---------------+-------------+-------------+---------------+-----------

| Waiting for master to send event 

| 192.168.1.193 

| replicator  

| 3306 

| 60 

| mysql-bin.000001 

| 106 

| mysqld-relay-bin.000002 

| 251 

| mysql-bin.000001      

| Yes              

| Yes               

| drupaldb        

|                     

|                    

|                        

|                         

|                             

| 0 

|            

| 0 

| 106 

| 407 

| None            

|                

| 0 

| No                 

|                    

|                    

|                 

|                   

|                

| 0 

| No                            

| 0 

|               

| 0 

|                

|

+----------------------------------+---------------+-------------+-------------+---------------+------------------

1 row in set (0.00 sec)




mysql> quit;
Bye

MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.1.193).
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.



reference
http://www.howtoforge.com/mysql_database_replication
http://www.kalpin.us/2008/12/19/tutorial-replikasi-mysql/

Comments

  1. Hey, I am Brazilian, thanks for you for post!

    :-)

    ReplyDelete

Post a Comment