MySQL互为主从Replication

mysql互为主从Replication

一、环境<？XML:NAMESPACE PREFIX = O />

系统：CentOS x64Mysql：Version 5.1.47主机：A：192.168.10.101 root：linuxidc.com B：192.168.10.102 root：linuxidc.com

二、步骤

两台MySQL均如要开启binlog日志功能，开启方法：加入log-bin=mysql-bin

1、主机A配置文件如下：

# vi /etc/my.cnf

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/lib/mysql/mysql.errlog = /var/lib/mysql/query_log.loglog-slow-queries = /var/lib/mysql/slow_query_log.loguser=mysqldefault-character-set=utf8init_connect="SET NAMES utf8"# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-bin=mysql-binserver-id=1binlog-do-db=iccstm1 #是要记录日志的数据库binlog-ignore-db=mysql #是不要记录日志的数据库名，多个数据库中间用逗号（,）隔开replicate-do-db=iccstm1replicate-ignore-db=mysqllog-slave-updates #表示如果一个MASTER 挂掉的话，另外一个马上接管；一定要加上，否则不会把更新的记录写到二进制文件里slave-skip-errors=all #是跳过错误，继续执行复制操作sync_binlog=1auto_increment_increment=2auto_increment_offset=1 #这样A的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID [client]default-character-set=utf8 [mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

2、主机B配置文件如下：

3、SQL操作

可以选择锁表进一步操作，这里锁表的目的是为了生产环境中不让进新的数据，好让从服务器定位同步位置。初次同步完成后，记得解锁

【1】取得master值

A服务器：

mysql> show master statusG*************************** 1. row *************************** File: mysql-bin.000007 Position: 42617 Binlog_Do_DB: iccstm1Binlog_Ignore_DB: mysql1 row in set （0.00 sec）

B服务器：

mysql> show master statusG*************************** 1. row *************************** File: mysql-bin.000005 Position: 44687 Binlog_Do_DB: iccstm1Binlog_Ignore_DB: mysql1 row in set （0.00 sec）

记住上面两个数值，master_log_file对应File，master_log_pos对应Position

【2】指定同步位置（以下操作顺序不可乱）

&&&要先打开两台服务器的允许远程&&&Mysql> grant all privileges on *.* to "root"@"%" identified by "linuxidc.com" with grant option; B服务器：

MySQL> grant replication slave on *.* to "replication"@"%" identified by "linuxidc.com";
Query OK, 0 rows affected （0.00 sec） MySQL>flush privileges;
Query OK, 0 rows affected （0.00 sec）

A服务器：

mysql>change master to master_host="192.168.10.102", master_user="replication", master_password="linuxidc.com",master_log_file="mysql-bin.000005",master_log_pos=44687;MySQL> grant replication slave on *.* to "replication"@"%" identified by "linuxidc.com";
Query OK, 0 rows affected （0.00 sec） MySQL>flush privileges;
Query OK, 0 rows affected （0.00 sec）

B服务器：

mysql>change master to master_host="192.168.10.101", master_user="replication", master_password="linuxidc.com",master_log_file="mysql-bin.000007",master_log_pos=42617;

A、B服务器分别查看从服务器状态A服务器

mysql>start slave;mysql>show slave statusG*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.10.102Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 42617Relay_Log_File: mysqld-relay-bin.000002Relay_Log_Pos: 46592Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: iccstm1Replicate_Ignore_DB: mysqlReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 42617Relay_Log_Space: 40748Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:1 row in set （0.00 sec）

B服务器

mysql>start slave;mysql>show slave statusG*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.10.101Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 44687Relay_Log_File: mysqld-relay-bin.000002Relay_Log_Pos: 532Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: iccstm1Replicate_Ignore_DB: mysqlReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 44687Relay_Log_Space: 688Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:1 row in set （0.00 sec）

Slave_IO_Running: YesSlave_SQL_Running: Yes

查看以上两项的值，均为Yes则表示状态正常使用cmake方式安装mysql-5.5.10一个常见的ORA-00060死锁现象相关资讯 MySQL互为主从

Linux下 MySQL 互为主从配置（02/28/2015 19:55:21）

本文评论查看全部评论（0）