下文主要给大家带来MySQL-5.5主从复制原理是什么及如何配置,希望MySQL-5.5主从复制原理是什么及如何配置能够带给大家实际用处,这也是我编辑这篇文章的主要目的。好了,废话不多说,大家直接看下文吧。
环境:
[root@SQL-M ~]# cat /etc/redhat-release
CentOS release 6.8 (Final)
[root@SQL-M ~]# uname -r
2.6.32-642.el6.x86_64
Master IP 192.168.0.88/24 eth0
Slave IP 192.168.0.90/24 eth0
主从复制原理:
当用户对数据有增删改操作时,主库本地存一份,另外会把用户增删改的操作记录在 binlog 里面(binlog是实现主从复制的基础),binlog的索引文件是mysql-bin.index;从库的IO线程根据本地master.info文件里面记录的ip、port、user、password、binlog name、pos连接主库IO线程,主库判断信息,正确就返回数据,返回的数据里包括下次复制起始点的binlog名称和pos值;从库收到数据后写入relay-log,同时把下次复制起始点的binlog名称和pos值刷新进master.info文件,之后从库的SQL线程读取relay-log里面的SQL语句,执行语句将数据写入本地磁盘,主从复制完成。
配置要点:
主从复制,主库开启 bin-log 从库开启 relay-log ,主从 server-id 不能相同。
Master 配置:
[root@SQL-M ~]# vim /etc/my.cnf
[client]
port=3306
socket= /usr/local/mysql/mysql.sock
default-character-set = utf8
[mysql]
no-auto-rehash
prompt=Master>\_
[mysqld]
user = mysql
port = 3306
socket = /usr/local/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
character-set-server = utf8
skip-character-set-client-handshake
init-connect = 'SET NAMES utf8'
open_files_limit=1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
server-id = 1 <<--- id 为 1
log-bin = /usr/local/mysql/data/mysql-bin <<--- 开启 bin log
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062,1007,1008,1050
replicate-ignore-db=mysql
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 16M
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid
Slave 配置:
[root@SQL-S1 ~]# vim /etc/my.cnf
[client]
port=3306
socket= /usr/local/mysql/mysql.sock
default-character-set = utf8
[mysql]
no-auto-rehash
prompt=Slave>\_
[mysqld]
user = mysql
port = 3306
socket = /usr/local/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
character-set-server = utf8
skip-character-set-client-handshake
init-connect = 'SET NAMES utf8'
open_files_limit=1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
server-id = 2 <<--- id 为 2
relay-log =/usr/local/mysql/data/relay-bin <<--- 开启 relay log
relay-log-info-file = /usr/local/mysql/data/relay-log.info
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062,1007,1008,1050
replicate-ignore-db=mysql
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 16M
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Master 端准备数据和创建复制用户
[root@SQL-M ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.55-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Master>
ster> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.00 sec)
Master> use school;
Database changed
Master>
Master> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| test01 |
| test02 |
+------------------+
3 rows in set (0.00 sec)
Master> select * from student;
+----+--------+-----+-----+
| id | name | sex | age |
+----+--------+-----+-----+
| 1 | 小东 | 男 | 0 |
| 3 | 小北 | 女 | 12 |
+----+--------+-----+-----+
2 rows in set (0.00 sec)
Master> grant replication slave on *.* to rep@'192.168.0.%' identified by '123'; # 创建专门用于主从复制的用户
Query OK, 0 rows affected (0.00 sec)
Master> select user,host from mysql.user;
+------+-------------+
| user | host |
+------+-------------+
| root | 127.0.0.1 |
| rep | 192.168.0.% |
| root | localhost |
+------+-------------+
3 rows in set (0.00 sec)
Master> show grants for replicaton@'192.168.0.%';
ERROR 1141 (42000): There is no such grant defined for user 'replicaton' on host '192.168.0.%'
Master> show grants for rep@'192.168.0.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.0.% |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.0.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
slave 端没有数据,处于初始状态
[root@SQL-S1 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.55 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Slave01> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
master 端用 mysqldump 导出数据
由于 mysqldump 是逻辑备份程序,所以要确保 MySQL 服务是启动状态。
[root@SQL-M ~]# mysqldump -uroot -p -A -B -F --master-data=1 --events >/tmp/sql_full_back.sql
Enter password:
[root@SQL-M ~]# ll -h /tmp/
total 152K
-rw-r--r-- 1 root root 151K Apr 23 12:48 sql_full_back.sql
mysqldump 参数:
-A 备份所有库表
-B 在导出的 sql 文件里加入建库语句,从库导入文件时就省事很多
-F 刷新 bin log,这个在增量恢复时有用
--master-data=1 这个参数的作用是在导出的 sql 文件里会加入一条语句“CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=xxx;” ,这样在从库导入数据后执行 CHANGE MASTER 时就不用加上bin-log和pos值了;=2 则是注释。
--events 忽略警告 Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
数据量大可以备份时gzip压缩:
mysqldump -uroot -p -A -B -F --master-data=1 --events|gzip >/tmp/sql_full_back.sql.gz
把备份文件 scp 到从库
[root@SQL-M ~]# scp /tmp/sql_full_back.sql 192.168.0.90:/tmp/
The authenticity of host '192.168.0.90 (192.168.0.90)' can't be established.
RSA key fingerprint is fb:9f:50:cd:ac:59:8b:a3:83:83:95:7c:62:d1:64:d2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.90' (RSA) to the list of known hosts.
root@192.168.0.90's password:
sql_full_back.sql 100% 546KB 546.1KB/s 00:00
从库导入备份文件并检查
[root@SQL-S1 ~]# ll /tmp/
total 548
-rw-r--r-- 1 root root 559192 Apr 23 13:10 sql_full_back.sql
[root@SQL-S1 ~]#
[root@SQL-S1 ~]# mysql -uroot -p </tmp/sql_full_back.sql
Enter password:
[root@SQL-S1 ~]# mysql -uroot -p # 登录检查一下
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.55 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Slave> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.00 sec)
Slave>
Slave> use school;
Database changed
Slave> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| test01 |
| test02 |
+------------------+
3 rows in set (0.00 sec)
Slave> select * from student;
+----+--------+-----+-----+
| id | name | sex | age |
+----+--------+-----+-----+
| 1 | 小东 | 男 | 0 |
| 3 | 小北 | 女 | 12 |
+----+--------+-----+-----+
2 rows in set (0.00 sec)
从库导入备份文件检查成功后 CHANGE MASTER
Slave> CHANGE MASTER TO MASTER_HOST='192.168.0.88',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='123';
Query OK, 0 rows affected (0.01 sec)
Slave> start slave; # 启动 slave
Query OK, 0 rows affected (0.00 sec)
Slave> show slave status\G # 查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.88
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000016
Read_Master_Log_Pos: 297
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000016
Slave_IO_Running: Yes <<--- 正常
Slave_SQL_Running: Yes <<--- 正常
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 297
Relay_Log_Space: 403
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 的状态判断:
Slave_IO_Running: Yes # IO线程负责与主库通信传输数据
Slave_SQL_Running: Yes # SQL线程,读取中继日志(rely-log),再把数据写入本地存储
Seconds_Behind_Master: 0 # 延迟时间,从主获取数据的延迟时间,
这三个参数是主从复制健康检查的监控的重点。
主库查看线程状态
Master> show processlist;
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 27 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 29 | rep | 192.168.0.90:64017 | NULL | Binlog Dump | 497 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
测试主从复制
Master 添加数据
Master> use school;
Database changed
Master> select * from student;
+----+--------+-----+-----+
| id | name | sex | age |
+----+--------+-----+-----+
| 1 | 小东 | 男 | 0 |
| 3 | 小北 | 女 | 12 |
+----+--------+-----+-----+
2 rows in set (0.00 sec)
Master> insert into student values(4,'楠楠','男',15); # 插入新数据
Query OK, 1 row affected (0.00 sec)
Master> select * from student;
+----+--------+-----+-----+
| id | name | sex | age |
+----+--------+-----+-----+
| 1 | 小东 | 男 | 0 |
| 3 | 小北 | 女 | 12 |
| 4 | 楠楠 | 男 | 15 |
+----+--------+-----+-----+
3 rows in set (0.00 sec)
Slave 端检查
Slave> use school;
Database changed
Slave> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| test01 |
| test02 |
+------------------+
3 rows in set (0.00 sec)
Slave> select * from student;
+----+--------+-----+-----+
| id | name | sex | age |
+----+--------+-----+-----+
| 1 | 小东 | 男 | 0 |
| 3 | 小北 | 女 | 12 |
| 4 | 楠楠 | 男 | 15 | <<---- 可以看到新的数据已经复制到位
+----+--------+-----+-----+
3 rows in set (0.00 sec)
以上主从复制配置完成
===================== 开启半同步模式 =========================
主从复制实际是异步的过程:
Master IO_thread --> Slave IO_thread -->Slave SQL_thread -->Slave localdisk
Master IO_thread 把数据交给 Slave IO_thread 之后就不管了,后面的数据存储有没有成功Master是不知道的,这样对数据来说显然是不够安全的,无法保证数据完整正确地存储在Slave端。
半同步复制
介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。
半同步是以已经实现主从复制为前提,并且MySQL版本为5.5及以上。
实现半同步的插件:
[root@SQL-M ~]# ll /usr/local/mysql/lib/plugin/
-rwxr-xr-x 1 mysql mysql 170878 Mar 18 13:14 semisync_master.so
-rwxr-xr-x 1 mysql mysql 88959 Mar 18 13:14 semisync_slave.so
很清楚,一个Master用的,一个Slave用的。
Master 端操作
Master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; # 安装插件
Query OK, 0 rows affected (0.13 sec)
Master> SET GLOBAL rpl_semi_sync_master_enabled = 1; # 启用插件
Query OK, 0 rows affected (0.00 sec)
Master> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
Master> show variables like 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 | <<--- 默认超时,单位毫秒;数据传输超时会自动转为异步复制,传输正常后会自动恢复为半同步。
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.00 sec)
Master> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
Master> show status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 0 | <<--- 还没有从库连接
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
Slave 端操作
Slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; # 安装slave插件
Query OK, 0 rows affected (0.11 sec)
Slave> SET GLOBAL rpl_semi_sync_slave_enabled = 1; # 启用插件
Query OK, 0 rows affected (0.00 sec)
Slave> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
Slave> stop slave; # 重启slave
Query OK, 0 rows affected (0.00 sec)
Slave> start slave;
Query OK, 0 rows affected (0.00 sec)
Slave> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
Slave> show status like 'rpl%';
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Rpl_semi_sync_slave_status | ON |
| Rpl_status | AUTH_MASTER |
+----------------------------+-------------+
2 rows in set (0.00 sec)
Slave> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.00 sec)
Master端检查从库连接情况
Master> show status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 | <<--- 可以看到有一个从库已经成功连接
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)
半同步测试
正常情况下master插入数据的速度很快
Master> insert into student values(6,'小欣','女',13);
Query OK, 1 row affected (0.00 sec) <<--- 速度很快
Master> insert into student values(7,'小倩','女',13);
Query OK, 1 row affected (0.00 sec) <<--- 一样
接下来模仿从库故障,停掉slave的 IO 线程
Slave> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
Master 再插入数据
Master> insert into student values(8,'姗姗','女',13);
Query OK, 1 row affected (10.00 sec) <<--- 超时了,自动转为异步
从库恢复正常
Slave> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
主库插入数据
Master> insert into student values(9,'小强','男',13);
Query OK, 1 row affected (0.00 sec) <<--- 自动恢复为半同步了
============================================================
到此半同步配置完成
对于以上关于MySQL-5.5主从复制原理是什么及如何配置,大家是不是觉得非常有帮助。如果需要了解更多内容,请继续关注我们的行业资讯,相信你会喜欢上这些内容的。
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。