mysql 5.6主从配置

mysql主从
先配置主
1、
mysql机器  create user repl;
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.244’ IDENTIFIED BY ‘666666’;
FLUSH   PRIVILEGES;

2、
my.conf

server-id=1
log_bin = mysql-bin
log-bin-index=mysql-bin.index
sync_binlog=1
binlog_format=mixed
binlog-do-db = mall //同步数据库
binlog-do-db = cms
binlog-do-db = ucenter
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
binlog_checksum=NONE

3、
service mysqld restart
进入mysql
SHOW MASTER STATUS;
返回
+——————+———-+——————+———————————————+——————-+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+——————+———-+——————+———————————————+——————-+
| mysql-bin.000003 |      120 | mall,cms,ucenter | mysql,performance_schema,information_schema |                   |
+——————+———-+——————+———————————————+——————-+
就ok了

再配置从
1、
my.conf

log-bin=mysql-bin
server-id=2
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1

service mysqld restart

2、
手动倒入库,先在从库上建立数据库
然后在主库上
flush tables with read lock;
mysqldump出来
然后从库source

3、
mysql> change master to master_host=’192.168.1.243′,master_user=’repl’,master_password=’666666′, master_log_file=’mysql-bin.000003′,master_log_pos=120

start slave;
show slave status\G;

返回
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
就对了

主库打开
UNLOCK TABLES;

SHOW SLAVE STATUS;

另外,从库配置只读。
show global variables like “%read_only%”;
set global read_only=1;

只保留1天日志
set global expire_logs_days=1;
查询
show variables like ‘%max_binlog_size%’

set global max_binlog_size=500;
后面会自动叠加文件,类似这样。
| mysql-bin.000001 |     18214 |
| mysql-bin.000002 |      4147 |
| mysql-bin.000003 |      2124 |
SHOW MASTER LOGS;

发表评论

电子邮件地址不会被公开。 必填项已用*标注