mysql 之主从复制 主从集群配置
数据库复制replication的实现原理
-
- 主服务器有语句的操作，对磁盘也有影响
-
- 从服务器要么跟着语句走 要么跟着磁盘变化走
所以：
1.主服务器
建立2进制日志，每产生语句或磁盘变化，写进日志binlog。
2.从服务器
读取主服务器binlog 形成relaylog（中继日志） 经语法分析变成从服务器的数据。
3.授权
由于数据都很私密，所以主服务器要授权复制账号 replication。
从服务器利用复制账号来坚挺主服务器的日志。
操作
1. 首先编辑主服务器mysql的配置文件 my.cnf (apt-get 安装位置 /etc/mysql/mysql.conf.d/ mysqld.cnf)
# The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. server-id = 147 指定server_id log_bin = /var/log/mysql/mysql-bin.log 指定bin-log文件位置 expire_logs_days = 10 设置过期时间 max_binlog_size = 100M 设置文件大小上限 #statement row mixed binlog-format=mixed 设置匹配模式 此处选择mixed模式
-
- binlog-format 日志格式用哪种好？
- 有statement ,row ,mixed (前两种的混合)
- 以 insert into tx (value1,value2) 为例，影响一行且为新增一行，不影响其他行，这种情况用row比较好 会直接复制磁盘上一行的变化
- 以 update tx set name=value1 为例，影响一行且为更新一行，不影响其他行，这种情况用row比较好 会直接复制磁盘上一行的变化
- 以 update tx set count=count+1 为例，对于磁盘上很多行都发生了变化 ，影响多行，语句却简单，此处适合statement的日志
- 以上两种格式各有优势，mysql 提供了mixed 模式来帮我们分析
- binlog-format 日志格式用哪种好？
2. 编辑从服务器的my.cnf
[mysqld] port = 3306 server-id=69 指定server-id relay-log=mysql-relay 指定relay-log
3.重启各自的mysql
sudo /etc/init.d/mysql restart
4.主服务器master设置复制授权账号
grant replication client,replication slave on *.* to 'rep1'@'192.168.%.%'//授权给192.168打头的 identified by 'rep1';//密码
-
- 查看主服务器的状态
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+
-
- position 的含义：
- 复制的日志文件的开始位置
- binery log 在/var/lib/mysql下
5.从服务器 给你一个账号去连你的master
change master to master_host='192.168.5.199',//主服务器地址 master_user='rep1',//授权用户名 master_password='rep1',//授权账号 master_log_file='mysql-bin.000001', //bin-log文件 master_log_pos=154; //同步的位置
6.重置从服务器 并启动slave
mysql>reset slave
-
- 启动slave
mysql>start slave
-
- 查看slave状态
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.147 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000008 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 Exec_Master_Log_Pos: 154 Relay_Log_Space: 523 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: 147 Master_UUID: dad611d4-3625-11e8-a11f-080027c354d3 Master_Info_File: D:\wamp64\bin\mysql\mysql5.7.14\data\master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
测试
1.先查看主服务器，从数据库信息
mysql> show databases; ----->master +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | twusa | +--------------------+ 5 rows in set (0.01 sec)
mysql> show databases; ----->slave
+--------------------+
| Database |
+--------------------+
| information_schema |
| blog |
| ecshop |
| mysql |
| performance_schema |
| shop |
| sys |
| test |
+--------------------+
8 rows in set (0.02 sec)
2.主服务器创建新的数据库test_master 并查看bin-log大小
-rw-r----- 1 mysql mysql 322 4月 9 16:47 mysql-bin.000009
mysql> create database test_123; Query OK, 1 row affected (0.01 sec)
-rw-r----- 1 mysql mysql 493 4月 9 16:52 mysql-bin.000009
这个时候回过头来看从库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | blog | | ecshop | | mysql | | performance_schema | | shop | | sys | | test | | test123 |--------->发现多了这一条 | twusa | +--------------------+ 10 rows in set (0.00 sec)
至此主从复制完成，撒花 ~
遇到的问题
1.启动slave 一直显示正在connecting
-
- 解决：先试试远程登陆主库，如果登陆不上，有可能是防火墙的原因，关闭防火墙即可，远程登陆成功即可。
2.Got fatal error 1236 from master when reading data from binary log: 'unknown error reading log event on the master; the first event '' at 4, the last event read from '/opt/3306/mysql-bin.000008' at 154, the last byte read from '/var/log/mysql-bin.000008' at 155.'
-
- 解决:
-
- 主库：
- flush logs;
- show master status;
- 记下File, Position。
- 从库
- stop slave;
- CHANGE MASTER TO MASTER_LOG_FILE...............再来一遍
- start slave;
- 主库：
-
- 解决:
备注
1.查看bin-log
sudo mysqlbinlog mysql-bin.000009 > masterbin000009.log
