mysql 双写入_Mysql双主热备+读写分离

#双主热备=互相配slave

vi /etc/my.cnf

[mysqld]

server-id = 1

log-bin = mysql-bin

binlog-ignore-db = mysql,information_schema

binlog_format = mixed

auto-increment-increment = 2

auto-increment-offset = 1

or

[mysqld]

log-bin=mysql-bin

server-id=1

#为所有从服务器授权所有数据库

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

#dd授权dd0的账户

mysql -uroot -p

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

flush privileges;

show master status;

#dd0授权dd的账户

mysql -uroot -p

grant replication slave on *.* to 'dd'@'1

92.168.55.138' identified by '123456';

flush privileges;

show master status;

#dd0设dd为主服务器

change master to master_host='192.168.55.138', master_user='dd', master_password='123456', master_log_file='mysql-bin.000001', ma

ster_log_pos=328;

start slave

show slave status \G;

#dd设dd0为主服务器

change master to master_host='192.168.55.139', master_user='dd', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=329;

start slave

show slave status \G;

create table dd(id int primary key auto_increment,name varchar(20));

--------------------------------------------------------------------------------

#读写分离

#dd授权dd2 dd3的账户

#授权

mysql -uroot -p

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

flush privileges;

show master status;

#设主

mysql -uroot -p

change master to master_host='192.168.55.138', master_user='dd', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=658;

start slave;

show slave status \G;

#主建表从查看测试

create database dd;

use dd;

create table xq(id int(10),name varchar(10),address varchar(20));

insert into xq values('1','xq01','this_is_master');

----------------------------------------

错误： Slave_IO_Running: Yes

Slave_SQL_Running: Connecting

解决： show variables like 'datadir';

select uuid();

restart mariadb/mysqld

----------------------------------------

#dd0安装amoeba

#dd dd2 dd3

grant all privileges on *.* to 'root'@'192.168.55.%' identified by 'root';

flush privileges;

--------------------------------------------------------------------------------

#dd0

tar /usr/local/amoeba

chmod -R 755 /usr/local/amoeba/

cd /usr/local/amoeba

vi conf/amoeba.xml

#amoeba账户密码

user root

password root

最后三个pool

master

master

slaves

--------------------------------------------------------------------------------

vi conf/dbServers.xml (用户密码的地方去掉注释)

#之前授权的账户密码

root

root

dd

dd2

dd3

dbServer name="slaves" virtual="true"

slave0,slave1

vi bin/amoeba

DEFAULT_OPTS="-server -Xms1024m -Xmx1024m -Xss256k"

/usr/local/amoeba/bin/amoeba start

--------------------------------------------------------------------------------

#dd1连接dd0测试

mysql -u root -proot -h dd0 -P8066

错误提示：

Could not create a validated object, cause: ValidateObject failed

依据 conf/dbServers.xml

test

主建test数据库

mysql -uroot -proot -h 192.168.55.130 -P8066

use test;

create table dd(id int primary key auto_increment,name varchar(20));

create table xq(id int(10),name varchar(10),address varchar(20));

insert into xq values('1','xq01','this_is_master');

select * from xq;

#查看dd dd2 dd3 数据库数据

--------------------------------------------------------------------------------

以优化的方式配置主从

#主

[mysqld]

vi /etc/my.cnf

log-bin=master-bin

log-slave-updates

server-id=1

innodb_flush_log_at_trx_commit=2

binlog-format=ROW

binlog_cache_size = 4M

max_binlog_size = 1G

max_binlog_cache_size = 2G

skip-name-resolve

#for 5.6

master_info_repository=table

relay_log_info_repository=TABLE

gtid-mode=ON

enforce-gtid-consistency=true

注解：

binlog-format=ROW ##二进制文件的格式

gtid-mode=ON ##开启 GTID 模式

enforce-gtid-consistency=true ##开启 GTID 的一些安全限制

binlog_cache_size = 4M ##单个事务的二进制日志占用内存的最大值

max_binlog_size = 1G ##二进制文件最大的大小

max_binlog_cache_size = 2G ##二进制文件占用内存的最大值

从

[mysqld]

relay-log=relay1-log-bin

relay-log-index=slave-relay1-bin.index

server-id=2

innodb_flush_log_at_trx_commit=2

slave_parallel_workers=16

binlog-format=ROW

binlog-row-image = minimal

log-bin=slave1-bin

log-bin-index=slave1-log-bin.index

binlog_cache_size = 4M

max_binlog_size = 1G

max_binlog_cache_size = 2G

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

log-slave-updates

relay_log_purge = 1

relay_log_recovery = 1

skip-name-resolve

#for 5.6

master_info_repository=table

relay_log_info_repository=TABLE

gtid-mode=ON

enforce-gtid-consistency=true

slave-parallel-type=LOGICAL_CLOCK

注解：

binlog-format=ROW ##二进制文件的格式

binlog-row-image = minimal ##使 mysql 复制中的行体积更小

log-bin=slave1-bin ##二进制日志文件的名称

log-bin-index=slave1-log-bin.index ##二进制日志文件的索引文件

gtid-mode=ON ##开启 GTID

enforce-gtid-consistency=true ##开启 GTID 的一些安全限制

binlog_cache_size = 4M ##单个事务的二进制日志占用内存的最大值

max_binlog_size = 1G ##二进制日志文件的最大大小

max_binlog_cache_size = 2G ##二进制日志文件占用内存的最大值

slave-sql-verify-checksum=1 ##判断从库 SQL Threa 线程从 relay log 中读出

的事件是否正常,默认设置为 1

binlog-rows-query-log_events=1 ##优化主从性能

log-slave-updates ##开启 slave 角色的日志更新

relay_log_purge = 1 ##不需要中继日志时自动清空

relay_log_recovery = 1 ##当slave从库宕机后，假如relay-log损坏了，

导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log，并且重新从 master 上

获取日志，这样就保证了 relay-log 的完整性。

#dd授权dd0 dd1的账户

mysql -uroot -p

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

flush privileges;

show master status;

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

flush privileges;

show master status;

#dd0 dd1设dd为主服务器

change master to master_host='192.168.55.138', master_user='dd', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=328;

start slave;

show slave status \G;

create table xq(id int(10),name varchar(10),address varchar(20));

insert into xq values(1,'xq','this is xq');

报错：Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist Can't find rec

use mysql;

create table `gtid_slave_pos` (

`domain_id` int(10) unsigned NOT NULL,

`sub_id` bigint(20) unsigned NOT NULL,

`server_id` int(10) unsigned NOT NULL,

`seq_no` bigint(20) unsigned NOT NULL,

PRIMARY KEY (`domain_id`,`sub_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

stop slave;

start slave;

show slave status \G;

报错：Got fatal error 1236

flush logs;

change master to master_log_file='mysql-bin.000010', master_log_pos=387;

show slave status \G;