mysql ab复制
关于mysql ab复制
本文讲解如何快速打包和安装mysql， mysql ab复制，mysql ab双向复制，mysql多级主从复制，解决ab双向复制主键冲突。
首先我们先介绍什么是mysql ab复制。
ab复制又称主从复制，实现的是数据同步。如果要做mysql ab复制，数据库版本尽量保持一致。如果版本不一致，从服务器版本高于主服务器，但是版本不一致不能做双向复制。mysql ab复制有什么好处呢？有两点，第一是解决宕机带来的数据不一致，因为mysql ab复制可以实时备份数据；第二点是减轻数据库服务器压力，这点很容易想到，多台服务器的性能一般比单台要好。但是mysql ab复制不适用于大数据量，如果是大数据环境，推荐使用集群。
然后我们来看看mysql复制的 3 个主要步骤：
1)主服务器把数据更改记录到二进制日志中，这个操作叫做二进制日志事件；
2)从服务器把主服务器的二进制日志事件拷贝到自己的中继日志（relay log）中；
3)从服务器执行中继日志中的事件，把更改应用到自己的数据上。
快速打包和安装mysql
在正式介绍mysql ab复制之前，介绍怎样打包mysql和快速安装mysql。
第一步，制作文件
[root@serv08 ~]# find /usr/local/mysql/ /etc/my.cnf /etc/init.d/mysqld > mysql第二步，打包
[root@serv08 ~]# tar -cpvzf mysql-5.5.29-linux2.6-x86_64.tar.gz -t mysql
[root@serv08 ~]# ll -h
total 202m
-rw-r--r--. 1 root root 411k oct 5 19:19 mysql
-rw-r--r--. 1 root root 202m oct 5 19:21 mysql-5.5.29-linux2.6-x86_64.tar.gz第三步，拷贝文件到实体机
[root@serv08 mysql]# scp mysql-5.5.29-linux2.6-x86_64.tar.gz 192.168.1.1:/home/wentasy/software/第四步，拷贝文件到serv01
[root@serv01 ~]# yum install /usr/bin/scp -y[root@larrywen 1005]# scp /home/wentasy/software/mysql-5.5.29-linux2.6-x86_64.tar.gz 192.168.1.11:/opt
root@192.168.1.11's password:
mysql-5.5.29-linux2.6-x86_64.tar.gz 100% 201mb 33.5mb/s 00:06 第五步，解压
[root@serv01 opt]# tar -xpvf mysql-5.5.29-linux2.6-x86_64.tar.gz第六步，创建组和用户，注意编号和安装好数据库的机器上的用户一致
[root@serv01 opt]# groupadd -g 500 mysql
[root@serv01 opt]# useradd -u 500 -g 500 -r -m -s /sbin/nologin mysql
[root@serv01 opt]# id mysql
uid=500(mysql) gid=500(mysql) groups=500(mysql)第七步，改变mysql安装目录的拥有者和所属组
[root@serv01 opt]# chown mysql.mysql /usr/local/mysql/ -r第八步，启动mysql，做测试
[root@serv01 opt]# /etc/init.d/mysqld start
starting mysql.. success!
[root@serv01 opt]# mysql
-bash: mysql: command not found
[root@serv01 opt]# vim ~/.bash_profile
[root@serv01 opt]# . !$
. ~/.bash_profile
[root@serv01 opt]# mysql
welcome to the mysql monitor. commands end with ; or /g.
your mysql connection id is 1
server version: 5.5.29-log source distributioncopyright (c) 2000, 2012, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.type 'help;' or '/h' for help. type '/c' to clear the current input statement.mysql> mysql ab单向复制
好了，相信读者已经学会怎样打包mysql和快速安装mysql，接下来正式进入主题，我们先来看看一主多从架构的拓扑图：
图一 一主多从架构
该图展示了一个 master 复制多个 slave 的架构，多个 slave 和单个 slave 的实施并没有实质性的区别，在 master 端并不在乎有多少个 slave 连接自己，只要有 slave 的 io 线程通过了连接认证，向他请求指定位置之后的 binary log 信息，他就会按照该 io 线程的要球，读取自己的binary log 信息，返回给 slave的 io 线程。
既然对拓扑图和原理有所了解，我们做一个实验，介绍如何使用mysql ab复制：
实验环境介绍
主机 ip地址 主机名 备注
serv01： 192.168.1.11 serv01.host.com master
serv08： 192.168.1.18 serv08.host.com slave01
操作系统版本：rhel server 6.1
所需要的软件包：mysql-5.5.29-linux2.6-x86_64.tar.gz
第一步，主服务器创建用户并清空日志
mysql> show privileges;
mysql> grant replication client, replication slave on *.* to 'larry'@'192.168.1.%' identified by 'larry';
query ok, 0 rows affected (0.00 sec)mysql> show binary logs;
+------------------+-----------+
| log_name | file_size |
+------------------+-----------+
| mysql-bin.000001 | 27320 |
| mysql-bin.000002 | 1035309 |
| mysql-bin.000003 | 126 |
| mysql-bin.000004 | 279 |
+------------------+-----------+
4 rows in set (0.00 sec)mysql> show databases;
+--------------------+
| database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)mysql> reset master;
query ok, 0 rows affected (0.02 sec)mysql> show binary logs;
+------------------+-----------+
| log_name | file_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)第二步，修改从服务器的server-id
[root@serv08 ~]# cat /etc/my.cnf | grep server-id
server-id = 1
#server-id = 2
[root@serv08 ~]# vim /etc/my.cnf
[root@serv08 ~]# cat /etc/my.cnf | grep server-id
server-id = 2
#server-id = 2
[root@serv08 ~]# /etc/init.d/mysqld restart
shutting down mysql... success!
starting mysql.. success! #可以查看从服务器中的数据文件
[root@serv08 ~]# cd /usr/local/mysql/data/
[root@serv08 data]# ll
total 29752
-rw-rw----. 1 mysql mysql 18874368 oct 5 19:45 ibdata1
-rw-rw----. 1 mysql mysql 5242880 oct 5 19:45 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 oct 5 18:16 ib_logfile1
drwxr-xr-x. 2 mysql mysql 4096 oct 5 18:15 mysql
-rw-rw----. 1 mysql mysql 27320 oct 5 18:15 mysql-bin.000001
-rw-rw----. 1 mysql mysql 1035309 oct 5 18:15 mysql-bin.000002
-rw-rw----. 1 mysql mysql 126 oct 5 18:16 mysql-bin.000003
-rw-rw----. 1 mysql mysql 126 oct 5 19:45 mysql-bin.000004
-rw-rw----. 1 mysql mysql 107 oct 5 19:45 mysql-bin.000005
-rw-rw----. 1 mysql mysql 95 oct 5 19:45 mysql-bin.index
drwx------. 2 mysql mysql 4096 oct 5 18:15 performance_schema
-rw-r-----. 1 mysql root 4775 oct 5 19:45 serv08.host.com.err
-rw-rw----. 1 mysql mysql 5 oct 5 19:45 serv08.host.com.pid
drwxr-xr-x. 2 mysql mysql 4096 oct 5 18:12 test第三步，从服务器清空日志
mysql> show binary logs;
error 2006 (hy000): mysql server has gone away
no connection. trying to reconnect...
connection id: 1
current database: *** none ***+------------------+-----------+
| log_name | file_size |
+------------------+-----------+
| mysql-bin.000001 | 27320 |
| mysql-bin.000002 | 1035309 |
| mysql-bin.000003 | 126 |
| mysql-bin.000004 | 126 |
| mysql-bin.000005 | 107 |
+------------------+-----------+
5 rows in set (0.00 sec)mysql> reset master;
query ok, 0 rows affected (0.02 sec)mysql> show binary logs;
+------------------+-----------+
| log_name | file_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)mysql> show slave status;
empty set (0.00 sec)第四步，从服务器通过change master to命令修改设置
mysql> change master to
-> master_host='192.168.1.11',
-> master_user='larry',
-> master_password='larry',
-> master_port=3306,
-> master_log_file='mysql-bin.000001',
-> master_log_pos=107;
query ok, 0 rows affected (0.01 sec)第五步，开启slave。
mysql> show slave status /g;
*************************** 1. row ***************************
slave_io_state:
master_host: 192.168.1.11
master_user: larry
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 107
relay_log_file: serv08-relay-bin.000001
relay_log_pos: 4
relay_master_log_file: mysql-bin.000001
slave_io_running: no
slave_sql_running: no
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: 107
relay_log_space: 107
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: null
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 0
1 row in set (0.00 sec)error:
no query specifiedmysql> start slave;
query ok, 0 rows affected (0.01 sec)第六步，从服务器查看是否和主服务器通信成功。如果出现 slave_io_running和slave_sql_running都是yes，则证明配置成功
mysql> show slave status /g;
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.1.11
master_user: larry
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 107
relay_log_file: serv08-relay-bin.000002
relay_log_pos: 253
relay_master_log_file: mysql-bin.000001
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: 107
relay_log_space: 410
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:
replicate_ignore_server_ids:
master_server_id: 1
1 row in set (0.00 sec)error:
no query specified第七步，从服务器查看数据文件的更改
[root@serv08 data]# ll
total 28724
-rw-rw----. 1 mysql mysql 18874368 oct 5 19:45 ibdata1
-rw-rw----. 1 mysql mysql 5242880 oct 5 19:45 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 oct 5 18:16 ib_logfile1
-rw-rw----. 1 mysql mysql 78 oct 5 19:49 master.info
drwxr-xr-x. 2 mysql mysql 4096 oct 5 18:15 mysql
-rw-rw----. 1 mysql mysql 107 oct 5 19:45 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 oct 5 19:45 mysql-bin.index
drwx------. 2 mysql mysql 4096 oct 5 18:15 performance_schema
-rw-rw----. 1 mysql mysql 51 oct 5 19:49 relay-log.info
-rw-r-----. 1 mysql root 5589 oct 5 19:49 serv08.host.com.err
-rw-rw----. 1 mysql mysql 5 oct 5 19:45 serv08.host.com.pid
-rw-rw----. 1 mysql mysql 157 oct 5 19:49 serv08-relay-bin.000001
-rw-rw----. 1 mysql mysql 253 oct 5 19:49 serv08-relay-bin.000002
-rw-rw----. 1 mysql mysql 52 oct 5 19:49 serv08-relay-bin.index
drwxr-xr-x. 2 mysql mysql 4096 oct 5 18:12 test
[root@serv08 data]# cat relay-log.info
./serv08-relay-bin.000002
253
mysql-bin.000001
107
[root@serv08 data]# cat master.info
18
mysql-bin.000001
107
192.168.1.11
larry
larry
3306第八步，测试
--serv08查看数据库
mysql> show databases;
+--------------------+
| database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.02 sec)--serv01创建数据库
mysql> create database larrydb;
query ok, 1 row affected (0.00 sec)
--serv01查看数据库
mysql> show databases;
+--------------------+
| database |
+--------------------+
| information_schema |
| larrydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)--serv08查看数据库，发现已经同步
mysql> show databases;
+--------------------+
| database |
+--------------------+
| information_schema |
| larrydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)--serv01创建表 插入数据
mysql> use larrydb;
database changed
mysql> create table test(id int(11));
query ok, 0 rows affected (0.00 sec)mysql> insert into test values(1);
query ok, 1 row affected (0.00 sec)--serv08查看数据是否同步成功，发现数据已经同步
mysql> use larrydb;
database changed
mysql> show tables;
+-------------------+
| tables_in_larrydb |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)第九步，查看进程状态
--serv01查看进程状态
mysql> show processlist;
+----+-------+--------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+
| id | user | host | db | command | time | state | info |
+----+-------+--------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1 | root | localhost | larrydb | query | 0 | null | show processlist |
| 2 | larry | 192.168.1.18:41393 | null | binlog dump | 854 | master has sent all binlog to slave; waiting for binlog to be updated | null |
+----+-------+--------------------+---------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)--serv08查看进程状态
mysql> show processlist;
+----+-------------+-----------+---------+---------+------+-----------------------------------------------------------------------------+------------------+
| id | user | host | db | command | time | state | info |
+----+-------------+-----------+---------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | root | localhost | larrydb | query | 0 | null | show processlist |
| 2 | system user | | null | connect | 880 | waiting for master to send event | null |
| 3 | system user | | null | connect | 65 | slave has read all relay log; waiting for the slave i/o thread to update it | null |
+----+-------------+-----------+---------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)mysqlab双向复制
好了，mysql ab单向复制介绍完毕。接下来想想，会有这样的应用场景。比如master和slave之间都要进行数据同步，那么单向复制是无法完成的，因为一个是master，一个是slave，只能单向操作，这就像网络里的半双工一样。既然一方可以向另一方同步数据，那么两方都做成master 不就可以实现互相同步数据了。这就是接下来要介绍的mysql ab双向复制。同样我们来看看mysql ab双向复制的拓扑图。
图二 mysql ab双向复制
既然对拓扑图和原理有所了解，我们做一个实验，介绍如何使用mysql ab双向复制，注意该实验是在mysql单级复制的基础上做的。
实验环境介绍
主机 ip地址 主机名 备注
serv01： 192.168.1.11 serv01.host.com master
serv08： 192.168.1.18 serv08.host.com slave01
操作系统版本：rhel server 6.1
所需要的软件包：mysql-5.5.29-linux2.6-x86_64.tar.gz
第一步，serv08创建授权用户
mysql> grant replication client, replication slave on *.* to 'larry'@'192.168.1.%' identified by 'larry';
query ok, 0 rows affected (0.01 sec)第二步，serv08清空日志
mysql> show binary logs;
+------------------+-----------+
| log_name | file_size |
+------------------+-----------+
| mysql-bin.000001 | 286 |
+------------------+-----------+
1 row in set (0.00 sec)mysql> reset master;
query ok, 0 rows affected (0.00 sec)mysql> show binary logs;
+------------------+-----------+
| log_name | file_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)第三步，serv01使用change master to命令修改从服务器设置
mysql> show slave status;
empty set (0.00 sec)mysql> change master to
-> master_host='192.168.1.18',
-> master_user='larry',
-> master_password='larry',
-> master_port=3306,
-> master_log_file='mysql-bin.000001',
-> master_log_pos=107;
query ok, 0 rows affected (0.01 sec)第四步，serv01开启slave
mysql> start slave;
query ok, 0 rows affected (0.01 sec)mysql> show slave status /g;
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.1.18
master_user: larry
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 107
relay_log_file: serv01-relay-bin.000002
relay_log_pos: 253
relay_master_log_file: mysql-bin.000001
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: 107
relay_log_space: 410
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:
replicate_ignore_server_ids:
master_server_id: 2
1 row in set (0.00 sec)error:
no query specified第五步，测试
--serv01查看数据
mysql> use larrydb;
database changed
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)--serv08插入数据
mysql> use larrydb;
database changed
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql> insert into test values(2);
query ok, 1 row affected (0.00 sec)--serv01查看数据，数据更新
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)--serv01插入数据
mysql> insert into test values(3);
query ok, 1 row affected (0.01 sec)--serv01查询数据
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)--serv08查询数据，数据已更新
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)mysql多级主从复制
好了，mysql ab双向复制介绍完毕，我们又想了，不管是mysql ab单向复制，还是mysql 双向复制，都是双方的关系。mysql ab单向复制可以是一对一，也就是一个master对应一个slave，或者一对多，也就是一个master对应多个slave；mysql双向复制是一对一的关系。我们可不可以这样，实现多级关系，一个master，接下来slave，slave下面还有slave。这样做有什么好处呢？这样可以缓解数据库压力。这就是接下来要介绍的mysql多级主从复制。多级也就是a---->b---->c，a作为主服务器，b是从服务器，b跟a建立主从关系；而且b是主服务器，c作为从服务器，b跟c建立主从关系。这样：a是主服务器，b既是主服务器，又是从服务器，c是从服务器。同样，我们来看看mysql 多级主从复制的拓扑图：
图三 mysql 多级主从复制
该拓扑图实现 mysql 的 a 到b 的复制，再从 b 到 c 的复制。
既然对拓扑图和原理有所了解，我们做一个实验，介绍如何使用mysql ab双向复制：
实验环境介绍
主机 ip地址 主机名 备注
serv01： 192.168.1.11 serv01.host.com master
serv08： 192.168.1.18 serv08.host.com slave01
serv09： 192.168.1.19 serv09.host.com slave02
操作系统版本：rhel server 6.1
所需要的软件包：mysql-5.5.29-linux2.6-x86_64.tar.gz
第一步，断开双向关系。a只作为主服务器。
--停止slave
mysql> stop slave;
query ok, 0 rows affected (0.00 sec)
--查看slave状态发现仍然有相关信息，我们要彻底删除，只需要把数据文件中相关文件删除即可。
mysql> show slave status /g;
*************************** 1. row ***************************
slave_io_state:
master_host: 192.168.1.18
master_user: larry
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 587
relay_log_file: serv01-relay-bin.000006
relay_log_pos: 733
relay_master_log_file: mysql-bin.000002
slave_io_running: no
slave_sql_running: no
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: 587
relay_log_space: 1036
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: null
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 2
1 row in set (0.00 sec)error:
no query specified--进入data目录，删除以下文件：master.info relay-log.info serv01-relay-bin.*
[root@serv01 ~]# cd /usr/local/mysql/data
[root@serv01 data]# ll
total 28736
-rw-rw----. 1 mysql mysql 18874368 oct 5 22:38 ibdata1
-rw-rw----. 1 mysql mysql 5242880 oct 5 22:38 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 oct 5 18:16 ib_logfile1
drwx------. 2 mysql mysql 4096 oct 5 22:36 larrydb
-rw-rw----. 1 mysql mysql 79 oct 5 23:24 master.info
drwxr-xr-x. 2 mysql mysql 4096 oct 5 18:15 mysql
-rw-rw----. 1 mysql mysql 690 oct 5 22:34 mysql-bin.000001
-rw-rw----. 1 mysql mysql 970 oct 5 22:38 mysql-bin.000002
-rw-rw----. 1 mysql mysql 38 oct 5 22:34 mysql-bin.index
drwx------. 2 mysql mysql 4096 oct 5 18:15 performance_schema
-rw-rw----. 1 mysql mysql 53 oct 5 23:24 relay-log.info
-rw-r-----. 1 mysql root 5309 oct 5 23:24 serv01.host.com.err
-rw-rw----. 1 mysql mysql 5 oct 5 22:34 serv01.host.com.pid
-rw-rw----. 1 mysql mysql 303 oct 5 22:35 serv01-relay-bin.000005
-rw-rw----. 1 mysql mysql 733 oct 5 22:37 serv01-relay-bin.000006
-rw-rw----. 1 mysql mysql 52 oct 5 22:35 serv01-relay-bin.index
-rw-r-----. 1 mysql mysql 2209 oct 5 18:16 serv08.host.com.err
drwxr-xr-x. 2 mysql mysql 4096 oct 5 18:12 test
[root@serv01 data]# rm -rf master.info relay-log.info serv01-relay-bin.*第二步，serv01重启服务，再次查看slave信息，发现已经不存在
[root@serv01 data]# /etc/init.d/mysqld restart
shutting down mysql.... success!
starting mysql.. success![root@serv01 opt]# mysql
welcome to the mysql monitor. commands end with ; or /g.
your mysql connection id is 1
server version: 5.5.29-log source distribution
mysql> show slave status /g;
empty set (0.00 sec)error:
no query specified第三步，serv08查看slave状态
mysql> show slave status /g;
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.1.11
master_user: larry
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000003
read_master_log_pos: 107
relay_log_file: serv08-relay-bin.000007
relay_log_pos: 253
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: 107
relay_log_space: 556
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:
replicate_ignore_server_ids:
master_server_id: 1
1 row in set (0.00 sec)error:
no query specified
--如果查看slave状态出错，我们重启服务
[root@serv08 ~]# /etc/init.d/mysqld restart
shutting down mysql.. success!
starting mysql.. success! 第四步，serv09搭建相同版本的mysql，修改server-id，启动服务
[root@serv09 ~]# vim /etc/my.cnf
[root@serv09 ~]# cat /etc/my.cnf | grep server-id
server-id = 3
[root@serv09 ~]# /etc/init.d/mysqld start
starting mysql.. success! 第五步，serv01插入数据
mysql> use larrydb;
database changed
mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
+----+---------+
5 rows in set (0.00 sec)mysql> insert into t2(name) values('larry07');
query ok, 1 row affected (0.01 sec)第六步，serv08查看serv01插入的数据是否记录到日志文件。可以发现，和serv01建立关系的延时日志文件中有相关记录，而主日志文件mysql-bin.000003中没有相关记录
[root@serv08 data]# mysqlbinlog serv08-relay-bin.000009 | grep insert -i --color
/*!40019 set @@session.max_insert_delayed_threads=0*/;
set insert_id=9/*!*/;
insert into t2(name) values('larry07')
[root@serv08 data]# mysqlbinlog mysql-bin.000003 | grep larry07第七步，我们要把serv08的数据同步到serv09，因为serv08中的mysql-bin.000003文件没有相关记录，所以不能通过日志文件同步，我们只有先serv08导出数据，然后serv09导入数据，再把log_slave_updates打开
--导出数据
[root@serv08 data]# mysqldump --help --verbose | grep database[root@serv08 data]# mysqldump --databases larrydb > larrydb.sql
--拷贝数据文件
[root@serv08 data]# scp larrydb.sql 192.168.1.19:/opt
root@192.168.1.19's password:
larrydb.sql 100% 2612 2.6kb/s 00:00 --serv09导入数据
mysql> source /opt/larrydb.sql;
query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 1 row affected (0.01 sec)database changed
query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 6 rows affected (0.01 sec)
records: 6 duplicates: 0 warnings: 0query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.02 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 3 rows affected (0.00 sec)
records: 3 duplicates: 0 warnings: 0query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)query ok, 0 rows affected (0.00 sec)mysql> use larrydb;
database changed
mysql> show tables;
+-------------------+
| tables_in_larrydb |
+-------------------+
| t2 |
| test |
+-------------------+
2 rows in set (0.01 sec)mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
| 9 | larry07 |
+----+---------+
6 rows in set (0.01 sec)--serv08修改配置文件，打开log_slave_updates，重启mysql服务
mysql> show variables like '%update%';
+-----------------------------------------+-------+
| variable_name | value |
+-----------------------------------------+-------+
| binlog_direct_non_transactional_updates | off |
| log_slave_updates | off |
| low_priority_updates | off |
| sql_low_priority_updates | off |
| sql_safe_updates | off |
+-----------------------------------------+-------+
5 rows in set (0.00 sec)[root@serv08 data]# vim /etc/my.cnf
[root@serv08 data]# cat /etc/my.cnf | grep log_slave_updates
log_slave_updates=1
[root@serv08 data]# /etc/init.d/mysqld restart
shutting down mysql.... success!
starting mysql.. success! --serv08
mysql> show variables like %update%;
+-----------------------------------------+-------+
| variable_name | value |
+-----------------------------------------+-------+
| binlog_direct_non_transactional_updates | off |
| log_slave_updates | on |
| low_priority_updates | off |
| sql_low_priority_updates | off |
| sql_safe_updates | off |
+-----------------------------------------+-------+
5 rows in set (0.00 sec)第八步，serv01插入测试数据，我们看到打开这个参数后mysql-bin.000004和serv08-relay-bin.000011都有相关的插入数据的记录
mysql> insert into t2(name) values('larry08');
query ok, 1 row affected (0.00 sec)mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
| 9 | larry07 |
| 11 | larry08 |
+----+---------+
7 rows in set (0.00 sec)[root@serv08 data]# mysqlbinlog mysql-bin.000004 | grep larry
use `larrydb`/*!*/;
insert into t2(name) values('larry08')
[root@serv08 data]# mysqlbinlog serv08-relay-bin.000011 | grep larry
use `larrydb`/*!*/;
insert into t2(name) values('larry08')第九步，serv08创建授权用户
mysql> select user,password,host from mysql.user where user='larry';
error 2006 (hy000): mysql server has gone away
no connection. trying to reconnect...
connection id: 3
current database: larrydb+-------+-------------------------------------------+-------------+
| user | password | host |
+-------+-------------------------------------------+-------------+
| larry | *0cdc8d34246e22649d647db04e7cccacab4368b6 | 192.168.1.% |
+-------+-------------------------------------------+-------------+
1 row in set (0.00 sec)mysql> show binary logs;
+------------------+-----------+
| log_name | file_size |
+------------------+-----------+
| mysql-bin.000001 | 1046 |
| mysql-bin.000002 | 606 |
| mysql-bin.000003 | 126 |
| mysql-bin.000004 | 335 |
+------------------+-----------+
4 rows in set (0.00 sec)第十步，serv09通过change master to修改slave配置，然后启动slave，查看slave状态，查看数据，发现已经从serv08更新过来
mysql> change master to
-> master_host='192.168.1.18',
-> master_user='larry',
-> master_password='larry',
-> master_port=3306,
-> master_log_file='mysql-bin.000003',
-> master_log_pos=126;
query ok, 0 rows affected (0.03 sec)mysql> show slave status /g;
*************************** 1. row ***************************
slave_io_state:
master_host: 192.168.1.18
master_user: larry
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000003
read_master_log_pos: 126
relay_log_file: serv09-relay-bin.000001
relay_log_pos: 4
relay_master_log_file: mysql-bin.000003
slave_io_running: no
slave_sql_running: no
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: 126
relay_log_space: 107
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: null
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 0
1 row in set (0.00 sec)error:
no query specifiedmysql> start slave;
query ok, 0 rows affected (0.01 sec)mysql> show slave status /g;
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.1.18
master_user: larry
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000004
read_master_log_pos: 335
relay_log_file: serv09-relay-bin.000003
relay_log_pos: 481
relay_master_log_file: mysql-bin.000004
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: 335
relay_log_space: 784
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:
replicate_ignore_server_ids:
master_server_id: 2
1 row in set (0.00 sec)error:
no query specifiedmysql> select * from larrydb.t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
| 9 | larry07 |
| 11 | larry08 |
+----+---------+
7 rows in set (0.00 sec)第十一步，serv01插入数据，可以看到serv08 serv09都已经同步过去了
--serv01
mysql> insert into t2(name) values('larry09');
query ok, 1 row affected (0.00 sec)
mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
| 9 | larry07 |
| 11 | larry08 |
| 13 | larry09 |
+----+---------+
8 rows in set (0.00 sec)--serv08
mysql> select * from larrydb.t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
| 9 | larry07 |
| 11 | larry08 |
| 13 | larry09 |
+----+---------+
8 rows in set (0.00 sec)--serv09
mysql> select * from larrydb.t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
| 9 | larry07 |
| 11 | larry08 |
| 13 | larry09 |
+----+---------+
8 rows in set (0.00 sec)解决ab双向复制主键冲突
在进行mysqlab双向复制时，如果一张表的主键是自增的，会出现问题。主服务器和从服务器在插入数据时会发生主键冲突，比如a服务器插入一条数据，id为5，b服务器同步过去，但是b服务器插入数据id也可能是5，就这会引起主键冲突，导致数据不能插入。因此，我们需要解决这个问题。解决办法是主键间隔设置，通过设置主键步长，比如a（13 5 7），b（2 4 6 8），有几台机器步长就为几。接下来的实验是在mysqlab双向复制的基础上做的。
第一步，serv08创建测试表，插入数据，查看数据
mysql> create table t2(id int auto_increment primary key,name varchar(30));
query ok, 0 rows affected (0.00 sec)mysql> desc t2;
+-------+-------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | no | pri | null | auto_increment |
| name | varchar(30) | yes | | null | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)mysql> insert into t2(name) values('larry01');
query ok, 1 row affected (0.00 sec)mysql> insert into t2(name) values('larry02');
query ok, 1 row affected (0.01 sec)mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
+----+---------+
2 rows in set (0.00 sec)第二步，serv01查看数据
mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
+----+---------+
2 rows in set (0.00 sec)mysql> drop table t2;
query ok, 0 rows affected (0.01 sec)第二步，serv01和serv08修改配置文件，并重启服务
[root@serv01 opt]# vim /etc/my.cnf
[root@serv01 opt]# cat /etc/my.cnf | grep auto_incre
auto_increment_increment=2
auto_increment_offset=1
[root@serv01 opt]# /etc/init.d/mysqld restart
shutting down mysql.. success!
starting mysql.. success! [root@serv08 data]# vim /etc/my.cnf
[root@serv08 data]# cat /etc/my.cnf | grep auto_incre
auto_increment_increment=2
auto_increment_offset=2[root@serv08 data]# /etc/init.d/mysqld restart
shutting down mysql. success!
starting mysql.. success! 第三步，serv01再次模拟数据
mysql> use larrydb;
database changed
mysql> show tables;
+-------------------+
| tables_in_larrydb |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)
mysql> create table t2(id int(11) primary key auto_increment, name varchar(30));
query ok, 0 rows affected (0.02 sec)mysql> insert into t2(name) values('larry01');
query ok, 1 row affected (0.01 sec)mysql> insert into t2(name) values('larry02');
query ok, 1 row affected (0.00 sec)mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
+----+---------+
2 rows in set (0.00 sec)--serv08
mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
+----+---------+
2 rows in set (0.00 sec)mysql> insert into t2(name) values('larry03');
query ok, 1 row affected (0.00 sec)mysql> insert into t2(name) values('larry04');
query ok, 1 row affected (0.00 sec)mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
+----+---------+
4 rows in set (0.00 sec)--serv01
mysql> insert into t2(name) values('larry05');
query ok, 1 row affected (0.00 sec)mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
+----+---------+
5 rows in set (0.00 sec)--serv08
mysql> select * from t2;
+----+---------+
| id | name |
+----+---------+
| 1 | larry01 |
| 3 | larry02 |
| 4 | larry03 |
| 6 | larry04 |
| 7 | larry05 |
+----+---------+
5 rows in set (0.00 sec) bitscn.com