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;