mysql设置主从同步

1、主从同步定义

　　主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

　　使用主从同步的好处：

  1. 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
  2. 提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
  3. 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

 

2、主数据库配置（master）的配置

　　修改主数据库的配置文件my.cnf， windows下修改my.ini：

 1 log_bin=mysql-bin-1 #文件名mysql-bin-1
 2 server_id=1 #服务ID，用于区分服务，范围1~2^32-1，注意该值不要重复
 3 
 4 #MySQL 磁盘写入策略以及数据安全性
 5 #每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去
 6 innodb_flush_log_at_trx_commit=1 
 7 
 8 #当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
 9 sync_binlog=1
10 binlog-do-db=db1 #需要同步的数据库名称
11 
12 #mysql复制模式，三种：SBR（基于sql语句复制），RBR（基于行的复制），MBR（混合模式复制）
13 binlog_format=MIXED #混合模式复制
14 expire_logs_days=7 #binlog过期清理时间
15 max_binlog_size=20M #binlog每个日志文件大小

 　　配置完成后，重启mysql数据库。

 

3、从数据库（slave）配置

　　修改从数据库的配置文件my.cnf， windows下修改my.ini：

 1 log_bin=mysql-bin-2 #文件名mysql-bin-2
 2 server_id=2 #服务ID，用于区分服务，范围1~2^32-1，注意该值不要重复
 3 
 4 #MySQL 磁盘写入策略以及数据安全性
 5 #每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去
 6 innodb_flush_log_at_trx_commit=1 
 7 
 8 #当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
 9 sync_binlog=1
10 binlog-do-db=db1 #需要同步的数据库名称
11 
12 #mysql复制模式，三种：SBR（基于sql语句复制），RBR（基于行的复制），MBR（混合模式复制）
13 binlog_format=MIXED #混合模式复制
14 expire_logs_days=7 #binlog过期清理时间
15 max_binlog_size=20M #binlog每个日志文件大小

 　　配置完成后，重启mysql数据库。

 

 4、主数据库创建同步账号

　　可以专门为主数据库创建一个账号作为同步用，或者也可以为已有账号添加slave账号作为同步账号：

grant replication slave on *.* to 'repl'@'%' identified by 'repl';
# 注意replication slave的级别是global，所以无法指定到具体的表，只能*.*
# 格式说明：GRANT REPLICATION SLAVE ON *.* TO '帐号'@'从服务器IP或主机名，%代表所有' IDENTIFIED BY '密码';

 

 

5、开启主从同步

　　（1）在主数据库上查看binlog日志文件以及坐标：

1 mysql> show master status;
2 +--------------------+----------+--------------+------------------+-------------------+
3 | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4 +--------------------+----------+--------------+------------------+-------------------+
5 | mysql-bin-1.000001 |  2833551 | db1         |                  |                   |
6 +--------------------+----------+--------------+------------------+-------------------+
7 1 row in set (0.00 sec)

　　（2）从数据库配置访问信息

 1 change master to
 2 master_host='主数据库访问ip',
 3 master_port=主数据库访问端口,
 4 master_user='主数据库访问用户名',
 5 master_password='主数据库访问密码',
 6 master_log_file='主数据binlog名称，对应上面File名',
 7 master_log_pos=主数据日志文件坐标，对应上面Position;
 8 
 9 例：
10 change master to
11 master_host='192.168.1.100',
12 master_port=3306,
13 master_user='root',
14 master_password='root',
15 master_log_file='mysql-bin-1.000001',
16 master_log_pos=2833551;

　　（3）从数据库启动主从同步

 1 mysql> start slave;
 2 Query OK, 0 rows affected (0.04 sec)
 3 
 4 mysql> show slave status\G;
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.100
 8                   Master_User: root
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin-1.000001
12           Read_Master_Log_Pos: 3866805
13                Relay_Log_File: 
14                 Relay_Log_Pos: 5953
15         Relay_Master_Log_File: mysql-bin-2.000002
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: Yes
18               Replicate_Do_DB:
19           Replicate_Ignore_DB:
20            Replicate_Do_Table:
21        Replicate_Ignore_Table:
22       Replicate_Wild_Do_Table:
23   Replicate_Wild_Ignore_Table:
24                    Last_Errno: 0
25                    Last_Error:
26                  Skip_Counter: 0

 　　当Slave_IO_Running和Slave_SQL_Running的值都为yes时，表示同步开启成功。

 

posted on 2017-12-26 13:46  程序员AI  阅读(230)  评论(0)  编辑  收藏  举报

导航