mysql主主复制某张表_MySQL主主复制实战

MySQL主主复制实战：

基础环境配置很简单，我这就不浪费时间了，直接开始。

#####################################################################################

m1-m2配置文件更改

#####################################################################################

一、添加my.cnf自增的参数和打开binlog

(1)Master1：

#master-master

auto_increment_increment = 2  #自增ID的间隔，如1 3 5 间隔为2

auto_increment_offset = 1     #自增ID的初始位置

log-bin=mysqlbin_3307_tangbo

(2)Master1：

#master-master

auto_increment_increment = 2   #自增ID的间隔，如2 4 6 间隔为2

auto_increment_offset = 2      #自增ID的初始位置

log-bin=mysqlbin_3308_tangbo

二、互为主从参数---两边都要加入配置

replicate-ignore-db = mysql #不同步mysql库

log-slave-updates  #开启从库binlog日志

binlog-ignore-db = mysql   #不记录binlog日志

binlog-ignore-db = information_schema #不记录binlog日志

binlog-ignore-db = performance_schema #不记录binlog日志

skip-slave-start #启动忽略从库启动

#####################################################################################

master1配置文件更改

#####################################################################################

三、在主库master1上的操作

(1)开启binlog并设置server-id的值

server_id = 100

log-bin=mysqlbin_3307_tangbo

(2)创建用于同步的账号tangbo(master)

mysql> GRANT replication slave ON *.* TO 'tangbo'@'192.168.11.%' IDENTIFIED BY '111111';

Query OK, 0 rows affected (0.01 sec)

mysql>

(3)锁表只读(master)

mysql> flush table with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql>

(4)查看master状态当前日志文件名和二进制日志偏移量(要记录，后面会用)

mysql> show master status;

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

| File                        | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |

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

| mysqlbin_3307_tangbo.000003 |      336 |              | mysql,information_schema,performance_schema |                   |

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

1 row in set (0.00 sec)

mysql>

(5)单开新窗口，备份数据库(数据量大，并且允许停机可以直接tar cp)

mysqldump -uroot -p111111 -S /data/3308/mysql.sock -A -B  | gzip > /opt/all.sql.gz

(6)主库解锁，恢复可写

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

mysql>

(7)把备份的数据考入到另一台主库

scp就行

#####################################################################################

master2配置文件更改

#####################################################################################

三、在主库master2上的操作

(1)设置server-id 值，且重启数据库

server_id = 101

(2)把备份的MySQL数据导入从库(Slave)

gzip -d all.sql.gz

mysql -uroot -p111111 -S /data/3308/mysql.sock  

(3)登陆mysql(master2)建立同步master1

change master to

master_host='192.168.11.36',

master_port=3307,

master_user='tangbo',

master_password='111111',

master_log_file='mysqlbin_3307_tangbo.000003',

master_log_pos=336;

(4)启动同步命令，并查看：slave

mysql> start slave;

Query OK, 0 rows affected (0.11 sec)

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

#####################################################################################

master2配置文件更改-跟前面步骤一样。连接时改改配置即可

#####################################################################################

change master to

master_host='192.168.11.36',

master_port=3308,

master_user='tangbo',

master_password='111111',

master_log_file='mysqlbin_3308_tangbo.000001',

master_log_pos=120;

创建表测试：

CREATE TABLE `caiwu` (

`id` int(30) NOT NULL AUTO_INCREMENT,

`name` varchar(60) NOT NULL,

`ages` varchar(60) DEFAULT NULL,

`job` varchar(30) DEFAULT NULL,

`pay` float DEFAULT NULL,

`time` date DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

测试脚本

[root@mysql-double ~]# cat mysqldata1.sh

#!/bin/bash

##########################

###########################

#MysqlLogin="mysql -uroot -p111111" #→定义登陆mysql的命令，方便下文使用

MysqlLogin="mysql -uroot -p111111 -S /data/3308/mysql.sock" #此行适合单机多实例数据库的方式

i=1

while true #→true表示永远为真

do

${MysqlLogin} -e "insert into tangbo.caiwu(name,ages,job,pay,time) values('tangbo"$i"','28','wangluo',5000,20150120);"

#${MysqlLogin} -e "insert into oldboy.student values ("$i",'oldboy"$i"','m','21','computer"$i"');"

#如果是多张表可以同时插入多张表,我这里给出的例子，是插入不同的记录，可以用于做各种小测试，比较适合各类初级不会mysql存储过程的运维人员。

((i++))

sleep 20;

done

[root@mysql-double ~]#

四、可以看到主主复制成功。