mysql主从复制大体分3个步骤:
在主库上把更新的数据写入到二进制日志binglog文件里面;
备库把主库的二进制日志复制到自己的relay_log中;
备库读取relay_log并将里面的数据重放到数据库。
上述步骤在主从之间涉及到3个线程,
a:主库上面的数据写入binglog线程,
b:从库上面启动的I/O数据同步线程,该线程在主库上启动一个特殊的二进制转储线程,去读取binglog文件,并把数据发送给I/O线程保存到relay_log,
c:从库的sql线程读取relay_log并写入数据库
主数据库master配置:
1、修改my.cnf配置文件:
[mysqld]
# 主数据库端ID号
server-id=10
#开启bin-log,并指定文件目录和文件名前缀。
log-bin=/usr/local/mysql/binlog/bin-log
#每个bin-log最大大小,当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
max_binlog_size=500M
#为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
#这个参数是用来设置在和主服务器连接丢失的时候,重试的时间间隔,默认是60秒(win下设置此参数会报错)
#master-connect-retry=60
#设置bin-log日志文件保存的天数,此参数mysql5.0以下版本不支持。
expire_logs_days=10
#需要同步的数据库名字,如果是多个,就以此格式在写一行即可。
binlog-do-db=test
#不需要同步的数据库名字,如果是多个,就以此格式在写一行即可。
binlog-ignore-db=mysql
#设置bin-log日志文件格式为:mixed(后面两种的结合),statement(存SQL语句),row(保存影响记录数据 ),默认格式是statement,建议用mixed
binlog_format=mixed
#当Slave从Master数据库读取日志时更新新写入日志中,如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。
#log-slave-updates
#read-only是用来限制普通用户对从数据库的更新操作,以确保从数据库的安全性,不过如果是超级用户依然可以对从数据库进行更新操作
#read-only
#跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
#slave_skip_errors=1062
2、重启mysql
service mysql restart
3、创建slave帐号slave,密码123456
grant replication slave on *.* to 'slave'@'%' identified by '123456';
flush privileges;
4、查询master的状态
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 590 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
至此主数据库的配置就完成了。
======================================================
从数据库slave配置:
1、修改MySQL配置my.cnf:
# 从数据库端ID号 建议取ip最后段
server-id =2
2、执行同步命令
mysql> change master to master_host='192.168.1.111',master_user='slave',master_password='123456',master_log_file='bin-log.000002',master_log_pos=590;
# 开启同步功能
mysql>start slave;
3、检查从数据库状态:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.111
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin-log.000002
Read_Master_Log_Pos: 903
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 631
Relay_Master_Log_File: bin-log.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 903
Relay_Log_Space: 842
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 880af816-d27e-11e6-a065-080027451959
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
Slave_IO_Running及Slave_SQL_Running进程必须正常运行,即YES状态,否则说明同步失败。
至此主从配置就结束了。可以去修改主数据库的数据来看看是否发生了修改。
最后特别强调点,如果是win7下安装的mysql-5.7.17压缩版,默认的文件是my-default.ini,这个文件的名字一定要改成my.ini。否则的话win上的做主库是不会生成二进制日志,这个困扰我好久。
最后补上一些主库的查询的语句:
#查询log_bin是否打开
show variables like 'log_bin';
#查询log_bin的相关信息
show variables like '%log_bin%';
#关闭复制
stop slave;
#打开复制
start slave;