目录
一、在master上创建复制帐号
二、配置master,开启binlog
三、配置slave
四、启动slave
1、在slave服务器上操作
2、开启slave
3、查看slave状态
五、 查看master和slave的线程状态
1、查看master线程的状态
2、查看slave线程状态
六、推荐的复制参数配置
1、sync_binlog
2、如果使用innodb 的推荐设置
3、在备库上推荐如下设置
4、通过relay_log_purge 选项来控制
5、通过relay_log_space_limit选项设置
6、主服务器的日志格式(binlog-format) 用哪种好?
七、复制总结
MySQL开始复制是很简单的过程,不过,根据特定的应用场景,都会在基本的步骤上有一些变化。最简单的场景就是一个新安装的master和slave,从高层来看,整个过程如下:
(1) 在主服务器上创建一个复制帐号;
(2) 配置master和slave;
(3) Slave连接master开始复制。
一、在master上创建复制帐号
在主库上操作,需要将数据存储到二进制日志中的服务器,然后每个slave都需要master开设的账号连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。
用户名的密码都会存储在文本文件master.info中。假如,你想创建repl用户,如下:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY '123456';
flush privileges;
复制事实上只需要有主库上的replication slave 权限,slave并不一定需要都有replication client 权限,那为什我们要把这两种权限给 主/备库都赋予呢?
1)用来监控和管理复制的账号需要replication client 权限,并且针对这两种目的使用同一个账号更加工容易
2)如果在主库上建立了账号,然后从主库将数据克隆到备库上时,备库也就设置好了变成主库所需要的配置,这样后续有需要可以方便地交换备库的角色
二、配置master,开启binlog
在my.cnf 中配置,下面的配置slave也是一样
(1)配置二进制日志
(2)配置server-id 通常默认的服务器ID 为 1,但是会导致和其他服务器ID冲突,一种通用的做法是使用服务器IP地址的末8位,但要保证它是不变而且唯一
[mysqld]
log-bin=mysql-bin
server-id=10
# 需要同步的日志的数据库
binlog-do-db=ty
#不同步的二进制数据库,如果不设置可以将其注释掉
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=personalsite
binlog-ignore-db=test
(3)重启master,运行SHOW MASTER STATUS,输出如下:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 353 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
三、配置slave
Slave的配置与master类似,你同样需要重启slave的MySQL。如下:
[mysqld]
server_id = 2
log-bin = mysql-bin #同时可以指定位置
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1
备注:relay_log的目录可以自定义,要保证有权限访问,否则会出现如下错误:
File '/var/lib/mysql/mysql-relay-bin.index' not found (Errcode: 13 - Permission denied)
chmod -R 777 /var/lib/mysql/
说明:
(1)server_id是必须的,而且唯一。slave没有必要开启二进制日志,但是在一些情况下,必须设置,例如,如果slave为其它slave的master,必须设置bin_log,这里我们开启了二进制日志。
(2)relay_log 指定中继日志的位置和命名(默认名称为hostname,但是,如果hostname改变则会出现问题)
(3)log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。
这样会给备库增加额外的工作,这样也有必要,有时候有些人开启了slave的二进制日志,却没有设置log_slave_updates,然后查看slave的数据是否改变,这是一种错误的配置。所以,尽量使用
(4)read_only,它防止改变数据(除了特殊的线程)。但是,read_only并不是很实用,特别是那些需要在slave上创建表的应用。
备注:不要在配置文件my.cnf 中设置 master_port 和master_host 这些选项,这是老的配置方式,已经被废除,它只会导致问题,不会有任何好处
四、启动slave
接下来就是让slave连接master,并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作,而应该使用CHANGE MASTER TO语句,该语句可以完全取代对配置文件的修改,而且它可以为slave指定不同的master,而不需要停止服务器。如下:
1、在slave服务器上操作
CHANGE MASTER TO MASTER_HOST='server1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;
1)master_log_pos 参数被设置为0,因为要才从日志的开头读起。这是对于master之前都是全新的,没有进行二进制文件存储操作
2)如果master之前已经配置过二进制文件,并且之前该slave也change过,后面reset slave ,此时要在master上show master status查看log position
3)如果之前master有配置过二进制文件,此时一个全新的slave配置到change到该master上,可以配置mysql-bin.000001 以及master_log_pos = 0,这时指的是将该master全部的数据都同步过来
4)配置好之后使用show slave status查看配置是否正确
2、开启slave
start slave ;
3、查看slave状态
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
#查看复制状态
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 266
Relay_Master_Log_File: mysql-bin.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
...................................
Master_Server_Id: 131
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
Slave_IO_State, Slave_IO_Running,和Slave_SQL_Running表示复制的状态
五、 查看master和slave的线程状态
1、查看master线程的状态
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 1
User: repl
Host: 192.168.80.133:41636
db: NULL
Command: Binlog Dump
Time: 6021
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2、查看slave线程状态
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 6042
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 6043
State: Waiting for master to send event
Info: NULL
六、推荐的复制参数配置
1、sync_binlog
在主库上关于二进制日志设置最重要的选项就是sync_binlog
sync_binlog = 1
如果开启该选项,mysql 每次在提交事务前会将二进制日志同步到磁盘上,保证在服务器奔溃时不会丢失事件。如果禁止该选项,服务器会少做一些工作,但二进制日志文件可能在服务器崩溃时损坏或丢失。在一个不需要作为主库的备库上,该选项带来了不必要的开销。
它只适用于二进制日志。而非中继日志(所以最好是在主库上设置该配置)
2、如果使用innodb 的推荐设置
innodb_flush_logs_at_trx_commit #Flush every log write
innodb_support_xa = 1
innodb_safe_binlog
3、在备库上推荐如下设置
log_bin = /var/lib/mysql/mysql-bin #从库的二进制日志,选择一个路径和名称
relay_log = /path/to/logs/relay-bin #为中继日志指定绝对路径
skip_slave_start = 1
read_only = 1
具体解释:
(1)read_only可以阻止大部分用户更改非临时表,除了复制sql线程和其他拥有超级权限的用户之外,这也是要尽量避免给正常账号授予超级权限的原因之一
(2)通过设置relay_log 可以避免中继日志文件基于机器名来命名,防止之前提到的可能在主库发生的问题。指定绝对路径可以避免多个mysql版本中存在的bug , 这些bug可能会导致中继日志,在一个意料的位置创建.
(3)skip_salve_start 选项能都阻止备库在崩溃后自动启动复制,这可以给你一些机会来修复可能发生的问题。如果备库在崩溃后自动启动并且处于不一致的状态,这可能就会导致更多的损坏。
4、通过relay_log_purge 选项来控制
如果备库与主库的延迟很大,备库的I/O 线程可能会写很多中继日志文件,SQL线程在重放完一个中继日志的事件后会尽快将其删除(通过relay_log_purge 选项来控制)
5、通过relay_log_space_limit选项设置
如果延迟非常严重,I/O 线程可能会把整个磁盘撑满,解决办法是配置relay_log_space_limit 变量,如果所有中继日志的大小之和超过这个值,I/O 线程会停止,等待SQL线程释放磁盘空间
6、主服务器的日志格式(binlog-format) 用哪种好?
有 statement,row, mixed3种,其中mixed是指前2种的混合.
binlog-format=statement/row/mixed
具体事例:
insert into xxtable values (x,y,z)
影响1行,且为新增1行, 对于其他行没有影响,这个情况,用row格式,直接复制磁盘上1行的新增变化.
update xxtable set age=21 where name="sss";
这个情况,一般也只是影响1行. 用row也比较合适.
update xxtable set salary=salary+100;
这个语句带来的影响,是针对每一行的, 因此磁盘上很多row都发生了变化,这种就应该适合statment格式的日志.
2种日志,各有各的高效的地方,mysql提供了mixed类型. 可以根据语句的不同,而自动选择适合的日志格式
七、复制总结
1、一台主服务器和一台从服务器,当从服务器发生写之后,这时便发生了错误,主从关系便丢失,主服务器写/更新/删除数据,都不会影响从服务器。
2、当主从服务器,关系在监听时;主服务器上(增、删、改) , 都会影响从服务器的数据;