����Linux��
Linux����

Mysql Replication

����ʱ��:2006-08-23 09:53:58��Դ:��������:����
�����ڴ�һ̨�������������ݿ⵽��һ̨�������ϣ�������ͨ������Master ��Slave�Ĺ�ϵȥʵʱ�ر�֤�������ݿ����ȫͬ�������������Mysql��3.23���п�ʼ���֡�
Master/Slaveģʽ����
TestEnv:
Master��Mysql-4.1.12 on Redhat9.0 IP:192.168. 0.217
Slave�� Mysql-4.1.12 on Redhat9.0 IP:192.168.10.244
1�����룬��װ
1. #tar -zxvf Mysql-4.1.12.tar.gz
2. #cd Mysql-4.1.12
3. .#/configure -prefix=/var/eyou/mysql
4. #make
5. #make install
6. #chown -R root /var/eyou/mysql
7. # chown -R mysql /var/eyou/mysql/var
8. #chgrp -R mysql /var/eyou/mysql
9. #scripts/mysql_install_db
10. #cp support-files/my-medium.cnf /etc/my.cnf
2��Master ��������Ȩ�ޣ�����Slave����FILE��Replication SlaveȨ���������Ҫͬ�������ݿ�ṹ��
Master# pwd
/var/eyou/mysql/bin
Master#./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.12
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT FILE ON *.* TO rep@192.168.0.244 IDENTIFIED BY ��eyou��;
mysql> GRANT REPLICATION SLAVE ON *.* TO rep@192.168.0.244 IDENTIFIED BY ��eyou��;
����192.168.10.244Ҳ����Slave ������FileȨ��, ���4.1.12���replication��Ȩ�޺������˵�����ֻ����Slave������FileȨ�޻����У���Ҫ����REPLICATION SLAVE��Ȩ�޲ſ��ԡ�
Ȼ����Ҫ���Ƶ����ݿ�
Master# cd var
Master# tar czvf reptest.tar.gz reptest
���������ǵõ�һ��reptest���ݿ�Ĵ���ļ�reptest.tar.gz
2������������Master��my.cnf������Mysql����
Master# vi /etc/my.cnf
��[mysqld]���ӻ��޸����µ�
[mysqld]
log-bin #��logbinѡ������д��slave�� I/O�߳�;
server-id=1 #��ʾ�DZ��������Ϊ1,һ����������master����˼.
sql-bin-update-same
binlog-do-db= reptest #��ʾͬ��reptest���ݿ�;
Ȼ���Master����������Mysql������
Master# /var/eyou/mysql/bin/mysqladmin -u root -p shutdown
Master# /var/eyou/mysql/bin/safe_mysqld --user=mysql &
3������Slave���ݿ�
�ղ���Master�д����reptest.tar.gz���������þ���Ҫ��Slave�ָ���һ�������ݿ⡣�Ȱ�Master ��reptest.tar.gz�ļ�����Slave������ȥ��Ȼ��
Slave# tar zxvf reptest.tar.gz -C /var/eyou/mysql/var/
4���޸�Slave��������my.cnf
Slave# vi /etc/my.cnf
��[mysqld]���ӻ��޸����µ�
master-host=192.168.10.217
master-user=rep
master-password=eyou
master-port=3306
server-id=2
master-connect-retry=60
replicate-do-db=reptest ������[Ҫ���µ����ݿ�]
log-slave-updates
5��ɾ��Slave�����ݿ�Ŀ¼�е�master.info
Slave# rm /var/eyou/mysql/var/master.info
6��������Slave��slave start��
Slave# /var/eyou/mysql/bin/mysqladmin -u root -p shutdown
Slave# /var/eyou/mysql/bin/safe_mysqld --user=mysql &
7������
�ȼ������Mysql���ݿ��е�reptest�Ƿ�������
�������Ӧ����Master��Slave �е�Mysql ������ͬ��reptest ���ݿ⣬������������ݶ�һ����
Ȼ�����replication �����Ƿ����á�
��Master�е�reptest���ݿ�����һ�����ݣ�
Master# /var/eyou/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.1.12
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use reptest;
Database changed
mysql> INSERT INTO rep_table VALUES ('test1', '4321', 'T',24);
Query OK, 1 row affected (0.00 sec)
mysql>
Ȼ��鿴Slave������reptest���ݿ⣺
Slave#/var/eyou/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.1.12
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use reptest;
Database changed
mysql>select * from reptable;;
+------+------+------+------+
| id | name | sex | age |
+------+------+------+------+
| test1| 4321 | T | 24 |
+------+------+------+------+
1 row in set (0.00 sec)

PS :
1��Slave������Ȩ�����⣬����Ҫ��slave����FileȨ�ޣ���Ҫ����REPLICATION SLAVE��Ȩ�ޡ�
2�����޸���Slave����/etc/my.cnf֮��slave������mysql��������֮ǰ���ǵ�Ҫɾ����master.info
3����show master status ����show slave status ������ʱ������.err������˵�ġ�
4��Slave��Mysql��Replication�����������߳�, I/O thread��SQL thread ��I/O �������Ǵ�master 3306�˿��ϰ�����binlogȡ����(master�ڱ��޸����κ�����֮��,�ͻ���޸���ʲôд���Լ���binlog�ȴ�slave����),Ȼ��д�����ص�relay-log,��SQL thread����ȥ�����ص�relay-log,�ٰ���ת���ɱ�Mysql�����������䣬����ͬ��������һ��һ�������.����I/O thread����/var/lib/mysql/master.info,������SQL thread����/var/lib/mysql/relay-log.info.

˫����ģʽ
1��
Slave ��������Ȩ�ޣ�����Master����FILE��Replication SlaveȨ��.
Master#./var/eyou/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.12
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT FILE ON *.* TO rep@192.168.0.217 IDENTIFIED BY ��eyou��;
mysql> GRANT REPLICATION SLAVE ON *.* TO rep@192.168.0.217 IDENTIFIED BY ��eyou��;
2��
��ԭ�е�master/slave���û������������޸ģ�
Master# vi /etc/my.cnf
���ӣ�
master-host=192.168.0.244 #��ʾ������slaveʱ��masterΪ192.168.0.244;
master-user=rep #�����ʾmaster�Ͽ��ŵ�һ����Ȩ�޵��û�
master-password=eyou #����û�������
master-connect-retry=60 #
rplicate-do-db=reptest #�������ݵ����ݿ�
log-slave-updates
�����˳�����mysql����
Slave#vi /etc/my.cnf
���ӣ�
binlog-do-db=reptest
sql-bin-update-same
�����˳�����mysql����
PS :
1����ǰSlave������server-id���ñ�
2��������mysql����󣬼ǵÿ�һ��slave status�����slave stop / slave start
��������ǰ�����µĻ��������4.1.12�汾���е��޸ģ�����в�׼ȷ�ĵط���������Ҹ�������лlinux����Ŀ�Դ����


����mysql replication��ʱ��һ��Ҫע��mysql����Ȩ����ͬ�İ汾��Ȩ������һ����������ִ��󣬿��Դ���־���濴������ԭ�򣬲�������ʾ���Խ�����⡣

[ ��������� ���� �� 2006-8-23 09:55 �༭ ]
��������

���� 0 ������