mysql 多主单从_mysql 多主-单从-因为是多库不方便没做完-后面做了主主实例

注意 多主单从 只能各主是不同数据库的案例，因为一个数据库只能有一个同步源

主库300：IP=192.168.10.212; PORT=4300; server-id=300; database=test3; table=user

主库400：IP=192.168.10.212; PORT=4400; server-id=400; database=test4; table=user

从库10345：IP=192.168.10.212; PORT=4345; server-id=10345; database=test3,test4,table=user

docker exec -it master300 /bin/bash

/etc/mysql/mysql.conf.d/mysqld.cnf

操作1

docker run --name master300 -p 4300:3306 \

-m 1g \

-v /data/mysql/data300:/var/lib/mysql -v /data/mysql-300/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

docker run --name master400 -p 4400:3306 \

-m 1g \

-v /data/mysql/data400:/var/lib/mysql -v /data/mysql-400/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

docker run --name master10345 -p 4345:3306 \

-m 1g \

-v /data/mysql/data10345:/var/lib/mysql -v /data/mysql-10345/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

docker exec -it master300 /bin/bash

/etc/mysql/mysql.conf.d/mysqld.cnf

https://baijiahao.baidu.com/s?id=1617841562444776324&wfr=spider&for=pc&isFailFlag=1

https://www.cnblogs.com/cocoliu/p/9020845.html

vi /etc/my.cnf

log-bin=mysql-bin-master #启用二进制日志

server-id=1 #本机数据库ID 标示

binlog-do-db=ha1 #可以被从服务器复制的库。二进制需要同步的数据库名

binlog-ignore-db=mysql #不可以被从服务器复制的库

mysql -uroot -p'MYW>s!jBe7KC'

vi /etc/my.cnf #普通msyql原始配置

[mysqld]

datadir=/var/lib/mysql

symbolic-links=0log-error=/var/log/mysql/error.log

socket=/var/run/mysqld/mysqld.sock

pid-file=/var/run/mysqld/mysqld.pid

docker内的配置

cat/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket= /var/run/mysqld/mysqld.sock

datadir= /var/lib/mysql

#log-error = /var/log/mysql/error.log

symbolic-links=0

主1数据库修改的配置

vi /data/mysql-300/conf/my.cnf #主1mysql配置

[mysqld]

log-bin=mysql-bin-master #启用二进制日志

server-id=1#本机数据库ID 标示

binlog-do-db=ha1 #可以被从服务器复制的库。二进制需要同步的数据库名

binlog-ignore-db=mysql #不可以被从服务器复制的库

auto-increment-increment=2#每次加2

auto-increment-offset=1#从1开始

log-slave-updates

docker restart master300

mysql -uroot -p123456 -h172.17.0.1 -P4300

主2修改的配置

vi /data/mysql-400/conf/my.cnf #主2mysql配置

[mysqld]

log-bin=mysql-bin-master #启用二进制日志

server-id=2#本机数据库ID 标示

binlog-do-db=ha1 #可以被从服务器复制的库。二进制需要同步的数据库名

binlog-ignore-db=mysql #不可以被从服务器复制的库

auto-increment-increment=2#每次加2

auto-increment-offset=2#从2开始

log-slave-updates

docker restart master400

mysql -uroot -p123456 -h172.17.0.1 -P4400

vi /data/mysql-10345/my.cnf #从1服务器配置

[mysqld]

log-bin=mysql-slave1 #启用二进制日志

server-id=3#本机数据库ID 标示

binlog-do-db=ha1 #可以被从服务器复制的库。二进制需要同步的数据库名

binlog-ignore-db=mysql #不可以被从服务器复制的库

log_slave_updates=1#让从库可以同步这台数据库的信息

read_only=1#配置只读

docker restart master10345 # 命名有问题其实是从服务器

mysql -uroot -p123456 -h172.17.0.1 -P4300

grant replication slave on*.* to 'slave'@'%' identified by '123456';

flush privileges;

show master status;

mysql-uroot -p123456 -h172.17.0.1 -P4400

grant replication slave on*.* to 'slave'@'%' identified by '123456';

flush privileges;

show master status;

从库操作

change master to master_host='192.168.1.62', master_port=4300, master_user='slave', master_password='123456', \

master_log_file='mysql-bin.000004', master_log_pos=120, for channel '300';

change master to master_host='192.168.1.62', master_port=4400, master_user='slave', master_password='123456', \

master_log_file='mysql-bin.000004', master_log_pos=120, for channel '400';

主主同步实例

mysql -uroot -p123456 -h172.17.0.1 -P4300

create database ha1;

use ha1;

create table test(idint,name varchar(20));

insert into test values(1,'man');#插入数据

mysqldump -uroot -p123456 -h172.17.0.1 -P4300 -B ha1 > ha1.sql

mysql -uroot -p123456 -h172.17.0.1 -P4400 < ha1.sql

2同步1

show master status\G #主1操作

File: mysql-bin-master.000001

Position: 1202

mysql -uroot -p123456 -h172.17.0.1 -P4400

change master to master_host='192.168.1.62', master_port=4300, master_user='slave', master_password='123456', \

master_log_file='mysql-bin-master.000001', master_log_pos=1202 ; #主2

start slave;

show slave status\G

1同步2

show master status\G #主1操作

File: mysql-bin-master.000001

Position: 2072

mysql -uroot -p123456 -h172.17.0.1 -P4300

change master to master_host='192.168.1.62', master_port=4400, master_user='slave', master_password='123456', \

master_log_file='mysql-bin-master.000001', master_log_pos=2072 ; #主2

start slave;

show slave status\G

insert into test values(3,'man3');#插入数据测试两边都可以写入

自增数列插入测试

use ha1;

CREATE TABLE heihei( id INT NOT NULL AUTO_INCREMENT, city varchar(50) NOT NULL, PRIMARY KEY (id))AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into heihei values(1,"shanghai");select * fromheihei;

insert into heihei(city) values('zhangsan');

主2插入数据测试

insert into heihei(city) values('zhangsan4');