MySQL主从复制
概述
MySQL支持的复制类型:
1) 基于语句的复制。在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高;
2) 基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍;
3) 混合类型的复制。默认采用基于语句的复制,一旦发现基于语句无法精确复制时,就会采用基于行的复制;
MySQL主从复制的原理:
1) 在Master中每个事物更新数据完成之后,写入二进制日志中;
2) slave的I/O线程监控Master的二进制日志,一旦日志发生变化,就将变化的部分读取并写入到slave的中继日志中去;
3) slave中的SQL线程监控中继日志,一旦发生变化读取变化的内容,之后重放(重新执行一遍SQL语句),实现mysql数据的同步。
配置使用
前提条件:保证服务器之间的连通性,保证服务器之间的时间一致(可以搭建NTP服务器)。
1.各MySQL服务器节点安装msyql服务(关闭防火墙)
此处略,可以参考:http://www.cnblogs.com/zhichaoma/p/7487186.html
2. 配置MySQL Master主服务器
1)修改MySQL配置文件
[root@master ~]# vim /etc/my.cnf [mysqld] ...... //部分内容省略 server-id = 1 //修改 log-bin=master-bin //修改 log-slave-updates=true //增加 [root@master ~]# service mysqld restart //重新启动MySQL服务
2)登录MySQL程序,给从服务器以授权
[root@master ~]# mysql -uroot -p mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123.com'; mysql> flush privileges; mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 496 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.12 sec)
其中File列显示日志名,Position列显示偏移量,这两个值在后面配置从服务器的时候需要。Slave应从该点在Master上进行新的更新
3. 配置从服务器
1) 修改MySQL配置文件
[root@slave ~]# vim /etc/my.cnf [mysqld] ...... //部分内容省略 server-id = 2 //修改 relay-log=relay-log-bin //增加 relay-log-index=slave-relay-bin.index //增加 [root@slave ~]# service mysqld restart //重新启动MySQL服务
注意:这里的server-id 不能与主服务器的相同
2) 登录MySQL,配置同步
按主服务器结果更改下面命令中的master_log_file和master_log_pos参数;
[root@slave ~]# mysql -u root -p mysql> change master to master_host='192.168.10.1',master_user='myslave',master_password='123.com',master_log_file='master-bin.000001',master_log_pos=496; //配置同步 mysql> start slave; //启动同步 mysql> show slave status\G //查看Slave状态 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.1 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 496 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 254 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes //确保此处为YES Slave_SQL_Running: Yes //确保此处为YES Replicate_Do_DB: Replicate_Ignore_DB:
4.验证主从复制的效果
登录主库创建一个数据库,之后查看从库是否将主库中新创建的库同步
其他:
使用mysqlbinlog命令可以查看mysql的二进制日志文件
[root@master ~]# mysqlbinlog /usr/local/mysql/data/master-bin.000001 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #171012 13:48:00 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.22-log created 171012 13:48:00 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' EALfWQ8BAAAAZwAAAGsAAAABAAQANS41LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAQAt9ZEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #171012 13:51:28 server id 1 end_log_pos 264 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1507787488/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123.com' /*!*/; # at 264 #171012 13:51:32 server id 1 end_log_pos 421 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1507787492/*!*/; grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123.com' /*!*/; # at 421 #171012 13:52:03 server id 1 end_log_pos 496 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1507787523/*!*/; flush privileges /*!*/; # at 496 #171012 14:10:18 server id 1 end_log_pos 575 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1507788618/*!*/; create database aa /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;