MySQL主从备份
本文目录结构:
1.主从复制
1.1简介
1.2实操
1.3问题处理
其他:my.cnf主从配置参数
1.主从复制
1.1简介
原理
主服务器数据库的每次操作都会记录在二进制日志文件mysql-bin.xxx中。从服务器的I/O线程使用专用帐号登陆到主服务器中读取该二进制文件,并将文件内容写入到自己本地的中继日志relay-log文件中。然后从服务器的SQL线程会根据中继日志中的内容执行SQL语句,这要求两台服务器有同样的初态。
逻辑步骤:
1)主服务器数据库的每次操作都会记录在二进制日志文件mysql-bin.xxx中.
2)从库的IO线程向主库的主进程发送请求,主库验证从库,交给主库IO线程负责数据传输
3)主库IO线程对比从库发送过来的master.info里的信息,将binlog文件信息,偏移量和binlog文件名等发送给从库
4)从库接收到信息后,将binlog信息保存到relay-bin中,同时更新master.info的偏移量和binlog文件名
5)从库的SQL线程不断的读取relay-bin的信息,同时将读到的偏移量和文件名写道relay-log.info文件,binlog信息写进自己的数据库,一次同步操作完成。
6)完成上次同步后,从库IO线程不断的向主库IO线程要binlog信息
形式:
一主一从,一主多从,多主一从,主主复制,联级复制。
用途:
实时灾备,读写分类,备份。
条件:
主库开启binlog日志
主从server-id不同
从库可以连到主库
1.2.实操配置
1)同步初态
先将主机数据库加锁,避免数据改变
>use tatabase_name;
>flush tables with read lock;
再将主机数据备份
mysqldump -uroot -pxxx database_name > database_name.sql
备份完后解锁数据库
>unlock tables;
在从机从机相同的数据库,然后导入数据
>create table database_name;
>use database_name;
>source database_name.sql;
2)主机打开二进制日志
在/etc/my.cnf主要配置如下内容:
log-bin=my-bin #开启二进制日志
server-id=1 #设置为主库,server-id值为1
修改后重启mysql服务
3)查看主机日志记录状态
>show master status\G
显示内容格式如下,记住此状态,后面用到
***************** 1. row **************** File: my-bin.000001 #当前记录的日志 Position: 553 #日志中记录的位置 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
4)主机登录mysql创建允许从服务器同步数据的账号
> grant replication slave on *.* to 'user_name'@'slave_ip' identified by 'pass_word';
5)配置从机mysql,在从机/my.cnf配置如下内容:
log-bin=my-bin #开启二进制日志 server-id= 2 #主数据库id为1,不能相同。 replicate_wild_do_table=copytest.% #只同步copytest库下的表,可以不配置此项 relay_log=mysqld-relay-bin #记录中继日志 log-slave-updates=YES #从服务器同步后记录日志
也可以添加以下两行指定开启日志的库:
binlog_do_db=db_name1;db_name2 --指定同步的数据库
binlog_ignore_db=db_name3;db_name4 --指定不同步的数据库
注意:如果配置binlog_do_db其他库都将被忽略
修改完后重启mysql
6)根据前面在主机配置的账号,在从机登录mysql后打开同步功能
> change master to master_host='master_ip',master_user='user_name',master_password='pass_word',master_log_file='my-bin.000001',master_log_pos=553;
配置完后从起msyql的slave服务
> slave start;
7)查看从服务器是否成功开启同步
>show slave status\G
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.225 Master_User: user_name Master_Port: 3306 Connect_Retry: 60 Master_Log_File: my-bin.000001 Read_Master_Log_Pos: 1114 Relay_Log_File: mysqld-relay-bin.000004 Relay_Log_Pos: 1260 Relay_Master_Log_File: my-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: test.% Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1114 Relay_Log_Space: 1563 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
其中:Slave_IO_Running和Slave_SQL_Running的状态都是YES,说明同步开启成功。
现在就可以去主服务器上的test库下创建表开测试同步了
1.3问题处理
1)主服务器:show master status\G 输出Empty set
参考博文:https://blog.csdn.net/lanyang123456/article/details/85221071
原因:MySQL没有开启日志,可以登录MySQL如下查看
mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 6 rows in set (0.00 sec)
可以看到log_bin为关闭状态,在mysql 配置文件 /etc/my.cnf中
[mysqld]下添加:
log-bin=my-bin
然后重启MySQL,再次查看master状态:
mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: my-bin.000001 Position: 245 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
log-bin配置项表示binlog的base name,产生的日志文件名类似:my-bin.00001,my-bin.00002...
show master status命令列出了日志位点信息,包括binlog file,binlog position等。
2)从服务器:show slave status\G 发现Slave_IO_Runing:No
参考博文:https://blog.csdn.net/xu1314/article/details/7693906
参考博文:https://www.cnblogs.com/l-hh/p/9922548.html
注意查看从机 Last_IO_Error,下面原因可能不准,但这儿是保存信息,可以根据这定位
可能原因:
a)slave没有访问master的权限,应检查master的权限配置和slaver的登录master账号配置,以及通信。
b)slave的master日志文件配置与主服务器不一致
使用show slave status\G 查看从机状态,使用show master status\G 查看主机状态,此时slave的Last_IO_Error提示:
Coud not find find first log file name in binary log index file --文件名不对
Misconfigured master -server id was not set --主机的server-id没有配置,要配置且为1,然后从起主机的MySQL和从机的slave
Client requested master to start replication from impossible position;the... --从机读取主机日志位置超过主机日志最大位置
本人原因:文件不对和主机server-id未配置,处理如下
主机配置my.cnf中的server-id,值为1
主机从起msyql
停止从机slave:slave stop
从机修改配置:CHANGE MASTER TO MASTER_LOG_FILE='主机的File', MASTER_LOG_POS=0或比主机的Position小的正数;
从机启动slave:slave start
其他1:my.cnf主从配置主要参数
设置主数据库的参数信息,主要设置字段为server-id,log_bin,binlog_do_db ,其他字段参考参数定义自行设置, 配置文件中相关参数定义如下: