MySQL主从备份

主从同步机制

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。

每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。

主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

1. 主服务器设置

1. 修改mysqld.cnf

在/etc/mysql/mysql.conf.d/mysqld.cnf的[mysqld]下新增字段

log-bin=mysql-bin
server-id=1
binlog-do-db=test

binlog-do-db是需要备份的数据库
然后重启mysql服务

2. 新增slave账号

create database test;
create user 'slave'@'%' identified by 'slave123';
grant replication slave on test.* to 'slave'@'%';
flush privileges;

然后查看master的status

show master status;

3. 从服务器设置

1. 修改mysqld.cnf

在[mysqld]下新增字段

server-id=2

重启mysql服务

2. 增加主服务器设置

使用root账号登录mysql，输入如下命令

create database test;
change master to master_host='121.248.50.86',master_user='slave',master_password='slave123',master_log_file='mysql-bin.000003',master_log_pos=749;

master_host是主服务器ip
master_user 是刚刚在主服务器上创建的slave账号
master_log_file,master_log_pos是主服务器中show master status;获取到的信息

3. 启动salve服务

在mysql中输入命令

start slave;
show slave status \G;

说明主从备份成功了，可以在master数据库上操作，slave数据库也会有相应的改变

4. 相关参数介绍

1. –master-retry-count=count

Property	Value
Command-Line Format	–master-retry-count=#
Deprecated	5.6.1
Type	Integer
Default Value	86400
Minimum Value	0
Maximum Value (64-bit platforms)	18446744073709551615
Maximum Value (32-bit platforms)	4294967295

The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement (default 60). Reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400. A value of 0 means “infinite”; the slave attempts to connect forever.

This option is deprecated as of MySQL 5.6.1 and will be removed in a future MySQL release. Applications should be updated to use the MASTER_RETRY_COUNT option of the CHANGE MASTER TO statement instead.

2. –replicate-do-db=db_name
   The effects of this option depend on whether statement-based or row-based replication is in use.
   Statement-based replication(use为主，如果use B，而salve是复制A，则该条语句不起作用). Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not replicate cross-database statements such as UPDATE some_db.some_table SET foo=‘bar’ while a different database (or no database) is selected.
   An example of what does not work as you might expect when using statement-based replication: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:

   USE prices;
   UPDATE sales.january SET amount=amount+1000;
   

   Row-based replication(以SQL语句为主，use没什么作用). Tells the slave SQL thread to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. Suppose that the slave is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the master

   USE prices;
   UPDATE sales.february SET amount=amount+100;
   

   The february table in the sales database on the slave is changed in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued.
   If you need cross-database updates to work, use --replicate-wild-do-table=db_name.%

3. –replicate-ignore-db=db_name
   这个与 --replicate-do-db，忽略db

4. –slave-net-timeout=seconds

   operty	Value
   mmand-Line Format	–slave-net-timeout=#
   stem Variable	slave_net_timeout
   ope	Global
   namic	Yes
   pe	Integer
   fault Value	3600
   nimum Value	1

   The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).