Mysql数据库主主从设置

注意：在同一台服务器上部署主从或主主之类的时候，数据库data下有一个auto.cnf里的uuid不能重复。

原则：做同步之前要保证两个数据库数据一致.

锁表操作：

FLUSH TABLES WITH READ LOCK;

注：没有锁定主服务器，这里记录的主服务器二进制日志position值可能会大于做mysqldump时的值，这将导致从服务器丢失在此期间的更新。如果可以保证在此期间主服务器不会出现创建新表的更新，那么丢失的影响不大；否则，将导致从服务器复制线程失败，这时必须在做mysqldump时锁定主服务器。
取消主数据库锁定 mysql>UNLOCK TABLES;

在主机1的mysqld下增加

server-id=1
log-bin=mysql-bin
binlog_format = mixed
#不需要记录进制日志的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
binlog-ignore-db=mysql,information_schema,performance_schema,sys
#不需要同步的数据库.如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项
replicate-ignore-db=mysql,information_schema,performance_schema,sys 
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
#将复制事件写入binlog,一台服务器既作主库又作从库此选项必需要开启
log-slave-updates
#控制数据库的binlog刷到磁盘上去,sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
sync_binlog=1
#避免两台服务器同时做更新时自增长字段的值之间发生冲突
auto_increment_offset=1
auto_increment_increment=2
slave-skip-errors=all #过滤掉一些没啥大问题的错误

小知识：slave_skip_errors选项有四个可用值，分别为： off，all，ErorCode，ddl_exist_errors。 默认情况下该参数值是off，我们可以列出具体的error code，也可以选择all，mysql5.6及MySQL Cluster NDB 7.3以及后续版本增加了参数ddl_exist_errors，该参数包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）
    一些error code代表的错误如下：
    1007：数据库已存在，创建数据库失败
    1008：数据库不存在，删除数据库失败
    1050：数据表已存在，创建数据表失败
    1051：数据表不存在，删除数据表失败
    1054：字段不存在，或程序文件跟数据库有冲突
    1060：字段重复，导致无法插入
    1061：重复键名
    1068：定义了多个主键
    1094：位置线程ID
    1146：数据表缺失，请恢复数据库
    1053：复制过程中主服务器宕机
    1062：主键冲突 Duplicate entry '%s' for key %d

在主机2的mysqld下增加：

server-id=2
log-bin=mysql-bin
binlog-ignore-db=mysql,information_schema,performance_schema,sys#不需要记录进制日志的数据库.如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
replicate-ignore-db=mysql,information_schema,performance_schema,sys #不需要同步的数据库.如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
slave-skip-errors=all #过滤掉一些没啥大问题的错误

在主机1上登录mysql
cd /home/mysqlinstall/public/mysql/bin
./mysql -S ../mysql.sock -uroot -p'密码'
grant replication slave on *.* to repl@'主机2' identified by '密码';
例：grant replication slave on *.* to repl@'ip' identified by '密码';
flush privileges;
show master status;（或show master status \G）#记录下二进制日志文件名和位置

在主机2设置操作
cd /home/mysqlinstall/public/mysql/bin
./mysql -S ../mysql.sock -uroot -p'密码'
stop slave;
change master to master_host='主机1',master_user='repl',master_password='密码',master_log_file='mysql-bin.000001',master_log_pos=608;
例：change master to master_host='ip',master_port=3306,master_user='repl',master_password='密码',master_log_file='mysql-bin.000001',master_log_pos=609;
(master_log_file和master_log_pos填上刚才记录下主1的二进制日志文件名和位置)
start slave;
show slave status\G（出现Slave_IO_Running: Yes Slave_SQL_Running: Yes 标识主1设置成功，即可测试主主复制）

在主机2上操作
grant replication slave on *.* to repl@'主机1' identified by '密码';
例：grant replication slave on *.* to repl@'ip' identified by '密码';
flush privileges;
show master status;（或show master status \G）#记录下二进制日志文件名和位置

在主机1设置操作
stop slave;
change master to master_host='主机2',master_user='repl',master_password='密码',master_log_file='mysql-bin.000001',master_log_pos=154;
例：change master to master_host=ip',master_port=3306,master_user='repl',master_password='密码',master_log_file='mysql-bin.000001',master_log_pos=609;
(master_log_file和master_log_pos填上刚才记录下主1的二进制日志文件名和位置)
start slave;
show slave status\G（出现Slave_IO_Running: Yes Slave_SQL_Running: Yes 标识主1设置成功，即可测试主主复制）

在主机3上登录

server-id=3
log-bin=mysql-bin
master_info_repository = table
relay_log_info_repository = table
#限制普通用户只读
read-only=1
#限制超级管理员用户只读
super_read_only=1

mysql -uroot -p

CHANGE MASTER TO
MASTER_HOST='主1IP',
MASTER_PORT=3306, MASTER_USER='用户',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=886 for channel '主1标识';
(master_log_file和master_log_pos填上刚才记录下主1的二进制日志文件名和位置)

CHANGE MASTER TO
MASTER_HOST='主2IP',
MASTER_PORT=3306, MASTER_USER='用户',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154 for channel '主2标识';
(master_log_file和master_log_pos填上刚才记录下主2的二进制日志文件名和位置)

例：
change master to master_host='ip',master_port=3306,master_user='repl',master_password='密码',master_log_file='mysql-bin.000004',master_log_pos=154 for channel '3306标识';
change master to master_host='ip',master_port=3307,master_user='repl',master_password='密码',master_log_file='mysql-bin.000003',master_log_pos=154 for channel '3307标识';

​flush privileges;
start slave;
show slave status\G（出现Slave_IO_Running: Yes Slave_SQL_Running: Yes 标识主1 和标识主2设置成功）

1.创建用户CREATE USER 'hwb'@'%' IDENTIFIED BY '密码';

2.用户授权所以数据库 grant SELECT on *.* to 'hwb'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `[databasename]`.* TO 'dba'@'%'
3.刷新 flush privileges;

测试表：

create database testdb;
create table testdb.data01( 
id int not null primary key auto_increment, 
name varchar(60), 
age int); 

insert into testdb.data01 (name,age) values
('tom',18),
('jack',17),
('rock',16),
('james',15),
('cris',20);

通过 select @@hostname 查看可以看到此时连接的是主库 B。