Mysql的AB复制
环境:rhel 6.5
master:192.168.2.1
slave1:192.168.2.2
slave2:192.168.2.10
1.mysql的单向复制
注: mysql数据库的版本,两个数据库版本要相同,或者slave比master版本高!
yum install mysql-server -y
请注意当你进行复制时,所有对复制中的表的更新必须在主服务器上进行。否则,你
必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的
更新之间的冲突。
master server配置
开启mysqld服务
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 106 | westos | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
1)创建同步帐户,并给予权限
mysql> grant replication slave, reload, super on *.* to westos@'%' identified by 'westos';
mysql>flush privileges;
2 )配置 /etc/my.cnf配置文件
在[mysqld]下添加一下参数
log-bin=mysql-bin #启动二进制日志系统
binlog-do-db=westos #二进制需要同步的数据库名
server-id=1 #必须为1到232–1之间的一个正整数值
binlog-ignore-db=mysql #避免同步 mysql用户配置,以免不必要的麻烦
3 )service mysqld restart
slave server配置
1 )配置 /etc/ my.cnf文件
在[mysqld]下添加一下参数
server-id=2 #从服务器 ID号,不要和主ID相同,如果设置多个从服务器,每个从服务器必须有一个唯一的server-id值,必须与主服务器的以及其它从服务器的不相同。可以 认为server-id值类似于IP地址:这些ID值能唯一识别复制服务器群集中的每个服务 器实例。
2 )service mysqld start
mysql -uwestos -pwestos -h 192.168.2.1 #进入mysql
mysql> change master to master_host='192.168.2.1', master_user='westos', master_password='westos', master_log_file='mysql-bin.000003', master_log_pos=106; #注意跟master的日志文件,日志位置保持一致
mysql> slave start;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.1
Master_User: westos
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
在master创建数据库,创建表添加数据,看slave server是否同步
mysql> create database westos;
mysql> use westos;
mysql> create table linux(
-> username varchar(20) not null,
-> password varchar(50) not null
-> );
mysql> desc linux;
mysql> insert into linux values ('user1','123');
mysql> insert into linux values ('user2',password(123));
mysql> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
slave机子上:
mysql> show databases;
mysql> select * from westos.linux; #数据同步成功
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
master上更改:
mysql> update linux set password=456;
slave上:#查看数据同步已过来
mysql> select * from westos.linux;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 456 |
| user2 | 456 |
+----------+----------+
线性A- > B - > C,master - >slave1- >slave2
添加一个slave2,192.168.2.10
在slave1即node2上修改配置文件
vim /etc/my.cnf
server-id=2
添加:log-bin=mysql-bin
binlog-do-db=westos
inlog-ignore-db=mysql
log-slave-updates
/etc/init.d/mysqld restart #重启mysql
授权mysql>grant replication slave, reload, super on *.* to westos@'%' identified by 'westos';
mysql> flush privileges;
在slave2即192.168.2.10上:
验证权限 mysql -uwestos -pwestos -h 192.168.2.2看能否登录进mysql
vim /etc/my.cnf
server-id=3
/etc/init.d/mysqld restart #重启mysql
在slave1上:
由于 master上已经有数据,而新加的slave2没有,必须在配置复制前同步数据。
把westos库里的表导入到back里
mysqldump westos > back
scp back 192.168.2.10: #把库发给slave2
在slave2上:
mysqladmin create westos #创建westos库
mysql westos < back #把back库里的表导入到westos库
mysql> show databases;
mysql> use westos;
mysql> show tables;
mysql> select * from linux;
在slave1上mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | westos | mysql |
+------------------+----------+--------------+------------------+
slave2上:
mysql> change master to master_host='192.168.2.2', master_user='westos', master_password='westos', master_log_file='mysql-bin.000001', master_log_pos=106
mysql> slave start;
mysql> show salve status\G; #查看状态是否正常
现在开始测试:
在A master上mysql> delete from linux;
验证查看B slave1和C slave2 mysql> select * from linux;
在A master上mysql> insert into linux values ('sxy','123');
验证查看B slave1和C slave2 mysql> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| sxy | 123 |
+----------+----------+