mysql设置同步_mysql设置主从同步

1、主从同步定义

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

使用主从同步的好处：

通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。

提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据

在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

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 to2 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 to11 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.04sec)3

4 mysql> show slave status\G;5 *************************** 1. row ***************************6 Slave_IO_State: Waiting for master to send event7 Master_Host: 192.168.1.100

8 Master_User: root9 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: Yes17 Slave_SQL_Running: Yes18 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时，表示同步开启成功。